Tuesday, March 16, 2010

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
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;

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.

Saturday, March 6, 2010

perl debugger

Perl is (still) a popular scripting language in system administration but it seems to me that few administrators know it has a debugger. I believe that it is a bit sad because this debugger is quite powerful and it can ease your job a lot at writing script and enable you to do it faster. So this article is a short introduction to this useful tool.

To explain how to use the debugger, we need a sample script. Here is a very basic one :
luangsay@ramiro:/tmp$ cat foo.pl
#!/usr/bin/perl -w

use strict;

my $bar = {};

sub myFunc {
$bar->{'one'} = 1;
$bar->{'two'} = 2;
print $bar

sub callFunc {


One of the feature I do like, is that the debugger is embedded in the perl interpreter (not like with python, which needs the pdb program). To activate the debug mode, just call the interpreter with the d switch :
luangsay@ramiro:/tmp$ perl -d ./foo.pl

Loading DB routines from perl5db.pl version 1.3
Editor support available.

Enter h or `h h' for help, or `man perldebug' for more help.

main::(./foo.pl:5): my $bar = {};

First thing you can do to learn the debugger is looking at the help :
DB<1> h
List/search source lines: Control script execution:
l [ln|sub] List source code T Stack trace
- or . List previous/current line s [expr] Single step [in expr]
v [line] View around line n [expr] Next, steps over subs
f filename View source in file Repeat last n or s

If you have a very complicated script, it may be useful to list the loaded perl modules :
DB<1> M
'Carp.pm' => '1.08 from /usr/share/perl/5.10/Carp.pm'
'Carp/Heavy.pm' => '/usr/share/perl/5.10/Carp/Heavy.pm'
'Config.pm' => '/usr/lib/perl/5.10/Config.pm'
'Config_heavy.pl' => '/usr/lib/perl/5.10/Config_heavy.pl'
'Exporter.pm' => '5.62 from /usr/share/perl/5.10/Exporter.pm'
'IO.pm' => '1.23_01 from /usr/lib/perl/5.10/IO.pm'
'IO/Handle.pm' => '1.27 from /usr/lib/perl/5.10/IO/Handle.pm'
To view some lines in the script, we can use the l command :
DB<1> l
5==> my $bar = {};
7 sub myFunc {
8: $bar->{'one'} = 1;
9: $bar->{'two'} = 2;
10: print $bar
11 }
13 sub callFunc {
14: myFunc()
We can see that the debugger is ready to execute the first instruction of the script at line 5. To execute this instruction, we type n:
DB<1> n
main::(./foo.pl:17): callFunc()
Now, we are about to call the callFunc function. If we want the debugger to execute just one step inside this funcion, we must use the s command :
DB<4> s
main::callFunc(./foo.pl:14): myFunc()

Let's say now that we want to know the value of the bar variable before printing it. We must therefore execute the script till line number 10 :
DB<5> c 10
main::myFunc(./foo.pl:10): print $bar
DB<6> v
7 sub myFunc {
8: $bar->{'one'} = 1;
9: $bar->{'two'} = 2;
10==> print $bar
11 }
13 sub callFunc {
14: myFunc()
15 }
We have just entered in another function. The T command gives us a listing of all the function calls that were made to get there :
DB<9> T
. = main::myFunc() called from file `./foo.pl' line 14
. = main::callFunc() called from file `./foo.pl' line 17

To view the value of a variable, you may use the p command. But as $bar is a reference of a hash, it proves to be better to do a dump of the variable with the x command. This latter command is very useful for instance to know the state of a huge perl object.
DB<6> p $bar
DB<7> x $bar
0 HASH(0x82496b8)
'one' => 1
'two' => 2

The last feature I would like to explain, is the ability to run perl commands directly in the debugger. This enables you to change values on the fly or to do some tests . For instance, we can load another module to get the time or change the value of $bar->{'two'} :
DB<15> use HTTP::Date;
DB<24> p time2str($time);
Sat, 06 Mar 2010 13:08:40 GMT
DB<25> $bar->{'two'} = 3;
DB<31> x $bar
0 HASH(0x824bfd0)
'one' => 1
'two' => 3

I hope this introduction will make you want to discover a bit more the perl debugger!