6 # usage: ./db-idempotent-populate <Oceanname>
7 # creates or updates OCEAN-Oceanname.db
10 # This is part of the YARRG website. YARRG is a tool and website
11 # for assisting 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 Affero General Public License as
17 # published by the Free Software Foundation, either version 3 of the
18 # License, or (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 Affero General Public License for more details.
25 # You should have received a copy of the GNU Affero 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 if (@ARGV and $ARGV[0] eq '-D') {
46 my ($oceanname) = @ARGV;
48 #---------- setup ----------
50 parse_info_serverside();
52 db_setocean($oceanname);
56 $dbh->trace(1) if $trace;
58 #---------- schema ----------
60 foreach my $bs (qw(buy sell)) {
62 CREATE TABLE IF NOT EXISTS $bs (
63 commodid INTEGER NOT NULL,
64 islandid INTEGER NOT NULL,
65 stallid INTEGER NOT NULL,
66 price INTEGER NOT NULL,
68 PRIMARY KEY (commodid, islandid, stallid)
70 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
71 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
77 my ($table,$fields) = @_;
78 db_doall(" CREATE TABLE IF NOT EXISTS $table (\n$fields );");
80 my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
83 $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
86 my (@have_fields, @missing_fields);
87 my $have_field_specs='';
89 foreach my $fspec (split /,/, $fields) {
90 next unless $fspec =~ m/\S/;
91 $fspec =~ m/^\s*(\w+)\s+(\w.*\S)\s*$/ or die "$table $fspec ?";
92 my ($f,$spec) = ($1,$2);
93 if ($have_fields{$f}) {
94 push @have_fields, $f;
95 $have_field_specs .= ",\n" if length $have_field_specs;
96 $have_field_specs .= "\t$f\t\t$spec\n";
98 push @missing_fields, $f;
102 return unless @missing_fields;
103 print " Adding missing fields to $table: @missing_fields ...\n";
105 my $have_fields= join ',', @have_fields;
108 CREATE TEMPORARY TABLE aside_$table (
110 INSERT INTO aside_$table SELECT $have_fields FROM $table;
113 CREATE TABLE $table (
116 INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
118 DROP TABLE aside_$table;
123 table('commods', <<END);
124 commodid INTEGER PRIMARY KEY NOT NULL,
125 commodname TEXT UNIQUE NOT NULL,
133 table('commodclasses', <<END);
134 commodclass TEXT PRIMARY KEY NOT NULL,
139 CREATE TABLE IF NOT EXISTS islands (
140 islandid INTEGER PRIMARY KEY NOT NULL,
141 islandname TEXT UNIQUE NOT NULL,
142 archipelago TEXT NOT NULL
144 CREATE TABLE IF NOT EXISTS stalls (
145 stallid INTEGER PRIMARY KEY NOT NULL,
146 islandid INTEGER NOT NULL,
147 stallname TEXT NOT NULL,
148 UNIQUE (islandid, stallname)
150 CREATE TABLE IF NOT EXISTS uploads (
151 islandid INTEGER PRIMARY KEY NOT NULL,
152 timestamp INTEGER NOT NULL,
153 message TEXT NOT NULL,
154 clientspec TEXT NOT NULL,
155 serverspec TEXT NOT NULL
157 CREATE TABLE IF NOT EXISTS dists (
158 aiid INTEGER NOT NULL,
159 biid INTEGER NOT NULL,
160 dist INTEGER NOT NULL,
161 PRIMARY KEY (aiid, biid)
163 CREATE TABLE IF NOT EXISTS routes (
164 aiid INTEGER NOT NULL,
165 biid INTEGER NOT NULL,
166 dist INTEGER NOT NULL,
167 PRIMARY KEY (aiid, biid)
169 CREATE TABLE IF NOT EXISTS vessels (
171 mass INTEGER NOT NULL,
172 volume INTEGER NOT NULL,
173 shot INTEGER NOT NULL,
181 #---------- commodity list ----------
183 sub commodsortkey ($) {
185 my $ordval= $commods{$commod}{Ordval};
186 return sprintf "B %20d", $ordval if defined $ordval;
187 return sprintf "A %s", $commod;
191 my $insert= $dbh->prepare(<<'END')
192 INSERT OR IGNORE INTO commods
199 my $setsizes= $dbh->prepare(<<'END')
206 my $setordval= $dbh->prepare(<<'END')
212 my $setclass= $dbh->prepare(<<'END')
218 my $setinclass= $dbh->prepare(<<'END')
225 foreach my $commod (sort {
226 commodsortkey($a) cmp commodsortkey($b)
228 my $c= $commods{$commod};
229 die "no mass for $commod" unless defined $c->{Mass};
230 die "no volume for $commod" unless defined $c->{Volume};
232 my @qa= ($c->{Mass}, $c->{Volume}, $commod);
233 $insert->execute(@qa);
234 $setsizes->execute(@qa);
235 $setordval->execute($c->{Ordval} || 0, $commod);
237 $setclass->execute($cl, $commod);
239 if (defined $c->{Ordval} and defined $cl) {
241 $setinclass->execute($incl{$cl}, $commod);
245 DELETE FROM commodclasses;
247 my $addclass= $dbh->prepare(<<'END')
248 INSERT INTO commodclasses
253 foreach my $cl (sort keys %incl) {
254 $addclass->execute($cl, $incl{$cl});
259 #---------- vessel types ----------
261 my $idempotent= $dbh->prepare(<<'END')
262 INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
266 foreach my $name (sort keys %vessels) {
267 my $v= $vessels{$name};
268 my $shotdamage= $shotname2damage{$v->{Shot}};
269 die "no shot damage for shot $v->{Shot} for vessel $name"
270 unless defined $shotdamage;
271 my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
272 $idempotent->execute(@qa);