[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
On 11/06/2015 12:36 PM, chromedout64 at yahoo.com wrote: > I noticed that this update is not included in the official 3.9.2 release. How > is it determined when an update such as this one gets included in a release? > Thanks for any info. Most likely as part of 3.10.0. You can always download the SQLite trunk, either via fossil or via a link like: http://www.sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk Then run [./configure && make amalgamation-tarball] to build an autoconf package. Regards, Dan. > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
Great, that's exactly what I need, I'll give it a try.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
I noticed that this update is not included in the official 3.9.2 release. How is it determined when an update such as this one gets included in a release? Thanks for any info.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
On 10/27/2015 05:06 PM, chromedout64 at yahoo.com wrote:
> Using the example from the documentation at https://www.sqlite.org/fts5.html
> I thought this worked in a previous version.
>
> CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
> CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl',
> content_rowid='a');
> CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
> CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b,
> old.c);
> END;
> CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
>INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b,
> old.c);
>INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
> END;
>
> REPLACE INTO tbl VALUES(1,'foo','bar');
I think this may have worked with an older version of FTS5 that did not
support REPLACE.
The trouble is that because the top-level statement uses OR REPLACE
conflict handling, so do the INSERT statements fired by the triggers.
And OR REPLACE conflict handling was broken for external content tables.
Fts5 is now updated on trunk so that OR REPLACE is ignored for
contentless and external content tables:
http://sqlite.org/src/info/a85c2a4758c27e8d
So this sort of thing should work again.
Dan.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
Thanks for fixing it, Dan. Do you know when this will be rolled into an official or preliminary autoconf file? Thanks again.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
This is on v3.9.1. I'm not sure exactly when it stopped working. I was using the external content functionality with triggers to keep the virtual table updated as part of a larger program, but found that the referenced example from the docs exhibits the same issue. INSERT's work fine, but REPLACE gives this error now.
[sqlite] FTS5 External Content Table - REPLACE - SQL logic error or missing database
Using the example from the documentation at https://www.sqlite.org/fts5.html
I thought this worked in a previous version.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a');
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
? INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
? INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b,
old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
? INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b,
old.c);
? INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
REPLACE INTO tbl VALUES(1,'foo','bar');
Error: near line 14: SQL logic error or missing database
