Hi!
Haven't been able to find a solution reading sqlite-users mailing list, http://www.sqlite.org/, nor googling at large. I have not received responses to http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2011-October/033600.html so I am trying my luck here. I need to insert rows with a unique column combination into a fts3 virtual table. http://www.sqlite.org/fts3.html states: "This is pure syntactic sugar, the supplied typenames are not used by FTS or the SQLite core for any purpose. The same applies to any constraints specified along with an FTS column name - they are parsed but not used or recorded by the system in any way." Given CREATE VIRTUAL TABLE fts3_table USING FTS3 (a,b,c,UNIQUE(a,b,c)); to INSERT OR IGNORE INTO fts3_table VALUES(a, b, c); would never IGNORE based on a uniqueness constraint, right? This is what my testing shows. The row gets inserted no matter whether it already exists in the fts3_table. I found the following solution to work (based on some google results), but performance is miserable for 1000 such inserts with varying values, even though performed inside a single transaction: INSERT INTO fts3_table (a,b,c) SELECT 'an A','a B','a C' WHERE NOT EXISTS (SELECT DISTINCT a,b,c FROM fts3_table WHERE a='an A' AND b='a B' AND c='a C'); Is there a more efficient way to insert unique rows (with constraint UNIQUE(a,b,c)) into an fts3 virtual table? I am using the SQLite 3.5.9 that comes with android-2.1-update1 on my HTC Hero. Thanks! -- You received this message because you are subscribed to the Google Groups "Android Developers" group. To post to this group, send email to android-developers@googlegroups.com To unsubscribe from this group, send email to android-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/android-developers?hl=en
