Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jjohn (22)

jjohn
  (email not shown publicly)
http://taskboy.com/
AOL IM: taskboy3000 (Add Buddy, Send Message)

Perl hack/Linux buff/OSS junkie.

Journal of jjohn (22)

Tuesday July 13, 2004
05:20 PM

SQLite - the wettening

[ #19831 ]

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;

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • I really enjoy using SQLite, especially since all it takes from Perl is install DBD::SQLite, and the whole database gets embedded inside the DBD handler!

    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.

    --
    • Randal L. Schwartz
    • Stonehenge
    • 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.
      • 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 diffe

        --
        • Randal L. Schwartz
        • Stonehenge
        • Hmm. What happened to my link: MySQL Gotchas [sql-info.de].
          --
          • Randal L. Schwartz
          • Stonehenge
        • 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.

          • 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
        • 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!
    • It's sad that you have nothing better to do than be our resident anti-MySQL troll.

      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".
  • Would you mind if I add this script to the docs as a getting started example? One of the regular complaints I get is people saying while it's trivial to install, they still have no idea how to use it.
    • 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