schema creator in Perl

[ypp-sc-tools.web-live.git] / pctb / db-idempotent-populate

diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate
new file mode 100755 (executable)
index 0000000..1b0ef02
--- /dev/null
+++ b/pctb/db-idempotent-populate
@@ -0,0 +1,116 @@
+#!/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;
+}