RFC: declarative DBI programming
From: Aaron J . Mackey (amackey_at_virginia.edu)
Date: 12/24/03
- Next message: Michael Peppler: "Announce: DBD::Sybase 1.02"
- Previous message: Sean: "Is Oracle 9i supported under DBD::Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Wed, 24 Dec 2003 11:56:34 -0500 To: dbi-users@perl.org
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 => \@want,
);
my %data;
@data{@want} = @{$output}{@want};
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 => \@want
-type => "select");
warn "Missing fields: $missing\n" if $missing;
my %data;
@data{@want} = @{$output}{@want};
$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 <amackey@virginia.edu>. 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
(amackey@virginia.edu).
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
- Next message: Michael Peppler: "Announce: DBD::Sybase 1.02"
- Previous message: Sean: "Is Oracle 9i supported under DBD::Oracle"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
