/extlib/DB/Table.php
PHP | 2649 lines | 1088 code | 350 blank | 1211 comment | 182 complexity | c902540e8ec6c8fb2e6602558a478f96 MD5 | raw file
Large files files are truncated, but you can click here to view the full file
- <?php
- /**
- *
- * DB_Table is a database API and data type SQL abstraction class.
- *
- * DB_Table provides database API abstraction, data type abstraction,
- * automated SELECT, INSERT, and UPDATE queries, automated table
- * creation, automated validation of inserted/updated column values,
- * and automated creation of QuickForm elements based on the column
- * definitions.
- *
- * @category DB
- *
- * @package DB_Table
- *
- * @author Paul M. Jones <pmjones@php.net>
- * @author Mark Wiesemann <wiesemann@php.net>
- *
- * @license http://www.gnu.org/copyleft/lesser.html LGPL
- *
- * @version $Id: Table.php,v 1.69 2006/11/09 20:56:30 wiesemann Exp $
- *
- */
- /**
- * Error code at instantiation time when the first parameter to the
- * constructor is not a PEAR DB object.
- */
- define('DB_TABLE_ERR_NOT_DB_OBJECT', -1);
- /**
- * Error code at instantiation time when the PEAR DB/MDB2 $phptype is not
- * supported by DB_Table.
- */
- define('DB_TABLE_ERR_PHPTYPE', -2);
- /**
- * Error code when you call select() or selectResult() and the first
- * parameter does not match any of the $this->sql keys.
- */
- define('DB_TABLE_ERR_SQL_UNDEF', -3);
- /**
- * Error code when you try to insert data to a column that is not in the
- * $this->col array.
- */
- define('DB_TABLE_ERR_INS_COL_NOMAP', -4);
- /**
- * Error code when you try to insert data, and that data does not have a
- * column marked as 'require' in the $this->col array.
- */
- define('DB_TABLE_ERR_INS_COL_REQUIRED', -5);
- /**
- * Error code when auto-validation fails on data to be inserted.
- */
- define('DB_TABLE_ERR_INS_DATA_INVALID', -6);
- /**
- * Error code when you try to update data to a column that is not in the
- * $this->col array.
- */
- define('DB_TABLE_ERR_UPD_COL_NOMAP', -7);
- /**
- * Error code when you try to update data, and that data does not have a
- * column marked as 'require' in the $this->col array.
- */
- define('DB_TABLE_ERR_UPD_COL_REQUIRED', -8);
- /**
- * Error code when auto-validation fails on update data.
- */
- define('DB_TABLE_ERR_UPD_DATA_INVALID', -9);
- /**
- * Error code when you use a create() flag that is not recognized (must
- * be 'safe', 'drop', 'verify' or boolean false.
- */
- define('DB_TABLE_ERR_CREATE_FLAG', -10);
- /**
- * Error code at create() time when you define an index in $this->idx
- * that has no columns.
- */
- define('DB_TABLE_ERR_IDX_NO_COLS', -11);
- /**
- * Error code at create() time when you define an index in $this->idx
- * that refers to a column that does not exist in the $this->col array.
- */
- define('DB_TABLE_ERR_IDX_COL_UNDEF', -12);
- /**
- * Error code at create() time when you define a $this->idx index type
- * that is not recognized (must be 'normal' or 'unique').
- */
- define('DB_TABLE_ERR_IDX_TYPE', -13);
- /**
- * Error code at create() time when you have an error in a 'char' or
- * 'varchar' definition in $this->col (usually because 'size' is wrong).
- */
- define('DB_TABLE_ERR_DECLARE_STRING', -14);
- /**
- * Error code at create() time when you have an error in a 'decimal'
- * definition (usually becuase the 'size' or 'scope' are wrong).
- */
- define('DB_TABLE_ERR_DECLARE_DECIMAL', -15);
- /**
- * Error code at create() time when you define a column in $this->col
- * with an unrecognized 'type'.
- */
- define('DB_TABLE_ERR_DECLARE_TYPE', -16);
- /**
- * Error code at validation time when a column in $this->col has an
- * unrecognized 'type'.
- */
- define('DB_TABLE_ERR_VALIDATE_TYPE', -17);
- /**
- * Error code at create() time when you define a column in $this->col
- * with an invalid column name (usually because it's a reserved keyword).
- */
- define('DB_TABLE_ERR_DECLARE_COLNAME', -18);
- /**
- * Error code at create() time when you define an index in $this->idx
- * with an invalid index name (usually because it's a reserved keyword).
- */
- define('DB_TABLE_ERR_DECLARE_IDXNAME', -19);
- /**
- * Error code at create() time when you define an index in $this->idx
- * that refers to a CLOB column.
- */
- define('DB_TABLE_ERR_IDX_COL_CLOB', -20);
- /**
- * Error code at create() time when you define a column name that is
- * more than 30 chars long (an Oracle restriction).
- */
- define('DB_TABLE_ERR_DECLARE_STRLEN', -21);
- /**
- * Error code at create() time when the index name ends up being more
- * than 30 chars long (an Oracle restriction).
- */
- define('DB_TABLE_ERR_IDX_STRLEN', -22);
- /**
- * Error code at create() time when the table name is more than 30 chars
- * long (an Oracle restriction).
- */
- define('DB_TABLE_ERR_TABLE_STRLEN', -23);
- /**
- * Error code at nextID() time when the sequence name is more than 30
- * chars long (an Oracle restriction).
- */
- define('DB_TABLE_ERR_SEQ_STRLEN', -24);
- /**
- * Error code at verify() time when the table does not exist in the
- * database.
- */
- define('DB_TABLE_ERR_VER_TABLE_MISSING', -25);
- /**
- * Error code at verify() time when the column does not exist in the
- * database table.
- */
- define('DB_TABLE_ERR_VER_COLUMN_MISSING', -26);
- /**
- * Error code at verify() time when the column type does not match the
- * type specified in the column declaration.
- */
- define('DB_TABLE_ERR_VER_COLUMN_TYPE', -27);
- /**
- * Error code at instantiation time when the column definition array
- * does not contain at least one column.
- */
- define('DB_TABLE_ERR_NO_COLS', -28);
- /**
- * Error code at verify() time when an index cannot be found in the
- * database table.
- */
- define('DB_TABLE_ERR_VER_IDX_MISSING', -29);
- /**
- * Error code at verify() time when an index does not contain all
- * columns that it should contain.
- */
- define('DB_TABLE_ERR_VER_IDX_COL_MISSING', -30);
- /**
- * Error code at instantiation time when a creation mode
- * is not available for a phptype.
- */
- define('DB_TABLE_ERR_CREATE_PHPTYPE', -31);
- /**
- * Error code at create() time when you define more than one primary key
- * in $this->idx.
- */
- define('DB_TABLE_ERR_DECLARE_PRIMARY', -32);
- /**
- * Error code at create() time when a primary key is defined in $this->idx
- * and SQLite is used (SQLite does not support primary keys).
- */
- define('DB_TABLE_ERR_DECLARE_PRIM_SQLITE', -33);
- /**
- * Error code at alter() time when altering a table field is not possible
- * (e.g. because MDB2 has no support for the change or because the DBMS
- * does not support the change).
- */
- define('DB_TABLE_ERR_ALTER_TABLE_IMPOS', -34);
- /**
- * Error code at alter() time when altering a(n) index/constraint is not possible
- * (e.g. because MDB2 has no support for the change or because the DBMS
- * does not support the change).
- */
- define('DB_TABLE_ERR_ALTER_INDEX_IMPOS', -35);
- /**
- * The PEAR class for errors
- */
- require_once 'PEAR.php';
- /**
- * The Date class for recasting date and time values
- */
- require_once 'DB/Table/Date.php';
- /**
- * DB_Table supports these RDBMS engines and their various native data
- * types; we need these here instead of in Manager.php because the
- * initial array key tells us what databases are supported.
- */
- $GLOBALS['_DB_TABLE']['type'] = array(
- 'fbsql' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'LONGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'REAL',
- 'double' => 'DOUBLE PRECISION',
- 'clob' => 'CLOB',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'ibase' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'FLOAT',
- 'double' => 'DOUBLE PRECISION',
- 'clob' => 'BLOB SUB_TYPE 1',
- 'date' => 'DATE',
- 'time' => 'TIME',
- 'timestamp' => 'TIMESTAMP'
- ),
- 'mssql' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'REAL',
- 'double' => 'FLOAT',
- 'clob' => 'TEXT',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'mysql' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'FLOAT',
- 'double' => 'DOUBLE',
- 'clob' => 'LONGTEXT',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'mysqli' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'FLOAT',
- 'double' => 'DOUBLE',
- 'clob' => 'LONGTEXT',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'oci8' => array(
- 'boolean' => 'NUMBER(1)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR2',
- 'smallint' => 'NUMBER(6)',
- 'integer' => 'NUMBER(11)',
- 'bigint' => 'NUMBER(19)',
- 'decimal' => 'NUMBER',
- 'single' => 'REAL',
- 'double' => 'DOUBLE PRECISION',
- 'clob' => 'CLOB',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'pgsql' => array(
- 'boolean' => 'DECIMAL(1,0)',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'DECIMAL',
- 'single' => 'REAL',
- 'double' => 'DOUBLE PRECISION',
- 'clob' => 'TEXT',
- 'date' => 'CHAR(10)',
- 'time' => 'CHAR(8)',
- 'timestamp' => 'CHAR(19)'
- ),
- 'sqlite' => array(
- 'boolean' => 'BOOLEAN',
- 'char' => 'CHAR',
- 'varchar' => 'VARCHAR',
- 'smallint' => 'SMALLINT',
- 'integer' => 'INTEGER',
- 'bigint' => 'BIGINT',
- 'decimal' => 'NUMERIC',
- 'single' => 'FLOAT',
- 'double' => 'DOUBLE',
- 'clob' => 'CLOB',
- 'date' => 'DATE',
- 'time' => 'TIME',
- 'timestamp' => 'TIMESTAMP'
- )
- );
- /**
- * US-English default error messages. If you want to internationalize, you can
- * set the translated messages via $GLOBALS['_DB_TABLE']['error']. You can also
- * use DB_Table::setErrorMessage(). Examples:
- *
- * <code>
- * (1) $GLOBALS['_DB_TABLE]['error'] = array(DB_TABLE_ERR_PHPTYPE => '...',
- * DB_TABLE_ERR_SQL_UNDEF => '...');
- * (2) DB_Table::setErrorMessage(DB_TABLE_ERR_PHPTYPE, '...');
- * DB_Table::setErrorMessage(DB_TABLE_ERR_SQL_UNDEF, '...');
- * (3) DB_Table::setErrorMessage(array(DB_TABLE_ERR_PHPTYPE => '...');
- * DB_TABLE_ERR_SQL_UNDEF => '...');
- * (4) $obj =& new DB_Table();
- * $obj->setErrorMessage(DB_TABLE_ERR_PHPTYPE, '...');
- * $obj->setErrorMessage(DB_TABLE_ERR_SQL_UNDEF, '...');
- * (5) $obj =& new DB_Table();
- * $obj->setErrorMessage(array(DB_TABLE_ERR_PHPTYPE => '...');
- * DB_TABLE_ERR_SQL_UNDEF => '...');
- * </code>
- *
- * For errors that can occur with-in the constructor call (i.e. e.g. creating
- * or altering the database table), only the code from examples (1) to (3)
- * will alter the default error messages early enough. For errors that can
- * occur later, examples (4) and (5) are also valid.
- */
- $GLOBALS['_DB_TABLE']['default_error'] = array(
- DB_TABLE_ERR_NOT_DB_OBJECT => 'First parameter must be a DB/MDB2 object',
- DB_TABLE_ERR_PHPTYPE => 'DB/MDB2 phptype (or dbsyntax) not supported',
- DB_TABLE_ERR_SQL_UNDEF => 'Select key not in map',
- DB_TABLE_ERR_INS_COL_NOMAP => 'Insert column not in map',
- DB_TABLE_ERR_INS_COL_REQUIRED => 'Insert data must be set and non-null for column',
- DB_TABLE_ERR_INS_DATA_INVALID => 'Insert data not valid for column',
- DB_TABLE_ERR_UPD_COL_NOMAP => 'Update column not in map',
- DB_TABLE_ERR_UPD_COL_REQUIRED => 'Update column must be set and non-null',
- DB_TABLE_ERR_UPD_DATA_INVALID => 'Update data not valid for column',
- DB_TABLE_ERR_CREATE_FLAG => 'Create flag not valid',
- DB_TABLE_ERR_IDX_NO_COLS => 'No columns for index',
- DB_TABLE_ERR_IDX_COL_UNDEF => 'Column not in map for index',
- DB_TABLE_ERR_IDX_TYPE => 'Type not valid for index',
- DB_TABLE_ERR_DECLARE_STRING => 'String column declaration not valid',
- DB_TABLE_ERR_DECLARE_DECIMAL => 'Decimal column declaration not valid',
- DB_TABLE_ERR_DECLARE_TYPE => 'Column type not valid',
- DB_TABLE_ERR_VALIDATE_TYPE => 'Cannot validate for unknown type on column',
- DB_TABLE_ERR_DECLARE_COLNAME => 'Column name not valid',
- DB_TABLE_ERR_DECLARE_IDXNAME => 'Index name not valid',
- DB_TABLE_ERR_DECLARE_TYPE => 'Column type not valid',
- DB_TABLE_ERR_IDX_COL_CLOB => 'CLOB column not allowed for index',
- DB_TABLE_ERR_DECLARE_STRLEN => 'Column name too long, 30 char max',
- DB_TABLE_ERR_IDX_STRLEN => 'Index name too long, 30 char max',
- DB_TABLE_ERR_TABLE_STRLEN => 'Table name too long, 30 char max',
- DB_TABLE_ERR_SEQ_STRLEN => 'Sequence name too long, 30 char max',
- DB_TABLE_ERR_VER_TABLE_MISSING => 'Verification failed: table does not exist',
- DB_TABLE_ERR_VER_COLUMN_MISSING => 'Verification failed: column does not exist',
- DB_TABLE_ERR_VER_COLUMN_TYPE => 'Verification failed: wrong column type',
- DB_TABLE_ERR_NO_COLS => 'Column definition array may not be empty',
- DB_TABLE_ERR_VER_IDX_MISSING => 'Verification failed: index does not exist',
- DB_TABLE_ERR_VER_IDX_COL_MISSING => 'Verification failed: index does not contain all specified cols',
- DB_TABLE_ERR_CREATE_PHPTYPE => 'Creation mode is not supported for this phptype',
- DB_TABLE_ERR_DECLARE_PRIMARY => 'Only one primary key is allowed',
- DB_TABLE_ERR_DECLARE_PRIM_SQLITE => 'SQLite does not support primary keys',
- DB_TABLE_ERR_ALTER_TABLE_IMPOS => 'Alter table failed: changing the field type not possible',
- DB_TABLE_ERR_ALTER_INDEX_IMPOS => 'Alter table failed: changing the index/constraint not possible'
- );
- // merge default and user-defined error messages
- if (!isset($GLOBALS['_DB_TABLE']['error'])) {
- $GLOBALS['_DB_TABLE']['error'] = array();
- }
- foreach ($GLOBALS['_DB_TABLE']['default_error'] as $code => $message) {
- if (!array_key_exists($code, $GLOBALS['_DB_TABLE']['error'])) {
- $GLOBALS['_DB_TABLE']['error'][$code] = $message;
- }
- }
- /**
- *
- * DB_Table is a database API and data type SQL abstraction class.
- *
- * DB_Table provides database API abstraction, data type abstraction,
- * automated SELECT, INSERT, and UPDATE queries, automated table
- * creation, automated validation of inserted/updated column values,
- * and automated creation of QuickForm elemnts based on the column
- * definitions.
- *
- * @category DB
- *
- * @package DB_Table
- *
- * @author Paul M. Jones <pmjones@php.net>
- * @author Mark Wiesemann <wiesemann@php.net>
- *
- * @version 1.4.0
- *
- */
- class DB_Table {
-
-
- /**
- *
- * The PEAR DB/MDB2 object that connects to the database.
- *
- * @access public
- *
- * @var object
- *
- */
-
- var $db = null;
-
-
- /**
- *
- * The backend type
- *
- * @access public
- *
- * @var string
- *
- */
-
- var $backend = null;
-
-
- /**
- *
- * The table or view in the database to which this object binds.
- *
- * @access public
- *
- * @var string
- *
- */
-
- var $table = null;
-
-
- /**
- *
- * Associative array of column definitions.
- *
- * @access public
- *
- * @var array
- *
- */
-
- var $col = array();
-
-
- /**
- *
- * Associative array of index definitions.
- *
- * @access public
- *
- * @var array
- *
- */
-
- var $idx = array();
-
-
- /**
- *
- * Baseline SELECT maps for select(), selectResult(), selectCount().
- *
- * @access public
- *
- * @var array
- *
- */
-
- var $sql = array();
-
-
- /**
- *
- * Whether or not to automatically validate data at insert-time.
- *
- * @access private
- *
- * @var bool
- *
- */
-
- var $_valid_insert = true;
-
-
- /**
- *
- * Whether or not to automatically validate data at update-time.
- *
- * @access private
- *
- * @var bool
- *
- */
-
- var $_valid_update = true;
-
-
- /**
- *
- * When calling select() and selectResult(), use this fetch mode (usually
- * a DB_FETCHMODE_* constant). If null, uses whatever is set in the $db
- * PEAR DB/MDB2 object.
- *
- * @access public
- *
- * @var int
- *
- */
-
- var $fetchmode = null;
-
-
- /**
- *
- * When fetchmode is DB_FETCHMODE_OBJECT, use this class for each
- * returned row. If null, uses whatever is set in the $db
- * PEAR DB/MDB2 object.
- *
- * @access public
- *
- * @var string
- *
- */
-
- var $fetchmode_object_class = null;
-
-
- /**
- *
- * If there is an error on instantiation, this captures that error.
- *
- * This property is used only for errors encountered in the constructor
- * at instantiation time. To check if there was an instantiation error...
- *
- * <code>
- * $obj =& new DB_Table();
- * if ($obj->error) {
- * // ... error handling code here ...
- * }
- * </code>
- *
- * @var object PEAR_Error
- *
- */
-
- var $error = null;
- /**
- *
- * Whether or not to automatically recast data at insert- and update-time.
- *
- * @access private
- *
- * @var bool
- *
- */
-
- var $_auto_recast = true;
-
-
- /**
- *
- * Specialized version of throwError() modeled on PEAR_Error.
- *
- * Throws a PEAR_Error with a DB_Table error message based on a
- * DB_Table constant error code.
- *
- * @static
- *
- * @access public
- *
- * @param Charcoal_String $code A DB_Table error code constant.
- *
- * @param Charcoal_String $extra Extra text for the error (in addition to the
- * regular error message).
- *
- * @return object PEAR_Error
- *
- */
-
- function &throwError($code, $extra = null)
- {
- // get the error message text based on the error code
- $text = $GLOBALS['_DB_TABLE']['error'][$code];
-
- // add any additional error text
- if ($extra) {
- $text .= ' ' . $extra;
- }
-
- // done!
- $error = PEAR::throwError($text, $code);
- return $error;
- }
-
-
- /**
- *
- * Constructor.
- *
- * If there is an error on instantiation, $this->error will be
- * populated with the PEAR_Error.
- *
- * @access public
- *
- * @param object &$db A PEAR DB/MDB2 object.
- *
- * @param Charcoal_String $table The table name to connect to in the database.
- *
- * @param mixed $create The automatic table creation mode to pursue:
- * - boolean false to not attempt creation
- * - 'safe' to create the table only if it does not exist
- * - 'drop' to drop any existing table with the same name and re-create it
- * - 'verify' to check whether the table exists, whether all the columns
- * exist, whether the columns have the right type, and whether the indexes
- * exist and have the right type
- * - 'alter' does the same as 'safe' if the table does not exist; if it
- * exists, a verification for columns existence, the column types, the
- * indexes existence, and the indexes types will be performed and the
- * table schema will be modified if needed
- *
- * @return object DB_Table
- *
- */
-
- function DB_Table(&$db, $table, $create = false)
- {
- // is the first argument a DB/MDB2 object?
- $this->backend = null;
- if (is_subclass_of($db, 'db_common')) {
- $this->backend = 'db';
- } elseif (is_subclass_of($db, 'mdb2_driver_common')) {
- $this->backend = 'mdb2';
- }
- if (is_null($this->backend)) {
- $this->error =& DB_Table::throwError(DB_TABLE_ERR_NOT_DB_OBJECT);
- return;
- }
-
- // array with column definition may not be empty
- if (! isset($this->col) || is_null($this->col) ||
- (is_array($this->col) && count($this->col) === 0)) {
- $this->error =& DB_Table::throwError(DB_TABLE_ERR_NO_COLS);
- return;
- }
- // set the class properties
- $this->db =& $db;
- $this->table = $table;
-
- // is the RDBMS supported?
- list($phptype, $dbsyntax) = DB_Table::getPHPTypeAndDBSyntax($db);
- if (! DB_Table::supported($phptype, $dbsyntax)) {
- $this->error =& DB_Table::throwError(
- DB_TABLE_ERR_PHPTYPE,
- "({$db->phptype})"
- );
- return;
- }
- // load MDB2_Extended module
- if ($this->backend == 'mdb2') {
- $this->db->loadModule('Extended', null, false);
- }
- // should we attempt table creation?
- if ($create) {
- if ($this->backend == 'mdb2') {
- $this->db->loadModule('Manager');
- }
- // check whether the chosen mode is supported
- list($phptype,) = DB_Table::getPHPTypeAndDBSyntax($this->db);
- $mode_supported = DB_Table::modeSupported($create, $phptype);
- if (PEAR::isError($mode_supported)) {
- $this->error =& $mode_supported;
- return;
- }
- if (!$mode_supported) {
- $this->error =& $this->throwError(
- DB_TABLE_ERR_CREATE_PHPTYPE,
- "('$create', '$phptype')"
- );
- return;
- }
- include_once 'DB/Table/Manager.php';
- switch ($create) {
- case 'alter':
- $result = $this->alter();
- break;
- case 'drop':
- case 'safe':
- $result = $this->create($create);
- break;
- case 'verify':
- $result = $this->verify();
- break;
- }
-
- if (PEAR::isError($result)) {
- // problem creating/altering/verifing the table
- $this->error =& $result;
- return;
- }
- }
- }
-
-
- /**
- *
- * Is a particular RDBMS supported by DB_Table?
- *
- * @static
- *
- * @access public
- *
- * @param Charcoal_String $phptype The RDBMS type for PHP.
- *
- * @param Charcoal_String $dbsyntax The chosen database syntax.
- *
- * @return bool True if supported, false if not.
- *
- */
-
- function supported($phptype, $dbsyntax = '')
- {
- // only Firebird is supported, not its ancestor Interbase
- if ($phptype == 'ibase' && $dbsyntax != 'firebird') {
- return false;
- }
- $supported = array_keys($GLOBALS['_DB_TABLE']['type']);
- return in_array(strtolower($phptype), $supported);
- }
- /**
- *
- * Is a creation mode supported for a RDBMS by DB_Table?
- *
- * @access public
- *
- * @param Charcoal_String $mode The chosen creation mode.
- *
- * @param Charcoal_String $phptype The RDBMS type for PHP.
- *
- * @return bool|object True if supported, false if not, or a PEAR_Error
- * if an unknown mode is specified.
- *
- */
-
- function modeSupported($mode, $phptype)
- {
- // check phptype for validity
- $supported = array_keys($GLOBALS['_DB_TABLE']['type']);
- if (!in_array(strtolower($phptype), $supported)) {
- return false;
- }
- switch ($mode) {
- case 'drop':
- case 'safe':
- // supported for all RDBMS
- return true;
- case 'alter':
- case 'verify':
- // not supported for fbsql and mssql (yet)
- switch ($phptype) {
- case 'fbsql':
- case 'mssql':
- return false;
- default:
- return true;
- }
- default:
- // unknown creation mode
- return $this->throwError(
- DB_TABLE_ERR_CREATE_FLAG,
- "('$mode')"
- );
- }
- }
- /**
- *
- * Detect values of 'phptype' and 'dbsyntax' keys of DSN.
- *
- * @static
- *
- * @access public
- *
- * @param object &$db A PEAR DB/MDB2 object.
- *
- * @return array Values of 'phptype' and 'dbsyntax' keys of DSN.
- *
- */
- function getPHPTypeAndDBSyntax(&$db) {
- $phptype = '';
- $dbsyntax = '';
- if (is_subclass_of($db, 'db_common')) {
- $phptype = $db->phptype;
- $dbsyntax = $db->dbsyntax;
- } elseif (is_subclass_of($db, 'mdb2_driver_common')) {
- $dsn = MDB2::parseDSN($db->getDSN());
- $phptype = $dsn['phptype'];
- $dbsyntax = $dsn['dbsyntax'];
- }
- return array($phptype, $dbsyntax);
- }
- /**
- *
- * Overwrite one or more error messages, e.g. to internationalize them.
- *
- * @access public
- *
- * @param mixed $code If string, the error message with code $code will
- * be overwritten by $message. If array, the error messages with code
- * of each array key will be overwritten by the key's value.
- *
- * @param Charcoal_String $message Only used if $key is not an array.
- *
- * @return void
- *
- */
- function setErrorMessage($code, $message = null) {
- if (is_array($code)) {
- foreach ($code as $single_code => $single_message) {
- $GLOBALS['_DB_TABLE']['error'][$single_code] = $single_message;
- }
- } else {
- $GLOBALS['_DB_TABLE']['error'][$code] = $message;
- }
- }
- /**
- *
- * Returns all or part of the $this->col property array.
- *
- * @access public
- *
- * @param mixed $col If null, returns the $this->col property array
- * as it is. If string, returns that column name from the $this->col
- * array. If an array, returns those columns named as the array
- * values from the $this->col array as an array.
- *
- * @return mixed All or part of the $this->col property array, or
- * boolean false if no matching column names are found.
- *
- */
-
- function getColumns($col = null)
- {
- // by default, return all column definitions
- if (is_null($col)) {
- return $this->col;
- }
-
- // if the param is a string, only return the column definition
- // named by the that string
- if (is_string($col)) {
- if (isset($this->col[$col])) {
- return $this->col[$col];
- } else {
- return false;
- }
- }
-
- // if the param is a sequential array of column names,
- // return only those columns named in that array
- if (is_array($col)) {
- $set = array();
- foreach ($col as $name) {
- $set[$name] = $this->getColumns($name);
- }
-
- if (count($set) == 0) {
- return false;
- } else {
- return $set;
- }
- }
-
- // param was not null, string, or array
- return false;
- }
-
-
- /**
- *
- * Returns all or part of the $this->idx property array.
- *
- * @access public
- *
- * @param Charcoal_String $col If specified, returns only this index key
- * from the $this->col property array.
- *
- * @return array All or part of the $this->idx property array.
- *
- */
-
- function getIndexes($idx = null)
- {
- // by default, return all index definitions
- if (is_null($idx)) {
- return $this->idx;
- }
-
- // if the param is a string, only return the index definition
- // named by the that string
- if (is_string($idx)) {
- if (isset($this->idx[$idx])) {
- return $this->idx[$idx];
- } else {
- return false;
- }
- }
-
- // if the param is a sequential array of index names,
- // return only those indexes named in that array
- if (is_array($idx)) {
- $set = array();
- foreach ($idx as $name) {
- $set[$name] = $this->getIndexes($name);
- }
-
- if (count($set) == 0) {
- return false;
- } else {
- return $set;
- }
- }
-
- // param was not null, string, or array
- return false;
- }
-
-
- /**
- *
- * Selects rows from the table using one of the DB/MDB2 get*() methods.
- *
- * @access public
- *
- * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
- * $this->sql property array.
- *
- * @param Charcoal_String $filter Ad-hoc SQL snippet to AND with the default
- * SELECT WHERE clause.
- *
- * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
- * SELECT ORDER BY clause.
- *
- * @param int $start The row number to start listing from in the
- * result set.
- *
- * @param int $count The number of rows to list in the result set.
- *
- * @param array $params Parameters to use in placeholder substitutions (if
- * any).
- *
- * @return mixed An array of records from the table (if anything but
- * 'getOne'), a single value (if 'getOne'), or a PEAR_Error object.
- *
- * @see DB::getAll()
- *
- * @see MDB2::getAll()
- *
- * @see DB::getAssoc()
- *
- * @see MDB2::getAssoc()
- *
- * @see DB::getCol()
- *
- * @see MDB2::getCol()
- *
- * @see DB::getOne()
- *
- * @see MDB2::getOne()
- *
- * @see DB::getRow()
- *
- * @see MDB2::getRow()
- *
- * @see DB_Table::_swapModes()
- *
- */
-
- function select($sqlkey, $filter = null, $order = null,
- $start = null, $count = null, $params = array())
- {
- // build the base command
- $sql = $this->buildSQL($sqlkey, $filter, $order, $start, $count);
- if (PEAR::isError($sql)) {
- return $sql;
- }
-
- // set the get*() method name
- if (isset($this->sql[$sqlkey]['get'])) {
- $method = ucwords(strtolower(trim($this->sql[$sqlkey]['get'])));
- $method = "get$method";
- } else {
- $method = 'getAll';
- }
-
- // DB_Table assumes you are using a shared PEAR DB/MDB2 object. Other
- // scripts using the same object probably expect its fetchmode
- // not to change, unless they change it themselves. Thus, to
- // provide friendly mode-swapping, we will restore these modes
- // afterwards.
- $restore_mode = $this->db->fetchmode;
- if ($this->backend == 'mdb2') {
- $restore_class = $this->db->getOption('fetch_class');
- } else {
- $restore_class = $this->db->fetchmode_object_class;
- }
- // swap modes
- $fetchmode = $this->fetchmode;
- $fetchmode_object_class = $this->fetchmode_object_class;
- if (isset($this->sql[$sqlkey]['fetchmode'])) {
- $fetchmode = $this->sql[$sqlkey]['fetchmode'];
- }
- if (isset($this->sql[$sqlkey]['fetchmode_object_class'])) {
- $fetchmode_object_class = $this->sql[$sqlkey]['fetchmode_object_class'];
- }
- $this->_swapModes($fetchmode, $fetchmode_object_class);
- // make sure params is an array
- if (! is_null($params)) {
- $params = (array) $params;
- }
-
- // get the result
- if ($this->backend == 'mdb2') {
- $result = $this->db->extended->$method($sql, null, $params);
- } else {
- switch ($method) {
- case 'getCol':
- $result = $this->db->$method($sql, 0, $params);
- break;
- case 'getAssoc':
- $result = $this->db->$method($sql, false, $params);
- break;
- default:
- $result = $this->db->$method($sql, $params);
- break;
- }
- }
-
- // swap modes back
- $this->_swapModes($restore_mode, $restore_class);
-
- // return the result
- return $result;
- }
-
-
- /**
- *
- * Selects rows from the table as a DB_Result/MDB2_Result_* object.
- *
- * @access public
- *
- * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
- * $this->sql property array.
- *
- * @param Charcoal_String $filter Ad-hoc SQL snippet to add to the default
- * SELECT WHERE clause.
- *
- * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
- * SELECT ORDER BY clause.
- *
- * @param int $start The record number to start listing from in the
- * result set.
- *
- * @param int $count The number of records to list in the result set.
- *
- * @param array $params Parameters to use in placeholder substitutions (if
- * any).
- *
- * @return mixed A PEAR_Error on failure, or a DB_Result/MDB2_Result_*
- * object on success.
- *
- * @see DB_Table::_swapModes()
- *
- */
-
- function selectResult($sqlkey, $filter = null, $order = null,
- $start = null, $count = null, $params = array())
- {
- // build the base command
- $sql = $this->buildSQL($sqlkey, $filter, $order, $start, $count);
- if (PEAR::isError($sql)) {
- return $sql;
- }
-
- // DB_Table assumes you are using a shared PEAR DB/MDB2 object. Other
- // scripts using the same object probably expect its fetchmode
- // not to change, unless they change it themselves. Thus, to
- // provide friendly mode-swapping, we will restore these modes
- // afterwards.
- $restore_mode = $this->db->fetchmode;
- if ($this->backend == 'mdb2') {
- $restore_class = $this->db->getOption('fetch_class');
- } else {
- $restore_class = $this->db->fetchmode_object_class;
- }
-
- // swap modes
- $fetchmode = $this->fetchmode;
- $fetchmode_object_class = $this->fetchmode_object_class;
- if (isset($this->sql[$sqlkey]['fetchmode'])) {
- $fetchmode = $this->sql[$sqlkey]['fetchmode'];
- }
- if (isset($this->sql[$sqlkey]['fetchmode_object_class'])) {
- $fetchmode_object_class = $this->sql[$sqlkey]['fetchmode_object_class'];
- }
- $this->_swapModes($fetchmode, $fetchmode_object_class);
-
- // make sure params is an array
- if (! is_null($params)) {
- $params = (array) $params;
- }
-
- // get the result
- if ($this->backend == 'mdb2') {
- $stmt =& $this->db->prepare($sql);
- $result =& $stmt->execute($params);
- } else {
- $result =& $this->db->query($sql, $params);
- }
-
- // swap modes back
- $this->_swapModes($restore_mode, $restore_class);
-
- // return the result
- return $result;
- }
-
-
- /**
- *
- * Counts the number of rows which will be returned by a query.
- *
- * This function works identically to {@link select()}, but it
- * returns the number of rows returned by a query instead of the
- * query results themselves.
- *
- * This makes using DB_Table with Pager easier, since you can pass the
- * return value of this to Pager as totalItems, then select only the
- * rows you need to display on a page.
- *
- * @author Ian Eure <ian@php.net>
- *
- * @access public
- *
- * @param Charcoal_String $sqlkey The name of the SQL SELECT to use from the
- * $this->sql property array.
- *
- * @param Charcoal_String $filter Ad-hoc SQL snippet to AND with the default
- * SELECT WHERE clause.
- *
- * @param Charcoal_String $order Ad-hoc SQL snippet to override the default
- * SELECT ORDER BY clause.
- *
- * @param int $start The row number to start listing from in the
- * result set.
- *
- * @param int $count The number of rows to list in the result set.
- *
- * @param array $params Parameters to use in placeholder substitutions (if
- * any).
- *
- * @return mixed An integer number of records from the table, or a
- * PEAR_Error object.
- *
- * @see DB_Table::select()
- *
- */
-
- function selectCount($sqlkey, $filter = null, $order = null,
- $start = null, $count = null, $params = array())
- {
- // does the SQL SELECT key exist?
- $tmp = array_keys($this->sql);
- if (! in_array($sqlkey, $tmp)) {
- return $this->throwError(
- DB_TABLE_ERR_SQL_UNDEF,
- "('$sqlkey')"
- );
- }
-
- // create a SQL key name for this count-query
- $count_key = '__count_' . $sqlkey;
-
- // has a count-query for the SQL key already been created?
- if (! isset($this->sql[$count_key])) {
-
- // we've not asked for a count on this query yet.
- // get the elements of the query ...
- $count_sql = $this->sql[$sqlkey];
-
- // is a count-field set for the query?
- if (! isset($count_sql['count']) ||
- trim($count_sql['count']) == '') {
- $count_sql['count'] = '*';
- }
-
- // replace the SELECT fields with a COUNT() command
- $count_sql['select'] = "COUNT({$count_sql['count']})";
-
- // replace the 'get' key so we only get the one result item
- $count_sql['get'] = 'one';
-
- // create the new count-query in the $sql array
- $this->sql[$count_key] = $count_sql;
- }
-
- // retrieve the count results
- return $this->select($count_key, $filter, $order, $start, $count,
- $params);
- }
-
-
- /**
- *
- * Changes the $this->db PEAR DB/MDB2 object fetchmode and
- * fetchmode_object_class.
- *
- * Because DB_Table objects tend to use the same PEAR DB/MDB2 object, it
- * may sometimes be useful to have one object return results in one
- * mode, and have another object return results in a different mode.
- * This method allows us to switch DB/MDB2 fetch modes on the fly.
- *
- * @access private
- *
- * @param Charcoal_String $new_mode A DB_FETCHMODE_* constant. If null,
- * defaults to whatever the DB/MDB2 object is currently using.
- *
- * @param Charcoal_String $new_class The object class to use for results when
- * the $db object is in DB_FETCHMODE_OBJECT fetch mode. If null,
- * defaults to whatever the the DB/MDB2 object is currently using.
- *
- * @return void
- *
- */
-
- function _swapModes($new_mode, $new_class)
- {
- // get the old (current) mode and class
- $old_mode = $this->db->fetchmode;
- if ($this->backend == 'mdb2') {
- $old_class = $this->db->getOption('fetch_class');
- } else {
- $old_class = $this->db->fetchmode_object_class;
- }
-
- // don't need to swap anything if the new modes are both
- // null or if the old and new modes already match.
- if ((is_null($new_mode) && is_null($new_class)) ||
- ($old_mode == $new_mode && $old_class == $new_class)) {
- return;
- }
-
- // set the default new mode
- if (is_null($new_mode)) {
- $new_mode = $old_mode;
- }
-
- // set the default new class
- if (is_null($new_class)) {
- $new_class = $old_class;
- }
-
- // swap modes
- $this->db->setFetchMode($new_mode, $new_class);
- }
-
-
- /**
- *
- * Builds the SQL command from a specified $this->sql element.
- *
- * @access public
- *
- * @param Charcoal_String $sqlkey The $this->sql key to use as the basis for the
- * SQL query string.
- *
- * @param Charcoal_String $filter A filter to add to the WHERE clause of the
- * defined SELECT in $this->sql.
- *
- * @param Charcoal_String $order An ORDER clause to override the defined order
- * in $this->sql.
- *
- * @param int $start The row number to start listing from in the
- * result set.
- *
- * @param int $count The number of rows to list in the result set.
- *
- * @return mixed A PEAR_Error on failure, or an SQL command string on
- * success.
- *
- */
-
- function buildSQL($sqlkey, $filter = null, $order = null,
- $start = null, $count = null)
- {
- // does the SQL SELECT key exist?
- if (is_null($this->sql)) {
- $this->sql = array();
- }
- $tmp = array_keys($this->sql);
- if (! in_array($sqlkey, $tmp)) {
- return $this->throwError(
- DB_TABLE_ERR_SQL_UNDEF,
- "('$sqlkey')"
- );
- }
-
- // the SQL clause parts and their default values
- $part = array(
- 'select' => '*',
- 'from' => $this->table,
- 'join' => null,
- 'where' => null,
- 'group' => null,
- 'having' => null,
- 'order' => null
- );
-
- // loop through each possible clause
- foreach ($part as $key => $val) {
- if (! isset($this->sql[$sqlkey][$key])) {
- continue;
- } else {
- $part[$key] = $this->sql[$sqlkey][$key];
- }
- }
-
- // add the filter to the WHERE part
- if ($filter) {
- if (! $part['where']) {
- $part['where'] .= $filter;
- } else {
- $part['where'] .= " AND ($filter)";
- }
- }
-
- // override the ORDER part
- if ($order) {
- $part['order'] = $order;
- }
-
- // build up the command string form the parts
- $cmd = '';
- foreach ($part as $key => $val) {
-
- // if the part value has not been set, skip it
- if (! $val) {
- continue;
- }
-
- switch ($key) {
-
- case 'join':
- $cmd .= " $val\n";
- break;
-
- case 'group':
- case 'order':
- $cmd .= strtoupper($key) . " BY $val\n";
- break;
-
- default:
- $cmd .= strtoupper($key) . " $val\n";
- break;
-
- }
- }
-
- // add LIMIT if requested
- if (! is_null($start) && ! is_null($count)) {
- if ($this->backend == 'mdb2') {
- $this->db->setLimit($count, $start);
- } else {
- $cmd = $this->db->modifyLimitQuery(
- $cmd, $start, $count);
- }
- }
-
- return $cmd;
- }
-
-
- /**
- *
- * Inserts a single table row after validating through validInsert().
- *
- * @access public
- *
- * @param array $data An associative array of key-value pairs where
- * the key is the column name and the value is the column value. This
- * is the data that will be inserted into the table. Data is checked
- * against the column data type for validity.
- *
- * @return mixed Void on success, a PEAR_Error object on failure.
- *
- * @see validInsert()
- *
- * @see DB::autoExecute()
- *
- * @see MDB2::autoExecute()
- *
- */
-
- function insert($data)
- {
- // forcibly recast the data elements to their proper types?
- if ($this->_auto_recast) {
- $this->recast($data);
- }
-
- // validate the data if auto-validation is turned on
- if ($this->_valid_insert) {
- $result = $this->validInsert($data);
- if (PEAR::isError($result)) {
- return $result;
- }
- }
- if ($this->backend == 'mdb2') {
- $result = $this->db->extended->autoExecute($this->table, $data,
- MDB2_AUTOQUERY_INSERT);
- } else {
- $result = $this->db->autoExecute($this->table, $data,
- DB_AUTOQUERY_INSERT);
- }
- return $result;
- }
-
-
- /**
- *
- * Turns on (or off) automatic validation of inserted data.
- *
- * @access public
- *
- * @param bool $flag True to turn on auto-validation, false to turn it off.
- *
- * @return void
- *
- */
-
- function autoValidInsert($flag = true)
- {
- if ($flag) {
- $this->_valid_insert = true;
- } else {
- $this->_valid_insert = false;
- }
- }
-
-
- /**
- *
- * Validates an array for insertion into the table.
- *
- * @access public
- *
- * @param array $data An associative array of key-value pairs where
- * the key is the column name and the value is the column value. This
- * is the data that will be inserted into the table. Data is checked
- * against the column data type for validity.
- *
- * @return mixed Boolean true on success, a PEAR_Error object on
- * failure.
- *
- * @see insert()
- *
- */
-
- function validInsert(&$data)
- {
- // loop through the data, and disallow insertion of unmapped
- // columns
- foreach ($data as $col => $val) {
- if (! isset($this->col[$col])) {
- return $this->throwError(
- DB_TABLE_ERR_INS_COL_NOMAP,
- "('$col')"
- );
- }
- }
-
- // loop through each column mapping, and check the data to be
- // inserted into it against the column data type. we loop through
- // column mappings instead of the insert data to make sure that
- // all necessary columns are being inserted.
- foreach ($this->col as $col => $val) {
-
- // is the value allowed to be null?
- if (isset($val['require']) &&
- $val['require'] == true &&
- (! isset($data[$col]) || is_null($data[$col]))) {
- return $this->throwError(
- DB_TABLE_ERR_INS_COL_REQUIRED,
- "'$col'"
- );
- }
-
- // does the value to be inserted match the column data type?
- if (isset($data[$col]) &&
- ! $this->isValid($data[$col], $col)) {
- return $this->throwError(
- DB_TABLE_ERR_INS_DATA_INVALID,
- "'$col' ('$data[$col]')"
- );
- }
- }
-
- return true;
- }
-
-
- /**
- *
- * Updates table row(s) matching a custom WHERE clause, after checking
- * against validUpdate().
- *
- * @access public
- *
- * @param array $data An associative array of key-value pairs where
- * the key is the column name and the value is the column value. These
- * are the columns that will be updated with new values.
- *
- * @param Charcoal_String $where An SQL WHERE clause limiting which records
- * are to be updated.
- *
- * @return mixed Void on success, a PEAR_Error object on failure.
- *
- * @see validUpdate()
- *
- * @see DB::autoExecute()
- *
- * @see MDB2::autoExecute()
- *
- */
-
- function update($data, $where)
- {
- // forcibly recast the data elements to their proper types?
- if ($this->_auto_recast) {
- $this->recast($data);
- }
-
- // validate the data if auto-validation is turned on
- if ($this->_valid_update) {
- $result = $this->validUpdate($data);
- if (PEAR::isError($result)) {
- return $result;
- }
- }
-
- if ($this->backend == 'mdb2') {
- $result = $this->db->extended->autoExecute($this->table, $data,
- MDB2_AUTOQUERY_UPDATE, $where);
- } else {
- $result = $this->db->autoExecute($this->table, $data,
- DB_AUTOQUERY_UPDATE, $where);
- }
- return $result;
- }
-
-
- /**
- *
- * Turns on (or off) automatic validation of updated data.
- *
- * @access public
- *
- * @param bool $flag True to turn on auto-validation, false to turn it off.
- *
- * @return void
- *
- */
-
- function autoValidUpdate($flag = true)
- {
- if ($flag) {
- $this->_valid_update = true;
- } else {
- $this->_valid_update = false;
- }
- }
-
-
- /**
- *
- * Validates an array for updating the table.
- *
- * @access public
- *
- * @param array $data An associative array of key-value pairs where
- * the key is the column name and the value is the column value. …
Large files files are truncated, but you can click here to view the full file