SQLite Demo

[kegghead]

Hello Mass Compilers!

Attached is the demo project we worked on at the 03/11/2014 meeting where we explored using SQLite in LabVIEW. To use the project you'll need to have installed J D Powell's SQLite LabVIEW library which may be downloaded from the lavag code repository.

The attached project is fully implemented, and contains the four slides I used as an introduction in pptx format.

I hope I at least showed how easy it can be to work with databases in LabVIEW.

Notes about the code

Create DB.vi can be used at any time to reset two tables their original state, should something go horribly wrong as you play with the example. The VI is an example of how to execute basic static queries on a database. Generally speaking this method is fine as long as you're executing queries which don't change (the query in the VI is a string constant, so all clear on that front). But if you ever need to take user input as part of a query, you should avoid this method in favor of using a prepared statement.

Demo.vi is the main VI for the demo. It's a basic event driven state machine, where the timeout case is used to update the UI. Note how initially the timeout is set to fire, and any time a new artist is added we return a zero timeout so our ring control in the UI will get repopulated. Missing from the VI is any representation of which albums are in the database, see Show DB.vi.

Get Artist Names and IDs.vi is called in the state machine's timeout case. This is an example of how retrieve data using a prepared statement.

Add Artist.vi is called to add a new artist to the database and serves as an example of how to use a prepared statement to supply data to the database.

Add Album.vi is called to add a new album, which needs both a name and the ID of an existing artist, also uses prepared statements, in this case binding two variables of different types.

Show DB.vi may be called at any time to retrieve a listing of all albums in the database and shows their corresponding artist.

Where to go from here?

One thing we never covered is how to explicitly define relationships in the database. Each album in our database has a field holding the ID of the artist associated with the album, which is a reference to a row in the corresponding artist table. As the database stands it is not aware of this relationship. If we delete an artist, the corresponding albums will fall out of date, referencing an artist that no longer exits. Basically our database lacks any constraints to ensuree integrity of data.

These relationships can be defined, in this case we would be looking to create a foreign key constraint. When defining a foreign key you can specify what happens when related keys are operated on, for example we could say that if I delete an artist, I need to set the artistid fields in any album that refer to that artist as NULL, or we can alternatively have the albums deleted. As an exercise I'd suggest looking into foreign keys and modifying our database to make these changes automatically for us.

Hint: In the tookit we used at the meeting, foreign keys are not turned on by default so once you open your connection, you will need to issue a PRAGMA statement:

PRAGMA foreign_keys = on;

Some other SQLite implememtations, for example S Rumbell's SQLite API, have foreign keys turned on by default so the PRAGMA statement would not be needed.

[drjdpowell]

Hello Michael, Some suggestions for extended SQL in the example:

1) to enforce the relationship between the tables, consider a TRIGGER, such as:

CREATE TRIGGER artistdelete BEFORE DELETE ON artist  

  BEGIN     

    DELETE FROM album WHERE artistid=OLD.id; 

  END;

This will delete all albums of the deleted artist. 

2) Consider defining a VIEW that does the table JOIN, so as to make the SQL in the LabVIEW VIs simpler:

CREATE VIEW albumview AS

   SELECT album.name as albumname, artist.name as artistname FROM

           album JOIN artist ON album.artistid=artist.id;

3) Consider using the artist name, rather than id, in the "Add Album" VI (if you prefer not tracking the IDs).  You can do this with a SELECT inside an INSERT:

INSERT INTO album VALUES (NULL, ?, (SELECT id FROM artist WHERE name =?));

-- James

[kegghead]

Thanks James. You're absolutely correct, good feedback. This was a quick 30 minute introduction not only to SQLite, but SQL in general so we never touched topics such as triggering or views.

By the way, this was the first time I sat down and used your API. I like it very much!

[Ancle]

Dear Sir,

Can you please add "delete" and "update" function? Thank you!

B/R

Ancle