The changes will occur both because the DBI specification is changing and hence the requirements on DBD drivers change, and because feedback from people reading this document will suggest improvements to it.

Please read the DBI documentation first and fully, including the DBI FAQ. Then reread the DBI specification again as you're reading this. It'll help.

This document is a patchwork of contributions from various authors. More contributions (preferably as patches) are very welcome.

DESCRIPTION

This document is primarily intended to help people writing new database drivers for the Perl Database Interface (Perl DBI). It may also help others interested in discovering why the internals of a DBD driver are written the way they are.

This is a guide. Few (if any) of the statements in it are completely authoritative under all possible circumstances. This means you will need to use judgement in applying the guidelines in this document. If in any doubt at all, please do contact the dbi-dev mailing list (details given below) where Tim Bunce and other driver authors can help.

CREATING A NEW DRIVER

The first rule for creating a new database driver for the Perl DBI is very simple: DON'T!

There is usually a driver already available for the database you want to use, almost regardless of which database you choose. Very often, the database will provide an ODBC driver interface, so you can often use DBD::ODBC to access the database. This is typically less convenient on a Unix box than on a Microsoft Windows box, but there are numerous options for ODBC driver managers on Unix too, and very often the ODBC driver is provided by the database supplier.

Before deciding that you need to write a driver, do your homework to ensure that you are not wasting your energies.

[As of December 2002, the consensus is that if you need an ODBC driver manager on Unix, then the unixODBC driver (available from http://www.unixodbc.org/) is the way to go.]

The second rule for creating a new database driver for the Perl DBI is also very simple: Don't -- get someone else to do it for you!

Nevertheless, there are occasions when it is necessary to write a new driver, often to use a proprietary language or API to access the database more swiftly, or more comprehensively, than an ODBC driver can. Then you should read this document very carefully, but with a suitably sceptical eye.

If there is something in here that does not make any sense, question it. You might be right that the information is bogus, but don't come to that conclusion too quickly.

URLs and mailing lists

The primary web-site for locating DBI software and information is

  http://dbi.perl.org/

There are two main and one auxilliary mailing lists for people working with DBI. The primary lists are dbi-users@perl.org for general users of DBI and DBD drivers, and dbi-dev@perl.org mainly for DBD driver writers (don't join the dbi-dev list unless you have a good reason). The auxilliary list is dbi-announce@perl.org for announcing new releases of DBI or DBD drivers.

You can join these lists by accessing the web-site http://dbi.perl.org/. The lists are closed so you cannot send email to any of the lists unless you join the list first.

You should also consider monitoring the comp.lang.perl.* newsgroups, especially comp.lang.perl.modules.

The Cheetah book

The definitive book on Perl DBI is the Cheetah book, so called because of the picture on the cover. Its proper title is 'Programming the Perl DBI: Database programming with Perl' by Alligator Descartes and Tim Bunce, published by O'Reilly Associates, February 2000, ISBN 1-56592-699-4. Buy it now if you have not already done so, and read it.

Locating drivers

Before writing a new driver, it is in your interests to find out whether there already is a driver for your database. If there is such a driver, it would be much easier to make use of it than to write your own!

The primary web-site for locating Perl software is http://search.cpan.org/. You should look under the various modules listings for the software you are after. For example:

  http://search.cpan.org/modlist/Database_Interfaces

Follow the DBD:: and DBIx:: links at the top to see those subsets.

See the DBI docs for information on DBI web sites and mailing lists.

Registering a new driver

Before going through any official registration process, you will need to establish that there is no driver already in the works. You'll do that by asking the DBI mailing lists whether there is such a driver available, or whether anybody is working on one.

When you get the go ahead, you will need to establish the name of the driver and a prefix for the driver. Typically, the name is based on the name of the database software it uses, and the prefix is a contraction of that. Hence, DBD::Oracle has the name Oracle and the prefix 'ora_'. The prefix must be lowercase and contain no underscores other than the one at the end.

This information will be recorded in the DBI module. Apart from documentation purposes, registration is a prerequisite for installing private methods.

If you are writing a driver which will not be distributed on CPAN, then you should choose a prefix beginning with 'x_', to avoid potential prefix collisions with drivers registered in the future. Thus, if you wrote a non-CPAN distributed driver called DBD::CustomDB, the prefix might be 'x_cdb_'.

This document assumes you are writing a driver called DBD::Driver, and that the prefix 'drv_' is assigned to the driver.

Two styles of database driver

There are two distinct styles of database driver that can be written to work with the Perl DBI.

Your driver can be written in pure Perl, requiring no C compiler. When feasible, this is the best solution, but most databases are not written in such a way that this can be done. Some examples of pure Perl drivers are DBD::File and DBD::CSV.

Alternatively, and most commonly, your driver will need to use some C code to gain access to the database. This will be classified as a C/XS driver.

What code will you write?

There are a number of files that need to be written for either a pure Perl driver or a C/XS driver. There are no extra files needed only by a pure Perl driver, but there are several extra files needed only by a C/XS driver.

Files common to pure Perl and C/XS drivers

Assuming that your driver is called DBD::Driver, these files are:

Makefile.PL
META.yml
README
MANIFEST
Driver.pm
lib/Bundle/DBD/Driver.pm
lib/DBD/Driver/Summary.pm
t/*.t

The first four files are mandatory. Makefile.PL is used to control how the driver is built and installed. The README file tells people who download the file about how to build the module and any prerequisite software that must be installed. The MANIFEST file is used by the standard Perl module distribution mechanism. It lists all the source files that need to be distributed with your module. Driver.pm is what is loaded by the DBI code; it contains the methods peculiar to your driver.

Although the META.yml file is not required you are advised to create one. Of particular importance are the build_requires and configure_requires attributes which newer CPAN modules understand. You use these to tell the CPAN module (and CPANPLUS) that your build and configure mechanisms require DBI. The best reference for META.yml (at the time of writing) is http://module-build.sourceforge.net/META-spec-v1.2.html. You can find a reasonable example of a META.yml in DBD::ODBC.

The lib/Bundle/DBD/Driver.pm file allows you to specify other Perl modules on which yours depends in a format that allows someone to type a simple command and ensure that all the pre-requisites are in place as well as building your driver.

The lib/DBD/Driver/Summary.pm file contains (an updated version of) the information that was included - or that would have been included - in the appendices of the Cheetah book as a summary of the abilities of your driver and the associated database.

The files in the t subdirectory are unit tests for your driver. You should write your tests as stringently as possible, while taking into account the diversity of installations that you can encounter:

Your tests should not casually modify operational databases.
You should never damage existing tables in a database.
You should code your tests to use a constrained name space within the database. For example, the tables (and all other named objects) that are created could all begin with 'dbd_drv_'.
At the end of a test run, there should be no testing objects left behind in the database.
If you create any databases, you should remove them.
If your database supports temporary tables that are automatically removed at the end of a session, then exploit them as often as possible.
Try to make your tests independent of each other. If you have a test t/t11dowhat.t that depends upon the successful running of t/t10thingamy.t, people cannot run the single test case t/t11dowhat.t. Further, running t/t11dowhat.t twice in a row is likely to fail (at least, if t/t11dowhat.t modifies the database at all) because the database at the start of the second run is not what you saw at the start of the first run.
Document in your README file what you do, and what privileges people need to do it.
You can, and probably should, sequence your tests by including a test number before an abbreviated version of the test name; the tests are run in the order in which the names are expanded by shell-style globbing.
It is in your interests to ensure that your tests work as widely as possible.

Many drivers also install sub-modules DBD::Driver::SubModule for any of a variety of different reasons, such as to support the metadata methods (see the discussion of METADATA METHODS below). Such sub-modules are conventionally stored in the directory lib/DBD/Driver. The module itself would usually be in a file SubModule.pm. All such sub-modules should themselves be version stamped (see the discussions far below).

Extra files needed by C/XS drivers

The software for a C/XS driver will typically contain at least four extra files that are not relevant to a pure Perl driver.

Driver.xs
Driver.h
dbdimp.h
dbdimp.c

The Driver.xs file is used to generate C code that Perl can call to gain access to the C functions you write that will, in turn, call down onto your database software.

The Driver.h header is a stylized header that ensures you can access the necessary Perl and DBI macros, types, and function declarations.

The dbdimp.h is used to specify which functions have been implemented by your driver.

The dbdimp.c file is where you write the C code that does the real work of translating between Perl-ish data types and what the database expects to use and return.

There are some (mainly small, but very important) differences between the contents of Makefile.PL and Driver.pm for pure Perl and C/XS drivers, so those files are described both in the section on creating a pure Perl driver and in the section on creating a C/XS driver.

Obviously, you can add extra source code files to the list.

Requirements on a driver and driver writer

To be remotely useful, your driver must be implemented in a format that allows it to be distributed via CPAN, the Comprehensive Perl Archive Network (http://www.cpan.org/ and http://search.cpan.org). Of course, it is easier if you do not have to meet this criterion, but you will not be able to ask for much help if you do not do so, and no-one is likely to want to install your module if they have to learn a new installation mechanism.

CREATING A PURE PERL DRIVER

Writing a pure Perl driver is surprisingly simple. However, there are some problems you should be aware of. The best option is of course picking up an existing driver and carefully modifying one method after the other.

Also look carefully at DBD::AnyData and DBD::Template.

As an example we take a look at the DBD::File driver, a driver for accessing plain files as tables, which is part of the DBD::CSV package.

The minimal set of files we have to implement are Makefile.PL, README, MANIFEST and Driver.pm.

Pure Perl version of Makefile.PL

You typically start with writing Makefile.PL, a Makefile generator. The contents of this file are described in detail in the the ExtUtils::MakeMaker manpage man pages. It is definitely a good idea if you start reading them. At least you should know about the variables CONFIGURE, DEFINED, PM, DIR, EXE_FILES, INC, LIBS, LINKTYPE, NAME, OPTIMIZE, PL_FILES, VERSION, VERSION_FROM, clean, depend, realclean from the the ExtUtils::MakeMaker manpage man page: these are used in almost any Makefile.PL.

Additionally read the section on Overriding MakeMaker Methods and the descriptions of the distcheck, disttest and dist targets: They will definitely be useful for you.

Of special importance for DBI drivers is the postamble method from the the ExtUtils::MM_Unix manpage man page.

For Emacs users, I recommend the libscan method, which removes Emacs backup files (file names which end with a tilde '~') from lists of files.

Now an example, I use the word Driver wherever you should insert your driver's name:

  # -*- perl -*-

  use ExtUtils::MakeMaker;

  WriteMakefile(
      dbd_edit_mm_attribs( {
          'NAME'         => 'DBD::Driver',
          'VERSION_FROM' => 'Driver.pm',
          'INC'          => '',
          'dist'         => { 'SUFFIX'   => '.gz',
                              'COMPRESS' => 'gzip -9f' },
          'realclean'    => { FILES => '*.xsi' },
          'PREREQ_PM'    => '1.03',
          'CONFIGURE'    => sub {
              eval {require DBI::DBD;};
              if ($@) {
                  warn $@;
                  exit 0;
              }
              my $dbi_arch_dir = dbd_dbi_arch_dir();
              if (exists($opts{INC})) {
                  return {INC => "$opts{INC} -I$dbi_arch_dir"};
              } else {
                  return {INC => "-I$dbi_arch_dir"};
              }
          }
      },
      { create_pp_tests => 1})
  );

  package MY;
  sub postamble { return main::dbd_postamble(@_); }
  sub libscan {
      my ($self, $path) = @_;
      ($path =~ m/\~$/) ? undef : $path;
  }

Note the calls to dbd_edit_mm_attribs() and dbd_postamble().

The second hash reference in the call to dbd_edit_mm_attribs() (containing create_pp_tests()) is optional; you should not use it unless your driver is a pure Perl driver (that is, it does not use C and XS code). Therefore, the call to dbd_edit_mm_attribs() is not relevant for C/XS drivers and may be omitted; simply use the (single) hash reference containing NAME etc as the only argument to WriteMakefile().

Note that the dbd_edit_mm_attribs() code will fail if you do not have a t sub-directory containing at least one test case.

PREREQ_PM tells MakeMaker that DBI (version 1.03 in this case) is required for this module. This will issue a warning that DBI 1.03 is missing if someone attempts to install your DBD without DBI 1.03. See CONFIGURE below for why this does not work reliably in stopping cpan testers failing your module if DBI is not installed.

CONFIGURE is a subroutine called by MakeMaker during WriteMakefile. By putting the require DBI::DBD in this section we can attempt to load DBI::DBD but if it is missing we exit with success. As we exit successfully without creating a Makefile when DBI::DBD is missing cpan testers will not report a failure. This may seem at odds with PREREQ_PM but PREREQ_PM does not cause WriteMakefile to fail (unless you also specify PREREQ_FATAL which is strongly discouraged by MakeMaker) so WriteMakefile would continue to call dbd_dbi_arch_dir and fail.

All drivers must use dbd_postamble() or risk running into problems.

Note the specification of VERSION_FROM; the named file (Driver.pm) will be scanned for the first line that looks like an assignment to $VERSION, and the subsequent text will be used to determine the version number. Note the commentary in the ExtUtils::MakeMaker manpage on the subject of correctly formatted version numbers.

If your driver depends upon external software (it usually will), you will need to add code to ensure that your environment is workable before the call to WriteMakefile(). If you need to check for the existance of an external library and perhaps modify INC to include the paths to where the external library header files are located and you cannot find the library or header files make sure you output a message saying they cannot be found but exit 0 (success) before calling WriteMakefile or CPAN testers will fail your module if the external library is not found.

A full-fledged Makefile.PL can be quite large (for example, the files for DBD::Oracle and DBD::Informix are both over 1000 lines long, and the Informix one uses - and creates - auxilliary modules too).

See also the ExtUtils::MakeMaker manpage and the ExtUtils::MM_Unix manpage. Consider using the CPAN::MakeMaker manpage in place of ExtUtils::MakeMaker.

README

The README file should describe what the driver is for, the pre-requisites for the build process, the actual build process, how to report errors, and who to report them to.

Users will find ways of breaking the driver build and test process which you would never even have dreamed to be possible in your worst nightmares. Therefore, you need to write this document defensively, precisely and concisely.

As always, use the README from one of the established drivers as a basis for your own; the version in DBD::Informix is worth a look as it has been quite successful in heading off problems.

Note that users will have versions of Perl and DBI that are both older and newer than you expected, but this will seldom cause much trouble. When it does, it will be because you are using features of DBI that are not supported in the version they are using.
Note that users will have versions of the database software that are both older and newer than you expected. You will save yourself time in the long run if you can identify the range of versions which have been tested and warn about versions which are not known to be OK.
Note that many people trying to install your driver will not be experts in the database software.
Note that many people trying to install your driver will not be experts in C or Perl.

MANIFEST

The MANIFEST will be used by the Makefile's dist target to build the distribution tar file that is uploaded to CPAN. It should list every file that you want to include in your distribution, one per line.

lib/Bundle/DBD/Driver.pm

The CPAN module provides an extremely powerful bundle mechanism that allows you to specify pre-requisites for your driver.

The primary pre-requisite is Bundle::DBI; you may want or need to add some more. With the bundle set up correctly, the user can type:

        perl -MCPAN -e 'install Bundle::DBD::Driver'

and Perl will download, compile, test and install all the Perl modules needed to build your driver.

The prerequisite modules are listed in the CONTENTS section, with the official name of the module followed by a dash and an informal name or description.

Listing Bundle::DBI as the main pre-requisite simplifies life.
Don't forget to list your driver.
Note that unless the DBMS is itself a Perl module, you cannot list it as a pre-requisite in this file.
You should keep the version of the bundle the same as the version of your driver.
You should add configuration management, copyright, and licencing information at the top.

A suitable skeleton for this file is shown below.

  package Bundle::DBD::Driver;

  $VERSION = '0.01';

1;

  __END__

  =head1 NAME

  Bundle::DBD::Driver - A bundle to install all DBD::Driver related modules

  =head1 SYNOPSIS

  C<perl -MCPAN -e 'install Bundle::DBD::Driver'>

  =head1 CONTENTS

  Bundle::DBI  - Bundle for DBI by TIMB (Tim Bunce)

  DBD::Driver  - DBD::Driver by YOU (Your Name)

  =head1 DESCRIPTION

  This bundle includes all the modules used by the Perl Database
  Interface (DBI) driver for Driver (DBD::Driver), assuming the
  use of DBI version 1.13 or later, created by Tim Bunce.

  If you've not previously used the CPAN module to install any
  bundles, you will be interrogated during its setup phase.
  But when you've done it once, it remembers what you told it.
  You could start by running:

    C<perl -MCPAN -e 'install Bundle::CPAN'>

  =head1 SEE ALSO

  Bundle::DBI

  =head1 AUTHOR

  Your Name E<lt>F<you@yourdomain.com>E<gt>

  =head1 THANKS

  This bundle was created by ripping off Bundle::libnet created by
  Graham Barr E<lt>F<gbarr@ti.com>E<gt>, and radically simplified
  with some information from Jochen Wiedmann E<lt>F<joe@ispsoft.de>E<gt>.
  The template was then included in the DBI::DBD documentation by
  Jonathan Leffler E<lt>F<jleffler@informix.com>E<gt>.

  =cut

lib/DBD/Driver/Summary.pm

There is no substitute for taking the summary file from a driver that was documented in the Perl book (such as DBD::Oracle or DBD::Informix or DBD::ODBC, to name but three), and adapting it to describe the facilities available via DBD::Driver when accessing the Driver database.

Pure Perl version of Driver.pm

The Driver.pm file defines the Perl module DBD::Driver for your driver. It will define a package DBD::Driver along with some version information, some variable definitions, and a function driver() which will have a more or less standard structure.

It will also define three sub-packages of DBD::Driver:

DBD::Driver::dr: with methods connect(), data_sources() and disconnect_all();
DBD::Driver::db: with methods such as prepare();
DBD::Driver::st: with methods such as execute() and fetch().

The Driver.pm file will also contain the documentation specific to DBD::Driver in the format used by perldoc.

In a pure Perl driver, the Driver.pm file is the core of the implementation. You will need to provide all the key methods needed by DBI.

Now let's take a closer look at an excerpt of File.pm as an example. We ignore things that are common to any module (even non-DBI modules) or really specific to the DBD::File package.

The DBD::Driver package

The header

  package DBD::File;

  use strict;
  use vars qw($VERSION $drh);

  $VERSION = "1.23.00"  # Version number of DBD::File

This is where the version number of your driver is specified, and is where Makefile.PL looks for this information. Please ensure that any other modules added with your driver are also version stamped so that CPAN does not get confused.

It is recommended that you use a two-part (1.23) or three-part (1.23.45) version number. Also consider the CPAN system, which gets confused and considers version 1.10 to precede version 1.9, so that using a raw CVS, RCS or SCCS version number is probably not appropriate (despite being very common).

For Subversion you could use:

  $VERSION = sprintf("12.%06d", q$Revision: 12345 $ =~ /(\d+)/o);

(use lots of leading zeros on the second portion so if you move the code to a shared repository like svn.perl.org the much larger revision numbers won't cause a problem, at least not for a few years). For RCS or CVS you can use:

  $VERSION = sprintf "%d.%02d", '$Revision: 11.21 $ ' =~ /(\d+)\.(\d+)/;

which pads out the fractional part with leading zeros so all is well (so long as you don't go past x.99)

  $drh = undef;         # holds driver handle once initialized

This is where the driver handle will be stored, once created. Note that you may assume there is only one handle for your driver.

The driver constructor

The driver() method is the driver handle constructor. Note that the driver() method is in the DBD::Driver package, not in one of the sub-packages DBD::Driver::dr, DBD::Driver::db, or DBD::Driver::db.

  sub driver
  {
      return $drh if $drh;      # already created - return same one
      my ($class, $attr) = @_;

      $class .= "::dr";

      DBD::Driver::db->install_method('drv_example_dbh_method');
      DBD::Driver::st->install_method('drv_example_sth_method');

      # not a 'my' since we use it above to prevent multiple drivers
      $drh = DBI::_new_drh($class, {
              'Name'        => 'File',
              'Version'     => $VERSION,
              'Attribution' => 'DBD::File by Jochen Wiedmann',
          })
          or return undef;

      return $drh;
        }

This is a reasonable example of how DBI implements its handles. There are three kinds: driver handles (typically stored in $drh; from now on called drh or $drh), database handles (from now on called dbh or $dbh) and statement handles (from now on called sth or $sth).

The prototype of DBI::_new_drh() is

  $drh = DBI::_new_drh($class, $public_attrs, $private_attrs);

with the following arguments:

$class: is typically the class for your driver, (for example, "DBD::File::dr"), passed as the first argument to the driver() method.
$public_attrs: is a hash ref to attributes like Name, Version, and Attribution. These are processed and used by DBI. You had better not make any assumptions about them nor should you add private attributes here.
$private_attrs: This is another (optional) hash ref with your private attributes. DBI will store them and otherwise leave them alone.

The DBI::_new_drh() method and the driver() method both return undef for failure (in which case you must look at $DBI::err and $DBI::errstr for the failure information, because you have no driver handle to use).

Using `install_method()` to expose driver-private methods

    DBD::Foo::db->install_method($method_name, \%attr);

Installs the driver-private method named by $method_name into the DBI method dispatcher so it can be called directly, avoiding the need to use the func() method.

It is called as a static method on the driver class to which the method belongs. The method name must begin with the corresponding registered driver-private prefix. For example, for DBD::Oracle $method_name must being with 'ora_', and for DBD::AnyData it must begin with 'ad_'.

The attributes can be used to provide fine control over how the DBI dispatcher handles the dispatching of the method. However, at this point, it's undocumented and very liable to change. (Volunteers to polish up and document the interface are very welcome to get in touch via dbi-dev@perl.org)

Methods installed using install_method default to the standard error handling behaviour for DBI methods: clearing err and errstr before calling the method, and checking for errors to trigger RaiseError etc. on return. This differs from the default behaviour of func().

Note for driver authors: The DBD::Foo::xx->install_method call won't work until the class-hierarchy has been setup. Normally the DBI looks after that just after the driver is loaded. This means install_method() can't be called at the time the driver is loaded unless the class-hierarchy is set up first. The way to do that is to call the setup_driver() method:

    DBI->setup_driver('DBD::Foo');

before using install_method().

The CLONE special subroutine

Also needed here, in the DBD::Driver package, is a CLONE() method that will be called by perl when an intrepreter is cloned. All your CLONE() method needs to do, currently, is clear the cached $drh so the new interpreter won't start using the cached $drh from the old interpreter:

  sub CLONE {
    undef $drh;
  }

See http://search.cpan.org/dist/perl/pod/perlmod.pod#Making_your_module_threadsafe for details.

The DBD::Driver::dr package

The next lines of code look as follows:

  package DBD::Driver::dr; # ====== DRIVER ======

  $DBD::Driver::dr::imp_data_size = 0;

Note that no @ISA is needed here, or for the other DBD::Driver::* classes, because the DBI takes care of that for you when the driver is loaded.

 *FIX ME* Explain what the imp_data_size is, so that implementors aren't
 practicing cargo-cult programming.

The database handle constructor

The database handle constructor is the driver's (hence the changed namespace) connect() method:

  sub connect
  {
      my ($drh, $dr_dsn, $user, $auth, $attr) = @_;

      # Some database specific verifications, default settings
      # and the like can go here. This should only include
      # syntax checks or similar stuff where it's legal to
      # 'die' in case of errors.
      # For example, many database packages requires specific
      # environment variables to be set; this could be where you
      # validate that they are set, or default them if they are not set.

      my $driver_prefix = "drv_"; # the assigned prefix for this driver

      # Process attributes from the DSN; we assume ODBC syntax
      # here, that is, the DSN looks like var1=val1;...;varN=valN
      foreach my $var ( split /;/, $dr_dsn ) {
          my ($attr_name, $attr_value) = split '=', $var, 2;
          return $drh->set_err($DBI::stderr, "Can't parse DSN part '$var'")
              unless defined $attr_value;

          # add driver prefix to attribute name if it doesn't have it already
          $attr_name = $driver_prefix.$attr_name
              unless $attr_name =~ /^$driver_prefix/o;

          # Store attribute into %$attr, replacing any existing value.
          # The DBI will STORE() these into $dbh after we've connected
          $attr->{$attr_name} = $attr_value;
                }

      # Get the attributes we'll use to connect.
      # We use delete here because these no need to STORE them
      my $db = delete $attr->{drv_database} || delete $attr->{drv_db}
          or return $drh->set_err($DBI::stderr, "No database name given in DSN '$dr_dsn'");
      my $host = delete $attr->{drv_host} || 'localhost';
      my $port = delete $attr->{drv_port} || 123456;

      # Assume you can attach to your database via drv_connect:
      my $connection = drv_connect($db, $host, $port, $user, $auth)
          or return $drh->set_err($DBI::stderr, "Can't connect to $dr_dsn: ...");

      # create a 'blank' dbh (call superclass constructor)
      my ($outer, $dbh) = DBI::_new_dbh($drh, { Name => $dr_dsn });

      $dbh->STORE('Active', 1 );
      $dbh->{drv_connection} = $connection;

      return $outer;
        }

This is mostly the same as in the driver handle constructor above. The arguments are described in the DBI manpage.

The constructor DBI::_new_dbh() is called, returning a database handle. The constructor's prototype is:

  ($outer, $inner) = DBI::_new_dbh($drh, $public_attr, $private_attr);

with similar arguments to those in the driver handle constructor, except that the $class is replaced by $drh. The Name attribute is a standard DBI attribute (see Database Handle Attributes in the DBI manpage).

In scalar context, only the outer handle is returned.

Note the use of the STORE() method for setting the dbh attributes. That's because within the driver code, the handle object you have is the 'inner' handle of a tied hash, not the outer handle that the users of your driver have.

Because you have the inner handle, tie magic doesn't get invoked when you get or set values in the hash. This is often very handy for speed when you want to get or set simple non-special driver-specific attributes.

However, some attribute values, such as those handled by the DBI like PrintError, don't actually exist in the hash and must be read via $h->FETCH($attrib) and set via $h->STORE($attrib, $value). If in any doubt, use these methods.

The `data_sources()` method

The data_sources() method must populate and return a list of valid data sources, prefixed with the "dbi:Driver" incantation that allows them to be used in the first argument of the DBI->connect() method. An example of this might be scanning the $HOME/.odbcini file on Unix for ODBC data sources (DSNs).

As a trivial example, consider a fixed list of data sources:

  sub data_sources
  {
      my($drh, $attr) = @_;
      my(@list) = ();
      # You need more sophisticated code than this to set @list...
      push @list, "dbi:Driver:abc";
      push @list, "dbi:Driver:def";
      push @list, "dbi:Driver:ghi";
      # End of code to set @list
      return @list;
  }

The `disconnect_all()` method

If you need to release any resources when the driver is unloaded, you can provide a disconnect_all method.

Other driver handle methods

If you need any other driver handle methods, they can follow here.

Error handling

It is quite likely that something fails in the connect method. With DBD::File for example, you might catch an error when setting the current directory to something not existent by using the (driver-specific) f_dir attribute.

To report an error, you use the set_err() method:

  $h->set_err($err, $errmsg, $state);

This will ensure that the error is recorded correctly and that RaiseError and PrintError etc are handled correctly.

Typically you'll always use the method instance, aka your method's first argument.

As set_err() always returns undef your error handling code can usually be simplified to something like this:

  return $h->set_err($err, $errmsg, $state) if ...;

The DBD::Driver::db package

  package DBD::Driver::db; # ====== DATABASE ======

  $DBD::Driver::db::imp_data_size = 0;

The statement handle constructor

There's nothing much new in the statement handle constructor, which is the prepare() method:

  sub prepare
  {
      my ($dbh, $statement, @attribs) = @_;

      # create a 'blank' sth
      my ($outer, $sth) = DBI::_new_sth($dbh, { Statement => $statement });

      $sth->STORE('NUM_OF_PARAMS', ($statement =~ tr/?//));

      $sth->{drv_params} = [];

      return $outer;
        }

This is still the same -- check the arguments and call the super class constructor DBI::_new_sth(). Again, in scalar context, only the outer handle is returned. The Statement attribute should be cached as shown.

Note the prefix drv_ in the attribute names: it is required that all your private attributes use a lowercase prefix unique to your driver. As mentioned earlier in this document, the DBI contains a registry of known driver prefixes and may one day warn about unknown attributes that don't have a registered prefix.

Note that we parse the statement here in order to set the attribute NUM_OF_PARAMS. The technique illustrated is not very reliable; it can be confused by question marks appearing in quoted strings, delimited identifiers or in SQL comments that are part of the SQL statement. We could set NUM_OF_PARAMS in the execute() method instead because the DBI specification explicitly allows a driver to defer this, but then the user could not call bind_param().

Transaction handling

Pure Perl drivers will rarely support transactions. Thus your commit() and rollback() methods will typically be quite simple:

  sub commit
  {
      my ($dbh) = @_;
      if ($dbh->FETCH('Warn')) {
          warn("Commit ineffective while AutoCommit is on");
      }
      0;
  }

  sub rollback {
      my ($dbh) = @_;
      if ($dbh->FETCH('Warn')) {
          warn("Rollback ineffective while AutoCommit is on");
      }
      0;
  }

Or even simpler, just use the default methods provided by the DBI that do nothing except return undef.

The DBI's default begin_work() method can be used by inheritance.

The `STORE()` and `FETCH()` methods

These methods (that we have already used, see above) are called for you, whenever the user does a:

  $dbh->{$attr} = $val;

or, respectively,

  $val = $dbh->{$attr};

See the perltie manpage for details on tied hash refs to understand why these methods are required.

The DBI will handle most attributes for you, in particular attributes like RaiseError or PrintError. All you have to do is handle your driver's private attributes and any attributes, like AutoCommit and ChopBlanks, that the DBI can't handle for you.

A good example might look like this:

  sub STORE
  {
      my ($dbh, $attr, $val) = @_;
      if ($attr eq 'AutoCommit') {
          # AutoCommit is currently the only standard attribute we have
          # to consider.
          if (!$val) { die "Can't disable AutoCommit"; }
          return 1;
      }
      if ($attr =~ m/^drv_/) {
          # Handle only our private attributes here
          # Note that we could trigger arbitrary actions.
          # Ideally we should warn about unknown attributes.
          $dbh->{$attr} = $val; # Yes, we are allowed to do this,
          return 1;             # but only for our private attributes
      }
      # Else pass up to DBI to handle for us
      $dbh->SUPER::STORE($attr, $val);
  }

  sub FETCH
  {
      my ($dbh, $attr) = @_;
      if ($attr eq 'AutoCommit') { return 1; }
      if ($attr =~ m/^drv_/) {
          # Handle only our private attributes here
          # Note that we could trigger arbitrary actions.
          return $dbh->{$attr}; # Yes, we are allowed to do this,
                                # but only for our private attributes
      }
      # Else pass up to DBI to handle
      $dbh->SUPER::FETCH($attr);
  }

The DBI will actually store and fetch driver-specific attributes (with all lowercase names) without warning or error, so there's actually no need to implement driver-specific any code in your FETCH() and STORE() methods unless you need extra logic/checks, beyond getting or setting the value.

Unless your driver documentation indicates otherwise, the return value of the STORE() method is unspecified and the caller shouldn't use that value.

Other database handle methods

As with the driver package, other database handle methods may follow here. In particular you should consider a (possibly empty) disconnect() method and possibly a quote() method if DBI's default isn't correct for you. You may also need the type_info_all() and get_info() methods, as described elsewhere in this document.

Where reasonable use $h->SUPER::foo() to call the DBI's method in some or all cases and just wrap your custom behavior around that.

If you want to use private trace flags you'll probably want to be able to set them by name. To do that you'll need to define a parse_trace_flag() method (note that's "parse_trace_flag", singular, not "parse_trace_flags", plural).

  sub parse_trace_flag {
      my ($h, $name) = @_;
      return 0x01000000 if $name eq 'foo';
      return 0x02000000 if $name eq 'bar';
      return 0x04000000 if $name eq 'baz';
      return 0x08000000 if $name eq 'boo';
      return 0x10000000 if $name eq 'bop';
      return $h->SUPER::parse_trace_flag($name);
  }

All private flag names must be lowercase, and all private flags must be in the top 8 of the 32 bits.

The DBD::Driver::st package

This package follows the same pattern the others do:

  package DBD::Driver::st;

  $DBD::Driver::st::imp_data_size = 0;

The `execute()` and `bind_param()` methods

This is perhaps the most difficult method because we have to consider parameter bindings here. In addition to that, there are a number of statement attributes which must be set for inherited DBI methods to function correctly (see Statement attributes below).

We present a simplified implementation by using the drv_params attribute from above:

  sub bind_param
  {
      my ($sth, $pNum, $val, $attr) = @_;
      my $type = (ref $attr) ? $attr->{TYPE} : $attr;
      if ($type) {
          my $dbh = $sth->{Database};
          $val = $dbh->quote($sth, $type);
      }
      my $params = $sth->{drv_params};
      $params->[$pNum-1] = $val;
      1;
  }

  sub execute
  {
      my ($sth, @bind_values) = @_;

      # start of by finishing any previous execution if still active
      $sth->finish if $sth->FETCH('Active');

      my $params = (@bind_values) ?
          \@bind_values : $sth->{drv_params};
      my $numParam = $sth->FETCH('NUM_OF_PARAMS');
      return $sth->set_err($DBI::stderr, "Wrong number of parameters")
          if @$params != $numParam;
      my $statement = $sth->{'Statement'};
      for (my $i = 0;  $i < $numParam;  $i++) {
          $statement =~ s/?/$params->[$i]/; # XXX doesn't deal with quoting etc!
      }
      # Do anything ... we assume that an array ref of rows is
      # created and store it:
      $sth->{'drv_data'} = $data;
      $sth->{'drv_rows'} = @$data; # number of rows
      $sth->STORE('NUM_OF_FIELDS') = $numFields;
      $sth->{Active} = 1;
      @$data || '0E0';
        }

There are a number of things you should note here.

We initialize the NUM_OF_FIELDS and Active attributes here, because they are essential for bind_columns() to work.

We use attribute $sth->{Statement} which we created within prepare(). The attribute $sth->{Database}, which is nothing else than the dbh, was automatically created by DBI.

Finally, note that (as specified in the DBI specification) we return the string '0E0' instead of the number 0, so that the result tests true but equal to zero.

  $sth->execute() or die $sth->errstr;

The `execute_array()`, `execute_for_fetch()` and `bind_param_array()` methods

In general, DBD's only need to implement execute_for_fetch() and bind_param_array. DBI's default execute_array() will invoke the DBD's execute_for_fetch() as needed.

The following sequence describes the interaction between DBI execute_array and a DBD's execute_for_fetch:

App calls $sth->execute_array(\%attrs, @array_of_arrays)
If @array_of_arrays was specified, DBI processes @array_of_arrays by calling DBD's bind_param_array(). Alternately, App may have directly called bind_param_array()
DBD validates and binds each array
DBI retrieves the validated param arrays from DBD's ParamArray attribute
DBI calls DBD's execute_for_fetch($fetch_tuple_sub, \@tuple_status), where &$fetch_tuple_sub is a closure to iterate over the returned ParamArray values, and \@tuple_status is an array to receive the disposition status of each tuple.
DBD iteratively calls &$fetch_tuple_sub to retrieve parameter tuples to be added to its bulk database operation/request.
when DBD reaches the limit of tuples it can handle in a single database operation/request, or the &$fetch_tuple_sub indicates no more tuples by returning undef, the DBD executes the bulk operation, and reports the disposition of each tuple in \@tuple_status.
DBD repeats steps 6 and 7 until all tuples are processed.

E.g., here's the essence of the DBD::Oracle manpage's execute_for_fetch:

       while (1) {
           my @tuple_batch;
           for (my $i = 0; $i < $batch_size; $i++) {
                push @tuple_batch, [ @{$fetch_tuple_sub->() || last} ];
           }
           last unless @tuple_batch;
           my $res = ora_execute_array($sth, \@tuple_batch,
              scalar(@tuple_batch), $tuple_batch_status);
           push @$tuple_status, @$tuple_batch_status;
       }

Note that DBI's default execute_array()/execute_for_fetch() implementation requires the use of positional (i.e., '?') placeholders. Drivers which require named placeholders must either emulate positional placeholders (e.g., see the DBD::Oracle manpage), or must implement their own execute_array()/execute_for_fetch() methods to properly sequence bound parameter arrays.

Fetching data

Only one method needs to be written for fetching data, fetchrow_arrayref(). The other methods, fetchrow_array(), fetchall_arrayref(), etc, as well as the database handle's select* methods are part of DBI, and call fetchrow_arrayref() as necessary.

  sub fetchrow_arrayref
  {
      my ($sth) = @_;
      my $data = $sth->{drv_data};
      my $row = shift @$data;
      if (!$row) {
          $sth->STORE(Active => 0); # mark as no longer active
          return undef;
      }
      if ($sth->FETCH('ChopBlanks')) {
          map { $_ =~ s/\s+$//; } @$row;
      }
      return $sth->_set_fbav($row);
  }
  *fetch = \&fetchrow_arrayref; # required alias for fetchrow_arrayref

Note the use of the method _set_fbav() -- this is required so that bind_col() and bind_columns() work.

If an error occurs which leaves the $sth in a state where remaining rows can't be fetched then Active should be turned off before the method returns.

The rows() method for this driver can be implemented like this:

  sub rows { shift->{drv_rows} }

because it knows in advance how many rows it has fetched. Alternatively you could delete that method and so fallback to the DBI's own method which does the right thing based on the number of calls to _set_fbav().

The more_results method

If your driver doesn't support multiple result sets, then don't even implement this method.

Otherwise, this method needs to get the statement handle ready to fetch results from the next result set, if there is one. Typically you'd start with:

    $sth->finish;

then you should delete all the attributes from the attribute cache that may no longer be relevant for the new result set:

    delete $sth->{$_}
        for qw(NAME TYPE PRECISION SCALE ...);

for drivers written in C use:

    hv_delete((HV*)SvRV(sth), "NAME", 4, G_DISCARD);
    hv_delete((HV*)SvRV(sth), "NULLABLE", 8, G_DISCARD);
    hv_delete((HV*)SvRV(sth), "NUM_OF_FIELDS", 13, G_DISCARD);
    hv_delete((HV*)SvRV(sth), "PRECISION", 9, G_DISCARD);
    hv_delete((HV*)SvRV(sth), "SCALE", 5, G_DISCARD);
    hv_delete((HV*)SvRV(sth), "TYPE", 4, G_DISCARD);

Don't forget to also delete, or update, any driver-private attributes that may not be correct for the next resultset.

The NUM_OF_FIELDS attribute is a special case. It should be set using STORE:

    $sth->STORE(NUM_OF_FIELDS => 0); /* for DBI <= 1.53 */
    $sth->STORE(NUM_OF_FIELDS => $new_value);

for drivers written in C use this incantation:

    /* Adjust NUM_OF_FIELDS - which also adjusts the row buffer size */
    DBIc_NUM_FIELDS(imp_sth) = 0; /* for DBI <= 1.53 */
    DBIc_STATE(imp_xxh)->set_attr_k(sth, sv_2mortal(newSVpvn("NUM_OF_FIELDS",13)), 0,
        sv_2mortal(newSViv(mysql_num_fields(imp_sth->result)))
    );

For DBI versions prior to 1.54 you'll also need to explicitly adjust the number of elements in the row buffer array (DBIc_FIELDS_AV(imp_sth)) to match the new result set. Fill any new values with newSV(0) not &sv_undef. Alternatively you could free DBIc_FIELDS_AV(imp_sth) and set it to null, but that would mean bind_columns() woudn't work across result sets.

Statement attributes

The main difference between dbh and sth attributes is, that you should implement a lot of attributes here that are required by the DBI, such as NAME, NULLABLE, TYPE, etc. See Statement Handle Attributes in the DBI manpage for a complete list.

Pay attention to attributes which are marked as read only, such as NUM_OF_PARAMS. These attributes can only be set the first time a statement is executed. If a statement is prepared, then executed multiple times, warnings may be generated.

You can protect against these warnings, and prevent the recalculation of attributes which might be expensive to calculate (such as the NAME and NAME_* attributes):

    my $storedNumParams = $sth->FETCH('NUM_OF_PARAMS');
    if (!defined $storedNumParams or $storedNumFields < 0) {
        $sth->STORE('NUM_OF_PARAMS') = $numParams;

        # Set other useful attributes that only need to be set once
        # for a statement, like $sth->{NAME} and $sth->{TYPE}
            }

One particularly important attribute to set correctly (mentioned in ATTRIBUTES COMMON TO ALL HANDLES in the DBI manpage is Active. Many DBI methods, including bind_columns(), depend on this attribute.

Besides that the STORE() and FETCH() methods are mainly the same as above for dbh's.

Other statement methods

A trivial finish() method to discard stored data, reset any attributes (such as Active) and do $sth->SUPER::finish().

If you've defined a parse_trace_flag() method in ::db you'll also want it in ::st, so just alias it in:

  *parse_trace_flag = \&DBD::foo:db::parse_trace_flag;

And perhaps some other methods that are not part of the DBI specification, in particular to make metadata available. Remember that they must have names that begin with your drivers registered prefix so they can be installed using install_method().

If DESTROY() is called on a statement handle that's still active ($sth->{Active} is true) then it should effectively call finish().

    sub DESTROY {
        my $sth = shift;
        $sth->finish if $sth->FETCH('Active');
    }

Tests

The test process should conform as closely as possibly to the Perl standard test harness.

In particular, most (all) of the tests should be run in the t sub-directory, and should simply produce an ok when run under make test. For details on how this is done, see the Camel book and the section in Chapter 7, "The Standard Perl Library" on the Test::Harness manpage.

The tests may need to adapt to the type of database which is being used for testing, and to the privileges of the user testing the driver. For example, the DBD::Informix test code has to adapt in a number of places to the type of database to which it is connected as different Informix databases have different capabilities: some of the tests are for databases without transaction logs; others are for databases with a transaction log; some versions of the server have support for blobs, or stored procedures, or user-defined data types, and others do not.

When a complete file of tests must be skipped, you can provide a reason in a pseudo-comment:

    if ($no_transactions_available)
    {
        print "1..0 # Skip: No transactions available\n";
        exit 0;
    }

Consider downloading the DBD::Informix code and look at the code in DBD/Informix/TestHarness.pm which is used throughout the DBD::Informix tests in the t sub-directory.

CREATING A C/XS DRIVER

Please also see the section under CREATING A PURE PERL DRIVER regarding the creation of the Makefile.PL.

Creating a new C/XS driver from scratch will always be a daunting task. You can and should greatly simplify your task by taking a good reference driver implementation and modifying that to match the database product for which you are writing a driver.

The de facto reference driver has been the one for DBD::Oracle written by Tim Bunce, who is also the author of the DBI package. The DBD::Oracle module is a good example of a driver implemented around a C-level API.

Nowadays it it seems better to base on DBD::ODBC, another driver maintained by Tim and Jeff Urlwin, because it offers a lot of metadata and seems to become the guideline for the future development. (Also as DBD::Oracle digs deeper into the Oracle 8 OCI interface it'll get even more hairy than it is now.)

The DBD::Informix driver is one driver implemented using embedded SQL instead of a function-based API. DBD::Ingres may also be worth a look.

C/XS version of Driver.pm

A lot of the code in the Driver.pm file is very similar to the code for pure Perl modules - see above. However, there are also some subtle (and not so subtle) differences, including:

The variables $DBD::Driver::{dr|db|st}::imp_data_size are not defined here, but in the XS code, because they declare the size of certain C structures.
Some methods are typically moved to the XS code, in particular prepare(), execute(), disconnect(), disconnect_all() and the STORE() and FETCH() methods.
Other methods are still part of Driver.pm, but have callbacks to the XS code.

If the driver-specific parts of the imp_drh_t structure need to be formally initialized (which does not seem to be a common requirement), then you need to add a call to an appropriate XS function in the driver method of DBD::Driver::driver(), and you define the corresponding function in Driver.xs, and you define the C code in dbdimp.c and the prototype in dbdimp.h.

For example, DBD::Informix has such a requirement, and adds the following call after the call to _new_drh() in Informix.pm:

  DBD::Informix::dr::driver_init($drh);

and the following code in Informix.xs:

  # Initialize the DBD::Informix driver data structure
  void
  driver_init(drh)
      SV *drh
      CODE:
      ST(0) = dbd_ix_dr_driver_init(drh) ? &sv_yes : &sv_no;

and the code in dbdimp.h declares:

  extern int dbd_ix_dr_driver_init(SV *drh);

and the code in dbdimp.ec (equivalent to dbdimp.c) defines:

  /* Formally initialize the DBD::Informix driver structure */
  int
  dbd_ix_dr_driver(SV *drh)
  {
      D_imp_drh(drh);
      imp_drh->n_connections = 0;       /* No active connections */
      imp_drh->current_connection = 0;  /* No current connection */
      imp_drh->multipleconnections = (ESQLC_VERSION >= 600) ? True : False;
      dbd_ix_link_newhead(&imp_drh->head