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.

local usefull databases

By default, linux and unix systems have two usefull databases installed. Those databases may speed up your work as a system administrator.

First of all, there is the locate database. This database contains the list of the files in your system. Imagine you need to edit a file whose name is "password" but you can't remember its path. A quick way to do it would be :

luangsay@ramiro:/tmp$ locate -r password$
/etc/pam.d/common-password
/home/luangsay/personnel/password
/mnt/chroot/usr/share/doc/cdialog-0.9b/samples/password
/usr/share/doc/dialog/examples/password
/usr/share/pam/common-password
/var/lib/pam/password

(-r switch is for using regular expressions).
To initialize the database or update it, you can use the command updatedb. Normally, your operating system does it with a cron task.

The second database is apropos, a french word which means "about". This is a collection of the first description sentences (the whatis sentence) of all the man pages. To know all the manpages that deal with the configuration of passwords, you would type :

luangsay@ramiro:/tmp$ apropos -s 5 password
login.defs (5) - configuration de la suite des mots de passe cachés shadow password
passwd (5) - the password file
shadow (5) - encrypted password file
smbpasswd (5) - The Samba encrypted password file

Here, to create/update the database, you will use makewhatis (or mandb in Debian).

Another interesting database that is less known is perlindex. Quite usefull because perl is (still...) used by many system admins. This tool indexes all your perl modules documentation pages. So, to find all the modules installed on your system that deal with LDAP, you can type:

luangsay@ramiro:/tmp$ perlindex ldap
1 1.791 share/perl5/URI/ldap.pm
2 0.317 share/perl5/URI.pm
3 0.137 share/perl/5.10.0/pod/perlhpux.pod

Highest mark (1.791) gives you the most relevant page. If you pulse 1, you'll get the perldoc page printed. Of course, such database isn't as big as the CPAN one, but it proved to have helped me quite a few times in the past.

Finally, I would like to say a word about maybe my prefered database. I mean, the debian packages database. Debian apt-get system is a lot more faster than redhat yum software and it offers you many more packages. I believe every debian admin knows the power of the "apt-cache search/show" commands so I won't waste my time giving an example. For those who don't have a debian based distribution, you can get an idea of this database on this web page. If you have to install a new solution on your servers, and you don't know what software can handle it, the debian package can give you a precious help to get the information. Even if you don't have debian, it can be very usefull.