NAME
VERSION
DATE
SYNOPSIS
DESCRIPTION
INTERFACE
PRIVATE
NOTES
AUTHOR
SEE ALSO
POD ERRORS

NAME

Bric::Util::DBI - The Bricolage Database Layer

VERSION

$Revision: 1.17 $

DATE

$Date: 2002/08/14 19:47:37 $

SYNOPSIS

use Bric::Util::DBI qw(:standard);

my @cols = qw(id lname fname mname title email phone foo bar bletch);

my $select = prepare_c(qq{
    SELECT @cols
    FROM   person
    WHERE  person_id = ?
});

$self->_set(\@cols, row_aref($select, $id));

DESCRIPTION

This module exports a number of database functions for use by Bricolage object classes. These functions have been designed to maximize database independence by implementing separate driver modules for each database platform. These modules, Bric::DBD::*, export into Bric::Util::DBI the variables and functions necessary to provide database-independent functions for getting and setting primary keys and dates in the format required by the database (but see Bric::Util::Time for the time formatting functions).

Bric::Util::DBI also provides the principal avenue to querying the database. No other Bricolage module should use DBI. The advantage to this approach (other than some level of database independence) is that the $dbh is stored in only one place in the entire application. It will not be generated in every module, or stored in every object. Indeed, objects themselves should have no knowledge of the database at all, but should rely on their methods to query, insert, update, and delete from the database using the functions exported by Bric::Util::DBI.

Bric::Util::DBI is not a complete database-independent solution, however. In particular, it does nothing to translate between the SQL syntaxes supported by different database platforms. As a result, you are encouraged to write your queries in as generic a way as possible, and to comment your code copiously when you must use proprietary or not-widely supported SQL syntax (such as outer joins).

NOTE: Bric::Util::DBI is intended only for internal use by Bricolage modules. It must not be used anywhere else in the application (e.g., in an Apache startup file) or users of the application may be able to gain access to our database.

INTERFACE

There are several ways to use Bric::Util::DBI. Some options include:

use Bric::Util::DBI qw(:standard);            # Get the standard db functions.
use Bric::Util::DBI qw(:standard :trans);     # Get standard and transactional functions.
use Bric::Util::DBI qw(:all);                 # Get all the functions.
use Bric::Util::DBI qw(prepare_c); # Get specific functions.

The first example imports all the functions you are likely to need in the normal course of writing a Bricolage class. The second example imports the standard functions plus functions needed for managing transactions. The third example imports all the functions and variables provided by Bric::Util::DBI. These should cover all of your database needs. The last example imports only a few key functions and variables. You may explicitly import as many functions and variables as you wish in this way. Specifying no parameters, e.g.,

use Bric::Util::DBI;

will compile DBI, but will provide no database access functions. You are not going to want to do this.

Here are the functions and variables imported with each import list:

standard

prepare_c()
row_aref()
fetch()
execute()
next_key()
last_key()
bind_columns()

trans

begin()
commit()
rollback()

all

all of the above, plus

prepare()
prepare_ca()
col_aref()
db_date_parts()
bind_col()
bind_param()
DB_DATE_FORMAT - the strftime format for the date format used by the databse. Used by Bric::Util::Time; you should not need this - use the functions exported by Bric::Util::Time instead.

Each of the functions below that will directly access the database will first check for a connection to the database and establish the connection if it does not exist. There is no need to worry about accessing or storing a $dbh in any Bricolage module. Plus, each function handles all aspects of database exception handling so tht you do not have to. The exception is with the transactional functions; see Begin() below for more information.

Constructors

NONE.

Destructors

NONE.

Public Class Methods

NONE.

Public Instance Methods

NONE.

Functions

my $bool = is_num(@values)

Alias for DBI::looks_like_number() to determine whether or not the values passed are numbers. Returns true for each value that looks like a number, and false for each that does not. Returns undef for each element that is undefined or empty.

Throws: NONE.

Side Effects: NONE.

Notes: NONE.

$sth = prepare($sql)

$sth = prepare($sql, $attr)

$sth = prepare($sql, $attr, $DEBUG)

Returns an $sth from $dbh->prepare. Pass any attributes you want associated with your $sth via the $attr hashref. If $DEBUG is true, it will also issue a warning that prints $sql. In general, use prepare_c() instead of prepare().

IMPORTANT: We strongly encourage only very specific uses of statement handles. It is easy to use them inefficiently, but the following guidelines should keep your code as speedy as possible. But the main point is: Use only functions exported by Bric::Util::DBI, not statement handle methods.

Use prepare_c() whenever possible, as it will cache the $sth for future use, even if your copy of it goes out of scope. This will save a lot of time for frequently-used queries, as they will only be compiled once per process. If you find that you are frequently doing only partial fetches from a statement handle, use prepare_ca().
Always use placeholders. If you have got a query you want to stick a variable in to the WHERE clause, do not put in the variable! Put in a placeholder (?) instead! Doing so allows the same statement to be used over and over without recompiling in the database. Placeholders also eliminate the need to use the DBI quote() method (which, you will notice, is not exported by this module).
When fetching values back from the statement handle, always bind variables to columns (using bind_col($select) or bind_columns($select)), and fetch each row with the fetch($select) function (see below). Do not use statement handle methods yourself; avoid using the $select->fetchrow_array() method, and especially the $select->fetchrow_hashref() methods, as they are much slower than fetch($select) with bound columns. If you need to use one of these methods let me know and we will see about adding them as functions to Bric::Util::DBI. But it should not be necessary. Better yet, anytime you find yourself wanting to use $select->fetchrow_hashref(), take it as a cue to go back, look at your code design, and decide whether you are making the best design decisions.

Throws:

Unable to connect to database.
Unable to prepare SQL statement.

Side Effects: Calls $dbh->prepare().

Notes: NONE.

my $sth = prepare_c($sql)

my $sth = prepare_c($sql, $attr)

my $sth = prepare_c($sql, $attr, $DEBUG)

Returns an $sth from $dbh->prepare_cached. Pass any attributes you want associated with your $sth via the $attr hashref. If $DEBUG is true, it will also issue a warning that prints $sql. A warning will also be issued if the $sth returned is already active.

See also the important note in the prepare() documentation above.

Throws:

Unable to connect to database.
Unable to prepare SQL statement.

Side Effects: Calls $dbh->prepare_cached().

Notes: NONE.

my $sth = prepare_ca($sql)

my $sth = prepare_ca($sql, $attr)

my $sth = prepare_ca($sql, $attr, $DEBUG)

Returns an $sth from $dbh->prepare_cached, and will not issue a warning if the $sth returned is already active. Pass any attributes you want associated with your $sth via the $ATTR hashref. If $DEBUG is true, it will also issue a warning that prints $sql.

See also the important note in the prepare() documentation above.

Throws:

Unable to connect to database.
Unable to prepare SQL statement.

Side Effects: Calls $dbh->prepare_cached() with the active flag set to true.

Notes: NONE.

my $ret = begin()

begin();
eval {
    execute($ins1);
    execute($ins2);
    execute($upd);
    commit();
};
if ($@) {
    rollback();
    die $@;
}

Sets $dbh->{AutoCommit} = 0. Use before a series of database transactions so that none of them is committed to the database until commit() is called. If there is a problem, call rollback() instead. Each of these two functions will also turn AutoCommit back on, so if you need to more transactional control, be sure to call begin() again. Also, be sure to always call either commit() or rollback() when you are done with your transactions, or AutoCommit will not be switched back on and future database activity will have unexpected results (nothing will be committed - except you, you insane hacker!).

Throws:

Unable to connect to database.
Unable to turn AutoCommit off.

Side Effects: Calls $dbh->{AutoCommit} = 0.

Notes: NONE.

my $ret = commit()

Call this function after calling begin() and executing a series of database transactions. It commits the transactions to the database, and then sets AutoCommit to true again. See begin() for an example.

Throws:

Unable to connect to database.
Unable to commit transactions.
Unable to turn on AutoCommit.

Side Effects: Calls $dbh->commit.

Notes: NONE.

my $ret = rollback()

Call this function after calling begin() and executing a series of database transactions, where one or more of the transactions fails and they all need to be rolled back. See begin() for an example.

Throws:

Unable to connect to database.
Unable to rollback transactions.
Unable to turn on AutoCommit.

Side Effects: Calls $dbh->commit.

Notes: NONE.

my $ret = execute($sth, @params)

Executes the prepared statement. Use this instead of $sth->execute(@params) and it will take care of exception handling for you. Returns the value returned by $sth->execute().

Throws:

Unable to execute SQL statement.

Side Effects: Calls $sth->execute().

Notes: NONE.

my $ret = bind_columns($sth, @args)

Binds variables to the columns in the statement handle. Functions exactly the same as $sth->bind_columns, only it handles the exception handling for you. Returns the value returned by $sth->bind_columns.

Throws:

Unable to bind to columns to statement handle.

Side Effects: Calls $sth->bind_columns().

Notes: NONE.

my $ret = bind_col($sth, @args)

Binds a variable to a columns in the statement handle. Functions exactly the same as $sth->bind_col, only it handles the exception handling for you. Returns the value returned by $sth->bind_col.

Throws:

Unable to bind to column to statement handle.

Side Effects: Calls $sth->bind_columns().

Notes: NONE.

my $ret = bind_param($sth, @args)

Binds parameter to the columns in the statement handle. Functions exactly the same as $sth->bind_param, only it handles the exception handling for you. Returns the value returned by $sth->bind_param.

Throws:

Unable to bind parameters to columns in statement handle.

Side Effects: Calls $sth->bind_columns().

Notes: NONE.

my $ret = fetch($sth)

Performs $sth->fetch() and returns the result. Functions exactly the same as $sth->fetch, only it handles the exception handling for you.

Throws:

Unable to fetch row from statement handle.

Side Effects: Calls $sth->bind_columns().

Notes: NONE.

my $ret = finish($sth)

Performs $sth->finish() and returns the result. Functions exactly the same as $sth->finish, only it handles the exception handling for you.

Throws:

Unable to finish statement handle.

Side Effects: Calls $sth->finish().

Notes: Do not confuse this function with finishing transactions. It simply tells a SELECT statement handle that you are done fetching records from it, so it can free up resources in the database. If you have started a series of transactions with begin(), finish() will not commit them; only commit() will commit them, and rollback() will roll them back.

################################################################################

my $row = row_aref($select, @params)

Executes the SELECT statement in $select and returns the first row of values in an array reference. Preferred for use fetching just one row, but if passed a multi-row query, will return the first row only. If placeholders have been used in $select, pass the parameters that map to them. This function will prepare() the query in $select, but it will not prepare_c() it. Thus it is generally prefered to prepare_c($select) yourself and then pass it to row_aref() as an $sth. See the Synopsis above for an example.

Throws:

Unable to connect to database.
Unable to select row.

Side Effects: Calls $dbh->selectrow_arrayref().

Notes: NONE.

my @row = row_array($select, @params)

Executes the SELECT statement in $select and returns the first row of values in an array. Preferred for use fetching just one row, but if passed a multi-row query, will return the first row only. If placeholders have been used in $select, pass the parameters that map to them. This function will prepare() the query in $select, but it will not prepare_c() it. Thus it is generally prefered to prepare_c($select) yourself and then pass it to row_array() as an $sth. For an example, see how the Synopsis above does this for row_aref().

Throws:

Unable to connect to database.
Unable to select row.

Side Effects: Calls $dbh->selectrow_array().

Notes: NONE.

my $data = all_aref($select, @params)

Executes $dbh->selectall_arrayref($select) and returns the data structure returned by that DBI method. See DBI(2) for details on the data structure. If placeholders have been used in $select, pass the parameters that map to them. This function will prepare() the query in $select, but it will not prepare_c() it. Thus it is generally prefered to prepare_c($select) yourself and then pass it to all_aref() as an $sth. For an example, see how the Synopsis above does this for row_aref().

This function is not generally recommended for use except for grabbing a very few, simple rows and you do not need to change the data structure. If you do need to change the data structure, it would probably be faster to fetch($select) with bound variables and construct the data structure yourself.

Throws:

Unable to connect to database.
Unable to select all.

Side Effects: Calls $dbh->selectall_arrayref().

Notes: NONE.

my $col = col_aref($select, @params)

Executes the SELECT statement in $select and returns the values of the first column from every row in an array reference. Preferred for fetching many rows for just one column. If placeholders have been used in $select, pass the parameters that map to them. This function will prepare() the query in $select, but it will not prepare_c() it. Thus it is generally prefered to prepare_c($select) yourself and then pass it to col_aref() as an $sth. For an example, see how the Synopsis above does this for row_aref().

Throws:

Unable to connect to database.
Unable to select column into arrayref.

Side Effects: Calls $dbh->selectcol_arrayref().

Notes: NONE.
my $id = next_key($table_name)

my $id = next_key($table_name, $db_name)

Returns an SQL string for inserting the next available key into $db_name.$table_name within the context of a larger INSERT statement. If $db_name is not passed, it defaults to the value stored in $Bric::Cust.

my @cols = qw(id lname fname mname title email phone foo bar bletch);

local $" = ', ';
my $insert = prepare_c(qq{
    INSERT INTO person (@cols)
    VALUES (${\next_key('person')}, ${\join ', ', map '?', @cols[1..$#cols]})
});

# Don't try to set ID - it will fail!
execute($insert, $self->_get(@cols[1..$#cols));

# Now grab the ID!
$self->_set({id => last_key('person')});

Throws: NONE.

Side Effects: NONE.

Notes: NONE.

last_key($table_name)
last_key($table_name, $db_name)
last_key($table_name, $db_name, $DEBUG)

Returns the last sequence number inserted into $db_name.$table_name by the current process. If $db_name is not passed, it defaults to the value stored in $Bric::Cust. Will return undef if this process has not yet inserted anything into $table_name. Use for retreiving an object ID immediately after executing an INSERT statement. See next_key() above for an example.

Throws:
- Unable to connect to database.
- Unable to prepare SQL statement.
- Unable to select row.
Side Effects: Gets the last sequence number by using prepare_c() to prepare the query, and row_aref() to fetch the result.

Notes: NONE.

PRIVATE

NONE.

Private Class Methods

NONE.

Private Instance Methods

NONE.

Private Functions

_connect()

Connects to the database and stores the connection in $dbh. Will re-connect if $dbh->ping fails. Should be called at the start of every function that does database access.

Notes: We may need to override $dbh->ping. If so, do it in the Bric::DBI::DBD::* driver class.

_disconnect()

Disconnects from the database. Called by an END block installed by this package.

_debug_prepare(\$sql)

Prints out debugging messages for a prepare call. Should be called by functions that prepare statements when DEBUG (DBI_DEBUG) is true.

_debug_execute(\@args, $sth)

Prints out debugging messages for an execute call. Should be called by functions that execute statements when DEBUG (DBI_DEBUG) is true.

_debug_prepare_and_execute(\@args, \$sql)

_debug_prepare_and_execute(\@args, \$sth)

Prints out debugging messages for a call that prepares and executes in one call. Should be called by functions that prepare and execute when DEBUG (DBI_DEBUG) is true.

_statement_signature(\$sql)

_statement_signature(\$sth)

Returns a fingerprint for an sql statement or statement handle. Used in debug output to match prepares to executes.

_print_call_trace

Writes out a call trace to STDERR. Should be called by functions that prepare statements when CALL_TRACE (DBI_CALL_TRACE) is true.

_profile_start()

Starts a timer used to profile database calls. Should be called before query execution when DBI_PROFILE is true.

_profile_stop()

Stops the profile timer and writes out the timing results to STDERR. Should be called immediately after query execution when DBI_PROFILE is true.

NOTES

NONE.

AUTHOR

David E. Wheeler <david@wheeler.net>

POD ERRORS

Hey! The above document had some coding errors, which are explained below:

Around line 256:: =back doesn't take any parameters, but you said =back 4
Around line 349:: =back doesn't take any parameters, but you said =back 4
Around line 1200:: Expected '=item *'
Around line 1202:: Expected '=item *'
Around line 1234:: Expected '=item *'
Around line 1236:: Expected '=item *'
Around line 1238:: Expected '=item *'
Around line 1278:: =back doesn't take any parameters, but you said =back 4
Around line 1280:: You forgot a '=back' before '=head1'

NAME

VERSION

DATE

SYNOPSIS

DESCRIPTION

INTERFACE

Constructors

Destructors

Public Class Methods

Public Instance Methods

Functions

PRIVATE

Private Class Methods

Private Instance Methods

Private Functions

NOTES

AUTHOR

SEE ALSO

POD ERRORS