My question is whether or not I can rely on my use of non-unique rowids in FTS5 contentless tables to remain unenforced into the future of SQLite or am I exploiting an oversight that may be resolved in the future? Details ... I have a key-value pair SQLite table (called props) used for storing properties of objects. It has the following columns: objID, propCode, propVal objID + propCode is my primary key.
I have created a contentless FTS5 virtual table as follows: create virtual table fti using FTS5(ftiCode, ftiVal, content=''); for every record in props table a corresponding insert is made to the fti table, I insert the props.objID into fti.rowid. Since any given objID in my props table can have multiple props I wind up making multiple inserts into the fti table with a non-unique rowid (since I'm using rowid to store the objid). This works perfectly fine in all use cases so my app runs perfectly. All of the docs on rowid say that it is unique but in the FTS5 contentless table this unique requirement is not enforced. Since I have to provide all 3 of the initial fields whenever a delete is required then this also works to remove only the exact record from FTI. I view the rowid in the FTS contentless table as simply holding whatever value I send to it and it will return that value in a field named rowid but that field isn't really a rowid in the true sense. If this interpretation will remain valid into the future then my implementation is perfect. For the curious: Why am I doing this instead of something more traditional? Mostly it has to do with elimination of duplicate returns from the FTS5 search. But also contentless to save space. When I query the FT index I am interested in which objects contain given property values. My table has millions of rows so I'm sensitive to space and speed. If I insert the following rows into fti rowid, ftiCode, ftiValue: 3, 7, A3, 8, B and then I ... SELECT rowid FROM FTI where FTI MATCH A OR B … I only get one row returned with rowid of 3. This was a surprise but was exactly what I wanted. A more traditional approach to using FTI would have resulted in two rows returned both with the same ID field (object ID in my case). By exploiting the fact that the FTS5 does not enforce unique rowids I save myself the expense of using DISTINCT. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
