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
