Tuesday, November 17, 2009

SQLite Database

If you have been using CSV/flat file as a persistent storage for your scripts, you should really checkout, SQLite. It gives you the power of RDBMS while without the complexity that comes with it. Any SQLite database you create is nothing but a file. It provides locking, transaction support, joins, etc. With ".dump" command, it can generate the SQL commands to reproduce the whole schema.

Did I mention, this database format is supported by ActivePerl, by default? You can use the standard Perl DBI module to manage this database.

Also, there is a CLI tool called sqlite3 (~500K) that lets you run all database manipulation and SQL commands. And, if it is good enough for Google Android and Apple Safari, chances are it should be robust enough for my needs.

Here is a simple Perl script to access this database from Perl.


use DBI;

my $dbh = DBI->connect('dbi:SQLite:dbname=sample.db',',');

$sql = qq{ CREATE TABLE MYCERT ( num int not null, name varchar(20) ); };
$sql = qq{ INSERT INTO MYCERT VALUES(1, 'ITM'); };
$sql = qq{ INSERT INTO MYCERT VALUES(2, 'Omnibus'); };


No comments: