Re: [sqlite] How to avoid duplicate entries in FTS table?

[Dan Kennedy]

On 07/01/2012 05:17 PM, Navaneeth.K.N wrote:

Hello,

On Sun, Jul 1, 2012 at 2:27 PM, Petite Abeillewrote:



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
)



I knew this. I was more worried about the performance. Will there be a
performance difference comparing to data stored directly on the FTS table?


No. Internally FTS just uses your table instead of one that it
creates itself.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to avoid duplicate entries in FTS table?

[Navaneeth.K.N]

Hello,

On Sun, Jul 1, 2012 at 2:27 PM, 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
> )
>
>
I knew this. I was more worried about the performance. Will there be a
performance difference comparing to data stored directly on the FTS table?

-- 
-n
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to avoid duplicate entries in FTS table?

[Petite Abeille]

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.


[1] http://www.sqlite.org/fts3.html#section_6_2_2
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to avoid duplicate entries in FTS table?

[Navaneeth.K.N]

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
appreciated.

-- 
-n
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users