sqlite : a light and easy to use database

Sometimes you would like to use the power of the SQL language to handle your data but without all the burden of a database. Indeed, why would you need a heavy software that can manage many different accounts, cluster configurations, and can listen on a network interface if the database will only be accessed just a few times each hour by a single software/script on the localhost? Sqlite proves to be the perfect solution for such situation and has the following assets:

• serverless database : forget the client-server scheme! Sqlite will only consume CPU and memory when you use it.
  
• zero configuration : just install the sqlite binaries and use it!
• single database file : the whole database stands in a single file. So it is very easy to handle or move to another computer.

In my case, I mainly use sqlite on my xen dom0 servers. I have a cron-script that is executed every 10 minutes, that collect statistic values about the running domUs and that stores them in the database (something similar to sar software). For such critical dom0 servers, I wanted to access a local low memory footprint database.

Let's give an introduction about how to use it.

On Debian 5, installation follows the traditionnal method :

apt-get install sqlite3

Then, you can start and create a new sqlite database with the sqlite3 command. Just give as an argument the name of the file that will store your database :

luangsay@ramiro:/tmp$ sqlite3 foo.db
sqlite> CREATE TABLE sa(date int(5), server varchar(20), load5 float(2), iowait int, eth0rx int, eth0tx int, eth1rx int, eth1tx int);

You can use the .help command to see the list of meta-commands. For instance, if we want to list all the tables and know their structure, we would type :

sqlite> .tables
sa
sqlite> .schema sa
CREATE TABLE sa(date int(5), server varchar(20), load5 float(2), iowait int, eth0rx int, eth0tx int, eth1rx int, eth1tx int);

And here are some basic insert/select commands :

sqlite> insert into sa values(0955, 'server1', 1.4, 32, 187474, 18747, 0, 0);
sqlite> select * from sa;
955|server1|1.4|32|187474|18747|0|0

Of course, you don't have to use sqlite3 to manage your database. You may use your favorite programming language. Here is an example for python.

First, install the software :

apt-get install python-pysqlite2

Then, on the python interpretor, you may type :

>>> import sqlite3
>>> conn = sqlite3.connect( '/tmp/foo.db')
>>> conn.row_factory = sqlite3.Row
>>> cursor = conn.cursor()
>>> cursor.execute('select * from sa')

>>> for line in cursor: print line['date'], line['server'], line['iowait']
...
955 server1 32
>>> conn.commit()
>>> cursor.close()

If you want to discover a bit more about this database, you may refer to the official website.