3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2002 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: Stig Bakken <ssb@fast.no> |
17 // +----------------------------------------------------------------------+
19 // Based on DB 1.3 from the pear.php.net repository.
20 // The only modifications made have been modification of the include paths.
22 rcs_id('$Id: mysql.php,v 1.2 2002-09-12 11:45:33 rurban Exp $');
23 rcs_id('From Pear CVS: Id: mysql.php,v 1.5 2002/06/19 00:41:06 cox Exp');
25 // Database independent query interface definition for PHP's MySQL
32 // XXX ERRORMSG: The error message from the mysql function should
33 // be registered here.
36 require_once "lib/pear/DB/common.php";
38 class DB_mysql extends DB_common
43 var $phptype, $dbsyntax;
44 var $prepare_tokens = array();
45 var $prepare_types = array();
46 var $num_rows = array();
47 var $transaction_opcount = 0;
48 var $autocommit = true;
49 var $fetchmode = DB_FETCHMODE_ORDERED; /* Default fetch mode */
56 * DB_mysql constructor.
64 $this->phptype = 'mysql';
65 $this->dbsyntax = 'mysql';
66 $this->features = array(
69 'transactions' => true,
72 $this->errorcode_map = array(
73 1004 => DB_ERROR_CANNOT_CREATE,
74 1005 => DB_ERROR_CANNOT_CREATE,
75 1006 => DB_ERROR_CANNOT_CREATE,
76 1007 => DB_ERROR_ALREADY_EXISTS,
77 1008 => DB_ERROR_CANNOT_DROP,
78 1046 => DB_ERROR_NODBSELECTED,
79 1050 => DB_ERROR_ALREADY_EXISTS,
80 1051 => DB_ERROR_NOSUCHTABLE,
81 1054 => DB_ERROR_NOSUCHFIELD,
82 1062 => DB_ERROR_ALREADY_EXISTS,
83 1064 => DB_ERROR_SYNTAX,
84 1100 => DB_ERROR_NOT_LOCKED,
85 1136 => DB_ERROR_VALUE_COUNT_ON_ROW,
86 1146 => DB_ERROR_NOSUCHTABLE,
87 1048 => DB_ERROR_CONSTRAINT,
96 * Connect to a database and log in as the specified user.
98 * @param $dsn the data source name (see DB::parseDSN for syntax)
99 * @param $persistent (optional) whether the connection should
102 * @return int DB_OK on success, a DB error on failure
105 function connect($dsninfo, $persistent = false)
107 if (!DB::assertExtension('mysql'))
108 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
110 $this->dsn = $dsninfo;
111 if (isset($dsninfo['protocol']) && $dsninfo['protocol'] == 'unix') {
112 $dbhost = ':' . $dsninfo['socket'];
114 $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
115 if (!empty($dsninfo['port'])) {
116 $dbhost .= ':' . $dsninfo['port'];
119 $user = $dsninfo['username'];
120 $pw = $dsninfo['password'];
122 $connect_function = $persistent ? 'mysql_pconnect' : 'mysql_connect';
124 @ini_set('track_errors', true);
125 if ($dbhost && $user && $pw) {
126 $conn = @$connect_function($dbhost, $user, $pw);
127 } elseif ($dbhost && $user) {
128 $conn = @$connect_function($dbhost, $user);
130 $conn = @$connect_function($dbhost);
134 @ini_restore('track_errors');
136 if (($err = @mysql_error()) != '') {
137 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
139 } elseif (empty($php_errormsg)) {
140 return $this->raiseError(DB_ERROR_CONNECT_FAILED);
142 return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
143 null, $php_errormsg);
147 if ($dsninfo['database']) {
148 if (!@mysql_select_db($dsninfo['database'], $conn)) {
149 switch(mysql_errno($conn)) {
151 return $this->raiseError(DB_ERROR_NOSUCHDB, null, null,
152 null, mysql_error($conn));
155 return $this->raiseError(DB_ERROR_ACCESS_VIOLATION, null, null,
156 null, mysql_error($conn));
159 return $this->raiseError(DB_ERROR, null, null,
160 null, mysql_error($conn));
165 // fix to allow calls to different databases in the same script
166 $this->_db = $dsninfo['database'];
169 $this->connection = $conn;
177 * Log out and disconnect from the database.
181 * @return bool TRUE on success, FALSE if not connected.
183 function disconnect()
185 $ret = mysql_close($this->connection);
186 $this->connection = null;
194 * Send a query to MySQL and return the results as a MySQL resource
197 * @param the SQL query
201 * @return mixed returns a valid MySQL result for successful SELECT
202 * queries, DB_OK for other successful queries. A DB error is
203 * returned on failure.
205 function simpleQuery($query)
207 $ismanip = DB::isManip($query);
208 $this->last_query = $query;
209 $query = $this->modifyQuery($query);
211 if (!@mysql_select_db($this->_db, $this->connection)) {
212 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
215 if (!$this->autocommit && $ismanip) {
216 if ($this->transaction_opcount == 0) {
217 $result = @mysql_query('SET AUTOCOMMIT=0', $this->connection);
218 $result = @mysql_query('BEGIN', $this->connection);
220 return $this->mysqlRaiseError();
223 $this->transaction_opcount++;
225 $result = @mysql_query($query, $this->connection);
227 return $this->mysqlRaiseError();
229 if (is_resource($result)) {
230 $numrows = $this->numrows($result);
231 if (is_object($numrows)) {
234 $this->num_rows[$result] = $numrows;
244 * Move the internal mysql result pointer to the next available result
246 * @param a valid fbsql result resource
250 * @return true if a result is available otherwise return false
252 function nextResult($result)
261 * Fetch and return a row of data (it uses fetchInto for that)
262 * @param $result MySQL result identifier
263 * @param $fetchmode format of fetched row array
264 * @param $rownum the absolute row number to fetch
266 * @return array a row of data, or false on error
268 function fetchRow($result, $fetchmode = DB_FETCHMODE_DEFAULT, $rownum=null)
270 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
271 $fetchmode = $this->fetchmode;
273 $res = $this->fetchInto ($result, $arr, $fetchmode, $rownum);
274 if ($res !== DB_OK) {
284 * Fetch a row and insert the data into an existing array.
286 * @param $result MySQL result identifier
287 * @param $arr (reference) array where data from the row is stored
288 * @param $fetchmode how the array data should be indexed
289 * @param $rownum the row number to fetch
292 * @return int DB_OK on success, a DB error on failure
294 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
296 if ($rownum !== null) {
297 if (!@mysql_data_seek($result, $rownum)) {
301 if ($fetchmode & DB_FETCHMODE_ASSOC) {
302 $arr = @mysql_fetch_array($result, MYSQL_ASSOC);
304 $arr = @mysql_fetch_row($result);
307 $errno = @mysql_errno($this->connection);
311 return $this->mysqlRaiseError($errno);
320 * Free the internal resources associated with $result.
322 * @param $result MySQL result identifier or DB statement identifier
326 * @return bool TRUE on success, FALSE if $result is invalid
328 function freeResult($result)
330 if (is_resource($result)) {
331 return mysql_free_result($result);
334 $result = (int)$result; // $result is a prepared query handle
335 if (!isset($this->prepare_tokens[$result])) {
339 // [ssb]: WTF? unset($this->prepare_types[$result]) makes PHP
340 // crash on my laptop (4.1.2 as well as 4.3.0-dev)
342 $copy = $this->prepare_types;
343 unset($copy[$result]);
344 $this->prepare_types = $copy;
345 // unset($this->prepare_types[$result]);
347 $copy = $this->prepare_tokens;
348 unset($copy[$result]);
349 $this->prepare_tokens = $copy;
350 // unset($this->prepare_tokens[$result]);
359 * Get the number of columns in a result set.
361 * @param $result MySQL result identifier
365 * @return int the number of columns per row in $result
367 function numCols($result)
369 $cols = @mysql_num_fields($result);
372 return $this->mysqlRaiseError();
382 * Get the number of rows in a result set.
384 * @param $result MySQL result identifier
388 * @return int the number of rows in $result
390 function numRows($result)
392 $rows = @mysql_num_rows($result);
393 if ($rows === null) {
394 return $this->mysqlRaiseError();
403 * Enable/disable automatic commits
405 function autoCommit($onoff = false)
407 // XXX if $this->transaction_opcount > 0, we should probably
408 // issue a warning here.
409 $this->autocommit = $onoff ? true : false;
417 * Commit the current transaction.
421 if ($this->transaction_opcount > 0) {
423 if (!@mysql_select_db($this->_db, $this->connection)) {
424 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
427 $result = @mysql_query('COMMIT', $this->connection);
428 $result = @mysql_query('SET AUTOCOMMIT=1', $this->connection);
429 $this->transaction_opcount = 0;
431 return $this->mysqlRaiseError();
441 * Roll back (undo) the current transaction.
445 if ($this->transaction_opcount > 0) {
447 if (!@mysql_select_db($this->_db, $this->connection)) {
448 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
451 $result = @mysql_query('ROLLBACK', $this->connection);
452 $result = @mysql_query('SET AUTOCOMMIT=1', $this->connection);
453 $this->transaction_opcount = 0;
455 return $this->mysqlRaiseError();
462 // {{{ affectedRows()
465 * Gets the number of rows affected by the data manipulation
466 * query. For other queries, this function returns 0.
468 * @return number of rows affected by the last query
471 function affectedRows()
473 if (DB::isManip($this->last_query)) {
474 $result = @mysql_affected_rows($this->connection);
485 * Get the native error code of the last error (if any) that
486 * occured on the current connection.
490 * @return int native MySQL error code
493 function errorNative()
495 return mysql_errno($this->connection);
502 * Get the next value in a sequence. We emulate sequences
503 * for MySQL. Will create the sequence if it does not exist.
507 * @param string $seq_name the name of the sequence
509 * @param bool $ondemand whether to create the sequence table on demand
512 * @return mixed a sequence integer, or a DB error
514 function nextId($seq_name, $ondemand = true)
516 $seqname = $this->getSequenceName($seq_name);
519 $this->pushErrorHandling(PEAR_ERROR_RETURN);
520 $result = $this->query("UPDATE ${seqname} ".
521 'SET id=LAST_INSERT_ID(id+1)');
522 $this->popErrorHandling();
523 if ($result == DB_OK) {
525 $id = mysql_insert_id($this->connection);
529 /** EMPTY SEQ TABLE **/
530 // Sequence table must be empty for some reason, so fill it and return 1
531 // Obtain a user-level lock
532 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
533 if (DB::isError($result)) {
534 return $this->raiseError($result);
537 // Failed to get the lock, bail with a DB_ERROR_NOT_LOCKED error
538 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
541 // add the default value
542 $result = $this->query("REPLACE INTO ${seqname} VALUES (0)");
543 if (DB::isError($result)) {
544 return $this->raiseError($result);
548 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
549 if (DB::isError($result)) {
550 return $this->raiseError($result);
552 // We know what the result will be, so no need to try again
555 /** ONDEMAND TABLE CREATION **/
556 } elseif ($ondemand && DB::isError($result) &&
557 $result->getCode() == DB_ERROR_NOSUCHTABLE)
559 $result = $this->createSequence($seq_name);
560 // Since createSequence initializes the ID to be 1,
561 // we do not need to retrieve the ID again (or we will get 2)
562 if (DB::isError($result)) {
563 return $this->raiseError($result);
565 // First ID of a newly created sequence is 1
569 /** BACKWARDS COMPAT **/
570 } elseif (DB::isError($result) &&
571 $result->getCode() == DB_ERROR_ALREADY_EXISTS)
573 // see _BCsequence() comment
574 $result = $this->_BCsequence($seqname);
575 if (DB::isError($result)) {
576 return $this->raiseError($result);
582 return $this->raiseError($result);
586 // {{{ createSequence()
588 function createSequence($seq_name)
590 $seqname = $this->getSequenceName($seq_name);
591 $res = $this->query("CREATE TABLE ${seqname} ".
592 '(id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'.
593 ' PRIMARY KEY(id))');
594 if (DB::isError($res)) {
597 // insert yields value 1, nextId call will generate ID 2
598 return $this->query("INSERT INTO ${seqname} VALUES(0)");
602 // {{{ dropSequence()
604 function dropSequence($seq_name)
606 $seqname = $this->getSequenceName($seq_name);
607 return $this->query("DROP TABLE ${seqname}");
613 * Bacwards Compatibility with old sequence emulation implementation
614 * (clean up the dupes)
615 * @param string $seqname The sequence name to clean up
616 * @return mixed DB_Error or true
618 function _BCsequence($seqname)
620 // Obtain a user-level lock... this will release any previous
621 // application locks, but unlike LOCK TABLES, it does not abort
622 // the current transaction and is much less frequently used.
623 $result = $this->getOne("SELECT GET_LOCK('${seqname}_lock',10)");
624 if (DB::isError($result)) {
628 // Failed to get the lock, can't do the conversion, bail
629 // with a DB_ERROR_NOT_LOCKED error
630 return $this->mysqlRaiseError(DB_ERROR_NOT_LOCKED);
633 $highest_id = $this->getOne("SELECT MAX(id) FROM ${seqname}");
634 if (DB::isError($highest_id)) {
637 // This should kill all rows except the highest
638 // We should probably do something if $highest_id isn't
639 // numeric, but I'm at a loss as how to handle that...
640 $result = $this->query("DELETE FROM ${seqname} WHERE id <> $highest_id");
641 if (DB::isError($result)) {
645 // If another thread has been waiting for this lock,
646 // it will go thru the above procedure, but will have no
648 $result = $this->getOne("SELECT RELEASE_LOCK('${seqname}_lock')");
649 if (DB::isError($result)) {
657 * Quote the given string so it can be safely used within string delimiters
659 * @param $string mixed Data to be quoted
660 * @return mixed "NULL" string, quoted string or original data
662 function quote($str = null)
664 switch (strtolower(gettype($str))) {
671 return "'".mysql_escape_string($str)."'";
677 function modifyQuery($query, $subject = null)
679 if ($this->options['optimize'] == 'portability') {
680 // "DELETE FROM table" gives 0 affected rows in MySQL.
681 // This little hack lets you know how many rows were deleted.
682 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $query)) {
683 $query = preg_replace('/^\s*DELETE\s+FROM\s+(\S+)\s*$/',
684 'DELETE FROM \1 WHERE 1=1', $query);
691 // {{{ modifyLimitQuery()
693 function modifyLimitQuery($query, $from, $count)
695 return $query . " LIMIT $from, $count";
699 // {{{ mysqlRaiseError()
701 function mysqlRaiseError($errno = null)
703 if ($errno === null) {
704 $errno = $this->errorCode(mysql_errno($this->connection));
706 return $this->raiseError($errno, null, null, null,
707 @mysql_errno($this->connection) . " ** " .
708 @mysql_error($this->connection));
714 function tableInfo($result, $mode = null) {
720 * depending on $mode, metadata returns the following values:
722 * - mode is false (default):
724 * [0]["table"] table name
725 * [0]["name"] field name
726 * [0]["type"] field type
727 * [0]["len"] field length
728 * [0]["flags"] field flags
730 * - mode is DB_TABLEINFO_ORDER
732 * ["num_fields"] number of metadata records
733 * [0]["table"] table name
734 * [0]["name"] field name
735 * [0]["type"] field type
736 * [0]["len"] field length
737 * [0]["flags"] field flags
738 * ["order"][field name] index of field named "field name"
739 * The last one is used, if you have a field name, but no index.
740 * Test: if (isset($result['meta']['myfield'])) { ...
742 * - mode is DB_TABLEINFO_ORDERTABLE
743 * the same as above. but additionally
744 * ["ordertable"][table name][field name] index of field
747 * this is, because if you have fields from different
748 * tables with the same field name * they override each
749 * other with DB_TABLEINFO_ORDER
751 * you can combine DB_TABLEINFO_ORDER and
752 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
753 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
756 // if $result is a string, then we want information about a
757 // table without a resultset
758 if (is_string($result)) {
759 $id = @mysql_list_fields($this->dsn['database'],
760 $result, $this->connection);
762 return $this->mysqlRaiseError();
764 } else { // else we want information about a resultset
767 return $this->mysqlRaiseError();
771 $count = @mysql_num_fields($id);
773 // made this IF due to performance (one if is faster than $count if's)
775 for ($i=0; $i<$count; $i++) {
776 $res[$i]['table'] = @mysql_field_table ($id, $i);
777 $res[$i]['name'] = @mysql_field_name ($id, $i);
778 $res[$i]['type'] = @mysql_field_type ($id, $i);
779 $res[$i]['len'] = @mysql_field_len ($id, $i);
780 $res[$i]['flags'] = @mysql_field_flags ($id, $i);
783 $res['num_fields']= $count;
785 for ($i=0; $i<$count; $i++) {
786 $res[$i]['table'] = @mysql_field_table ($id, $i);
787 $res[$i]['name'] = @mysql_field_name ($id, $i);
788 $res[$i]['type'] = @mysql_field_type ($id, $i);
789 $res[$i]['len'] = @mysql_field_len ($id, $i);
790 $res[$i]['flags'] = @mysql_field_flags ($id, $i);
791 if ($mode & DB_TABLEINFO_ORDER) {
792 $res['order'][$res[$i]['name']] = $i;
794 if ($mode & DB_TABLEINFO_ORDERTABLE) {
795 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
800 // free the result only if we were called on a table
801 if (is_string($result)) {
802 @mysql_free_result($id);
808 // {{{ getTablesQuery()
811 * Returns the query needed to get some backend info
812 * @param string $type What kind of info you want to retrieve
813 * @return string The SQL query string
815 function getSpecialQuery($type)
819 $sql = "SHOW TABLES";
822 return DB_ERROR_NOT_CAPABLE;
824 $sql = "select distinct User from user";
825 if($this->dsn['database'] != 'mysql') {
827 $dsn['database'] = 'mysql';
828 if (DB::isError($db = DB::connect($dsn))) {
831 $sql = $db->getCol($sql);
833 // XXX Fixme the mysql driver should take care of this
834 if (!@mysql_select_db($this->dsn['database'], $this->connection)) {
835 return $this->mysqlRaiseError(DB_ERROR_NODBSELECTED);
841 $sql = "SHOW DATABASES";