wip route computation

[ypp-sc-tools.web-live.git] / pctb / db-idempotent-populate

diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate
index 1b0ef02214a005d834ac20dbe5b50960eb1bb757..893f4a74e84f8620a5ff581041348feac5a5fe7f 100755 (executable)
--- a/pctb/db-idempotent-populate
+++ b/pctb/db-idempotent-populate
@@ -33,9 +33,9 @@ use DBI;
 use Commods;
 
 @ARGV==1 or die;
 use Commods;
 
 @ARGV==1 or die;

-my ($ocean) = @ARGV;
+my ($oceanname) = @ARGV;

 
 

-my $dbfn= "OCEAN-$ocean.db";
+my $dbfn= "OCEAN-$oceanname.db";

 
 our $dbh;
 
 
 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 ?";
 
 $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,
 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);
        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;
 
 
 $dbh->commit;
 

-parse_masters();
+#---------- commodity list ----------

 
 {
     my $sth= $dbh->prepare(<<'END')
 
 {
     my $sth= $dbh->prepare(<<'END')


@@ -114,3 +121,67 @@ END
     }
     $dbh->commit;
 }
     }
     $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) */