From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Mon, 2 Nov 2009 03:25:23 +0000 (+0000)
Subject: Database schema improvements
X-Git-Tag: 6.0~22
X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.db-live.git;a=commitdiff_plain;h=ffb20293c74628b5d7e7c4de33142f1229f2f991

Database schema improvements

* Do not create columns as AUTOINCREMENT (doesn't work well with
    INSERT OR IGNORE).  We still need to fix existing databases.

* Allow automatically adding columns to schema.

* Add various columns to commods, and commodclasses table, for ordering etc.
---

diff --git a/yarrg/TODO b/yarrg/TODO
index 83a6d93..16f3874 100644
--- a/yarrg/TODO
+++ b/yarrg/TODO
@@ -10,6 +10,9 @@ DATABASE/DICTIONARY MANAGER
 
 eliminate black dye from live database
 
+eliminate spurious autoincrements in existing tables
+and condense islandids (just this once)
+
 when update rejected print better error message including
  broken commodity name
 
diff --git a/yarrg/db-idempotent-populate b/yarrg/db-idempotent-populate
index 0ecde9e..6ca8382 100755
--- a/yarrg/db-idempotent-populate
+++ b/yarrg/db-idempotent-populate
@@ -36,6 +36,12 @@ use DBI;
 use Commods;
 use CommodsDatabase;
 
+my $trace;
+if (@ARGV and $ARGV[0] eq '-D') {
+	$trace=1;
+	shift @ARGV;
+}
+
 @ARGV==1 or die;
 my ($oceanname) = @ARGV;
 
@@ -47,6 +53,8 @@ db_setocean($oceanname);
 db_writer();
 db_connect();
 
+$dbh->trace(1) if $trace;
+
 #---------- schema ----------
 
 foreach my $bs (qw(buy sell)) {
@@ -65,20 +73,76 @@ END
     ;
 }
 
-db_doall(<<END)
- CREATE TABLE IF NOT EXISTS commods (
-	commodid	INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
+sub table ($$) {
+    my ($table,$fields) = @_;
+    db_doall(" CREATE TABLE IF NOT EXISTS $table (\n$fields );");
+
+    my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
+    $check->execute();
+    my %have_fields;
+    $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
+    $check->finish();
+
+    my (@have_fields, @missing_fields);
+    my $have_field_specs='';
+
+    foreach my $fspec (split /,/, $fields) {
+	next unless $fspec =~ m/\S/;
+	$fspec =~ m/^\s*(\w+)\s+(\w.*\S)\s*$/ or die "$table $fspec ?";
+	my ($f,$spec) = ($1,$2);
+	if ($have_fields{$f}) {
+	    push @have_fields, $f;
+	    $have_field_specs .= ",\n" if length $have_field_specs;
+	    $have_field_specs .= "\t$f\t\t$spec\n";
+	} else {
+	    push @missing_fields, $f;
+	}
+    }
+
+    return unless @missing_fields;
+    print "    Adding missing fields to $table: @missing_fields ...\n";
+
+    my $have_fields= join ',', @have_fields;
+
+    db_doall(<<END);
+ CREATE TEMPORARY TABLE aside_$table (
+$have_field_specs );
+ INSERT INTO aside_$table SELECT $have_fields FROM $table;
+
+ DROP TABLE $table;
+ CREATE TABLE $table (
+$fields );
+
+ INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
+
+ DROP TABLE aside_$table;
+END
+    $dbh->commit;
+}
+
+table('commods', <<END);
+	commodid	INTEGER	PRIMARY KEY	NOT NULL,
 	commodname	TEXT	UNIQUE		NOT NULL,
 	unitmass	INTEGER,
-	unitvolume	INTEGER
- );
+	unitvolume	INTEGER,
+	ordval		INTEGER,
+	commodclass	TEXT,
+	inclass		INTEGER
+END
+
+table('commodclasses', <<END);
+	commodclass	TEXT	PRIMARY KEY	NOT NULL,
+	size		INTEGER
+END
+
+db_doall(<<END)
  CREATE TABLE IF NOT EXISTS islands (
-	islandid	INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
+	islandid	INTEGER	PRIMARY KEY	NOT NULL,
 	islandname	TEXT	UNIQUE		NOT NULL,
 	archipelago	TEXT			NOT NULL
  );
  CREATE TABLE IF NOT EXISTS stalls (
-	stallid		INTEGER	PRIMARY KEY AUTOINCREMENT NOT NULL,
+	stallid		INTEGER	PRIMARY KEY	NOT NULL,
 	islandid	INTEGER			NOT NULL,
 	stallname	TEXT			NOT NULL,
 	UNIQUE (islandid, stallname)
@@ -116,6 +180,13 @@ $dbh->commit;
 
 #---------- commodity list ----------
 
+sub commodsortkey ($) {
+    my ($commod) = @_;
+    my $ordval= $commods{$commod}{Ordval};
+    return sprintf "B %20d", $ordval if defined $ordval;
+    return sprintf "A %s", $commod;
+}
+
 {
     my $insert= $dbh->prepare(<<'END')
  INSERT OR IGNORE INTO commods
@@ -125,20 +196,62 @@ $dbh->commit;
      VALUES (?,?,?);
 END
     ;
-    my $update= $dbh->prepare(<<'END')
+    my $setsizes= $dbh->prepare(<<'END')
  UPDATE commods
      SET unitmass = ?,
          unitvolume = ?
      WHERE commodname = ?
 END
     ;
-    foreach my $commod (sort keys %commods) {
+    my $setordval= $dbh->prepare(<<'END')
+ UPDATE commods
+     SET ordval = ?
+     WHERE commodname = ?
+END
+    ;
+    my $setclass= $dbh->prepare(<<'END')
+ UPDATE commods
+     SET commodclass = ?
+     WHERE commodname = ?
+END
+    ;
+    my $setinclass= $dbh->prepare(<<'END')
+ UPDATE commods
+     SET inclass = ?
+     WHERE commodname = ?
+END
+    ;
+    my %incl;
+    foreach my $commod (sort {
+	        commodsortkey($a) cmp commodsortkey($b)
+	    } keys %commods) {
 	my $c= $commods{$commod};
         die "no mass for $commod" unless defined $c->{Mass};
-        die "no colume for $commod" unless defined $c->{Volume};
+        die "no volume for $commod" unless defined $c->{Volume};
+	
 	my @qa= ($c->{Mass}, $c->{Volume}, $commod);
 	$insert->execute(@qa);
-	$update->execute(@qa);
+	$setsizes->execute(@qa);
+	$setordval->execute($c->{Ordval} || 0, $commod);
+	my $cl= $c->{Class};
+	$setclass->execute($cl, $commod);
+
+	if (defined $c->{Ordval} and defined $cl) {
+	    $incl{$cl}++;
+	    $setinclass->execute($incl{$cl}, $commod);
+	}
+    }
+    db_doall(<<END);
+ DELETE FROM commodclasses;
+END
+    my $addclass= $dbh->prepare(<<'END')
+ INSERT INTO commodclasses
+     (commodclass, size)
+     VALUES (?,?)
+END
+    ;
+    foreach my $cl (sort keys %incl) {
+	$addclass->execute($cl, $incl{$cl});	
     }
     $dbh->commit;
 }