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 // | Authors: Stig Bakken <ssb@fast.no> |
18 // +----------------------------------------------------------------------+
20 // Based on code from the PHP CVS repository. The only modifications made
21 // have been modification of the include paths.
23 rcs_id('$Id: common.php,v 1.1 2002-01-28 04:01:57 dairiki Exp $');
24 rcs_id('From Pear CVS: Id: common.php,v 1.78 2002/01/19 07:46:24 cox Exp');
26 // Base class for DB implementations.
30 * DB_common is a base class for DB implementations, and must be
31 * inherited by all such.
34 class DB_common extends PEAR
38 * assoc of capabilities for this DB implementation
39 * $features['limit'] => 'emulate' => emulate with fetch row by number
40 * 'alter' => alter the query
47 * assoc mapping native error codes to DB ones
53 * DB type (mysql, oci8, odbc etc.)
71 var $prepared_queries;
76 var $prepare_maxstmt = 0;
86 var $fetchmode = DB_FETCHMODE_ORDERED;
91 var $fetchmode_object_class = 'stdClass';
94 * $options["persistent"] -> boolean persistent connection true|false?
95 * $options["optimize"] -> string 'performance' or 'portability'
96 * $options["debug"] -> integer numeric debug level
100 'persistent' => false,
101 'optimize' => 'performance',
114 * String conversation
121 $info = get_class($this);
122 $info .= ": (phptype=" . $this->phptype .
123 ", dbsyntax=" . $this->dbsyntax .
126 if ($this->connection) {
127 $info .= " [connected]";
140 $this->PEAR('DB_Error');
141 $this->features = array();
142 $this->errorcode_map = array();
143 $this->fetchmode = DB_FETCHMODE_ORDERED;
150 * Quotes a string so it can be safely used within string delimiters
151 * in a query (preserved for compatibility issues, quote() is preffered).
153 * @return string quoted string
157 function quoteString($string)
159 $string = $this->quote($string);
160 if ($string{0} == "'") {
161 return substr($string, 1, -1);
167 * Quotes a string so it can be safely used in a query. It will return
168 * the string with single quotes around. Other backend quote styles
169 * should override this method.
171 * @param string $string the input string to quote
173 * @return string The NULL string or the string quotes
174 * in magic_quote_sybase style
176 function quote($string)
178 return ($string === null) ? 'NULL' : "'".str_replace("'", "''", $string)."'";
185 * Tell whether a DB implementation or its backend extension
186 * supports a given feature.
188 * @param array $feature name of the feature (see the DB class doc)
189 * @return bool whether this DB implementation supports $feature
193 function provides($feature)
195 return $this->features[$feature];
202 * Map native error codes to DB's portable ones. Requires that
203 * the DB implementation's constructor fills in the $errorcode_map
206 * @param mixed $nativecode the native error code, as returned by the backend
207 * database extension (string or integer)
209 * @return int a portable DB error code, or FALSE if this DB
210 * implementation has no mapping for the given error code.
215 function errorCode($nativecode)
217 if (isset($this->errorcode_map[$nativecode])) {
218 return $this->errorcode_map[$nativecode];
221 //php_error(E_WARNING, get_class($this)."::errorCode: no mapping for $nativecode");
222 // Fall back to DB_ERROR if there was no mapping.
228 // {{{ errorMessage()
231 * Map a DB error code to a textual message. This is actually
232 * just a wrapper for DB::errorMessage().
234 * @param integer $dbcode the DB error code
236 * @return string the corresponding error message, of FALSE
237 * if the error code was unknown
242 function errorMessage($dbcode)
244 return DB::errorMessage($this->errorcode_map[$dbcode]);
251 * This method is used to communicate an error and invoke error
252 * callbacks etc. Basically a wrapper for PEAR::raiseError
253 * without the message string.
255 * @param mixed integer error code, or a PEAR error object (all
256 * other parameters are ignored if this parameter is
259 * @param int error mode, see PEAR_Error docs
261 * @param mixed If error mode is PEAR_ERROR_TRIGGER, this is the
262 * error level (E_USER_NOTICE etc). If error mode is
263 * PEAR_ERROR_CALLBACK, this is the callback function,
264 * either as a function name, or as an array of an
265 * object and method name. For other error modes this
266 * parameter is ignored.
268 * @param string Extra debug information. Defaults to the last
269 * query and native error code.
271 * @param mixed Native error code, integer or string depending the
274 * @return object a PEAR error object
279 function &raiseError($code = DB_ERROR, $mode = null, $options = null,
280 $userinfo = null, $nativecode = null)
282 // The error is yet a DB error object
283 if (is_object($code)) {
284 return PEAR::raiseError($code, null, null, null, null, null, true);
287 if ($userinfo === null) {
288 $userinfo = $this->last_query;
292 $userinfo .= " [nativecode=$nativecode]";
295 return PEAR::raiseError(null, $code, $mode, $options, $userinfo,
300 // {{{ setFetchMode()
303 * Sets which fetch mode should be used by default on queries
304 * on this connection.
306 * @param integer $fetchmode DB_FETCHMODE_ORDERED or
307 * DB_FETCHMODE_ASSOC, possibly bit-wise OR'ed with
308 * DB_FETCHMODE_FLIPPED.
310 * @param string $object_class The class of the object
311 * to be returned by the fetch methods when
312 * the DB_FETCHMODE_OBJECT mode is selected.
313 * If no class is specified by default a cast
314 * to object from the assoc array row will be done.
315 * There is also the posibility to use and extend the
318 * @see DB_FETCHMODE_ORDERED
319 * @see DB_FETCHMODE_ASSOC
320 * @see DB_FETCHMODE_FLIPPED
321 * @see DB_FETCHMODE_OBJECT
322 * @see DB_Row::DB_Row()
326 function setFetchMode($fetchmode, $object_class = null)
328 switch ($fetchmode) {
329 case DB_FETCHMODE_OBJECT:
331 $this->fetchmode_object_class = $object_class;
333 case DB_FETCHMODE_ORDERED:
334 case DB_FETCHMODE_ASSOC:
335 $this->fetchmode = $fetchmode;
338 return $this->raiseError('invalid fetchmode mode');
345 * set the option for the db class
347 * @param string $option option name
348 * @param mixed $value value for the option
350 * @return mixed DB_OK or DB_Error
352 function setOption($option, $value)
354 if (isset($this->options[$option])) {
355 $this->options[$option] = $value;
358 return $this->raiseError("unknown option $option");
364 * returns the value of an option
366 * @param string $option option name
368 * @return mixed the option value
370 function getOption($option)
372 if (isset($this->options[$option])) {
373 return $this->options[$option];
375 return $this->raiseError("unknown option $option");
382 * Prepares a query for multiple execution with execute().
383 * With some database backends, this is emulated.
384 * prepare() requires a generic query as string like
385 * "INSERT INTO numbers VALUES(?,?,?)". The ? are wildcards.
386 * Types of wildcards:
387 * ? - a quoted scalar value, i.e. strings, integers
388 * & - requires a file name, the content of the file
389 * insert into the query (i.e. saving binary data
391 * ! - value is inserted 'as is'
393 * @param string the query to prepare
395 * @return resource handle for the query
401 function prepare($query)
403 $tokens = split("[\&\?\!]", $query);
407 for ($i = 0; $i < strlen($query); $i++) {
408 switch ($query[$i]) {
410 $types[$token++] = DB_PARAM_SCALAR;
413 $types[$token++] = DB_PARAM_OPAQUE;
416 $types[$token++] = DB_PARAM_MISC;
421 $this->prepare_tokens[] = &$tokens;
422 end($this->prepare_tokens);
424 $k = key($this->prepare_tokens);
425 $this->prepare_types[$k] = $types;
426 $this->prepared_queries[$k] = &$query;
434 * Executes a prepared SQL query
435 * With execute() the generic query of prepare is
436 * assigned with the given data array. The values
437 * of the array inserted into the query in the same
438 * order like the array order
440 * @param resource $stmt query handle from prepare()
441 * @param array $data numeric array containing the
442 * data to insert into the query
444 * @return mixed a new DB_Result or a DB_Error when fail
449 function execute($stmt, $data = false)
451 $realquery = $this->executeEmulateQuery($stmt, $data);
452 if (DB::isError($realquery)) {
455 $result = $this->simpleQuery($realquery);
456 if (DB::isError($result) || $result === DB_OK) {
459 return new DB_result($this, $result);
464 // {{{ executeEmulateQuery()
467 * Emulates the execute statement, when not supported
469 * @param resource $stmt query handle from prepare()
470 * @param array $data numeric array containing the
471 * data to insert into the query
473 * @return mixed a string containing the real query run when emulating
474 * prepare/execute. A DB error code is returned on failure.
480 function executeEmulateQuery($stmt, $data = false)
482 $p = &$this->prepare_tokens;
484 if (!isset($this->prepare_tokens[$stmt]) ||
485 !is_array($this->prepare_tokens[$stmt]) ||
486 !sizeof($this->prepare_tokens[$stmt]))
488 return $this->raiseError(DB_ERROR_INVALID);
491 $qq = &$this->prepare_tokens[$stmt];
492 $qp = sizeof($qq) - 1;
494 if ((!$data && $qp > 0) ||
495 (!is_array($data) && $qp > 1) ||
496 (is_array($data) && $qp > sizeof($data)))
498 $this->last_query = $this->prepared_queries[$stmt];
499 return $this->raiseError(DB_ERROR_NEED_MORE_DATA);
503 for ($i = 0; $i < $qp; $i++) {
504 $type = $this->prepare_types[$stmt][$i];
505 if ($type == DB_PARAM_OPAQUE) {
506 if (is_array($data)) {
507 $fp = fopen($data[$i], 'r');
509 $fp = fopen($data, 'r');
515 while (($buf = fread($fp, 4096)) != false) {
520 if (is_array($data)) {
527 $realquery .= ($type != DB_PARAM_MISC) ? $this->quote($pdata) : $pdata;
528 $realquery .= $qq[$i + 1];
535 // {{{ executeMultiple()
538 * This function does several execute() calls on the same
539 * statement handle. $data must be an array indexed numerically
540 * from 0, one execute call is done for every "row" in the array.
542 * If an error occurs during execute(), executeMultiple() does not
543 * execute the unfinished rows, but rather returns that error.
545 * @param resource $stmt query handle from prepare()
546 * @param array $data numeric array containing the
547 * data to insert into the query
549 * @return mixed DB_OK or DB_Error
552 * @see prepare(), execute()
555 function executeMultiple( $stmt, &$data )
557 for($i = 0; $i < sizeof( $data ); $i++) {
558 $res = $this->execute($stmt, $data[$i]);
559 if (DB::isError($res)) {
570 * This method is used by backends to alter queries for various
571 * reasons. It is defined here to assure that all implementations
572 * have this method defined.
574 * @param string $query query to modify
576 * @return the new (modified) query
580 function modifyQuery($query) {
585 // {{{ modifyLimitQuery()
587 * This method is used by backends to alter limited queries
589 * @param string $query query to modify
590 * @param integer $from the row to start to fetching
591 * @param integer $count the numbers of rows to fetch
593 * @return the new (modified) query
598 function modifyLimitQuery($query, $from, $count)
607 * Send a query to the database and return any results with a
612 * @param string $query the SQL query or the statement to prepare
613 * @param string $params the data to be added to the query
614 * @return mixed a DB_result object or DB_OK on success, a DB
618 * @see DB_common::prepare
619 * @see DB_common::execute
621 function &query($query, $params = array()) {
622 if (sizeof($params) > 0) {
623 $sth = $this->prepare($query);
624 if (DB::isError($sth)) {
627 return $this->execute($sth, $params);
629 $result = $this->simpleQuery($query);
630 if (DB::isError($result) || $result === DB_OK) {
633 return new DB_result($this, $result);
641 * Generates a limited query
644 * @param string $query query
645 * @param integer $from the row to start to fetching
646 * @param integer $count the numbers of rows to fetch
648 * @return mixed a DB_Result object or a DB_Error
652 function limitQuery($query, $from, $count)
654 $query = $this->modifyLimitQuery($query, $from, $count);
655 $result = $this->simpleQuery($query);
656 if (DB::isError($result) || $result === DB_OK) {
659 $res_obj =& new DB_result($this, $result);
660 $res_obj->limit_from = $from;
661 $res_obj->limit_count = $count;
670 * Fetch the first column of the first row of data returned from
671 * a query. Takes care of doing the query and freeing the results
674 * @param string $query the SQL query
675 * @param array $params if supplied, prepare/execute will be used
676 * with this array as execute parameters
678 * @return mixed DB_Error or the returned value of the query
683 function &getOne($query, $params = array())
685 settype($params, "array");
686 if (sizeof($params) > 0) {
687 $sth = $this->prepare($query);
688 if (DB::isError($sth)) {
691 $res = $this->execute($sth, $params);
693 $res = $this->query($query);
696 if (DB::isError($res)) {
700 $err = $res->fetchInto($row, DB_FETCHMODE_ORDERED);
701 if ($err !== DB_OK) {
708 $this->freeResult($sth);
718 * Fetch the first row of data returned from a query. Takes care
719 * of doing the query and freeing the results when finished.
721 * @param string $query the SQL query
722 * @param integer $fetchmode the fetch mode to use
723 * @param array $params array if supplied, prepare/execute will be used
724 * with this array as execute parameters
726 * @return array the first row of results as an array indexed from
727 * 0, or a DB error code.
730 function &getRow($query,
732 $fetchmode = DB_FETCHMODE_DEFAULT)
734 // compat check, the params and fetchmode parameters used to
735 // have the opposite order
736 if (!is_array($params)) {
737 if (is_array($fetchmode)) {
739 $params = $fetchmode;
741 } elseif ($params !== null) {
742 $fetchmode = $params;
746 $params = (empty($params)) ? array() : $params;
747 $fetchmode = (empty($fetchmode)) ? DB_FETCHMODE_DEFAULT : $fetchmode;
748 settype($params, 'array');
749 if (sizeof($params) > 0) {
750 $sth = $this->prepare($query);
751 if (DB::isError($sth)) {
754 $res = $this->execute($sth, $params);
756 $res = $this->query($query);
759 if (DB::isError($res)) {
763 $err = $res->fetchInto($row, $fetchmode);
765 if ($err !== DB_OK) {
771 $this->freeResult($sth);
781 * Fetch a single column from a result set and return it as an
784 * @param string $query the SQL query
786 * @param mixed $col which column to return (integer [column number,
787 * starting at 0] or string [column name])
789 * @param array $params array if supplied, prepare/execute will be used
790 * with this array as execute parameters
793 * @return array an indexed array with the data from the first
794 * row at index 0, or a DB error code.
797 function &getCol($query, $col = 0, $params = array())
799 settype($params, "array");
800 if (sizeof($params) > 0) {
801 $sth = $this->prepare($query);
803 if (DB::isError($sth)) {
807 $res = $this->execute($sth, $params);
809 $res = $this->query($query);
812 if (DB::isError($res)) {
816 $fetchmode = is_int($col) ? DB_FETCHMODE_ORDERED : DB_FETCHMODE_ASSOC;
819 while (is_array($row = $res->fetchRow($fetchmode))) {
822 if (DB::isError($row)) {
828 $this->freeResult($sth);
838 * Fetch the entire result set of a query and return it as an
839 * associative array using the first column as the key.
841 * If the result set contains more than two columns, the value
842 * will be an array of the values from column 2-n. If the result
843 * set contains only two columns, the returned value will be a
844 * scalar with the value of the second column (unless forced to an
845 * array with the $force_array parameter). A DB error code is
846 * returned on errors. If the result set contains fewer than two
847 * columns, a DB_ERROR_TRUNCATED error is returned.
849 * For example, if the table "mytable" contains:
852 * --------------------------------
855 * 3 'three' 944679408
857 * Then the call getAssoc('SELECT id,text FROM mytable') returns:
864 * ...while the call getAssoc('SELECT id,text,date FROM mytable') returns:
866 * '1' => array('one', '944679408'),
867 * '2' => array('two', '944679408'),
868 * '3' => array('three', '944679408')
871 * If the more than one row occurs with the same value in the
872 * first column, the last row overwrites all previous ones by
873 * default. Use the $group parameter if you don't want to
874 * overwrite like this. Example:
876 * getAssoc('SELECT category,id,name FROM mytable', false, null,
877 DB_FETCHMODE_ASSOC, true) returns:
879 * '1' => array(array('id' => '4', 'name' => 'number four'),
880 * array('id' => '6', 'name' => 'number six')
882 * '9' => array(array('id' => '4', 'name' => 'number four'),
883 * array('id' => '6', 'name' => 'number six')
887 * Keep in mind that database functions in PHP usually return string
888 * values for results regardless of the database's internal type.
890 * @param string $query the SQL query
892 * @param boolean $force_array used only when the query returns
893 * exactly two columns. If true, the values of the returned array
894 * will be one-element arrays instead of scalars.
896 * @param array $params array if supplied, prepare/execute will be used
897 * with this array as execute parameters
899 * @param boolean $group if true, the values of the returned array
900 * is wrapped in another array. If the same
901 * key value (in the first column) repeats
902 * itself, the values will be appended to
903 * this array instead of overwriting the
908 * @return array associative array with results from the query.
911 function &getAssoc($query, $force_array = false, $params = array(),
912 $fetchmode = DB_FETCHMODE_ORDERED, $group = false)
914 settype($params, "array");
915 if (sizeof($params) > 0) {
916 $sth = $this->prepare($query);
918 if (DB::isError($sth)) {
922 $res = $this->execute($sth, $params);
924 $res = $this->query($query);
927 if (DB::isError($res)) {
931 $cols = $res->numCols();
934 return $this->raiseError(DB_ERROR_TRUNCATED);
939 if ($cols > 2 || $force_array) {
940 // return array values
941 // XXX this part can be optimized
942 if ($fetchmode == DB_FETCHMODE_ASSOC) {
943 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ASSOC))) {
945 $key = current($row);
946 unset($row[key($row)]);
948 $results[$key][] = $row;
950 $results[$key] = $row;
954 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ORDERED))) {
955 // we shift away the first element to get
956 // indices running from 0 again
957 $key = array_shift($row);
959 $results[$key][] = $row;
961 $results[$key] = $row;
965 if (DB::isError($row)) {
969 // return scalar values
970 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ORDERED))) {
972 $results[$row[0]][] = $row[1];
974 $results[$row[0]] = $row[1];
977 if (DB::isError($row)) {
985 $this->freeResult($sth);
995 * Fetch all the rows returned from a query.
997 * @param string $query the SQL query
999 * @param array $params array if supplied, prepare/execute will be used
1000 * with this array as execute parameters
1001 * @param integer $fetchmode the fetch mode to use
1004 * @return array an nested array, or a DB error
1007 function &getAll($query,
1009 $fetchmode = DB_FETCHMODE_DEFAULT)
1011 // compat check, the params and fetchmode parameters used to
1012 // have the opposite order
1013 if (!is_array($params)) {
1014 if (is_array($fetchmode)) {
1016 $params = $fetchmode;
1018 } elseif ($params !== null) {
1019 $fetchmode = $params;
1023 $params = (empty($params)) ? array() : $params;
1024 $fetchmode = (empty($fetchmode)) ? DB_FETCHMODE_DEFAULT : $fetchmode;
1025 settype($params, "array");
1026 if (sizeof($params) > 0) {
1027 $sth = $this->prepare($query);
1029 if (DB::isError($sth)) {
1033 $res = $this->execute($sth, $params);
1035 $res = $this->query($query);
1038 if (DB::isError($res)) {
1043 $this->pushErrorHandling(PEAR_ERROR_RETURN);
1044 while (DB_OK === $res->fetchInto($row, $fetchmode)) {
1045 if ($fetchmode & DB_FETCHMODE_FLIPPED) {
1046 foreach ($row as $key => $val) {
1047 $results[$key][] = $val;
1053 $this->popErrorHandling();
1058 $this->freeResult($sth);
1060 if (DB::isError($row)) {
1061 return $this->raiseError($row);
1069 * enable automatic Commit
1071 * @param boolean $onoff
1072 * @return mixed DB_Error
1076 function autoCommit($onoff=false)
1078 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1086 * @return mixed DB_Error
1092 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1100 * @return mixed DB_Error
1106 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1112 * returns the number of rows in a result object
1114 * @param object DB_Result the result object to check
1116 * @return mixed DB_Error or the number of rows
1120 function numRows($result)
1122 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1126 // {{{ affectedRows()
1128 * returns the affected rows of a query
1130 * @return mixed DB_Error or number of rows
1134 function affectedRows()
1136 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1140 // {{{ errorNative()
1142 * returns an errormessage, provides by the database
1144 * @return mixed DB_Error or message
1148 function errorNative()
1150 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1156 * returns the next free id of a sequence
1158 * @param string $seq_name name of the sequence
1159 * @param boolean $ondemand when true the seqence is
1160 * automatic created, if it
1163 * @return mixed DB_Error or id
1165 function nextId($seq_name, $ondemand = true)
1167 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1171 // {{{ createSequence()
1173 * creates a new sequence
1175 * @param string $seq_name name of the new sequence
1177 * @return mixed DB_Error
1181 function createSequence($seq_name)
1183 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1187 // {{{ dropSequence()
1189 * deletes a sequence
1191 * @param string $seq_name name of the sequence
1193 * @return mixed DB_Error
1197 function dropSequence($seq_name)
1199 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1205 * returns meta data about the result set
1207 * @param object DB_Result $result the result object to analyse
1208 * @param mixed $mode depends on implementation
1210 * @return mixed DB_Error
1214 function tableInfo($result, $mode = null)
1216 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1224 function getTables()
1226 return $this->getListOf('tables');
1232 * list internal DB info
1233 * valid values for $type are db dependent,
1234 * often: databases, users, view, functions
1236 * @param string $type type of requested info
1238 * @return mixed DB_Error or the requested data
1242 function getListOf($type)
1244 $sql = $this->getSpecialQuery($type);
1245 if ($sql === null) { // No support
1246 return $this->raiseError(DB_ERROR_UNSUPPORTED);
1247 } elseif (is_int($sql) || DB::isError($sql)) { // Previous error
1248 return $this->raiseError($sql);
1249 } elseif (is_array($sql)) { // Already the result
1252 return $this->getCol($sql); // Launch this query