klo wrote:
> Just changed a table of mine to support fts4 so that I can do searches
> on it
Please note that, depending on the implementation, virtual tables are
not a full replacement for 'normal' SQLite tables and will not support
all features.
> and noticed that INSERT OR REPLACE is not working anymore as the way
> it is supposed to. Instead of replacing the item with the primary ID
> it is instead adding a new entry.
> CREATE VIRTUAL TABLE item USING fts4 (
> ID INTEGER PRIMARY KEY,
> name TEXT
> );
>
> INSERT OR REPLACE INTO item (1,'Some Text');
> INSERT OR REPLACE INTO item (2,'Some more Text');
> INSERT OR REPLACE INTO item (1,'Text that should be replaced to');
FTS tables do not support constraints like PRIMARY KEY.
However, FTS tables always have the rowid (often called docid); just do
not mention the ID in the table definition and replace it with "docid"
in your queries:
CREATE VIRTUAL TABLE item USING fts4 (
name TEXT
);
INSERT OR REPLACE INTO item(docid, name) (1,'Some Text');
INSERT OR REPLACE INTO item(docid, name) (2,'Some more Text');
INSERT OR REPLACE INTO item(docid, name) (1,'Text that should be replaced to');
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
