X-Git-Url: http://www.chiark.greenend.org.uk/ucgi/~yarrgweb/git?a=blobdiff_plain;f=yarrg%2Fdb-idempotent-populate;h=5e0a8d5059d3f4ccc2d719257c8e76d7113430d0;hb=e353043ae5bf5a7df54f650c51f00d6c3b862ba9;hp=69703a6745e1bcf7b7365ac50fd021dbe9f9580c;hpb=062f9c87d921e901c1daf85a3e602e7e4c9a0cb4;p=ypp-sc-tools.web-live.git diff --git a/yarrg/db-idempotent-populate b/yarrg/db-idempotent-populate index 69703a6..5e0a8d5 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,75 @@ 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; +db_chkcommit(); #---------- 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 @@ -120,26 +195,82 @@ $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); + $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); + } elsif (defined $cl) { + $incl{$cl} += 0; + } + } + db_doall(<prepare(<<'END') + INSERT INTO commodclasses + (commodclass, size) + VALUES (?,?) +END + ; + foreach my $cl (sort keys %incl) { + $addclass->execute($cl, $incl{$cl}); } - $dbh->commit; + db_chkcommit(); } -#---------- island list ---------- -#---------- routes ---------- -# now done by yppedia-chart-parser - -__DATA__ +#---------- vessel types ---------- +{ + my $idempotent= $dbh->prepare(<<'END') + INSERT OR REPLACE INTO vessels (name, shot, mass, volume) + VALUES (?,?,?,?) +END + ; + 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); + } + db_chkcommit(); +}