2 /* vim: set expandtab tabstop=4 shiftwidth=4 foldmethod=marker: */
3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2004 The PHP Group |
7 // +----------------------------------------------------------------------+
8 // | This source file is subject to version 2.02 of the PHP license, |
9 // | that is bundled with this package in the file LICENSE, and is |
10 // | available at through the world-wide-web at |
11 // | http://www.php.net/license/2_02.txt. |
12 // | If you did not receive a copy of the PHP license and are unable to |
13 // | obtain it through the world-wide-web, please send a note to |
14 // | license@php.net so we can mail you a copy immediately. |
15 // +----------------------------------------------------------------------+
16 // | Author: Chaillan Nicolas <nicos@php.net> |
17 // | Based on mysql.php by Stig Bakken <ssb@php.net> |
18 // | Maintainer: Daniel Convissor <danielc@php.net> |
19 // +----------------------------------------------------------------------+
24 // NOTE: The tableInfo() method must be redone because the functions it
25 // relies on no longer exist in the new extension.
30 require_once 'DB/common.php';
33 * Database independent query interface definition for PHP's mysqli
36 * This is for MySQL versions 4.1 and above. Requires PHP 5.
38 * Note that persistent connections no longer exist.
43 * @author Chaillan Nicolas <nicos@php.net>
45 class DB_mysqli extends DB_common
50 var $phptype, $dbsyntax;
51 var $prepare_tokens = array();
52 var $prepare_types = array();
53 var $num_rows = array();
54 var $transaction_opcount = 0;
55 var $autocommit = true;
56 var $fetchmode = DB_FETCHMODE_ORDERED; /* Default fetch mode */
63 * DB_mysql constructor.
70 $this->phptype = 'mysqli';
71 $this->dbsyntax = 'mysqli';
72 $this->features = array(
75 'transactions' => true,
78 $this->errorcode_map = array(
79 1004 => DB_ERROR_CANNOT_CREATE,
80 1005 => DB_ERROR_CANNOT_CREATE,
81 1006 => DB_ERROR_CANNOT_CREATE,
82 1007 => DB_ERROR_ALREADY_EXISTS,
83 1008 => DB_ERROR_CANNOT_DROP,
84 1022 => DB_ERROR_ALREADY_EXISTS,
85 1046 => DB_ERROR_NODBSELECTED,
86 1050 => DB_ERROR_ALREADY_EXISTS,
87 1051 => DB_ERROR_NOSUCHTABLE,
88 1054 => DB_ERROR_NOSUCHFIELD,
89 1062 => DB_ERROR_ALREADY_EXISTS,
90 1064 => DB_ERROR_SYNTAX,
91 1100 => DB_ERROR_NOT_LOCKED,
92 1136 => DB_ERROR_VALUE_COUNT_ON_ROW,
93 1146 => DB_ERROR_NOSUCHTABLE,
94 1048 => DB_ERROR_CONSTRAINT,
95 1216 => DB_ERROR_CONSTRAINT,
103 * Connect to a database and log in as the specified user.
105 * @param string $dsn the data source name (see DB::parseDSN for syntax)
106 * @param boolean $persistent (optional) whether the connection should
108 * @return mixed DB_OK on success, a DB error on failure
111 function connect($dsninfo, $persistent = false)
113 if (!DB::assertExtension('mysqli')) {
114 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
117 $this->dsn = $dsninfo;
118 if ($dsninfo['protocol'] && $dsninfo['protocol'] == 'unix') {
119 $dbhost = ':' . $dsninfo['socket'];
121 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
122 if ($dsninfo['port']) {
123 $dbhost .= ':' . $dsninfo['port'];
127 $ssl_mode = $this->getOption('ssl') === true ? 'CLIENT_SSL' : NULL;
129 @ini_set('track_errors', true);
131 if ($dbhost && $dsninfo['username'] && $dsninfo['password']) {
132 // Need to verify if arguments are okay
133 $conn = @mysqli_connect($dbhost, $dsninfo['username'],
134 $dsninfo['password'], $ssl_mode);
135 } elseif ($dbhost && isset($dsninfo['username'])) {
136 $conn = @mysqli_connect($dbhost, $dsninfo['username'], null,
139 $conn = @mysqli_connect($dbhost, null, null, $ssl_mode);
144 @ini_restore('track_errors');
147 if (($err = @mysqli_error()) != '') {
148 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
150 } elseif (empty($php_errormsg)) {
151 return $this->raiseError(DB_ERROR_CONNECT_FAILED);
153 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
154 null, $php_errormsg);
158 if ($dsninfo['database']) {
159 if (!@mysqli_select_db($dsninfo['database'], $conn)) {
160 switch(mysqli_errno($conn)) {
162 return $this->raiseError(DB_ERROR_NOSUCHDB, null, null,
163 null, @mysqli_error($conn));
165 return $this->raiseError(DB_ERROR_ACCESS_VIOLATION, null, null,
166 null, @mysqli_error($conn));
168 return $this->raiseError(DB_ERROR, null, null,
169 null, @mysqli_error($conn));
172 // fix to allow calls to different databases in the same script
173 $this->_db = $dsninfo['database'];
176 $this->connection = $conn;
184 * Log out and disconnect from the database.
186 * @return boolean true on success, false if not connected
189 function disconnect()
191 $ret = @mysqli_close($this->connection);
192 $this->connection = null;
200 * Send a query to MySQL and return the results as a MySQL resource
203 * @param string $query the SQL query
204 * @return mixed a valid MySQL result for successful SELECT
205 * queries, DB_OK for other successful queries.
206 * A DB error is returned on failure.
209 function simpleQuery($query)
211 $ismanip = DB::isManip($query);
212 $this->last_query = $query;
213 $query = $this->modifyQuery($query);
215 if (!@mysqli_select_db($this->_db, $this->connection)) {
216 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
219 if (!$this->autocommit && $ismanip) {
220 if ($this->transaction_opcount == 0) {
221 $result = @mysqli_query('SET AUTOCOMMIT=0', $this->connection);
222 $result = @mysqli_query('BEGIN', $this->connection);
224 return $this->mysqlRaiseError();
227 $this->transaction_opcount++;
229 $result = @mysqli_query($query, $this->connection);
231 return $this->mysqlRaiseError();
233 if (is_resource($result)) {
234 $numrows = $this->numrows($result);
235 if (is_object($numrows)) {
238 $this->num_rows[(int)$result] = $numrows;
248 * Move the internal mysql result pointer to the next available result.
250 * This method has not been implemented yet.
252 * @param resource $result a valid sql result resource
256 function nextResult($result)
265 * Fetch a row and insert the data into an existing array.
267 * Formating of the array and the data therein are configurable.
268 * See DB_result::fetchInto() for more information.
270 * @param resource $result query result identifier
271 * @param array $arr (reference) array where data from the row
273 * @param int $fetchmode how the resulting array should be indexed
274 * @param int $rownum the row number to fetch
276 * @return mixed DB_OK on success, null when end of result set is
277 * reached or on failure
279 * @see DB_result::fetchInto()
282 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
284 if ($rownum !== null) {
285 if (!@mysqli_data_seek($result, $rownum)) {
289 if ($fetchmode & DB_FETCHMODE_ASSOC) {
290 $arr = @mysqli_fetch_array($result, MYSQLI_ASSOC);
291 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
292 $arr = array_change_key_case($arr, CASE_LOWER);
295 $arr = @mysqli_fetch_row($result);
298 $errno = @mysqli_errno($this->connection);
302 return $this->mysqlRaiseError($errno);
304 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
306 * Even though this DBMS already trims output, we do this because
307 * a field might have intentional whitespace at the end that
308 * gets removed by DB_PORTABILITY_RTRIM under another driver.
310 $this->_rtrimArrayValues($arr);
312 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
313 $this->_convertNullArrayValuesToEmpty($arr);
322 * Free the internal resources associated with $result.
324 * @param resource $result MySQL result identifier
325 * @return bool true on success, false if $result is invalid
328 function freeResult($result)
330 unset($this->num_rows[(int)$result]);
331 return @mysqli_free_result($result);
338 * Get the number of columns in a result set.
340 * @param $result MySQL result identifier
344 * @return int the number of columns per row in $result
346 function numCols($result)
348 $cols = @mysqli_num_fields($result);
351 return $this->mysqlRaiseError();
361 * Get the number of rows in a result set.
363 * @param resource $result MySQL result identifier
364 * @return int the number of rows in $result
367 function numRows($result)
369 $rows = @mysqli_num_rows($result);
370 if ($rows === null) {
371 return $this->mysqlRaiseError();
380 * Enable/disable automatic commits.
382 function autoCommit($onoff = false)
384 // XXX if $this->transaction_opcount > 0, we should probably
385 // issue a warning here.
386 $this->autocommit = $onoff ? true : false;
394 * Commit the current transaction.
398 if ($this->transaction_opcount > 0) {
400 if (!@mysqli_select_db($this->_db, $this->connection)) {
401 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
404 $result = @mysqli_query('COMMIT', $this->connection);
405 $result = @mysqli_query('SET AUTOCOMMIT=1', $this->connection);
406 $this->transaction_opcount = 0;
408 return $this->mysqlRaiseError();
418 * Roll back (undo) the current transaction.
422 if ($this->transaction_opcount > 0) {
424 if (!@mysqli_select_db($this->_db, $this->connection)) {
425 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
428 $result = @mysqli_query('ROLLBACK', $this->connection);
429 $result = @mysqli_query('SET AUTOCOMMIT=1', $this->connection);
430 $this->transaction_opcount = 0;
432 return $this->mysqlRaiseError();
439 // {{{ affectedRows()
442 * Gets the number of rows affected by the data manipulation
443 * query. For other queries, this function returns 0.
445 * @return integer number of rows affected by the last query
447 function affectedRows()
449 if (DB::isManip($this->last_query)) {
450 return @mysqli_affected_rows($this->connection);
460 * Get the native error code of the last error (if any) that
461 * occured on the current connection.
463 * @return int native MySQL error code
466 function errorNative()
468 return @mysqli_errno($this->connection);
475 * Returns the next free id in a sequence
477 * @param string $seq_name name of the sequence
478 * @param boolean $ondemand when true, the seqence is automatically
479 * created if it does not exist
481 * @return int the next id number in the sequence. DB_Error if problem.
484 * @see DB_common::nextID()
487 function nextId($seq_name, $ondemand = true)
489 $seqname = $this->getSequenceName($seq_name);
492 $this->pushErrorHandling(PEAR_ERROR_RETURN);
493 $result = $this->query("UPDATE ${seqname} ".
494 'SET id=LAST_INSERT_ID(id+1)');
495 $this->popErrorHandling();
496 if ($result == DB_OK) {
498 $id = @mysqli_insert_id($this->connection);
502 /** EMPTY SEQ TABLE **/
503 // Sequence table must be empty for some reason, so fill it and return 1
504 // Obtain a user-level lock
505 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
506 if (DB::isError($result)) {
507 return $this->raiseError($result);
510 // Failed to get the lock, bail with a DB_ERROR_NOT_LOCKED error
511 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
514 // add the default value
515 $result = $this->query("REPLACE INTO ${seqname} VALUES (0)");
516 if (DB::isError($result)) {
517 return $this->raiseError($result);
521 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
522 if (DB::isError($result)) {
523 return $this->raiseError($result);
525 // We know what the result will be, so no need to try again
528 /** ONDEMAND TABLE CREATION **/
529 } elseif ($ondemand && DB::isError($result) &&
530 $result->getCode() == DB_ERROR_NOSUCHTABLE)
532 $result = $this->createSequence($seq_name);
533 // Since createSequence initializes the ID to be 1,
534 // we do not need to retrieve the ID again (or we will get 2)
535 if (DB::isError($result)) {
536 return $this->raiseError($result);
538 // First ID of a newly created sequence is 1
542 /** BACKWARDS COMPAT **/
543 } elseif (DB::isError($result) &&
544 $result->getCode() == DB_ERROR_ALREADY_EXISTS)
546 // see _BCsequence() comment
547 $result = $this->_BCsequence($seqname);
548 if (DB::isError($result)) {
549 return $this->raiseError($result);
555 return $this->raiseError($result);
559 * Creates a new sequence
561 * @param string $seq_name name of the new sequence
563 * @return int DB_OK on success. A DB_Error object is returned if
567 * @see DB_common::createSequence()
570 function createSequence($seq_name)
572 $seqname = $this->getSequenceName($seq_name);
573 $res = $this->query("CREATE TABLE ${seqname} ".
574 '(id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'.
575 ' PRIMARY KEY(id))');
576 if (DB::isError($res)) {
579 // insert yields value 1, nextId call will generate ID 2
580 return $this->query("INSERT INTO ${seqname} VALUES(0)");
584 // {{{ dropSequence()
589 * @param string $seq_name name of the sequence to be deleted
591 * @return int DB_OK on success. DB_Error if problems.
594 * @see DB_common::dropSequence()
597 function dropSequence($seq_name)
599 return $this->query('DROP TABLE ' . $this->getSequenceName($seq_name));
606 * Backwards compatibility with old sequence emulation implementation
607 * (clean up the dupes).
609 * @param string $seqname The sequence name to clean up
610 * @return mixed DB_Error or true
612 function _BCsequence($seqname)
614 // Obtain a user-level lock... this will release any previous
615 // application locks, but unlike LOCK TABLES, it does not abort
616 // the current transaction and is much less frequently used.
617 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
618 if (DB::isError($result)) {
622 // Failed to get the lock, can't do the conversion, bail
623 // with a DB_ERROR_NOT_LOCKED error
624 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
627 $highest_id = $this->getOne("SELECT MAX(id) FROM ${seqname}");
628 if (DB::isError($highest_id)) {
631 // This should kill all rows except the highest
632 // We should probably do something if $highest_id isn't
633 // numeric, but I'm at a loss as how to handle that...
634 $result = $this->query("DELETE FROM ${seqname} WHERE id <> $highest_id");
635 if (DB::isError($result)) {
639 // If another thread has been waiting for this lock,
640 // it will go thru the above procedure, but will have no
642 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
643 if (DB::isError($result)) {
650 // {{{ quoteIdentifier()
653 * Quote a string so it can be safely used as a table or column name
655 * Quoting style depends on which database driver is being used.
657 * MySQL can't handle the backtick character (<kbd>`</kbd>) in
658 * table or column names.
660 * @param string $str identifier name to be quoted
662 * @return string quoted identifier string
668 function quoteIdentifier($str)
670 return '`' . $str . '`';
674 // {{{ escapeSimple()
677 * Escape a string according to the current DBMS's standards
679 * @param string $str the string to be escaped
681 * @return string the escaped string
685 function escapeSimple($str) {
686 return @mysqli_real_escape_string($str, $this->connection);
692 function modifyQuery($query)
694 if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {
695 // "DELETE FROM table" gives 0 affected rows in MySQL.
696 // This little hack lets you know how many rows were deleted.
697 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
698 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
699 'DELETE FROM \1 WHERE 1=1', $query);
706 // {{{ modifyLimitQuery()
708 function modifyLimitQuery($query, $from, $count)
710 if (DB::isManip($query)) {
711 return $query . " LIMIT $count";
713 return $query . " LIMIT $from, $count";
718 // {{{ mysqlRaiseError()
721 * Gather information about an error, then use that info to create a
722 * DB error object and finally return that object.
724 * @param integer $errno PEAR error number (usually a DB constant) if
725 * manually raising an error
726 * @return object DB error object
727 * @see DB_common::errorCode()
728 * @see DB_common::raiseError()
730 function mysqlRaiseError($errno = null)
732 if ($errno === null) {
733 if ($this->options['portability'] & DB_PORTABILITY_ERRORS) {
734 $this->errorcode_map[1022] = DB_ERROR_CONSTRAINT;
735 $this->errorcode_map[1048] = DB_ERROR_CONSTRAINT_NOT_NULL;
736 $this->errorcode_map[1062] = DB_ERROR_CONSTRAINT;
738 // Doing this in case mode changes during runtime.
739 $this->errorcode_map[1022] = DB_ERROR_ALREADY_EXISTS;
740 $this->errorcode_map[1048] = DB_ERROR_CONSTRAINT;
741 $this->errorcode_map[1062] = DB_ERROR_ALREADY_EXISTS;
743 $errno = $this->errorCode(mysqli_errno($this->connection));
745 return $this->raiseError($errno, null, null, null,
746 @mysqli_errno($this->connection) . ' ** ' .
747 @mysqli_error($this->connection));
754 * Returns information about a table or a result set.
756 * WARNING: this method will probably not work because the mysqli_*()
757 * functions it relies upon may not exist.
759 * @param object|string $result DB_result object from a query or a
760 * string containing the name of a table
761 * @param int $mode a valid tableInfo mode
762 * @return array an associative array with the information requested
763 * or an error object if something is wrong
766 * @see DB_common::tableInfo()
768 function tableInfo($result, $mode = null) {
769 if (isset($result->result)) {
771 * Probably received a result object.
772 * Extract the result resource identifier.
774 $id = $result->result;
776 } elseif (is_string($result)) {
778 * Probably received a table name.
779 * Create a result resource identifier.
781 $id = @mysqli_list_fields($this->dsn['database'],
782 $result, $this->connection);
786 * Probably received a result resource identifier.
788 * Depricated. Here for compatibility only.
794 if (!is_resource($id)) {
795 return $this->mysqlRaiseError(DB_ERROR_NEED_MORE_DATA);
798 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
799 $case_func = 'strtolower';
801 $case_func = 'strval';
804 $count = @mysqli_num_fields($id);
806 // made this IF due to performance (one if is faster than $count if's)
808 for ($i=0; $i<$count; $i++) {
809 $res[$i]['table'] = $case_func(@mysqli_field_table($id, $i));
810 $res[$i]['name'] = $case_func(@mysqli_field_name($id, $i));
811 $res[$i]['type'] = @mysqli_field_type($id, $i);
812 $res[$i]['len'] = @mysqli_field_len($id, $i);
813 $res[$i]['flags'] = @mysqli_field_flags($id, $i);
816 $res['num_fields']= $count;
818 for ($i=0; $i<$count; $i++) {
819 $res[$i]['table'] = $case_func(@mysqli_field_table($id, $i));
820 $res[$i]['name'] = $case_func(@mysqli_field_name($id, $i));
821 $res[$i]['type'] = @mysqli_field_type($id, $i);
822 $res[$i]['len'] = @mysqli_field_len($id, $i);
823 $res[$i]['flags'] = @mysqli_field_flags($id, $i);
825 if ($mode & DB_TABLEINFO_ORDER) {
826 $res['order'][$res[$i]['name']] = $i;
828 if ($mode & DB_TABLEINFO_ORDERTABLE) {
829 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
834 // free the result only if we were called on a table
836 @mysqli_free_result($id);
842 // {{{ getSpecialQuery()
845 * Returns the query needed to get some backend info.
847 * @param string $type What kind of info you want to retrieve
848 * @return string The SQL query string
850 function getSpecialQuery($type)
854 return 'SHOW TABLES';
856 return DB_ERROR_NOT_CAPABLE;
858 $sql = 'select distinct User from user';
859 if ($this->dsn['database'] != 'mysql') {
861 $dsn['database'] = 'mysql';
862 if (DB::isError($db = DB::connect($dsn))) {
865 $sql = $db->getCol($sql);
867 // XXX Fixme the mysql driver should take care of this
868 if (!@mysqli_select_db($this->dsn['database'], $this->connection)) {
869 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
874 return 'SHOW DATABASES';