@ARGV==1 or die;
my ($oceanname) = @ARGV;
+$|=1;
#---------- setup ----------
return unless @need_recreate;
# yes:
- print " Recreating $table:\n";
- print " $_\n" foreach @need_recreate;
+ print " Recreating $table: ", join('; ',@need_recreate);
my $have_fields= join ',', @have_fields;
my $have_field_specs= join ",\n", @have_field_specs;
END
#----- Do we need to compact ids ? -----
- return unless
+ (print("\n"), return) unless
defined $cpact_idfield
and grep { m/^remove autoinc/ } @need_recreate;
# yes:
- print " will compact\n";
+ print "; will compact.\n";
unshift @$cpact_needupdates, [ $table ], [ $cpact_idfield ];
push @need_compact, {
}
{
- my $insert= $dbh->prepare(<<'END')
+ my $insert= $dbh->prepare(<<'END');
INSERT OR IGNORE INTO commods
(unitmass,
unitvolume,
commodname)
VALUES (?,?,?);
END
- ;
- my $setsizes= $dbh->prepare(<<'END')
+ my $setsizes= $dbh->prepare(<<'END');
UPDATE commods
SET unitmass = ?,
unitvolume = ?
WHERE commodname = ?
END
- ;
- my $setordval= $dbh->prepare(<<'END')
+ my $setordval= $dbh->prepare(<<'END');
UPDATE commods
SET ordval = ?
WHERE commodname = ?
END
- ;
- my $setclass= $dbh->prepare(<<'END')
+ my $setclass= $dbh->prepare(<<'END');
UPDATE commods
SET commodclass = ?
WHERE commodname = ?
END
- ;
- my $setinclass= $dbh->prepare(<<'END')
+ my $setinclass= $dbh->prepare(<<'END');
UPDATE commods
SET inclass = ?
WHERE commodname = ?
END
- ;
my %incl;
foreach my $commod (sort {
commodsortkey($a) cmp commodsortkey($b)
db_doall(<<END);
DELETE FROM commodclasses;
END
- my $addclass= $dbh->prepare(<<'END')
+ my $addclass= $dbh->prepare(<<'END');
INSERT INTO commodclasses
(commodclass, size)
VALUES (?,?)
END
- ;
foreach my $cl (sort keys %incl) {
$addclass->execute($cl, $incl{$cl});
}
+
+ my $search= $dbh->prepare(<<'END');
+ SELECT commodname,commodid FROM commods;
+END
+ my %check;
+ foreach my $bs (qw(buy sell)) {
+ $check{$bs}= $dbh->prepare(<<END);
+ SELECT islandname,stallname,price,qty
+ FROM $bs
+ JOIN stalls USING (stallid)
+ JOIN islands ON ($bs.islandid = islands.islandid)
+ WHERE commodid = ? LIMIT 1
+END
+ }
+ my $delete= $dbh->prepare(<<'END');
+ DELETE FROM 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;
+ db_check_referential_integrity();
}
}
+#---------- 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 TABLE aside_$tab (
+ new_$id INTEGER PRIMARY KEY NOT NULL,
+$tmp_field_specs
+ );
+ INSERT INTO aside_$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("aside_$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 aside_$tab
+ WHERE aside_$tab.$id = $utab.$ufield);
+ UPDATE $utab
+ SET $ufield = $ufield - $offset;
+END
+ }
+ }
+ }
+ print "\n";
+}
+
#---------- put it all into effect ----------
+
db_chkcommit();
+
{
local $dbh->{AutoCommit} = 1;
- print " Vacuuming.\n";
$dbh->do('VACUUM');
}