Random Ramblings on LabVIEW Design

Databases - MySQL and SQLite

Hello Data Darlings,

This conversation has come up on LinkedIn and the comments section is a little light to give it justice. I'll give it some time here.

Databases are a very important tool in a software designers toolbox and they are everywhere.

Very commonly customers will stipulate using ODBC, Access and SQL Server and the assumption is that these paid for solutions are better than the alternatives. This is misconception #1.

Misconception #2 is that ACCESS/SQLite = SQL Server/MySQL, I will explain the difference further into the article.

Misconception #1

Looking at https://db-engines.com/en/ranking we can see that from a internet chatter perspective MySQL>SQL Server and Access>SQLite. From a deployed instance perspective SQLite is used everywhere. (As an example Adobe Reader, Android, Windows 10, iOS Text Messages to name a few..)

MySQL and SQLite are free and open-source and usually don't have many limitations on usage. This means there is a LOT of free info, utilities and help. It also means they are easy to distribute.

Standard SQL - SQLite uses PostgreSQL syntax as its basis (another great dB, but not one I've used) and MySQL uses a pretty standard SQL syntax too. I've never had any issue converting one to another.

Worried about whether it will cope? Taken from Oracle

RightNow Technologies is a company that provides CRM software solutions for 2000 organisations

• 30+TB of data - all stored in MySQL
• 17 billion queries and 500 million page turns per month

Put this down as personal bias but, Access SQL is weird and SQL Server has issues talking from one version to another! I find SQL Server very hard to get my head around. I've done some great software with these products, but I don't regard them as the best tools for the job any more.

Finally let's talk ODBC, I've always viewed it as a solution to a problem I don't have. If you use SQL being able to swap from 1 type of database to another is of limited value. This is because you will need to change all the SQL to suit. Using ODBC limits you to Windows (don't get me started on 32 bit and 64 bit...).

Whereas you can talk to SQLite though a dll and MySQL through TCP/IP. No license, minimal dependencies.

Misconception #2

There are 2 types of databases to consider here.

Embedded

These are designed to stay local to the application and be part of the distribution. If you use Access you would be talking to an mdb or accdb file. If you use SQLite you will typically be storing your data to a db3 file via a system library (dll for Windows).

Typically there is some sort of locking of the file to an application that will prevent sharing.

Microsoft Access and SQLite are embedded databases

Client/Server

These are designed to be loaded remotely (server) and accessed via 1 or more clients. Only updating fields are generally locked here.

MySQL and SQL Server are Client/Server databases

Enough of the verbiage, let's look at some code!

SQLite

We use James Powells wonderful tool kit you can check out more capabilities in this GDevCon video.

Here's part of our SQL Component.

We'll just look at the code required to do a simple query, so you open a session.

Do some SQL (SELECT Query or INSERT, UPDATE or DELETE), the close the session. Here's what it looks like in use.

SQLite can work in RAM or securely to file. Secure means it INSERTS a new record, waits a cycle of the hard disk and confirms it has been updated. This means it is stupendously fast to read and it can be much slower to INSERT. This can be optimised by doing batch inserts. Because SQLite is a compact file system many applications use it as their file format, in one of our projects we create SQLite db3 files as results files. With no data and only the schema it is tiny (14kb). I've actually embedded it in a constant in a LabVIEW VI.

We use SQLite for all our application configuration and calibration data.

MySQL

We use MySQL for our Laboratory management systems and for our Issue Tracking and project management data.I wish I knew who the original author of the toolkit I use is so I could give them Kudos. It works like a dream. The only changes we've made is for large datatypes.

Here's our MySQL LCOD component.