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 commods (
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 dists (
102 aiid INTEGER NOT NULL,
103 biid INTEGER NOT NULL,
104 dist INTEGER NOT NULL,
105 PRIMARY KEY (aiid, biid)
112 #---------- commodity list ----------
115 my $sth= $dbh->prepare(<<'END')
116 INSERT OR IGNORE INTO commods (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 ----------
144 foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
145 warn "$route_mysteries{$oceanname}{$islandname} routes".
146 " for unknown island $islandname\n";
150 CREATE TEMPORARY TABLE newdists (
151 aiid INTEGER NOT NULL,
152 biid INTEGER NOT NULL,
153 dist INTEGER NOT NULL
155 INSERT INTO newdists SELECT (aiid,biid,dist) FROM dists;
156 INSERT INTO newdists SELECT (biid,aiid,dist) FROM dists;
157 INSERT INTO newdists SELECT (islandid,islandid,0) FROM islands;
161 my $sth= $dbh->prepare(<<'END')
162 INSERT INTO newdists VALUES
163 ((SELECT islandid FROM islands WHERE islandname == ?),
164 (SELECT islandid FROM islands WHERE islandname == ?),
168 my $allroutes= $routes{$oceanname};
169 foreach my $an (keys %$allroutes) {
170 my $routes= $allroutes->{$an};
171 foreach my $bn (keys %$routes) {
172 $sth->execute($an, $bn, $routes->{$bn});
176 my $stmt_updatemain= $dbh->prepare(<<'END')
177 UPDATE dists SET dist = (
178 SELECT min(dist) FROM newdists
179 WHERE dists.aiid == newdists.aiid
180 AND dists.biid == newdists.biid
181 AND NOT dists.dist <= newdists.dist
185 my $stmt_removeredund= $dbh->prepare(<<'END')
189 WHERE dists.aiid == newdists.aiid
190 AND dists.aiid == newdists.aiid
195 my $stmt_gennew= $dbh->prepare(<<'END')
196 INSERT INTO newdists (
197 SELECT src.aiid, dst.biid, min(src.dist + dst.dist)
198 FROM dists AS src, dists AS dst ON src.biid == dst.aiid
199 GROUP BY src.aiid, dst.biid
205 $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;");
208 my $affected= $stmt_updatemain->execute();
209 last unless $affected;
216 #print Dumper(\%routes);