Defindit Docs and Howto Home

This page last modified: Sep 01 2009
title:SQLite hints and Perl DBI mini-howto.
keywords:sqlite,sql,database,dbi,dbd,perl,postgres,postgresql,install,upgrade,db,feature,advanced,html,export,table,spreadsheet,date,conversion,unix
description:Speed hints, examples SQLite commands, and Perl DBI/DBD examples.

Table of contents
-----------------
Docs link to sqlite3 command line interface (CLI)
SQLite3 import speed hints
Install SQLite and Perl DBI/DBD
How to check Perl DBI and Perl DBD drivers
Perl and SQL example


Docs link to sqlite3 command line interface (CLI)
-------------------------------------------------

I've occasionally had trouble finding the SQLite sqlite3 command line
docs:

http://www.sqlite.org/sqlite.html

It isn't a client, but is analagous to PostgreSQL psql client CLI. 




SQLite3 import speed hints
--------------------------

1) Use a pre-existing db file large enough for your db. SQLite expands
the database somewhat slowly, and it does not seem to try to predict
how much space you might need. It would be tricky to see into the
future. The simple solution is to create a database with a dummy
table such that the SQLite database is large enough to hold your
data. Drop the table and do not vacuum the database. Copy the now
empty database file and insert into it. Too large is better than too
small, assuming you've got plenty of disk. It will take a while to
initially expand the database, but you only need to do that once (or
only need to do it occasionally). This assumes that your import tasks
involve a known quantity of data.

2) Use begin/commit around the all the SQL, or at least around maybe
every 100K insert statements. Notice that SQLite database dumps are
wrapped in a transaction. The first line of the dump is "begin
transaction;" and the last line is "commit;" In addition to the usual
sanity gained by using a transaction, this greatly improves speed. In
some cases the speed improvement is greater than 5000 times. When
sqlite3 runs SQL not wrapped in a transaction, each statement will be
treated as a transaction. Each statement incurs the commit overhead.

3) Modify the .sql by putting a select 'xx inserts done'; every 10000
inserts (more or less) to give you an idea of the file import
status. I wrote a small Perl script that inserts a select after every few
thousand insert statements. The Perl script counts the inserts, and
the select serves merely to get SQLite to print a message to stdout.

See the code below item 4.

4) As of 2009 and SQLite 3.6.12, sqlite3 can do 1217874 inserts in 34
seconds on a middle of the road server using the suggestions above.

Usage:

cat dump.sql | sqlite_add_status.pl > dump_status.pl

--
#!/usr/bin/perl

# sqlite_add_status.pl This probably works with most SQL. Naively
# assumes that "insert" is at the beginning of the line and followed
# by some whitespace and "into".

use strict;

# Add a select status statement every $freq number of inserts.

my $freq = 50000;

main:
{

    # Read in a .sql file. Don't change any exising lines, but insert
    # a status select statement after every $freq insert statements.

    my $insert_count = 0;

    while(my $line = <>)
    {
        print $line;
        if ($line =~ m/^insert\s+into/i)
        {
            $insert_count++;

            if (($insert_count % $freq) == 0)
            {
                print "select '$insert_count done';\n";
            }
        }
    }
}
--


Install SQLite and Perl DBI/DBD
-------------------------------

For Fedora Core 4, perl-DBD-SQLite is in the "extras" repository.

yum -y install perl-DBD-SQLite


The cpan page says the Perl DBD module includes
a full sqlite distro. The documentation is at:
http://search.cpan.org/~msergeant/DBD-SQLite-1.13/lib/DBD/SQLite.pm


SQL syntax:
http://www.sqlite.org/lang.html

sqlite command line:
http://www.sqlite.org/sqlite.html


sqlite3 mydate.db
.read schema.sql


SQLite has no date or time data types. If you need dates, I strongly
suggest that you use PostgreSQL.



How to check Perl DBI and Perl DBD drivers
------------------------------------------

Here is a Perl script drivers.pl. Run this to determine which Perl DBI
drivers are present on your system:


#!/usr/bin/perl
use DBI;
@driver_names = DBI->available_drivers();
print "driver_names (apparently installed, available):\n";
foreach my $dn (@driver_names)
{
    print "$dn\n";
}





Perl and SQL example
--------------------


Here is a trivial example of a table schema SQL file suitable for use
with SQLite via the .read command. I recomment that you put your table
creation commands as well as everything else necesary to create the
"empty" database into a file named schema.sql

create table query (
        query_id integer,
        query_name text,
        query_date text
);


Run the SQLite command line (sqlite3) and enter the .read command as
follows:


sqlite3 demo.db
.read schema.sql


Below is a tiny example of Perl and SQLite, with transactions,
inserting data into a database. The file demo.db contains the
database.


#!/usr/bin/perl

use DBI;
use strict;

main:
{
    my $dbargs = {AutoCommit => 0,
                  PrintError => 1};

    my $dbh = DBI->connect("dbi:SQLite:dbname=demo.db","","",$dbargs);

    $dbh->do("insert into query (query_id, query_name,query_date) values (1,'stuff','2006-11-06')");

    if ($dbh->err()) { die "$DBI::errstr\n"; }

    $dbh->commit();
    $dbh->disconnect();
}