Jump to content
drjdpowell

SQLite Library 1.6 beta: Attributes lookup table

Recommended Posts

Attached is a beta version of the latest 1.6 version of SQLite Library, for anyone who like to give feedback.   A major addition (not yet well tested) is “Attributesâ€, modeled on Variant Attributes or Waveform Attributes, but stored in any SQLite db file.  The idea is to make it easy to store simple named parameters without much effort.  See the example “SQLite Attributes.viâ€.  

 

A more minor upgrade is making “Execute SQL†polymorphic, so as to return data in a number of forms in addition to a 2D-array of strings.  See the upgraded example “SQLite Example 1 — Create Table.vi†which uses the new polymorphic VI, including showing how to return results as a Cluster.

 

For Attributes, I had to make some choices in how to store the various LabVIEW types in SQLite’s limited number of types.   The format I decided on is:

1) all simple types that already have a defined mapping (i.e. a “Bind†property node) are stored as defined (so strings and paths are Text, DBLs and Singles are Float, integers (except U64) are Integers.

2) Timestamps are ISO-8601 Text (the most standardized format of the four possibilities)

3) Enums are stored as the item text as Text, rather than the integer value.  This seems the most robust against changes in the enum definition.

4) LVOOP objects are stored flattened in a Blob.

5) any other LV type is, contained in a Variant, flattened and stored in a Blob.  Using a flattened Variant means we store the type information and LabVIEW version.

 

post-18176-0-04139400-1448742257.png

 

drjdpowell_lib_sqlite_labview-1.6.0.51.vip

LabVIEW 2011-2015

 


The Attribute stuff grew out of a project where SQLite files held the data, one for each “Runâ€, and the Runs had lots of small bits of information that needed to be stored in addition to the bulk of the data.   When and where the measurement was taken, what the equipment setup was, who the Operator was, etc.  I purpose-made a name-value look-up table for this, but realized that such a table could be made into reusable “attributesâ€.

  • Like 2

Share this post


Link to post
Share on other sites

Out of curiosity, does storing a path as a string present platform problems? That is if I store "foo\bar.txt" in Windows, are the underlying primitives smart enough to change it to "foo/bar.txt" on a mac?

  • Like 1

Share this post


Link to post
Share on other sites

Out of curiosity, does storing a path as a string present platform problems? That is if I store "foo\bar.txt" in Windows, are the underlying primitives smart enough to change it to "foo/bar.txt" on a mac?

 

No, but I’ll add the same NI off-pallet VIs used elsewhere in the library to convert to/from Mac paths: “Path to Command Line String†and “Command Line String to Pathâ€.  Thanks.

 

Do you use LabVIEW on the Mac?  I haven’t in a long while and I could do with someone testing it.

 

— James

Share this post


Link to post
Share on other sites

Glad I asked then, it was a genuine curiosity... I'm platform locked to Windows so I've never been sure how that is handled (other than under the hood paths aren't a simple string).

Sorry I can't help with the Mac testing.

Share this post


Link to post
Share on other sites

Problem found using LabVIEW for MAC OS X::

 

Comparing the behavior of an "empty path" of:

 

LabVIEW 2012 and LabVIEW 2013 - 32-bit

vs

LabVIEW 2014 - 64-bit   

 

LabVIEW 2014 - 64-bit  empty path is not really empty... it has the value of "/"

 

By consequence, connection.lvclass:Open (string).vi  will never execute the empty case to get the default OSX

installation of SQLite dylib
 
My suggestion is to change the case condition to : "", "/"
 
Easy to work around, but would be nice to have this fix as part of the package. Also, I can help with some future validation on OS X platform as I spend most of my time working with LabVIEW for MAC OS X
 
-Sergio
  • Like 1

Share this post


Link to post
Share on other sites

Hi,

 

Any thoughts on incorporating the JSON1 functionality included with the latest release of SQLite (http://sqlite.org/json1.html)?%C2'> This new functionality is quite intriguing to me as it seems to allow SQLite to be a possible "best of both worlds" solution for relational and document oriented data management situations along the lines of what the latest version of PostgreSQL has.

 

Thanks,

-Dino

 

btw: great job on the existing code - works like a charm in our development sandbox at the moment!

Share this post


Link to post
Share on other sites

Any thoughts on incorporating the JSON1 functionality included with the latest release of SQLite ( This new functionality is quite intriguing to me as it seems to allow SQLite to be a possible "best of both worlds" solution for relational and document oriented data management situations along the lines of what the latest version of PostgreSQL has.

The JSON SQL functions should be working in this beta (I think it includes the 3.9.0 dll with those functions).   I’ve only played around with them a little, but they do work.   

Share this post


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.


×
×
  • Create New...

Important Information

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