#!/usr/bin/perl -w
#
+# Normally run from
+# update-master-info
+#
# usage: ./db-idempotent-populate <Oceanname>
# creates or updates OCEAN-Oceanname.db
-# from master-master.txt
+# from source-info.txt
-# This is part of ypp-sc-tools, a set of third-party tools for assisting
-# players of Yohoho Puzzle Pirates.
+# This is part of the YARRG website. YARRG is a tool and website
+# 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.
+# it under the terms of the GNU Affero 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.
+# GNU Affero General Public License for more details.
#
-# You should have received a copy of the GNU General Public License
+# You should have received a copy of the GNU Affero 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
use Commods;
use CommodsDatabase;
+my $trace;
+if (@ARGV and $ARGV[0] eq '-D') {
+ $trace=1;
+ shift @ARGV;
+}
+
@ARGV==1 or die;
my ($oceanname) = @ARGV;
#---------- setup ----------
-parse_masters_ocean($oceanname);
-our $ocean= $oceans{$oceanname};
+parse_info_serverside();
db_setocean($oceanname);
+db_writer();
db_connect();
+$dbh->trace(1) if $trace;
+
#---------- schema ----------
foreach my $bs (qw(buy sell)) {
;
}
-db_doall(<<END)
- CREATE TABLE IF NOT EXISTS commods (
- commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+sub table ($$) {
+ my ($table,$fields) = @_;
+ db_doall(" CREATE TABLE IF NOT EXISTS $table (\n$fields );");
+
+ my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
+ $check->execute();
+ my %have_fields;
+ $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
+ $check->finish();
+
+ my (@have_fields, @missing_fields);
+ my $have_field_specs='';
+
+ foreach my $fspec (split /,/, $fields) {
+ next unless $fspec =~ m/\S/;
+ $fspec =~ m/^\s*(\w+)\s+(\w.*\S)\s*$/ or die "$table $fspec ?";
+ my ($f,$spec) = ($1,$2);
+ if ($have_fields{$f}) {
+ push @have_fields, $f;
+ $have_field_specs .= ",\n" if length $have_field_specs;
+ $have_field_specs .= "\t$f\t\t$spec\n";
+ } else {
+ push @missing_fields, $f;
+ }
+ }
+
+ return unless @missing_fields;
+ print " Adding missing fields to $table: @missing_fields ...\n";
+
+ my $have_fields= join ',', @have_fields;
+
+ db_doall(<<END);
+ CREATE TEMPORARY TABLE aside_$table (
+$have_field_specs );
+ INSERT INTO aside_$table SELECT $have_fields FROM $table;
+
+ DROP TABLE $table;
+ CREATE TABLE $table (
+$fields );
+
+ INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
+
+ DROP TABLE aside_$table;
+END
+ $dbh->commit;
+}
+
+table('commods', <<END);
+ commodid INTEGER PRIMARY KEY NOT NULL,
commodname TEXT UNIQUE NOT NULL,
unitmass INTEGER,
- unitvolume INTEGER
- );
+ unitvolume INTEGER,
+ ordval INTEGER,
+ commodclass TEXT,
+ inclass INTEGER
+END
+
+table('commodclasses', <<END);
+ commodclass TEXT PRIMARY KEY NOT NULL,
+ size INTEGER
+END
+
+db_doall(<<END)
CREATE TABLE IF NOT EXISTS islands (
- islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ islandid INTEGER PRIMARY KEY NOT NULL,
islandname TEXT UNIQUE NOT NULL,
archipelago TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS stalls (
- stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+ stallid INTEGER PRIMARY KEY NOT NULL,
islandid INTEGER NOT NULL,
stallname TEXT NOT NULL,
UNIQUE (islandid, stallname)
dist INTEGER NOT NULL,
PRIMARY KEY (aiid, biid)
);
+ CREATE TABLE IF NOT EXISTS routes (
+ aiid INTEGER NOT NULL,
+ biid INTEGER NOT NULL,
+ dist INTEGER NOT NULL,
+ PRIMARY KEY (aiid, biid)
+ );
+ CREATE TABLE IF NOT EXISTS vessels (
+ name TEXT NOT NULL,
+ mass INTEGER NOT NULL,
+ volume INTEGER NOT NULL,
+ shot INTEGER NOT NULL,
+ PRIMARY KEY (name)
+ );
END
;
#---------- commodity list ----------
-{
- my $sth= $dbh->prepare(<<'END')
- INSERT OR IGNORE INTO commods (commodname) VALUES (?);
-END
- ;
- foreach my $commod (sort keys %commods) {
- $sth->execute($commod);
- }
- $dbh->commit;
+sub commodsortkey ($) {
+ my ($commod) = @_;
+ my $ordval= $commods{$commod}{Ordval};
+ return sprintf "B %20d", $ordval if defined $ordval;
+ return sprintf "A %s", $commod;
}
-#---------- island list ----------
-
{
- my $sth= $dbh->prepare(<<'END')
- INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?);
+ my $insert= $dbh->prepare(<<'END')
+ INSERT OR IGNORE INTO commods
+ (unitmass,
+ unitvolume,
+ commodname)
+ VALUES (?,?,?);
END
;
- foreach my $archname (sort keys %$ocean) {
- my $arch= $ocean->{$archname};
- foreach my $islandname (sort keys %$arch) {
- $sth->execute($islandname, $archname);
+ my $setsizes= $dbh->prepare(<<'END')
+ UPDATE commods
+ SET unitmass = ?,
+ unitvolume = ?
+ WHERE commodname = ?
+END
+ ;
+ my $setordval= $dbh->prepare(<<'END')
+ UPDATE commods
+ SET ordval = ?
+ WHERE commodname = ?
+END
+ ;
+ my $setclass= $dbh->prepare(<<'END')
+ UPDATE commods
+ SET commodclass = ?
+ WHERE commodname = ?
+END
+ ;
+ my $setinclass= $dbh->prepare(<<'END')
+ UPDATE commods
+ SET inclass = ?
+ WHERE commodname = ?
+END
+ ;
+ my %incl;
+ foreach my $commod (sort {
+ commodsortkey($a) cmp commodsortkey($b)
+ } keys %commods) {
+ my $c= $commods{$commod};
+ die "no mass for $commod" unless defined $c->{Mass};
+ die "no volume for $commod" unless defined $c->{Volume};
+
+ my @qa= ($c->{Mass}, $c->{Volume}, $commod);
+ $insert->execute(@qa);
+ $setsizes->execute(@qa);
+ $setordval->execute($c->{Ordval} || 0, $commod);
+ my $cl= $c->{Class};
+ $setclass->execute($cl, $commod);
+
+ if (defined $c->{Ordval} and defined $cl) {
+ $incl{$cl}++;
+ $setinclass->execute($incl{$cl}, $commod);
}
}
+ db_doall(<<END);
+ DELETE FROM commodclasses;
+END
+ my $addclass= $dbh->prepare(<<'END')
+ INSERT INTO commodclasses
+ (commodclass, size)
+ VALUES (?,?)
+END
+ ;
+ foreach my $cl (sort keys %incl) {
+ $addclass->execute($cl, $incl{$cl});
+ }
$dbh->commit;
}
-#---------- routes ----------
-
+#---------- vessel types ----------
{
- foreach my $islandname (sort keys %{ $route_mysteries{$oceanname} }) {
- warn "$route_mysteries{$oceanname}{$islandname} routes".
- " for unknown island $islandname\n";
- }
-
- my $allroutes= $routes{$oceanname};
-
- my @propqueue= ();
-
- sub distance_set_propagate ($$$$) {
- my ($lev, $start, $upto, $start2upto) = @_;
- $allroutes->{$start}{$upto}= $start2upto;
- push @propqueue, [ $lev, $start, $upto ];
- }
-
- sub distance_propagate_now {
- my ($lev, $start, $upto) = @_;
- my $startref= $allroutes->{$start};
- my $start2upto= $startref->{$upto};
- my $uptoref= $allroutes->{$upto};
-
- for my $next (keys %$uptoref) {
- next if $next eq $upto;
- my $unext= $uptoref->{$next};
- next unless defined $unext;
- distance_update("${lev}p", $start, $next, $start2upto + $unext);
- }
- }
-
- sub distance_update ($$$$) {
- my ($lev, $x, $y, $newdist) = @_;
- distance_update_one("${lev}x",$x,$y,$newdist);
- distance_update_one("${lev}y",$y,$x,$newdist);
- }
-
- sub distance_update_one ($$$$) {
- my ($lev, $x, $y, $newdist) = @_;
- my $xref= $allroutes->{$x};
- my $currently= $xref->{$y};
- return if defined($currently) and $currently <= $newdist;
- distance_set_propagate("${lev}o",$x,$y,$newdist);
- }
-
- foreach my $xn (keys %$allroutes) {
- my $routes= $allroutes->{$xn};
- distance_set_propagate('0', $xn, $xn, 0);
- foreach my $yn (keys %$routes) {
- distance_set_propagate('0', $yn, $yn, 0);
- distance_set_propagate('X', $xn, $yn, $routes->{$yn});
- distance_set_propagate('Y', $yn, $xn, $routes->{$yn});
- }
- }
- my $ref;
- while ($ref= shift @propqueue) {
- distance_propagate_now(@$ref);
- }
-
- db_doall(<<END)
- DELETE FROM dists;
-END
- ;
- my $sth= $dbh->prepare(<<'END')
- INSERT INTO dists VALUES
- ((SELECT islandid FROM islands WHERE islandname == ?),
- (SELECT islandid FROM islands WHERE islandname == ?),
- ?);
+ my $idempotent= $dbh->prepare(<<'END')
+ INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
+ VALUES (?,?,?,?)
END
;
- foreach my $xn (keys %$allroutes) {
- my $routes= $allroutes->{$xn};
- foreach my $yn (keys %$routes) {
- $sth->execute($xn, $yn, $routes->{$yn});
- }
+ foreach my $name (sort keys %vessels) {
+ my $v= $vessels{$name};
+ my $shotdamage= $shotname2damage{$v->{Shot}};
+ die "no shot damage for shot $v->{Shot} for vessel $name"
+ unless defined $shotdamage;
+ my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
+ $idempotent->execute(@qa);
}
- $dbh->commit();
-
- # select ia.islandname, ib.islandname,dists.dist from dists, islands as ia on dists.aiid = ia.islandid, islands as ib on dists.biid = ib.islandid order by ia.islandname, ib.islandname;
+ $dbh->commit;
}
-
-__DATA__
~yarrgweb / ypp-sc-tools.main.git / blobdiff