3 # usage: ./db-idempotent-populate <Oceanname>
4 # creates or updates OCEAN-Oceanname.db
5 # from master-master.txt
7 # This is part of ypp-sc-tools, a set of third-party tools for assisting
8 # players of Yohoho Puzzle Pirates.
10 # Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
12 # This program is free software: you can redistribute it and/or modify
13 # it under the terms of the GNU General Public License as published by
14 # the Free Software Foundation, either version 3 of the License, or
15 # (at your option) any later version.
17 # This program is distributed in the hope that it will be useful,
18 # but WITHOUT ANY WARRANTY; without even the implied warranty of
19 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
20 # GNU General Public License for more details.
22 # You should have received a copy of the GNU General Public License
23 # along with this program. If not, see <http://www.gnu.org/licenses/>.
25 # Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
26 # are used without permission. This program is not endorsed or
27 # sponsored by Three Rings.
29 use strict (qw(vars));
38 my $dbfn= "OCEAN-$ocean.db";
43 foreach my $cmd (split /\;/, $_[0]) {
44 $dbh->do("$cmd;") if $cmd =~ m/\S/;
48 $dbh= DBI->connect("dbi:SQLite:$dbfn",'','',
50 RaiseError=>1, ShowErrorStatement=>1,
52 or die "$dbfn $DBI::errstr ?";
54 foreach my $bs (qw(buy sell)) {
56 CREATE TABLE IF NOT EXISTS $bs (
57 commodid INTEGER NOT NULL,
58 islandid INTEGER NOT NULL,
59 stallid INTEGER NOT NULL,
62 PRIMARY KEY (commodid, islandid, stallid)
64 CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
65 CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
71 CREATE TABLE IF NOT EXISTS commodities (
72 commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
73 commodname TEXT UNIQUE NOT NULL,
77 CREATE TABLE IF NOT EXISTS islands (
78 islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
79 islandname TEXT UNIQUE NOT NULL,
80 archipelago TEXT NOT NULL
82 CREATE TABLE IF NOT EXISTS stalls (
83 stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
84 islandid INTEGER NOT NULL,
85 stallname TEXT NOT NULL,
86 UNIQUE (islandid, stallname)
88 CREATE TABLE IF NOT EXISTS uploads (
89 islandid INTEGER PRIMARY KEY NOT NULL,
91 clientspec TEXT NOT NULL,
92 serverspec TEXT NOT NULL
94 CREATE TABLE IF NOT EXISTS distances (
95 aiid INTEGER NOT NULL,
96 biid INTEGER NOT NULL,
97 distance INTEGER NOT NULL,
98 PRIMARY KEY (aiid, biid)
108 my $sth= $dbh->prepare(<<'END')
109 INSERT OR IGNORE INTO commodities (commodname) values (?);
112 foreach my $commod (sort keys %commods) {
113 $sth->execute($commod);