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: Stig Bakken <ssb@php.net> |
17 // | Tomas V.V.Cox <cox@idecnet.com> |
18 // | Maintainer: Daniel Convissor <danielc@php.net> |
19 // +----------------------------------------------------------------------+
23 require_once 'PEAR.php';
26 * DB_common is a base class for DB implementations, and must be
27 * inherited by all such
32 * @author Stig Bakken <ssb@php.net>
33 * @author Tomas V.V.Cox <cox@idecnet.com>
35 class DB_common extends PEAR
40 * assoc of capabilities for this DB implementation
41 * $features['limit'] => 'emulate' => emulate with fetch row by number
42 * 'alter' => alter the query
46 var $features = array();
49 * assoc mapping native error codes to DB ones
52 var $errorcode_map = array();
55 * DB type (mysql, oci8, odbc etc.)
73 var $prepared_queries;
78 var $prepare_maxstmt = 0;
88 var $fetchmode = DB_FETCHMODE_ORDERED;
93 var $fetchmode_object_class = 'stdClass';
96 * Run-time configuration options.
98 * The 'optimize' option has been deprecated. Use the 'portability'
101 * @see DB_common::setOption()
104 var $options = array(
105 'persistent' => false,
108 'seqname_format' => '%s_seq',
110 'portability' => DB_PORTABILITY_NONE,
111 'optimize' => 'performance', // Deprecated. Use 'portability'.
124 * String conversation
131 $info = strtolower(get_class($this));
132 $info .= ': (phptype=' . $this->phptype .
133 ', dbsyntax=' . $this->dbsyntax .
136 if ($this->connection) {
137 $info .= ' [connected]';
151 $this->PEAR('DB_Error');
158 * DEPRECATED: Quotes a string so it can be safely used within string
159 * delimiters in a query
161 * @return string quoted string
163 * @see DB_common::quoteSmart(), DB_common::escapeSimple()
164 * @deprecated Deprecated in release 1.2 or lower
167 function quoteString($string)
169 $string = $this->quote($string);
170 if ($string{0} == "'") {
171 return substr($string, 1, -1);
180 * DEPRECATED: Quotes a string so it can be safely used in a query
182 * @param string $string the input string to quote
184 * @return string The NULL string or the string quotes
185 * in magic_quote_sybase style
187 * @see DB_common::quoteSmart(), DB_common::escapeSimple()
188 * @deprecated Deprecated in release 1.6.0
191 function quote($string = null)
193 return ($string === null) ? 'NULL' : "'".str_replace("'", "''", $string)."'";
197 // {{{ quoteIdentifier()
200 * Quote a string so it can be safely used as a table or column name
202 * Delimiting style depends on which database driver is being used.
204 * NOTE: just because you CAN use delimited identifiers doesn't mean
205 * you SHOULD use them. In general, they end up causing way more
206 * problems than they solve.
208 * Portability is broken by using the following characters inside
209 * delimited identifiers:
210 * + backtick (<kbd>`</kbd>) -- due to MySQL
211 * + double quote (<kbd>"</kbd>) -- due to Oracle
212 * + brackets (<kbd>[</kbd> or <kbd>]</kbd>) -- due to Access
214 * Delimited identifiers are known to generally work correctly under
215 * the following drivers:
226 * InterBase doesn't seem to be able to use delimited identifiers
227 * via PHP 4. They work fine under PHP 5.
229 * @param string $str identifier name to be quoted
231 * @return string quoted identifier string
236 function quoteIdentifier($str)
238 return '"' . str_replace('"', '""', $str) . '"';
245 * Format input so it can be safely used in a query
247 * The output depends on the PHP data type of input and the database
250 * @param mixed $in data to be quoted
252 * @return mixed the format of the results depends on the input's
257 * <kbd>input</kbd> -> <samp>returns</samp>
260 * <kbd>null</kbd> -> the string <samp>NULL</samp>
263 * <kbd>integer</kbd> or <kbd>double</kbd> -> the unquoted number
266 * &type.bool; -> output depends on the driver in use
267 * Most drivers return integers: <samp>1</samp> if
268 * <kbd>true</kbd> or <samp>0</samp> if
270 * Some return strings: <samp>TRUE</samp> if
271 * <kbd>true</kbd> or <samp>FALSE</samp> if
273 * Finally one returns strings: <samp>T</samp> if
274 * <kbd>true</kbd> or <samp>F</samp> if
275 * <kbd>false</kbd>. Here is a list of each DBMS,
276 * the values returned and the suggested column type:
279 * <kbd>dbase</kbd> -> <samp>T/F</samp>
280 * (<kbd>Logical</kbd>)
283 * <kbd>fbase</kbd> -> <samp>TRUE/FALSE</samp>
284 * (<kbd>BOOLEAN</kbd>)
287 * <kbd>ibase</kbd> -> <samp>1/0</samp>
288 * (<kbd>SMALLINT</kbd>) [1]
291 * <kbd>ifx</kbd> -> <samp>1/0</samp>
292 * (<kbd>SMALLINT</kbd>) [1]
295 * <kbd>msql</kbd> -> <samp>1/0</samp>
296 * (<kbd>INTEGER</kbd>)
299 * <kbd>mssql</kbd> -> <samp>1/0</samp>
303 * <kbd>mysql</kbd> -> <samp>1/0</samp>
304 * (<kbd>TINYINT(1)</kbd>)
307 * <kbd>mysqli</kbd> -> <samp>1/0</samp>
308 * (<kbd>TINYINT(1)</kbd>)
311 * <kbd>oci8</kbd> -> <samp>1/0</samp>
312 * (<kbd>NUMBER(1)</kbd>)
315 * <kbd>odbc</kbd> -> <samp>1/0</samp>
316 * (<kbd>SMALLINT</kbd>) [1]
319 * <kbd>pgsql</kbd> -> <samp>TRUE/FALSE</samp>
320 * (<kbd>BOOLEAN</kbd>)
323 * <kbd>sqlite</kbd> -> <samp>1/0</samp>
324 * (<kbd>INTEGER</kbd>)
327 * <kbd>sybase</kbd> -> <samp>1/0</samp>
328 * (<kbd>TINYINT(1)</kbd>)
331 * [1] Accommodate the lowest common denominator because not all
332 * versions of have <kbd>BOOLEAN</kbd>.
335 * other (including strings and numeric strings) ->
336 * the data with single quotes escaped by preceeding
337 * single quotes, backslashes are escaped by preceeding
338 * backslashes, then the whole string is encapsulated
339 * between single quotes
344 * @see DB_common::escapeSimple()
347 function quoteSmart($in)
349 if (is_int($in) || is_double($in)) {
351 } elseif (is_bool($in)) {
353 } elseif (is_null($in)) {
356 return "'" . $this->escapeSimple($in) . "'";
361 // {{{ escapeSimple()
364 * Escape a string according to the current DBMS's standards
366 * In SQLite, this makes things safe for inserts/updates, but may
367 * cause problems when performing text comparisons against columns
368 * containing binary data. See the
369 * {@link http://php.net/sqlite_escape_string PHP manual} for more info.
371 * @param string $str the string to be escaped
373 * @return string the escaped string
376 * @see DB_common::quoteSmart()
379 function escapeSimple($str) {
380 return str_replace("'", "''", $str);
387 * Tell whether a DB implementation or its backend extension
388 * supports a given feature
390 * @param array $feature name of the feature (see the DB class doc)
391 * @return bool whether this DB implementation supports $feature
394 function provides($feature)
396 return $this->features[$feature];
403 * Map native error codes to DB's portable ones
405 * Requires that the DB implementation's constructor fills
406 * in the <var>$errorcode_map</var> property.
408 * @param mixed $nativecode the native error code, as returned by the
409 * backend database extension (string or integer)
411 * @return int a portable DB error code, or DB_ERROR if this DB
412 * implementation has no mapping for the given error code.
416 function errorCode($nativecode)
418 if (isset($this->errorcode_map[$nativecode])) {
419 return $this->errorcode_map[$nativecode];
421 // Fall back to DB_ERROR if there was no mapping.
426 // {{{ errorMessage()
429 * Map a DB error code to a textual message. This is actually
430 * just a wrapper for DB::errorMessage()
432 * @param integer $dbcode the DB error code
434 * @return string the corresponding error message, of false
435 * if the error code was unknown
439 function errorMessage($dbcode)
441 return DB::errorMessage($this->errorcode_map[$dbcode]);
448 * Communicate an error and invoke error callbacks, etc
450 * Basically a wrapper for PEAR::raiseError without the message string.
452 * @param mixed integer error code, or a PEAR error object (all
453 * other parameters are ignored if this parameter is
456 * @param int error mode, see PEAR_Error docs
458 * @param mixed If error mode is PEAR_ERROR_TRIGGER, this is the
459 * error level (E_USER_NOTICE etc). If error mode is
460 * PEAR_ERROR_CALLBACK, this is the callback function,
461 * either as a function name, or as an array of an
462 * object and method name. For other error modes this
463 * parameter is ignored.
465 * @param string Extra debug information. Defaults to the last
466 * query and native error code.
468 * @param mixed Native error code, integer or string depending the
471 * @return object a PEAR error object
476 function &raiseError($code = DB_ERROR, $mode = null, $options = null,
477 $userinfo = null, $nativecode = null)
479 // The error is yet a DB error object
480 if (is_object($code)) {
481 // because we the static PEAR::raiseError, our global
482 // handler should be used if it is set
483 if ($mode === null && !empty($this->_default_error_mode)) {
484 $mode = $this->_default_error_mode;
485 $options = $this->_default_error_options;
487 $tmp = PEAR::raiseError($code, null, $mode, $options, null, null, true);
491 if ($userinfo === null) {
492 $userinfo = $this->last_query;
496 $userinfo .= ' [nativecode=' . trim($nativecode) . ']';
499 $tmp = PEAR::raiseError(null, $code, $mode, $options, $userinfo,
505 // {{{ setFetchMode()
508 * Sets which fetch mode should be used by default on queries
511 * @param integer $fetchmode DB_FETCHMODE_ORDERED or
512 * DB_FETCHMODE_ASSOC, possibly bit-wise OR'ed with
513 * DB_FETCHMODE_FLIPPED.
515 * @param string $object_class The class of the object
516 * to be returned by the fetch methods when
517 * the DB_FETCHMODE_OBJECT mode is selected.
518 * If no class is specified by default a cast
519 * to object from the assoc array row will be done.
520 * There is also the posibility to use and extend the
523 * @see DB_FETCHMODE_ORDERED
524 * @see DB_FETCHMODE_ASSOC
525 * @see DB_FETCHMODE_FLIPPED
526 * @see DB_FETCHMODE_OBJECT
527 * @see DB_row::DB_row()
530 function setFetchMode($fetchmode, $object_class = 'stdClass')
532 switch ($fetchmode) {
533 case DB_FETCHMODE_OBJECT:
534 $this->fetchmode_object_class = $object_class;
535 case DB_FETCHMODE_ORDERED:
536 case DB_FETCHMODE_ASSOC:
537 $this->fetchmode = $fetchmode;
540 return $this->raiseError('invalid fetchmode mode');
548 * Set run-time configuration options for PEAR DB
550 * Options, their data types, default values and description:
553 * <var>autofree</var> <kbd>boolean</kbd> = <samp>false</samp>
554 * <br />should results be freed automatically when there are no
557 * <var>debug</var> <kbd>integer</kbd> = <samp>0</samp>
560 * <var>persistent</var> <kbd>boolean</kbd> = <samp>false</samp>
561 * <br />should the connection be persistent?
563 * <var>portability</var> <kbd>integer</kbd> = <samp>DB_PORTABILITY_NONE</samp>
564 * <br />portability mode constant (see below)
566 * <var>seqname_format</var> <kbd>string</kbd> = <samp>%s_seq</samp>
567 * <br />the sprintf() format string used on sequence names. This
568 * format is applied to sequence names passed to
569 * createSequence(), nextID() and dropSequence().
571 * <var>ssl</var> <kbd>boolean</kbd> = <samp>false</samp>
572 * <br />use ssl to connect?
576 * -----------------------------------------
580 * These modes are bitwised, so they can be combined using <kbd>|</kbd>
581 * and removed using <kbd>^</kbd>. See the examples section below on how
584 * <samp>DB_PORTABILITY_NONE</samp>
585 * turn off all portability features
587 * This mode gets automatically turned on if the deprecated
588 * <var>optimize</var> option gets set to <samp>performance</samp>.
591 * <samp>DB_PORTABILITY_LOWERCASE</samp>
592 * convert names of tables and fields to lower case when using
593 * <kbd>get*()</kbd>, <kbd>fetch*()</kbd> and <kbd>tableInfo()</kbd>
595 * This mode gets automatically turned on in the following databases
596 * if the deprecated option <var>optimize</var> gets set to
597 * <samp>portability</samp>:
601 * <samp>DB_PORTABILITY_RTRIM</samp>
602 * right trim the data output by <kbd>get*()</kbd> <kbd>fetch*()</kbd>
605 * <samp>DB_PORTABILITY_DELETE_COUNT</samp>
606 * force reporting the number of rows deleted
608 * Some DBMS's don't count the number of rows deleted when performing
609 * simple <kbd>DELETE FROM tablename</kbd> queries. This portability
610 * mode tricks such DBMS's into telling the count by adding
611 * <samp>WHERE 1=1</samp> to the end of <kbd>DELETE</kbd> queries.
613 * This mode gets automatically turned on in the following databases
614 * if the deprecated option <var>optimize</var> gets set to
615 * <samp>portability</samp>:
622 * <samp>DB_PORTABILITY_NUMROWS</samp>
623 * enable hack that makes <kbd>numRows()</kbd> work in Oracle
625 * This mode gets automatically turned on in the following databases
626 * if the deprecated option <var>optimize</var> gets set to
627 * <samp>portability</samp>:
631 * <samp>DB_PORTABILITY_ERRORS</samp>
632 * makes certain error messages in certain drivers compatible
633 * with those from other DBMS's
635 * + mysql, mysqli: change unique/primary key constraints
636 * DB_ERROR_ALREADY_EXISTS -> DB_ERROR_CONSTRAINT
638 * + odbc(access): MS's ODBC driver reports 'no such field' as code
639 * 07001, which means 'too few parameters.' When this option is on
640 * that code gets mapped to DB_ERROR_NOSUCHFIELD.
641 * DB_ERROR_MISMATCH -> DB_ERROR_NOSUCHFIELD
644 * <samp>DB_PORTABILITY_NULL_TO_EMPTY</samp>
645 * convert null values to empty strings in data output by get*() and
646 * fetch*(). Needed because Oracle considers empty strings to be null,
647 * while most other DBMS's know the difference between empty and null.
650 * <samp>DB_PORTABILITY_ALL</samp>
651 * turn on all portability features
653 * -----------------------------------------
655 * Example 1. Simple setOption() example
657 * $dbh->setOption('autofree', true);
660 * Example 2. Portability for lowercasing and trimming
662 * $dbh->setOption('portability',
663 * DB_PORTABILITY_LOWERCASE | DB_PORTABILITY_RTRIM);
666 * Example 3. All portability options except trimming
668 * $dbh->setOption('portability',
669 * DB_PORTABILITY_ALL ^ DB_PORTABILITY_RTRIM);
672 * @param string $option option name
673 * @param mixed $value value for the option
675 * @return int DB_OK on success. DB_Error object on failure.
677 * @see DB_common::$options
679 function setOption($option, $value)
681 if (isset($this->options[$option])) {
682 $this->options[$option] = $value;
685 * Backwards compatibility check for the deprecated 'optimize'
686 * option. Done here in case settings change after connecting.
688 if ($option == 'optimize') {
689 if ($value == 'portability') {
690 switch ($this->phptype) {
692 $this->options['portability'] =
693 DB_PORTABILITY_LOWERCASE |
694 DB_PORTABILITY_NUMROWS;
700 $this->options['portability'] =
701 DB_PORTABILITY_DELETE_COUNT;
705 $this->options['portability'] = DB_PORTABILITY_NONE;
711 return $this->raiseError("unknown option $option");
718 * Returns the value of an option
720 * @param string $option option name
722 * @return mixed the option value
724 function getOption($option)
726 if (isset($this->options[$option])) {
727 return $this->options[$option];
729 return $this->raiseError("unknown option $option");
736 * Prepares a query for multiple execution with execute()
738 * Creates a query that can be run multiple times. Each time it is run,
739 * the placeholders, if any, will be replaced by the contents of
740 * execute()'s $data argument.
742 * Three types of placeholders can be used:
743 * + <kbd>?</kbd> scalar value (i.e. strings, integers). The system
744 * will automatically quote and escape the data.
745 * + <kbd>!</kbd> value is inserted 'as is'
746 * + <kbd>&</kbd> requires a file name. The file's contents get
747 * inserted into the query (i.e. saving binary
752 * $sth = $dbh->prepare('INSERT INTO tbl (a, b, c) VALUES (?, !, &)');
758 * $res = $dbh->execute($sth, $data);
761 * Use backslashes to escape placeholder characters if you don't want
762 * them to be interpreted as placeholders:
764 * "UPDATE foo SET col=? WHERE col='over \& under'"
767 * With some database backends, this is emulated.
769 * {@internal ibase and oci8 have their own prepare() methods.}}
771 * @param string $query query to be prepared
773 * @return mixed DB statement resource on success. DB_Error on failure.
775 * @see DB_common::execute()
778 function prepare($query)
780 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
781 PREG_SPLIT_DELIM_CAPTURE);
784 $newtokens = array();
786 foreach ($tokens as $val) {
789 $types[$token++] = DB_PARAM_SCALAR;
792 $types[$token++] = DB_PARAM_OPAQUE;
795 $types[$token++] = DB_PARAM_MISC;
798 $newtokens[] = preg_replace('/\\\([&?!])/', "\\1", $val);
802 $this->prepare_tokens[] = &$newtokens;
803 end($this->prepare_tokens);
805 $k = key($this->prepare_tokens);
806 $this->prepare_types[$k] = $types;
807 $this->prepared_queries[$k] = implode(' ', $newtokens);
816 * Automaticaly generate an insert or update query and pass it to prepare()
818 * @param string $table name of the table
819 * @param array $table_fields ordered array containing the fields names
820 * @param int $mode type of query to make (DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE)
821 * @param string $where in case of update queries, this string will be put after the sql WHERE statement
822 * @return resource handle for the query
823 * @see DB_common::prepare(), DB_common::buildManipSQL()
826 function autoPrepare($table, $table_fields, $mode = DB_AUTOQUERY_INSERT, $where = false)
828 $query = $this->buildManipSQL($table, $table_fields, $mode, $where);
829 return $this->prepare($query);
836 * Automaticaly generate an insert or update query and call prepare()
837 * and execute() with it
839 * @param string $table name of the table
840 * @param array $fields_values assoc ($key=>$value) where $key is a field name and $value its value
841 * @param int $mode type of query to make (DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE)
842 * @param string $where in case of update queries, this string will be put after the sql WHERE statement
843 * @return mixed a new DB_Result or a DB_Error when fail
844 * @see DB_common::autoPrepare(), DB_common::buildManipSQL()
847 function autoExecute($table, $fields_values, $mode = DB_AUTOQUERY_INSERT, $where = false)
849 $sth = $this->autoPrepare($table, array_keys($fields_values), $mode, $where);
850 $ret =& $this->execute($sth, array_values($fields_values));
851 $this->freePrepared($sth);
857 // {{{ buildManipSQL()
860 * Make automaticaly an sql query for prepare()
862 * Example : buildManipSQL('table_sql', array('field1', 'field2', 'field3'), DB_AUTOQUERY_INSERT)
863 * will return the string : INSERT INTO table_sql (field1,field2,field3) VALUES (?,?,?)
864 * NB : - This belongs more to a SQL Builder class, but this is a simple facility
865 * - Be carefull ! If you don't give a $where param with an UPDATE query, all
866 * the records of the table will be updated !
868 * @param string $table name of the table
869 * @param array $table_fields ordered array containing the fields names
870 * @param int $mode type of query to make (DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE)
871 * @param string $where in case of update queries, this string will be put after the sql WHERE statement
872 * @return string sql query for prepare()
875 function buildManipSQL($table, $table_fields, $mode, $where = false)
877 if (count($table_fields) == 0) {
878 $this->raiseError(DB_ERROR_NEED_MORE_DATA);
882 case DB_AUTOQUERY_INSERT:
885 foreach ($table_fields as $value) {
895 return "INSERT INTO $table ($names) VALUES ($values)";
896 case DB_AUTOQUERY_UPDATE:
898 foreach ($table_fields as $value) {
904 $set .= "$value = ?";
906 $sql = "UPDATE $table SET $set";
908 $sql .= " WHERE $where";
912 $this->raiseError(DB_ERROR_SYNTAX);
920 * Executes a DB statement prepared with prepare()
924 * $sth = $dbh->prepare('INSERT INTO tbl (a, b, c) VALUES (?, !, &)');
930 * $res =& $dbh->execute($sth, $data);
933 * @param resource $stmt a DB statement resource returned from prepare()
934 * @param mixed $data array, string or numeric data to be used in
935 * execution of the statement. Quantity of items
936 * passed must match quantity of placeholders in
937 * query: meaning 1 placeholder for non-array
938 * parameters or 1 placeholder per array element.
940 * @return object a new DB_Result or a DB_Error when fail
942 * {@internal ibase and oci8 have their own execute() methods.}}
944 * @see DB_common::prepare()
947 function execute($stmt, $data = array())
949 $realquery = $this->executeEmulateQuery($stmt, $data);
950 if (DB::isError($realquery)) {
953 $result = $this->simpleQuery($realquery);
955 if (DB::isError($result) || $result === DB_OK) {
958 $tmp = new DB_result($this, $result);
964 // {{{ executeEmulateQuery()
967 * Emulates the execute statement, when not supported
969 * @param resource $stmt a DB statement resource returned from execute()
970 * @param mixed $data array, string or numeric data to be used in
971 * execution of the statement. Quantity of items
972 * passed must match quantity of placeholders in
973 * query: meaning 1 placeholder for non-array
974 * parameters or 1 placeholder per array element.
976 * @return mixed a string containing the real query run when emulating
977 * prepare/execute. A DB error code is returned on failure.
979 * @see DB_common::execute()
982 function executeEmulateQuery($stmt, $data = array())
984 if (!is_array($data)) {
985 $data = array($data);
988 if (count($this->prepare_types[$stmt]) != count($data)) {
989 $this->last_query = $this->prepared_queries[$stmt];
990 return $this->raiseError(DB_ERROR_MISMATCH);
993 $realquery = $this->prepare_tokens[$stmt][0];
996 foreach ($data as $value) {
997 if ($this->prepare_types[$stmt][$i] == DB_PARAM_SCALAR) {
998 $realquery .= $this->quoteSmart($value);
999 } elseif ($this->prepare_types[$stmt][$i] == DB_PARAM_OPAQUE) {
1000 $fp = @fopen($value, 'rb');
1002 return $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
1004 $realquery .= $this->quoteSmart(fread($fp, filesize($value)));
1007 $realquery .= $value;
1010 $realquery .= $this->prepare_tokens[$stmt][++$i];
1017 // {{{ executeMultiple()
1020 * This function does several execute() calls on the same
1023 * $data must be an array indexed numerically
1024 * from 0, one execute call is done for every "row" in the array.
1026 * If an error occurs during execute(), executeMultiple() does not
1027 * execute the unfinished rows, but rather returns that error.
1029 * @param resource $stmt query handle from prepare()
1030 * @param array $data numeric array containing the
1031 * data to insert into the query
1033 * @return mixed DB_OK or DB_Error
1035 * @see DB_common::prepare(), DB_common::execute()
1038 function executeMultiple($stmt, $data)
1040 foreach ($data as $value) {
1041 $res =& $this->execute($stmt, $value);
1042 if (DB::isError($res)) {
1050 // {{{ freePrepared()
1053 * Free the resource used in a prepared query
1055 * @param $stmt The resurce returned by the prepare() function
1056 * @see DB_common::prepare()
1058 function freePrepared($stmt)
1060 // Free the internal prepared vars
1061 if (isset($this->prepare_tokens[$stmt])) {
1062 unset($this->prepare_tokens[$stmt]);
1063 unset($this->prepare_types[$stmt]);
1064 unset($this->prepared_queries[$stmt]);
1071 // {{{ modifyQuery()
1074 * This method is used by backends to alter queries for various
1077 * It is defined here to assure that all implementations
1078 * have this method defined.
1080 * @param string $query query to modify
1082 * @return the new (modified) query
1086 function modifyQuery($query) {
1091 // {{{ modifyLimitQuery()
1094 * This method is used by backends to alter limited queries
1096 * @param string $query query to modify
1097 * @param integer $from the row to start to fetching
1098 * @param integer $count the numbers of rows to fetch
1100 * @return the new (modified) query
1104 function modifyLimitQuery($query, $from, $count)
1113 * Send a query to the database and return any results with a
1116 * The query string can be either a normal statement to be sent directly
1117 * to the server OR if <var>$params</var> are passed the query can have
1118 * placeholders and it will be passed through prepare() and execute().
1120 * @param string $query the SQL query or the statement to prepare
1121 * @param mixed $params array, string or numeric data to be used in
1122 * execution of the statement. Quantity of items
1123 * passed must match quantity of placeholders in
1124 * query: meaning 1 placeholder for non-array
1125 * parameters or 1 placeholder per array element.
1127 * @return mixed a DB_result object or DB_OK on success, a DB
1130 * @see DB_result, DB_common::prepare(), DB_common::execute()
1133 function &query($query, $params = array())
1135 if (sizeof($params) > 0) {
1136 $sth = $this->prepare($query);
1137 if (DB::isError($sth)) {
1140 $ret = $this->execute($sth, $params);
1141 $this->freePrepared($sth);
1144 $result = $this->simpleQuery($query);
1145 if (DB::isError($result) || $result === DB_OK) {
1148 $tmp =& new DB_result($this, $result);
1158 * Generates a limited query
1160 * @param string $query query
1161 * @param integer $from the row to start to fetching
1162 * @param integer $count the numbers of rows to fetch
1163 * @param array $params required for a statement
1165 * @return mixed a DB_Result object, DB_OK or a DB_Error
1169 function &limitQuery($query, $from, $count, $params = array())
1171 $query = $this->modifyLimitQuery($query, $from, $count);
1172 if (DB::isError($query)){
1175 $result =& $this->query($query, $params);
1176 if (is_a($result, 'DB_result')) {
1177 $result->setOption('limit_from', $from);
1178 $result->setOption('limit_count', $count);
1187 * Fetch the first column of the first row of data returned from
1190 * Takes care of doing the query and freeing the results when finished.
1192 * @param string $query the SQL query
1193 * @param mixed $params array, string or numeric data to be used in
1194 * execution of the statement. Quantity of items
1195 * passed must match quantity of placeholders in
1196 * query: meaning 1 placeholder for non-array
1197 * parameters or 1 placeholder per array element.
1199 * @return mixed the returned value of the query. DB_Error on failure.
1203 function &getOne($query, $params = array())
1205 settype($params, 'array');
1206 if (sizeof($params) > 0) {
1207 $sth = $this->prepare($query);
1208 if (DB::isError($sth)) {
1211 $res =& $this->execute($sth, $params);
1212 $this->freePrepared($sth);
1214 $res =& $this->query($query);
1217 if (DB::isError($res)) {
1221 $err = $res->fetchInto($row, DB_FETCHMODE_ORDERED);
1224 if ($err !== DB_OK) {
1235 * Fetch the first row of data returned from a query
1237 * Takes care of doing the query and freeing the results when finished.
1239 * @param string $query the SQL query
1240 * @param array $params array to be used in execution of the statement.
1241 * Quantity of array elements must match quantity
1242 * of placeholders in query. This function does
1243 * NOT support scalars.
1244 * @param int $fetchmode the fetch mode to use
1246 * @return array the first row of results as an array indexed from
1247 * 0, or a DB error code.
1251 function &getRow($query,
1253 $fetchmode = DB_FETCHMODE_DEFAULT)
1255 // compat check, the params and fetchmode parameters used to
1256 // have the opposite order
1257 if (!is_array($params)) {
1258 if (is_array($fetchmode)) {
1259 if ($params === null) {
1260 $tmp = DB_FETCHMODE_DEFAULT;
1264 $params = $fetchmode;
1266 } elseif ($params !== null) {
1267 $fetchmode = $params;
1272 if (sizeof($params) > 0) {
1273 $sth = $this->prepare($query);
1274 if (DB::isError($sth)) {
1277 $res =& $this->execute($sth, $params);
1278 $this->freePrepared($sth);
1280 $res =& $this->query($query);
1283 if (DB::isError($res)) {
1287 $err = $res->fetchInto($row, $fetchmode);
1291 if ($err !== DB_OK) {
1302 * Fetch a single column from a result set and return it as an
1305 * @param string $query the SQL query
1306 * @param mixed $col which column to return (integer [column number,
1307 * starting at 0] or string [column name])
1308 * @param mixed $params array, string or numeric data to be used in
1309 * execution of the statement. Quantity of items
1310 * passed must match quantity of placeholders in
1311 * query: meaning 1 placeholder for non-array
1312 * parameters or 1 placeholder per array element.
1314 * @return array an indexed array with the data from the first
1315 * row at index 0, or a DB error code
1317 * @see DB_common::query()
1320 function &getCol($query, $col = 0, $params = array())
1322 settype($params, 'array');
1323 if (sizeof($params) > 0) {
1324 $sth = $this->prepare($query);
1326 if (DB::isError($sth)) {
1330 $res =& $this->execute($sth, $params);
1331 $this->freePrepared($sth);
1333 $res =& $this->query($query);
1336 if (DB::isError($res)) {
1340 $fetchmode = is_int($col) ? DB_FETCHMODE_ORDERED : DB_FETCHMODE_ASSOC;
1343 while (is_array($row = $res->fetchRow($fetchmode))) {
1344 $ret[] = $row[$col];
1349 if (DB::isError($row)) {
1360 * Fetch the entire result set of a query and return it as an
1361 * associative array using the first column as the key
1363 * If the result set contains more than two columns, the value
1364 * will be an array of the values from column 2-n. If the result
1365 * set contains only two columns, the returned value will be a
1366 * scalar with the value of the second column (unless forced to an
1367 * array with the $force_array parameter). A DB error code is
1368 * returned on errors. If the result set contains fewer than two
1369 * columns, a DB_ERROR_TRUNCATED error is returned.
1371 * For example, if the table "mytable" contains:
1375 * --------------------------------
1378 * 3 'three' 944679408
1381 * Then the call getAssoc('SELECT id,text FROM mytable') returns:
1390 * ...while the call getAssoc('SELECT id,text,date FROM mytable') returns:
1393 * '1' => array('one', '944679408'),
1394 * '2' => array('two', '944679408'),
1395 * '3' => array('three', '944679408')
1399 * If the more than one row occurs with the same value in the
1400 * first column, the last row overwrites all previous ones by
1401 * default. Use the $group parameter if you don't want to
1402 * overwrite like this. Example:
1405 * getAssoc('SELECT category,id,name FROM mytable', false, null,
1406 * DB_FETCHMODE_ASSOC, true) returns:
1409 * '1' => array(array('id' => '4', 'name' => 'number four'),
1410 * array('id' => '6', 'name' => 'number six')
1412 * '9' => array(array('id' => '4', 'name' => 'number four'),
1413 * array('id' => '6', 'name' => 'number six')
1418 * Keep in mind that database functions in PHP usually return string
1419 * values for results regardless of the database's internal type.
1421 * @param string $query the SQL query
1422 * @param boolean $force_array used only when the query returns
1423 * exactly two columns. If true, the values
1424 * of the returned array will be one-element
1425 * arrays instead of scalars.
1426 * @param mixed $params array, string or numeric data to be used in
1427 * execution of the statement. Quantity of items
1428 * passed must match quantity of placeholders in
1429 * query: meaning 1 placeholder for non-array
1430 * parameters or 1 placeholder per array element.
1431 * @param boolean $group if true, the values of the returned array
1432 * is wrapped in another array. If the same
1433 * key value (in the first column) repeats
1434 * itself, the values will be appended to
1435 * this array instead of overwriting the
1438 * @return array associative array with results from the query.
1439 * DB Error on failure.
1443 function &getAssoc($query, $force_array = false, $params = array(),
1444 $fetchmode = DB_FETCHMODE_DEFAULT, $group = false)
1446 settype($params, 'array');
1447 if (sizeof($params) > 0) {
1448 $sth = $this->prepare($query);
1450 if (DB::isError($sth)) {
1454 $res =& $this->execute($sth, $params);
1455 $this->freePrepared($sth);
1457 $res =& $this->query($query);
1460 if (DB::isError($res)) {
1463 if ($fetchmode == DB_FETCHMODE_DEFAULT) {
1464 $fetchmode = $this->fetchmode;
1466 $cols = $res->numCols();
1469 $tmp =& $this->raiseError(DB_ERROR_TRUNCATED);
1475 if ($cols > 2 || $force_array) {
1476 // return array values
1477 // XXX this part can be optimized
1478 if ($fetchmode == DB_FETCHMODE_ASSOC) {
1479 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ASSOC))) {
1481 $key = current($row);
1482 unset($row[key($row)]);
1484 $results[$key][] = $row;
1486 $results[$key] = $row;
1489 } elseif ($fetchmode == DB_FETCHMODE_OBJECT) {
1490 while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) {
1491 $arr = get_object_vars($row);
1492 $key = current($arr);
1494 $results[$key][] = $row;
1496 $results[$key] = $row;
1500 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ORDERED))) {
1501 // we shift away the first element to get
1502 // indices running from 0 again
1503 $key = array_shift($row);
1505 $results[$key][] = $row;
1507 $results[$key] = $row;
1511 if (DB::isError($row)) {
1515 // return scalar values
1516 while (is_array($row = $res->fetchRow(DB_FETCHMODE_ORDERED))) {
1518 $results[$row[0]][] = $row[1];
1520 $results[$row[0]] = $row[1];
1523 if (DB::isError($row)) {
1537 * Fetch all the rows returned from a query
1539 * @param string $query the SQL query
1540 * @param array $params array to be used in execution of the statement.
1541 * Quantity of array elements must match quantity
1542 * of placeholders in query. This function does
1543 * NOT support scalars.
1544 * @param int $fetchmode the fetch mode to use
1546 * @return array an nested array. DB error on failure.
1550 function &getAll($query,
1552 $fetchmode = DB_FETCHMODE_DEFAULT)
1554 // compat check, the params and fetchmode parameters used to
1555 // have the opposite order
1556 if (!is_array($params)) {
1557 if (is_array($fetchmode)) {
1558 if ($params === null) {
1559 $tmp = DB_FETCHMODE_DEFAULT;
1563 $params = $fetchmode;
1565 } elseif ($params !== null) {
1566 $fetchmode = $params;
1571 if (sizeof($params) > 0) {
1572 $sth = $this->prepare($query);
1574 if (DB::isError($sth)) {
1578 $res =& $this->execute($sth, $params);
1579 $this->freePrepared($sth);
1581 $res =& $this->query($query);
1584 if (DB::isError($res) || $res == DB_OK) {
1589 while (DB_OK === $res->fetchInto($row, $fetchmode)) {
1590 if ($fetchmode & DB_FETCHMODE_FLIPPED) {
1591 foreach ($row as $key => $val) {
1592 $results[$key][] = $val;
1601 if (DB::isError($row)) {
1602 $tmp =& $this->raiseError($row);
1612 * enable automatic Commit
1614 * @param boolean $onoff
1615 * @return mixed DB_Error
1619 function autoCommit($onoff=false)
1621 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1630 * @return mixed DB_Error
1636 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1645 * @return mixed DB_Error
1651 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1658 * Returns the number of rows in a result object
1660 * @param object DB_Result the result object to check
1662 * @return mixed DB_Error or the number of rows
1666 function numRows($result)
1668 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1672 // {{{ affectedRows()
1675 * Returns the affected rows of a query
1677 * @return mixed DB_Error or number of rows
1681 function affectedRows()
1683 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1687 // {{{ errorNative()
1690 * Returns an errormessage, provides by the database
1692 * @return mixed DB_Error or message
1696 function errorNative()
1698 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1702 // {{{ getSequenceName()
1705 * Generate the name used inside the database for a sequence
1707 * The createSequence() docblock contains notes about storing sequence
1710 * @param string $sqn the sequence's public name
1712 * @return string the sequence's name in the backend
1714 * @see DB_common::createSequence(), DB_common::dropSequence(),
1715 * DB_common::nextID(), DB_common::setOption()
1718 function getSequenceName($sqn)
1720 return sprintf($this->getOption('seqname_format'),
1721 preg_replace('/[^a-z0-9_.]/i', '_', $sqn));
1728 * Returns the next free id in a sequence
1730 * @param string $seq_name name of the sequence
1731 * @param boolean $ondemand when true, the seqence is automatically
1732 * created if it does not exist
1734 * @return int the next id number in the sequence. DB_Error if problem.
1736 * @see DB_common::createSequence(), DB_common::dropSequence(),
1737 * DB_common::getSequenceName()
1740 function nextId($seq_name, $ondemand = true)
1742 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1746 // {{{ createSequence()
1749 * Creates a new sequence
1751 * The name of a given sequence is determined by passing the string
1752 * provided in the <var>$seq_name</var> argument through PHP's sprintf()
1753 * function using the value from the <var>seqname_format</var> option as
1754 * the sprintf()'s format argument.
1756 * <var>seqname_format</var> is set via setOption().
1758 * @param string $seq_name name of the new sequence
1760 * @return int DB_OK on success. A DB_Error object is returned if
1763 * @see DB_common::dropSequence(), DB_common::getSequenceName(),
1764 * DB_common::nextID()
1767 function createSequence($seq_name)
1769 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1773 // {{{ dropSequence()
1776 * Deletes a sequence
1778 * @param string $seq_name name of the sequence to be deleted
1780 * @return int DB_OK on success. DB_Error if problems.
1782 * @see DB_common::createSequence(), DB_common::getSequenceName(),
1783 * DB_common::nextID()
1786 function dropSequence($seq_name)
1788 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1795 * Returns information about a table or a result set
1797 * The format of the resulting array depends on which <var>$mode</var>
1798 * you select. The sample output below is based on this query:
1800 * SELECT tblFoo.fldID, tblFoo.fldPhone, tblBar.fldId
1802 * JOIN tblBar ON tblFoo.fldId = tblBar.fldId
1808 * <kbd>null</kbd> (default)
1815 * [flags] => primary_key not_null
1819 * [name] => fldPhone
1829 * [flags] => primary_key not_null
1835 * <kbd>DB_TABLEINFO_ORDER</kbd>
1837 * <p>In addition to the information found in the default output,
1838 * a notation of the number of columns is provided by the
1839 * <samp>num_fields</samp> element while the <samp>order</samp>
1840 * element provides an array with the column names as the keys and
1841 * their location index number (corresponding to the keys in the
1842 * the default output) as the values.</p>
1844 * <p>If a result set has identical field names, the last one is
1849 * [order] => Array (
1857 * <kbd>DB_TABLEINFO_ORDERTABLE</kbd>
1859 * <p>Similar to <kbd>DB_TABLEINFO_ORDER</kbd> but adds more
1860 * dimensions to the array in which the table names are keys and
1861 * the field names are sub-keys. This is helpful for queries that
1862 * join tables which have identical field names.</p>
1866 * [ordertable] => Array (
1867 * [tblFoo] => Array (
1871 * [tblBar] => Array (
1880 * The <samp>flags</samp> element contains a space separated list
1881 * of extra information about the field. This data is inconsistent
1882 * between DBMS's due to the way each DBMS works.
1883 * + <samp>primary_key</samp>
1884 * + <samp>unique_key</samp>
1885 * + <samp>multiple_key</samp>
1886 * + <samp>not_null</samp>
1888 * Most DBMS's only provide the <samp>table</samp> and <samp>flags</samp>
1889 * elements if <var>$result</var> is a table name. The following DBMS's
1890 * provide full information from queries:
1894 * If the 'portability' option has <samp>DB_PORTABILITY_LOWERCASE</samp>
1895 * turned on, the names of tables and fields will be lowercased.
1897 * @param object|string $result DB_result object from a query or a
1898 * string containing the name of a table.
1899 * While this also accepts a query result
1900 * resource identifier, this behavior is
1902 * @param int $mode either unused or one of the tableInfo modes:
1903 * <kbd>DB_TABLEINFO_ORDERTABLE</kbd>,
1904 * <kbd>DB_TABLEINFO_ORDER</kbd> or
1905 * <kbd>DB_TABLEINFO_FULL</kbd> (which does both).
1906 * These are bitwise, so the first two can be
1907 * combined using <kbd>|</kbd>.
1908 * @return array an associative array with the information requested.
1909 * If something goes wrong an error object is returned.
1911 * @see DB_common::setOption()
1914 function tableInfo($result, $mode = null)
1917 * If the DB_<driver> class has a tableInfo() method, that one
1918 * overrides this one. But, if the driver doesn't have one,
1919 * this method runs and tells users about that fact.
1921 return $this->raiseError(DB_ERROR_NOT_CAPABLE);
1928 * @deprecated Deprecated in release 1.2 or lower
1930 function getTables()
1932 return $this->getListOf('tables');
1939 * list internal DB info
1940 * valid values for $type are db dependent,
1941 * often: databases, users, view, functions
1943 * @param string $type type of requested info
1945 * @return mixed DB_Error or the requested data
1949 function getListOf($type)
1951 $sql = $this->getSpecialQuery($type);
1952 if ($sql === null) { // No support
1953 return $this->raiseError(DB_ERROR_UNSUPPORTED);
1954 } elseif (is_int($sql) || DB::isError($sql)) { // Previous error
1955 return $this->raiseError($sql);
1956 } elseif (is_array($sql)) { // Already the result
1959 return $this->getCol($sql); // Launch this query
1963 // {{{ getSpecialQuery()
1966 * Returns the query needed to get some backend info
1968 * @param string $type What kind of info you want to retrieve
1970 * @return string The SQL query string
1974 function getSpecialQuery($type)
1976 return $this->raiseError(DB_ERROR_UNSUPPORTED);
1980 // {{{ _rtrimArrayValues()
1983 * Right trim all strings in an array
1985 * @param array $array the array to be trimmed (passed by reference)
1989 function _rtrimArrayValues(&$array)
1991 foreach ($array as $key => $value) {
1992 if (is_string($value)) {
1993 $array[$key] = rtrim($value);
1999 // {{{ _convertNullArrayValuesToEmpty()
2002 * Convert all null values in an array to empty strings
2004 * @param array $array the array to be de-nullified (passed by reference)
2008 function _convertNullArrayValuesToEmpty(&$array)
2010 foreach ($array as $key => $value) {
2011 if (is_null($value)) {