Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item

[klo]

Thanks Clemens,

I actually removed fts4 now and replaced it with an index on the table. This
way I can keep my old setup.

Thanks for the help



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/FTS4-INSERT-OR-REPLACE-Not-replacing-but-adding-item-tp71147p71195.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item

[Clemens Ladisch]

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 (
> IDINTEGER 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 (
nameTEXT
);

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