Random Ramblings on LabVIEW Design

Community Browser
Labels
cancel
Showing results for 
Search instead for 
Did you mean: 

Re: Databases - MySQL and SQLite

swatts
Active Participant

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

DB Ranking.png

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.

DB1.png

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

DB2.png

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

DB3.png

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.

MySQL1.png

As before you open a session (connect to the server)

MySQL2.png

Do some SQL and close the session. Here's an example.

MySQL3.png

This has been tested as platform independent, so will happily park your test data from a cRIO.

 

05-Feb-2020 Added MySQL TCP Driver LabVIEW 2015 - Example.vi shows example connecting to an open dB

 

Useful Tool

We use Navicat to simplify interactions and query creation for SQLite and MySQL

Navicat.png

Databases need designing too! Check out Databases with Jonny and Stevey

Sorry that it is a bit broke...

Lots of love

Steve


Opportunity to learn from experienced developers / entrepeneurs (Fab,Joerg and Brian amongst them):
DSH Pragmatic Software Development Workshop


Random Ramblings Index
My Profile

Comments
mbremer
Member

Any thoughts on object-relational mapping toolkits? I've been doing some work in Python recently and have used SQLAlchemy. Seems pretty useful in abstracting away some of the SQL. Haven't found anything similar in LabVIEW though.

swatts
Active Participant

Personally I like SQL and have not seen many abstractions of it that actually give me the flexibility I need. To me the tool to interact with databases is SQL. A fair few LabVIEW programmers seem keen to avoid it tho', I wonder if that is because it text based and the urge is to make a graphical representation.

So here's the big Caveat: I've not heard of this SQLAlchemy, so my comment above is mainly pertaining to the techniques within LabVIEW of abstracting databases to datatypes.

A quick butchers at https://www.sqlalchemy.org/features.html and it looks really interesting (you don't seem to lose functionality to the abstraction).

Steve


Opportunity to learn from experienced developers / entrepeneurs (Fab,Joerg and Brian amongst them):
DSH Pragmatic Software Development Workshop


Random Ramblings Index
My Profile

Intaris
Proven Zealot

Edit: Sorry, a bit off-topic. Not SQL per se.

 

After trying out SQLite (And James' amazing toolkit for it) a few years ago and being very positively impressed, I came to some secondary conclusions.  ACID (Atomicity, Consistency, Isolation and Durability), the cornerstone as to why we can just TRUST databases, are generally things which could help make our software more reliable and robust (YMMV). By reading up on and pondering the consequences of applying both Atomicity and Consistency to our code, I have been able to formalise solutions to things which had been bugging me (literally) for a long time.

 

Although for self-taught code monkeys like myself reading up on ACID principles and design choices made by modern DB systems is a deep deep rabbit hole, I can still recommend reading up on some of the concepts and why certain patterns of behavior have emerged.

drjdpowell
Trusted Enthusiast

I am also of the "learn SQL rather than trying to abstract it away" school.  SQL, a Query Language, is quite different from a programming language and has different and complimentary strengths.

joerg.hampel
Active Participant

+1 for showing rather than hiding/abstracting the SQL statements in your code.




DSH Pragmatic Software Development Workshops (Fab, Steve, Brian and me)
Release Automation Tools for LabVIEW (CI/CD integration with LabVIEW)
HSE Discord Server (Discuss our free and commercial tools and services)
DQMH® (The Future of Team-Based LabVIEW Development)


drjdpowell
Trusted Enthusiast

I should also say that I'm not a user of "Object Relational Mapping".  This is, I think, usually a case of "I've got objects with 50+ elements of data and so I must need a Table of 50+ columns; yikes, I'd better automate that!"   I would suggest you consider if you actually have just a few key data items, that must be columns, plus a lot of ancillary data that can be stored in a JSON-type column.  I have written plenty of Tables that are just Primary Key + one JSON column.

GregPayne
Member

+1 This is a great library. Thanks to Steve's advice I've been successfully using it in a work project for a few months, but more importantly in a home project running on a Raspberry Pi with the community edition and WebVI's; runs perfectly with the database (mariadb) on the Pi.

swatts
Active Participant

Thank you Greg!

I dearly wish I knew the original creator so I could credit them better.

I've corrected an issue with connecting to dBs with no password in this version too..

Steve


Opportunity to learn from experienced developers / entrepeneurs (Fab,Joerg and Brian amongst them):
DSH Pragmatic Software Development Workshop


Random Ramblings Index
My Profile

drjdpowell
Trusted Enthusiast

 

I dearly wish I knew the original creator so I could credit them better.

 

I think you are using a modification of this:

https://forums.ni.com/t5/Example-Code/Native-LabVIEW-TCP-IP-Connector-for-mySQL-Database/tac-p/34966...

 

But don't know if MarcoPolo5 is the original author.