About the Perl DBI

Perl's Database Interface uses a database abstraction model, so it doesn't really matter what the underlying database is. The DBI module calls the appropriate Database Driver module and passes off the SQL commands for execution.

There are many more DBD modules than you are ever likely to use, supporting all the major database systems and many of the minor ones. The naming convention is DBD::dbname, for example DBD::Oracle for Oracle or DBD::Sybase for Sybase (and SQL Server). There's also DBD::ODBC for other ODBC-enabled databases that don't have their own specific DBD module and even DBD::CSV to interact with comma-separated-values files using DBI.

The DBD module we are interested in is called DBD::SQLite2, and the way in which queries from your Perl script interact with DBI and the SQLite DBD driver is shown in Figure 7.1.

Figure 7.1. The Perl Database Interface model.

The DBI module is loaded into your script with this simple command:

 use DBI; 

You do not need to use any specific DBD module as DBI will take care of that when it is needed. This makes it easy to write highly portable database applications in Perl because only a single instruction needs to be changed to tell DBI to work with a different database. The instruction looks like this for SQLite:

 $dbh = DBI->connect("DBI:SQLite2:dbname=dbfile", "", ""); 

The prototype for DBI->connect() has three parametersa data source, username, and password. As SQLite does not use user-based authentication, the second and third parameters are always blank.

The data source contains three parts separated by colons: the keyword DBI, the database typein this case SQLiteand an expression indicating the name of the database. The filename given as dbfile can include an absolute or relative path or will be opened from the current working directory if no path is given.

Getting Information About the DBI

The DBI module will report back its supported DBD drivers through the available_drivers() method. The script in Listing 7.1 grabs the available drivers as an array and loops through to print each entry to screen.

Listing 7.1. Using available_drivers() to Check What DBD Modules Are Installed

 #!/usr/bin/perl -w use DBI; use strict; my @drivers = DBI->available_drivers(); foreach my $driver (@drivers) {   print "$driver \n"; } 

The output will look similar to this:

 # ./listing7.1.pl CSV DBM ExampleP File Proxy SQLite Sponge 

Your output may be different if you have additional DBD modules already installed on your system. Of those shown in the preceding example, only CSV, DBM, and SQLite are actual database drivers. ExampleP is a simple example of a driver that can be used to help write new DBD drivers. The others are drivers that perform internal operations shared by other DBD drivers.

DBI also provides the installed_versions method, which will give a formatted report of both the supported DBD modules and their installed versions as well as details of the Perl and operating-system versions. This method can easily be called from the command line as follows:

 $ perl -MDBI -we 'DBI->installed_versions()'   Perl            : 5.008003    (i386-linux-thread-multi)   OS              : linux       (2.4.21-4.elsmp)   DBI             : 1.43   DBD::Sponge     : 11.10   DBD::SQLite2    : 0.32   DBD::Proxy      : 0.2004   DBD::File       : 0.31   DBD::ExampleP   : 11.12   DBD::DBM        : 0.02   DBD::CSV        : 0.21 

Using DBD Drivers

Let's look at how the same query can be performed using two different DBD modules by simply changing the DBI->connect() instruction. Listing 7.2 shows a script that will connect to a SQLite database, create a very simple table, and insert a few rows of data.

Listing 7.2. Creating Some Simple Data Records Using Perl DBI

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", ""); my $sql = "CREATE TABLE mytable (mynumber INTEGER)"; $dbh->do($sql); for (my $i=1; $i<=5; $i++) {   $sql = "INSERT INTO mytable VALUES ($i)";   $dbh->do($sql); } 

The DBI->connect() call opens up a database called perldb from the current working directory. Unless you already have a database by this name in that directory, a new file will be created.

 $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", ""); 

To send an SQL statement to be processed by DBI, we use the do() method on a database handle.

 $dbh->do($sql); 

Running the script in Listing 7.2 will create a new database file called perldb (unless one already existed, of course), and we can view the schema of the new table and view the rows that were inserted.

 $ sqlite perldb SQLite version 2.8.15 Enter ".help" for instructions sqlite> .schema CREATE TABLE mytable (mynumber INTEGER); sqlite> SELECT * FROM mytable; 1 2 3 4 5 

Now let's try the same thing using a different DBD module. One of the simplest, although crudest, ways to store data to the filesystem is in a comma-separated-values file, and Perl can use a DBD module to communicate with CSV files using an SQL interface. If your system does not already have it, install the DBD::CSV module using cpan.

 cpan> install DBD::CSV 

To adapt the script from Listing 7.2 to save data to comma-separated files rather than SQLite, simply change the DBI->connect() instruction so that it reads as follows:

 $dbh = DBI->connect("DBI:CSV:f_dir=/tmp", "", ""); 

The data source for the CSV driver has an optional assignment, f_dir, for the output directory and uses the current directory if this is not given. One CSV file is written per table, rather than one file for the entire database in SQLite. The operation is successful in creating a file called mytable containing the same data rows as before, as you can see by examining the new file created in /tmp.

 $ cat /tmp/mytable mynumber 1 2 3 4 5 

Though the way the data files are written is different with the CSV driver, the same SQL code that we wrote to create tables in SQLite will also create a CSV if the data source is changed. The same procedure can be used for any supported DBD module, whether it is a filesystem-based database or a client/server RDBMS.

For the remainder of this chapter we will look at examples with DBD::SQLite2, but do remember that the Perl interface for SQLite is not specific to SQLite. Indeed you may already have an existing Perl application that uses DBI with a different database that could easily be ported to SQLite by simply changing the DBD module in the data source.