Name

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

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 = ?
}, undef);

$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:

# Get the standard db functions.
use Bric::Util::DBI qw(:standard);

# Get standard and transactional functions.
use Bric::Util::DBI qw(:standard :trans);

# Get all the functions.
use Bric::Util::DBI qw(:all);

# Get specific functions.
use Bric::Util::DBI qw(prepare_c execute fetch);

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
trans
all

all of the above, plus

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 that 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

ANY
my @p = Bric::Biz::Person->list({ lname => ANY( 'wall', 'conway') });

Use this function when you want to perform a query comparing more than one value, and you want objects returned that match any of the values passed.

Throws:

No parameters passed to ANY()"

Side Effects: NONE.

Notes: NONE.

NONE
my @p = Bric::Biz::Person->list({ lname => NONE( 'wall', 'conway') });

Use this function when you want to perform a query comparing more than one value, and you want objects returned that don't match any of the values passed (c.f. "ANY"; NONE can be used anywhere that ANY can be)

Throws:

No parameters passed to NONE()"

Side Effects: NONE.

Notes: NONE.

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)

Returns an $sth from $dbh->prepare. Pass any attributes you want associated with your $sth via the $attr hashref. 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.

Throws:

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

Notes: NONE.

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

Returns an $sth from $dbh->prepare_cached. Pass any attributes you want associated with your $sth via the $attr hashref. A warning will also be issued if the $sth returned is already active.

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

Throws:

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

Notes: NONE.

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

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.

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

Throws:

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 (my $err = $@) {
    rollback();
    rethrow_exception($err);
}

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:

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:

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:

Side Effects: Calls $dbh->commit.

Notes: NONE.

fetch_objects( $pkg, $sql, $fields, $grp_col_cnt, $args )

This function takes a package name, a reference to an SQL statement, an arrayref of fields, a count of the number of columns containing lists of group IDs, a list of arguments. It uses the results from the SQL statement to construct objects of the specified package.

Throws:

Side Effects:

NONE

Notes:

NONE

build_query($cols, $tables, $where_clause, $order);

Builds a and returns a reference to a query.

Throws:

NONE

Side Effects:

NONE

Notes:

NONE

$params = clean_params($params)

Parameters for Asset objects should be run through this before sending them to the query building functions.

Throws: NONE.

Side Effects: NONE.

Notes: Bric::Util::Time must be loaded before this method is called.

tables

The from clause for the main select is built here.

Throws: NONE.

Side Effects: NONE.

Notes: NONE.

where_clause

The where clause for the main select is built here.

Throws: NONE.

Side Effects: NONE.

Notes: NONE.

any_where
my $where = any_where($value, $where_expression, \@params);

Examines $value to determine whether it is a single value or an ANY or a NONE value. If it is an ANY or a NONE value, then each of those values is pushed on to the end of the $params array reference and the where expression is grouped together in parentheses and ORed together the same number of times as there are values (and in the case of NONE, the word "NOT" comes before the parentheses). Otherwise, a single value is pushed onto the $params array reference and the where expression simply returned.

For example, if called like so:

my @params;
my $where = any_where(ANY(1, 2, 3), "f.name = ?", \@params);

Then @params will contain (1, 2, 3) and the string "(f.name = ? OR f.name = ? OR f.name = ?)" will be assigned to $where. If called with NONE instead of ANY, the string would be "NOT(f.name = ? OR f.name = ? OR f.name = ?)".

However, if the value is not an ANY or a NONE value:

my @params;
my $where = any_where(1, "f.name = ?", \@params);

Then @params will of course contain only (1) and the string "f.name = ?" will be assigned to $where.

This function is useful in classes that wish to add ANY/NONE support to specific list() method parameters.

my $order_by = order_by

Builds up the ORDER BY clause.

Throws:

Bad Order parameter.
OrderDirection parameter must either ASC or DESC.

Side Effects: NONE.

Notes: NONE.

my $group_by = group_by

Builds up the GROUP BY clause.

Throws: None.

Side Effects: NONE.

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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]})
}, undef);

# 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)

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:

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()

Returns a connection to the database using DBI->connect_cached(). Should be called at the start of every function that does database access.

Notes: NONE.

_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@justatheory.com>

See Also

DBI, Bric, Bric::Util::Time, Bric::Util::DBD::Oracle