6 # usage: ./db-idempotent-populate <Oceanname>
7 # creates or updates OCEAN-Oceanname.db
8 # from master-master.txt
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)
110 #---------- commodity list ----------
113 my $insert= $dbh->prepare(<<'END')
114 INSERT OR IGNORE INTO commods
121 my $update= $dbh->prepare(<<'END')
128 foreach my $commod (sort keys %commods) {
129 my $c= $commods{$commod};
130 die "no mass for $commod" unless defined $c->{Mass};
131 die "no colume for $commod" unless defined $c->{Volume};
132 my @qa= ($c->{Mass}, $c->{Volume}, $commod);
133 $insert->execute(@qa);
134 $update->execute(@qa);
139 #---------- island list ----------
140 #---------- routes ----------
141 # now done by yppedia-chart-parser