Recipe 14.18 Using SQL Without a Database Server

14.18.1 Problem

You want to make complex SQL queries but don't want to maintain a relational database server.

14.18.2 Solution

Use the DBD::SQLite module from CPAN:

use DBI;

$dbh = DBI->connect("dbi:SQLite:dbname=/Users/gnat/salaries.sqlt", "", "",
                    { RaiseError => 1, AutoCommit => 1 });

$dbh->do("UPDATE salaries SET salary = 2 * salary WHERE name = 'Nat'");

$sth = $dbh->prepare("SELECT id,deductions FROM salaries WHERE name = 'Nat'");
# ...

14.18.3 Discussion

An SQLite database lives in a single file, specified with the dbname parameter in the DBI constructor. Unlike most relational databases, there's no database server here—DBD::SQLite interacts directly with the file. Multiple processes can read from the same database file at the same time (with SELECTs), but only one process can make changes (and other processes are prevented from reading while those changes are being made).

SQLite supports transactions. That is, you can make a number of changes to different tables, but the updates won't be written to the file until you commit them:

use DBI;
$dbh = DBI->connect("dbi:SQLite:dbname=/Users/gnat/salaries.sqlt", "", "",
                    { RaiseError => 1, AutoCommit => 0 });
eval {
  $dbh->do("INSERT INTO people VALUES (29, 'Nat', 1973)");
  $dbh->do("INSERT INTO people VALUES (30, 'William', 1999)");
  $dbh->do("INSERT INTO father_of VALUES (29, 30)");
  $dbh->commit( );
};
if ($@) {
      eval { $dbh->rollback( ) };
      die "Couldn't roll back transaction" if $@;
}

SQLite is a typeless database system. Regardless of the types specified when you created a table, you can put any type (strings, numbers, dates, blobs) into any field. Indeed, you can even create a table without specifying any types:

CREATE TABLE people (id, name, birth_year);

The only time that data typing comes into play is when comparisons occur, either through WHERE clauses or when the database has to sort values. The database ignores the type of the column and looks only at the type of the specific value being compared. Like Perl, SQLite recognizes only strings and numbers. Two numbers are compared as floating-point values, two strings are compared as strings, and a number is always less than a string when values of two different types are compared.

There is only one case when SQLite looks at the type you declare for a column. To get an automatically incrementing column, such as unique identifiers, specify a field of type "INTEGER PRIMARY KEY":

CREATE TABLE people (id INTEGER PRIMARY KEY, name, birth_year);

Example 14-6 shows how this is done.

Example 14-6. ipk

  #!/usr/bin/perl -w
  # ipk - demonstrate integer primary keys
  use DBI;
  use strict;
  my $dbh = DBI->connect("dbi:SQLite:ipk.dat", "", "",
  {RaiseError => 1, AutoCommit => 1});
  # quietly drop the table if it already existed
  eval {
    local $dbh->{PrintError} = 0;
    $dbh->do("DROP TABLE names");
  };
  # (re)create it
  $dbh->do("CREATE TABLE names (id INTEGER PRIMARY KEY, name)");
  # insert values
  foreach my $person (qw(Nat Tom Guido Larry Damian Jon)) {
    $dbh->do("INSERT INTO names VALUES (NULL, '$person')");
  }
  # remove a middle value
  $dbh->do("DELETE FROM names WHERE name='Guido'");
  # add a new value
  $dbh->do("INSERT INTO names VALUES (NULL, 'Dan')");
  # display contents of the table
  my $all = $dbh->selectall_arrayref("SELECT id,name FROM names");
  foreach my $row (@$all) {
    my ($id, $word) = @$row;
    print "$word has id $id\n";
  }

SQLite can hold 8-bit text data, but can't hold an ASCII NUL character (\0). The only workaround is to do your own encoding (for example, URL encoding or Base64) before you store and after you retrieve the data. This is true even of columns declared as BLOBs.

14.18.4 See Also

Recipe 14.9; the documentation for the CPAN module DBD::SQLite; the SQLite home page at http://www.hwaci.com/sw/sqlite/