DBIx::Librarian - Manage SQL in repository outside code
use DBIx::Librarian; my $dblbn = new DBIx::Librarian; my $data = { id => 473 }; eval { $dblbn->execute("lookup_employee", $data); }; die $@ if $@; print "Employee $data->{id} is $data->{name}\n"; $dblbn->disconnect;
Separation of database logic from application logic (SQL from Perl)
Simple interface - sacrifices some flexibility in exchange for code readability and development speed
Leave SQL syntax untouched if possible; support any extensions that are supported by the underlying database
Support transaction capability if the database allows it
This is NOT an object-to-relational-mapping toolkit or a persistence framework. For that sort of thing, see SPOPS or any of several other excellent modules. The combination of DBIx::Librarian and Template Toolkit or one of the other templating packages will give the basis of a fairly comprehensive database-driven application framework.
Support full complexity of Perl associative data structures
Multiple SQL statements chained in a single execute() invocation. Use results from one call as inputs to the next.
Each execute() is automatically a transaction, comprising one or more statements per the above. Optional delayed commit to collect multiple invocations into a transaction. Note that if your database doesn't support transactions (e.g. vanilla mySQL), then you're still out of luck here.
Processing modes for select statements: exactly one row, zero-or-one, multiple rows (zero to many); optional exception on receiving multiple rows when expecting just one. SQL syntax is extended to provide these controls.
Support bind variables, and on-the-fly SQL generation through substitution of entire SQL fragments.
Supports multiple repositories for queries - currently supports individual files and multiple-query files.
Database connection can be passed into the Librarian initializer, or it will create it internally.
If the database connection is down when execute() is called, Librarian will attempt to re-connect.
Sets DBI LongReadLen and LongTruncOk to allow for fetching long values. Optional LONGREADLEN parameter to DBIx::Librarian::new will be passed through to DBI (default 1000).
DBIx::Librarian will use the following:
DBI_DSN standard DBI connection parameters DBI_USER DBI_PASS
This is for data manipulation (SELECT, INSERT, UPDATE, DELETE), not for data definition (CREATE, DROP, ALTER). Some DDL statements may work inside this module, but correct behavior is not guaranteed.
Results of "SELECT1 colname FROM table", expected to return a single row:
{ colname => "value" } Access via $data->{colname} If more than one row is returned, raise an exception.
Results of "SELECT* colname FROM table", expected to return multiple rows (note alteration to standard SQL syntax):
[ { colname => "vala" }, { colname => "valb" }, { colname => "valc" } ] Access via $data->[n]->{colname}
Results of "SELECT1 col1, col2 FROM table", expected to return a single row:
{ col1 => "valA", col2 => "valB", } Access via $data->{colname} If more than one row is returned, raise an exception.
Results of
SELECT* col1 "record.col1", col2 "record.col2", col3 "record.col3" FROM table
expected to return multiple rows:
{ record => [ { col1 => "val1a", col2 => "val2a", col3 => "val3a" }, { col1 => "val1b", col2 => "val2b", col3 => "val3b" }, { col1 => "val1c", col2 => "val2c", col3 => "val3c" }, ] } Access via $data->{record}[n]->{colname}
Endeavor to consolidate some of this work with other similar modules
Optional constraint on number of rows returned by SELECT statements
Optional cancellation of long-running queries
Verbosity controls for logging during initialization and query execution; tie in with DBI tracing
Limits on number of cached statement handles. Some databases may place limits on the number of concurrent handles. Some sort of LRU stack of handles would be useful for this.
Consider whether DBI Taint mode would be appropriate here.
Make sure this works properly with threads.
Improve regex matching for substitution variables in SQL statements so they handle quoting and comments.
Additional SQL storage options, e.g. SQL::Catalog (store in a database - should be able to keep SQL in a different database from the app data), Class::Phrasebook::SQL (store in XML).
You must call $dblbn->disconnect explicitly before your program terminates.
This module uses strict throughout. There is one notable side-effect; if you have a scalar value in a hash element:
$data->{name} = "John"
and you run a multi-row SELECT with the same field as a target:
select* name, department from EMPLOYEE
then you are likely to get an error like this:
Can't use string ("John") as an ARRAY ref while "strict refs" in use at .../DBIx/Librarian/Statement/SelectMany.pm line XXX.
This is because it is trying to write values into
$data->{name}[0] $data->{name}[1] etc.
Recommended syntax for multi-row, multi-column SELECTs is:
select* name "employee.name", department "employee.dept" from EMPLOYEE
so then you can access the information via
$data->{employee}[0]->{name} $data->{employee}[0]->{dept} $data->{employee}[1]->{name} etc.
my $dblbn = new DBIx::Librarian({ name => "value" ... });
Supported Librarian parameters:
ARCHIVER Reference to class responsible for caching SQL statements. Default is Data::Library::OnePerFile. LIB If set, passed through to archiver EXTENSION If set, passed through to archiver AUTOCOMMIT If set, will commit() upon completion of all the SQL statements in a tag (not after each statement). If not set, the application must call commit() directly. Default is set. ALLARRAYS If set, all bind and direct substition variables will be obtained from element 0 of the named array, rather than from scalars. Default is off. DBH If set, Librarian will use this database handle and will not open one itself. DBI_DSN passed directly to DBI::connect DBI_USER passed directly to DBI::connect DBI_PASS passed directly to DBI::connect LONGREADLEN passed through to "LongReadLen" DBI parameter. Defaults to 10000. MAXSELECTROWS Set to a numeric value. Limits the number of rows returned by a SELECT call. Defaults to 1000.
$dblbn->prepare(@tag_list);
Retrieves, prepares and caches a list of SQL queries.
$dblbn->can("label");
Returns true if a valid SQL block exists for tag "label". Side effect is that the SQL is prepared for later execution.
$dblbn->execute("label", $data);
$data is assumed to be a hash reference. Inputs for bind variables will be obtained from $data. SELECT results will be written back to $data.
The SQL block is obtained from the repository specified above.
An array of two values is returned: Total number of rows affected by all SQL statements (including SELECTs) Reference to a list of the individual rowcounts for each statement
May abort for various reasons, primarily Oracle errors. Will abort if a SELECT is attempted without a $data target.
Invokes commit() on the database handle. Not needed unless $dblbn->delaycommit() has been called.
Invokes rollback() on the database handle. Not needed unless $dblbn->delaycommit() has been called.
Sets the AUTOCOMMIT flag. Once set, explicit commit and rollback are not needed.
Clears the AUTOCOMMIT flag. Explicit commit and rollback will be needed to apply changes to the database.
$dblbn->disconnect;
Disconnect from the database. Database handle and any active statements are discarded.
$dblbn->is_connected;
Returns boolean indicator whether the database connection is active. This depends on the $dbh->{Active} flag set by DBI, which is driver-specific.
Declares two log channels using Log::Channel, "init" and "exec". Connect and disconnect events are logged to the init channel, query execution (prepare, execute, commit, rollback) to exec.
See also the channels for DBIx::Librarian::Statement logging.
Jason W. May <jmay@pobox.com>
Copyright (C) 2001-2003 Jason W. May. All rights reserved. This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Under development.
Class:Phrasebook::SQL Ima::DBI SQL::Catalog DBIx::SearchProfiles DBIx::Abstract DBIx::Recordset Tie::DBI Relevant links stolen from SQL::Catalog documentation: http://perlmonks.org/index.pl?node_id=96268&lastnode_id=96273 http://perlmonks.org/index.pl?node=Leashing%20DBI&lastnode_id=96268
2 POD Errors
The following errors were encountered while parsing the POD:
'=item' outside of any '=over'
You forgot a '=back' before '=head1'
To install DBIx::Librarian, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Librarian
CPAN shell
perl -MCPAN -e shell install DBIx::Librarian
For more information on module installation, please visit the detailed CPAN module installation guide.