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;
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
jdavidboyd on 2004-07-14T13:51:46
Without getting all long and drawn out here, do you have some previous postings to point to so I can understand what leads you to state that MySQL is bad and PostgreSQL is good?
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
merlyn on 2004-07-14T14:13:23
This might be slightly dated, but check out MySQL Gotchas. I consider the lack of reporting that data is too large for the storage to be showstopping, personally, even if the rest of it weren't true.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 difference.
Also, large-database style features like views, subselects, foreign key referential integrity, and transactions, are just being added to MySQL, with the subsequent bug shakeout and optimization still early in the cycle. PostgreSQL has had those for years.
Don't get me wrong. MySQL was great in its day. But its day has passed (except for legacy apps and brainspace). PostgreSQL is the full-featured database of choice for me and my customers.
Re:I've been using it
merlyn on 2004-07-14T14:14:02
Hmm. What happened to my link: MySQL Gotchas.Re:I've been using it
zatoichi on 2004-07-14T16:11:49
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
jdavidb on 2004-07-16T21:19:22
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.
Re:I've been using it
jdavidboyd on 2004-07-14T16:20:16
Hmm, these are good things to know.
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
gav on 2004-07-16T13:03:40
It's sad that you have nothing better to do than be our resident anti-MySQL troll.
See also: X is better than Y
I'm now adding a new reason to the list of reasons to use MySQL: "You're not associating with PostgreSQL zealots".
Re:Docs
jjohn on 2004-07-14T11:43:01
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 standalone perl applications that need a robust data store.
MySQL is still my preference for larger web application applications, mostly because of its speed, admin tools and developer mind share. I flirted with PostgreSQL, but found it a bit slow and ackward to maintain (how's that for flamebait?
:-) Thanks for popularizing and extending DBD::SQLite, Matt.