General discussion about the SQLite embedded database.

Navaneeth.K.N | 1 Jul 2012 09:11

How to avoid duplicate entries in FTS table?

Hello,

I have a table wth the following schema.

create virtual table patterns using fts4 (pattern text, id integer)

Now, repeating a "pattern" and "id" combination is an error to me. There
should be always one "pattern" to "id" combination. If this was not a
virtual table, I'd have solved the problem by creating a primary key on
both "pattern" and "id". But this trick is not working on FTS tables.

So to ensure the unique "pattern" to "id" combinations, I have to do
something like,

insert into patterns (pattern, id) select ?1, ?2 where not exists (select 1
from patterns where pattern match ?1 and id = ?2);

This is not efficient because this does a linear table scan on patterns
table. I couldn't find a way to use multiple match on a single statement.
Something like, pattern match ?1 and id match ?2. This was failing with
error " unable to use function MATCH in the requested context".

I am also concerned about the thread safety of this approach. is there a
possibility of getting two threads/processes execute the inner select at
the same time which will yield to duplicate rows? I am using latest sqlite
and all my queries are inside a transaction which was started by executing
"BEGIN". Each process/thread will be using separate connection to the
database.

I am confused about how to solve this problem. Any help would be

(Continue reading)

headers

Petite Abeille | 1 Jul 2012 10:57

Re: How to avoid duplicate entries in FTS table?


On Jul 1, 2012, at 9:11 AM, Navaneeth.K.N wrote:

> Now, repeating a "pattern" and "id" combination is an error to me. There
> should be always one "pattern" to "id" combination. If this was not a
> virtual table, I'd have solved the problem by creating a primary key on
> both "pattern" and "id". But this trick is not working on FTS tables.

Perhaps you could try the following setup:

(1) Create a regular table to hold your unique patterns, using an unique constraint

create table foo
( 
  id integer not null constraint foo_pk primary key,
  bar text,
  constraint  foo_uk unique( bar ) 
)

(2) Create a FTS table with external content [1] to search the above

create virtual table foo_bar using fts4
(
  content = "foo",
  bar text
)

That way the regular table provide an unique constraint, and the FTS the search.

As always, YMMV.

(Continue reading)

headers

Navaneeth.K.N | 1 Jul 2012 12:17

Re: How to avoid duplicate entries in FTS table?

Hello,

On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeille <petite.abeille@...>wrote:

>
> On Jul 1, 2012, at 9:11 AM, Navaneeth.K.N wrote:
>
> > Now, repeating a "pattern" and "id" combination is an error to me. There
> > should be always one "pattern" to "id" combination. If this was not a
> > virtual table, I'd have solved the problem by creating a primary key on
> > both "pattern" and "id". But this trick is not working on FTS tables.
>
> Perhaps you could try the following setup:
>
> (1) Create a regular table to hold your unique patterns, using an unique
> constraint
>
> create table foo
> (
>   id integer not null constraint foo_pk primary key,
>   bar text,
>   constraint  foo_uk unique( bar )
> )
>
> (2) Create a FTS table with external content [1] to search the above
>
> create virtual table foo_bar using fts4
> (
>   content = "foo",
>   bar text

(Continue reading)

headers

Dan Kennedy | 2 Jul 2012 07:06

Re: How to avoid duplicate entries in FTS table?

On 07/01/2012 05:17 PM, Navaneeth.K.N wrote:
> Hello,
>
> On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeille<petite.abeille@...>wrote:
>
>>
>> On Jul 1, 2012, at 9:11 AM, Navaneeth.K.N wrote:
>>
>>> Now, repeating a "pattern" and "id" combination is an error to me. There
>>> should be always one "pattern" to "id" combination. If this was not a
>>> virtual table, I'd have solved the problem by creating a primary key on
>>> both "pattern" and "id". But this trick is not working on FTS tables.
>>
>> Perhaps you could try the following setup:
>>
>> (1) Create a regular table to hold your unique patterns, using an unique
>> constraint
>>
>> create table foo
>> (
>>    id integer not null constraint foo_pk primary key,
>>    bar text,
>>    constraint  foo_uk unique( bar )
>> )
>>
>> (2) Create a FTS table with external content [1] to search the above
>>
>> create virtual table foo_bar using fts4
>> (
>>    content = "foo",

(Continue reading)