+# This is part of ypp-sc-tools, a set of third-party tools for assisting
+# players of Yohoho Puzzle Pirates.
+#
+# Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+# Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
+# are used without permission. This program is not endorsed or
+# sponsored by Three Rings.
package Commods;
use IO::File;
--- /dev/null
+#!/usr/bin/perl -w
+#
+# usage: ./db-idempotent-populate <Oceanname>
+# creates or updates OCEAN-Oceanname.db
+# from master-master.txt
+
+# This is part of ypp-sc-tools, a set of third-party tools for assisting
+# players of Yohoho Puzzle Pirates.
+#
+# Copyright (C) 2009 Ian Jackson <ijackson@chiark.greenend.org.uk>
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+#
+# Yohoho and Puzzle Pirates are probably trademarks of Three Rings and
+# are used without permission. This program is not endorsed or
+# sponsored by Three Rings.
+
+use strict (qw(vars));
+
+use DBI;
+
+use Commods;
+
+@ARGV==1 or die;
+my ($ocean) = @ARGV;
+
+my $dbfn= "OCEAN-$ocean.db";
+
+our $dbh;
+
+sub dbdoall ($) {
+ foreach my $cmd (split /\;/, $_[0]) {
+ $dbh->do("$cmd;") if $cmd =~ m/\S/;
+ }
+}
+
+$dbh= DBI->connect("dbi:SQLite:$dbfn",'','',
+ { AutoCommit=>0,
+ RaiseError=>1, ShowErrorStatement=>1,
+ unicode=>1 })
+ or die "$dbfn $DBI::errstr ?";
+
+foreach my $bs (qw(buy sell)) {
+ dbdoall(<<END)
+ CREATE TABLE IF NOT EXISTS $bs (
+ commodid INTEGER NOT NULL,
+ islandid INTEGER NOT NULL,
+ stallid INTEGER NOT NULL,
+ price INTEGER,
+ qty INTEGER,
+ PRIMARY KEY (commodid, islandid, stallid)
+ );
+ CREATE INDEX IF NOT EXISTS ${bs}_by_island ON $bs (commodid, islandid, price);
+ CREATE INDEX IF NOT EXISTS ${bs}_by_price ON $bs (commodid, price, islandid);
+END
+ ;
+}
+
+dbdoall(<<END)
+ CREATE TABLE IF NOT EXISTS commodities (
+ commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ commodname TEXT UNIQUE NOT NULL,
+ unitmass INTEGER,
+ unitvolume INTEGER
+ );
+ CREATE TABLE IF NOT EXISTS islands (
+ islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ islandname TEXT UNIQUE NOT NULL,
+ archipelago TEXT NOT NULL
+ );
+ CREATE TABLE IF NOT EXISTS stalls (
+ stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ islandid INTEGER NOT NULL,
+ stallname TEXT NOT NULL,
+ UNIQUE (islandid, stallname)
+ );
+ CREATE TABLE IF NOT EXISTS uploads (
+ islandid INTEGER PRIMARY KEY NOT NULL,
+ age INTEGER NOT NULL,
+ clientspec TEXT NOT NULL,
+ serverspec TEXT NOT NULL
+ );
+ CREATE TABLE IF NOT EXISTS distances (
+ aiid INTEGER NOT NULL,
+ biid INTEGER NOT NULL,
+ distance INTEGER NOT NULL,
+ PRIMARY KEY (aiid, biid)
+ );
+END
+ ;
+
+$dbh->commit;
+
+parse_masters();
+
+{
+ my $sth= $dbh->prepare(<<'END')
+ INSERT OR IGNORE INTO commodities (commodname) values (?);
+END
+ ;
+ foreach my $commod (sort keys %commods) {
+ $sth->execute($commod);
+ }
+ $dbh->commit;
+}
+++ /dev/null
-#!/usr/bin/sqlite3 -init
-;
-
-CREATE TABLE IF NOT EXISTS buy (
- commodid INTEGER NOT NULL,
- islandid INTEGER NOT NULL,
- stallid INTEGER NOT NULL,
- price INTEGER,
- qty INTEGER,
- PRIMARY KEY (commodid, islandid, stallid)
-);
-CREATE INDEX IF NOT EXISTS buy_by_island ON buy (commodid, islandid, price);
-CREATE INDEX IF NOT EXISTS buy_by_price ON buy (commodid, price, islandid);
-
-CREATE TABLE IF NOT EXISTS sell (
- commodid INTEGER NOT NULL,
- islandid INTEGER NOT NULL,
- stallid INTEGER NOT NULL,
- price INTEGER,
- qty INTEGER,
- PRIMARY KEY (commodid, islandid, stallid)
-);
-CREATE INDEX IF NOT EXISTS sell_by_island ON sell (commodid, islandid, price);
-CREATE INDEX IF NOT EXISTS sell_by_price ON sell (commodid, price, islandid);
-
-CREATE TABLE IF NOT EXISTS commodities (
- commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- commodname TEXT UNIQUE NOT NULL,
- unitmass INTEGER,
- unitvolume INTEGER
-);
-
-CREATE TABLE IF NOT EXISTS islands (
- islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- islandname TEXT UNIQUE NOT NULL,
- archipelago TEXT NOT NULL
-);
-
-CREATE TABLE IF NOT EXISTS stalls (
- stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- islandid INTEGER NOT NULL,
- stallname TEXT NOT NULL,
- UNIQUE (islandid, stallname)
-);
-
-CREATE TABLE IF NOT EXISTS uploads (
- islandid INTEGER PRIMARY KEY NOT NULL,
- age INTEGER NOT NULL,
- clientspec TEXT NOT NULL,
- serverspec TEXT NOT NULL
-);
-
-CREATE TABLE IF NOT EXISTS distances (
- aiid INTEGER NOT NULL,
- biid INTEGER NOT NULL,
- distance INTEGER NOT NULL,
- PRIMARY KEY (aiid, biid)
-);
-
~yarrgweb / ypp-sc-tools.web-live.git / commitdiff