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: Sterling Hughes <sterling@php.net> |
17 // | Maintainer: Daniel Convissor <danielc@php.net> |
18 // +----------------------------------------------------------------------+
21 // - If dbsyntax is not firebird, the limitQuery may fail
23 require_once 'DB/common.php';
26 * Database independent query interface definition for PHP's Interbase
32 * @author Sterling Hughes <sterling@php.net>
34 class DB_ibase extends DB_common
40 var $phptype, $dbsyntax;
42 var $manip_query = array();
50 $this->phptype = 'ibase';
51 $this->dbsyntax = 'ibase';
52 $this->features = array(
55 'transactions' => true,
58 // just a few of the tons of Interbase error codes listed in the
59 // Language Reference section of the Interbase manual
60 $this->errorcode_map = array(
61 -104 => DB_ERROR_SYNTAX,
62 -150 => DB_ERROR_ACCESS_VIOLATION,
63 -151 => DB_ERROR_ACCESS_VIOLATION,
64 -155 => DB_ERROR_NOSUCHTABLE,
65 88 => DB_ERROR_NOSUCHTABLE,
66 -157 => DB_ERROR_NOSUCHFIELD,
67 -158 => DB_ERROR_VALUE_COUNT_ON_ROW,
68 -170 => DB_ERROR_MISMATCH,
69 -171 => DB_ERROR_MISMATCH,
70 -172 => DB_ERROR_INVALID,
71 -204 => DB_ERROR_INVALID,
72 -205 => DB_ERROR_NOSUCHFIELD,
73 -206 => DB_ERROR_NOSUCHFIELD,
74 -208 => DB_ERROR_INVALID,
75 -219 => DB_ERROR_NOSUCHTABLE,
76 -297 => DB_ERROR_CONSTRAINT,
77 -530 => DB_ERROR_CONSTRAINT,
78 -607 => DB_ERROR_NOSUCHTABLE,
79 -803 => DB_ERROR_CONSTRAINT,
80 -551 => DB_ERROR_ACCESS_VIOLATION,
81 -552 => DB_ERROR_ACCESS_VIOLATION,
82 -922 => DB_ERROR_NOSUCHDB,
83 -923 => DB_ERROR_CONNECT_FAILED,
84 -924 => DB_ERROR_CONNECT_FAILED
91 function connect($dsninfo, $persistent = false)
93 if (!DB::assertExtension('interbase')) {
94 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
96 $this->dsn = $dsninfo;
97 $dbhost = $dsninfo['hostspec'] ?
98 ($dsninfo['hostspec'] . ':' . $dsninfo['database']) :
101 $connect_function = $persistent ? 'ibase_pconnect' : 'ibase_connect';
105 $params[] = $dsninfo['username'] ? $dsninfo['username'] : null;
106 $params[] = $dsninfo['password'] ? $dsninfo['password'] : null;
107 $params[] = isset($dsninfo['charset']) ? $dsninfo['charset'] : null;
108 $params[] = isset($dsninfo['buffers']) ? $dsninfo['buffers'] : null;
109 $params[] = isset($dsninfo['dialect']) ? $dsninfo['dialect'] : null;
110 $params[] = isset($dsninfo['role']) ? $dsninfo['role'] : null;
112 $conn = @call_user_func_array($connect_function, $params);
114 return $this->ibaseRaiseError(DB_ERROR_CONNECT_FAILED);
116 $this->connection = $conn;
117 if ($this->dsn['dbsyntax'] == 'firebird') {
118 $this->features['limit'] = 'alter';
126 function disconnect()
128 $ret = @ibase_close($this->connection);
129 $this->connection = null;
136 function simpleQuery($query)
138 $ismanip = DB::isManip($query);
139 $this->last_query = $query;
140 $query = $this->modifyQuery($query);
141 $result = @ibase_query($this->connection, $query);
143 return $this->ibaseRaiseError();
145 if ($this->autocommit && $ismanip) {
146 @ibase_commit($this->connection);
148 // Determine which queries that should return data, and which
149 // should return an error code only.
150 return $ismanip ? DB_OK : $result;
154 // {{{ modifyLimitQuery()
157 * This method is used by backends to alter limited queries
158 * Uses the new FIRST n SKIP n Firebird 1.0 syntax, so it is
159 * only compatible with Firebird 1.x
161 * @param string $query query to modify
162 * @param integer $from the row to start to fetching
163 * @param integer $count the numbers of rows to fetch
165 * @return the new (modified) query
166 * @author Ludovico Magnocavallo <ludo@sumatrasolutions.com>
169 function modifyLimitQuery($query, $from, $count)
171 if ($this->dsn['dbsyntax'] == 'firebird') {
172 //$from++; // SKIP starts from 1, ie SKIP 1 starts from the first record
173 // (cox) Seems that SKIP starts in 0
174 $query = preg_replace('/^\s*select\s(.*)$/is',
175 "SELECT FIRST $count SKIP $from $1", $query);
184 * Move the internal ibase result pointer to the next available result
186 * @param a valid fbsql result resource
190 * @return true if a result is available otherwise return false
192 function nextResult($result)
201 * Fetch a row and insert the data into an existing array.
203 * Formating of the array and the data therein are configurable.
204 * See DB_result::fetchInto() for more information.
206 * @param resource $result query result identifier
207 * @param array $arr (reference) array where data from the row
209 * @param int $fetchmode how the resulting array should be indexed
210 * @param int $rownum the row number to fetch
212 * @return mixed DB_OK on success, null when end of result set is
213 * reached or on failure
215 * @see DB_result::fetchInto()
218 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
220 if ($rownum !== null) {
221 return $this->ibaseRaiseError(DB_ERROR_NOT_CAPABLE);
223 if ($fetchmode & DB_FETCHMODE_ASSOC) {
224 if (function_exists('ibase_fetch_assoc')) {
225 $arr = @ibase_fetch_assoc($result);
227 $arr = get_object_vars(ibase_fetch_object($result));
229 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
230 $arr = array_change_key_case($arr, CASE_LOWER);
233 $arr = @ibase_fetch_row($result);
236 if ($errmsg = @ibase_errmsg()) {
237 return $this->ibaseRaiseError(null, $errmsg);
242 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
243 $this->_rtrimArrayValues($arr);
245 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
246 $this->_convertNullArrayValuesToEmpty($arr);
254 function freeResult($result)
256 return @ibase_free_result($result);
262 function freeQuery($query)
264 @ibase_free_query($query);
271 function numCols($result)
273 $cols = @ibase_num_fields($result);
275 return $this->ibaseRaiseError();
284 * Prepares a query for multiple execution with execute().
286 * prepare() requires a generic query as string like <code>
287 * INSERT INTO numbers VALUES (?, ?, ?)
288 * </code>. The <kbd>?</kbd> characters are placeholders.
290 * Three types of placeholders can be used:
291 * + <kbd>?</kbd> a quoted scalar value, i.e. strings, integers
292 * + <kbd>!</kbd> value is inserted 'as is'
293 * + <kbd>&</kbd> requires a file name. The file's contents get
294 * inserted into the query (i.e. saving binary
297 * Use backslashes to escape placeholder characters if you don't want
298 * them to be interpreted as placeholders. Example: <code>
299 * "UPDATE foo SET col=? WHERE col='over \& under'"
302 * @param string $query query to be prepared
303 * @return mixed DB statement resource on success. DB_Error on failure.
305 function prepare($query)
307 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
308 PREG_SPLIT_DELIM_CAPTURE);
313 foreach ($tokens as $key => $val) {
316 $types[$token++] = DB_PARAM_SCALAR;
319 $types[$token++] = DB_PARAM_OPAQUE;
322 $types[$token++] = DB_PARAM_MISC;
325 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
326 $newquery .= $tokens[$key] . '?';
330 $newquery = substr($newquery, 0, -1);
331 $this->last_query = $query;
332 $newquery = $this->modifyQuery($newquery);
333 $stmt = @ibase_prepare($this->connection, $newquery);
334 $this->prepare_types[(int)$stmt] = $types;
335 $this->manip_query[(int)$stmt] = DB::isManip($query);
343 * Executes a DB statement prepared with prepare().
345 * @param resource $stmt a DB statement resource returned from prepare()
346 * @param mixed $data array, string or numeric data to be used in
347 * execution of the statement. Quantity of items
348 * passed must match quantity of placeholders in
349 * query: meaning 1 for non-array items or the
350 * quantity of elements in the array.
351 * @return object a new DB_Result or a DB_Error when fail
352 * @see DB_ibase::prepare()
355 function &execute($stmt, $data = array())
357 if (!is_array($data)) {
358 $data = array($data);
361 $types =& $this->prepare_types[$stmt];
362 if (count($types) != count($data)) {
363 $tmp =& $this->raiseError(DB_ERROR_MISMATCH);
368 foreach ($data as $key => $value) {
369 if ($types[$i] == DB_PARAM_MISC) {
371 * ibase doesn't seem to have the ability to pass a
372 * parameter along unchanged, so strip off quotes from start
373 * and end, plus turn two single quotes to one single quote,
374 * in order to avoid the quotes getting escaped by
375 * ibase and ending up in the database.
377 $data[$key] = preg_replace("/^'(.*)'$/", "\\1", $data[$key]);
378 $data[$key] = str_replace("''", "'", $data[$key]);
379 } elseif ($types[$i] == DB_PARAM_OPAQUE) {
380 $fp = @fopen($data[$key], 'rb');
382 $tmp =& $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
385 $data[$key] = fread($fp, filesize($data[$key]));
391 array_unshift($data, $stmt);
393 $res = call_user_func_array('ibase_execute', $data);
395 $tmp =& $this->ibaseRaiseError();
399 if ($this->autocommit && $this->manip_query[(int)$stmt]) {
400 @ibase_commit($this->connection);
402 if ($this->manip_query[(int)$stmt]) {
405 $tmp =& new DB_result($this, $res);
411 * Free the internal resources associated with a prepared query.
413 * @param $stmt The interbase_query resource type
415 * @return bool true on success, false if $result is invalid
417 function freePrepared($stmt)
419 if (!is_resource($stmt)) {
422 @ibase_free_query($stmt);
423 unset($this->prepare_tokens[(int)$stmt]);
424 unset($this->prepare_types[(int)$stmt]);
425 unset($this->manip_query[(int)$stmt]);
432 function autoCommit($onoff = false)
434 $this->autocommit = $onoff ? 1 : 0;
443 return @ibase_commit($this->connection);
451 return @ibase_rollback($this->connection);
455 // {{{ transactionInit()
457 function transactionInit($trans_args = 0)
459 return $trans_args ? @ibase_trans($trans_args, $this->connection) : @ibase_trans();
466 * Returns the next free id in a sequence
468 * @param string $seq_name name of the sequence
469 * @param boolean $ondemand when true, the seqence is automatically
470 * created if it does not exist
472 * @return int the next id number in the sequence. DB_Error if problem.
475 * @see DB_common::nextID()
478 function nextId($seq_name, $ondemand = true)
480 $sqn = strtoupper($this->getSequenceName($seq_name));
483 $this->pushErrorHandling(PEAR_ERROR_RETURN);
484 $result =& $this->query("SELECT GEN_ID(${sqn}, 1) "
485 . 'FROM RDB$GENERATORS '
486 . "WHERE RDB\$GENERATOR_NAME='${sqn}'");
487 $this->popErrorHandling();
488 if ($ondemand && DB::isError($result)) {
490 $result = $this->createSequence($seq_name);
491 if (DB::isError($result)) {
498 if (DB::isError($result)) {
499 return $this->raiseError($result);
501 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
507 // {{{ createSequence()
510 * Create the sequence
512 * @param string $seq_name the name of the sequence
513 * @return mixed DB_OK on success or DB error on error
516 function createSequence($seq_name)
518 $sqn = strtoupper($this->getSequenceName($seq_name));
519 $this->pushErrorHandling(PEAR_ERROR_RETURN);
520 $result = $this->query("CREATE GENERATOR ${sqn}");
521 $this->popErrorHandling();
527 // {{{ dropSequence()
532 * @param string $seq_name the name of the sequence
533 * @return mixed DB_OK on success or DB error on error
536 function dropSequence($seq_name)
538 $sqn = strtoupper($this->getSequenceName($seq_name));
539 return $this->query('DELETE FROM RDB$GENERATORS '
540 . "WHERE RDB\$GENERATOR_NAME='${sqn}'");
544 // {{{ _ibaseFieldFlags()
547 * get the Flags of a Field
549 * @param string $field_name the name of the field
550 * @param string $table_name the name of the table
552 * @return string The flags of the field ("primary_key", "unique_key", "not_null"
553 * "default", "computed" and "blob" are supported)
556 function _ibaseFieldFlags($field_name, $table_name)
558 $sql = 'SELECT R.RDB$CONSTRAINT_TYPE CTYPE'
559 .' FROM RDB$INDEX_SEGMENTS I'
560 .' JOIN RDB$RELATION_CONSTRAINTS R ON I.RDB$INDEX_NAME=R.RDB$INDEX_NAME'
561 .' WHERE I.RDB$FIELD_NAME=\'' . $field_name . '\''
562 .' AND UPPER(R.RDB$RELATION_NAME)=\'' . strtoupper($table_name) . '\'';
564 $result = @ibase_query($this->connection, $sql);
566 return $this->ibaseRaiseError();
570 if ($obj = @ibase_fetch_object($result)) {
571 @ibase_free_result($result);
572 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'PRIMARY KEY') {
573 $flags .= 'primary_key ';
575 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'UNIQUE') {
576 $flags .= 'unique_key ';
580 $sql = 'SELECT R.RDB$NULL_FLAG AS NFLAG,'
581 .' R.RDB$DEFAULT_SOURCE AS DSOURCE,'
582 .' F.RDB$FIELD_TYPE AS FTYPE,'
583 .' F.RDB$COMPUTED_SOURCE AS CSOURCE'
584 .' FROM RDB$RELATION_FIELDS R '
585 .' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME'
586 .' WHERE UPPER(R.RDB$RELATION_NAME)=\'' . strtoupper($table_name) . '\''
587 .' AND R.RDB$FIELD_NAME=\'' . $field_name . '\'';
589 $result = @ibase_query($this->connection, $sql);
591 return $this->ibaseRaiseError();
593 if ($obj = @ibase_fetch_object($result)) {
594 @ibase_free_result($result);
595 if (isset($obj->NFLAG)) {
596 $flags .= 'not_null ';
598 if (isset($obj->DSOURCE)) {
599 $flags .= 'default ';
601 if (isset($obj->CSOURCE)) {
602 $flags .= 'computed ';
604 if (isset($obj->FTYPE) && $obj->FTYPE == 261) {
616 * Returns information about a table or a result set.
618 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
621 * @param object|string $result DB_result object from a query or a
622 * string containing the name of a table
623 * @param int $mode a valid tableInfo mode
624 * @return array an associative array with the information requested
625 * or an error object if something is wrong
628 * @see DB_common::tableInfo()
630 function tableInfo($result, $mode = null)
632 if (isset($result->result)) {
634 * Probably received a result object.
635 * Extract the result resource identifier.
637 $id = $result->result;
639 } elseif (is_string($result)) {
641 * Probably received a table name.
642 * Create a result resource identifier.
644 $id = @ibase_query($this->connection,
645 "SELECT * FROM $result WHERE 1=0");
649 * Probably received a result resource identifier.
651 * Depricated. Here for compatibility only.
657 if (!is_resource($id)) {
658 return $this->ibaseRaiseError(DB_ERROR_NEED_MORE_DATA);
661 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
662 $case_func = 'strtolower';
664 $case_func = 'strval';
667 $count = @ibase_num_fields($id);
669 // made this IF due to performance (one if is faster than $count if's)
671 for ($i=0; $i<$count; $i++) {
672 $info = @ibase_field_info($id, $i);
673 $res[$i]['table'] = $got_string ? $case_func($result) : '';
674 $res[$i]['name'] = $case_func($info['name']);
675 $res[$i]['type'] = $info['type'];
676 $res[$i]['len'] = $info['length'];
677 $res[$i]['flags'] = ($got_string) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
680 $res['num_fields']= $count;
682 for ($i=0; $i<$count; $i++) {
683 $info = @ibase_field_info($id, $i);
684 $res[$i]['table'] = $got_string ? $case_func($result) : '';
685 $res[$i]['name'] = $case_func($info['name']);
686 $res[$i]['type'] = $info['type'];
687 $res[$i]['len'] = $info['length'];
688 $res[$i]['flags'] = ($got_string) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
690 if ($mode & DB_TABLEINFO_ORDER) {
691 $res['order'][$res[$i]['name']] = $i;
693 if ($mode & DB_TABLEINFO_ORDERTABLE) {
694 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
699 // free the result only if we were called on a table
701 @ibase_free_result($id);
707 // {{{ ibaseRaiseError()
710 * Gather information about an error, then use that info to create a
711 * DB error object and finally return that object.
713 * @param integer $db_errno PEAR error number (usually a DB constant) if
714 * manually raising an error
715 * @param string $native_errmsg text of error message if known
716 * @return object DB error object
717 * @see DB_common::errorCode()
718 * @see DB_common::raiseError()
720 function &ibaseRaiseError($db_errno = null, $native_errmsg = null)
722 if ($native_errmsg === null) {
723 $native_errmsg = @ibase_errmsg();
725 // memo for the interbase php module hackers: we need something similar
726 // to mysql_errno() to retrieve error codes instead of this ugly hack
727 if (preg_match('/^([^0-9\-]+)([0-9\-]+)\s+(.*)$/', $native_errmsg, $m)) {
728 $native_errno = (int)$m[2];
730 $native_errno = null;
732 // try to map the native error to the DB one
733 if ($db_errno === null) {
735 // try to interpret Interbase error code (that's why we need ibase_errno()
736 // in the interbase module to return the real error code)
737 switch ($native_errno) {
739 if (is_int(strpos($m[3], 'Table unknown'))) {
740 $db_errno = DB_ERROR_NOSUCHTABLE;
744 $db_errno = $this->errorCode($native_errno);
747 $error_regexps = array(
748 '/[tT]able not found/' => DB_ERROR_NOSUCHTABLE,
749 '/[tT]able .* already exists/' => DB_ERROR_ALREADY_EXISTS,
750 '/validation error for column .* value "\*\*\* null/' => DB_ERROR_CONSTRAINT_NOT_NULL,
751 '/violation of [\w ]+ constraint/' => DB_ERROR_CONSTRAINT,
752 '/conversion error from string/' => DB_ERROR_INVALID_NUMBER,
753 '/no permission for/' => DB_ERROR_ACCESS_VIOLATION,
754 '/arithmetic exception, numeric overflow, or string truncation/' => DB_ERROR_DIVZERO
756 foreach ($error_regexps as $regexp => $code) {
757 if (preg_match($regexp, $native_errmsg)) {
759 $native_errno = null;
765 $tmp =& $this->raiseError($db_errno, null, null, null, $native_errmsg);