Subject: | fts4 with contentless table : bind values not supported |
Fts4 now supports virtual tables with "external content" (only the fulltext index is stored within sqlite).
But there is a surprising bug : inserting with a prepare/execute combination does not work, while an insert without bind values works properly. See example below.
my $sql = qq{CREATE VIRTUAL TABLE ft9 USING fts4(content="", x)};
$dbh->do($sql);
$sql = "INSERT INTO ft9(docid, x) VALUES (?, ?)";
print STDERR $sql, "\n";
my $sth = $dbh->prepare($sql);
my $docid = 13;
# this code generates an error "constraint failed"
for my $v ('U O N X G', 'C J J U B', 'N J Y G X', 'R Y D O R', 'I Y T Q O') {
print STDERR "$v\n";
$sth->execute($docid++, $v);
}
# this code works
# $dbh->do("INSERT INTO ft9(docid, x) VALUES (13, 'U O N X G')");
# $dbh->do("INSERT INTO ft9(docid, x) VALUES (14, 'C J J U B')");
# $dbh->do("INSERT INTO ft9(docid, x) VALUES (15, 'N J Y G X')");
# $dbh->do("INSERT INTO ft9(docid, x) VALUES (16, 'R Y D O R')");
print STDERR "done\n";