[CR] SQLite Library

[ShaunR 834]

19 minutes ago, Stobber said:

How hard might it be to add support for this feature? https://sqlite.org/c3ref/update_hook.html

I'm really, really interested in using it to avoid polling for changes to a db that gets written by other processes.

That's not the function you are looking for. This is.

[Stobber 38]

20 minutes ago, ShaunR said:

That's not the function you are looking for. This is.

No it isn't; I use this library exclusively on Linux devices. Also, I think a change notification on the entire db file will be useless if I only want to be notified when a certain table changes.

[Rolf Kalbermatter 472]

12 minutes ago, Stobber said:

No it isn't; I use this library exclusively on Linux devices. Also, I think a change notification on the entire db file will be useless if I only want to be notified when a certain table changes.

Either way to make this useful you would want to have this translated into a user event and that will require the creation of an external shared library which can install that callback which then is translated into a user event through the LabVIEW PostLVUserEvent() C manager API function. As the current interface goes to great lengths to avoid having to create an intermediate shared library this is not a trivial addition to the library but a very significant change, especially since every supported platform will require the creation of its own shared library (Windows 32 and 64 bit, Linux 32 and 64 bit, and MacOSX 32 and 64 bit makes already 6 different shared libraries not to mention the extra at least 4 cRIO flavours).

And I might be misreading the documentation for that function but it does not call the callback for a specific table but for any row update, insert or delete in any rowid table for the current database connection. But not for tables without rowid.

Edited May 27, 2016 by rolfk

[Stobber 38]

46 minutes ago, rolfk said:

...every supported platform will require the creation of its own shared library (Windows 32 and 64 bit, Linux 32 and 64 bit, and MacOSX 32 and 64 bit makes already 6 different shared libraries not to mention the extra at least 4 cRIO flavours).

Yeah, that's what I expected. Sounds like something I'd have to create for our use. I could post it as a bolt-on to the existing API. Might be able to inherit from JDPowell's class to extend it with this feature...

[ShaunR 834]

1 hour ago, Stobber said:

No it isn't; I use this library exclusively on Linux devices. Also, I think a change notification on the entire db file will be useless if I only want to be notified when a certain table changes.

You misunderstand. It won't work cross process because it is not a system-wide hook. It works great in LabVIEW on all platforms but won't tell you when another application has changed it.- not even on Linux Funnily enough, it does work the way you think it does on VxWorks because there is only one process.

Edited May 27, 2016 by ShaunR

[Stobber 38]

1 hour ago, ShaunR said:

It won't work cross process because it is not a system-wide hook. It works great in LabVIEW on all platforms but won't tell you when another application has changed it.

I don't see anything in the documentation that says it only invokes the hook when changes are made on the same Connection...so how do you know this is true? Why wouldn't an UPSERT made from my Python app's connection invoke the hook that was registered on my LV app's connection?

[ShaunR 834]

30 minutes ago, Stobber said:

I don't see anything in the documentation that says it only invokes the hook when changes are made on the same Connection...so how do you know this is true? Why wouldn't an UPSERT made from my Python app's connection invoke the hook that was registered on my LV app's connection?

SQLite doesn't support UPSERT. 

[Stobber 38]

19 minutes ago, ShaunR said:

SQLite doesn't support UPSERT. 

I know; I'm using that as a succinct way to say "when a row in a table changes in an interesting way". Are you being pedantic and ignoring my actual points for fun?

[Rolf Kalbermatter 472]

3 hours ago, Stobber said:

I don't see anything in the documentation that says it only invokes the hook when changes are made on the same Connection...so how do you know this is true? Why wouldn't an UPSERT made from my Python app's connection invoke the hook that was registered on my LV app's connection?

See here: http://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg53058.html

It comes from the fact that SQLLite is a file based database, not a server based one. The SQL Lite shared library is the entire SQL Lite database engine, but as DLL it gets loaded into each process seperately and does not share any state from another SQL Lite shared library engine in another process. It does use file range locking (when enabled in the build) in order to maintain some kind of consistency even if two processes happen to modify the data concurrently. And it apparently does at least under Windows (when enabled in the build) use file notifications to get informed about changes from other processes but in order to detect what has changed it then has to read in all the data again and update its internal management information so I'm not sure why it would work under Windows like Shaun claims.

Basically the fact that SQL Lite is a file based database more or less makes what you want pretty impossible.

The solution most people have come up with is to add an extra table which stores records about changes to tables and then query that regularly, possible with a file change notification callback mechanisme to avoid to much polling.

Edited May 27, 2016 by rolfk

[Stobber 38]

Thanks, Rolf. That email thread is 6 years old, so I sent a message to Ed Scherer to see if he ever found a solution. Who knows, he might be able to help.

[ShaunR 834]

9 hours ago, rolfk said:

so I'm not sure why it would work under Windows like Shaun claims.

I don't claim it works under Windows. Thats why I suggested the file change notification for Windows..

Edited May 28, 2016 by ShaunR

[Rolf Kalbermatter 472]

7 hours ago, ShaunR said:

I don't claim it works under Windows. Thats why I suggested the file change notification for Windows..

Well I misunderstood you there then! But for Linux you have inotify() or the older dnotify() to do something similar than with FindFirstChangeNotification() under Windows. inotify() is present since around kernel 2.6.13 and glibc 2.4 and working from glibc 2.5, so nowadays there should be no reason to have to use the inferior dnotify() functionality.

Edited May 28, 2016 by rolfk

[drjdpowell 409]

Sorry guys, I was on holiday when Stobber asked his question.  Rolf has answered better than I could.   I have only used polling to identify changes.   I tend to have timestamped data with the timestamp as primary key, and getting MAX() of the primary key is very fast.   One could also use triggers to increment some field whenever something of interest changes, and then poll that field.

[hooovahh 717]

I hit like on the first post, and subscribed to notification, but that just didn't seem like "Thank You" enough.  This is an awesome set of function, but what compliments it in my opinion is the common templates, example finder examples, and the Youtube video.  Having worked with databases a little these really helped fill in some of the gaps I had about how to do things.  Thanks again.

[drjdpowell 409]

Thanks.   Is there anything you could suggest to improve things (new examples, for example)?

[hooovahh 717]

I can't think of something right now, but if I do I will be sure to post.  Thanks again.

[KFadgen 4]

Is there a reason that the LVTools network always seems to be behind a version when compared to what we can get via LAVA?  At the moment VIPM lists 1.6.2.58 as the latest for me.  Refreshing the feed doesn't bring up the latest version.

[drjdpowell 409]

Because I forget to submit to NI.   Updating LAVA-CR is quick and instant, and I intend to send to NI later when any bugs are discovered, but then it drops of my radar.  

[shoneill 98]

First of all, great toolkit.  Thanks.

I am re-visiting some benchmarking I had started some time ago and have some observed some unexpected behaviour.  After creating a file with an Index, I repeatedly overwrite the existing data with random DBL values (of which there are 500k Rows x 24 Columns).  I see an Update rate of approximately 70k Updates per second (Wrapping all 500kx24 Updates in a single transaction).  All good so far.  Actually, it's 70k Updates where each Update writes 24 distinct Columns so it's actually 1.68M Value updates per second.  But 70k Rows.

I open the file once, Fill it with zeros (Total number of data points is known from the start - 500kx24 plue 3 Columns for 3D Indexing X,Y and Z), create an Index on the most important columns (X,Y,Z which are NOT overwritten later), prepare a statement for Updating the values and then re-use this statement in a loop for updating.  I put all 500k x 24 Updates in a single transaction to maximise speed.  Only after the benchmark VI is finished (after looping N times) do I finish the Statement and the SQLite file.

All good so far, but now comes the weird part.  When I tried investigating the effect of parallel read access I saw no decrease in UPDATE performance.  Quite the opposite.  When executing a QUERY from a different process (using a different SQLite DLL) whilst writing, the UPDATE speed seemed to INCREASE.  The speed of updates went from 70k to approximately 124k per second.  On a side note, this is also the speed increase seen when executing the UPDATE with "Synchronous=OFF".

Has anyone seen something similar?  Can I somehow use this fact to me advantage to generally speed up UPDATE commands?  Is the synchronous mode somehow being negated in this situation?  The whole thing feels weird to me and I'm sure I'm making a colossal mistake somewhere.  I am writing the data in LV, reading using the SQLite DB Browser, so different DLLs and different application spaces are involved.  I won't be able to control which SQLite DLL the users have for reading, so this is pretty much real-world for us.

File system is NTFS, OS is Win7 64-bit.  SQLIte DB Broswer is Version 3.9.1 (64-bit).  It uses the V3.11 SQLite DLL as far as I know.  I'm using LV 2015 SP1 (32-bit).  I've observed this behaviour with both V3.10.0 SQLite DLL and the newest V3.15.2 SQLite DLL.  Oh, and I'm writing to a HDD, not an SSD.

My PRAGMAS for the UPDATE connection (some are leftovers from investigative benchmarking i.e. threads):

PRAGMA threads=0;
PRAGMA temp_store=MEMORY;
PRAGMA cache_size=-32000;
PRAGMA locking_mode=NORMAL;
PRAGMA synchronous=NORMAL;
PRAGMA journal_mode=WAL;

The results are confusing me a little. 

[drjdpowell 409]

I would hazard a guess that it's something to do with the Write-Ahead-Log (WAL).  Perhaps an UPDATE is done on the main file normally, but if the main file is locked by a reader the UPDATE is recorded in the WAL file (which might be faster).   Then the completion of the UPDATE happens at the next WAL checkpoint.  I don't know, but regardless, WAL checkpointing makes benchmarking trickier; you might want to manually checkpoint by calling PRAGMA wal_checkpoint(FULL) as part of your performance test (just after the COMMIT) to make sure you are measuring the full cost of your UPDATEs.

[shoneill 98]

I just came to the same conclusion while reading the documentation on WAL mode.  Go figure.

It seems the WAL file does not operate in synchronous mode at all.  I wonder if that affects fobustness at all?

[drjdpowell 409]

17 minutes ago, shoneill said:

IIt seems the WAL file does not operate in synchronous mode at all.  I wonder if that affects fobustness at all?

You mean with PRAGMA synchronous=OFF?   With "NORMAL" that you are using I think there is a chance that the WAL will be corrupted on power loss, but that just means the loss of the latest few transactions.   On the default "FULL" setting each transaction to the WAL is synced.  

[shoneill 98]

Does the sync refer to updates to the WAL or to the DB file itself?

[shoneill 98]

Never mind, I just need to learn to read.

Quote

In WAL mode when synchronous is NORMAL (1), the WAL file is synchronized before each checkpoint and the database file is synchronized after each completed checkpoint and the WAL file header is synchronized when a WAL file begins to be reused after a checkpoint, but no sync operations occur during most transactions. With synchronous=FULL in WAL mode, an additional sync operation of the WAL file happens after each transaction commit. The extra WAL sync following each transaction help ensure that transactions are durable across a power loss, but they do not aid in preserving consistency. If durability is not a concern, then synchronous=NORMAL is normally all one needs in WAL mode.

 

Edited December 16, 2016 by shoneill
clarification

[Stobber 38]

djpowell, could you add support for extended error codes to this API? I'm struggling with a SQLITE_CANTOPEN error on a cRIO that never used to appear, and I don't have enough context to know what the heck is wrong.

Also, could you modify SQLite.lvlib:Format Error.vi to yield the full call chain? Figuring out where an error came from without it is sometimes really hard. If you don't want to stringify the call chain all the time, maybe make it an option I can toggle on Open?