X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.web-live.git;a=blobdiff_plain;f=pctb%2Fdb-idempotent-populate;h=893f4a74e84f8620a5ff581041348feac5a5fe7f;hp=1b0ef02214a005d834ac20dbe5b50960eb1bb757;hb=e3bd945bfba693d7467f28a2c59e77219387caa9;hpb=5c448a7c813853cb34ce8006bb6a544fbb15b2be;ds=sidebyside

diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate
index 1b0ef02..893f4a7 100755
--- a/pctb/db-idempotent-populate
+++ b/pctb/db-idempotent-populate
@@ -33,9 +33,9 @@ use DBI;
 use Commods;
 
 @ARGV==1 or die;
-my ($ocean) = @ARGV;
+my ($oceanname) = @ARGV;
 
-my $dbfn= "OCEAN-$ocean.db";
+my $dbfn= "OCEAN-$oceanname.db";
 
 our $dbh;
 
@@ -45,20 +45,27 @@ sub dbdoall ($) {
     }
 }
 
+#---------- setup ----------
+
+parse_masters_ocean($oceanname);
+our $ocean= $oceans{$oceanname};
+
 $dbh= DBI->connect("dbi:SQLite:$dbfn",'','',
 		   { AutoCommit=>0,
 		     RaiseError=>1, ShowErrorStatement=>1,
 		     unicode=>1 })
     or die "$dbfn $DBI::errstr ?";
 
+#---------- schema ----------
+
 foreach my $bs (qw(buy sell)) {
     dbdoall(<<END)
  CREATE TABLE IF NOT EXISTS $bs (
 	commodid	INTEGER			NOT NULL,
 	islandid	INTEGER			NOT NULL,
 	stallid		INTEGER			NOT NULL,
-	price		INTEGER,
-	qty		INTEGER,
+	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);
@@ -102,7 +109,7 @@ END
 
 $dbh->commit;
 
-parse_masters();
+#---------- commodity list ----------
 
 {
     my $sth= $dbh->prepare(<<'END')
@@ -114,3 +121,67 @@ END
     }
     $dbh->commit;
 }
+
+#---------- island list ----------
+
+{
+    my $sth= $dbh->prepare(<<'END')
+ INSERT OR IGNORE INTO islands (islandname, archipelago) values (?, ?);
+END
+    ;
+    foreach my $archname (sort keys %$ocean) {
+	my $arch= $ocean->{$archname};
+	foreach my $islandname (sort keys %$arch) {
+	    $sth->execute($islandname, $archname);
+	}
+    }
+    $dbh->commit;
+}
+
+#---------- routes ----------
+
+foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
+    warn "$route_mysteries{$oceanname}{$islandname} routes".
+	  " for unknown island $islandname\n";
+}
+
+#use Data::Dumper;
+#print Dumper(\%routes);
+
+__DATA__
+
+  /* 'distances' will hold the final results. */
+  create table distances (
+  a varchar(40) not null references islands(name),
+  b varchar(40) not null references islands(name),
+  distance int not null,
+  primary key (a,b) );
+
+  /* Create a rule such that insertion into the distances table is only 
+  possible if a shorter route or a new route is being recorded; all other 
+  inserts are ignored. */
+
+  create or replace rule update_only_if_shorter as
+  on insert to distances
+  where ((new.a,new.b) in (select a,b from distances))
+  do instead
+  update distances set distance=(case when new.distance<=distance then 
+  new.distance else distance end)
+  where a=new.a and b=new.b;
+
+  /* Start by copying manually entered routes into distances */
+  insert into distances (select a,b,distance from routes);
+  /* Also all the reverse routes */
+  insert into distances (select b,a,distance from routes);
+  /* Also all the null routes */
+  insert into distances (select distinct a,a,0 from routes);
+  insert into distances (select distinct b,b,0 from routes);
+
+  /* Now extend the distances table by computing new routes */
+  insert into distances (select source.a,dest.b,
+  min(source.distance+dest.distance)
+  from distances source,distances dest
+  where source.b=dest.a group by source.a,dest.b);
+
+  /* Repeat that query until no new routes are added (i.e. select count(*) 
+  from distances; returns the same value after the query as it does before) */