Jump to content

Recommended Posts

  • Replies 211
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

View File SQLite Library Introductory video now available on YouTube: Intro to SQLite in LabVIEW   SQLite3 is a very light-weight, server-less, database-in-a

Powerpoint slides from a presentation I gave at the European CLA Summit: CLA Summit 2014 SQLite.pptx

James -   I just used v1.0.3.16 in a small benchtop product test application, and I think it's fantastic. The API makes sense and is flexible enough without getting cumbersome for simple tasks. Grea

Posted Images

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.

Link to post
Share on other sites
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 by rolfk
Link to post
Share on other sites
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...

Link to post
Share on other sites
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 by ShaunR
Link to post
Share on other sites
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?

Link to post
Share on other sites
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. 

Link to post
Share on other sites
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?

Link to post
Share on other sites
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 by rolfk
  • Like 1
Link to post
Share on other sites
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 by ShaunR
Link to post
Share on other sites
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 by rolfk
Link to post
Share on other sites
  • 2 weeks later...

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.

Link to post
Share on other sites
  • 3 weeks later...

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.

Link to post
Share on other sites
  • 2 months later...
  • 2 months later...

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. 

Link to post
Share on other sites

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.

Link to post
Share on other sites
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.  

Link to post
Share on other sites

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 by shoneill
clarification
Link to post
Share on other sites

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?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Similar Content

    • By drjdpowell
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
    • By Thang Nguyen
      Hi,
      I would like to know if I can write stream data into two different tables with SQLite at the same time or not? Currently my system has one camera. I store the capture images and particle measurement in one table. I have just add one more camera to the system and would like to do the same thing and write to a second table in a parallel process.  I wonder if this is possible or not.
      I use SQLite library.
      Thanks in advance.
       
    • By McQuillan
      Hi Everyone,
      I (re)watched James Powell's talk at GDevCon#2 about Application Design Around SQLite. I really like this idea as I have an application with lots of data (from serial devices and software configuration) that's all needed in several areas of the application (and external applications) and his talk was a 'light-bulb' moment where I thought I could have a centralized SQLite database that all the modules could access to select / update data.
      He said the database could be the 'model' in the model-view-controller design pattern because the database is very fast. So you can collect data in one actor and publish it directly to the DB, and have another actor read the data directly from the DB, with a benefit of having another application being able to view the data.
      Link to James' talk: https://www.youtube.com/watch?v=i4_l-UuWtPY&t=1241s)
       
      I created a basic proof of concept which launches N-processes to generate-data (publish to database) and others to act as a UI (read data from database and update configuration settings in the DB (like set-point)). However after launching a couple of processes I ran into  'Database is locked (error 5) ', and I realized 2 things, SQLite databases aren't magically able to have n-concurrent readers/writers , and I'm not using them right...(I hope).
      I've created a schematic (attached) to show what I did in the PoC (that was getting 'Database is locked (error 5)' errors).
      I'm a solo-developer (and SQLite first-timer*) and would really appreciate it if someone could look over the schematic and give me guidance on how it should be done. There's a lot more to the actual application, but I think once I understand the limitations of the DB I'll be able to work with it.
      *I've done SQL training courses.
      In the actual application, the UI and business logic are on two completely separate branches (I only connected them to a single actor for the PoC) 
      Some general questions / thoughts I had:
      Is the SQLite based application design something worth perusing / is it a sensible design choice? Instead of creating lots of tables (when I launch the actors) should I instead make separate databases? - to reduce the number of requests per DB? (I shouldn't think so... but worth asking) When generating data, I'm using UPDATE to change a single row in a table (current value), I'm then reading that single row in other areas of code. (Then if logging is needed, I create a trigger to copy the data to a separate table) Would it be better if I INSERT data and have the other modules read the max RowId for the current value and periodically delete rows? The more clones I had, the slower the UI seemed to update (should have been 10 times/second, but reduced to updating every 3 seconds). I was under the impression that you can do thousands of transactions per second, so I think I'm querying the DB inefficiently. The two main reasons why I like the database approach are:
      External applications will need to 'tap-into' the data, if they could get to it via an SQL query - that would be ideal. Data-logging is a big part of the application. Any advice you can give would be much appreciated.
      Cheers,
      Tom
      (I'm using quite a few reuse libraries so I can't easily share the code, however, if it would be beneficial, I could re-work the PoC to just use 'Core-LabVIEW' and James Powell's SQLite API)

    • By Munch
      Good Afternoon,
      I have been having an issue trying to resolve an error all afternoon, and hope that you might be able to help.
      I am trying to execute the follow SQL "
      SELECT m.name as tableName,
             p.name as columnName
      FROM sqlite_master m
      left outer join pragma_table_info((m.name)) p
           on m.name <> p.name
           where columnName = 'UploadedFlag'
      order by tableName, columnName
      " That returns a list of the tables that Contain the 'UploadedFlag' Column.   I have testing the SQL on both HeidiSQL and SQLite Expert Personal applications and run and return what I expect, however when I run it through LabVIEW I get a 402860 Error with the description " SQLITE_ERROR(1): near "(": syntax error " 
      If anyone could point me in the direction of the syntax error then I would be very greatful.
      Regards
      Mark
    • By drjdpowell
      For comment, here is a beta version of the next SQLite Library release (1.11).   It has a significant new feature of a "Parameter(s)" input to the "Execute SQL" functions.  This can be a single parameter or a cluster of multiple parameters.  Uses Variant functions and will be not as performance as a more explicit preparing and binding of a Statement object, but should be easier to code.

       
      drjdpowell_lib_sqlite_labview-1.11.0.86.vip



×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.