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 // +----------------------------------------------------------------------+
24 // - If dbsyntax is not firebird, the limitQuery may fail
27 require_once 'DB/common.php';
30 * Database independent query interface definition for PHP's Interbase
36 * @author Sterling Hughes <sterling@php.net>
38 class DB_ibase extends DB_common
44 var $phptype, $dbsyntax;
46 var $manip_query = array();
54 $this->phptype = 'ibase';
55 $this->dbsyntax = 'ibase';
56 $this->features = array(
59 'transactions' => true,
62 // just a few of the tons of Interbase error codes listed in the
63 // Language Reference section of the Interbase manual
64 $this->errorcode_map = array(
65 -104 => DB_ERROR_SYNTAX,
66 -150 => DB_ERROR_ACCESS_VIOLATION,
67 -151 => DB_ERROR_ACCESS_VIOLATION,
68 -155 => DB_ERROR_NOSUCHTABLE,
69 88 => DB_ERROR_NOSUCHTABLE,
70 -157 => DB_ERROR_NOSUCHFIELD,
71 -158 => DB_ERROR_VALUE_COUNT_ON_ROW,
72 -170 => DB_ERROR_MISMATCH,
73 -171 => DB_ERROR_MISMATCH,
74 -172 => DB_ERROR_INVALID,
75 -204 => DB_ERROR_INVALID,
76 -205 => DB_ERROR_NOSUCHFIELD,
77 -206 => DB_ERROR_NOSUCHFIELD,
78 -208 => DB_ERROR_INVALID,
79 -219 => DB_ERROR_NOSUCHTABLE,
80 -297 => DB_ERROR_CONSTRAINT,
81 -530 => DB_ERROR_CONSTRAINT,
82 -607 => DB_ERROR_NOSUCHTABLE,
83 -803 => DB_ERROR_CONSTRAINT,
84 -551 => DB_ERROR_ACCESS_VIOLATION,
85 -552 => DB_ERROR_ACCESS_VIOLATION,
86 -922 => DB_ERROR_NOSUCHDB,
87 -923 => DB_ERROR_CONNECT_FAILED,
88 -924 => DB_ERROR_CONNECT_FAILED
95 function connect($dsninfo, $persistent = false)
97 if (!DB::assertExtension('interbase')) {
98 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
100 $this->dsn = $dsninfo;
101 $dbhost = $dsninfo['hostspec'] ?
102 ($dsninfo['hostspec'] . ':' . $dsninfo['database']) :
103 $dsninfo['database'];
105 $connect_function = $persistent ? 'ibase_pconnect' : 'ibase_connect';
109 $params[] = $dsninfo['username'] ? $dsninfo['username'] : null;
110 $params[] = $dsninfo['password'] ? $dsninfo['password'] : null;
111 $params[] = isset($dsninfo['charset']) ? $dsninfo['charset'] : null;
112 $params[] = isset($dsninfo['buffers']) ? $dsninfo['buffers'] : null;
113 $params[] = isset($dsninfo['dialect']) ? $dsninfo['dialect'] : null;
114 $params[] = isset($dsninfo['role']) ? $dsninfo['role'] : null;
116 $conn = @call_user_func_array($connect_function, $params);
118 return $this->ibaseRaiseError(DB_ERROR_CONNECT_FAILED);
120 $this->connection = $conn;
121 if ($this->dsn['dbsyntax'] == 'firebird') {
122 $this->features['limit'] = 'alter';
130 function disconnect()
132 $ret = @ibase_close($this->connection);
133 $this->connection = null;
140 function simpleQuery($query)
142 $ismanip = DB::isManip($query);
143 $this->last_query = $query;
144 $query = $this->modifyQuery($query);
145 $result = @ibase_query($this->connection, $query);
147 return $this->ibaseRaiseError();
149 if ($this->autocommit && $ismanip) {
150 @ibase_commit($this->connection);
152 // Determine which queries that should return data, and which
153 // should return an error code only.
154 return $ismanip ? DB_OK : $result;
158 // {{{ modifyLimitQuery()
161 * This method is used by backends to alter limited queries
162 * Uses the new FIRST n SKIP n Firebird 1.0 syntax, so it is
163 * only compatible with Firebird 1.x
165 * @param string $query query to modify
166 * @param integer $from the row to start to fetching
167 * @param integer $count the numbers of rows to fetch
169 * @return the new (modified) query
170 * @author Ludovico Magnocavallo <ludo@sumatrasolutions.com>
173 function modifyLimitQuery($query, $from, $count)
175 if ($this->dsn['dbsyntax'] == 'firebird') {
176 //$from++; // SKIP starts from 1, ie SKIP 1 starts from the first record
177 // (cox) Seems that SKIP starts in 0
178 $query = preg_replace('/^\s*select\s(.*)$/is',
179 "SELECT FIRST $count SKIP $from $1", $query);
188 * Move the internal ibase result pointer to the next available result
190 * @param a valid fbsql result resource
194 * @return true if a result is available otherwise return false
196 function nextResult($result)
205 * Fetch a row and insert the data into an existing array.
207 * Formating of the array and the data therein are configurable.
208 * See DB_result::fetchInto() for more information.
210 * @param resource $result query result identifier
211 * @param array $arr (reference) array where data from the row
213 * @param int $fetchmode how the resulting array should be indexed
214 * @param int $rownum the row number to fetch
216 * @return mixed DB_OK on success, null when end of result set is
217 * reached or on failure
219 * @see DB_result::fetchInto()
222 function fetchInto($result, &$arr, $fetchmode, $rownum=null)
224 if ($rownum !== null) {
225 return $this->ibaseRaiseError(DB_ERROR_NOT_CAPABLE);
227 if ($fetchmode & DB_FETCHMODE_ASSOC) {
228 if (function_exists('ibase_fetch_assoc')) {
229 $arr = @ibase_fetch_assoc($result);
231 $arr = get_object_vars(ibase_fetch_object($result));
233 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
234 $arr = array_change_key_case($arr, CASE_LOWER);
237 $arr = @ibase_fetch_row($result);
240 if ($errmsg = @ibase_errmsg()) {
241 return $this->ibaseRaiseError(null, $errmsg);
246 if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
247 $this->_rtrimArrayValues($arr);
249 if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
250 $this->_convertNullArrayValuesToEmpty($arr);
258 function freeResult($result)
260 return @ibase_free_result($result);
266 function freeQuery($query)
268 @ibase_free_query($query);
275 function numCols($result)
277 $cols = @ibase_num_fields($result);
279 return $this->ibaseRaiseError();
288 * Prepares a query for multiple execution with execute().
290 * prepare() requires a generic query as string like <code>
291 * INSERT INTO numbers VALUES (?, ?, ?)
292 * </code>. The <kbd>?</kbd> characters are placeholders.
294 * Three types of placeholders can be used:
295 * + <kbd>?</kbd> a quoted scalar value, i.e. strings, integers
296 * + <kbd>!</kbd> value is inserted 'as is'
297 * + <kbd>&</kbd> requires a file name. The file's contents get
298 * inserted into the query (i.e. saving binary
301 * Use backslashes to escape placeholder characters if you don't want
302 * them to be interpreted as placeholders. Example: <code>
303 * "UPDATE foo SET col=? WHERE col='over \& under'"
306 * @param string $query query to be prepared
307 * @return mixed DB statement resource on success. DB_Error on failure.
309 function prepare($query)
311 $tokens = preg_split('/((?<!\\\)[&?!])/', $query, -1,
312 PREG_SPLIT_DELIM_CAPTURE);
317 foreach ($tokens as $key => $val) {
320 $types[$token++] = DB_PARAM_SCALAR;
323 $types[$token++] = DB_PARAM_OPAQUE;
326 $types[$token++] = DB_PARAM_MISC;
329 $tokens[$key] = preg_replace('/\\\([&?!])/', "\\1", $val);
330 $newquery .= $tokens[$key] . '?';
334 $newquery = substr($newquery, 0, -1);
335 $this->last_query = $query;
336 $newquery = $this->modifyQuery($newquery);
337 $stmt = @ibase_prepare($this->connection, $newquery);
338 $this->prepare_types[(int)$stmt] = $types;
339 $this->manip_query[(int)$stmt] = DB::isManip($query);
347 * Executes a DB statement prepared with prepare().
349 * @param resource $stmt a DB statement resource returned from prepare()
350 * @param mixed $data array, string or numeric data to be used in
351 * execution of the statement. Quantity of items
352 * passed must match quantity of placeholders in
353 * query: meaning 1 for non-array items or the
354 * quantity of elements in the array.
355 * @return object a new DB_Result or a DB_Error when fail
356 * @see DB_ibase::prepare()
359 function &execute($stmt, $data = array())
361 if (!is_array($data)) {
362 $data = array($data);
365 $types =& $this->prepare_types[$stmt];
366 if (count($types) != count($data)) {
367 $tmp =& $this->raiseError(DB_ERROR_MISMATCH);
372 foreach ($data as $key => $value) {
373 if ($types[$i] == DB_PARAM_MISC) {
375 * ibase doesn't seem to have the ability to pass a
376 * parameter along unchanged, so strip off quotes from start
377 * and end, plus turn two single quotes to one single quote,
378 * in order to avoid the quotes getting escaped by
379 * ibase and ending up in the database.
381 $data[$key] = preg_replace("/^'(.*)'$/", "\\1", $data[$key]);
382 $data[$key] = str_replace("''", "'", $data[$key]);
383 } elseif ($types[$i] == DB_PARAM_OPAQUE) {
384 $fp = @fopen($data[$key], 'rb');
386 $tmp =& $this->raiseError(DB_ERROR_ACCESS_VIOLATION);
389 $data[$key] = fread($fp, filesize($data[$key]));
395 array_unshift($data, $stmt);
397 $res = call_user_func_array('ibase_execute', $data);
399 $tmp =& $this->ibaseRaiseError();
403 if ($this->autocommit && $this->manip_query[(int)$stmt]) {
404 @ibase_commit($this->connection);
406 if ($this->manip_query[(int)$stmt]) {
409 $tmp =& new DB_result($this, $res);
415 * Free the internal resources associated with a prepared query.
417 * @param $stmt The interbase_query resource type
419 * @return bool true on success, false if $result is invalid
421 function freePrepared($stmt)
423 if (!is_resource($stmt)) {
426 @ibase_free_query($stmt);
427 unset($this->prepare_tokens[(int)$stmt]);
428 unset($this->prepare_types[(int)$stmt]);
429 unset($this->manip_query[(int)$stmt]);
436 function autoCommit($onoff = false)
438 $this->autocommit = $onoff ? 1 : 0;
447 return @ibase_commit($this->connection);
455 return @ibase_rollback($this->connection);
459 // {{{ transactionInit()
461 function transactionInit($trans_args = 0)
463 return $trans_args ? @ibase_trans($trans_args, $this->connection) : @ibase_trans();
470 * Returns the next free id in a sequence
472 * @param string $seq_name name of the sequence
473 * @param boolean $ondemand when true, the seqence is automatically
474 * created if it does not exist
476 * @return int the next id number in the sequence. DB_Error if problem.
479 * @see DB_common::nextID()
482 function nextId($seq_name, $ondemand = true)
484 $sqn = strtoupper($this->getSequenceName($seq_name));
487 $this->pushErrorHandling(PEAR_ERROR_RETURN);
488 $result =& $this->query("SELECT GEN_ID(${sqn}, 1) "
489 . 'FROM RDB$GENERATORS '
490 . "WHERE RDB\$GENERATOR_NAME='${sqn}'");
491 $this->popErrorHandling();
492 if ($ondemand && DB::isError($result)) {
494 $result = $this->createSequence($seq_name);
495 if (DB::isError($result)) {
502 if (DB::isError($result)) {
503 return $this->raiseError($result);
505 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
511 // {{{ createSequence()
514 * Create the sequence
516 * @param string $seq_name the name of the sequence
517 * @return mixed DB_OK on success or DB error on error
520 function createSequence($seq_name)
522 $sqn = strtoupper($this->getSequenceName($seq_name));
523 $this->pushErrorHandling(PEAR_ERROR_RETURN);
524 $result = $this->query("CREATE GENERATOR ${sqn}");
525 $this->popErrorHandling();
531 // {{{ dropSequence()
536 * @param string $seq_name the name of the sequence
537 * @return mixed DB_OK on success or DB error on error
540 function dropSequence($seq_name)
542 $sqn = strtoupper($this->getSequenceName($seq_name));
543 return $this->query('DELETE FROM RDB$GENERATORS '
544 . "WHERE RDB\$GENERATOR_NAME='${sqn}'");
548 // {{{ _ibaseFieldFlags()
551 * get the Flags of a Field
553 * @param string $field_name the name of the field
554 * @param string $table_name the name of the table
556 * @return string The flags of the field ("primary_key", "unique_key", "not_null"
557 * "default", "computed" and "blob" are supported)
560 function _ibaseFieldFlags($field_name, $table_name)
562 $sql = 'SELECT R.RDB$CONSTRAINT_TYPE CTYPE'
563 .' FROM RDB$INDEX_SEGMENTS I'
564 .' JOIN RDB$RELATION_CONSTRAINTS R ON I.RDB$INDEX_NAME=R.RDB$INDEX_NAME'
565 .' WHERE I.RDB$FIELD_NAME=\'' . $field_name . '\''
566 .' AND UPPER(R.RDB$RELATION_NAME)=\'' . strtoupper($table_name) . '\'';
568 $result = @ibase_query($this->connection, $sql);
570 return $this->ibaseRaiseError();
574 if ($obj = @ibase_fetch_object($result)) {
575 @ibase_free_result($result);
576 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'PRIMARY KEY') {
577 $flags .= 'primary_key ';
579 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'UNIQUE') {
580 $flags .= 'unique_key ';
584 $sql = 'SELECT R.RDB$NULL_FLAG AS NFLAG,'
585 .' R.RDB$DEFAULT_SOURCE AS DSOURCE,'
586 .' F.RDB$FIELD_TYPE AS FTYPE,'
587 .' F.RDB$COMPUTED_SOURCE AS CSOURCE'
588 .' FROM RDB$RELATION_FIELDS R '
589 .' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME'
590 .' WHERE UPPER(R.RDB$RELATION_NAME)=\'' . strtoupper($table_name) . '\''
591 .' AND R.RDB$FIELD_NAME=\'' . $field_name . '\'';
593 $result = @ibase_query($this->connection, $sql);
595 return $this->ibaseRaiseError();
597 if ($obj = @ibase_fetch_object($result)) {
598 @ibase_free_result($result);
599 if (isset($obj->NFLAG)) {
600 $flags .= 'not_null ';
602 if (isset($obj->DSOURCE)) {
603 $flags .= 'default ';
605 if (isset($obj->CSOURCE)) {
606 $flags .= 'computed ';
608 if (isset($obj->FTYPE) && $obj->FTYPE == 261) {
620 * Returns information about a table or a result set.
622 * NOTE: only supports 'table' and 'flags' if <var>$result</var>
625 * @param object|string $result DB_result object from a query or a
626 * string containing the name of a table
627 * @param int $mode a valid tableInfo mode
628 * @return array an associative array with the information requested
629 * or an error object if something is wrong
632 * @see DB_common::tableInfo()
634 function tableInfo($result, $mode = null)
636 if (isset($result->result)) {
638 * Probably received a result object.
639 * Extract the result resource identifier.
641 $id = $result->result;
643 } elseif (is_string($result)) {
645 * Probably received a table name.
646 * Create a result resource identifier.
648 $id = @ibase_query($this->connection,
649 "SELECT * FROM $result WHERE 1=0");
653 * Probably received a result resource identifier.
655 * Depricated. Here for compatibility only.
661 if (!is_resource($id)) {
662 return $this->ibaseRaiseError(DB_ERROR_NEED_MORE_DATA);
665 if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
666 $case_func = 'strtolower';
668 $case_func = 'strval';
671 $count = @ibase_num_fields($id);
673 // made this IF due to performance (one if is faster than $count if's)
675 for ($i=0; $i<$count; $i++) {
676 $info = @ibase_field_info($id, $i);
677 $res[$i]['table'] = $got_string ? $case_func($result) : '';
678 $res[$i]['name'] = $case_func($info['name']);
679 $res[$i]['type'] = $info['type'];
680 $res[$i]['len'] = $info['length'];
681 $res[$i]['flags'] = ($got_string) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
684 $res['num_fields']= $count;
686 for ($i=0; $i<$count; $i++) {
687 $info = @ibase_field_info($id, $i);
688 $res[$i]['table'] = $got_string ? $case_func($result) : '';
689 $res[$i]['name'] = $case_func($info['name']);
690 $res[$i]['type'] = $info['type'];
691 $res[$i]['len'] = $info['length'];
692 $res[$i]['flags'] = ($got_string) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
694 if ($mode & DB_TABLEINFO_ORDER) {
695 $res['order'][$res[$i]['name']] = $i;
697 if ($mode & DB_TABLEINFO_ORDERTABLE) {
698 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
703 // free the result only if we were called on a table
705 @ibase_free_result($id);
711 // {{{ ibaseRaiseError()
714 * Gather information about an error, then use that info to create a
715 * DB error object and finally return that object.
717 * @param integer $db_errno PEAR error number (usually a DB constant) if
718 * manually raising an error
719 * @param string $native_errmsg text of error message if known
720 * @return object DB error object
721 * @see DB_common::errorCode()
722 * @see DB_common::raiseError()
724 function &ibaseRaiseError($db_errno = null, $native_errmsg = null)
726 if ($native_errmsg === null) {
727 $native_errmsg = @ibase_errmsg();
729 // memo for the interbase php module hackers: we need something similar
730 // to mysql_errno() to retrieve error codes instead of this ugly hack
731 if (preg_match('/^([^0-9\-]+)([0-9\-]+)\s+(.*)$/', $native_errmsg, $m)) {
732 $native_errno = (int)$m[2];
734 $native_errno = null;
736 // try to map the native error to the DB one
737 if ($db_errno === null) {
739 // try to interpret Interbase error code (that's why we need ibase_errno()
740 // in the interbase module to return the real error code)
741 switch ($native_errno) {
743 if (is_int(strpos($m[3], 'Table unknown'))) {
744 $db_errno = DB_ERROR_NOSUCHTABLE;
748 $db_errno = $this->errorCode($native_errno);
751 $error_regexps = array(
752 '/[tT]able not found/' => DB_ERROR_NOSUCHTABLE,
753 '/[tT]able .* already exists/' => DB_ERROR_ALREADY_EXISTS,
754 '/validation error for column .* value "\*\*\* null/' => DB_ERROR_CONSTRAINT_NOT_NULL,
755 '/violation of [\w ]+ constraint/' => DB_ERROR_CONSTRAINT,
756 '/conversion error from string/' => DB_ERROR_INVALID_NUMBER,
757 '/no permission for/' => DB_ERROR_ACCESS_VIOLATION,
758 '/arithmetic exception, numeric overflow, or string truncation/' => DB_ERROR_DIVZERO
760 foreach ($error_regexps as $regexp => $code) {
761 if (preg_match($regexp, $native_errmsg)) {
763 $native_errno = null;
769 $tmp =& $this->raiseError($db_errno, null, null, null, $native_errmsg);