From 277da54af30bc362980d1b12e944575381202c07 Mon Sep 17 00:00:00 2001
From: Ian Jackson <ian@liberator.relativity.greenend.org.uk>
Date: Sun, 26 Jul 2009 23:55:38 +0100
Subject: [PATCH] WIP database populate before perl route calc

---
 pctb/db-idempotent-populate | 119 +++++++++++++++++++++++-------------
 1 file changed, 75 insertions(+), 44 deletions(-)

diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate
index 893f4a7..ccc79be 100755
--- 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) */
-- 
2.30.2