RFC: declarative DBI programming

[Aaron J . Mackey]

If interested, please comment on this module I'm currently developing:

NAME
    DBIx::Declare - Interact with DBI-based storage via declarative
    constructs
SYNOPSIS
      use DBIx::Declare;
      use DBI;
      use YAML qw(Load);
      my $dbh = DBI->connect($dsn, $user, $pass);
      my $sql = Load(join("", <DATA>));
      my $dbx = DBIx::Declare->new(-sql => $sql,
                                   -dbh => $dbh);
      my @want = qw(name abbrev);

      my ($output, $missing) =
        $dbx->process(
            -context => { person_id => 10 },
            -wanted => [EMAIL PROTECTED],
        );
      my %data;
      @[EMAIL PROTECTED] = @[EMAIL PROTECTED];
      print "City, state: $data{name}, $data{abbrev}\n";

      __DATA__
      name: get_current_addressinfo
      type: select
      requires:
        - person_id
      provides:
        - address.street1
        - address.street2
        - city.name
        - abbrev
      sql: >
        SELECT street1, street2, city.name, state.abbrev
        FROM   person2address
               INNER JOIN address ON (person2address.address_id = 
address.id)
               INNER JOIN city ON (address.city_id = city.city_id)
               INNER JOIN state ON (city.state_id = state.state_id)
        WHERE  person2address.person_id = ?
          AND  person2address.current = 1

ABSTRACT
    This module aims to separate the SQL required to manipulate data 
storage
    in a relational database from the Perl code that generates and 
utilizes
    the data. Unlike other SQL catalog or phrasebook methods that 
accomplish
    this task, DBIx::Declare also attempts to automatically operate 
(select,
    insert, update, delete) on the requested data, given some input 
context.
    This allows a "parameter-driven" programming approach, particularly
    useful in a templating environment when the Perl code is simply 
matching
    data to template parameters. For example, here's an entirely generic
    template processor using HTML::Template with template inspection:

        my $query = CGI->new();
        my $tmpl = HTML::Template->new(filename => "foo.tmpl");
        # find out what parameters the template wants:
        my @want = map { $tmpl->query(name => $_) eq "VAR" } 
$tmpl->query();

        my ($output, $missing) = $dbx->process(-context => $query->Vars,
                                               -wanted  => [EMAIL PROTECTED]
                                               -type    => "select");
        warn "Missing fields: $missing\n" if $missing;
        my %data;
        @[EMAIL PROTECTED] = @[EMAIL PROTECTED];
        $tmpl->param(%data);

    You could imagine a version of this code running many of your 
templated
    websites; the advantage is that adding a new page might not require
    adding any new code, only a new template -- which may mean that 
you've
    put yourself out of work, or that you can focus on other clever 
things.

DESCRIPTION
  Declarative Programming
    Most programming we do in Perl (and most other languages) is
    *imperative*; we tell the Perl interpreter what the data elements 
are,
    and how to operate on them. For instance, a CGI script that fills 
out an
    HTML::Template from data in a database might (minimally) look like 
this:

      use CGI;
      use HTML::Template;
      use DBI;
      my $q = CGI->new();
      my $dbh = DBI->connect($dsn, $user, $pass);
      my $tmpl = HTML::Template->new(filename => "welcome.tmpl");
      my $sql = "SELECT name FROM user WHERE user_id = ?";
      my $sth = $dbh->prepare($sql);
      my $user_id = $q->param("user_id");
      if($user_id) {
        $sth->execute($user_id);
        my $name = $sth->fetchrow;
        $tmpl->param(NAME => $name);
      }
      print $tmpl->output;

    In contrast, *declarative* programming is a method in which various
    facts and constraints are described explictly; for instance, we can
    reformulate the above example using an imaginary declarative 
language:

      dsn      $dsn  = "dbi:mysql:test";
      user     $user = "user";
      pass     $pass = "pass";
      dbi      $dbh  requires: dsn, user, pass;

      cgi      $q    provides: user_id;

      template $tmpl requires: name
                     provides: output;
      sql      $sql  requires: dbi, user_id
                     provides: name;
      print output;

    Our imaginary interpreter reads these descriptions and will 
*somehow*
    figure out that to fill in the "name" parameter to the template, it 
will
    need to execute the sql query (via the $dbh from DBI) with a 
"user_id"
    obtained from the query. Writing that interpreter is left as an 
exercise
    for the reader, but the code that handles matching up template
    parameters with SQL statements is DBIx::Declare.

  DBIx::Declare
    DBIx::Declare is yet another way to separate raw SQL from the guts 
of a
    Perl program, but rather than simply retrieving the SQL by name (as 
do
    SQL::Catalog and Class::Phrasebook::SQL), DBIx::Declare retrieves 
SQL by
    *context*: a list of currently available parameters that an SQL
    statement might make use of, and a list of desirable data elements 
that
    an SQL statement might provide. Given the context and a catalog of
    annotated SQL statements, DBIx::Declare attempts to *somehow* 
figure out
    the SQL statement(s) necessary to obtain the requested data. Having 
done
    so, it then executes the SQL statement(s) with the given parameters 
and
    returns the requested data. Simple, right?

METHODS
  new
      my $dbx = DBIx::Declare->new( -sql   => $sql,
                                    -dbh   => $dbh,
                                    -cache => 1 );
    Constructor to create a new DBIx::Declare object; parameters are 
defined
    below:

    sql An arrayref of hashrefs describing the SQL "catalog" to be used 
to
        process data. Each hashref should have the following fields:

        name
            A unique label for this SQL construct
        type
            One of "select", "insert", "update" or "delete".
        requires
            An arrayref of field names required to execute this 
statement;
            these fields should be found in the context during 
processing,
            and be used to replace any "?" placeholders. Field names 
may or
            may not be table-qualified.

        provides
            An arrayref of fields that the SQL statement will return; 
field
            names may or may not be table-qualified. "insert" statements
            will provide the autogenerated primary key, if applicable, 
while
            "update" and "delete" statements will only provide a count 
of
            the rows updated or deleted.

        sql The actual SQL statement to be used, including any 
placeholder
            "?" elements.

    dbh A DBI-based database handle.

    cache
        If caching is enabled, DBIx::Declare will save all the row data
        retrieved for each SQL "select" query used to provide the 
requested
        data during a process call; if available, this row data will be 
used
        directly upon subsequent calls to process that require the same 
SQL
        query, using the same input. For instance, if the SYNOPSIS code
        above had instead looked like:

          $output = $dbx->process(-context => { person_id => 10 },
                                  -wanted => [ qw( name ) ]);
          my $city = $output->{name};
          $output = $dbx->process(-context => { person_id => 10 },
                                  -wanted => [ qw( abbrev ) ]);
          my $state = $output->{abbrev};
        then only one database query would have been executed. This 
lends a
        kind of "atomicity" to DBIx::Declare, so that multiple requests 
for
        different fields obtained from the same query occur as if only 
one
        request had occurred. Note that this cache is neither 
persistent nor
        shared; it is private to each DBIx::Declare object.

  sql
      $sql = $dbx->sql();
      $dbx->sql($newsql);
    A get/set accessor method for the SQL catalog.

  add_sql
      $dbx->add_sql($newsql);
    Adds a new SQL hashref (described above) to the internal SQL 
catalog.

  dbh
      $dbh = $dbx->dbh();
      $dbx->dbh($newdbh);
    A get/set accessor method for the DBI database handle.

  cache
      $iscaching = $dbx->cache();
      $dbx->cache(0);
    A get/set accessor method for caching status; use to temporarily 
turn
    caching on and off.

  clear_cache
      $dbx->clear_cache;
    Clears the SQL result cache (see above).

  clone
      my $newdbx = $dbx->clone();
    Obtain a new DBIx::Declare object that shares the same $sql and $dbh
    parameters; useful when you want to maintain the cache of one $dbx
    object but need a "clean slate" to work with.
  process
      ($output, $missing, $info) =
        $dbx->process(-context => $input,
                      -wanted  => $wanted,
                      -type    => $type);
    This is where all the magic happens; given a context hashref of 
named
    input parameters and an arrayref of wanted field names to output, 
this
    method performs the taks of figuring out which SQL query (or 
queries)
    must be run to obtain the desired fields. The $output will be a 
hashref
    of successfully retrieved data, using the $wanted field names as 
keys.
    $missing is an arrayref of remaining wanted field names unable to be
    retrieved automatically. $info is an arrayref of hashrefs of 
information
    about each SQL query chosen to satisfy the request, including the 
name
    of the query, the actual sql, the parameters provided to the query 
and
    the fields used to generate the output. See the debug method for 
further
    info.

    The "-type" argument is used to explicitly specify the type of SQL
    statement we expect to execute; e.g. for filling out templates, we
    expect to use "select" statements, while processing form data might
    involve "insert", "update" or "delete" statements. The default is
    "select".
    The optional "-name" argument may also be used to explicitly 
specify the
    name of an SQL query to be used; this allows DBIx::Declare to behave
    akin to other phrasebook methods..

  debug($info);
      ($output, $missing, $info) = $dbx->process(...);
      warn $dbx->debug($info);
    This is a utility method for converting the $info structure returned
    from process into a formatted text string.
NAMING CONVENTIONS
    For DBIx::Declare to be useful, certain table field naming 
conventions
    should be followed. First, using the bare field name "id" for every
    table's primary key will likely render DBIx::Declare useless; 
instead,
    use "table_id" for primary keys, and identically named foreign keys 
to
    reference the table. Secondly, try to minimize the use of "generic"
    field names, such as "name"; otherwise you'll want to always
    disambiguate between city.name, state.name, person.name, 
company.name,
    etc. However, DBIx::Declare will try to match up table-qualified 
field
    names with unqualfied fields when appropriate (as demonstrated in 
the
    SYNOPSIS code).

AUTHOR INFORMATION
    Copyright 2003, Aaron J. Mackey <[EMAIL PROTECTED]>. All rights
    reserved.
    This library is free software; you can redistribute it and/or 
modify it
    under the same terms as Perl itself.

CREDITS
    This work was inspired by discussions with Bill Pearson.
BUGS
    I'm sure there are situations I haven't encountered that will make 
all
    this break; please report these, and any other bugs to me directly
    ([EMAIL PROTECTED]).

TODO
    counting
        Provide a utility to obtain the count of the number of rows 
obtained
        in a query.

    non-placeholder dynamic queries
        Some SQL constructs can't use the DBI "?" placeholder construct,
        e.g. "person_id IN ( 10, 11, 12, 101)"; provide a mechanism for 
sql
        "interpolation" using parameters.

SEE ALSO
    DBI, SQL::Catalog, Class::Phrasebook::SQL