Location: PHPKode > projects > Swat > Swat-1.4.108/Swat-1.4.108/SwatDB/SwatDB.php
<?php

/* vim: set noexpandtab tabstop=4 shiftwidth=4 foldmethod=marker: */

require_once 'MDB2.php';
require_once 'Swat/SwatObject.php';
require_once 'Swat/SwatDataTreeNode.php';
require_once 'Swat/SwatViewSelection.php';
require_once 'SwatI18N/SwatI18NLocale.php';
require_once 'SwatDB/SwatDBField.php';
require_once 'SwatDB/SwatDBTransaction.php';
require_once 'SwatDB/SwatDBDefaultRecordsetWrapper.php';
require_once 'SwatDB/exceptions/SwatDBException.php';

/**
 * Database helper class
 *
 * Static convenience methods for working with a database.
 *
 * @package   SwatDB
 * @copyright 2005-2008 silverorange
 * @license   http://www.gnu.org/copyleft/lesser.html LGPL License 2.1
 */
class SwatDB extends SwatObject
{
	// {{{ protected static properties

	protected static $debug = false;
	protected static $debug_info = array();
	protected static $debug_wrapper_depth = 0;

	// }}}
	// {{{ public static function setDebug()

	/**
	 * Sets the debug mode used by SwatDB
	 *
	 * @param boolean $debug optional. Whether or not to display SQL queries.
	 *                        Defaults to true.
	 */
	public static function setDebug($debug = true)
	{
		self::$debug = (boolean)$debug;
	}

	// }}}
	// {{{ public static function connect()

	/**
	 * Connects to a database
	 *
 	 * Convenience method to connect to a database and get a driver instance
	 * for that database.
	 *
	 * @param string $dsn the DSN to which to connect.
	 *
	 * @return MDB2_Driver_Common $db the database connection.
	 *
	 * @throws SwatDBException
	 */
	public static function connect($dsn)
	{
		$db = MDB2::connect($dsn);

		if (PEAR::isError($db))
			throw new SwatDBException($db);

		return $db;
	}

	// }}}
	// {{{ public static function query()

	/**
	 * Query a recordset
	 *
 	 * Convenience method to query.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 * @param string $sql The SQL to execute.
	 * @param array $wrapper Optional name of class to wrap the recordset with.
	 * @param array $types Optional array MDB2 datatypes for the recordset.
	 *
	 * @return MDB2_result_common A recordset containing the query result.
	 *
	 * @throws SwatDBException
	 */
	public static function query($db, $sql,
		$wrapper = 'SwatDBDefaultRecordsetWrapper', $types = null)
	{
		$mdb2_types = $types === null ? true : $types;
		$mdb2_wrapper = ($wrapper === null) ? false : $wrapper;

		$rs = self::executeQuery($db, 'query',
			array($sql, $mdb2_types, true, $mdb2_wrapper));

		return $rs;
	}

	// }}}
	// {{{ public static function exec()

	/**
	 * Execute a data manipulation SQL statement
	 *
 	 * Convenience method for MDB2::exec().
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 * @param string $sql The SQL to execute.
	 *
	 * @return integer Number of affected rows.
	 *
	 * @throws SwatDBException
	 */
	public static function exec($db, $sql)
	{
		return self::executeQuery($db, 'exec', array($sql));
	}

	// }}}
	// {{{ public static function updateColumn()

	/**
	 * Update a column
	 *
 	 * Convenience method to update a single database field for one or more
	 * rows. One convenient use of this method is for processing {@link
	 * SwatAction}s
	 * that change a single database field.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $field The name of the database field to update. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then integer is assummed for this
	 *        field.
	 *
	 * @param mixed $value The value to store in database field $field. The
	 *        type should correspond to the type of $field.
	 *
	 * @param string $id_field The name of the database field that contains the
	 *        the id. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param array $ids An array of identifiers corresponding to the database
	 *        rows to be updated. The type of the individual identifiers should
	 *        correspond to the type of $id_field.
	 *
	 * @param string $where An optional additional where clause.
	 *
	 * @return integer the number of rows updated.
	 *
	 * @throws SwatDBException
	 */
	public static function updateColumn($db, $table, $field, $value, $id_field,
		$ids, $where = null)
	{
		$ids = self::initArray($ids);

		if (count($ids) == 0)
			return;

		$field = new SwatDBField($field, 'integer');
		$id_field = new SwatDBField($id_field, 'integer');

		$sql = 'update %s set %s = %s where %s in (%s) %s';

		foreach ($ids as &$id)
			$id = $db->quote($id, $id_field->type);

		$id_list = implode(',', $ids);

		$where = ($where === null) ? '' : 'and '.$where;

		$sql = sprintf($sql,
			$table,
			$field->name,
			$db->quote($value, $field->type),
			$id_field->name,
			$id_list,
			$where);

		return self::exec($db, $sql);
	}

	// }}}
	// {{{ public static function queryColumn()

	/**
	 * Query a column
	 *
 	 * Convenience method to query for values in a single database column.
	 * One convenient use of this method is for loading values from a binding
	 * table.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $field The name of the database field to query. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then integer is assummed for this
	 *        field.
	 *
	 * @param string $id_field The name of the database field that contains the
	 *        the id.  If not null this will be used to construct a where clause
	 *        to limit results. Can be given in the form type:name where type is
	 *        a standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param mixed $id The value to look for in the $id_field. The type should
	 *        correspond to the type of $id_field.
	 *
	 * @return array An associative array of $id_field => $field
	 *
	 * @throws SwatDBException
	 */
	public static function queryColumn($db, $table, $field, $id_field = null,
		$id = 0)
	{
		$field = new SwatDBField($field, 'integer');

		if ($id_field == null) {
			$sql = 'select %s from %s';
			$sql = sprintf($sql, $field->name, $table);
		} else {
			$id_field = new SwatDBField($id_field, 'integer');
			$sql = 'select %s from %s where %s = %s';
			$sql = sprintf($sql,
				$field->name,
				$table,
				$id_field->name,
				$db->quote($id, $id_field->type));
		}

		$values = self::executeQuery($db, 'queryCol',
			array($sql, $field->type));

		return $values;
	}

	// }}}
	// {{{ public static function queryOne()

	/**
	 * Query a single value
	 *
	 * Convenience method to query a single value in a single database column.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 * @param string $sql The SQL to execute.
	 * @param string $type Optional MDB2 datatype for the result.
	 *
	 * @return mixed The value queried for a single result. Null when there are
	 *         no results.
	 *
	 * @throws SwatDBException
	 */
	public static function queryOne($db, $sql, $type = null)
	{
		$mdb2_type = $type === null ? true : $type;
		return self::executeQuery($db, 'queryOne', array($sql, $mdb2_type));
	}

	// }}}
	// {{{ public static function queryRow()

	/**
	 * Query a single row
	 *
 	 * Convenience method to query for a single row from a database table.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 * @param string $sql The SQL to execute.
	 * @param array $types Optional array of MDB2 datatypes for the result.
	 *
	 * @return Object A row object, or null.
	 *
	 * @throws SwatDBException
	 */
	public static function queryRow($db, $sql, $types = null)
	{
		$mdb2_types = $types === null ? true : $types;

		$row = self::executeQuery($db, 'queryRow',
			array($sql, $mdb2_types, MDB2_FETCHMODE_OBJECT));

		return $row;
	}

	// }}}
	// {{{ public static function queryOneFromTable()

	/**
	 * Query a single value from a specified table and column
	 *
 	 * Convenience method to query a single value in a single database column.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $field The name of the database field to query. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then integer is assummed for this
	 *        field.
	 *
	 * @param string $id_field The name of the database field that contains the
	 *        the id.  If not null this will be used to construct a where clause
	 *        to limit results. Can be given in the form type:name where type is
	 *        a standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param mixed $id The value to look for in the $id_field. The type should
	 *        correspond to the type of $id_field.
	 *
	 * @return mixed The value queried for a single result.
	 *
	 * @throws SwatDBException
	 */
	public static function queryOneFromTable($db, $table, $field, $id_field = null,
		$id = 0)
	{
		$field = new SwatDBField($field, 'integer');

		if ($id_field == null) {
			$sql = 'select %s from %s';
			$sql = sprintf($sql, $field->name, $table);
		} else {
			$id_field = new SwatDBField($id_field, 'integer');
			$sql = 'select %s from %s where %s = %s';
			$sql = sprintf($sql, $field->name, $table, $id_field->name,
				$db->quote($id, $id_field->type));
		}

		$value = self::queryOne($db, $sql, $field->type);

		return $value;
	}

	// }}}
	// {{{ public static function queryRowFromTable()

	/**
	 * Query a single row from a specified table and column
	 *
 	 * Convenience method to query for a single row from a database table.
	 * One convenient use of this method is for loading data on an edit page.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param array $fields An array of fields to be queried. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then text is assummed.
	 *
	 * @param string $id_field The name of the database field that contains the
	 *        the id. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param mixed $id The value to look for in the id field column. The
	 *        type should correspond to the type of $field.
	 *
	 * @return Object A row object.
	 *
	 * @throws SwatDBException
	 */
	public static function queryRowFromTable($db, $table, $fields, $id_field, $id)
	{
		self::initFields($fields);
		$id_field = new SwatDBField($id_field, 'integer');
		$sql = 'select %s from %s where %s = %s';
		$field_list = implode(',', self::getFieldNameArray($fields));

		$sql = sprintf($sql,
			$field_list,
			$table,
			$id_field->name,
			$db->quote($id, $id_field->type));

		$rs = self::query($db, $sql, null);
		$row = $rs->fetchRow(MDB2_FETCHMODE_OBJECT);

		if (MDB2::isError($row))
			throw new SwatDBException($row);

		return $row;
	}

	// }}}
	// {{{ public static function executeStoredProc()

	/**
	 * Execute a stored procedure
	 *
 	 * Convenience method to execute a stored procedure.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $proc The name of the stored procedure to execute.
	 *
	 * @param mixed $params The parameters to pass to the stored procedure.
	 *        Use an array for more than one parameter.
	 *
	 * @param mixed $wrapper Optional MDB2 wrapper class.
	 * @param array $types Optional array MDB2 datatypes for the recordset.
	 *
	 * @return mixed An MDB2 recordset or an instance of the wrapper class.
	 *
	 * @throws SwatDBException
	 */
	public static function executeStoredProc($db, $proc, $params,
		$wrapper = 'SwatDBDefaultRecordsetWrapper', $types = null)
	{
		$db->loadModule('Function');

		if (!is_array($params))
			$params = array($params);

		$mdb2_wrapper = ($wrapper === null) ? false : $wrapper;
		$mdb2_types = $types === null ? true : $types;

		$rs = $db->function->executeStoredProc(
			$proc, $params, $mdb2_types, true, $mdb2_wrapper);

		if (MDB2::isError($rs))
			throw new SwatDBException($rs);

		return $rs;
	}

	// }}}
	// {{{ public static function executeStoredProcOne()

	/**
	 * Execute a stored procedure that returns a single value
	 *
 	 * Convenience method to execute a stored procedure that returns a single
	 * value.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $proc The name of the stored procedure to execute.
	 *
	 * @param mixed $params The parameters to pass to the stored procedure.
	 *        Use an array for more than one parameter.
	 *
	 * @return mixed The value returned by the stored procedure.
	 *
	 * @throws SwatDBException
	 */
	public static function executeStoredProcOne($db, $proc, $params)
	{
		if (!is_array($params))
			$params = array($params);

		$rs = self::executeStoredProc($db, $proc, $params);
		$row = $rs->getFirst();
		return current($row);
	}

	// }}}
	// {{{ public static function updateBinding()

	/**
	 * Update a binding table
	 *
 	 * Convenience method to update rows in a binding table. It will delete
	 * and insert rows as necessary.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The binding table to update.
	 *
	 * @param string $id_field The name of the binding table field that contains
	 *        the fixed value.  Can be given in the form type:name where type is
	 *        a standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param mixed $id The value to store in the $id_field. The type should
	 *        correspond to the type of $id_field.
	 *
	 * @param string $value_field The name of the binding table field that
	 *        contains the values from the bound table.  Can be given in the
	 *        form type:name where type is a standard MDB2 datatype. If type is
	 *        ommitted, then integer is assummed for this field.
	 *
	 * @param array $values An array of values that should be stored in the
	 *        $value_field. The type of the individual values should
	 *        correspond to the type of $value_field.
	 *
	 * @param string $bound_table The table bound through the binding table.
	 *
	 * @param string $bound_field The database field in the bound table that the
	 *        binding table references.
	 *
	 * @throws SwatDBException
	 */
	public static function updateBinding($db, $table, $id_field, $id,
		$value_field, $values, $bound_table, $bound_field)
	{
		$id_field = new SwatDBField($id_field, 'integer');
		$value_field = new SwatDBField($value_field, 'integer');
		$bound_field = new SwatDBField($bound_field, 'integer');

		$values = self::initArray($values);

		$delete_sql = 'delete from %s where %s = %s';

		$delete_sql = sprintf($delete_sql,
			$table,
			$id_field->name,
			$db->quote($id, $id_field->type));

		if (count($values)) {

			foreach ($values as &$value)
				$value = $db->quote($value, $value_field->type);

			$value_list = implode(',', $values);

			$insert_sql = 'insert into %s (%s, %s) select %s, %s from %s '.
				'where %s not in (select %s from %s where %s = %s) and %s in (%s)';

			$insert_sql = sprintf($insert_sql,
				$table,
				$id_field->name,
				$value_field->name,
				$db->quote($id, $id_field->type),
				$bound_field->name,
				$bound_table,
				$bound_field->name,
				$value_field->name,
				$table,
				$id_field->name,
				$db->quote($id, $id_field->type),
				$bound_field->name,
				$value_list);

			$delete_sql .= sprintf(' and %s not in (%s)',
				$value_field->name,
				$value_list);
		}

		$transaction = new SwatDBTransaction($db);
		try {
			if (count($values)) {
				self::exec($db, $insert_sql);
			}

			self::exec($db, $delete_sql);

		} catch (Exception $e) {
			$transaction->rollback();
			throw $e;
		}
		$transaction->commit();
	}

	// }}}
	// {{{ public static function insertRow()

	/**
	 * Insert a row
	 *
 	 * Convenience method to insert a single database row. One convenient use
	 * of this method is for saving data on an edit page.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to update.
	 *
	 * @param array $fields An array of fields to be updated. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then text is assummed.
	 *
	 * @param array $values An associative array of values to store in the
	 *        database.  The array keys should correspond to field names.
	 *        The type of the individual values should correspond to the
	 *        field type.
	 *
	 * @param string $id_field The name of the database field that contains an
	 *        identifier of row to be updated. Can be given in the form
	 *        type:name where type is a standard MDB2 datatype. If type is
	 *        ommitted, then integer is assummed for this field.
	 *        If $id_field is set, the value in the $id_field column of
	 *        the inserted row is returned.
	 *
	 * @return mixed If $id_field is set, the value in the $id_field column of
	 *        the inserted row is returned.
	 *
	 * @throws SwatDBException
	 */
	public static function insertRow($db, $table, $fields, $values,
		$id_field = null)
	{
		self::initFields($fields);

		$ret = null;

		$transaction = new SwatDBTransaction($db);
		try {
			$sql = 'insert into %s (%s) values (%s)';
			$field_list = implode(',', self::getFieldNameArray($fields));

			$values_in_order = array();

			foreach ($fields as &$field) {
				$value = isset($values[$field->name]) ?
					$values[$field->name] : null;

				$values_in_order[] = $db->quote($value, $field->type);
			}

			$value_list = implode(',', $values_in_order);

			$sql = sprintf($sql,
				$table,
				$field_list,
				$value_list);

			$result = self::exec($db, $sql);

			if ($id_field !== null)
				$ret = self::getFieldMax($db, $table, $id_field);

		} catch (Exception $e) {
			$transaction->rollback();
			throw $e;
		}
		$transaction->commit();

		return $ret;
	}

	// }}}
	// {{{ public static function updateRow()

	/**
	 * Update a row
	 *
 	 * Convenience method to update multiple fields of a single database row.
	 * One convenient use of this method is for save data on an edit page.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to update.
	 *
	 * @param array $fields An array of fields to be updated. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then text is assummed.
	 *
	 * @param array $values An associative array of values to store in the
	 *        database.  The array keys should correspond to field names.
	 *        The type of the individual values should correspond to the
	 *        field type.
	 *
	 * @param string $id_field The name of the database field that contains an
	 *        identifier of row to be updated. Can be given in the form
	 *        type:name where type is a standard MDB2 datatype. If type is
	 *        ommitted, then integer is assummed for this field.
	 *
	 * @param mixed $id The value to look for in the $id_field column. The
	 *        type should correspond to the type of $field.
	 *
	 * @throws SwatDBException
	 */
	public static function updateRow($db, $table, $fields, $values, $id_field,
		$id)
	{
		self::initFields($fields);
		$id_field = new SwatDBField($id_field, 'integer');
		$sql = 'update %s set %s where %s = %s';
		$updates = array();

		foreach ($fields as &$field) {
			$value = isset($values[$field->name]) ?
				$values[$field->name] : null;

			$updates[] = sprintf('%s = %s',
				$field->name, $db->quote($value, $field->type));
		}

		$update_list = implode(',', $updates);

		$sql = sprintf($sql,
			$table,
			$update_list,
			$id_field->name,
			$db->quote($id, $id_field->type));

		self::exec($db, $sql);
	}

	// }}}
	// {{{ public static function deleteRow()

	/**
	 * Delete a row
	 *
 	 * Convenience method to delete a single database row.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to delete from.
	 *
	 * @param string $id_field The name of the database field that contains an
	 *        identifier of row to be deleted. Can be given in the form
	 *        type:name where type is a standard MDB2 datatype. If type is
	 *        ommitted, then integer is assummed for this field.
	 *
	 * @param mixed $id The value to look for in the $id_field column. The
	 *        type should correspond to the type of $field.
	 *
	 * @throws SwatDBException
	 */
	public static function deleteRow($db, $table, $id_field, $id)
	{
		$id_field = new SwatDBField($id_field, 'integer');
		$sql = 'delete from %s where %s = %s';

		$sql = sprintf($sql,
			$table,
			$id_field->name,
			$db->quote($id, $id_field->type));

		self::exec($db, $sql);
	}

	// }}}
	// {{{ public static function getOptionArray()

    /**
	 * Query for an option array
	 *
 	 * Convenience method to query for a set of options, each consisting of
	 * an id and a title. The returned option array in the form of
	 * $id => $title can be passed directly to other classes, such as
	 * {@link SwatFlydown} for example.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $title_field The name of the database field to query for
	 *        the title. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then text is
	 *        assummed for this field.
	 *
	 * @param string $id_field The name of the database field to query for
	 *        the id. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param string $order_by_clause Optional comma deliminated list of
	 *        database field names to use in the <i>order by</i> clause.
	 *        Do not include "order by" in the string; only include the list
	 *        of field names. Pass null to skip over this paramater.
	 *
	 * @param string $where_clause Optional <i>where</i> clause to limit the
	 *        returned results.  Do not include "where" in the string; only
	 *        include the conditionals.
	 *
	 * @return array An array in the form of $id => $title.
	 *
	 * @throws SwatDBException
	 */
	public static function getOptionArray($db, $table, $title_field, $id_field,
		$order_by_clause = null, $where_clause = null)
	{
		$title_field = new SwatDBField($title_field, 'text');
		$id_field = new SwatDBField($id_field, 'integer');

		$sql = 'select %s, %s from %s';
		$sql = sprintf($sql, $id_field->name, $title_field->name, $table);

		if ($where_clause != null)
			$sql .= ' where '.$where_clause;

		if ($order_by_clause != null)
			$sql .= ' order by '.$order_by_clause;

		$rs = self::query($db, $sql, null);

		$options = array();

		while ($row = $rs->fetchRow(MDB2_FETCHMODE_OBJECT)) {
			if (MDB2::isError($row))
				throw new SwatDBException($row);

			$title_field_name = $title_field->name;
			$id_field_name = $id_field->name;
			$options[$row->$id_field_name] = $row->$title_field_name;
		}

		return $options;
	}

	// }}}
	// {{{ public static function getCascadeOptionArray()

	/**
	 * Query for an option array cascaded by a field
	 *
 	 * Convenience method to query for a set of options, each consisting of
	 * an id, title, and a group-by field. The returned option array in the form of
	 * $cascade => array($id => $title, $id => $title) can be passed directly to
	 * other classes, such as {@link SwatCascade} for example.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $title_field The name of the database field to query for
	 *        the title. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then text is
	 *        assummed for this field.
	 *
	 * @param string $id_field The name of the database field to query for
	 *        the id. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param string $cascade_field The name of the database field to cascade
	 *        the options by. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param string $order_by_clause Optional comma deliminated list of
	 *        database field names to use in the <i>order by</i> clause.
	 *        Do not include "order by" in the string; only include the list
	 *        of field names. Pass null to skip over this paramater.
	 *
	 * @param string $where_clause Optional <i>where</i> clause to limit the
	 *        returned results.  Do not include "where" in the string; only
	 *        include the conditionals.
	 *
	 * @return array An array in the form of $id => $title.
	 *
	 * @throws SwatDBException
	 */
	public static function getCascadeOptionArray($db, $table, $title_field,
		$id_field, $cascade_field, $order_by_clause = null,
		$where_clause = null)
	{
		$title_field = new SwatDBField($title_field, 'text');
		$id_field = new SwatDBField($id_field, 'integer');
		$cascade_field = new SwatDBField($cascade_field, 'integer');

		$sql = 'select %s, %s, %s from %s';
		$sql = sprintf($sql, $id_field->name, $title_field->name,
			$cascade_field->name, $table);

		if ($where_clause !== null)
			$sql .= ' where '.$where_clause;

		$sql .= ' order by '.$cascade_field->name;
		if ($order_by_clause !== null)
			$sql.= ', '.$order_by_clause;

		$rs = self::query($db, $sql, null);

		$options = array();
		$current = null;
		$title_field_name = $title_field->name;
		$id_field_name = $id_field->name;
		$cascade_field_name = $cascade_field->name;

		while ($row = $rs->fetchRow(MDB2_FETCHMODE_OBJECT)) {
			if (MDB2::isError($row))
				throw new SwatDBException($row);

			if ($row->$cascade_field_name != $current) {
				$current = $row->$cascade_field_name;
				$options[$current] = array();
			}

			$options[$current][$row->$id_field_name] = $row->$title_field_name;
		}

		return $options;
	}

	// }}}
	// {{{ public static function getGroupedOptionArray()

	/**
	 * Queries for a grouped option array
	 *
 	 * Convenience method to query a grouped list of {@link SwatDataTreeNode}
	 * objects used for things like {@link SwatCheckboxList} where checkboxes
	 * are grouped together under a title.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to query.
	 *
	 * @param string $title_field The name of the database field to query for
	 *        the title. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then text is
	 *        assummed for this field.
	 *
	 * @param string $id_field The name of the database field to query for
	 *        the id. Can be given in the form type:name where type is a
	 *        standard MDB2 datatype. If type is ommitted, then integer is
	 *        assummed for this field.
	 *
	 * @param string $group_table The database table that the group titles come
	 *        from.
	 *
	 * @param string $group_idfield The name of the database field to query for
	 *        the id of the $group_table. Can be given in the form type:name
	 *        where type is a standard MDB2 datatype. If type is ommitted, then
	 *        integer is assummed for this field.
	 *
	 * @param string $group_title_field The name of the database field to query
	 *        for the group title. Can be given in the form type:name where
	 *        type is a standard MDB2 datatype. If type is ommitted, then text
	 *        is assummed for this field.
	 *
	 * @param string $group_field The name of the database field in $table that
	 *        links with the $group_idfield. Can be given in the form type:name
	 *        where type is a standard MDB2 datatype. If type is ommitted, then
	 *        integer is assummed for this field.
	 *
	 * @param string $order_by_clause Optional comma deliminated list of
	 *        database field names to use in the <i>order by</i> clause.
	 *        Do not include "order by" in the string; only include the list
	 *        of field names. Pass null to skip over this paramater.
	 *
	 * @param string $where_clause Optional <i>where</i> clause to limit the
	 *        returned results.  Do not include "where" in the string; only
	 *        include the conditionals.
	 * @param SwatDataTreeNode $tree a tree to add nodes to. If no tree is
	 *                                specified, nodes are added to a new
	 *                                empty tree.
	 *
	 * @return SwatDataTreeNode a tree composed of {@link SwatDataTreeNode}
	 *                           objects.
	 *
	 * @throws SwatDBException
	 */
	public static function getGroupedOptionArray($db, $table, $title_field,
		$id_field, $group_table, $group_title_field, $group_id_field,
		$group_field, $order_by_clause = null, $where_clause = null,
		$tree = null)
	{
		$title_field = new SwatDBField($title_field, 'text');
		$id_field = new SwatDBField($id_field, 'integer');
		$group_title_field = new SwatDBField($group_title_field, 'text');
		$group_id_field = new SwatDBField($group_id_field, 'integer');
		$group_field = new SwatDBField($group_field, 'text');

		$sql = 'select %s as id, %s as title, %s as group_title, %s as group_id
			from %s';

		$sql = sprintf($sql,
			"{$table}.{$id_field->name}",
			"{$table}.{$title_field->name}",
			"{$group_table}.{$group_title_field->name}",
			"{$group_table}.{$group_id_field->name}",
			$table);

		$sql.= ' inner join %s on %s = %s';
		$sql = sprintf($sql,
			$group_table,
			"{$group_table}.{$group_id_field->name}",
			"{$table}.{$group_field->name}");

		if ($where_clause != null)
			$sql.= ' where '.$where_clause;

		if ($order_by_clause != null)
			$sql.= ' order by '.$order_by_clause;

		$rs = self::query($db, $sql, null);

		$options = array();

		if ($tree !== null && $tree instanceof SwatDataTreeNode)
			$base_parent = $tree;
		else
			$base_parent = new SwatDataTreeNode(null, Swat::_('Root'));

		$current_group = null;

		while ($row = $rs->fetchRow(MDB2_FETCHMODE_OBJECT)) {
			if ($current_group !== $row->group_id) {
				$current_parent = new SwatDataTreeNode(null, $row->group_title);
				$base_parent->addChild($current_parent);
				$current_group = $row->group_id;
			}

			$current_parent->addChild(
				new SwatDataTreeNode($row->id, $row->title));
		}

		return $base_parent;
	}

	// }}}
	// {{{ public static function getFieldMax()

	/**
	 * Get max field value
	 *
 	 * Convenience method to grab the max value from a single field.
	 *
	 * @param MDB2_Driver_Common $db The database connection.
	 *
	 * @param string $table The database table to update.
	 *
	 * @param string $field The field to be return the max value of. Can be
	 *        given in the form type:name where type is a standard MDB2
	 *        datatype. If type is ommitted, then text is assummed.
	 *
	 * @return mixed The max value of field specified.
	 *
	 * @throws SwatDBException
	 */
	public static function getFieldMax($db, $table, $field)
	{
		$field = new SwatDBField($field, 'integer');

		$sql = sprintf('select max(%s) as %s from %s',
			$field->name, $field->name, $table);

		return self::queryOne($db, $sql);
	}

	// }}}
	// {{{ public static function equalityOperator()

	/**
	 * Get proper conditional operator
	 *
 	 * Convenience method to return proper operators for database values that
 	 * may be null.
	 *
	 * @param mixed $value The value to check for null on
	 *
	 * @param boolean $neg Whether to return the operator for a negative
	 *        comparison
	 *
	 * @return string SQL operator
	 */
	public static function equalityOperator($value, $neg = false)
	{
		if ($value === null && $neg)
			return 'is not';
		elseif ($value === null)
			return 'is';
		elseif ($neg)
			return '!=';
		else
			return '=';
	}

	// }}}
	// {{{ public static function getDataTree()

	/**
	 * Get a tree of data nodes
	 *
 	 * Convenience method to take a structured query with each row consisting of
	 * an id, levelnum, and a title, and turning it into a tree of
	 * {@link SwatDataTreeNode} objects. The returned option array in the form
	 * of a collection of {@link SwatDataTreeNode} objects can be used by other
	 * classes, such as {@link SwatTreeFlydown} and
	 * {@link @SwatChecklistTree}.
	 *
	 * @param MDB2_Driver_Common $rs The MDB2 result set, usually the
	 * 	result of a stored procedure. Must be wrapped in {@link
	 * 	SwatDBRecordsetWrapper}.
	 *
	 * @param string $title_field_name The name of the database field
	 * 	representing the title
	 *
	 * @param string $idfield_field_name The name of the database field
	 * 	representing the id
	 *
	 * @param string $level_field_name the name of the database field
	 *                                  representing the tree level.
	 * @param SwatDataTreeNode $tree an optional tree to add nodes to. If no
	 *                                   tree is specified, nodes are added to
	 *                                   a new empty tree.
	 *
	 * @return SwatDataTreeNode a tree composed of
	 *                              {@link SwatDataTreeNode} objects.
	 *
	 * @throws SwatDBException
	 */
	public static function getDataTree($rs, $title_field_name,
		$id_field_name, $level_field_name, $tree = null)
	{
		$stack = array();
		if ($tree !== null && $tree instanceof SwatDataTreeNode)
			$current_parent = $tree;
		else
			$current_parent = new SwatDataTreeNode('', Swat::_('Root'));

		$base_parent = $current_parent;
		array_push($stack, $current_parent);
		$last_node = $current_parent;

		foreach ($rs as $row) {
			$title = $row->$title_field_name;
			$id = $row->$id_field_name;
			$level = $row->$level_field_name;

			if ($level > count($stack)) {
				array_push($stack, $current_parent);
				$current_parent = $last_node;
			} elseif ($level < count($stack)) {
				$current_parent = array_pop($stack);
			}

			$last_node = new SwatDataTreeNode($id, $title);
			$current_parent->addChild($last_node);
		}

		return $base_parent;
	}

	// }}}
	// {{{ public static function implodeSelection()

	/**
	 * Implodes a view selection object
	 *
	 * Each item in the view is quoted using the specified type.
	 *
	 * @param MDB2_Driver_Common $db the database connection to use to implode
	 *                                the view.
	 * @param SwatViewSelection $selection the selection to implode.
	 * @param string $type optional. The datatype to use. Must be a valid MDB2
	 *                      datatype. If unspecified, 'integer' is used.
	 *
	 * @return string the imploded view ready for inclusion in an SQL statement.
	 */
	public static function implodeSelection(MDB2_Driver_Common $db,
		SwatViewSelection $selection, $type = 'integer')
	{
		$quoted_ids = array();
		foreach ($selection as $id)
			$quoted_ids[] = $db->quote($id, $type);

		return implode(',', $quoted_ids);
	}

	// }}}
	// {{{ private static function executeQuery()

	private static function executeQuery($db, $method, array $args)
	{
		self::debugStart(current($args)); // sql is always the first arg
		$ret = call_user_func_array(array($db, $method), $args);
		self::debugEnd();

		if (MDB2::isError($ret)) {
			throw new SwatDBException($ret);
		}

		return $ret;
	}

	// }}}
	// {{{ private static function getFieldNameArray()

	private static function getFieldNameArray($fields)
	{
		if (count($fields) == 0)
			return;

		$names = array();

		foreach ($fields as &$field)
			$names[] = $field->name;

		return $names;
	}

	// }}}
	// {{{ private static function getFieldTypeArray()

	private static function getFieldTypeArray($fields)
	{
		if (count($fields) == 0)
			return;

		$types = array();

		foreach ($fields as &$field)
			$types[] = $field->type;

		return $types;
	}

	// }}}
	// {{{ private static function initFields()

	/**
	 * Transforms an array of text field identifiers ('type:name') into
	 * an array of SwatDBField objects.
	 *
	 * The array is passed by reference and modified in-place. Nothing is
	 * returned by this method.
	 *
	 * @param array $fields a reference to the array of field identifiers to
	 *                       transform.
	 */
	private function initFields(&$fields)
	{
		if (count($fields) == 0)
			// TODO: throw exception instead of returning
			return;

		foreach ($fields as &$field)
			$field = new SwatDBField($field, 'text');
	}

	// }}}
	// {{{ private static function initArray()

	/**
	 * Noramlizes Iterator objects into simple arrays
	 *
	 * Checks a variable to see if it is an array or if it is an Iterator. If
	 * the variable is an Iterator, converts it to an array of values.
	 *
	 * @param array|Iterator $array the variable to normalize.
	 *
	 * @return array the normalized array
	 *
	 * @throws SwatDBException if the <i>$array</i> parameter is not an array
	 *                         or an Iterator.
	 */
	private function initArray($array)
	{
		if (is_array($array)) {
			return $array;
		} elseif ($array instanceof Iterator) {
			$return = array();
			foreach ($array as $value)
				$return[] = $value;

			return $return;
		}

		throw new SwatDBException('Value is not an array');
	}

	// }}}
	// {{{ private static function debugStart()

	private static function debugStart($message)
	{
		// SWATDB_DEBUG is legacy, use SwatDB::setDebug() instead
		if (defined('SWATDB_DEBUG') || self::$debug) {
			$trace = debug_backtrace();

			// get first trace line that is not in the SwatDB package
			foreach ($trace as $entry) {
				if (!array_key_exists('class', $entry) ||
					strncmp($entry['class'], 'SwatDB', 6) !== 0) {
					break;
				}
			}

			$class = (array_key_exists('class', $entry)) ?
				$entry['class'] : null;

			$function = (array_key_exists('function', $entry)) ?
				$entry['function'] : null;

			ob_start();
			printf("<strong>%s%s%s()</strong><br />\n",
				($class === null) ? '' : $class,
				array_key_exists('type', $entry) ? $entry['type'] : '',
				($function === null) ? '' : $function);

			echo $message;
			$debug_message = ob_get_clean();

			$debug_info = array();
			$debug_info['message'] = $debug_message;
			$debug_info['time'] = microtime(true) * 1000;
			$debug_info['depth'] = self::$debug_wrapper_depth;
			self::$debug_info[] = $debug_info;

			self::$debug_wrapper_depth++;
		}
	}

	// }}}
	// {{{ private static function debugEnd()

	private static function debugEnd()
	{
		// SWATDB_DEBUG is legacy, use SwatDB::setDebug() instead
		if (defined('SWATDB_DEBUG') || self::$debug) {
			self::$debug_wrapper_depth--;

			if (self::$debug_wrapper_depth == 0) {
				$count = 0;
				$depth = 0;

				foreach (self::$debug_info as $info) {
					if ($info['depth'] < $depth) {
						echo str_repeat('</blockquote>',
							$depth - $info['depth']);
					} elseif ($info['depth'] > $depth) {
						echo '<blockquote class="swat-db-debug">';
					} elseif ($info['depth'] == 0) {
						echo '<hr />';
					}

					echo "\n".$info['message']."\n";

					if (count(self::$debug_info) > $count + 1) {
						$time = self::$debug_info[$count + 1]['time'];
					} else {
						$time = (microtime(true) * 1000);
					}

					$ms = $time - $info['time'];

					$locale = SwatI18NLocale::get();
					printf("<p><strong>%s ms</strong></p>\n",
						$locale->formatNumber($ms, 3));

					if ($info['depth'] == 0 &&  count(self::$debug_info) > 1) {
						$ms = (microtime(true) * 1000) -
							self::$debug_info[0]['time'];

						echo '<p><strong>';

						printf(Swat::_('Total time: %s ms (includes '.
							'queries within the wrapper)'),
							$locale->formatNumber($ms, 3));

						echo '</strong></p>';
					}

					$count++;
					$depth = $info['depth'];
				}

				if ($depth > 0) {
					echo str_repeat('</blockquote>', $depth);
				}

				// reset debug info for the next query
				self::$debug_info = array();
			}
		}
	}

	// }}}
}

?>
Return current item: Swat