Latest News

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->dump_results(35,"\n"," | ",); } else { print "WARN - '$_': ", $sth->errstr, "\n"; } } $dbh->disconnect;

[Original use.perl.org post and comments.]