+$dbh->trace(1) if $trace;
+
+
+#---------- schema update code ----------
+
+our @need_compact;
+our @need_transfer_back;
+
+our %table;
+
+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 @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 ? -----
+ 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 (
+$aside_field_specs
+ );
+ 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 ($aside_fields) SELECT $aside_fields FROM aside_$table;
+
+ DROP TABLE aside_$table;
+END
+ };
+
+ #----- Do we need to compact ids ? -----
+ (print(".\n"), 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 ----------