NAME

DBIx::Librarian - Manage SQL in template files

SYNOPSIS

use DBIx::SQLLibrary;

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;

OBJECTIVES

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.

FEATURES

• 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.

• SQL files can be edited while the application is running, and the modified SQL will be used when that query is next executed.

• Database connection can be passed into the Librarian initializer, or it will create it internally.

ENVIRONMENT VARIABLES

DBIx::Librarian will use the following:

DBI_DSN       standard DBI connection parameters
DBI_USER
DBI_PASS

DBIX_LIBRARIAN_TRACE  turns on basic internal logging

DESCRIPTION

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}

TO DO

• 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.

WARNINGS

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.

METHODS

new

my $dblbn = new DBIx::Librarian({ name => "value" ... });

Supported Librarian parameters:

LIB         Search path for SQL files.  Defaults to [ "sql" ]

EXTENSION   Filename extension for SQL files.  Defaults to ".sql"

AUTOCOMMIT  If set, will commit() upon completion of all the SQL
            statements in a file.  If not set, the application must
            call $dblbn->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.

DBI_DSN     passed directly to DBI::connect
DBI_USER    passed directly to DBI::connect
DBI_PASS    passed directly to DBI::connect

execute

$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 will be located by searching through the directory path in the LIB parameter for a file named "label.EXTENSION". The first matching file will be used. Conflicts are not detected.

Return value is the number of non-SELECT SQL statements that were executed, if you find that useful.

commit

Invokes commit() on the database handle. Not needed unless $dblbn->delaycommit() has been called.

rollback

Invokes rollback() on the database handle. Not needed unless $dblbn->delaycommit() has been called.

autocommit

Sets the AUTOCOMMIT flag. Once set, explicit commit and rollback are not needed.

delaycommit

Clears the AUTOCOMMIT flag. Explicit commit and rollback will be needed to apply changes to the database.

disconnect

$dblbn->disconnect;

Disconnect from the database. Database handle and any active statements are discarded.

AUTHOR

Jason W. May <jmay@pobox.com>

COPYRIGHT

Copyright (C) 2001 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.

TEST SUITE

Under development.

SEE ALSO

Class:Phrasebook::SQL
Ima::DBI
SQL::Catalog
DBIx::SearchProfiles
DBIx::Abstract

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

cpanm DBIx::Librarian

perl -MCPAN -e shell
install DBIx::Librarian