SQLite Database

I’ve been working with different types of Databases over the years from dBase to B-Trieve to Oracle enterprise database. Along the way, I looked at or picked up quite a few – Integra SQL, Informix, MS Access, MySQL, SQL Server(s), Watcom SQL/Sybase SQL Anywhere, DB2, DB2/400 etc. While SQL databases are good, I miss the simplicity of dBase or B-Trieve files. After many years now, I think I found the lost love now!! SQLite!

In my intro I talk about “beautiful software”. This is how I felt about this software. Installation is simple, you just download and unzip 1 EXE – sqlite3.exe. (or install in Linux using apt-get or yum). Done! When you are ready to create a database, you type sqlite3 <database name>. That’s it!!! You want to work with the same database again, just type,

sqlite3 <database name>

The file size is what amazes me – it’s a mere 50k. They’ve really packed a lot inside it. For one, it supports almost full (basic) SQL. I was able to actually run SQLs meant for Oracle in Sqlite. The command line utility achieves what other big databases didn’t. SQL*Plus can learn a lot from this tool. And it’s highly portable – in fact, I created a database in windows at work and brought home to Linux. It works like a charm.

For one, they differentiated the tool commands by .prefixing with period (.). So, you want help, just type .help. You want to see the tables in the database you just opened, just type .tables.

What impressed me with the tool, other than the simplicity is, that it provides table export imports naturally. Commands?

.import <filename> <table>

What about exporting table or query output? Just use,

.dump [<tablename>]

.output <filename>

Don’t you want to export to CSV or HTML etc.? Sure, just use .mode. Then, you want a different separator? Use .separator. (Though HTML mode only dumps the tags for table rows, as it’s output is often embedded in CGI outputs, but you can always add the missing tags, right?). Have you tried exporting a table from Oracle SQL*Plus? It’s like extracting teeth, so we end up writing programs/procedures to do it.

True, Oracle (and other databases) is much larger and has a lot more bells and whistles, but for a simple (personal) database task, SQLite is the natural choice. Granted, this is just file based database, but smaller tasks this is a great tool to have in your toolbox. My specific task this time, is to import the log files into a database table, so I can query and procedure usage reports and charts for our recently re-factored web app. In the past, I used MS Access for this type of activity, but with a slow machine I have, Access doesn’t fair well with hundreds of thousands of log records.

I’ve only scratched the surface with SQLite. It seems to be used in embedded applications. There is an API available to work with it C/CPP. Python (Perl, PHP etc) come with native support for SQLite. It’s a pleasure to work with SQLite in Python. More on this later. Apart from these, SQLite offers extensibility by allowing to load (.load) external modules. You can also use Virtual Tables (like Oracle External table) to access a CSV file directly.

I can go on and on, but I will stop ranting and raving about the greatness of this software. If this post made you want to look up more on SQLite, please try the links below.

• http://www.sqlite.org
• http://en.wikipedia.org/wiki/SQLite
• http://www.sqlite.org/whentouse.html
• http://www.sqlite.org/selfcontained.html
• http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/
• http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
• http://code.btbytes.com/2010/09/16/sqlite-csv-loading-using-virtual-table-extension.html
• http://docs.python.org/2/library/sqlite3.html
• http://www.sqlite.org/vtab.html
• http://www.drdobbs.com/article/print?articleId=202802959&siteSectionName=database
• http://www.yolinux.com/TUTORIALS/SQLite.html
• http://robert.accettura.com/blog/2008/02/26/and-the-winner-for-most-embedded-db-is-sqlite/