Re: [sqlite] BUG:"insert or replace" doesn't works for External Content FTS4 Tables
On 12/07/2012 11:17 PM, Alexey Pechnikov wrote: What does the following: SELECT * FROM view_address_exists WHERE rowid=64402; sqlite>SELECT "sys_title:hash" FROM view_address_exists WHERE rowid=64402; "sys_title:hash" e9b4d0bcb5 But what does "SELECT * FROM ..." return? According to table view_address_exist, does row 64402 actually contain the token 'e7d4683bb2'? If not, FTS has no way to delete the entry that maps from token 'e7d4683bb2' -> docid=64402. About documented "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.". I think that "insert or replace" is broken because we can't update external table in middle of the "insert or replace" command execution (after the old record complete deletion from FTS table and before the new record insertion into FTS table). May be is more reasonable to make the content of FTS table synchronized with the content of external table by FTS extension internally? It would be better that way. But the virtual table interface doesn't provide any way to do that. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG:"insert or replace" doesn't works for External Content FTS4 Tables
> What does the following: > > SELECT * FROM view_address_exists WHERE rowid=64402; > sqlite> SELECT "sys_title:hash" FROM view_address_exists WHERE rowid=64402; "sys_title:hash" e9b4d0bcb5 About documented "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.". I think that "insert or replace" is broken because we can't update external table in middle of the "insert or replace" command execution (after the old record complete deletion from FTS table and before the new record insertion into FTS table). May be is more reasonable to make the content of FTS table synchronized with the content of external table by FTS extension internally? -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG:"insert or replace" doesn't works for External Content FTS4 Tables
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
[sqlite] BUG:"insert or replace" doesn't works for External Content FTS4 Tables
"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
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
--
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
