DBD::SQLite produces "database schema has changed" under
various conditions, including after ANALYZE-ing a database.
The can cause problems with prepare_cached and OO-RDMBS mappers
such as Class::DBI.
Sample code to produce the problem (a test case is attached)
$dbh->prepare_cached('select "hello" from a');
$dbh->do("analyze");
my $sth = $dbh->prepare_cached('select "hello" from a');
print "got ", $dbh->selectrow_array($sth), "\n";
It's also difficult to suppress the error output, I haven't
managed to do so.
A current work around is to clear the statement cache after
any schema changing operations.
$dbh->{CachedKids} = {}
The best solution is probably to use a newer version of
the sqlite prepare API:
http://sqlite.org/capi3ref.html#sqlite3_prepare_v2
This handles the schema change internally. We're currently
using the "legacy interface".
Some discussion on the Class-DBI list:
http://lists.digitalcraftsmen.net/pipermail/classdbi/2007-June/001724.html
http://www.perlmonks.org/index.pl?node_id=619312
Thanks,
Brad
| Subject: | bsb_schema_change.t |
$|++;
use strict;
use warnings;
use Test;
BEGIN { plan tests => 5 }
use DBI;
my $dbname = "foo$$";
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbname", "", "",
{ RaiseError => 1 });
ok($dbh);
$dbh->{AutoCommit} = 1;
$dbh->do("CREATE TABLE f (f1, f2, f3)");
my $sth = $dbh->prepare_cached("SELECT f.f1, f.* FROM f");
ok($sth);
$dbh->do("ANALYZE"); # invalidate prepared statement handles
my $sth2 = $dbh->prepare_cached("SELECT f.f1, f.* FROM f");
ok($sth2);
my $ret = eval { $sth2->execute(); "ok" };
ok($@, '');
ok($ret, 'ok');
$sth2->finish;
undef $sth2;
$dbh->disconnect;
END {
unlink $dbname;
}