6 # usage: ./db-idempotent-populate <Oceanname>
7 # creates or updates OCEAN-Oceanname.db
8 # from master-master.txt
10 # This is part of ypp-sc-tools, a set of third-party tools for assisting
11 # players of Yohoho Puzzle Pirates.
13 # Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
15 # This program is free software: you can redistribute it and/or modify
16 # it under the terms of the GNU General Public License as published by
17 # the Free Software Foundation, either version 3 of the License, or
18 # (at your option) any later version.
20 # This program is distributed in the hope that it will be useful,
21 # but WITHOUT ANY WARRANTY; without even the implied warranty of
22 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 # GNU General Public License for more details.
25 # You should have received a copy of the GNU General Public License
26 # along with this program. If not, see <http://www.gnu.org/licenses/>.
28 # Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
29 # are used without permission. This program is not endorsed or
30 # sponsored by Three Rings.
32 use strict (qw(vars));
40 my ($oceanname) = @ARGV;
42 #---------- setup ----------
44 parse_info_serverside();
45 parse_info_serverside_ocean($oceanname);
46 our $ocean= $oceans{$oceanname};
48 db_setocean($oceanname);
52 #---------- schema ----------
54 foreach my $bs (qw(buy sell)) {
56 CREATE TABLE IF NOT EXISTS $bs (
57 commodid INTEGER NOT NULL,
58 islandid INTEGER NOT NULL,
59 stallid INTEGER NOT NULL,
60 price INTEGER NOT NULL,
62 PRIMARY KEY (commodid, islandid, stallid)
64 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
65 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
71 CREATE TABLE IF NOT EXISTS commods (
72 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
73 commodname TEXT UNIQUE NOT NULL,
77 CREATE TABLE IF NOT EXISTS islands (
78 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
79 islandname TEXT UNIQUE NOT NULL,
80 archipelago TEXT NOT NULL
82 CREATE TABLE IF NOT EXISTS stalls (
83 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
84 islandid INTEGER NOT NULL,
85 stallname TEXT NOT NULL,
86 UNIQUE (islandid, stallname)
88 CREATE TABLE IF NOT EXISTS uploads (
89 islandid INTEGER PRIMARY KEY NOT NULL,
90 timestamp INTEGER NOT NULL,
91 message TEXT NOT NULL,
92 clientspec TEXT NOT NULL,
93 serverspec TEXT NOT NULL
95 CREATE TABLE IF NOT EXISTS dists (
96 aiid INTEGER NOT NULL,
97 biid INTEGER NOT NULL,
98 dist INTEGER NOT NULL,
99 PRIMARY KEY (aiid, biid)
106 #---------- commodity list ----------
109 my $insert= $dbh->prepare(<<'END')
110 INSERT OR IGNORE INTO commods
117 my $update= $dbh->prepare(<<'END')
124 foreach my $commod (sort keys %commods) {
125 my $c= $commods{$commod};
126 die "no mass for $commod" unless defined $c->{Mass};
127 die "no colume for $commod" unless defined $c->{Volume};
128 my @qa= ($c->{Mass}, $c->{Volume}, $commod);
129 $insert->execute(@qa);
130 $update->execute(@qa);
135 #---------- island list ----------
138 my $sth= $dbh->prepare(<<'END')
139 INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?);
142 foreach my $archname (sort keys %$ocean) {
143 my $arch= $ocean->{$archname};
144 foreach my $islandname (sort keys %$arch) {
145 $sth->execute($islandname, $archname);
151 #---------- routes ----------
154 foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
155 warn "$route_mysteries{$oceanname}{$islandname} routes".
156 " for unknown island $islandname\n";
159 my $allroutes= $routes{$oceanname};
163 sub distance_set_propagate ($$$$) {
164 my ($lev, $start, $upto, $start2upto) = @_;
165 $allroutes->{$start}{$upto}= $start2upto;
166 push @propqueue, [ $lev, $start, $upto ];
169 sub distance_propagate_now {
170 my ($lev, $start, $upto) = @_;
171 my $startref= $allroutes->{$start};
172 my $start2upto= $startref->{$upto};
173 my $uptoref= $allroutes->{$upto};
175 for my $next (keys %$uptoref) {
176 next if $next eq $upto;
177 my $unext= $uptoref->{$next};
178 next unless defined $unext;
179 distance_update("${lev}p", $start, $next, $start2upto + $unext);
183 sub distance_update ($$$$) {
184 my ($lev, $x, $y, $newdist) = @_;
185 distance_update_one("${lev}x",$x,$y,$newdist);
186 distance_update_one("${lev}y",$y,$x,$newdist);
189 sub distance_update_one ($$$$) {
190 my ($lev, $x, $y, $newdist) = @_;
191 my $xref= $allroutes->{$x};
192 my $currently= $xref->{$y};
193 return if defined($currently) and $currently <= $newdist;
194 distance_set_propagate("${lev}o",$x,$y,$newdist);
197 foreach my $xn (keys %$allroutes) {
198 my $routes= $allroutes->{$xn};
199 distance_set_propagate('0', $xn, $xn, 0);
200 foreach my $yn (keys %$routes) {
201 distance_set_propagate('0', $yn, $yn, 0);
202 distance_set_propagate('X', $xn, $yn, $routes->{$yn});
203 distance_set_propagate('Y', $yn, $xn, $routes->{$yn});
207 while ($ref= shift @propqueue) {
208 distance_propagate_now(@$ref);
215 my $sth= $dbh->prepare(<<'END')
216 INSERT INTO dists VALUES
217 ((SELECT islandid FROM islands WHERE islandname == ?),
218 (SELECT islandid FROM islands WHERE islandname == ?),
222 foreach my $xn (keys %$allroutes) {
223 my $routes= $allroutes->{$xn};
224 foreach my $yn (keys %$routes) {
225 $sth->execute($xn, $yn, $routes->{$yn});
230 # select ia.islandname, ib.islandname,dists.dist from dists, islands as ia on dists.aiid = ia.islandid, islands as ib on dists.biid = ib.islandid order by ia.islandname, ib.islandname;