[sqlite] Will ROWID be required to be unique in contentless Full text search

[paul tracy]

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

Re: [sqlite] Options used for precompiled dll for Windows?

[sky5walk]

Ok, "making" some headway. :)

; COMPILE SOURCE AS DLL:
;   Use MS Visual Studio Command Prompt with the appropriate target: x86 or
x64.
;   Ex. shortcut: x64 Native Tools Command Prompt for VS 2017
;   %comspec% /k "C:\Program Files (x86)\Microsoft Visual
Studio\2017\Community\VC\Auxiliary\Build\vcvars64.bat
;
;   Then create and execute a bat file with the following: (enable/disable
desired options)
;   cl sqlite3.c -DSQLITE_THREADSAFE -DSQLITE_API=__declspec(dllexport)
-link -dll -out:sqlite3.dll

SQLite Version= 3.28.0
SQLite Lib= 2019-04-16 19:49:53
884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
SQLite ThreadSafe = 1
SQLite Options:
COMPILER=msvc-1915
THREADSAFE=1
This approach is super fast and light, but it does not populate the version
fields within the dll? I need that for traceability.

So, needs more work or try compiling with nmake cmd and the edited
makefile.msc(which is huge). Maybe someone has a clue?

Thanks for SQLite!

On Fri, Apr 19, 2019 at 7:26 PM  wrote:

> I was afraid you'd say that.
> I wanted to edit the Windows x64 make file to reduce a lot of the options.
>
> SQLite Version= 3.28.0
> SQLite Lib= 2019-04-16 19:49:53
> 884b4b7e502b4e991677b53971277adfaf0a04a284f8e483e2553d0f83156b50
>
> Anyway, I iterated this sql query:
> "SELECT sqlite_compileoption_get(0-n until empty return); "
> SQLite Options Returned:
> COMPILER=msvc-1500
> ENABLE_COLUMN_METADATA
> ENABLE_DBSTAT_VTAB
> ENABLE_FTS3
> ENABLE_FTS4
> ENABLE_FTS5
> ENABLE_GEOPOLY
> ENABLE_JSON1
> ENABLE_RTREE
> ENABLE_STMTVTAB
> MAX_TRIGGER_DEPTH=100
> TEMP_STORE=1
> THREADSAFE=1
>
> Any chance to publish the make file used for the releases?
> I found this thousand liner in the amalgamated sqlite-autoconf-328
> folder.
> ~\sqlite-autoconf-328\Makefile.msc
> But it makes no mention of ENABLE_FTS5?
> Most of the other options are in there.
>
> Any examples to drop an option?
> Ex. OPT_FEATURE_FLAGS = $(OPT_FEATURE_FLAGS) -DSQLITE_ENABLE_RTREE=0 <--0|1
>
> Also, I read the mail list that some additional flag is required to export
> the functions in the dll?
>
> Thanks for SQLite!
>
> On Fri, Apr 19, 2019 at 9:36 AM Warren Young  wrote:
>
>> On Apr 18, 2019, at 2:37 PM, sky5w...@gmail.com wrote:
>> >
>> > Searched a while...what was the makefile or the options used to create
>> the
>> > latest precompiled sqlite3.dll?
>> > https://www.sqlite.org/download.html
>> > --> sqlite-dll-win64-x64-328.zip
>>
>> Execute “PRAGMA compile_options;” and it’ll tell you.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users