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

$LastChangedRevision$

$LastChangedDate: 2005-08-23 16:32:50 -0700 (Tue, 23 Aug 2005) $

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

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 CWuse DBI. The advantage to this approach (other than some level of database independence) is that the CW$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 CWused 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.

There are several ways to CWuse 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

*

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 database. 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 CW$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.

NONE.

NONE.

NONE.

NONE.

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

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

*

Use prepare_c() whenever possible, as it will cache the CW$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 CW$select->fetchrow_array() method, and especially the CW$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 CW$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 CW$dbh->prepare(). Notes: NONE. Returns an CW$sth from CW$dbh->prepare_cached. Pass any attributes you want associated with your CW$sth via the CW$attr hashref. A warning will also be issued if the CW$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 CW$dbh->prepare_cached(). Notes: NONE. Returns an CW$sth from CW$dbh->prepare_cached, and will not issue a warning if the CW$sth returned is already active. Pass any attributes you want associated with your CW$sth via the CW$ATTR hashref. See also the important note in the prepare() documentation above. Throws:

*

Unable to connect to database.

*

Unable to prepare SQL statement. Side Effects: Calls CW$dbh->prepare_cached() with the active flag set to true. Notes: NONE.

  begin();
  eval {
      execute($ins1);
      execute($ins2);
      execute($upd);
      commit();
  };
  if (my $err = $@) {
      rollback();
      rethrow_exception($err);
  }

Sets CW$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 CW$dbh->{AutoCommit} = 0. Notes: NONE. 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 CW$dbh->commit. Notes: NONE. 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 CW$dbh->commit. Notes: NONE. 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:

*

Unable to connect to database.

*

Unable to prepare SQL statement.

*

Unable to execute SQL statement. Side Effects: NONE Notes: NONE 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. Builds up the ORDER BY clause. Throws:

OrderDirection parameter must either ASC or DESC.

Side Effects: NONE. Notes: NONE. Builds up the GROUP BY clause. Throws: None. Side Effects: NONE. Notes: NONE. Executes the prepared statement. Use this instead of CW$sth->execute(@params) and it will take care of exception handling for you. Returns the value returned by CW$sth->execute(). Throws:

*

Unable to execute SQL statement. Side Effects: Calls CW$sth->execute. Notes: NONE. Binds variables to the columns in the statement handle. Functions exactly the same as CW$sth->bind_columns, only it handles the exception handling for you. Returns the value returned by CW$sth->bind_columns. Throws:

*

Unable to bind to columns to statement handle. Side Effects: Calls CW$sth->bind_columns(). Notes: NONE. Binds a variable to a columns in the statement handle. Functions exactly the same as CW$sth->bind_col, only it handles the exception handling for you. Returns the value returned by CW$sth->bind_col. Throws:

*

Unable to bind to column to statement handle. Side Effects: Calls CW$sth->bind_columns(). Notes: NONE. Binds parameter to the columns in the statement handle. Functions exactly the same as CW$sth->bind_param, only it handles the exception handling for you. Returns the value returned by CW$sth->bind_param. Throws:

*

Unable to bind parameters to columns in statement handle. Side Effects: Calls CW$sth->bind_columns(). Notes: NONE. Performs CW$sth->fetch() and returns the result. Functions exactly the same as CW$sth->fetch, only it handles the exception handling for you. Throws:

*

Unable to fetch row from statement handle. Side Effects: Calls CW$sth->bind_columns(). Notes: NONE. Performs CW$sth->finish() and returns the result. Functions exactly the same as CW$sth->finish, only it handles the exception handling for you. Throws:

*

Unable to finish statement handle. Side Effects: Calls CW$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. Executes the SELECT statement in CW$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 CW$select, pass the parameters that map to them. This function will prepare() the query in CW$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 CW$sth. See the Synopsis above for an example. Throws:

*

Unable to connect to database.

*

Unable to select row. Side Effects: Calls CW$dbh->selectrow_arrayref(). Notes: NONE. Executes the SELECT statement in CW$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 CW$select, pass the parameters that map to them. This function will prepare() the query in CW$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 CW$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 CW$dbh->selectrow_array(). Notes: NONE. Executes CW$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 CW$select, pass the parameters that map to them. This function will prepare() the query in CW$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 CW$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 CW$dbh->selectall_arrayref(). Notes: NONE. Executes the SELECT statement in CW$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 CW$select, pass the parameters that map to them. This function will prepare() the query in CW$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 CW$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 CW$dbh->selectcol_arrayref(). Notes: NONE. Returns an SQL string for inserting the next available key into CW$db_name.$table_name within the context of a larger INSERT statement. If CW$db_name is not passed, it defaults to the value stored in CW$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)

Returns the last sequence number inserted into CW$db_name.$table_name by the current process. If CW$db_name is not passed, it defaults to the value stored in CW$Bric::Cust. Will return undef if this process has not yet inserted anything into CW$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.

NONE.

NONE.

NONE.

_connect()

Returns a connection to the database using CWDBI->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. 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.

NONE.

David E. Wheeler <david@wheeler.net>

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