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;
122 table('commods', <<END);
123 commodid INTEGER PRIMARY KEY NOT NULL,
124 commodname TEXT UNIQUE NOT NULL,
132 table('commodclasses', <<END);
133 commodclass TEXT PRIMARY KEY NOT NULL,
138 CREATE TABLE IF NOT EXISTS islands (
139 islandid INTEGER PRIMARY KEY NOT NULL,
140 islandname TEXT UNIQUE NOT NULL,
141 archipelago TEXT NOT NULL
143 CREATE TABLE IF NOT EXISTS stalls (
144 stallid INTEGER PRIMARY KEY NOT NULL,
145 islandid INTEGER NOT NULL,
146 stallname TEXT NOT NULL,
147 UNIQUE (islandid, stallname)
149 CREATE TABLE IF NOT EXISTS uploads (
150 islandid INTEGER PRIMARY KEY NOT NULL,
151 timestamp INTEGER NOT NULL,
152 message TEXT NOT NULL,
153 clientspec TEXT NOT NULL,
154 serverspec TEXT NOT NULL
156 CREATE TABLE IF NOT EXISTS dists (
157 aiid INTEGER NOT NULL,
158 biid INTEGER NOT NULL,
159 dist INTEGER NOT NULL,
160 PRIMARY KEY (aiid, biid)
162 CREATE TABLE IF NOT EXISTS routes (
163 aiid INTEGER NOT NULL,
164 biid INTEGER NOT NULL,
165 dist INTEGER NOT NULL,
166 PRIMARY KEY (aiid, biid)
168 CREATE TABLE IF NOT EXISTS vessels (
170 mass INTEGER NOT NULL,
171 volume INTEGER NOT NULL,
172 shot INTEGER NOT NULL,
180 #---------- commodity list ----------
182 sub commodsortkey ($) {
184 my $ordval= $commods{$commod}{Ordval};
185 return sprintf "B %20d", $ordval if defined $ordval;
186 return sprintf "A %s", $commod;
190 my $insert= $dbh->prepare(<<'END')
191 INSERT OR IGNORE INTO commods
198 my $setsizes= $dbh->prepare(<<'END')
205 my $setordval= $dbh->prepare(<<'END')
211 my $setclass= $dbh->prepare(<<'END')
217 my $setinclass= $dbh->prepare(<<'END')
224 foreach my $commod (sort {
225 commodsortkey($a) cmp commodsortkey($b)
227 my $c= $commods{$commod};
228 die "no mass for $commod" unless defined $c->{Mass};
229 die "no volume for $commod" unless defined $c->{Volume};
231 my @qa= ($c->{Mass}, $c->{Volume}, $commod);
232 $insert->execute(@qa);
233 $setsizes->execute(@qa);
234 $setordval->execute($c->{Ordval} || 0, $commod);
236 $setclass->execute($cl, $commod);
238 if (defined $c->{Ordval} and defined $cl) {
240 $setinclass->execute($incl{$cl}, $commod);
244 DELETE FROM commodclasses;
246 my $addclass= $dbh->prepare(<<'END')
247 INSERT INTO commodclasses
252 foreach my $cl (sort keys %incl) {
253 $addclass->execute($cl, $incl{$cl});
258 #---------- vessel types ----------
260 my $idempotent= $dbh->prepare(<<'END')
261 INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
265 foreach my $name (sort keys %vessels) {
266 my $v= $vessels{$name};
267 my $shotdamage= $shotname2damage{$v->{Shot}};
268 die "no shot damage for shot $v->{Shot} for vessel $name"
269 unless defined $shotdamage;
270 my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
271 $idempotent->execute(@qa);