3 # usage: ./db-idempotent-populate <Oceanname>
4 # creates or updates OCEAN-Oceanname.db
5 # from master-master.txt
7 # This is part of ypp-sc-tools, a set of third-party tools for assisting
8 # players of Yohoho Puzzle Pirates.
10 # Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
12 # This program is free software: you can redistribute it and/or modify
13 # it under the terms of the GNU General Public License as published by
14 # the Free Software Foundation, either version 3 of the License, or
15 # (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU General Public License for more details.
22 # You should have received a copy of the GNU General Public License
23 # along with this program. If not, see <http://www.gnu.org/licenses/>.
25 # Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
26 # are used without permission. This program is not endorsed or
27 # sponsored by Three Rings.
29 use strict (qw(vars));
36 my ($oceanname) = @ARGV;
38 my $dbfn= "OCEAN-$oceanname.db";
43 foreach my $cmd (split /\;/, $_[0]) {
44 $dbh->do("$cmd;") if $cmd =~ m/\S/;
48 #---------- setup ----------
50 parse_masters_ocean($oceanname);
51 our $ocean= $oceans{$oceanname};
53 $dbh= DBI->connect("dbi:SQLite:$dbfn",'','',
55 RaiseError=>1, ShowErrorStatement=>1,
57 or die "$dbfn $DBI::errstr ?";
59 #---------- schema ----------
61 foreach my $bs (qw(buy sell)) {
63 CREATE TABLE IF NOT EXISTS $bs (
64 commodid INTEGER NOT NULL,
65 islandid INTEGER NOT NULL,
66 stallid INTEGER NOT NULL,
67 price INTEGER NOT NULL,
69 PRIMARY KEY (commodid, islandid, stallid)
71 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
72 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
78 CREATE TABLE IF NOT EXISTS commodities (
79 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
80 commodname TEXT UNIQUE NOT NULL,
84 CREATE TABLE IF NOT EXISTS islands (
85 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
86 islandname TEXT UNIQUE NOT NULL,
87 archipelago TEXT NOT NULL
89 CREATE TABLE IF NOT EXISTS stalls (
90 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
91 islandid INTEGER NOT NULL,
92 stallname TEXT NOT NULL,
93 UNIQUE (islandid, stallname)
95 CREATE TABLE IF NOT EXISTS uploads (
96 islandid INTEGER PRIMARY KEY NOT NULL,
98 clientspec TEXT NOT NULL,
99 serverspec TEXT NOT NULL
101 CREATE TABLE IF NOT EXISTS distances (
102 aiid INTEGER NOT NULL,
103 biid INTEGER NOT NULL,
104 distance INTEGER NOT NULL,
105 PRIMARY KEY (aiid, biid)
112 #---------- commodity list ----------
115 my $sth= $dbh->prepare(<<'END')
116 INSERT OR IGNORE INTO commodities (commodname) values (?);
119 foreach my $commod (sort keys %commods) {
120 $sth->execute($commod);
125 #---------- island list ----------
128 my $sth= $dbh->prepare(<<'END')
129 INSERT OR IGNORE INTO islands (islandname, archipelago) values (?, ?);
132 foreach my $archname (sort keys %$ocean) {
133 my $arch= $ocean->{$archname};
134 foreach my $islandname (sort keys %$arch) {
135 $sth->execute($islandname, $archname);
141 #---------- routes ----------
143 foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
144 warn "$route_mysteries{$oceanname}{$islandname} routes".
145 " for unknown island $islandname\n";
149 #print Dumper(\%routes);
153 /* 'distances' will hold the final results. */
154 create table distances (
155 a varchar(40) not null references islands(name),
156 b varchar(40) not null references islands(name),
157 distance int not null,
160 /* Create a rule such that insertion into the distances table is only
161 possible if a shorter route or a new route is being recorded; all other
162 inserts are ignored. */
164 create or replace rule update_only_if_shorter as
165 on insert to distances
166 where ((new.a,new.b) in (select a,b from distances))
168 update distances set distance=(case when new.distance<=distance then
169 new.distance else distance end)
170 where a=new.a and b=new.b;
172 /* Start by copying manually entered routes into distances */
173 insert into distances (select a,b,distance from routes);
174 /* Also all the reverse routes */
175 insert into distances (select b,a,distance from routes);
176 /* Also all the null routes */
177 insert into distances (select distinct a,a,0 from routes);
178 insert into distances (select distinct b,b,0 from routes);
180 /* Now extend the distances table by computing new routes */
181 insert into distances (select source.a,dest.b,
182 min(source.distance+dest.distance)
183 from distances source,distances dest
184 where source.b=dest.a group by source.a,dest.b);
186 /* Repeat that query until no new routes are added (i.e. select count(*)
187 from distances; returns the same value after the query as it does before) */