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.

#!/usr/bin/perl -w

use DBI;
use strict;

my @drivers = DBI->available_drivers();
foreach my $driver (@drivers) {
  print "$driver 
";
}

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

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.

#!/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.

As we saw before, the DBI->connect() function is used to open a database, and for a SQLite database no username or password arguments are required. The usage is always as follows:

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

Some basic error trapping is useful in case the connection to the database fails. Because SQLite will create a new database file with the given name if one does not exist, DBI->connect() will only fail if there is an I/O error, for instance file permissions not allowing the file to be opened or created in a particular directory, or no more disk space on the device.

The errstr property contains the most recent database error. The following example could be used to exit with an error message if SQLite is unable to open the specified dbfile:

$dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
       or die("Error: " . DBI::errstr);

To close the connection to a database, simply invoke the disconnect() method on your DBI object.

$dbh->disconnect();

We have already seen that the do() method can be used to pass SQL to the DBI module in order to execute a command. In fact, do() is good only for non-SELECT statements and the preferred method to send SQL commands to the DBI module is using a two-step process. do() is a convenience function that effectively combines these two steps.

First the query needs to be prepared by the SQL engine using prepare, after which it can be executed using the execute method.

Listing 7.3 creates the contacts table in SQLite with error trapping at every stage. The errstr property returns the most recent error message generated by the DBD, so any cause of error that causes the script to exit prematurely will be displayed to the screen.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $sql = "CREATE TABLE contacts (   ".
          "  id INTEGER PRIMARY KEY, ".
          "  first_name TEXT,        ".
          "  last_name TEXT,         ".
          "  email TEXT)             ";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

my $ret = $sth->execute()
          or die("Cannot execute: " . DBI::errstr() );

The prepare() method returns a statement handle object, which in turn can invoke the execute() method to perform the query.

$sth = $dbh->prepare($sql);
...
$ret = $sth->execute();

A powerful feature of DBI is that an SQL statement still needs to be prepared only once, even if it is to be executed repeatedly with different values in the statement. This is achieved using placeholders in the SQL and an array of bind values passed as an argument to execute() and gives a significant performance saving over preparing a query with static values each time it is executed.

A placeholder is indicated by the question mark symbol. For example, the following query could be prepared to create an SQL INSERT statement where the placeholders indicate that we will supply the values to be inserted at execution time. Note that no quotes are required around a placeholder even if a string value is the expected substitution.

INSERT INTO contacts (first_name, last_name, email) VALUES (?, ?, ?)

Listing 7.4 shows how a record is inserted using this technique.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $sql = "INSERT INTO contacts (first_name, last_name, email) ".
          "VALUES (?, ?, ?)";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() ); 

$sth->execute('Chris', 'Newman', '[email protected]')
       or die("Cannot execute: " . DBI::errstr() );

The execute() method takes one argument for each placeholder in the order specified in the query. We can verify that the record was inserted as expected using the sqlite tool.

$ sqlite perldb
SQLite version 2.8.15
Enter ".help" for instructions
sqlite> SELECT * FROM contacts;
id  first_name  last_name   email
--  ----------  ----------  --------------------
1   Chris       Newman      [email protected]

Let's extend this a little further to create a script that allows you to populate the contacts table by entering records from the keyboard.

The script in Listing 7.5 creates a loop that reads user input that is expected to be the three elements first_name, last_name, and email separated by commas. If the format of the input is valid, the record is inserted into the database; otherwise, the script will exit.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr);

my $sql = "INSERT INTO contacts (first_name, last_name, email) ".
          "VALUES (?, ?, ?)";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr); 

while(<>) {
  chomp;
  my ($first_name, $last_name, $email) = split /,/;

  if (!$email) {
    print "Required format: first_name, last_name, email
";
    next;
  }

  $sth->execute($first_name, $last_name, $email)
         or die("Cannot execute: ". DBI::errstr() );

  print "Inserted: $first_name, $last_name, $email
";

}

The key part of this script is the while loop, which reads from standard input. Input is taken from stdin a line at a time, and we use chomp to strip the trailing newline characters.

while(<>) {
  chomp;

The expected data format is a comma-separated list of the three elements to be inserted into contacts. The split instruction breaks up the read line of input and the first three comma-separated elements are assigned to $first_name, $last_name, and $email respectively.

my ($first_name, $last_name, $email) = split /,/;

To validate the data record we test that a value has been assigned to $email. If there are less than three comma-separated values in the input, $email will be empty. If there are more than three elements, the fourth and subsequent elements are simply discarded.

if (!$email) {
  print "Required format: first_name, last_name, email
";
  next;
}

If everything is okay, a row is inserted by calling the execute() method on the prepared SQL statement with the assigned variables.

$sth->execute($first_name, $last_name, $email)
       or die("Cannot execute: ". DBI::errstr );

The bind_param() function is used to set bind parameter values individually and provides a little more functionality than supplying a list of bind values to the execute() method by allowing you to give a data type hint.

In fact when bind parameters are passed to the execute() method, the DBI effectively calls bind_param() for each value in the list. The data type is assumed to be SQL_VARCHAR.

The syntax of bind_param() is as follows:

$sth->bind_param($p_num, $bind_value, $bind_type)

The parameters are numbered in the order they appear in the SQL starting at 1, and the number of the parameter you want to assign is passed in the $p_num argument.

The only data type constants appropriate for SQLite are SQL_VARCHAR and SQL_INTEGER. In order to use these constants in your script you must import them with the use command:

use DBI qw(:sql_types);

To produce the same result we saw in Listing 7.4 using bind_param(), instead of passing the bind values to execute(), the following lines would be required:

$sth->bind_param(1, $first_name, SQL_VARCHAR);
$sth->bind_param(2, $last_name, SQL_VARCHAR);
$sth->bind_param(3, $email, SQL_VARCHAR);
$sth->execute() or die "Cannot execute: ". DBI::errstr;

Bind values are safe when passed into an SQL statement. For example, the following execution of Listing 7.5 shows that a surname containing an apostrophe does not conflict with the implied quotation marks around that string value.

$ ./listing7.5.pl
Paddy,O'Brien,[email protected]
Inserted: Paddy, O'Brien, [email protected]

Had we performed the same INSERT command using static values in the script, some kind of delimiting would be required to avoid the query looking like this:

INSERT INTO contacts (first_name, last_name, email)
VALUES ('Paddy', 'O'Brien', '[email protected]')

This query would cause an error upon execution as the parser would not be able to determine that the apostrophe in O'Brien was not in fact a closing quotation mark around the string 'O'.

In Perl we can call the DBI method quote() to put a value into the right type of quotes for the underlying database (single quote for DBD::SQLite2) while adding the necessary delimiters to make them safe for use within an SQL command. Listing 7.6 performs the quote method on each of the values in an INSERT statement.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $first_name = "Paddy";
my $last_name = "O'Brien";
my $email = "[email protected]";

my $sql = sprintf("INSERT INTO contacts (first_name, last_name, email) 
".
                                "VALUES (%s, %s, %s)",
                  $dbh->quote($first_name),
                  $dbh->quote($last_name),
                  $dbh->quote($email));

print $sql . "
";

The output from running Listing 7.6 displays the SQLite-safe query text.

$ ./listing7.6.pl
INSERT INTO contacts (first_name, last_name, email)
VALUES ('Paddy', 'O''Brien', '[email protected]')

Remember that SQLite delimits the apostrophe character by doubling it—the occurrence of '' in a string enclosed by single quotes means that the string contains one apostrophe.

When an UPDATE or DELETE operation is performed, it is usually the intention to alter one or more database rows. The number of rows affected by the statement can be found by looking at the rows property of the statement handler.

$sth->execute();
print $sth->rows;

The number of affected rows is also the return value from execute, which provides a handy shortcut to this information. Because the do() method is a convenience function that prepares and executes an SQL command in one go, you can also look at the return value from do() to find the number of affected rows.

For example, after the following statement is executed, $num will contain the number of rows that has been deleted:

$num = $dbh->do("DELETE FROM contacts WHERE first_name = 'Chris'");

When an INSERT is performed on a table with an INTEGER PRIMARY KEY field—as in our previous examples—SQLite assigns the next numerical value to that field if the inserted value is NULL. The SQLite DBD module extends DBI to provide a private method for retrieving this value. It is called as follows:

$id = $dbh->func("last_insert_rowid").

Listing 7.7 performs an UPDATE followed by a INSERT on the contacts table, and displays the number of affected rows and the assigned INTEGER PRIMARY KEY field respectively.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $sql = "UPDATE contacts ".
          "SET first_name = upper(first_name)";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

my $num = $sth->execute or die("Cannot execute: ". DBI::errstr() );

print "Updated " . $sth->rows ." rows 
";

$sql = "INSERT INTO contacts (first_name, last_name, email) ".
       "VALUES ('Bill', 'Williams', '[email protected]')";

$sth = $dbh->prepare($sql)
       or die("Cannot prepare: " . DBI::errstr() ); 

$sth->execute() or die("Cannot execute: ". DBI::errstr() );

print "Inserted with ID " . $dbh->func("last_insert_rowid"). "
";

The following output will be generated, depending on the actual number of records already in the contacts table:

$ ./listing7.7.pl
Updated 4 rows
Inserted with ID 5

Three DBI functions are provided to begin and end SQLite transactions. They map directly to the underlying SQL commands and each is equivalent to issuing the corresponding command shown in Table 7.1.

We have already seen how an INSERT or UPDATE command returns the number of affected rows as the result of the execute method. Now let's take a look at how the result of a SELECT statement is processed using the Perl DBI.

The fetchrow_array() method can be used on an executed statement handler to return a list of the selected column values. The first time it is called, the first row from the query is returned, and on subsequent calls the record pointer is advanced to return the next row in sequence.

Listing 7.8 shows how fetchrow_array() can be used in a loop to output every row from a table.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $sql = "SELECT first_name, last_name FROM contacts";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

$sth->execute() or die("Cannot execute: ". DBI::errstr() );

while( my ($first_name, $last_name) = $sth->fetchrow_array() ) {
  print "$first_name $last_name 
";
}

The while loop assigns two variables from the elements of the array returned by fetchrow_array() for each iteration.

while( my ($first_name, $last_name) = $sth->fetchrow_array() ) {

After the last row of data has been fetched, the while condition is false and the loop exits. The output generated by Listing 7.8 is as follows:

$ ./listing7.8.pl
CHRIS Newman
PADDY O'Brien
BILL Williams

As an alternative to fetchrow_array(), you can use the fetchrow_arrayref() method, which returns a reference to an array containing each row's elements. A while loop to achieve the same result as Listing 7.8 using this method would look like this:

while (my $ref = $sth->fetchrow_arrayref()) {
  print "@{$ref} 
";
}

The data rows can also be returned as a hash, using the fetchrow_hash() method. A reference to a hash is returned where the hash contains column name and value pairs.

while (my $ref = $sth->fetchrow_hashref()) {
  print "$$ref{'first_name'} $$ref{'last_name'}
";
}

The functions that begin fetchrow_ also have corresponding functions that begin fetchall_. The difference, as you might expect, is that the entire dataset returned from the query is fetched all at once, rather than a row at a time.

Listing 7.9 shows how the result fetched by fetchall_arrayref() can be used in Perl.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

my $sql = "SELECT * FROM contacts ORDER BY first_name";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

$sth->execute() or die("Cannot execute: ". DBI::errstr() );

my $ref = $sth->fetchall_arrayref();

foreach my $row (@{$ref}) {
  print "@$row
";
}

The output looks like this, with the columns displayed in the order they appear in the table schema:

$ ./listing7.9.pl
3 BILL Williams bill.com
1 CHRIS Newman [email protected]
2 PADDY O'Brien paddy.com

An optional argument to fetchall_arrayref() can be used to fetch only a subset of the columns returned by the query into the array, known as a slice. The argument should be a hash reference of the column numbers to be returned, where columns are numbered from zero starting with the first in the selected list. Negative numbers can be used to count from the end of the column list.

For example, the following statement would cause only the second and last columns from the query to be put into the array:

$ref = $sth->fetchall_arrayref([1,-1]);

An optional second argument allows the maximum number of rows that will be created in the array to be specified. A non-zero value will restrict the dataset if it is larger than the total number of rows returned by the query, including the LIMIT clause if there is one.

If either argument is not required, it can be specified as undef. The following statement does not slice the array but limits the number of rows returned to 2:

$ref = $sth->fetchall_arrayref(undef, 2);

The fetchall_hashref() method requires an argument that gives the name of the field to be used as the key of the returned hash. The key field argument may be the column name or number.

The following statement calls fetchall_hashref() on contacts using the email column as the key:

$ref = $sth->fetchall_hashref('email'),

Given an email address, we can now use this hash to find other information:

$first_name = $ref->{'[email protected]'}->{'first_name'};
$last_name = $ref->{'[email protected]'}->{'last_name'};

The finish method that can be called on a statement handle is used to destroy the handle when it is no longer needed. It is simply called as follows:

$sth->finish();

However, finish() should only be called when you are done with reading data from the statement handle and know that there is more data still to come. If any of the fetch instructions have come to the end of the dataset, there is no need to call finish().

The error checking we have come across so far has tested the return code of a function and taken action to display the error and exit if necessary. This might look like the following example using a traditional if condition to check for success:

$ret = $sth->execute();
if (!$ret) {
  print DBI::rrstr;
  exit;
}

Or it can be done in the more concise syntax using or die.

$ret = $sth->execute()
       or die("Cannot execute: " . DBI::errstr);

There is an even more concise way to apply error checking to all your database function calls. The Perl DBI implements automatic error checking using two built-in exceptions, PrintError and RaiseError, enabled by setting attributes with those names against the database handle.

The values of PrintError and RaiseError can be set at the time the database connection is established via the attr argument, using 1 to turn that feature on and 0 to turn it off.

To create a database connection with automatic error checking handled using RaiseError, you would use a statement similar to the following:

 $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "", {
  PrintError => 0,
  RaiseError => 1
} );

The PrintError exception causes the warn() function to be called with the error string—the value of the errstr property—as its argument. The error message will be displayed, but program execution will not stop.

The RaiseError exception causes the die() function to be called, halting program execution. If both PrintError and RaiseError are enabled, warn() is called first, then die().

The status of each exception can be changed mid-script simply by reassigning the value of that attribute. To turn off RaiseError, for instance, you would do the following:

$dbh->{RaiseError} = 0;

The Perl DBI includes a powerful tracing mechanism that allows you to follow a query's execution to aid debugging.

Tracing is enabled using the trace() method, which can be called on a database handle or on the DBI itself to alter the default settings for every database handle opened in the script. Its argument is a trace level from 0 to 15; the values for which are shown in Table 7.2.

Trace level 1 should give a good overview of what is going on in your script. Level 2 will give some extra information that can be useful when debugging, and the higher levels are really only any use if you are trying to trap a specific problem.

To perform a simple trace of your script using trace level one, use the following command:

DBI->trace(1);

Trace output is written to stderr by default; however, an optional second parameter to trace() allows this to be changed.

Listing 7.10 creates a new table, numbers, and performs a simple loop to insert the first few square numbers into the num column. This is a trivial example to show tracing in action, but we will use the data in this listing later in this chapter.

#!/usr/bin/perl -w

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

$dbh->trace(1);

my $sql = "CREATE TABLE numbers (num INTEGER)";
$dbh->do($sql);

$sql = "INSERT INTO numbers (num) VALUES (?)";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() ); 

for (my $i=1; $i<=9; $i++) {
  $sth->execute($i * $i)
         or die("Cannot execute: " . DBI::errstr() );
}

Tracing is set to level 1 at the DBI level. Running this script produces trace output similar to the following:

$ ./listing7.10.pl
    DBI::db=HASH(0x9b88b90) trace level set to 0x0/1 (DBI @ 0x0/0)
in DBI 1.43-ithread (pid 2509)
    <- do('CREATE TABLE numbers (num INTEGER)')= '0E0' at
listing7.10.pl line 11
    <- prepare('INSERT INTO numbers (num) VALUES (?)')= DBI::st=HASH(0x9b8bd0c)
at listing7.10.pl line 15
    <- execute(1)= 1 at listing7.10.pl line 19
    <- execute(4)= 1 at listing7.10.pl line 19
    <- execute(9)= 1 at listing7.10.pl line 19
    <- execute(16)= 1 at listing7.10.pl line 19
    <- execute(25)= 1 at listing7.10.pl line 19
    <- execute(36)= 1 at listing7.10.pl line 19
    <- execute(49)= 1 at listing7.10.pl line 19
    <- execute(64)= 1 at listing7.10.pl line 19
    <- execute(81)= 1 at listing7.10.pl line 19
!   <- DESTROY(DBI::db=HASH(9ae4248))= undef during global destruction

Tracing can also be enabled from the shell without needing to amend your scripts using the DBI_TRACE environment variable. If DBI_TRACE is assigned a non-zero numeric value it will set the trace level for the DBI in Perl scripts to that value. If the number is followed by =filename, trace output will be redirected to that file.

For example, to run a script in trace level 2 with output written to trace.log, the following would work in the Bourne shell:

$ DBI_TRACE=2=trace.log perl myscript.pl

You can register a new function using the private method create_function, called as follows:

$dbh->func( $name, $argc, $func_ref, "create_function" )

The three arguments required are the function name, the number of arguments the function will take, and a reference to the Perl function that is to be used whenever the SQL function is called.

The simplest way to see how a user-defined function is used is to register a built-in Perl function in the SQL language. The following statement uses a minimal inline function, such as $func_ref, which registers the Perl function rand() as the SQL function rand(). No arguments are required for the function, so $argc is zero.

$dbh->func( "rand", 0, sub { return rand() }, "create_function" );

SQLite's built-in random() function returns a random signed 32-bit integer, whereas Perl's rand() function returns a decimal between 0 and 1. You could see the difference by preparing and executing the following statement from a Perl script after the rand() function has been registered in SQL:

SELECT rand(), random();

User-defined functions have to be registered for each database connection where they are to be used. Such functions are available in SQL only for the duration of that connection—they are not saved to the database itself or made available to other connection objects within the same script. Of course, user-defined functions are designed to allow you to add custom functions to SQL, so let's look at a more complex example (see Listing 7.11).

#!/usr/bin/perl -w

sub altcaps {
  my $str = $_[0];
  my $newstr = "";
  for ($i=0; $i<length($str); $i++) {
    $char = substr($str, $i, 1);
    if ($i%2) {
      $newstr .= uc($char);
    }
    else {
      $newstr .= lc($char);
    }
  }
  return $newstr;
}

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
       or die("Cannot connect: " . DBI::errstr() );

$dbh->func( "altcaps", 1, "altcaps", "create_function" );

my $sql = "SELECT altcaps(last_name) FROM contacts";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

$sth->execute() or die("Cannot execute: ". DBI::errstr() );

while ( my $first_name = $sth->fetchrow_array() ) {
  print "$first_name 
";
}

Listing 7.11 creates a new function in Perl called altcaps(), which converts the case of a string so that it contains alternating upper- and lowercase characters. The function is then registered as an SQL function with the same name and is used to select a modified version of the last_name column from contacts.

The output from running this script is as follows:

nEwMaN
o'bRiEn
wIlLiAmS

In this example we specified a $argc value of 1 as only a single string argument is required. SQLite will return an error code if the function is passed too many or too few arguments.

For example, if the SQL statement is changed to

$sql = "SELECT altcaps(first_name, last_name) FROM contacts";

The following error is produced:

Cannot execute: wrong number of arguments to function altcaps()
at ./func.pl line 30.

However, you can call the user-defined function as many times as you like in the same query. For example, this is a valid usage of the custom function:

$sql = "SELECT altcaps(first_name), altcaps(last_name) FROM contacts";

SQLite also allows you to create new aggregating functions through the Perl interface. An aggregating function is one that applies a calculation across all the rows returned by a query or across similar rows indicated by a GROUP BY clause.

The private method in DBD::SQLite2 that is used to create an aggregating function is called create_aggregate.

$dbh->func( $name, $argc, $pkg, "create_aggregate" );

This is called in much the same way as create_function, but the $pkg argument is a reference to a package containing the steps necessary to implement an aggregating function.

Three steps are required:

In Listing 7.12 we create a new aggregating function, median(), to calculate the median of a set of numbers. The median is a value such that half the values in the set are above the median and half are below it. The SQLite built-in avg() function computes the mean average—the sum of all values divided by the number of values.

#!/usr/bin/perl -w

package median;

sub new {
  bless [];
}

sub step {
  my ( $context, $value ) = @_;
  push @$context, $value;
}

sub finalize {

  my $context = $_[0];

  if (!@$context) {
    return undef;
  }

  my @sorted = sort { $a <=> $b } @$context;
  my $count = @sorted;

  my $median;
  if ($count%2 == 1) { # Odd number of elements
    $median = $sorted[int($count/2)];
  }
  else {               # Even number of elements
    $median = ($sorted[$count/2] + $sorted[($count/2)-1] ) / 2;
  }

  return $median;
}

use strict;
use DBI;

my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")
          or die("Cannot connect: " . DBI::errstr() );

$dbh->func( "median", 1, "median", "create_aggregate" );

my $sql = "SELECT median(num), AVG(num) FROM numbers";

my $sth = $dbh->prepare($sql)
          or die("Cannot prepare: " . DBI::errstr() );

$sth->execute() or die("Cannot execute: ". DBI::errstr() );

my ($median, $mean) = $sth->fetchrow_array();
print "Median: $median 
";
print "Mean: $mean 
";

Let's look at the three methods that make up the median package, beginning with new().

sub new {
  bless [];
}

This simple function is all that's needed to initialize an aggregator.

The step() method is also quite simple. Each $value that is to be aggregated is read in turn and pushed onto context. We do all the hard work in the finalize() function, so step() is just used to gather the required data into a workable format.

sub step {
  my ( $context, $value ) = @_;
  push @$context, $value;
}

The finalize() method begins by reading context and making sure it contains at least one element.

my $context = $_[0];

if (!@$context) {
  return undef;
}

If there are no elements from which to find the median, undef is returned—translated to a NULL result in SQL.

To find the median value we need to look at the elements in numerical order, so a sort is performed on context.

@sorted = sort { $a <=> $b } @context;

The rule to find the median where there is an odd number of values is simply to find the value of the middle element in the list.

$count = @sorted;

if ($count%2 == 1) { # Odd number of elements
  $median = $sorted[int($count/2)];
}

For an even number of values, the median is the midpoint between the two middle values.

else {               # Even number of elements
  $median = ($sorted[$count/2] + $sorted[($count/2)-1] ) / 2;
}

Finally we return $median, which will become the result of the SQL aggregating function.

return $median;

We will use the data added by Listing 7.10 to test this new function, so we need some data to work from. This script created a table named numbers and inserted a sequence of square numbers into the num column.

The square numbers that were inserted, in order, are

1 4 9 16 25 36 49 64 81

We can see from looking at this sequence that the median will be 25, as there are four numbers on either side. The mean average, on the other hand, will be a bigger value as the later numbers are relatively large numbers compared to the earlier numbers in the sequence.

Listing 7.12 fetches both the median and the mean average of the rows using the new user-defined function and the SQLite built-in:

$sql = "SELECT median(num), AVG(num) FROM numbers";

Running the script shows the calculated values as follows:

$ ./listing7.12.pl
Median: 25
Mean: 31.6666666666667