DBIx::FlexibleBinding - Flexible parameter binding and record fetching
This module extends the DBI allowing you choose from a variety of supported parameter placeholder and binding patterns as well as offering simplified ways to interact with datasources, while improving general readability.
############################################################################### # SCENARIO 1 # A connect followed by a prepare-execute-process cycle ############################################################################### use DBIx::FlexibleBinding ':all'; use constant DSN => 'dbi:mysql:test;host=127.0.0.1'; use constant SQL => << '//'; SELECT solarSystemName AS name FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // # Pretty standard connect, just with the new DBI subclass ... # my $dbh = DBIx::FlexibleBinding->connect(DSN, '', '', { RaiseError => 1 }); # Prepare statement using named placeholders (not bad for MySQL, eh) ... # my $sth = $dbh->prepare(SQL); # Execute the statement (parameter binding is automatic) ... # my $rv = $sth->execute(is_regional => 1, minimum_security => 1.0); # Fetch and transform rows with a blocking callback to get only the data you # want without cluttering the place up with intermediate state ... # my @system_names = $sth->processall_hashref(callback { $_->{name} }); ############################################################################### # SCENARIO 2 # Let's simplify the previous scenario using the database handle's version # of that processall_hashref method. ############################################################################### use DBIx::FlexibleBinding ':all', -alias => 'DFB'; use constant DSN => 'dbi:mysql:test;host=127.0.0.1'; use constant SQL => << '//'; SELECT solarSystemName AS name FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // # Pretty standard connect, this time with the DBI subclass package alias ... # my $dbh = DFB->connect(DSN, '', '', { RaiseError => 1 }); # Cut out the middle men ... # my @system_names = $dbh->processall_hashref(SQL, is_regional => 1, minimum_security => 1.0, callback { $_->{name} }); ############################################################################### # SCENARIO 3 # The subclass import method provides a versatile mechanism for simplifying # matters further. ############################################################################### use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB' ]; use constant DSN => 'dbi:mysql:test;host=127.0.0.1'; use constant SQL => << '//'; SELECT solarSystemName AS name FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // # MyDB will represent our datasource; initialise it ... # MyDB DSN, '', '', { RaiseError => 1 }; # Cut out the middle men and some of the line-noise, too ... # my @system_names = MyDB(SQL, is_regional => 1, minimum_security => 1.0, callback { $_->{name} });
This module subclasses the DBI to provide improvements and greater flexibility in the following areas:
Accessing and interacting with datasources
Parameter placeholder and data binding
Data retrieval and processing
The module's -subs import option may be used to create and import special soubroutines into the caller's own namespace, whose roles are to act as proxies for database or statement handles. To begin with, these subroutines exist in an undefined state and aren't very useful. The caller should first assign them to a database or statement handle in order to them.
-subs
These proxies may be used in a variety of ways. Exactly what they do depends upon the context in which they are used.
Use for connecting to datasources
use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB' ]; # Pass in any set of well-formed DBI->connect(...) arguments to associate # your name with a live database connection ... # MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } ); # Or, simply pass an existing database handle as the only argument ... # MyDB($dbh);
Use them to represent database handles
use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB' ]; use constant SQL => << '//'; SELECT * FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } ); # If your name is already associated with a database handle then just call # it with no parameters to use it as such ... # my $sth = MyDB->prepare(SQL);
Use them to represent statement handles
use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB', 'solar_systems' ]; use constant SQL => << '//'; SELECT * FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } ); my $sth = MyDB->prepare(SQL); # Simply call the statement handle proxy, passing a statement handle in as # the only argument ... # solar_systems($sth);
Use to interact with the represented database and statement handles
use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB', 'solar_systems' ]; use constant SQL => << '//'; SELECT * FROM mapsolarsystems WHERE regional = :is_regional AND security >= :minimum_security // MyDB( 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 } ); # Use the database handle proxy to prepare, bind and execute statements, then # retrieve the results ... # # Use the database handle proxy to prepare, bind and execute statements, then # retrieve the results ... # my $array_of_hashrefs = MyDB(SQL, is_regional => 1, minimum_security => 1.0); # In list context, results come back as lists ... # my @array_of_hashrefs = MyDB(SQL, is_regional => 1, minimum_security => 1.0); # Using -subs also relaxes strict 'subs' in the caller's scope, so pretty-up # void context calls by losing the parentheses, if you wish to use callbacks # to process the results ... # MyDB SQL, is_regional => 1, minimum_security => 1.0, callback { printf "%-16s %.1f\n", $_->{solarSystemName}, $_->{security}; }; # You can use proxies to represent statements, too. Simply pass in a statement # handle as the only argument ... # my $sth = MyDB->prepare(SQL); solar_systems($sth); # Using "solar_systems" as statement proxy. # Now, when called with other types of arguments, those argument values are # bound and the statement is executed ... # my $array_of_hashrefs = solar_systems(is_regional => 1, minimum_security => 1.0); # In list context, results come back as lists ... # my @array_of_hashrefs = solar_systems(is_regional => 1, minimum_security => 1.0); # Statements requiring no parameters cannot be used in this manner because # making a call to a statement proxy with an arity of zero results in the # statement handle being returned. In this situation, use something like # undef as an argument (it will be ignored in this particular instance) ... # my $rv = statement_proxy(undef); # # Meh, you can't win 'em all!
Just be aware that this option automatically relaxes strict 'refs' for the remainder of the caller's scope containing the use directive. That is, unless use strict 'refs' or use strict appears after that point.
strict 'refs'
use
use strict 'refs'
use strict
This module provides support for a wider range of data-binding and parameter placeholder schemes. In addition to maintaining support for standard positional placeholders (?), this module provides additional support for numeric placeholders (:N) and (?N) and named placeholders (:NAME and @NAME).
?
:N
?N
:NAME
@NAME
The process of binding data values to parameters is, by default, completely automated and removes a significant part of the workload from the traditional prepare-bind-execute cycle. It is also possible to swtch off the automatic binding feature.
The following familiar operations have been enhanced scenes to accommodate these changes.
$DATABASE_HANDLE->prepare($STATEMENT, \%ATTR);
$DATABASE_HANDLE->do($STATEMENT, \%ATTR, @DATA);
$STATEMENT_HANDLE->bind_param($NAME_OR_POSITION, $VALUE, \%ATTR);
$STATEMENT_HANDLE->execute(@DATA);
Four new methods, each available for database and statement handles, have been implemented to provide new ways to fetch and optionally transform individual rows and entire result sets.
processrow_arrayref
Fetches the next row as an array reference, optionally transforms it using blocking callbacks, and returns the result which may me a reference or other scalar value.
processrow_hashref
Fetches the next row as an hash reference, optionally transforms it using blocking callbacks, and returns the result which may me a reference or other scalar value.
processall_arrayref
Fetches all rows as array references, optionally transforms them using blocking callbacks, and returns the entire result set as an array reference (or as an array when in called in list context).
processall_hashref
Fetches all rows as hash references, optionally transforms them using blocking callbacks, and returns the entire result set as an array reference (or as an array when in called in list context).
A boolean setting used to determine whether or not automatic parameter binding should take place when executing statements prepared using a non-standard placeholder scheme (i.e anything other than the standard positional (?) scheme).
The default setting is 1 and binding is automatic. You should rarely, if ever, need to change this.
1
A string setting used by those subroutines listed using the -subs import option and being used as proxies for database or statement handles. It determines which method gets called to fetch results when the proxy is called upon to execute statements.
The default is for these souroutines setting is processall_hashref, one of the methods defined by the DBIx::FlexibleBinding::db package.
DBIx::FlexibleBinding::db
Use this import tag to pull all of the package's exportable symbol table entries into the caller's namespace. Currently, only one subroutine (callback) is exported upon request.
callback
This option may be used by the caller to select an alias to use for this package's unwieldly namespace.
use DBIx::FlexibleBinding -alias => 'DBIFB'; my $dbh = DBIFB->connect('dbi:SQLite:test.db', '', '');
This option creates special subroutines that may be used to instantiate and interact with DBI database handles, and exports those subroutines into the caller's namespace at compile time.
use DBIx::FlexibleBinding ':all', -subs => [ 'MyDB' ]; # Initialise by passing in a valid set of DBI->connect(...) arguments. # The database handle will be the return value. # MyDB 'dbi:mysql:test;host=127.0.0.1', '', '', { RaiseError => 1 }; # Or, initialise by passing in a DBI database handle. # The handle is also the return value. # MyDB $dbh; # Once initialised, use the subroutine as you would a DBI database handle. # my $statement = << '//'; SELECT solarSystemName AS name FROM mapsolarsystems WHERE security >= :minimum_security // my $sth = MyDB->prepare($statement); # Or use it as an expressive time-saver! # my $array_of_hashrefs = MyDB($statement, security => 1.0); my @system_names = MyDB($statement, minimum_security => 1.0, callback { return $_->{name}; }); MyDB $statement, minimum_security => 1.0, callback { my ($row) = @_; print "$row->{name}\n"; };
my $value = $dbh->processrow_arrayref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks);
If the statement is presented as a string then it is first prepared using, if present, the optional statement attributes. The statement may also be a pre- prepared statement handle.
If auto binding is enabled and there are data bindings, these are bound to their respective parameter placeholders and the statement is executed.
The method will then fetch the first and only row, initially presented as an array reference, optionally transforming it using a chain of zero or more blocking callbacks.
Returns the transformed scalar value.
A transformation stage need not present the transformed data in the same manner it was presented, though it is better if a scalar value of some sort is returned.
A transformation stage may eliminate a row from the result set by returning an empty list.
my $value = $dbh->processrow_hashref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks);
The method will then fetch the first and only row, initially presented as a hash reference, optionally transforming it using a chain of zero or more blocking callbacks.
my $array_of_values = $dbh->processall_arrayref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks); my @array_of_values = $dbh->processall_arrayref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks);
The method will then fetch the entire result set, initially presenting rows as array references, optionally transforming each rows using a chain of zero or more blocking callbacks.
Returns the transformed result set as a reference to an array or as a list depending on calling context.
my $array_of_values = $dbh->processall_hashref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks); my @array_of_values = $dbh->processall_hashref($statement_handle_or_string, \%optional_statement_attr, @optional_data_bindings, @optional_callbacks);
The method will then fetch the entire result set, initially presenting rows as hash references, optionally transforming each rows using a chain of zero or more blocking callbacks.
The following symbols are exported when requested by name or through the use of the :all tag.
:all
A simple piece of syntactic sugar that announces a callback. The code reference it precedes is blessed as a Params::Callbacks::Callback object, disambiguating it from unblessed subs that are being passed as standard arguments.
Params::Callbacks::Callback
Multiple callbacks may be chained together with or without comma separators:
callback { ... }, callback { ... } # Valid callback { ... } callback { ... } # Valid, too!
There are no automatic exports.
Iain Campbell, <cpanic at cpan.org>
<cpanic at cpan.org>
https://github.com/cpanic/DBIx-FlexibleBinding
Please report any bugs or feature requests to bug-dbix-anybinding at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-FlexibleBinding. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.
bug-dbix-anybinding at rt.cpan.org
You can find documentation for this module with the perldoc command.
perldoc DBIx::FlexibleBinding
You can also look for information at:
RT: CPAN's request tracker (report bugs here)
http://rt.cpan.org/NoAuth/Bugs.html?Dist=DBIx-FlexibleBinding
AnnoCPAN: Annotated CPAN documentation
http://annocpan.org/dist/DBIx-FlexibleBinding
CPAN Ratings
http://cpanratings.perl.org/d/DBIx-FlexibleBinding
Search CPAN
http://search.cpan.org/dist/DBIx-FlexibleBinding/
Test data set extracted from Fuzzwork's MySQL conversion of CCP's EVE Online Static Data Export:
Fuzzwork https://www.fuzzwork.co.uk/
EVE Online http://www.eveonline.com/
Eternal gratitude to GitHub contributors:
Syohei Yoshida http://search.cpan.org/~syohex/
Copyright 2015 Iain Campbell.
This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 dated June, 1991 or at your option any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
A copy of the GNU General Public License is available in the source tree; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
To install DBIx::FlexibleBinding, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::FlexibleBinding
CPAN shell
perl -MCPAN -e shell install DBIx::FlexibleBinding
For more information on module installation, please visit the detailed CPAN module installation guide.