[CR] SQLite Library

[smithd]

I can't see what you're actually doing but I'd say this seems expected...sqlite has somewhat squishy types.

https://www.sqlite.org/datatype3.html

"Any column in an SQLite version 3 database, except an INTEGER PRIMARY KEY column, may be used to store a value of any storage class."

"Any column can still store any type of data. It is just that some columns, given the choice, will prefer to use one storage class over another. The preferred storage class for a column is called its "affinity"."

 

If that makes you feel bad, just remember that almost every interaction you have with your bank relies on a technology whose type system is pure madness.

Edited December 6, 2018 by smithd

[drjdpowell]

9 hours ago, infinitenothing said:

The attached code shows how SQLite will interpret various types into an integer. It makes me a little nervous that there's no warnings or other way to determine that a value might not match what was requested. I guess I could check every value for its column type. Is there an easier way that I'm missing?

 

SQLite differs from other databases in its dynamic type system and philosophy of "best attempt at conversion instead of error".  Note though that SQLite is meant as an embedded database, sitting behind an API written in another language.  Types are enforceable in that API.  I don't recall ever having mixed types in a column, because a table is generally only written to by one subVI, and maybe modified by another one right next to it in the same library/class.  I have one client where there are four applications that access the same db files, but they all use the same LabVIEW class for access.  The API even stores data points in a scaled format to take up less space, which is transparent to the application code.

[drjdpowell]

BTW, the SQLite Statement class can be subclassed, and you could easily define your own strict-typed versions of the "Get Column" property nodes.  These would just check type before calling the regular function.

[Gepponline]

Quote

Save your image files in binary Blobs.

Yes, I know that SQLite needs Binary Blob but not How to do it in labview and pass it to SQLite library using an SQL statement.

Edited December 6, 2018 by Gepponline

[drjdpowell]

What have you tried?  Have you looked at the examples?

[Gepponline]

On 12/6/2018 at 11:32 PM, drjdpowell said:

What have you tried?  Have you looked at the examples?

Which example do you refer to?
I'm trying using this code to write my image but it seems not to work and I can't understand what I'm doing wrong.

 

[drjdpowell]

"Seems not to work" is the vagueous description you could give, but I wonder why you aren't just saving your bmp image as a blob, rather than turning it into a LabVIEW image format (and then only storing part of that).

[Gepponline]

2 hours ago, drjdpowell said:

"but I wonder why you aren't just saving your bmp image as a blob, rather than turning it into a LabVIEW image format (and then only storing part of that)."

As I told in my previous message, Because I Don't Know How to save my image as a blob in labview!
The image is not originally stored in my PC but is acquired with Vision Libraries.

So once acquired, Hwo to save it as a blob?
And then How to pass it to SQLite library to store it into my DB?

[infinitenothing]

I suggest you break your question down into parts as different threads and forums will be able to answer each part better. It looks like your SQLite stuff is correct and your issue is just flattening and unflattening so the machine vision forum might be a better place to ask for help. In the interim, here's one way to do your flatten unflatten using the IMAQ toolkit.

 

Edited December 10, 2018 by infinitenothing

[Gepponline]

As you suggested, I've made an example using Vision Library, starting from a previously stored image but nothing change.
Or better, I don't get any error executing the VI and in the DB I can see the new record but using SQLite Administrator I'm not able to see the picture inside the DB.
SQLite Administrator has a specific function that should show the image.
I attach even the DB with 4 records with that should have the same image on it but I can't see it.

 

DB_CN35.sdb

[smithd]

3 hours ago, Gepponline said:

using SQLite Administrator

This is a pretty important piece of information. I took a quick look at their page (unfortunately their support forums are down, and there is no way I'm installing that software on my computer, so this is all I can go on) and it seems to say it supports bmp and jpg images. from your posts, you have so far provided it with:

• The pixel values of an image, type cast directly into a string with no metadata
• The NI vision software flattened representation of an image.

I'd suggest the following:

• If you have a bmp or jpg image, use the read from binary file function with a elements to read set to -1 (to read the whole file as one big string) and pass that directly to sqlite. You may need to make sure that sqlite administrator knows the type, but i think bmp and jpg both use linux-style 'lets hide the extension inside the file' encoding, so its probably not necessary.
• If you have an imaqdx image in memory, use writestring (https://zone.ni.com/reference/en-XX/help/370281AD-01/imaqvision/imaq_write_string/) to generate a bmp or jpg string, and pass that directly to sqlite.

 

To give a slightly more complete explanation, if you read the first few posts on this page we discuss the sqlite type system, which is very flexible -- in this case, everything is a string. You told everyone you wanted to store images in the database, which you were successfully doing in the formats described above. You did not explain that you had a very specific tool in mind for reading those images back out, and that is where the problem lies. It was expecting the string to be a specific format, and you were storing it not as that format.

[drjdpowell]

I think your issue is the multitudes of "image" formats.  There are many formats, and many conversion functions, and it is difficult to get things to work by trial and error.  The webpage for "SQLite Administrator" indicates it understands JPG format, yet your attached code is converting from JPG to a LabVIEW-only flattened image format.  You need to read that JPG image into a LabVIEW string, without converting it into some other format, and insert it.   Look into the "Read form binary file"... (I see smithd has beaten me to it). 

[infinitenothing]

I want to clarify that as far as I know there's no standard way to store an image in a SQLite database. That is, @Gepponline, your question has nothing to do with SQLite much less this toolkit. As far as we know, SQLite Administrator uses a proprietary binary format for storing the images as blobs. You should really go to that community to determine what that format is. Unfortunately, that project looks like it was abandoned with no source code available so you might just be out of luck.

[Gepponline]

My initial problem was on HOW to store blob data in an SQLite record field using this toolkit.
So i thought this was the right place to make my question.
I Had no idea about the problem of no standard for storing images on a BLOB field.
Now that I know that, i can say:

1)Attached  are 2 solution working with SQLite Manager but not with SQLite administrator neither with DB Browser for SQLite

2) Being so, maybe it's better to store images in a specific folder and store the path in the DB.

Thank you so much for the support.

 

[smithd]

DB browser seems to only support png/bmp. I found a video suggesting jpeg support too, but the link said only via nightly builds

Edited December 12, 2018 by smithd

[drjdpowell]

Tried inserting png, jpg, and bmp images (by solution 1) and they can be viewed by SQLite Expert Personal, which I use.

[smithd]

1 hour ago, drjdpowell said:

Tried inserting png, jpg, and bmp images (by solution 1) and they can be viewed by SQLite Expert Personal, which I use.

I've used that in the past too, but i was creeped out today when I looked at it and saw that the downloads were http instead of https. The reason I noticed was that windows claimed the 32-bit version had a trojan. I'm assuming it was a false detection, but even if thats the case you shouldn't be serving up installers over an unsecure connection.

[Cerebu5]

Hi I'm new to LAVA.  This library you've created looks great!  I was hoping I could try and use it on a 9068 cRIO controller.  Looking through the "SQLite.lvlib:Connection.lvclass:Open (DD).vi" I noticed when the TARGET_TYPE==RT it looks for the sqlite3 library location is at "/usr/lib/libsqlite3.so"

I was wondering if you could either direct me to instructions or provide the file or directions to obtain this file so that I can add it to my controller that I'm testing your code with?

Again thanks for your help!

[drjdpowell]

See the posts starting here.  You have to use opkg to install sqlite on a Linux RT system.

[Mads]

I was thinking of replacing a proprietary chunk based time series log file format that we use with SQLite, using this library. From a test I did some time ago I thought read performance would not be an issue (unlike for TDMS for example, which performs really badly due to the fragmentation you get when logging large non-continuous time series ), but now that I redid the tests I see that the read times get quite high (writes are less of an issue in this case as we normally just write smaller batches of data once a minute). I expected the database to be much slower, but the difference is of a magnitude that could be troublesome (no longer a real-time response).

To give a simple example; if I have a table of 350 000 rows containing a time stamp (real) and 3 columns of  measurements (real),  and I select a time (sub)period from this table; it takes up to 0,6 seconds to retrieve the measurements on my test machine (using either a single SQL Execute 2D dbl, or the Select many rows template). For comparison the current binary file solution can locate and read the same sub-periods with a worst case of 0,03 seconds. 20x times faster.  This difference would not be an issue if the accumulated time per batch of request was still sub-second, but the end users might combine several requests, which means the total waiting time goes from less than a second ("instantaneous") to 5-30 seconds...

Perhaps I was using @ShaunR 's library back when I was able to get this done "instantaneously", but that seems to no longer exist (?), and in this case I need a solution that will run on Linux RT too...Should it be possible to read something like this any faster, and/or do anyone have any suggestions on how to improve the performance? The data will have to be on disk, the speedup cannot be  based on it being in cache / memory...

Edited February 12, 2020 by Mads

[drjdpowell]

First question is what is your table's Primary Key?  I would assume it would be your Timestamp, but if not, the lookup of a small time range will require a full table scan, rather than a much quicker search.

Have you put a probe on your prepared statement?  The included custom probe runs "explain query plan" on the statement and displays the analysis.  What does this probe show?

[Mads]

3 hours ago, drjdpowell said:

First question is what is your table's Primary Key?  I would assume it would be your Timestamp, but if not, the lookup of a small time range will require a full table scan, rather than a much quicker search.

Have you put a probe on your prepared statement?  The included custom probe runs "explain query plan" on the statement and displays the analysis.  What does this probe show?

Whether I use the timestamp as the primary key or not does not affect much. A select * from the table will also spend about as much time as one with an equivalent where clause on the timestamp....

99% of the time is spent in the loop running the get column dbl node (retrieving one cell at a time is normally something I would think would be inefficient in itself, but perhaps not, I guess that is dictated by the underlying API anyway, or?).

 

[drjdpowell]

Can you post an example?

[Mads]

13 hours ago, drjdpowell said:

Can you post an example?

Attached is an example VI and db-file where I just use a single sql execute to retrieve the data...On my Windows 10 computer (SSD) with LabVIEW 2018 a full retrieval, with or without a time clause, takes about 0,66 seconds this way.

I have tried using the select many rows template, and it takes about the same time.

Retrieval test.zip

Edited February 13, 2020 by Mads

[Mads]

On 2/13/2020 at 9:44 AM, Mads said:

Attached is an example VI and db-file where I just use a single sql execute to retrieve the data...On my Windows 10 computer (SSD) with LabVIEW 2018 a full retrieval, with or without a time clause, takes about 0,66 seconds this way.

I have tried using the select many rows template, and it takes about the same time.

Retrieval test.zip 4.19 MB · 1 download

Managed to find an old copy of LVS's sqlte_api-V4.1.0 now, which I suspect is what I used when evaluating SQLite previously. Stripped down to just the query I see that it is actually 3x slower than your solution @drjdpowell for this query(!). I also tried SAPHIR's Gdatabase with similar slow results. I also did a test in python as a reference, and it ticked in at 0.44 seconds.

So no reason to expect better after all...

Edited February 14, 2020 by Mads