use Commods;
@ARGV==1 or die;
-my ($ocean) = @ARGV;
+my ($oceanname) = @ARGV;
-my $dbfn= "OCEAN-$ocean.db";
+my $dbfn= "OCEAN-$oceanname.db";
our $dbh;
}
}
+#---------- 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);
$dbh->commit;
-parse_masters();
+#---------- commodity list ----------
{
my $sth= $dbh->prepare(<<'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) */