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

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

[klo]

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