From: Ian Jackson Date: Sun, 26 Jul 2009 18:19:04 +0000 (+0100) Subject: schema creator in Perl X-Git-Tag: 3.0~34 X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?p=ypp-sc-tools.web-live.git;a=commitdiff_plain;h=5c448a7c813853cb34ce8006bb6a544fbb15b2be schema creator in Perl --- diff --git a/.gitignore b/.gitignore index 12c53be..7bfbf16 100644 --- a/.gitignore +++ b/.gitignore @@ -11,3 +11,4 @@ pctb/u.* pctb/ypp-commodities pctb/_*.* +pctb/OCEAN-*.db diff --git a/pctb/Commods.pm b/pctb/Commods.pm index 7d9a0be..712847d 100644 --- a/pctb/Commods.pm +++ b/pctb/Commods.pm @@ -1,3 +1,24 @@ +# 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 +# +# 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 . +# +# 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; diff --git a/pctb/db-idempotent-populate b/pctb/db-idempotent-populate new file mode 100755 index 0000000..1b0ef02 --- /dev/null +++ b/pctb/db-idempotent-populate @@ -0,0 +1,116 @@ +#!/usr/bin/perl -w +# +# usage: ./db-idempotent-populate +# 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 +# +# 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 . +# +# 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(<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; +} diff --git a/pctb/db-schema.sqlite b/pctb/db-schema.sqlite deleted file mode 100755 index 8a42bb5..0000000 --- a/pctb/db-schema.sqlite +++ /dev/null @@ -1,59 +0,0 @@ -#!/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) -); -