1. Computing

Some thoughts on working with SQLite

By

In How to use SQLite from C#, I looked at using SQLite from a particular programming point of view. In this article though, I'm more into working with SQLite generally.

I've recently had to get up close and personal with SQLite because of some code I wrote while contracting in 1998-1999. Yes 14 years later, the code that I wrote has risen from the grave Zombie like and is coming back to haunt me, so I need it to live again. At the time it used MS Access 95 which is a rather dated technology these days.

Also the programming language I used then Delphi. Its version 3 compiler is also about 8 release version behind the current incarnation. In my case I was provided with a five year old system (Delphi 2007) and was asked to bring the application back to life using it.

As the Microsoft Jet drivers are now part of Windows, it was tempting to stay. I have Office 2010 with Access and it converted the 750 MB database to the latest .accdb format. But though Access is almost relevant there is a nagging suspicion that it will go away one of these days.

So I decided to switch to another database. I didn't want a client server database as this is a smallish application (14K Lines of code). It needed a database that (a) would work as a single file database like the original access.mdb and (b) had drivers for Delphi 2007. I found SQLite and some drivers.

I'm not going to dwell on the Delphi aspect of the application as this article is about using SQLite not programming it. The first problem was extracting the database tables. There were 990 of them so manually exporting them was really going to be a last resort.

I did a lot of searching on the web and luckily came upon a codeplex project mdbtocsv. It did what it said in exporting mdb tables to csv and within a few minutes all 990 tables had been converted into csv files where the c of csv is actually colon (:) not comma. The file contents look a bit like this Data:78:rpm instead of Data,78,rpm but that's easily fixed.

Because SQLite is totally free, there are also a few free utilities for working with it and here's two Windows GUI ones that I really like.

SQLiteSpy is fast at executing queries. If you open a very large table (ie 600,000 rows) in SQLite Database Browser, it takes a few seconds but it has the advantage of allowing import and export from csv and SQL dump files.

SQLite also comes with a command line shell that lets you run sql and import/export using csv as well. I've used it a few times and it has a few quirks, in that it defaults to : for the csv separator (More colons!), but it's easily changed with a .separator , command to use commas. It's very fast at importing well, but don't add any indexes until after the data is imported.

So long as you don't mind getting your hands a little dirty, with a combination of the those two Windows Gui tools or the cross platform SQLite shell, it's possible to import data in large quantities into SQLite.

Initially importing my application's data for most of the tables brought the .db file to 70 MB size but then I added another table and that bumped it up to 340 MB. Once I added a six column index to that table, it added another couple of hundred megs leaving it close to the original Access.mdb file size.

Note there is a SQLite Vacuum command that will shrink the database file if you've deleted rows or dropped database objects, because those actions do not free space and shrink the database file automatically. The Vacuum command copies the original file doesn't copy the deleted data and so shrinks it that way.

Using SQLite

For those of us used to creating tables with statically defined columns, SQLite is a bit different with its manifest typing. From version 3 on (the current version) the space occupied (and its storage class) depends upon the value stored.

You can specify the type you'd like a column to store and there are five to choose from (TEXT, NUMERIC, INTEGER, REAL and NONE). However the actual type stored depends up on the value. Numbers inserted into a TEXT column are converted to text while text inserted into a NUMERIC column will be stored as a number if a lossless conversion can be made. See SQLite Types.

Performance wise, SQLite is pretty fast. Things like multiple inserts can be done in a transaction to gain extra speed. This question on the Stackoverflow website looks at different methods used to speed up SQLite inserts and is well worth a read. It shows how they speeded it up from 85 inserts a second to over 96,000 a second which is a tremendous improvement!

SQLite SQL

About the only thing lacking is Right Outer Join, but Left Outer join works so its not too bad.

Conclusion

  1. About.com
  2. Computing
  3. C / C++ / C#
  4. Pro. Development
  5. Working With SQLite Database

©2014 About.com. All rights reserved.