use Commods;
use CommodsDatabase;
-my $trace;
-if (@ARGV and $ARGV[0] eq '-D') {
- $trace=1;
+my $trace=0;
+while (@ARGV and $ARGV[0] eq '-D') {
+ $trace++;
shift @ARGV;
}
#---------- schema update code ----------
our @need_compact;
+our @need_transfer_back;
+
+our %table;
sub table ($$) {
my ($table, $fields) = @_;
$check->finish();
my @have_fields;
+ my @aside_fields;
my @have_field_specs;
+ my @aside_field_specs;
foreach my $f (@want_fields) {
if ($have_fields{$f}) {
push @have_fields, $f;
push @have_field_specs, $want_field_specs{$f};
} else {
+ my $aside= $want_field_specs{$f};
+ $aside =~ s/\bUNIQUE\b//i;
+ $aside =~ s/\bNOT\s*NULL\b//i;
+ $aside =~ s/\bPRIMARY\s*KEY\b//i;
+ $aside =~ s/\s+$//;
+ push @aside_fields, $f;
+ push @aside_field_specs, $aside;
push @need_recreate, "field $f";
}
}
#----- Do we need to recreate ? -----
- return unless @need_recreate;
- # yes:
+ if (!@need_recreate) {
+ $table{$table}= $table;
+ return;
+ }
+ #----- Yes, recreate: -----
print " Recreating $table: ", join('; ',@need_recreate);
+ $table{$table}= "aside_$table";
my $have_fields= join ',', @have_fields;
+ my $aside_fields= join ',', @have_fields, @aside_fields;
my $have_field_specs= join ",\n", @have_field_specs;
+ my $aside_field_specs= join ",\n", @have_field_specs, @aside_field_specs;
db_doall(<<END);
CREATE TEMPORARY TABLE aside_$table (
-$have_field_specs
+$aside_field_specs
);
- INSERT INTO aside_$table SELECT $have_fields FROM $table;
+ INSERT INTO aside_$table ($have_fields)
+ SELECT $have_fields FROM $table;
DROP TABLE $table;
+END
+
+ push @need_transfer_back, {
+ Table => $table,
+ Sql => <<END
CREATE TABLE $table (
$want_field_specs
);
- INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
+ INSERT INTO $table ($aside_fields) SELECT $aside_fields FROM aside_$table;
DROP TABLE aside_$table;
END
-
+ };
+
#----- Do we need to compact ids ? -----
- (print("\n"), return) unless
+ (print(".\n"), return) unless
defined $cpact_idfield
and grep { m/^remove autoinc/ } @need_recreate;
# yes:
#---------- actual schema ----------
foreach my $bs (qw(buy sell)) {
- db_doall(<<END)
- CREATE TABLE IF NOT EXISTS $bs (
- commodid INTEGER NOT NULL,
- islandid INTEGER NOT NULL,
- stallid INTEGER NOT NULL,
- price INTEGER NOT NULL,
- qty INTEGER NOT NULL,
- PRIMARY KEY (commodid, islandid, stallid)
- );
- 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);
+ table($bs,<<END);
+ commodid INTEGER NOT NULL
+ islandid INTEGER NOT NULL
+ stallid INTEGER NOT NULL
+ price INTEGER NOT NULL
+ qty INTEGER NOT NULL
+ + PRIMARY KEY (commodid, islandid, stallid)
END
- ;
}
table_maycompact('commods', 'commodid',
commodname TEXT UNIQUE NOT NULL
unitmass INTEGER
unitvolume INTEGER
- ordval INTEGER
- commodclass TEXT
- inclass INTEGER
+ commodclassid INTEGER NOT NULL
+ ordval INTEGER NOT NULL
+ posinclass INTEGER NOT NULL
END
table_maycompact('islands', 'islandid',
END
table('commodclasses', <<END);
- commodclass TEXT PRIMARY KEY NOT NULL
- size INTEGER
+ commodclassid INTEGER PRIMARY KEY NOT NULL
+ commodclass TEXT UNIQUE NOT NULL
+ maxposinclass INTEGER NOT NULL
END
-db_doall(<<END)
- CREATE TABLE IF NOT EXISTS uploads (
- islandid INTEGER PRIMARY KEY NOT NULL,
- timestamp INTEGER NOT NULL,
- message TEXT NOT NULL,
- clientspec TEXT NOT NULL,
+table('uploads', <<END);
+ islandid INTEGER PRIMARY KEY NOT NULL
+ timestamp INTEGER NOT NULL
+ message TEXT NOT NULL
+ clientspec TEXT NOT NULL
serverspec TEXT NOT NULL
- );
- CREATE TABLE IF NOT EXISTS dists (
- aiid INTEGER NOT NULL,
- biid INTEGER NOT NULL,
- 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
- ;
+
+table('dists', <<END);
+ aiid INTEGER NOT NULL
+ biid INTEGER NOT NULL
+ dist INTEGER NOT NULL
+ + PRIMARY KEY (aiid, biid)
+END
+
+table('routes', <<END);
+ aiid INTEGER NOT NULL
+ biid INTEGER NOT NULL
+ dist INTEGER NOT NULL
+ + PRIMARY KEY (aiid, biid)
+END
+
+table('vessels', <<END);
+ name TEXT NOT NULL
+ mass INTEGER NOT NULL
+ volume INTEGER NOT NULL
+ shot INTEGER NOT NULL
+ + PRIMARY KEY (name)
+END
#---------- commodity list ----------
sub commodsortkey ($) {
my ($commod) = @_;
- my $ordval= $commods{$commod}{Ordval};
- return sprintf "B %20d", $ordval if defined $ordval;
- return sprintf "A %s", $commod;
+ return $commods{$commod}{Ordval} ||
+ $commods{$commod}{ClassOrdval};
+}
+sub commods_ordered () {
+ sort {
+ commodsortkey($a) <=> commodsortkey($b);
+ } keys %commods;
}
+our %posincl;
+
{
- my $insert= $dbh->prepare(<<'END');
- INSERT OR IGNORE INTO commods
- (unitmass,
- unitvolume,
- commodname)
- VALUES (?,?,?);
-END
- my $setsizes= $dbh->prepare(<<'END');
- UPDATE commods
- SET unitmass = ?,
- unitvolume = ?
- WHERE commodname = ?
-END
- my $setordval= $dbh->prepare(<<'END');
- UPDATE commods
- SET ordval = ?
- WHERE commodname = ?
+ my %classorderedcount;
+
+ foreach my $cl (keys %commodclasses) {
+ $classorderedcount{$cl}= 0;
+ }
+ foreach my $commod (commods_ordered()) {
+ my $cl= $commods{$commod}{Class};
+ die "no class for commodity $commod" unless defined $cl;
+
+ my $clid= $commodclasses{$cl};
+ die "unknown class $cl for $commod ".(join '|', sort keys %commodclasses) unless defined $clid;
+
+ if (defined $commods{$commod}{Ordval}) {
+ $posincl{$commod}= ++$classorderedcount{$cl};
+ } else {
+ $posincl{$commod}= 0;
+ }
+ }
+
+ db_doall(<<END);
+ DELETE FROM $table{commodclasses};
END
- my $setclass= $dbh->prepare(<<'END');
- UPDATE commods
- SET commodclass = ?
- WHERE commodname = ?
+ my $addclass= $dbh->prepare(<<END);
+ INSERT INTO $table{commodclasses}
+ (commodclassid, commodclass, maxposinclass)
+ VALUES (?,?,?)
END
- my $setinclass= $dbh->prepare(<<'END');
- UPDATE commods
- SET inclass = ?
+ foreach my $cl (sort keys %commodclasses) {
+ my $clname= $cl;
+ $clname =~ s/_/ /g;
+ $addclass->execute($commodclasses{$cl}+1,
+ ucfirst $clname,
+ $classorderedcount{$cl});
+ }
+}
+
+{
+ my @valuefields= qw(
+ unitmass
+ unitvolume
+ commodclassid
+ ordval
+ posinclass
+ );
+ my $insert= $dbh->prepare("
+ INSERT OR IGNORE INTO $table{commods}
+ ( commodname,
+ ".join(",
+ ", @valuefields)." )
+ VALUES (?,".join(',', map {'?'} @valuefields).")
+");
+ my $update= $dbh->prepare("
+ UPDATE $table{commods}
+ SET ".join(",
+ ", map { "$_ = ?" } @valuefields)."
WHERE commodname = ?
-END
- my %incl;
- foreach my $commod (sort {
- commodsortkey($a) cmp commodsortkey($b)
- } keys %commods) {
+");
+ foreach my $commod (commods_ordered()) {
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);
- } elsif (defined $cl) {
- $incl{$cl} += 0;
- }
- }
- 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});
+ my $clid= $commodclasses{$cl}+1;
+
+ my @valuevalues= (
+ $c->{Mass},
+ $c->{Volume},
+ $clid,
+ commodsortkey($commod),
+ $posincl{$commod}
+ );
+ $insert->execute($commod, @valuevalues);
+ $update->execute(@valuevalues, $commod);
}
- my $search= $dbh->prepare(<<'END');
- SELECT commodname,commodid FROM commods;
+ 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 $bs
- JOIN stalls USING (stallid)
- JOIN islands ON ($bs.islandid = islands.islandid)
+ 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 commods WHERE commodid = ?
+ my $delete= $dbh->prepare(<<END);
+ DELETE FROM $table{commods} WHERE commodid = ?
END
$search->execute();
my $any=0;
$delete->execute($row->{'commodid'});
}
print ".\n" if $any;
- db_check_referential_integrity();
}
-
#---------- vessel types ----------
{
- my $idempotent= $dbh->prepare(<<'END')
- INSERT OR REPLACE INTO vessels (name, shot, mass, volume)
- VALUES (?,?,?,?)
+ my $idempotent= $dbh->prepare(<<END)
+ INSERT OR REPLACE INTO $table{vessels}
+ (name, shot, mass, volume)
+ VALUES (?,?,?,?)
END
;
foreach my $name (sort keys %vessels) {
}
}
+#---------- 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();
#---------- compact IDs ----------
$tmp_field_specs =~ s/\bprimary key\b/UNIQUE/i or
die "$tab $tmp_field_specs ?";
db_doall(<<END);
- CREATE TABLE aside_$tab (
+ CREATE TEMPORARY TABLE idlookup_$tab (
new_$id INTEGER PRIMARY KEY NOT NULL,
$tmp_field_specs
);
- INSERT INTO aside_$tab ($fields)
+ INSERT INTO idlookup_$tab ($fields)
SELECT $fields
FROM $tab;
END
$cp->{Id},
getminmax($tab,'min',$id),
$oldmax,
- getminmax("aside_$tab",'max',"new_$id"));
+ getminmax("idlookup_$tab",'max',"new_$id"));
my @updates= @{ $cp->{Updates} };
while (@updates) {
my $utabs= shift @updates;
db_doall(<<END);
UPDATE $utab
SET $ufield = $offset +
- (SELECT new_$id FROM aside_$tab
- WHERE aside_$tab.$id = $utab.$ufield);
+ (SELECT new_$id FROM idlookup_$tab
+ WHERE idlookup_$tab.$id = $utab.$ufield);
UPDATE $utab
SET $ufield = $ufield - $offset;
END
~yarrgweb / ypp-sc-tools.db-test.git / blobdiff