/ 
SQL-Interpolate-0.31_01
River stage zero No dependents
/ DBIx::Interpolate

NAME

DBIx::Interpolate - Integrate SQL::Interpolate into DBI

SYNOPSIS

use DBI;
use DBIx::Interpolate qw(:all);

# simple usage
my $dbx = new DBIx::Interpolate($dbh);
$dbx->selectall_arrayref(
    q[SELECT * FROM table WHERE color IN], \@colors,
    q[AND y =], \$x
);

# caching statement handles (for performance)
my $stx = $dbx->prepare();
    # note: $stx represents a set of statement handles ($sth) for a class
    # of queries.
for my $colors (@colorlists) {
    $stx->execute(q[SELECT * FROM table WHERE color IN], $colors);
        # note: this will transparently prepare a new $sth whenever
        # one compatible with the given query invocation is not cached.
    my $ary_ref = $stx->fetchall_arrayref();
}

# using the DBI adapter (dbi_interp) directly
$dbh->selectall_arrayref(dbi_interp
    q[SELECT * FROM mytable WHERE color IN], \@colors,
    q[AND y =], \$x, q[OR], {z => 3, w => 2}
);
# note: dbi_interp typically returns ($sql, \%attr, @bind)

DESCRIPTION

DBIx::Interpolate wraps DBI and inherits from SQL::Interpolate. It does nothing more than bring SQL::Interpolate behavior into DBI. The DBIx::Interpolate interface is very close to that of DBI. All DBI-derived methods look and behave identically or analogously to their DBI counterparts and mainly differ in that certain methods, such as do and select.* expect an interpolation list as input:

$dbx->selectall_arrayref(
    qq[SELECT * from mytable WHERE height > ], \$x);

rather than the typical ($statement, \%attr, @bind_values) of DBI:

$dbx->selectall_arrayref(
    qq[SELECT * from mytable WHERE height > ?], undef, $x);

DBIx::Interpolate also supports statement handle sets. A statement handle set is an abstraction of a statement handle and represents an entire set of statement handles for a given class of SQL queries. This abstraction is useful because a single interpolation list may interpolate into any number of SQL queries (depending on variable input), so multiple statement handles may need to be managed and cached.

Implementation

The parameters for DBIx::Interpolate methods are internally passed to "dbi_interp", which is a thin wrapper around SQL::Interpolate::sql_interp. dbi_interp accepts a few additional types of parameters and typically returns ($statement, \%attr, @bind_values), which is passed directed the respective DBI method. Therefore, the above is equivalent to

$dbh->select_arrayref(dbi_interp
    qq[SELECT * from mytable WHERE height > ], \$x
);

which in this case is equivalent to

my($sql, @bind) = sql_interp
    qq[SELECT * from mytable WHERE height > ], \$x
);
$dbh->selectall_arrayref($sql, undef, @bind);

Therefore, DBIx::Interpolate is typically quite thin and follows a design goal of otherwise maintaining as much compatibility with DBI as possible.

Special Cases

dbi_interp can convert some DBI-specific objects into additional return values expected by certain DBI methods. For example, selectall_hashref accepts an additional $key_field parameter:

$dbh->selectall_hashref($statement, $key_field, \%attr, @bind_values);

dbi_interp can generate the $key_field parameter (as well as \%attr) as follows:

my($sql, $key_field, $attr, @bind) = dbi_interp
    "SELECT * FROM mytable WHERE x=", \$x,
    key_field("y"), attr(myatt => 1)
# Sets
#   ($sql, $key_field, $attr, @bind) =
#       ("SELECT * FROM mytable WHERE x=?", 'y', {myatt=>1}, $x)

Therefore, one may do

$dbx->selectall_hashref(
    "SELECT * FROM mytable WHERE x=", \$x,
    key_field("y"), attr(myatt => 1));

FUNCTIONS

dbi_interp

This is a wrapper function around sql_interp(). It serves as an adapter that returns also the \%attr value (and sometimes $key_field value) so that the result can be passed directly to the DBI functions.

($sql, $attr, @bind) = dbi_interp(@interp_list);
($sql, $key_field, $attr, @bind) = dbi_interp(@interp_list);

In addition to the parameters accepted by SQL::Interpolate::sql_interp, @interp_list may contain the macros returned by attr and key_field functions. These respectively affect the \%attr and $key_field values returned.

$dbh->selectall_hashref(dbi_interp
   "SELECT * FROM mytable WHERE x=", \$x, key_field("y")
);

dbi_interp is typically unnecessary to use directly since it is called internally by the DBI wrapper methods:

$dbx->selectall_hashref("SELECT * FROM mytable WHERE x=",
    \$x, key_field("y"));
# same as
# $dbh->selectall_hashref(dbi_interp "SELECT * FROM mytable WHERE x=",
#   \$x, key_field("y"));
make_dbi_interp
$dbi_interp = make_dbi_interp(@params);          # functional
$dbi_interp = $interp->make_dbi_interp(@params); # OO

This is similar in make_sql_interp except that is generates a closure around the dbi_interp function or method rather than sql_interp.

key_field
$keyobj = key_field($key_field);

Creates and returns an SQL::Interpolate::Key macro object, which if processed by dbi_interp will cause dbi_interp to return an extra $key_field value in the result so that it is suitable for passing into $dbh->fetchrow_hashref and related methods.

my($sql, $key, $attr, @bind) =
my @params = dbi_interp q[SELECT * FROM mytable], key_field('itemid');
$dbh->selectall_hashref(@params);
attr
$attrobj = attr(%attr);

Creates and returns an SQL::Interpolate::Attr macro object, which if processed by dbi_interp will cause dbi_interp to add the provided key-value pair to the $attr hashref used by DBI methods.

my($sql, $attr, @bind) =
my @params =
  dbi_interp q[SELECT a, b FROM mytable], attr(Columns=>[1,2]);
$dbh->selectcol_arrayref(@params);

DATABASE OBJECT (DBX) METHODS

new (static method)

Creates a new object and optionally creates or attached a DBI handle.

my $dbx = new DBX::Interpolate($db, %params);

$db [optional] is either a DBI database handle or an ARRAYREF containing parameters that will be passed to DBI::connect, e.g. [$data_source, $username, $auth, \%attr]. This parameter may be omitted.

Any additional %params are passed onto SQL::Interpolate::new.

connect (static method)

Connects to a database.

$dbx = DBIx::Interpolate->connect($data_source, $username, $auth, \%attr);

This is identical to DBI::connect except that it returns at DBIx::Interpolate object. An alternate way to connect or attach an existing DBI handle is via the new method.

dbh
$dbh = $dbx->dbh();

Returns the underlying DBI handle $dbh. The is useful if you need to pass the DBI handle to code that does not use SQL::Interpolate.

$dbx->dbh()->selectall_arrayref(
    "SELECT * FROM mytable WHERE x = ?", undef, $x);
do | select.*

These methods are identical to those in DBI except that it takes a parameter list identical to dbi_interp.

my $res = $dbx->selectall_hashref("SELECT * FROM mytable WHERE x=", \$x);
prepare
$stx = $dbx->prepare();

Creates a new statement handle set ($stx of type SQL::Interpolate::STX) associated with $dbx. There are no parameters.

A statement handle set represents a set of statement handles for a class of queries. Up to one statement handle is considered active. Other operations performed on the statement handle set are passed to the active statement handle so that the statement handle set often looks and feels like a regular statement handle.

STATEMENT HANDLE SET (STX) METHODS

new
$stx = new SQL::Interpolate::STX($dbx);

Creates a new statement handle set. Typically this is not called directly but rather is invoked through prepare.

max_sths
$max_sths = $stx->max_sths(); # get
$stx->max_sths($max_sths);    # set

Gets or sets the maximum number of statement handles to cache in the statement handle set. The default and minimum value is 1.

sth
$sth = $stx->sth();

Gets the current active statement handle (e.g. the only that was just executed). Returns undef on none.

sths
$sths = $stx->sths();

Return a hashref of contained statement handles (map: $sql -> $sth).

execute
$rv = $stx->execute(@list);

Executes the query in the given interpolation list against a statement handle. If no statement matching statement handle exists, a new one is prepared. The used statement handle is made the active statement handle. Return an error behavior is similar to DBI's execute.

@list is an interpolation list (suitable for passing to dbi_interp).

fetch...
$ary_ref = $stx->fetchrow_arrayref();

Various fetch.* methods analogous to those in DBIx::Interpolate are available. The fetch will be performed against the active statement handle in the set.

ADDITIONAL EXAMPLES

Preparing and reusing statement handles

# preparing and reusing statement handles
my $stx = $dbx->prepare();
    # note: $stx represents a set of statement handles ($sth) for a class
    # of queries.
$stx->max_sths(3);
for my $colors (@colorlists) {
    $stx->execute(q[SELECT * FROM table WHERE color IN], $colors);
        # note: this will transparently prepare a new $sth whenever
        # one compatible with the given query is not cached.
    my $ary_ref = $stx->fetchall_arrayref();
}

The statement handle set transparently prepare statement handles if ever and whenever the underlying SQL string (and number of bind values) changes. The size of the statement handle cache (3) may be configured to optimize performance on given data sets. Compare this simpler and more flexible code to the example in SQL::Interpolate.

Binding variable types (DBI bind_param)

$dbx->selectall_arrayref(
    "SELECT * FROM mytable WHERE",
    "x=", \$x, "AND y=", sql_var(\$y, SQL_VARCHAR), "AND z IN",
    sql_var([1, 2], SQL_INTEGER)
);

Compare this much simpler code to the example in SQL::Interpolate.

LIMITATIONS

DBI attributes

DBI database handle and statement handle attributes are not currently exposed from the wrapper except via $dbx->dbh()->{...}. Maybe a Tie can be used. e.g. $dbx->{mysql_insert_id}

bind_param_array

DBI's bind_param_array is not currently supported. A syntax as follows might be used

"INSERT INTO mytable", [[...], [...], ...]

Possible Enhancements

Support might be added for something analogous to DBI's bind_param_inout.

Passing identified variables:

my $x = {one => 'two'};
my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", \$x);
$stx->execute_vars();
...
$x->{two} = 'three';
$stx->execute_vars();
...

my $x = {one => 'two'};
my $y = {one => 'three', two => 'four'};
my $stx = $dbx->prepare("SELECT * FROM mytable WHERE", sql_var($x, 'x'));
$stx->execute_vars();
...
$stx->execute_vars(sql_var($x, 'x'); # or?
$stx->execute_vars(x => $x); # or?
...

Conditional macros: (made possible by late expansion of macros)

$blue = 1;
$z = 123;
$stx = $dbx->prepare(
    q[SELECT * FROM mytable WHERE],
    sql_and( sql_if(\$blue,  q[color = "blue"]),
            sql_if(\$shape, sql_fragment(q[shape =], \$shape)),
            'z=', \$z),
    q[LIMIT 10]
);
$stx->execute_vars();
$stx->selectall_arrayref();
$z = 234;
$stx->execute_vars();  # note: $sth unchanged
$stx->selectall_arrayref();
$blue = 0;
$stx->execute_vars();  # note: $sth changed
$stx->selectall_arrayref();

DESIGN NOTES

DBIx::Interpolate is designed to look an feel like DBI even when the DBI interface is not entirely user friendly (e.g. the (fetch|select)(all|row)?_(array|hash)(ref)? and do methods). Still, the approach lowers the learning code and could simplify the process of converting existing DBI code over to SQL::Interpolate.

The use of statement handle sets (STX) is not strictly necessary but is rather designed to mimic DBI's statement handles more than anything else. An alternate solution is for the DBX object to itself be the statement handle set (i.e. cache statement handles like in DBIx::Simple's keep_statements). This latter solution would be simple to add to DBIx::Interpolate as well.

A real solution would probably be to integrate SQL::Interpolate into DBIx::Simple.

CONTRIBUTORS

David Manura (http://math2.org/david)--author.

The existence and original design of this module as an AUTOLOAD wrapper around DBI was suggested by Jim Cromie.

Bug reports and comments on the design are most welcome.

LEGAL

Copyright (c) 2004-2005, David Manura. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. See http://www.perl.com/perl/misc/Artistic.html.

SEE ALSO

Other modules in this distribution

SQL::Interpolate, SQL::Interpolate::Filter, SQL::Interpolate::Macro.

Dependencies: DBI.

Related: DBIx::Simple, SQL::Abstract, DBIx::Abstract, Class::DBI.