I started mucking around with DBD::SQLite. SQLite, as you probablyknow, is an embeddable public domain SQL system with ACID support. Matt S
has created a Perl interface to it that includes the SQLite source. Installing
DBD::SQLite from cpan is easy:
sudo perl -MCPAN -e 'install DBD::SQLite'
SQLite version 2 is a mostly typeless database system. That is, most
everything appears to be stored as ASCII. This makes your CREATE TABLE
statements just for documentation. However, those that like MySQL’s
autoincrement column type are not foresaken. In SQLite, this feature is
accomplished with “INTEGER PRIMARY KEY.”
Good.
Here’s my little perl script to generate two tables, populate them with
data and then create a DBI-like shell to interact with it. This is a good
introduction to SQLite, I think.
!/usr/bin/perl — --cperl--
Try out SQLite
use strict; use DBI; use Term::ReadLine;
my %sql = (create_companies => q[ CREATE TABLE companies ( id INTEGER PRIMARY KEY, name char(64), revenue int ); ], create_contacts => q[CREATE TABLE contacts ( id INTEGER PRIMARY KEY, co_id int, first char(64), last char(64), title char(64) ); ], ); my $companies = [ {name => ‘ABC. Corp’, revenue=> ‘5’}, {name => ‘DEF. Corp’, revenue=> ‘10’}, {name => ‘Arbusto’, revenue=> ‘100’}, ];
my $contacts = [ {co_id => 1, first => ‘Sam’, last =>’Houston’, title => ‘CEO’}, {co_id => 1, first => ‘Tam’, last =>’Bouston’, title => ‘VP’}, {co_id => 1, first => ‘Lam’, last =>’Rouston’, title => ‘COO’}, {co_id => 2, first => ‘Tim’, last =>’Dallas’, title => ‘CEO’}, {co_id => 2, first => ‘Rim’, last =>’Malice’, title => ‘VP’}, {co_id => 3, first => ‘George’, last =>’Bush’, title => ‘CEO’}, ];
my $dbh = DBI->connect(“dbi:SQLite:dbname=try.db”) || die “connect: $DBI::errstr\n”;
print “Creating companies\n”; $dbh->do(“drop table companies”); $dbh->do($sql{“create_companies”});
my $sql = q[INSERT INTO companies (name, revenue) VALUES (?,?)]; my $sth = $dbh->prepare($sql); for my $r (@{$companies}) { unless ($sth->execute($r->{name},$r->{revenue})) { warn(“ERROR - ‘$sql’ : “, $sth->errstr, “\n”); } }
$dbh->do(“drop table contacts”); $dbh->do($sql{“create_contacts”}); $sql = q[INSERT INTO contacts (co_id,first,last,title) VALUES (?,?,?,?)]; $sth = $dbh->prepare($sql); for my $r (@{$contacts}) { unless ($sth->execute($r->{co_id},$r->{first},$r->{last},$r->{title})) { warn(“ERROR - ‘$sql’ : “, $sth->errstr, “\n”); } }
print “Going to SQL shell mode\n”;
my $T = Term::ReadLine->new(“SQLite Shell”); my $Out = $T->OUT || *STDOUT;
while (defined($_ = $T->readline(“SQL> “))) { chomp($_);
last if /^\s*qu?i?t?$/i;
$T->addhistory($_) if /\S/;
my $sth = $dbh->prepare($); if ($sth->execute) { $sth->dumpresults(35,”\n”,” | “,); } else { print “WARN - ‘$_’: “, $sth->errstr, “\n”; }
}
$dbh->disconnect;