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));
37 my ($oceanname) = @ARGV;
39 #---------- setup ----------
41 parse_info_serverside();
42 parse_info_serverside_ocean($oceanname);
43 our $ocean= $oceans{$oceanname};
45 db_setocean($oceanname);
49 #---------- schema ----------
51 foreach my $bs (qw(buy sell)) {
53 CREATE TABLE IF NOT EXISTS $bs (
54 commodid INTEGER NOT NULL,
55 islandid INTEGER NOT NULL,
56 stallid INTEGER NOT NULL,
57 price INTEGER NOT NULL,
59 PRIMARY KEY (commodid, islandid, stallid)
61 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
62 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
68 CREATE TABLE IF NOT EXISTS commods (
69 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
70 commodname TEXT UNIQUE NOT NULL,
74 CREATE TABLE IF NOT EXISTS islands (
75 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
76 islandname TEXT UNIQUE NOT NULL,
77 archipelago TEXT NOT NULL
79 CREATE TABLE IF NOT EXISTS stalls (
80 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
81 islandid INTEGER NOT NULL,
82 stallname TEXT NOT NULL,
83 UNIQUE (islandid, stallname)
85 CREATE TABLE IF NOT EXISTS uploads (
86 islandid INTEGER PRIMARY KEY NOT NULL,
87 timestamp INTEGER NOT NULL,
88 message TEXT NOT NULL,
89 clientspec TEXT NOT NULL,
90 serverspec TEXT NOT NULL
92 CREATE TABLE IF NOT EXISTS dists (
93 aiid INTEGER NOT NULL,
94 biid INTEGER NOT NULL,
95 dist INTEGER NOT NULL,
96 PRIMARY KEY (aiid, biid)
103 #---------- commodity list ----------
106 my $sth= $dbh->prepare(<<'END')
107 INSERT OR IGNORE INTO commods (commodname) VALUES (?);
110 foreach my $commod (sort keys %commods) {
111 $sth->execute($commod);
116 #---------- island list ----------
119 my $sth= $dbh->prepare(<<'END')
120 INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?);
123 foreach my $archname (sort keys %$ocean) {
124 my $arch= $ocean->{$archname};
125 foreach my $islandname (sort keys %$arch) {
126 $sth->execute($islandname, $archname);
132 #---------- routes ----------
135 foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
136 warn "$route_mysteries{$oceanname}{$islandname} routes".
137 " for unknown island $islandname\n";
140 my $allroutes= $routes{$oceanname};
144 sub distance_set_propagate ($$$$) {
145 my ($lev, $start, $upto, $start2upto) = @_;
146 $allroutes->{$start}{$upto}= $start2upto;
147 push @propqueue, [ $lev, $start, $upto ];
150 sub distance_propagate_now {
151 my ($lev, $start, $upto) = @_;
152 my $startref= $allroutes->{$start};
153 my $start2upto= $startref->{$upto};
154 my $uptoref= $allroutes->{$upto};
156 for my $next (keys %$uptoref) {
157 next if $next eq $upto;
158 my $unext= $uptoref->{$next};
159 next unless defined $unext;
160 distance_update("${lev}p", $start, $next, $start2upto + $unext);
164 sub distance_update ($$$$) {
165 my ($lev, $x, $y, $newdist) = @_;
166 distance_update_one("${lev}x",$x,$y,$newdist);
167 distance_update_one("${lev}y",$y,$x,$newdist);
170 sub distance_update_one ($$$$) {
171 my ($lev, $x, $y, $newdist) = @_;
172 my $xref= $allroutes->{$x};
173 my $currently= $xref->{$y};
174 return if defined($currently) and $currently <= $newdist;
175 distance_set_propagate("${lev}o",$x,$y,$newdist);
178 foreach my $xn (keys %$allroutes) {
179 my $routes= $allroutes->{$xn};
180 distance_set_propagate('0', $xn, $xn, 0);
181 foreach my $yn (keys %$routes) {
182 distance_set_propagate('0', $yn, $yn, 0);
183 distance_set_propagate('X', $xn, $yn, $routes->{$yn});
184 distance_set_propagate('Y', $yn, $xn, $routes->{$yn});
188 while ($ref= shift @propqueue) {
189 distance_propagate_now(@$ref);
196 my $sth= $dbh->prepare(<<'END')
197 INSERT INTO dists VALUES
198 ((SELECT islandid FROM islands WHERE islandname == ?),
199 (SELECT islandid FROM islands WHERE islandname == ?),
203 foreach my $xn (keys %$allroutes) {
204 my $routes= $allroutes->{$xn};
205 foreach my $yn (keys %$routes) {
206 $sth->execute($xn, $yn, $routes->{$yn});
211 # 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;