Getting started with Embedded SQLite

Unlike most other relational databases, SQLite is not a server. MySQL, Oracle etc. run as servers and require a degree of configuration. SQLite needs no configuration and if you are writing a program in C you can statically link the code in so it’s part of your executable. It’s just 122.9 thousand lines of code. Usually though you use it a shared library i.e. a dll on Windows.

The important thing is that supplying another dll requires way less setup than installing a 3rd party database server and then configuring it. SQLite needs no configuration. Of course it’s not multi-user so you wouldn’t use it for those sorts of applications.

In this article I’ve provided a simple config class (in C#) that lets you use SQLite to store all configuration data in a hierarchical fashion, not unlike the Windows registry.
It was tempting to use JSON (JavaScript Object Notation) as the configuration technology especially as SQLite has loadable extensions and one of those is for JSON. I’ll save that for another time.

Config Design

I wanted a config system based very loosely on the registry. It has sections and within each section there are multiple keys. Each key holds a single item. To make it more interesting, each section can be encrypted and all keys in that section are encrypted and decrypted automatically (once you open the section with the password). Note that you can buy an extension to SQLite that lets you read and write encrypted database files but that’s not cheap. Here the encryption I’m using is provided by the .NET cryptographic library.

Typically for applications with data like this, but here I wanted the classes that back this to be very lightweight holding just the ids for database objects.

SQLite

Before you can use this you need to setup a SQLite database file. To keep things simple, there’s just three tables in the database. The first is sections which has a row for each section with a flag to say of the data is encrypted. This is the SQL to create the config table.

CREATE TABLE sections (

idconfig    int,

sectionname text,

encrypted   int);



CREATE INDEX iact on sections(idconfig);

Keys are held in sections so the table for keys includes the sections id as well as for the key.

CREATE TABLE keys (

keyname text,

idkey int,

idsection int

);



CREATE INDEX icnf2 on keys(idsection,idkey);

Likewise the key values are held in a table keyvalues defined like this:

CREATE TABLE keyvalues (

idkey int,

value text

);



CREATE INDEX ikey on keyvalues(idkey);

With more time I could have had the application create these tables for you. The project includes a file test.db (you can rename it to anything) with the tables predefined. I used the free (but welcomes donations) SQLiteSpy to set this up.

SQLite is very flexible with what you store in its columns. You specify what type each column is meant to hold but SQLite will for example let you store strings in a number field. As they say in their page about datatypes. 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. The storage classes are NULL, INTEGER, REAL, TEXT and BLOB.

For simplicity sake, the values are held in a string so there’s no worry about dealing with types. You still have to convert numeric or date data to/from strings but you could hold multiple values in one string with separators like comma, semi-colons or pipes.

Installing SQLite drivers

I developed this using the Community edition of Visual Studio 2015, but the newer 2017 should work fine. You do need to get a SQLite driver dll for .Net applications. There’s a very large set of downloads on the sqlite.org website or you can get them using NuGet (see near the bottom of that page). Make sure you get the install version for your .NET framework (Windows 32 or 64) and .NET version.

These aren’t big files but if you don’t use NuGet you need to find the System.Data.SQLite.dll. Once you’ve got the file put it somewhere, then from your Visual Studio project click References then Add Reference then browse to the dll. It should be ticked in the Reference Manager list and is then ready to use and you should see System.data.SQLite in your references.

Conclusion

It’s a pretty simple set of classes but can be built on and extended as you wish. I’ve setup the repository Github.

Post a Comment

Your email address will not be published.