6 # usage: ./db-idempotent-populate <Oceanname>
7 # creates or updates OCEAN-Oceanname.db
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();
46 db_setocean($oceanname);
50 #---------- schema ----------
52 foreach my $bs (qw(buy sell)) {
54 CREATE TABLE IF NOT EXISTS $bs (
55 commodid INTEGER NOT NULL,
56 islandid INTEGER NOT NULL,
57 stallid INTEGER NOT NULL,
58 price INTEGER NOT NULL,
60 PRIMARY KEY (commodid, islandid, stallid)
62 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
63 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
69 CREATE TABLE IF NOT EXISTS commods (
70 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
71 commodname TEXT UNIQUE NOT NULL,
75 CREATE TABLE IF NOT EXISTS islands (
76 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
77 islandname TEXT UNIQUE NOT NULL,
78 archipelago TEXT NOT NULL
80 CREATE TABLE IF NOT EXISTS stalls (
81 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
82 islandid INTEGER NOT NULL,
83 stallname TEXT NOT NULL,
84 UNIQUE (islandid, stallname)
86 CREATE TABLE IF NOT EXISTS uploads (
87 islandid INTEGER PRIMARY KEY NOT NULL,
88 timestamp INTEGER NOT NULL,
89 message TEXT NOT NULL,
90 clientspec TEXT NOT NULL,
91 serverspec TEXT NOT NULL
93 CREATE TABLE IF NOT EXISTS dists (
94 aiid INTEGER NOT NULL,
95 biid INTEGER NOT NULL,
96 dist INTEGER NOT NULL,
97 PRIMARY KEY (aiid, biid)
99 CREATE TABLE IF NOT EXISTS routes (
100 aiid INTEGER NOT NULL,
101 biid INTEGER NOT NULL,
102 dist INTEGER NOT NULL,
103 PRIMARY KEY (aiid, biid)
105 CREATE TABLE IF NOT EXISTS vessels (
107 mass INTEGER NOT NULL,
108 volume INTEGER NOT NULL,
109 shot INTEGER NOT NULL,
117 #---------- commodity list ----------
120 my $insert= $dbh->prepare(<<'END')
121 INSERT OR IGNORE INTO commods
128 my $update= $dbh->prepare(<<'END')
135 foreach my $commod (sort keys %commods) {
136 my $c= $commods{$commod};
137 die "no mass for $commod" unless defined $c->{Mass};
138 die "no colume for $commod" unless defined $c->{Volume};
139 my @qa= ($c->{Mass}, $c->{Volume}, $commod);
140 $insert->execute(@qa);
141 $update->execute(@qa);
146 #---------- vessel types ----------
148 my $idempotent= $dbh->prepare(<<'END')
149 INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
153 foreach my $name (sort keys %vessels) {
154 my $v= $vessels{$name};
155 my $shotdamage= $shotname2damage{$v->{Shot}};
156 die "no shot damage for shot $v->{Shot} for vessel $name"
157 unless defined $shotdamage;
158 my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
159 $idempotent->execute(@qa);