Rev 320 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
<?php/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: *//*** The PEAR DB driver for PHP's mysql extension* for interacting with MySQL databases** PHP versions 4 and 5** LICENSE: This source file is subject to version 3.0 of the PHP license* that is available through the world-wide-web at the following URI:* http://www.php.net/license/3_0.txt. If you did not receive a copy of* the PHP License and are unable to obtain it through the web, please* send a note to license@php.net so we can mail you a copy immediately.** @category Database* @package DB* @author Stig Bakken <ssb@php.net>* @author Daniel Convissor <danielc@php.net>* @copyright 1997-2005 The PHP Group* @license http://www.php.net/license/3_0.txt PHP License 3.0* @version CVS: $Id: mysql.php,v 1.2 2005-09-20 17:01:22 ddelon Exp $* @link http://pear.php.net/package/DB*//*** Obtain the DB_common class so it can be extended from*/require_once 'DB/common.php';/*** The methods PEAR DB uses to interact with PHP's mysql extension* for interacting with MySQL databases** These methods overload the ones declared in DB_common.** @category Database* @package DB* @author Stig Bakken <ssb@php.net>* @author Daniel Convissor <danielc@php.net>* @copyright 1997-2005 The PHP Group* @license http://www.php.net/license/3_0.txt PHP License 3.0* @version Release: @package_version@* @link http://pear.php.net/package/DB*/class DB_mysql extends DB_common{// {{{ properties/*** The DB driver type (mysql, oci8, odbc, etc.)* @var string*/var $phptype = 'mysql';/*** The database syntax variant to be used (db2, access, etc.), if any* @var string*/var $dbsyntax = 'mysql';/*** The capabilities of this DB implementation** The 'new_link' element contains the PHP version that first provided* new_link support for this DBMS. Contains false if it's unsupported.** Meaning of the 'limit' element:* + 'emulate' = emulate with fetch row by number* + 'alter' = alter the query* + false = skip rows** @var array*/var $features = array('limit' => 'alter','new_link' => '4.2.0','numrows' => true,'pconnect' => true,'prepare' => false,'ssl' => false,'transactions' => true,);/*** A mapping of native error codes to DB error codes* @var array*/var $errorcode_map = array(1004 => DB_ERROR_CANNOT_CREATE,1005 => DB_ERROR_CANNOT_CREATE,1006 => DB_ERROR_CANNOT_CREATE,1007 => DB_ERROR_ALREADY_EXISTS,1008 => DB_ERROR_CANNOT_DROP,1022 => DB_ERROR_ALREADY_EXISTS,1044 => DB_ERROR_ACCESS_VIOLATION,1046 => DB_ERROR_NODBSELECTED,1048 => DB_ERROR_CONSTRAINT,1049 => DB_ERROR_NOSUCHDB,1050 => DB_ERROR_ALREADY_EXISTS,1051 => DB_ERROR_NOSUCHTABLE,1054 => DB_ERROR_NOSUCHFIELD,1061 => DB_ERROR_ALREADY_EXISTS,1062 => DB_ERROR_ALREADY_EXISTS,1064 => DB_ERROR_SYNTAX,1091 => DB_ERROR_NOT_FOUND,1100 => DB_ERROR_NOT_LOCKED,1136 => DB_ERROR_VALUE_COUNT_ON_ROW,1142 => DB_ERROR_ACCESS_VIOLATION,1146 => DB_ERROR_NOSUCHTABLE,1216 => DB_ERROR_CONSTRAINT,1217 => DB_ERROR_CONSTRAINT,);/*** The raw database connection created by PHP* @var resource*/var $connection;/*** The DSN information for connecting to a database* @var array*/var $dsn = array();/*** Should data manipulation queries be committed automatically?* @var bool* @access private*/var $autocommit = true;/*** The quantity of transactions begun** {@internal While this is private, it can't actually be designated* private in PHP 5 because it is directly accessed in the test suite.}}** @var integer* @access private*/var $transaction_opcount = 0;/*** The database specified in the DSN** It's a fix to allow calls to different databases in the same script.** @var string* @access private*/var $_db = '';// }}}// {{{ constructor/*** This constructor calls <kbd>$this->DB_common()</kbd>** @return void*/function DB_mysql(){$this->DB_common();}// }}}// {{{ connect()/*** Connect to the database server, log in and open the database** Don't call this method directly. Use DB::connect() instead.** PEAR DB's mysql driver supports the following extra DSN options:* + new_link If set to true, causes subsequent calls to connect()* to return a new connection link instead of the* existing one. WARNING: this is not portable to* other DBMS's. Available since PEAR DB 1.7.0.* + client_flags Any combination of MYSQL_CLIENT_* constants.* Only used if PHP is at version 4.3.0 or greater.* Available since PEAR DB 1.7.0.** @param array $dsn the data source name* @param bool $persistent should the connection be persistent?** @return int DB_OK on success. A DB_Error object on failure.*/function connect($dsn, $persistent = false){if (!PEAR::loadExtension('mysql')) {return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);}$this->dsn = $dsn;if ($dsn['dbsyntax']) {$this->dbsyntax = $dsn['dbsyntax'];}$params = array();if ($dsn['protocol'] && $dsn['protocol'] == 'unix') {$params[0] = ':' . $dsn['socket'];} else {$params[0] = $dsn['hostspec'] ? $dsn['hostspec']: 'localhost';if ($dsn['port']) {$params[0] .= ':' . $dsn['port'];}}$params[] = $dsn['username'] ? $dsn['username'] : null;$params[] = $dsn['password'] ? $dsn['password'] : null;if (!$persistent) {if (isset($dsn['new_link'])&& ($dsn['new_link'] == 'true' || $dsn['new_link'] === true)){$params[] = true;} else {$params[] = false;}}if (version_compare(phpversion(), '4.3.0', '>=')) {$params[] = isset($dsn['client_flags'])? $dsn['client_flags'] : null;}$connect_function = $persistent ? 'mysql_pconnect' : 'mysql_connect';$ini = ini_get('track_errors');$php_errormsg = '';if ($ini) {$this->connection = @call_user_func_array($connect_function,$params);} else {ini_set('track_errors', 1);$this->connection = @call_user_func_array($connect_function,$params);ini_set('track_errors', $ini);}if (!$this->connection) {if (($err = @mysql_error()) != '') {return $this->raiseError(DB_ERROR_CONNECT_FAILED,null, null, null,$err);} else {return $this->raiseError(DB_ERROR_CONNECT_FAILED,null, null, null,$php_errormsg);}}if ($dsn['database']) {if (!@mysql_select_db($dsn['database'], $this->connection)) {return $this->mysqlRaiseError();}$this->_db = $dsn['database'];}return DB_OK;}// }}}// {{{ disconnect()/*** Disconnects from the database server** @return bool TRUE on success, FALSE on failure*/function disconnect(){$ret = @mysql_close($this->connection);$this->connection = null;return $ret;}// }}}// {{{ simpleQuery()/*** Sends a query to the database server** Generally uses mysql_query(). If you want to use* mysql_unbuffered_query() set the "result_buffering" option to 0 using* setOptions(). This option was added in Release 1.7.0.** @param string the SQL query string** @return mixed + a PHP result resrouce for successful SELECT queries* + the DB_OK constant for other successful queries* + a DB_Error object on failure*/function simpleQuery($query){$ismanip = DB::isManip($query);$this->last_query = $query;$query = $this->modifyQuery($query);if ($this->_db) {if (!@mysql_select_db($this->_db, $this->connection)) {return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);}}if (!$this->autocommit && $ismanip) {if ($this->transaction_opcount == 0) {$result = @mysql_query('SET AUTOCOMMIT=0', $this->connection);$result = @mysql_query('BEGIN', $this->connection);if (!$result) {return $this->mysqlRaiseError();}}$this->transaction_opcount++;}if (!$this->options['result_buffering']) {$result = @mysql_unbuffered_query($query, $this->connection);} else {$result = @mysql_query($query, $this->connection);}if (!$result) {return $this->mysqlRaiseError();}if (is_resource($result)) {return $result;}return DB_OK;}// }}}// {{{ nextResult()/*** Move the internal mysql result pointer to the next available result** This method has not been implemented yet.** @param a valid sql result resource** @return false*/function nextResult($result){return false;}// }}}// {{{ fetchInto()/*** Places a row from the result set into the given array** Formating of the array and the data therein are configurable.* See DB_result::fetchInto() for more information.** This method is not meant to be called directly. Use* DB_result::fetchInto() instead. It can't be declared "protected"* because DB_result is a separate object.** @param resource $result the query result resource* @param array $arr the referenced array to put the data in* @param int $fetchmode how the resulting array should be indexed* @param int $rownum the row number to fetch (0 = first row)** @return mixed DB_OK on success, NULL when the end of a result set is* reached or on failure** @see DB_result::fetchInto()*/function fetchInto($result, &$arr, $fetchmode, $rownum = null){if ($rownum !== null) {if (!@mysql_data_seek($result, $rownum)) {return null;}}if ($fetchmode & DB_FETCHMODE_ASSOC) {$arr = @mysql_fetch_array($result, MYSQL_ASSOC);if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {$arr = array_change_key_case($arr, CASE_LOWER);}} else {$arr = @mysql_fetch_row($result);}if (!$arr) {return null;}if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {/** Even though this DBMS already trims output, we do this because* a field might have intentional whitespace at the end that* gets removed by DB_PORTABILITY_RTRIM under another driver.*/$this->_rtrimArrayValues($arr);}if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {$this->_convertNullArrayValuesToEmpty($arr);}return DB_OK;}// }}}// {{{ freeResult()/*** Deletes the result set and frees the memory occupied by the result set** This method is not meant to be called directly. Use* DB_result::free() instead. It can't be declared "protected"* because DB_result is a separate object.** @param resource $result PHP's query result resource** @return bool TRUE on success, FALSE if $result is invalid** @see DB_result::free()*/function freeResult($result){return @mysql_free_result($result);}// }}}// {{{ numCols()/*** Gets the number of columns in a result set** This method is not meant to be called directly. Use* DB_result::numCols() instead. It can't be declared "protected"* because DB_result is a separate object.** @param resource $result PHP's query result resource** @return int the number of columns. A DB_Error object on failure.** @see DB_result::numCols()*/function numCols($result){$cols = @mysql_num_fields($result);if (!$cols) {return $this->mysqlRaiseError();}return $cols;}// }}}// {{{ numRows()/*** Gets the number of rows in a result set** This method is not meant to be called directly. Use* DB_result::numRows() instead. It can't be declared "protected"* because DB_result is a separate object.** @param resource $result PHP's query result resource** @return int the number of rows. A DB_Error object on failure.** @see DB_result::numRows()*/function numRows($result){$rows = @mysql_num_rows($result);if ($rows === null) {return $this->mysqlRaiseError();}return $rows;}// }}}// {{{ autoCommit()/*** Enables or disables automatic commits** @param bool $onoff true turns it on, false turns it off** @return int DB_OK on success. A DB_Error object if the driver* doesn't support auto-committing transactions.*/function autoCommit($onoff = false){// XXX if $this->transaction_opcount > 0, we should probably// issue a warning here.$this->autocommit = $onoff ? true : false;return DB_OK;}// }}}// {{{ commit()/*** Commits the current transaction** @return int DB_OK on success. A DB_Error object on failure.*/function commit(){if ($this->transaction_opcount > 0) {if ($this->_db) {if (!@mysql_select_db($this->_db, $this->connection)) {return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);}}$result = @mysql_query('COMMIT', $this->connection);$result = @mysql_query('SET AUTOCOMMIT=1', $this->connection);$this->transaction_opcount = 0;if (!$result) {return $this->mysqlRaiseError();}}return DB_OK;}// }}}// {{{ rollback()/*** Reverts the current transaction** @return int DB_OK on success. A DB_Error object on failure.*/function rollback(){if ($this->transaction_opcount > 0) {if ($this->_db) {if (!@mysql_select_db($this->_db, $this->connection)) {return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);}}$result = @mysql_query('ROLLBACK', $this->connection);$result = @mysql_query('SET AUTOCOMMIT=1', $this->connection);$this->transaction_opcount = 0;if (!$result) {return $this->mysqlRaiseError();}}return DB_OK;}// }}}// {{{ affectedRows()/*** Determines the number of rows affected by a data maniuplation query** 0 is returned for queries that don't manipulate data.** @return int the number of rows. A DB_Error object on failure.*/function affectedRows(){if (DB::isManip($this->last_query)) {return @mysql_affected_rows($this->connection);} else {return 0;}}// }}}// {{{ nextId()/*** Returns the next free id in a sequence** @param string $seq_name name of the sequence* @param boolean $ondemand when true, the seqence is automatically* created if it does not exist** @return int the next id number in the sequence.* A DB_Error object on failure.** @see DB_common::nextID(), DB_common::getSequenceName(),* DB_mysql::createSequence(), DB_mysql::dropSequence()*/function nextId($seq_name, $ondemand = true){$seqname = $this->getSequenceName($seq_name);do {$repeat = 0;$this->pushErrorHandling(PEAR_ERROR_RETURN);$result = $this->query("UPDATE ${seqname} ".'SET id=LAST_INSERT_ID(id+1)');$this->popErrorHandling();if ($result === DB_OK) {// COMMON CASE$id = @mysql_insert_id($this->connection);if ($id != 0) {return $id;}// EMPTY SEQ TABLE// Sequence table must be empty for some reason, so fill// it and return 1 and obtain a user-level lock$result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");if (DB::isError($result)) {return $this->raiseError($result);}if ($result == 0) {// Failed to get the lockreturn $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);}// add the default value$result = $this->query("REPLACE INTO ${seqname} (id) VALUES (0)");if (DB::isError($result)) {return $this->raiseError($result);}// Release the lock$result = $this->getOne('SELECT RELEASE_LOCK('. "'${seqname}_lock')");if (DB::isError($result)) {return $this->raiseError($result);}// We know what the result will be, so no need to try againreturn 1;} elseif ($ondemand && DB::isError($result) &&$result->getCode() == DB_ERROR_NOSUCHTABLE){// ONDEMAND TABLE CREATION$result = $this->createSequence($seq_name);if (DB::isError($result)) {return $this->raiseError($result);} else {$repeat = 1;}} elseif (DB::isError($result) &&$result->getCode() == DB_ERROR_ALREADY_EXISTS){// BACKWARDS COMPAT// see _BCsequence() comment$result = $this->_BCsequence($seqname);if (DB::isError($result)) {return $this->raiseError($result);}$repeat = 1;}} while ($repeat);return $this->raiseError($result);}// }}}// {{{ createSequence()/*** Creates a new sequence** @param string $seq_name name of the new sequence** @return int DB_OK on success. A DB_Error object on failure.** @see DB_common::createSequence(), DB_common::getSequenceName(),* DB_mysql::nextID(), DB_mysql::dropSequence()*/function createSequence($seq_name){$seqname = $this->getSequenceName($seq_name);$res = $this->query('CREATE TABLE ' . $seqname. ' (id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'. ' PRIMARY KEY(id))');if (DB::isError($res)) {return $res;}// insert yields value 1, nextId call will generate ID 2$res = $this->query("INSERT INTO ${seqname} (id) VALUES (0)");if (DB::isError($res)) {return $res;}// so reset to zeroreturn $this->query("UPDATE ${seqname} SET id = 0");}// }}}// {{{ dropSequence()/*** Deletes a sequence** @param string $seq_name name of the sequence to be deleted** @return int DB_OK on success. A DB_Error object on failure.** @see DB_common::dropSequence(), DB_common::getSequenceName(),* DB_mysql::nextID(), DB_mysql::createSequence()*/function dropSequence($seq_name){return $this->query('DROP TABLE ' . $this->getSequenceName($seq_name));}// }}}// {{{ _BCsequence()/*** Backwards compatibility with old sequence emulation implementation* (clean up the dupes)** @param string $seqname the sequence name to clean up** @return bool true on success. A DB_Error object on failure.** @access private*/function _BCsequence($seqname){// Obtain a user-level lock... this will release any previous// application locks, but unlike LOCK TABLES, it does not abort// the current transaction and is much less frequently used.$result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");if (DB::isError($result)) {return $result;}if ($result == 0) {// Failed to get the lock, can't do the conversion, bail// with a DB_ERROR_NOT_LOCKED errorreturn $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);}$highest_id = $this->getOne("SELECT MAX(id) FROM ${seqname}");if (DB::isError($highest_id)) {return $highest_id;}// This should kill all rows except the highest// We should probably do something if $highest_id isn't// numeric, but I'm at a loss as how to handle that...$result = $this->query('DELETE FROM ' . $seqname. " WHERE id <> $highest_id");if (DB::isError($result)) {return $result;}// If another thread has been waiting for this lock,// it will go thru the above procedure, but will have no// real effect$result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");if (DB::isError($result)) {return $result;}return true;}// }}}// {{{ quoteIdentifier()/*** Quotes a string so it can be safely used as a table or column name** MySQL can't handle the backtick character (<kbd>`</kbd>) in* table or column names.** @param string $str identifier name to be quoted** @return string quoted identifier string** @see DB_common::quoteIdentifier()* @since Method available since Release 1.6.0*/function quoteIdentifier($str){return '`' . $str . '`';}// }}}// {{{ quote()/*** @deprecated Deprecated in release 1.6.0*/function quote($str){return $this->quoteSmart($str);}// }}}// {{{ escapeSimple()/*** Escapes a string according to the current DBMS's standards** @param string $str the string to be escaped** @return string the escaped string** @see DB_common::quoteSmart()* @since Method available since Release 1.6.0*/function escapeSimple($str){if (function_exists('mysql_real_escape_string')) {return @mysql_real_escape_string($str, $this->connection);} else {return @mysql_escape_string($str);}}// }}}// {{{ modifyQuery()/*** Changes a query string for various DBMS specific reasons** This little hack lets you know how many rows were deleted* when running a "DELETE FROM table" query. Only implemented* if the DB_PORTABILITY_DELETE_COUNT portability option is on.** @param string $query the query string to modify** @return string the modified query string** @access protected* @see DB_common::setOption()*/function modifyQuery($query){if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {// "DELETE FROM table" gives 0 affected rows in MySQL.// This little hack lets you know how many rows were deleted.if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {$query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/','DELETE FROM \1 WHERE 1=1', $query);}}return $query;}// }}}// {{{ modifyLimitQuery()/*** Adds LIMIT clauses to a query string according to current DBMS standards** @param string $query the query to modify* @param int $from the row to start to fetching (0 = the first row)* @param int $count the numbers of rows to fetch* @param mixed $params array, string or numeric data to be used in* execution of the statement. Quantity of items* passed must match quantity of placeholders in* query: meaning 1 placeholder for non-array* parameters or 1 placeholder per array element.** @return string the query string with LIMIT clauses added** @access protected*/function modifyLimitQuery($query, $from, $count, $params = array()){if (DB::isManip($query)) {return $query . " LIMIT $count";} else {return $query . " LIMIT $from, $count";}}// }}}// {{{ mysqlRaiseError()/*** Produces a DB_Error object regarding the current problem** @param int $errno if the error is being manually raised pass a* DB_ERROR* constant here. If this isn't passed* the error information gathered from the DBMS.** @return object the DB_Error object** @see DB_common::raiseError(),* DB_mysql::errorNative(), DB_common::errorCode()*/function mysqlRaiseError($errno = null){if ($errno === null) {if ($this->options['portability'] & DB_PORTABILITY_ERRORS) {$this->errorcode_map[1022] = DB_ERROR_CONSTRAINT;$this->errorcode_map[1048] = DB_ERROR_CONSTRAINT_NOT_NULL;$this->errorcode_map[1062] = DB_ERROR_CONSTRAINT;} else {// Doing this in case mode changes during runtime.$this->errorcode_map[1022] = DB_ERROR_ALREADY_EXISTS;$this->errorcode_map[1048] = DB_ERROR_CONSTRAINT;$this->errorcode_map[1062] = DB_ERROR_ALREADY_EXISTS;}$errno = $this->errorCode(mysql_errno($this->connection));}return $this->raiseError($errno, null, null, null,@mysql_errno($this->connection) . ' ** ' .@mysql_error($this->connection));}// }}}// {{{ errorNative()/*** Gets the DBMS' native error code produced by the last query** @return int the DBMS' error code*/function errorNative(){return @mysql_errno($this->connection);}// }}}// {{{ tableInfo()/*** Returns information about a table or a result set** @param object|string $result DB_result object from a query or a* string containing the name of a table.* While this also accepts a query result* resource identifier, this behavior is* deprecated.* @param int $mode a valid tableInfo mode** @return array an associative array with the information requested.* A DB_Error object on failure.** @see DB_common::tableInfo()*/function tableInfo($result, $mode = null){if (is_string($result)) {/** Probably received a table name.* Create a result resource identifier.*/$id = @mysql_list_fields($this->dsn['database'],$result, $this->connection);$got_string = true;} elseif (isset($result->result)) {/** Probably received a result object.* Extract the result resource identifier.*/$id = $result->result;$got_string = false;} else {/** Probably received a result resource identifier.* Copy it.* Deprecated. Here for compatibility only.*/$id = $result;$got_string = false;}if (!is_resource($id)) {return $this->mysqlRaiseError(DB_ERROR_NEED_MORE_DATA);}if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {$case_func = 'strtolower';} else {$case_func = 'strval';}$count = @mysql_num_fields($id);$res = array();if ($mode) {$res['num_fields'] = $count;}for ($i = 0; $i < $count; $i++) {$res[$i] = array('table' => $case_func(@mysql_field_table($id, $i)),'name' => $case_func(@mysql_field_name($id, $i)),'type' => @mysql_field_type($id, $i),'len' => @mysql_field_len($id, $i),'flags' => @mysql_field_flags($id, $i),);if ($mode & DB_TABLEINFO_ORDER) {$res['order'][$res[$i]['name']] = $i;}if ($mode & DB_TABLEINFO_ORDERTABLE) {$res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;}}// free the result only if we were called on a tableif ($got_string) {@mysql_free_result($id);}return $res;}// }}}// {{{ getSpecialQuery()/*** Obtains the query string needed for listing a given type of objects** @param string $type the kind of objects you want to retrieve** @return string the SQL query string or null if the driver doesn't* support the object type requested** @access protected* @see DB_common::getListOf()*/function getSpecialQuery($type){switch ($type) {case 'tables':return 'SHOW TABLES';case 'users':return 'SELECT DISTINCT User FROM mysql.user';case 'databases':return 'SHOW DATABASES';default:return null;}}// }}}}/** Local variables:* tab-width: 4* c-basic-offset: 4* End:*/?>