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();
db_writer();
db_connect();
-#---------- schema ----------
+$dbh->trace(1) if $trace;
+
+
+#---------- schema update code ----------
+
+our @need_compact;
+
+sub table ($$) {
+ my ($table, $fields) = @_;
+ table_maycompact($table,undef,undef,$fields);
+}
+
+sub table_maycompact ($$$$) {
+ my ($table, $cpact_idfield, $cpact_needupdates, $fields) = @_;
+
+ #----- parse $fields -----
+
+ my @want_fields;
+ my @want_field_specs;
+ my %want_field_specs;
+
+ foreach my $fspec (split /\n/, $fields) {
+ next unless $fspec =~ m/\S/;
+ if ($fspec =~ m/^\s*\+/) {
+ push @want_field_specs, "\t".$';
+ next;
+ } elsif ($fspec =~ m/^\s*(\w+)(\s+)(\w.*\S)\s*$/) {
+ my ($f,$spaces,$rhs) = ($1,$2,$3);
+ my $spec= "\t".$f.$spaces.$rhs;
+ push @want_fields, $f;
+ push @want_field_specs, $spec;
+ $want_field_specs{$f}= $spec;
+ } else {
+ die "$table $fspec ?";
+ }
+ }
+
+ my $want_field_specs= join ",\n", @want_field_specs;
+
+ #----- ensure table exists -----
+
+ db_doall(<<END);
+ CREATE TABLE IF NOT EXISTS $table (
+$want_field_specs
+ );
+END
+ my @need_recreate;
+
+ #----- check whether we need to remove autoinc -----
+
+ if ($fields !~ /\bautoinc/i) {
+ my $autoinc= $dbh->prepare(<<END);
+ SELECT sql FROM sqlite_master
+ WHERE type='table' and name=? and tbl_name=?
+END
+ $autoinc->execute($table,$table);
+ my ($sql)= $autoinc->fetchrow_array();
+ die unless defined $sql;
+ push @need_recreate, 'remove autoinc'
+ if $sql =~ m/\bautoinc/i;
+ }
+
+ #----- check whether we need to add fields -----
+
+ my $check= $dbh->prepare("SELECT * FROM $table LIMIT 1");
+ $check->execute();
+ my %have_fields;
+ $have_fields{$_}=1 foreach @{ $check->{NAME_lc} };
+ $check->finish();
+
+ my @have_fields;
+ my @have_field_specs;
+
+ foreach my $f (@want_fields) {
+ if ($have_fields{$f}) {
+ push @have_fields, $f;
+ push @have_field_specs, $want_field_specs{$f};
+ } else {
+ push @need_recreate, "field $f";
+ }
+ }
+
+ #----- Do we need to recreate ? -----
+ return unless @need_recreate;
+ # yes:
+
+ print " Recreating $table:\n";
+ print " $_\n" foreach @need_recreate;
+
+ my $have_fields= join ',', @have_fields;
+ my $have_field_specs= join ",\n", @have_field_specs;
+
+ db_doall(<<END);
+ CREATE TEMPORARY TABLE aside_$table (
+$have_field_specs
+ );
+ INSERT INTO aside_$table SELECT $have_fields FROM $table;
+
+ DROP TABLE $table;
+ CREATE TABLE $table (
+$want_field_specs
+ );
+
+ INSERT INTO $table ($have_fields) SELECT $have_fields FROM aside_$table;
+
+ DROP TABLE aside_$table;
+END
+
+ #----- Do we need to compact ids ? -----
+ return unless
+ defined $cpact_idfield
+ and grep { m/^remove autoinc/ } @need_recreate;
+ # yes:
+
+ print " will compact\n";
+ unshift @$cpact_needupdates, [ $table ], [ $cpact_idfield ];
+
+ push @need_compact, {
+ Table => $table,
+ Id => $cpact_idfield,
+ Updates => $cpact_needupdates,
+ Fields => [ @want_fields ],
+ FieldSpecs => $want_field_specs
+ };
+}
+
+
+#---------- actual schema ----------
foreach my $bs (qw(buy sell)) {
db_doall(<<END)
;
}
-db_doall(<<END)
- CREATE TABLE IF NOT EXISTS commods (
- commodid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- commodname TEXT UNIQUE NOT NULL,
- unitmass INTEGER,
+table_maycompact('commods', 'commodid',
+ [ [ qw(buy sell) ], [ qw(commodid) ],
+ ], <<END);
+ commodid INTEGER PRIMARY KEY NOT NULL
+ commodname TEXT UNIQUE NOT NULL
+ unitmass INTEGER
unitvolume INTEGER
- );
- CREATE TABLE IF NOT EXISTS islands (
- islandid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- islandname TEXT UNIQUE NOT NULL,
+ ordval INTEGER
+ commodclass TEXT
+ inclass INTEGER
+END
+
+table_maycompact('islands', 'islandid',
+ [ [ qw(buy sell stalls uploads) ], [ qw(islandid) ],
+ [ qw(dists routes) ], [ qw(aiid biid) ],
+ ], <<END);
+ islandid INTEGER PRIMARY KEY NOT NULL
+ islandname TEXT UNIQUE NOT NULL
archipelago TEXT NOT NULL
- );
- CREATE TABLE IF NOT EXISTS stalls (
- stallid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
- islandid INTEGER NOT NULL,
- stallname TEXT NOT NULL,
- UNIQUE (islandid, stallname)
- );
+END
+
+table('stalls', <<END);
+ stallid INTEGER PRIMARY KEY NOT NULL
+ islandid INTEGER NOT NULL
+ stallname TEXT NOT NULL
+ + UNIQUE (islandid, stallname)
+END
+
+table('commodclasses', <<END);
+ commodclass TEXT PRIMARY KEY NOT NULL
+ size INTEGER
+END
+
+db_doall(<<END)
CREATE TABLE IF NOT EXISTS uploads (
islandid INTEGER PRIMARY KEY NOT NULL,
timestamp INTEGER NOT NULL,
END
;
-$dbh->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
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(<<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});
}
- $dbh->commit;
}
+
#---------- vessel types ----------
{
my $idempotent= $dbh->prepare(<<'END')
my @qa= ($name, $shotdamage, map { $v->{$_} } qw(Mass Volume));
$idempotent->execute(@qa);
}
- $dbh->commit;
+}
+
+
+#---------- put it all into effect ----------
+db_chkcommit();
+{
+ local $dbh->{AutoCommit} = 1;
+ print " Vacuuming.\n";
+ $dbh->do('VACUUM');
}
~yarrgweb / ypp-sc-tools.db-live.git / blobdiff