+ die "no volume for $commod" unless defined $c->{Volume};
+
+ my $cl= $c->{Class};
+ my $clid= $commodclasses{$cl}+1;
+
+ my @valuevalues= (
+ $c->{Mass},
+ $c->{Volume},
+ $clid,
+ commodsortkey($commod),
+ $posincl{$commod},
+ $c->{Flags}
+ );
+ $insert->execute($commod, @valuevalues);
+ $update->execute(@valuevalues, $commod);
+ }
+
+ my $search= $dbh->prepare(<<END);
+ SELECT commodname,commodid FROM $table{commods};
+END
+ my %check;
+ foreach my $bs (qw(buy sell)) {
+ $check{$bs}= $dbh->prepare(<<END);
+ SELECT islandname,stallname,price,qty
+ FROM $table{$bs}
+ JOIN $table{stalls} USING (stallid)
+ JOIN $table{islands} ON ($bs.islandid = $table{islands}.islandid)
+ WHERE commodid = ? LIMIT 1
+END
+ }
+ my $delete= $dbh->prepare(<<END);
+ DELETE FROM $table{commods} WHERE commodid = ?
+END
+ $search->execute();
+ my $any=0;
+ while (my $row= $search->fetchrow_hashref()) {
+ next if defined $commods{$row->{'commodname'}};
+ print $any++ ? '; ' : " Dropping old commodities: ",
+ $row->{'commodname'};
+ foreach my $bs (qw(buy sell)) {
+ $check{$bs}->execute($row->{'commodid'});
+ my $problem= $check{$bs}->fetchrow_hashref();
+ if ($problem) {
+ print "\n";
+ die <<END
+
+FATAL ERROR
+ Removed commodity
+ $row->{'commodid'}
+ $row->{'commodname'}
+ but
+ $bs
+ $problem->{'islandname'}
+ $problem->{'stallname'}
+ $problem->{'qty'} at $problem->{'price'}
+END
+ }
+ }
+ $delete->execute($row->{'commodid'});
+ }
+ print ".\n" if $any;
+}
+
+#---------- vessel types ----------
+{
+ my $idempotent= $dbh->prepare(<<END)
+ INSERT OR REPLACE INTO $table{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);
+ }
+}
+
+#---------- transfer data back from any recreated tables ----------
+
+foreach my $tb (@need_transfer_back) {
+ my $tab= $tb->{Table};
+ print " Retransferring $tab...";
+
+ if (!eval {
+ db_doall($tb->{Sql});
+ 1;
+ }) {
+ my $emsg= $@;
+ my $w=20;
+ print STDERR "\n=== $tab retransfer failed, dumping:\n";
+ my $dumph= $dbh->prepare("SELECT * FROM aside_$tab");
+ $dumph->execute();
+ my @cols= @{ $dumph->{NAME_lc} };
+ dumptab_head(\*STDERR,$w,\@cols);
+ my $row;
+ while ($row= $dumph->fetchrow_hashref()) {
+ dumptab_row_hashref(\*STDERR,$w,\@cols,$row);
+ }
+ die "\n$emsg";
+ }
+ print "\n";
+ $table{$tab}= $tab;
+}
+
+#---------- create indices ----------
+
+foreach my $bs (qw(buy sell)) {
+ db_doall(<<END)
+ 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
+ ;
+}
+
+db_check_referential_integrity(1);
+
+#---------- compact IDs ----------
+
+sub getminmax ($$$) {
+ my ($tab,$minmax,$f) = @_;
+ my $sth= $dbh->prepare("SELECT $minmax($f) FROM $tab");
+ $sth->execute();
+ my ($val)= $sth->fetchrow_array();
+ return defined($val) ? $val : '?';
+}
+
+foreach my $cp (@need_compact) {
+ print " Compacting $cp->{Table}";
+ my $tab= $cp->{Table};
+ my $id= $cp->{Id};
+ my $tmp_field_specs= $cp->{FieldSpecs};
+ my $fields= join ',', @{$cp->{Fields}};
+ $tmp_field_specs =~ s/\bprimary key\b/UNIQUE/i or
+ die "$tab $tmp_field_specs ?";
+ db_doall(<<END);
+ CREATE TEMPORARY TABLE idlookup_$tab (
+ new_$id INTEGER PRIMARY KEY NOT NULL,
+$tmp_field_specs
+ );
+ INSERT INTO idlookup_$tab ($fields)
+ SELECT $fields
+ FROM $tab;
+END
+ my $oldmax= getminmax($tab,'max',$id);
+ my $offset= $oldmax+1;
+
+ printf(" %s %s..%d=>1..%d:",
+ $cp->{Id},
+ getminmax($tab,'min',$id),
+ $oldmax,
+ getminmax("idlookup_$tab",'max',"new_$id"));
+ my @updates= @{ $cp->{Updates} };
+ while (@updates) {
+ my $utabs= shift @updates;
+ my $ufields= shift @updates;
+ foreach my $utab (@$utabs) {
+ printf(" %s",$utab);
+ my $fh= '.';
+ foreach my $ufield (@$ufields) {
+ printf("%s%s",$fh,$ufield); $fh=',';
+ db_doall(<<END);
+ UPDATE $utab
+ SET $ufield = $offset +
+ (SELECT new_$id FROM idlookup_$tab
+ WHERE idlookup_$tab.$id = $utab.$ufield);
+ UPDATE $utab
+ SET $ufield = $ufield - $offset;
+END
+ }
+ }