SQLite for OpenG

[drjdpowell]

Hello, I’ve been working with SQLite for a logging application and I thought I might offer my SQLite LabVIEW wrapper for possible inclusion in OpenG. There are at least two other implementations out there, both with licensing restrictions, but it would be nice to have this in OpenG as I think SQLite is a major addition to the capabilities of LabVIEW.

Below is a zip file; it includes a couple of examples.

SQLite LabVIEW.zip

LabVIEW 2011. SQLite dll for Windows (32-bit) included.

NOTE: more recent version now in the Code Repository.

An (incomplete) menu:

Here is the block diagram of Example2:

There are basically two use modes: (1) calling “Execute SQL” on a Connection to run SQL scripts (and optionally return 2D arrays of strings or variants 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.

This package closely follows the SQLite C/C++ interface and is intended to facilitate the execution of SQL scripts, rather than provide VIs that substitute for SQL statements. Thus there are no VIs for creating tables, triggers, etc. The SQLite website provides extensive documentation of SQL and the C/C++ interface.

The only differences from the C/C++ interface are:

1) “Reset” and “Finalize” do not return the error code from the previous “Step” (as this would be both unnecessary an confusing in LabVIEW)

2) The default busy timeout for waiting for a database file that is temporarily “busy” due to another connection is set at 5000 ms, rather than 0 ms.

3) I created a “First Step” VI that wraps “Step”, intended to be the first call on Step that actually execute the statement (further calls to Step increment through return result rows). I did this to allow future potential retry logic in “First Step”, and to have a clearer set of VI icons showing the difference between executing a statement and stepping through result rows.

As I said, it would be really nice to have an SQLite interface in OpenG. I’ve only just scratched the surface of what can be done with SQLite (see, for example, the “Full Text Search” and “R*tree” extensions).

— James

Edited July 2, 2012 by drjdpowell

[jcarmody]

Thanks. I'll be looking at it right away.

[...]

This package closely follows the SQLite C/C++ interface and is intended to facilitate the execution of SQL scripts, rather than provide VIs that substitute for SQL statements. [...]

That's typically how I work with databases, anyway.

[JackDunaway]

Cool! Just a quick couple of gotchas (that got me) prior to running the examples in this library:

• Install OpenG String Library
  
• Use LV 32-bit since 64-bit LV returns Error 12 for the SQLite DLL calls (I wonder if wrapping the SQLite exe rather than DLL would give better platform independence? just thinking aloud)
  

Those two things settled, both examples run like a champ.

[drjdpowell]

• Use LV 32-bit since 64-bit LV returns Error 12 for the SQLite DLL calls (I wonder if wrapping the SQLite exe rather than DLL would give better platform independence? just thinking aloud)

Thanks, should have mentioned that. I took the precompiled win32 binary from the SQLite Downloads page. I specify the SQLite binary at only one point in the library, so it should be easy to substitute different compiled code for different operating systems using a single conditional disable structure.

-- James

[asbo]

I specify the SQLite binary at only one point in the library, so it should be easy to substitute different compiled code for different operating systems using a single conditional disable structure.

I'm excited to see this toolkit released. I agree that the ideal route would be to use a conditional structure to specify the correct binary - then each platform can benefit from their specific build.

Does licensing actually allow you to include the binaries with the toolkit? I know that some software is prohibitive of that.

[JackDunaway]

I specify the SQLite binary at only one point in the library, so it should be easy to substitute different compiled code for different operating systems using a single conditional disable structure.

Cool, and also, +1 for using "Names" Name Format on the calls - good style.

[drjdpowell]

Does licensing actually allow you to include the binaries with the toolkit?

Yes. They’re public domain. "Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means… All of the deliverable code in SQLite has been written from scratch. No code has been taken from other projects or from the open internet. Every line of code can be traced back to its original author, and all of those authors have public domain dedications on file. So the SQLite code base is clean and is uncontaminated with licensed code from other projects."

[asbo]

Wow. That is seriously open source software.

[mje]

Indeed, and SQLite is an excellent lightweight database.

[drjdpowell]

If anyone has SQLite experience, can you comment on my choices for data type conversion between SQLite3 and LabVIEW? There isn't a clear one-to-one conversion between LabVIEW types and SQLite's dynamic typing system, so I ended up deciding to leave the choice of type up to the User. This has the disadvantage of requiring the user to understand the SQLite3 datatypes in addition to LV types, but it has the advantage of full control. The specific issues/choices I made are:

1) SQLite3 has "TEXT" (UTF-8 encoded, zero-terminated strings) and "BLOB" (binary), while LabVIEW has strings used as either ANSI-encoded characters or binary (as in "Flatten to String"). This is a problem for any possible Variant-to-SQLite converter, as it is not possible to determine if a particular string is really character text or binary.

2) SQLite3 "INTEGER" is variable size (1 to 8) bytes and can hold any LabVIEW integer type except U64. I use I64 as the corresponding LV type. Not sure what to do about U64.

3) "REAL" is easy, as it is exactly the same as LabVIEW DBL. Except for one slight issue: "NaN" is not allowed by SQLite and is converted to "NULL", but "NULL" is retrieved by SQLite as zero! I opted to override this and return any NULLs as "Not a Number" if retrieved as a DBL.

4) There is no timestamp data type in SQLite3. I added functions for saving LV Timestamps as REAL (DBL) values. However there are alternate possible choices for timestamps that would allow the use of inbuilt SQL functions.

There is a “Get Column Variant” property that converts any SQLite value to a LV Variant (REAL—>DBL, INT—>I64, NULL—>Void,TEXT/BLOB—>String), but no function for binding a LV Variant, because of the above described difficulties.

— James

[mje]

Well, I'm definitely not the resident expert, but we do use SQLite for our biggest LabVIEW application. Typing can be tricky since columns don't have a fixed type, it's the individual values that are typed. That said, SQLite pretty much interconverts as necessary, so doesn't really care about types in most cases.

1) Non-issue really. If you see a LabVIEW string, you'll always have to check for null characters anyways to decide if you're going to bind text/blob. Unless you store all text as blobs, but then you need to throw collation out the window (I think?) and searching becomes interesting.

2) U64s will store just fine as text, though searching might bet a bit weird. Keep in mind SQLite decides how to store something, not you. Even if you bind the string "123" as text, there's a good chance SQLite will store it as an I8 instead (though column affinities might come into play, not sure).

3) Eek, I wasn't aware of the NaN issue. Not a big deal though since null is meaningless to a DBL. If the user is requesting a DBL, do a type check: if you see a DBL, retrieve the data, if you see a null, return NaN.

4) For timestamps, I like the ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") values. They're easy to read, easy to parse, easy to generate.

I'd stay away from variants, due to performance hits. If you're set on them, leave them on a top level palette with lower level access.

[drjdpowell]

1) Non-issue really. If you see a LabVIEW string, you'll always have to check for null characters anyways to decide if you're going to bind text/blob. Unless you store all text as blobs, but then you need to throw collation out the window (I think?) and searching becomes interesting.

Yes, the collation is a big reason not to just go with BLOB for all LV strings.

2) U64s will store just fine as text, though searching might bet a bit weird. Keep in mind SQLite decides how to store something, not you. Even if you bind the string "123" as text, there's a good chance SQLite will store it as an I8 instead (though column affinities might come into play, not sure).

I think searches would go wrong for U64 values too high to convert into an I64.

If the user is requesting a DBL, do a type check: if you see a DBL, retrieve the data, if you see a null, return NaN.

That’s what I did.

4) For timestamps, I like the ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS") values. They're easy to read, easy to parse, easy to generate.

But they’re 23 bytes instead of 8. I can modify “Get Column Timestamp” to handle ISO8601 strings in addition to DBLs. And perhaps I could have two “Bind Timestamps”: “Bind Timestamp DBL” and “Bind Timestamp ISO8601”?

[Jordan Kuehn]

At risk of derailing the discussion, what benefits besides OSS does SQLite provide over other database types including MS Access?

[mje]

Serverless. It's just a library that you distribute with your application. No other processes, installers, etc.

[Jordan Kuehn]

Serverless. It's just a library that you distribute with your application. No other processes, installers, etc.

I like your style, short and to the point. SQLite will get a look next time a customer needs a database.

[drjdpowell]

Serverless. It's just a library that you distribute with your application. No other processes, installers, etc.

And that library is a mere 564 kB. Very light. Being so small and simple, it allows one to think of using a database solution for a wider array of problems. One thing that needs to be done is for someone to compile the SQLite source for Real Time targets. Any volunteers?

[Wouter]

1) SQLite3 has "TEXT" (UTF-8 encoded, zero-terminated strings) and "BLOB" (binary), while LabVIEW has strings used as either ANSI-encoded characters or binary (as in "Flatten to String"). This is a problem for any possible Variant-to-SQLite converter, as it is not possible to determine if a particular string is really character text or binary.

Don't support LabVIEW variants.

2) SQLite3 "INTEGER" is variable size (1 to 8) bytes and can hold any LabVIEW integer type except U64. I use I64 as the corresponding LV type. Not sure what to do about U64.

Uhmmm call me stupid but how do you want to support U64/I64 when the variable size is 1 to 8 bytes you can only support till U32/I32 and not I64 or U64, so my opinion would again be don't support U64/I64.

3) "REAL" is easy, as it is exactly the same as LabVIEW DBL. Except for one slight issue: "NaN" is not allowed by SQLite and is converted to "NULL", but "NULL" is retrieved by SQLite as zero! I opted to override this and return any NULLs as "Not a Number" if retrieved as a DBL.

Again don't support NaN's. (or use http://www.mail-arch...g/msg68928.html but then again how to handle +Inf, -Inf)

4) There is no timestamp data type in SQLite3. I added functions for saving LV Timestamps as REAL (DBL) values. However there are alternate possible choices for timestamps that would allow the use of inbuilt SQL functions.

Use ISO8601, thus save as text. Edited April 16, 2012 by Wouter

[Wouter]

I like your style, short and to the point. SQLite will get a look next time a customer needs a database.

Please read this http://www.sqlite.org/whentouse.html.

[JackDunaway]

Uhmmm call me stupid but how do you want to support U64/I64 when the variable size is 1 to 8 bytes you can only support till U32/I32 and not I64 or U64, so my opinion would again be don't support U64/I64.

Well, I won't call you stupid, but a 64-bit integer has no prob squeezing into a size 8 ("hey! who's calling me a bigint??").

[drjdpowell]

Again don't support NaN's. (or use http://www.mail-arch...g/msg68928.html but then again how to handle +Inf, -Inf)

That conversation is the one I came across, and the solution I chose. +Inf and -Inf store in SQLite3 with no problem. It is only NaN that is treated differently.

Use ISO8601, thus save as text.

I’m considering removing the “Bind Timestamp” and “Get Column Timestamp” methods entirely, thus forcing the User to explicitly decide on what to use as Timestamps. Possibly with some support VIs to convert LabVIEW Timestamps into ISO8601 text formats or the other two types suggested in the SQLite3 documentation: Julian day number as a DBL, or Unix Time as an integer. Other options (the number of possibilities is why I’m considering dropping Timestamps altogether) is LV timestamp as a DBL, or the full 128-bit LV timestamp as a BLOB.

— James

[drjdpowell]

Yet another Timestamp issue: SQLite3’s datetime function (which can be handily used in an SQL statement as "datetime(now)”) is in the format YYYY-MM-DD HH:MM:SS and is UTC, but the proper ISO8601 format is YYYY-MM-DDTHH:MM:SS.SSSZ. One can get this format in an SQL statement by using the longer "strftime('%Y-%m-%dT%H:%M:%fZ','NOW’)”.

Currently, my thinking is to have “Bind Timestamp” which saves the full 128-bit LV Timestamp as a BLOB, and “Bind Timestamp (Text)” which saves the ISO8601 format as TEXT. I will have a single “Get Column Timestamp” that checks for the datatype and tries to convert accordingly (16-byte BLOBs or TEXT of the right format).

[drjdpowell]

On a side note, does anyone know how one might implement this using LabVIEW? It would allow creation of new SQL functions (such as ones that can handle LabVIEW timestamps). It requires the passing of a function pointer to the SQLite dll function. I have no real experience in such things, but I would have thought one could make a VI into a dll, and somehow pass a pointer to it to SQLite. But Google says no.

[mje]

I doubt you could do that, I don't expect LabVIEW follows the C-style function interface. It would require you to compile a binary in a C language, make sure the proper calling convention is used for the function, and supply that function pointer. I think you're out of luck if you want to stick with LabVIEW only and have arbitrary VIs get called.

[drjdpowell]

LabVIEW dll functions can be called from C, I’m pretty sure, but I can’t figure out how toy get a pointer to the function in LabVIEW.

[drjdpowell]

Here’s what I decided with the Timestamps. LabVIEW Timestamps saved as 128-bit BLOB or 8-byte DBL, or ISO8601 Timestring with “T” and “Z”. The “Get Column Timestring” property recognizes all these formats as well as the standard SQLite text format (sans “T” or “Z”). Shown is a “Test Timestamps" VI that records Timestamps in five ways, the three bindings and two autotimestamping Triggers (using strftime() to get the full ISO8601, and date time()):

SQLite LabVIEW.zip