chiark / gitweb /
Database schema improvements
[ypp-sc-tools.db-test.git] / yarrg / db-idempotent-populate
1 #!/usr/bin/perl -w
2 #
3 # Normally run from
4 #  update-master-info
5 #
6 # usage: ./db-idempotent-populate <Oceanname>
7 #  creates or updates OCEAN-Oceanname.db
8 #  from source-info.txt
9
10 # This is part of the YARRG website.  YARRG is a tool and website
11 # for assisting players of Yohoho Puzzle Pirates.
12 #
13 # Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
14 #
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.
19 #
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.
24 #
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/>.
27 #
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.
31
32 use strict (qw(vars));
33
34 use DBI;
35
36 use Commods;
37 use CommodsDatabase;
38
39 my $trace;
40 if (@ARGV and $ARGV[0] eq '-D') {
41         $trace=1;
42         shift @ARGV;
43 }
44
45 @ARGV==1 or die;
46 my ($oceanname) = @ARGV;
47
48 #---------- setup ----------
49
50 parse_info_serverside();
51
52 db_setocean($oceanname);
53 db_writer();
54 db_connect();
55
56 $dbh->trace(1) if $trace;
57
58 #---------- schema ----------
59
60 foreach my $bs (qw(buy sell)) {
61     db_doall(<<END)
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,
67         qty             INTEGER                 NOT NULL,
68         PRIMARY KEY (commodid, islandid, stallid)
69  );
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);
72 END
73     ;
74 }
75
76 sub table ($$) {
77     my ($table,$fields) = @_;
78     db_doall(" CREATE TABLE IF NOT EXISTS $table (\n$fields );");
79
80     my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
81     $check->execute();
82     my %have_fields;
83     $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
84     $check->finish();
85
86     my (@have_fields, @missing_fields);
87     my $have_field_specs='';
88
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";
97         } else {
98             push @missing_fields, $f;
99         }
100     }
101
102     return unless @missing_fields;
103     print "    Adding missing fields to $table: @missing_fields ...\n";
104
105     my $have_fields= join ',', @have_fields;
106
107     db_doall(<<END);
108  CREATE TEMPORARY TABLE aside_$table (
109 $have_field_specs );
110  INSERT INTO aside_$table SELECT $have_fields FROM $table;
111
112  DROP TABLE $table;
113  CREATE TABLE $table (
114 $fields );
115
116  INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
117
118  DROP TABLE aside_$table;
119 END
120     $dbh->commit;
121 }
122
123 table('commods', <<END);
124         commodid        INTEGER PRIMARY KEY     NOT NULL,
125         commodname      TEXT    UNIQUE          NOT NULL,
126         unitmass        INTEGER,
127         unitvolume      INTEGER,
128         ordval          INTEGER,
129         commodclass     TEXT,
130         inclass         INTEGER
131 END
132
133 table('commodclasses', <<END);
134         commodclass     TEXT    PRIMARY KEY     NOT NULL,
135         size            INTEGER
136 END
137
138 db_doall(<<END)
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
143  );
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)
149  );
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
156  );
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)
162  );
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)
168  );
169  CREATE TABLE IF NOT EXISTS vessels (
170         name            TEXT                    NOT NULL,
171         mass            INTEGER                 NOT NULL,
172         volume          INTEGER                 NOT NULL,
173         shot            INTEGER                 NOT NULL,
174         PRIMARY KEY (name)
175  );
176 END
177     ;
178
179 $dbh->commit;
180
181 #---------- commodity list ----------
182
183 sub commodsortkey ($) {
184     my ($commod) = @_;
185     my $ordval= $commods{$commod}{Ordval};
186     return sprintf "B %20d", $ordval if defined $ordval;
187     return sprintf "A %s", $commod;
188 }
189
190 {
191     my $insert= $dbh->prepare(<<'END')
192  INSERT OR IGNORE INTO commods
193      (unitmass,
194       unitvolume,
195       commodname)
196      VALUES (?,?,?);
197 END
198     ;
199     my $setsizes= $dbh->prepare(<<'END')
200  UPDATE commods
201      SET unitmass = ?,
202          unitvolume = ?
203      WHERE commodname = ?
204 END
205     ;
206     my $setordval= $dbh->prepare(<<'END')
207  UPDATE commods
208      SET ordval = ?
209      WHERE commodname = ?
210 END
211     ;
212     my $setclass= $dbh->prepare(<<'END')
213  UPDATE commods
214      SET commodclass = ?
215      WHERE commodname = ?
216 END
217     ;
218     my $setinclass= $dbh->prepare(<<'END')
219  UPDATE commods
220      SET inclass = ?
221      WHERE commodname = ?
222 END
223     ;
224     my %incl;
225     foreach my $commod (sort {
226                 commodsortkey($a) cmp commodsortkey($b)
227             } keys %commods) {
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};
231         
232         my @qa= ($c->{Mass}, $c->{Volume}, $commod);
233         $insert->execute(@qa);
234         $setsizes->execute(@qa);
235         $setordval->execute($c->{Ordval} || 0, $commod);
236         my $cl= $c->{Class};
237         $setclass->execute($cl, $commod);
238
239         if (defined $c->{Ordval} and defined $cl) {
240             $incl{$cl}++;
241             $setinclass->execute($incl{$cl}, $commod);
242         }
243     }
244     db_doall(<<END);
245  DELETE FROM commodclasses;
246 END
247     my $addclass= $dbh->prepare(<<'END')
248  INSERT INTO commodclasses
249      (commodclass, size)
250      VALUES (?,?)
251 END
252     ;
253     foreach my $cl (sort keys %incl) {
254         $addclass->execute($cl, $incl{$cl});    
255     }
256     $dbh->commit;
257 }
258
259 #---------- vessel types ----------
260 {
261     my $idempotent= $dbh->prepare(<<'END')
262  INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
263                          VALUES (?,?,?,?)
264 END
265     ;
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);
273     }
274     $dbh->commit;
275 }