Re: [sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item
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
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
[sqlite] FTS4 + INSERT OR REPLACE = Not replacing but adding item
Hey everybody, I really need some help. Just changed a table of mine to support fts4 so that I can do searches on it 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. How can I prevent this so that is working as it does without fts4. Here is my example: 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'); With fts4: Returns 3 rows instead of 2. 1|Some Text 2|Some more Text 1|Text that should be replaced to Without fts4: Returns 2 rows. 1|Text that should be replaced to 2|Some more Text Can anyone help or has a workaround. Thanks :) -- View this message in context: http://sqlite.1065341.n5.nabble.com/FTS4-INSERT-OR-REPLACE-Not-replacing-but-adding-item-tp71147.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