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 // +----------------------------------------------------------------------+
23 // NOTE: The tableInfo() method must be redone because the functions it
24 // relies on no longer exist in the new extension.
28 require_once 'DB/common.php';
31 * Database independent query interface definition for PHP's mysqli
34 * This is for MySQL versions 4.1 and above. Requires PHP 5.
36 * Note that persistent connections no longer exist.
41 * @author Chaillan Nicolas <nicos@php.net>
43 class DB_mysqli extends DB_common
48 var $phptype, $dbsyntax;
49 var $prepare_tokens = array();
50 var $prepare_types = array();
51 var $num_rows = array();
52 var $transaction_opcount = 0;
53 var $autocommit = true;
54 var $fetchmode = DB_FETCHMODE_ORDERED; /* Default fetch mode */
61 * DB_mysql constructor.
68 $this->phptype = 'mysqli';
69 $this->dbsyntax = 'mysqli';
70 $this->features = array(
73 'transactions' => true,
76 $this->errorcode_map = array(
77 1004 => DB_ERROR_CANNOT_CREATE,
78 1005 => DB_ERROR_CANNOT_CREATE,
79 1006 => DB_ERROR_CANNOT_CREATE,
80 1007 => DB_ERROR_ALREADY_EXISTS,
81 1008 => DB_ERROR_CANNOT_DROP,
82 1022 => DB_ERROR_ALREADY_EXISTS,
83 1046 => DB_ERROR_NODBSELECTED,
84 1050 => DB_ERROR_ALREADY_EXISTS,
85 1051 => DB_ERROR_NOSUCHTABLE,
86 1054 => DB_ERROR_NOSUCHFIELD,
87 1062 => DB_ERROR_ALREADY_EXISTS,
88 1064 => DB_ERROR_SYNTAX,
89 1100 => DB_ERROR_NOT_LOCKED,
90 1136 => DB_ERROR_VALUE_COUNT_ON_ROW,
91 1146 => DB_ERROR_NOSUCHTABLE,
92 1048 => DB_ERROR_CONSTRAINT,
93 1216 => DB_ERROR_CONSTRAINT,
101 * Connect to a database and log in as the specified user.
103 * @param string $dsn the data source name (see DB::parseDSN for syntax)
104 * @param boolean $persistent (optional) whether the connection should
106 * @return mixed DB_OK on success, a DB error on failure
109 function connect($dsninfo, $persistent = false)
111 if (!DB::assertExtension('mysqli')) {
112 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
115 $this->dsn = $dsninfo;
116 if ($dsninfo['protocol'] && $dsninfo['protocol'] == 'unix') {
117 $dbhost = ':' . $dsninfo['socket'];
119 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
120 if ($dsninfo['port']) {
121 $dbhost .= ':' . $dsninfo['port'];
125 $ssl_mode = $this->getOption('ssl') === true ? 'CLIENT_SSL' : NULL;
127 @ini_set('track_errors', true);
129 if ($dbhost && $dsninfo['username'] && $dsninfo['password']) {
130 // Need to verify if arguments are okay
131 $conn = @mysqli_connect($dbhost, $dsninfo['username'],
132 $dsninfo['password'], $ssl_mode);
133 } elseif ($dbhost && isset($dsninfo['username'])) {
134 $conn = @mysqli_connect($dbhost, $dsninfo['username'], null,
137 $conn = @mysqli_connect($dbhost, null, null, $ssl_mode);
142 @ini_restore('track_errors');
145 if (($err = @mysqli_error()) != '') {
146 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
148 } elseif (empty($php_errormsg)) {
149 return $this->raiseError(DB_ERROR_CONNECT_FAILED);
151 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
152 null, $php_errormsg);
156 if ($dsninfo['database']) {
157 if (!@mysqli_select_db($dsninfo['database'], $conn)) {
158 switch(mysqli_errno($conn)) {
160 return $this->raiseError(DB_ERROR_NOSUCHDB, null, null,
161 null, @mysqli_error($conn));
163 return $this->raiseError(DB_ERROR_ACCESS_VIOLATION, null, null,
164 null, @mysqli_error($conn));
166 return $this->raiseError(DB_ERROR, null, null,
167 null, @mysqli_error($conn));
170 // fix to allow calls to different databases in the same script
171 $this->_db = $dsninfo['database'];
174 $this->connection = $conn;
182 * Log out and disconnect from the database.
184 * @return boolean true on success, false if not connected
187 function disconnect()
189 $ret = @mysqli_close($this->connection);
190 $this->connection = null;
198 * Send a query to MySQL and return the results as a MySQL resource
201 * @param string $query the SQL query
202 * @return mixed a valid MySQL result for successful SELECT
203 * queries, DB_OK for other successful queries.
204 * A DB error is returned on failure.
207 function simpleQuery($query)
209 $ismanip = DB::isManip($query);
210 $this->last_query = $query;
211 $query = $this->modifyQuery($query);
213 if (!@mysqli_select_db($this->_db, $this->connection)) {
214 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
217 if (!$this->autocommit && $ismanip) {
218 if ($this->transaction_opcount == 0) {
219 $result = @mysqli_query('SET AUTOCOMMIT=0', $this->connection);
220 $result = @mysqli_query('BEGIN', $this->connection);
222 return $this->mysqlRaiseError();
225 $this->transaction_opcount++;
227 $result = @mysqli_query($query, $this->connection);
229 return $this->mysqlRaiseError();
231 if (is_resource($result)) {
232 $numrows = $this->numrows($result);
233 if (is_object($numrows)) {
236 $this->num_rows[(int)$result] = $numrows;
246 * Move the internal mysql result pointer to the next available result.
248 * This method has not been implemented yet.
250 * @param resource $result a valid sql result resource
254 function nextResult($result)
263 * Fetch a row and insert the data into an existing array.
265 * Formating of the array and the data therein are configurable.
266 * See DB_result::fetchInto() for more information.
268 * @param resource $result query result identifier
269 * @param array $arr (reference) array where data from the row
271 * @param int $fetchmode how the resulting array should be indexed
272 * @param int $rownum the row number to fetch
274 * @return mixed DB_OK on success, null when end of result set is
275 * reached or on failure
277 * @see DB_result::fetchInto()
280 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
282 if ($rownum !== null) {
283 if (!@mysqli_data_seek($result, $rownum)) {
287 if ($fetchmode & DB_FETCHMODE_ASSOC) {
288 $arr = @mysqli_fetch_array($result, MYSQLI_ASSOC);
289 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
290 $arr = array_change_key_case($arr, CASE_LOWER);
293 $arr = @mysqli_fetch_row($result);
296 $errno = @mysqli_errno($this->connection);
300 return $this->mysqlRaiseError($errno);
302 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
304 * Even though this DBMS already trims output, we do this because
305 * a field might have intentional whitespace at the end that
306 * gets removed by DB_PORTABILITY_RTRIM under another driver.
308 $this->_rtrimArrayValues($arr);
310 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
311 $this->_convertNullArrayValuesToEmpty($arr);
320 * Free the internal resources associated with $result.
322 * @param resource $result MySQL result identifier
323 * @return bool true on success, false if $result is invalid
326 function freeResult($result)
328 unset($this->num_rows[(int)$result]);
329 return @mysqli_free_result($result);
336 * Get the number of columns in a result set.
338 * @param $result MySQL result identifier
342 * @return int the number of columns per row in $result
344 function numCols($result)
346 $cols = @mysqli_num_fields($result);
349 return $this->mysqlRaiseError();
359 * Get the number of rows in a result set.
361 * @param resource $result MySQL result identifier
362 * @return int the number of rows in $result
365 function numRows($result)
367 $rows = @mysqli_num_rows($result);
368 if ($rows === null) {
369 return $this->mysqlRaiseError();
378 * Enable/disable automatic commits.
380 function autoCommit($onoff = false)
382 // XXX if $this->transaction_opcount > 0, we should probably
383 // issue a warning here.
384 $this->autocommit = $onoff ? true : false;
392 * Commit the current transaction.
396 if ($this->transaction_opcount > 0) {
398 if (!@mysqli_select_db($this->_db, $this->connection)) {
399 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
402 $result = @mysqli_query('COMMIT', $this->connection);
403 $result = @mysqli_query('SET AUTOCOMMIT=1', $this->connection);
404 $this->transaction_opcount = 0;
406 return $this->mysqlRaiseError();
416 * Roll back (undo) the current transaction.
420 if ($this->transaction_opcount > 0) {
422 if (!@mysqli_select_db($this->_db, $this->connection)) {
423 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
426 $result = @mysqli_query('ROLLBACK', $this->connection);
427 $result = @mysqli_query('SET AUTOCOMMIT=1', $this->connection);
428 $this->transaction_opcount = 0;
430 return $this->mysqlRaiseError();
437 // {{{ affectedRows()
440 * Gets the number of rows affected by the data manipulation
441 * query. For other queries, this function returns 0.
443 * @return integer number of rows affected by the last query
445 function affectedRows()
447 if (DB::isManip($this->last_query)) {
448 return @mysqli_affected_rows($this->connection);
458 * Get the native error code of the last error (if any) that
459 * occured on the current connection.
461 * @return int native MySQL error code
464 function errorNative()
466 return @mysqli_errno($this->connection);
473 * Returns the next free id in a sequence
475 * @param string $seq_name name of the sequence
476 * @param boolean $ondemand when true, the seqence is automatically
477 * created if it does not exist
479 * @return int the next id number in the sequence. DB_Error if problem.
482 * @see DB_common::nextID()
485 function nextId($seq_name, $ondemand = true)
487 $seqname = $this->getSequenceName($seq_name);
490 $this->pushErrorHandling(PEAR_ERROR_RETURN);
491 $result = $this->query("UPDATE ${seqname} ".
492 'SET id=LAST_INSERT_ID(id+1)');
493 $this->popErrorHandling();
494 if ($result == DB_OK) {
496 $id = @mysqli_insert_id($this->connection);
500 /** EMPTY SEQ TABLE **/
501 // Sequence table must be empty for some reason, so fill it and return 1
502 // Obtain a user-level lock
503 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
504 if (DB::isError($result)) {
505 return $this->raiseError($result);
508 // Failed to get the lock, bail with a DB_ERROR_NOT_LOCKED error
509 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
512 // add the default value
513 $result = $this->query("REPLACE INTO ${seqname} VALUES (0)");
514 if (DB::isError($result)) {
515 return $this->raiseError($result);
519 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
520 if (DB::isError($result)) {
521 return $this->raiseError($result);
523 // We know what the result will be, so no need to try again
526 /** ONDEMAND TABLE CREATION **/
527 } elseif ($ondemand && DB::isError($result) &&
528 $result->getCode() == DB_ERROR_NOSUCHTABLE)
530 $result = $this->createSequence($seq_name);
531 // Since createSequence initializes the ID to be 1,
532 // we do not need to retrieve the ID again (or we will get 2)
533 if (DB::isError($result)) {
534 return $this->raiseError($result);
536 // First ID of a newly created sequence is 1
540 /** BACKWARDS COMPAT **/
541 } elseif (DB::isError($result) &&
542 $result->getCode() == DB_ERROR_ALREADY_EXISTS)
544 // see _BCsequence() comment
545 $result = $this->_BCsequence($seqname);
546 if (DB::isError($result)) {
547 return $this->raiseError($result);
553 return $this->raiseError($result);
557 * Creates a new sequence
559 * @param string $seq_name name of the new sequence
561 * @return int DB_OK on success. A DB_Error object is returned if
565 * @see DB_common::createSequence()
568 function createSequence($seq_name)
570 $seqname = $this->getSequenceName($seq_name);
571 $res = $this->query("CREATE TABLE ${seqname} ".
572 '(id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'.
573 ' PRIMARY KEY(id))');
574 if (DB::isError($res)) {
577 // insert yields value 1, nextId call will generate ID 2
578 return $this->query("INSERT INTO ${seqname} VALUES(0)");
582 // {{{ dropSequence()
587 * @param string $seq_name name of the sequence to be deleted
589 * @return int DB_OK on success. DB_Error if problems.
592 * @see DB_common::dropSequence()
595 function dropSequence($seq_name)
597 return $this->query('DROP TABLE ' . $this->getSequenceName($seq_name));
604 * Backwards compatibility with old sequence emulation implementation
605 * (clean up the dupes).
607 * @param string $seqname The sequence name to clean up
608 * @return mixed DB_Error or true
610 function _BCsequence($seqname)
612 // Obtain a user-level lock... this will release any previous
613 // application locks, but unlike LOCK TABLES, it does not abort
614 // the current transaction and is much less frequently used.
615 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
616 if (DB::isError($result)) {
620 // Failed to get the lock, can't do the conversion, bail
621 // with a DB_ERROR_NOT_LOCKED error
622 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
625 $highest_id = $this->getOne("SELECT MAX(id) FROM ${seqname}");
626 if (DB::isError($highest_id)) {
629 // This should kill all rows except the highest
630 // We should probably do something if $highest_id isn't
631 // numeric, but I'm at a loss as how to handle that...
632 $result = $this->query("DELETE FROM ${seqname} WHERE id <> $highest_id");
633 if (DB::isError($result)) {
637 // If another thread has been waiting for this lock,
638 // it will go thru the above procedure, but will have no
640 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
641 if (DB::isError($result)) {
648 // {{{ quoteIdentifier()
651 * Quote a string so it can be safely used as a table or column name
653 * Quoting style depends on which database driver is being used.
655 * MySQL can't handle the backtick character (<kbd>`</kbd>) in
656 * table or column names.
658 * @param string $str identifier name to be quoted
660 * @return string quoted identifier string
666 function quoteIdentifier($str)
668 return '`' . $str . '`';
672 // {{{ escapeSimple()
675 * Escape a string according to the current DBMS's standards
677 * @param string $str the string to be escaped
679 * @return string the escaped string
683 function escapeSimple($str) {
684 return @mysqli_real_escape_string($str, $this->connection);
690 function modifyQuery($query)
692 if ($this->options['portability'] & DB_PORTABILITY_DELETE_COUNT) {
693 // "DELETE FROM table" gives 0 affected rows in MySQL.
694 // This little hack lets you know how many rows were deleted.
695 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
696 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
697 'DELETE FROM \1 WHERE 1=1', $query);
704 // {{{ modifyLimitQuery()
706 function modifyLimitQuery($query, $from, $count)
708 if (DB::isManip($query)) {
709 return $query . " LIMIT $count";
711 return $query . " LIMIT $from, $count";
716 // {{{ mysqlRaiseError()
719 * Gather information about an error, then use that info to create a
720 * DB error object and finally return that object.
722 * @param integer $errno PEAR error number (usually a DB constant) if
723 * manually raising an error
724 * @return object DB error object
725 * @see DB_common::errorCode()
726 * @see DB_common::raiseError()
728 function mysqlRaiseError($errno = null)
730 if ($errno === null) {
731 if ($this->options['portability'] & DB_PORTABILITY_ERRORS) {
732 $this->errorcode_map[1022] = DB_ERROR_CONSTRAINT;
733 $this->errorcode_map[1048] = DB_ERROR_CONSTRAINT_NOT_NULL;
734 $this->errorcode_map[1062] = DB_ERROR_CONSTRAINT;
736 // Doing this in case mode changes during runtime.
737 $this->errorcode_map[1022] = DB_ERROR_ALREADY_EXISTS;
738 $this->errorcode_map[1048] = DB_ERROR_CONSTRAINT;
739 $this->errorcode_map[1062] = DB_ERROR_ALREADY_EXISTS;
741 $errno = $this->errorCode(mysqli_errno($this->connection));
743 return $this->raiseError($errno, null, null, null,
744 @mysqli_errno($this->connection) . ' ** ' .
745 @mysqli_error($this->connection));
752 * Returns information about a table or a result set.
754 * WARNING: this method will probably not work because the mysqli_*()
755 * functions it relies upon may not exist.
757 * @param object|string $result DB_result object from a query or a
758 * string containing the name of a table
759 * @param int $mode a valid tableInfo mode
760 * @return array an associative array with the information requested
761 * or an error object if something is wrong
764 * @see DB_common::tableInfo()
766 function tableInfo($result, $mode = null) {
767 if (isset($result->result)) {
769 * Probably received a result object.
770 * Extract the result resource identifier.
772 $id = $result->result;
774 } elseif (is_string($result)) {
776 * Probably received a table name.
777 * Create a result resource identifier.
779 $id = @mysqli_list_fields($this->dsn['database'],
780 $result, $this->connection);
784 * Probably received a result resource identifier.
786 * Depricated. Here for compatibility only.
792 if (!is_resource($id)) {
793 return $this->mysqlRaiseError(DB_ERROR_NEED_MORE_DATA);
796 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
797 $case_func = 'strtolower';
799 $case_func = 'strval';
802 $count = @mysqli_num_fields($id);
804 // made this IF due to performance (one if is faster than $count if's)
806 for ($i=0; $i<$count; $i++) {
807 $res[$i]['table'] = $case_func(@mysqli_field_table($id, $i));
808 $res[$i]['name'] = $case_func(@mysqli_field_name($id, $i));
809 $res[$i]['type'] = @mysqli_field_type($id, $i);
810 $res[$i]['len'] = @mysqli_field_len($id, $i);
811 $res[$i]['flags'] = @mysqli_field_flags($id, $i);
814 $res['num_fields']= $count;
816 for ($i=0; $i<$count; $i++) {
817 $res[$i]['table'] = $case_func(@mysqli_field_table($id, $i));
818 $res[$i]['name'] = $case_func(@mysqli_field_name($id, $i));
819 $res[$i]['type'] = @mysqli_field_type($id, $i);
820 $res[$i]['len'] = @mysqli_field_len($id, $i);
821 $res[$i]['flags'] = @mysqli_field_flags($id, $i);
823 if ($mode & DB_TABLEINFO_ORDER) {
824 $res['order'][$res[$i]['name']] = $i;
826 if ($mode & DB_TABLEINFO_ORDERTABLE) {
827 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
832 // free the result only if we were called on a table
834 @mysqli_free_result($id);
840 // {{{ getSpecialQuery()
843 * Returns the query needed to get some backend info.
845 * @param string $type What kind of info you want to retrieve
846 * @return string The SQL query string
848 function getSpecialQuery($type)
852 return 'SHOW TABLES';
854 return DB_ERROR_NOT_CAPABLE;
856 $sql = 'select distinct User from user';
857 if ($this->dsn['database'] != 'mysql') {
859 $dsn['database'] = 'mysql';
860 if (DB::isError($db = DB::connect($dsn))) {
863 $sql = $db->getCol($sql);
865 // XXX Fixme the mysql driver should take care of this
866 if (!@mysqli_select_db($this->dsn['database'], $this->connection)) {
867 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
872 return 'SHOW DATABASES';