WIP database populate before perl route calc

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

diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate
index 893f4a74e84f8620a5ff581041348feac5a5fe7f..ccc79be3609a42fb0c6197839980f2641a7fcdef 100755 (executable)
--- a/pctb/db-idempotent-populate
+++ b/pctb/db-idempotent-populate
@@ -75,7 +75,7 @@ END
 }
 
 dbdoall(<<END)
- CREATE TABLE IF NOT EXISTS commodities (
+ CREATE TABLE IF NOT EXISTS commods (
        commodid        INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        commodname      TEXT    UNIQUE          NOT NULL,
        unitmass        INTEGER,
@@ -98,10 +98,10 @@ dbdoall(<<END)
        clientspec      TEXT                    NOT NULL,
        serverspec      TEXT                    NOT NULL
  );
- CREATE TABLE IF NOT EXISTS distances (
+ CREATE TABLE IF NOT EXISTS dists (
        aiid            INTEGER                 NOT NULL,
        biid            INTEGER                 NOT NULL,
-       distance        INTEGER                 NOT NULL,
+       dist            INTEGER                 NOT NULL,
        PRIMARY KEY (aiid, biid)
  );
 END
@@ -113,7 +113,7 @@ $dbh->commit;
 
 {
     my $sth= $dbh->prepare(<<'END')
- INSERT OR IGNORE INTO commodities (commodname) values (?);
+ INSERT OR IGNORE INTO commods (commodname) VALUES (?);
 END
     ;
     foreach my $commod (sort keys %commods) {
@@ -126,7 +126,7 @@ END
 
 {
     my $sth= $dbh->prepare(<<'END')
- INSERT OR IGNORE INTO islands (islandname, archipelago) values (?, ?);
+ INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?);
 END
     ;
     foreach my $archname (sort keys %$ocean) {
@@ -140,48 +140,79 @@ END
 
 #---------- routes ----------
 
-foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
-    warn "$route_mysteries{$oceanname}{$islandname} routes".
-         " for unknown island $islandname\n";
+{
+    foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
+       warn "$route_mysteries{$oceanname}{$islandname} routes".
+           " for unknown island $islandname\n";
+    }
+
+    dbdoall(<<END)
+ CREATE TEMPORARY TABLE newdists (
+       aiid            INTEGER                 NOT NULL,
+       biid            INTEGER                 NOT NULL,
+       dist            INTEGER                 NOT NULL
+ );
+ INSERT INTO newdists SELECT (aiid,biid,dist) FROM dists;
+ INSERT INTO newdists SELECT (biid,aiid,dist) FROM dists;
+ INSERT INTO newdists SELECT (islandid,islandid,0) FROM islands;
+END
+    ;
+
+    my $sth= $dbh->prepare(<<'END')
+ INSERT INTO newdists VALUES
+       ((SELECT islandid FROM islands WHERE islandname == ?),
+        (SELECT islandid FROM islands WHERE islandname == ?),
+        ?);
+END
+    ;
+    my $allroutes= $routes{$oceanname};
+    foreach my $an (keys %$allroutes) {
+       my $routes= $allroutes->{$an};
+       foreach my $bn (keys %$routes) {
+           $sth->execute($an, $bn, $routes->{$bn});
+       }
+    }
+
+    my $stmt_updatemain= $dbh->prepare(<<'END')
+ UPDATE dists SET dist = (
+       SELECT min(dist) FROM newdists
+       WHERE dists.aiid == newdists.aiid
+         AND dists.biid == newdists.biid
+         AND NOT dists.dist <= newdists.dist
+ );
+END
+
+    my $stmt_removeredund= $dbh->prepare(<<'END')
+ DELETE FROM newdists
+       WHERE dist > (
+               SELECT dists.dist
+               WHERE dists.aiid == newdists.aiid
+                 AND dists.aiid == newdists.aiid
+ );
+END
+    ;
+
+    my $stmt_gennew= $dbh->prepare(<<'END')
+ INSERT INTO newdists (
+       SELECT src.aiid, dst.biid, min(src.dist + dst.dist)
+       FROM dists AS src, dists AS dst ON src.biid == dst.aiid
+       GROUP BY src.aiid, dst.biid
+ );
+END
+    ;
+    
+    for (;;) {
+       $ar= $dbh->selectall_arrayref("select ia.islandname, ib.islandname,newdists.dist from newdists, islands as ia on newdists.aiid = ia.islandid, islands as ib on newdists.biid = ib.islandid;");
+       print Dumper($ar);
+
+       my $affected= $stmt_updatemain->execute();
+       last unless $affected;
+
+       $stmt_
+       
 }
 
 #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) */