From bf276b2bde33aabd3534d49bb4ea8accfb8dd4bb Mon Sep 17 00:00:00 2001
From: Ian Jackson <ijackson@chiark.greenend.org.uk>
Date: Mon, 2 Nov 2009 16:50:42 +0000
Subject: [PATCH 1/1] Ready to write id compacter

---
 yarrg/db-idempotent-populate | 200 +++++++++++++++++++++++++----------
 1 file changed, 147 insertions(+), 53 deletions(-)

diff --git a/yarrg/db-idempotent-populate b/yarrg/db-idempotent-populate
index 5e0a8d5..24dbf35 100755
--- a/yarrg/db-idempotent-populate
+++ b/yarrg/db-idempotent-populate
@@ -45,6 +45,7 @@ if (@ARGV and $ARGV[0] eq '-D') {
 @ARGV==1 or die;
 my ($oceanname) = @ARGV;
 
+
 #---------- setup ----------
 
 parse_info_serverside();
@@ -55,27 +56,67 @@ db_connect();
 
 $dbh->trace(1) if $trace;
 
-#---------- schema ----------
 
-foreach my $bs (qw(buy sell)) {
-    db_doall(<<END)
- CREATE TABLE IF NOT EXISTS $bs (
-	commodid	INTEGER			NOT NULL,
-	islandid	INTEGER			NOT NULL,
-	stallid		INTEGER			NOT NULL,
-	price		INTEGER			NOT NULL,
-	qty		INTEGER			NOT NULL,
-	PRIMARY KEY (commodid, islandid, stallid)
- );
- CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
- CREATE INDEX IF NOT EXISTS ${bs}_by_price  ON $bs (commodid, price, islandid);
-END
-    ;
-}
+#---------- schema update code ----------
+
+our @need_compact;
 
 sub table ($$) {
-    my ($table,$fields) = @_;
-    db_doall(" CREATE TABLE IF NOT EXISTS $table (\n$fields );");
+    my ($table, $fields) = @_;
+    table_maycompact($table,undef,undef,$fields);
+}
+
+sub table_maycompact ($$$$) {
+    my ($table, $cpact_idfield, $cpact_needupdates, $fields) = @_;
+
+    #----- parse $fields -----
+
+    my @want_fields;
+    my @want_field_specs;
+    my %want_field_specs;
+
+    foreach my $fspec (split /\n/, $fields) {
+	next unless $fspec =~ m/\S/;
+	if ($fspec =~ m/^\s*\+/) {
+	    push @want_field_specs, "\t".$';
+	    next;
+	} elsif ($fspec =~ m/^\s*(\w+)(\s+)(\w.*\S)\s*$/) {
+	    my ($f,$spaces,$rhs) = ($1,$2,$3);
+	    my $spec= "\t".$f.$spaces.$rhs;
+	    push @want_fields, $f;
+	    push @want_field_specs, $spec;
+	    $want_field_specs{$f}= $spec;
+	} else {
+	    die "$table $fspec ?";
+	}
+    }
+
+    my $want_field_specs= join ",\n", @want_field_specs;
+
+    #----- ensure table exists -----
+
+    db_doall(<<END);
+ CREATE TABLE IF NOT EXISTS $table (
+$want_field_specs
+	);
+END
+    my @need_recreate;
+
+    #----- check whether we need to remove autoinc -----
+
+    if ($fields !~ /\bautoinc/i) {
+	my $autoinc= $dbh->prepare(<<END);
+ SELECT sql FROM sqlite_master
+	WHERE type='table' and name=? and tbl_name=?
+END
+        $autoinc->execute($table,$table);
+	my ($sql)= $autoinc->fetchrow_array();
+	die unless defined $sql;
+	push @need_recreate, 'remove autoinc'
+	    if $sql =~ m/\bautoinc/i;
+    }
+
+    #----- check whether we need to add fields -----
 
     my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
     $check->execute();
@@ -83,69 +124,115 @@ sub table ($$) {
     $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
     $check->finish();
 
-    my (@have_fields, @missing_fields);
-    my $have_field_specs='';
+    my @have_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);
+    foreach my $f (@want_fields) {
 	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";
+	    push @have_field_specs, $want_field_specs{$f};
 	} else {
-	    push @missing_fields, $f;
+	    push @need_recreate, "field $f";
 	}
     }
 
-    return unless @missing_fields;
-    print "    Adding missing fields to $table: @missing_fields ...\n";
+    #----- Do we need to recreate ? -----
+    return unless @need_recreate;
+    # yes:
+
+    print "    Recreating $table:\n";
+    print "        $_\n" foreach @need_recreate;
 
     my $have_fields= join ',', @have_fields;
+    my $have_field_specs= join ",\n", @have_field_specs;
 
     db_doall(<<END);
  CREATE TEMPORARY TABLE aside_$table (
-$have_field_specs );
+$have_field_specs
+	);
  INSERT INTO aside_$table SELECT $have_fields FROM $table;
 
  DROP TABLE $table;
  CREATE TABLE $table (
-$fields );
+$want_field_specs
+	);
 
  INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
 
  DROP TABLE aside_$table;
 END
+
+    #----- Do we need to compact ids ? -----
+    return unless
+        defined $cpact_idfield
+	and grep { m/^remove autoinc/ } @need_recreate;
+    # yes:
+
+    print "        will compact\n";
+    unshift @$cpact_needupdates, [ $table ], [ $cpact_idfield ];
+
+    push @need_compact, {
+	Table => $table,
+	Id => $cpact_idfield,
+	Updates => $cpact_needupdates,
+	Fields => [ @want_fields ],
+	FieldSpecs => $want_field_specs
+	};
 }
 
-table('commods', <<END);
-	commodid	INTEGER	PRIMARY KEY	NOT NULL,
-	commodname	TEXT	UNIQUE		NOT NULL,
-	unitmass	INTEGER,
-	unitvolume	INTEGER,
-	ordval		INTEGER,
-	commodclass	TEXT,
+
+#---------- actual schema ----------
+
+foreach my $bs (qw(buy sell)) {
+    db_doall(<<END)
+ CREATE TABLE IF NOT EXISTS $bs (
+	commodid	INTEGER			NOT NULL,
+	islandid	INTEGER			NOT NULL,
+	stallid		INTEGER			NOT NULL,
+	price		INTEGER			NOT NULL,
+	qty		INTEGER			NOT NULL,
+	PRIMARY KEY (commodid, islandid, stallid)
+ );
+ CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
+ CREATE INDEX IF NOT EXISTS ${bs}_by_price  ON $bs (commodid, price, islandid);
+END
+    ;
+}
+
+table_maycompact('commods', 'commodid',
+		 [ [ qw(buy sell) ], [ qw(commodid) ],
+ ], <<END);
+	commodid	INTEGER	PRIMARY KEY	NOT NULL
+	commodname	TEXT	UNIQUE		NOT NULL
+	unitmass	INTEGER
+	unitvolume	INTEGER
+	ordval		INTEGER
+	commodclass	TEXT
 	inclass		INTEGER
 END
 
+table_maycompact('islands', 'islandid',
+		 [ [ qw(buy sell stalls uploads) ], [ qw(islandid) ], 
+		   [ qw(dists routes) ], [ qw(aiid biid) ], 
+ ], <<END);
+	islandid	INTEGER	PRIMARY KEY	NOT NULL
+	islandname	TEXT	UNIQUE		NOT NULL
+	archipelago	TEXT			NOT NULL
+END
+
+table('stalls', <<END);
+	stallid		INTEGER	PRIMARY KEY	NOT NULL
+	islandid	INTEGER			NOT NULL
+	stallname	TEXT			NOT NULL
+	+ UNIQUE (islandid, stallname)
+END
+
 table('commodclasses', <<END);
-	commodclass	TEXT	PRIMARY KEY	NOT NULL,
+	commodclass	TEXT	PRIMARY KEY	NOT NULL
 	size		INTEGER
 END
 
 db_doall(<<END)
- CREATE TABLE IF NOT EXISTS islands (
-	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	NOT NULL,
-	islandid	INTEGER			NOT NULL,
-	stallname	TEXT			NOT NULL,
-	UNIQUE (islandid, stallname)
- );
  CREATE TABLE IF NOT EXISTS uploads (
 	islandid	INTEGER PRIMARY KEY	NOT NULL,
 	timestamp	INTEGER			NOT NULL,
@@ -175,7 +262,6 @@ db_doall(<<END)
 END
     ;
 
-db_chkcommit();
 
 #---------- commodity list ----------
 
@@ -254,9 +340,9 @@ END
     foreach my $cl (sort keys %incl) {
 	$addclass->execute($cl, $incl{$cl});	
     }
-    db_chkcommit();
 }
 
+
 #---------- vessel types ----------
 {
     my $idempotent= $dbh->prepare(<<'END')
@@ -272,5 +358,13 @@ END
 	my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
 	$idempotent->execute(@qa);
     }
-    db_chkcommit();
+}
+
+
+#---------- put it all into effect ----------
+db_chkcommit();
+{
+    local $dbh->{AutoCommit} = 1;
+    print "    Vacuuming.\n";
+    $dbh->do('VACUUM');
 }
-- 
2.30.2