Small. Fast. Reliable.
Choose any three.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11. The information shown here has not been updated since that cut-over. These pages are retained for historical reference only.

Perl and DBD::SQLite notes

Temporary databases

  my $dbh = DBI->connect('dbi:SQLite:dbname=:memory:');  	# in-memory database
  my $dbh = DBI->connect('dbi:SQLite:'); 			# temporary database file created in /var/tmp

Defining a REGEXP function

  $dbh->func('regexp', 2, sub { return ($_[1] =~ m/$_[0]/) ? 1 : 0; }, 'create_function');

Warning "Closing dbh with active statement handles"

The way to hide this warning is to undefine any prepared SQL statements, just after calling finish() on them, and before disconnecting the database:

  $stmt->finish();
  undef $stmt; # otherwise we get a warning for active statement at close time.
  $dbh->disconnect();

Compiling new SQLite versions into the DBI driver

DBD::SQlite is not frequently updated (the last time was 2007), but there is another option at DBD::SQLite-Amalgamation. 'Amalgamation' is not updated very often either, but it is (intentionally) easy to use it to build a new DBD::SQLite using the latest SQLite source. It will replace DBD::SQlite when installed.

(Note there is a script in the DBD-SQLite-Amalgamation source that is supposed to do this for you, but it never worked for me.)

  1. Fetch latest from http://search.cpan.org/dist/DBD-SQLite-Amalgamation/
  2. Fetch latest SQLite Amalgamation source
  3. Unpack the DBD source tar
  4. Unpack the .h files from the SQLite source and copy into the DBD directory
  5. Replace sqlite-amalgamation.c in the DBD source directory with sqlite3.c from the SQLite source
  6. Follow the regular Perl make/install procedure for the DBD source package

Example updating DBD-SQLite-Amalgamation-3.6.1.2 to use SQLite 3.6.11:

  1. wget http://search.cpan.org/CPAN/authors/id/A/AU/AUDREYT/DBD-SQLite-Amalgamation-3.6.1.2.tar.gz
  2. wget http://www.sqlite.org/sqlite-amalgamation-3.6.11.tar.gz
  3. tar -xzf DBD-SQLite-Amalgamation-3.6.1.2.tar.gz
  4. tar -x sqlite-3.6.11/sqlite3ext.h -x sqlite-3.6.11/sqlite3.h -x sqlite-3.6.11/sqlite3.c -z -f sqlite-amalgamation-3.6.11.tar.gz
     cp sqlite-3.6.11/*.h DBD-SQLite-Amalgamation-3.6.1.2/
  5. cp sqlite-3.6.11/sqlite3.c DBD-SQLite-Amalgamation-3.6.1.2/sqlite-amalgamation.c
  6. cd DBD-SQLite-Amalgamation-3.6.1.2
     perl Makefile.PL
     make
     sudo make install

Your DBD::SQLite will now use SQLite 3.6.11. FTS3 is included by default as of 3.5.8.