All the Perl that's Practical to Extract and Report
use Perl Log In
jjohn (22)
(email not shown publicly)
http://taskboy.com/
AOL IM: taskboy3000 (Add Buddy, Send Message)
Perl hack/Linux buff/OSS junkie.
SQLite - the wettening
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;
glancing at the cover periodically to make sure it isn't a book on
software design.
--Bruce Tognazzini
I've been using it (Score:2)
Between SQLite for the low end, and PostgreSQL for the high-end, there's really no need to use the crippled MySQL for any new installations. Joy.
Re:I've been using it (Score:1)
I've skimmed over both of them slightly, and have no real opinions either way, but I don't feel like heading down the wrong track and having to back up and start over.
Re:I've been using it (Score:2)
Another showstopper is the licensing. Even the MySQL website advocates a commercial license if you're doing any sort of money making with MySQL. On the other hand, PostgreSQL is completely free, being under the free'er-than-GPL BSD license. Since I work mainly with commercial clients, this can make a diffe
Re:I've been using it (Score:2)
Re:I've been using it (Score:1)
But no support for Windows. That is a showstopper for PostgreSQL. It is "coming" I know. I think FirebirdSQL is really good for high end stuff *and* it is cross-platform.
I really like SQLite, as you can do some quick and dirty stuff with it.
Re:I've been using it (Score:2)
As near as I can tell, PostgreSQL has been installed on my last three windows machines at work, under Cygwin. I have never used it, however.
J. David works really hard, has a passion for writing good software, and knows many of the world's best Perl programmers
Re:I've been using it (Score:1)
I use Informix and Oracle at work, and would like a GPL database that is close to how they work.
Sounds like PostgreSQL might be a much better fit.
Thanks!
Re:I've been using it (Score:2)
See also: X is better than Y [perl.org]
I'm now adding a new reason to the list of reasons to use MySQL: "You're not associating with PostgreSQL zealots".
Docs (Score:2)
Re:Docs (Score:1)
Would you mind if I add this script to the docs as a getting started example?
You're absolutely welcome to use this code or a modified version of it. I think the Term::ReadLine stuff might confuse some people. Maybe not. I've only recently come to enjoy the wonders of this module.
I'm surprised just how compliant SQLite is. It seemed to handle natural joins well. I didn't try LIKE statements, but I've got a larger dataset with which to experiment.
SQLite appears to be an excellent solution for those s