X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?a=blobdiff_plain;ds=sidebyside;f=yarrg%2Fdb-idempotent-populate;h=6ca83829355fc6f9504cbd41694e934bfd156c69;hb=ffb20293c74628b5d7e7c4de33142f1229f2f991;hp=b44d45dd25cc7d63c435059c521bfc0b84a36800;hpb=bd155443745759a5a9c95cd1cbce5a8f73894737;p=ypp-sc-tools.main.git diff --git a/yarrg/db-idempotent-populate b/yarrg/db-idempotent-populate index b44d45d..6ca8382 100755 --- a/yarrg/db-idempotent-populate +++ b/yarrg/db-idempotent-populate @@ -5,24 +5,24 @@ # # usage: ./db-idempotent-populate # 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 # # 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 . # # Yohoho and Puzzle Pirates are probably trademarks of Three Rings and @@ -36,19 +36,25 @@ use DBI; 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_info_serverside(); -parse_info_serverside_ocean($oceanname); -our $ocean= $oceans{$oceanname}; db_setocean($oceanname); db_writer(); db_connect(); +$dbh->trace(1) if $trace; + #---------- schema ---------- foreach my $bs (qw(buy sell)) { @@ -67,20 +73,76 @@ END ; } -db_doall(<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(<commit; +} + +table('commods', <commit; #---------- commodity list ---------- +sub commodsortkey ($) { + my ($commod) = @_; + my $ordval= $commods{$commod}{Ordval}; + return sprintf "B %20d", $ordval if defined $ordval; + return sprintf "A %s", $commod; +} + { my $insert= $dbh->prepare(<<'END') INSERT OR IGNORE INTO commods @@ -114,120 +196,80 @@ $dbh->commit; VALUES (?,?,?); END ; - my $update= $dbh->prepare(<<'END') + my $setsizes= $dbh->prepare(<<'END') UPDATE commods SET unitmass = ?, unitvolume = ? WHERE commodname = ? END ; - foreach my $commod (sort keys %commods) { + 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 colume for $commod" unless defined $c->{Volume}; + die "no volume for $commod" unless defined $c->{Volume}; + my @qa= ($c->{Mass}, $c->{Volume}, $commod); $insert->execute(@qa); - $update->execute(@qa); - } - $dbh->commit; -} + $setsizes->execute(@qa); + $setordval->execute($c->{Ordval} || 0, $commod); + my $cl= $c->{Class}; + $setclass->execute($cl, $commod); -#---------- island list ---------- - -{ - my $sth= $dbh->prepare(<<'END') - INSERT OR IGNORE INTO islands (islandname, archipelago) VALUES (?, ?); + if (defined $c->{Ordval} and defined $cl) { + $incl{$cl}++; + $setinclass->execute($incl{$cl}, $commod); + } + } + db_doall(<prepare(<<'END') + INSERT INTO commodclasses + (commodclass, size) + VALUES (?,?) END ; - foreach my $archname (sort keys %$ocean) { - my $arch= $ocean->{$archname}; - foreach my $islandname (sort keys %$arch) { - $sth->execute($islandname, $archname); - } + 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(<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__