On 12/07/2012 06:17 PM, Alexey Pechnikov wrote:
"insert or replace" doesn't trigger updating of the FTS index but only
'rebuild' do it:
sqlite> .s address_fts0
CREATE VIRTUAL TABLE "address_fts0" USING
fts4(content="view_address_exists", "sys_title:hash");
sqlite> select rowid,"sys_title:hash" from address_fts0 where
"sys_title:hash" match 'e7d4683bb2';
rowid|sys_title:hash
64402|e9b4d0bcb5
What does the following:
SELECT * FROM view_address_exists WHERE rowid=64402;
return at this point?
From http://www.sqlite.org/fts3.html#section_6_2 :
"When a row is deleted from an external content FTS4 table, FTS4 needs
to retrieve the column values of the row being deleted from the content
table. This is so that FTS4 can update the full-text index entries for
each token that occurs within the deleted row to indicate that that row
has been deleted. If the content table row cannot be found, or if it
contains values inconsistent with the contents of the FTS index, the
results can be difficult to predict."
sqlite> insert or replace into "address_fts0" (rowid, "sys_title:hash")
select rowid,"sys_title:hash" from "view_address_exists" where sys_id=64402;
sqlite> select rowid,"sys_title:hash" from address_fts0 where
"sys_title:hash" match 'e7d4683bb2';
rowid|sys_title:hash
64402|e9b4d0bcb5
sqlite> INSERT INTO address_fts0(address_fts0) VALUES('rebuild');
sqlite> select count(*) from address_fts0 where "sys_title:hash" match
'e7d4683bb2';
count(*)
0
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
