3 // +----------------------------------------------------------------------+
5 // +----------------------------------------------------------------------+
6 // | Copyright (c) 1997-2002 The PHP Group |
7 // +----------------------------------------------------------------------+
8 // | This source file is subject to version 2.02 of the PHP license, |
9 // | that is bundled with this package in the file LICENSE, and is |
10 // | available at through the world-wide-web at |
11 // | http://www.php.net/license/2_02.txt. |
12 // | If you did not receive a copy of the PHP license and are unable to |
13 // | obtain it through the world-wide-web, please send a note to |
14 // | license@php.net so we can mail you a copy immediately. |
15 // +----------------------------------------------------------------------+
16 // | Author: Sterling Hughes <sterling@php.net> |
17 // +----------------------------------------------------------------------+
19 // $Id: ibase.php,v 1.2 2004-04-26 20:44:37 rurban Exp $
21 // Database independent query interface definition for PHP's Interbase
24 // Based on DB 1.3 from the pear.php.net repository.
25 // The only modifications made have been modification of the include paths.
27 rcs_id('$Id: ibase.php,v 1.2 2004-04-26 20:44:37 rurban Exp $');
28 rcs_id('From Pear CVS: Id: ibase.php,v 1.3 2002/05/09 12:29:53 ssb Exp');
30 require_once 'DB/common.php';
32 class DB_ibase extends DB_common
35 var $phptype, $dbsyntax;
37 var $manip_query = array();
42 $this->phptype = 'ibase';
43 $this->dbsyntax = 'ibase';
44 $this->features = array(
47 'transactions' => true,
50 // just a few of the tons of Interbase error codes listed in the
51 // Language Reference section of the Interbase manual
52 $this->errorcode_map = array(
53 -104 => DB_ERROR_SYNTAX,
54 -150 => DB_ERROR_ACCESS_VIOLATION,
55 -151 => DB_ERROR_ACCESS_VIOLATION,
56 -155 => DB_ERROR_NOSUCHTABLE,
57 -157 => DB_ERROR_NOSUCHFIELD,
58 -158 => DB_ERROR_VALUE_COUNT_ON_ROW,
59 -170 => DB_ERROR_MISMATCH,
60 -171 => DB_ERROR_MISMATCH,
61 -172 => DB_ERROR_INVALID,
62 -204 => DB_ERROR_INVALID,
63 -205 => DB_ERROR_NOSUCHFIELD,
64 -206 => DB_ERROR_NOSUCHFIELD,
65 -208 => DB_ERROR_INVALID,
66 -219 => DB_ERROR_NOSUCHTABLE,
67 -297 => DB_ERROR_CONSTRAINT,
68 -530 => DB_ERROR_CONSTRAINT,
69 -803 => DB_ERROR_CONSTRAINT,
70 -551 => DB_ERROR_ACCESS_VIOLATION,
71 -552 => DB_ERROR_ACCESS_VIOLATION,
72 -922 => DB_ERROR_NOSUCHDB,
73 -923 => DB_ERROR_CONNECT_FAILED,
74 -924 => DB_ERROR_CONNECT_FAILED
78 function connect($dsninfo, $persistent = false)
80 if (!DB::assertExtension('interbase')) {
81 return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
83 $this->dsn = $dsninfo;
84 $user = $dsninfo['username'];
85 $pw = $dsninfo['password'];
86 $dbhost = $dsninfo['hostspec'] ?
87 ($dsninfo['hostspec'] . ':/' . $dsninfo['database']) :
90 $connect_function = $persistent ? 'ibase_pconnect' : 'ibase_connect';
94 $params[] = !empty($user) ? $user : null;
95 $params[] = !empty($pw) ? $pw : null;
96 $params[] = isset($dsninfo['charset']) ? $dsninfo['charset'] : null;
97 $params[] = isset($dsninfo['buffers']) ? $dsninfo['buffers'] : null;
98 $params[] = isset($dsninfo['dialect']) ? $dsninfo['dialect'] : null;
99 $params[] = isset($dsninfo['role']) ? $dsninfo['role'] : null;
102 if ($dbhost && $user && $pw) {
103 $conn = $connect_function($dbhost, $user, $pw);
104 } elseif ($dbhost && $user) {
105 $conn = $connect_function($dbhost, $user);
107 $conn = $connect_function($dbhost);
109 return $this->raiseError("no host, user or password");
112 $conn = @call_user_func_array($connect_function, $params);
114 return $this->ibaseRaiseError(DB_ERROR_CONNECT_FAILED);
116 $this->connection = $conn;
120 function disconnect()
122 $ret = @ibase_close($this->connection);
123 $this->connection = null;
127 function simpleQuery($query)
129 $ismanip = DB::isManip($query);
130 $this->last_query = $query;
131 $query = $this->modifyQuery($query);
132 $result = @ibase_query($this->connection, $query);
134 return $this->ibaseRaiseError();
136 if ($this->autocommit && $ismanip) {
137 ibase_commit($this->connection);
139 // Determine which queries that should return data, and which
140 // should return an error code only.
141 return DB::isManip($query) ? DB_OK : $result;
144 // {{{ modifyLimitQuery()
147 * This method is used by backends to alter limited queries
148 * Uses the new FIRST n SKIP n Firebird 1.0 syntax, so it is
149 * only compatible with Firebird 1.x
151 * @param string $query query to modify
152 * @param integer $from the row to start to fetching
153 * @param integer $count the numbers of rows to fetch
155 * @return the new (modified) query
156 * @author Ludovico Magnocavallo <ludo@sumatrasolutions.com>
160 function modifyLimitQuery($query, $from, $count)
162 if ($this->dsn['dbsyntax'] == 'firebird') {
163 $from++; // SKIP starts from 1, ie SKIP 1 starts from the first record
164 $query = preg_replace('/^\s*select\s(.*)$/is',
165 "SELECT FIRST $count SKIP $from $1", $query);
176 * Move the internal ibase result pointer to the next available result
178 * @param a valid fbsql result resource
182 * @return true if a result is available otherwise return false
184 function nextResult($result)
191 function fetchInto($result, &$ar, $fetchmode, $rownum = null)
193 if ($rownum !== NULL) {
194 return $this->ibaseRaiseError(DB_ERROR_NOT_CAPABLE);
196 if ($fetchmode & DB_FETCHMODE_ASSOC) {
197 $ar = get_object_vars(ibase_fetch_object($result));
198 if ($ar && $this->options['optimize'] == 'portability') {
199 $ar = array_change_key_case($ar, CASE_LOWER);
202 $ar = ibase_fetch_row($result);
205 if ($errmsg = ibase_errmsg()) {
206 return $this->ibaseRaiseError(null, $errmsg);
214 function freeResult()
216 if (is_resource($result)) {
217 return ibase_free_result($result);
219 if (!isset($this->prepare_tokens[(int)$result])) {
222 unset($this->prepare_tokens[(int)$result]);
223 unset($this->prepare_types[(int)$result]);
227 function freeQuery($query)
229 ibase_free_query($query);
233 function numCols($result)
235 $cols = ibase_num_fields($result);
237 return $this->ibaseRaiseError();
242 function prepare($query)
244 $this->last_query = $query;
245 $query = $this->modifyQuery($query);
246 $stmt = ibase_prepare($query);
247 $this->manip_query[(int)$stmt] = DB::isManip($query);
251 function execute($stmt, $data = false)
253 $result = ibase_execute($stmt, $data);
255 return $this->ibaseRaiseError();
257 if ($this->autocommit) {
258 ibase_commit($this->connection);
260 return DB::isManip($this->manip_query[(int)$stmt]) ? DB_OK : new DB_result($this, $result);
263 function autoCommit($onoff = false)
265 $this->autocommit = $onoff ? 1 : 0;
271 return ibase_commit($this->connection);
274 function rollback($trans_number)
276 return ibase_rollback($this->connection, $trans_number);
279 function transactionInit($trans_args = 0)
281 return $trans_args ? ibase_trans($trans_args, $this->connection) : ibase_trans();
286 * Get the next value in a sequence.
288 * If the sequence does not exist, it will be created,
289 * unless $ondemand is false.
292 * @param string $seq_name the name of the sequence
293 * @param bool $ondemand whether to create the sequence on demand
294 * @return a sequence integer, or a DB error
296 function nextId($seq_name, $ondemand = true)
298 $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name));
301 $this->pushErrorHandling(PEAR_ERROR_RETURN);
302 $result = $this->query("SELECT GEN_ID(${sqn}_SEQ, 1) FROM RDB\$GENERATORS"
303 ." WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'");
304 $this->popErrorHandling();
305 if ($ondemand && DB::isError($result)) {
307 $result = $this->createSequence($seq_name);
308 if (DB::isError($result)) {
315 if (DB::isError($result)) {
318 $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
324 // {{{ createSequence()
327 * Create the sequence
329 * @param string $seq_name the name of the sequence
330 * @return mixed DB_OK on success or DB error on error
333 function createSequence($seq_name)
335 $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name));
336 $this->pushErrorHandling(PEAR_ERROR_RETURN);
337 $result = $this->query("CREATE GENERATOR ${sqn}_SEQ");
338 $this->popErrorHandling();
344 // {{{ dropSequence()
349 * @param string $seq_name the name of the sequence
350 * @return mixed DB_OK on success or DB error on error
353 function dropSequence($seq_name)
355 $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name));
356 return $this->query("DELETE FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'");
360 // {{{ _ibaseFieldFlags()
363 * get the Flags of a Field
365 * @param string $field_name the name of the field
366 * @param string $table_name the name of the table
368 * @return string The flags of the field ("primary_key", "unique_key", "not_null"
369 * "default", "computed" and "blob" are supported)
372 function _ibaseFieldFlags($field_name, $table_name)
375 $sql = 'SELECT R.RDB$CONSTRAINT_TYPE CTYPE'
376 .' FROM RDB$INDEX_SEGMENTS I'
377 .' JOIN RDB$RELATION_CONSTRAINTS R ON I.RDB$INDEX_NAME=R.RDB$INDEX_NAME'
378 .' WHERE I.RDB$FIELD_NAME=\''.$field_name.'\''
379 .' AND R.RDB$RELATION_NAME=\''.$table_name.'\'';
380 $result = ibase_query($this->connection, $sql);
381 if (empty($result)) {
382 return $this->ibaseRaiseError();
384 if ($obj = @ibase_fetch_object($result)) {
385 ibase_free_result($result);
386 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'PRIMARY KEY') {
387 $flags = 'primary_key ';
389 if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'UNIQUE') {
390 $flags .= 'unique_key ';
394 $sql = 'SELECT R.RDB$NULL_FLAG AS NFLAG,'
395 .' R.RDB$DEFAULT_SOURCE AS DSOURCE,'
396 .' F.RDB$FIELD_TYPE AS FTYPE,'
397 .' F.RDB$COMPUTED_SOURCE AS CSOURCE'
398 .' FROM RDB$RELATION_FIELDS R '
399 .' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME'
400 .' WHERE R.RDB$RELATION_NAME=\''.$table_name.'\''
401 .' AND R.RDB$FIELD_NAME=\''.$field_name.'\'';
402 $result = ibase_query($this->connection, $sql);
403 if (empty($result)) {
404 return $this->ibaseRaiseError();
406 if ($obj = @ibase_fetch_object($result)) {
407 ibase_free_result($result);
408 if (isset($obj->NFLAG)) {
409 $flags .= 'not_null ';
411 if (isset($obj->DSOURCE)) {
412 $flags .= 'default ';
414 if (isset($obj->CSOURCE)) {
415 $flags .= 'computed ';
417 if (isset($obj->FTYPE) && $obj->FTYPE == 261) {
429 * Returns information about a table or a result set
431 * NOTE: doesn't support 'flags'and 'table' if called from a db_result
433 * @param mixed $resource Interbase result identifier or table name
434 * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or
435 * DB_TABLEINFO_ORDER)
437 * @return array An array with all the information
439 function tableInfo($result, $mode = null)
446 * depending on $mode, metadata returns the following values:
448 * - mode is false (default):
450 * [0]["table"] table name
451 * [0]["name"] field name
452 * [0]["type"] field type
453 * [0]["len"] field length
454 * [0]["flags"] field flags
456 * - mode is DB_TABLEINFO_ORDER
458 * ["num_fields"] number of metadata records
459 * [0]["table"] table name
460 * [0]["name"] field name
461 * [0]["type"] field type
462 * [0]["len"] field length
463 * [0]["flags"] field flags
464 * ["order"][field name] index of field named "field name"
465 * The last one is used, if you have a field name, but no index.
466 * Test: if (isset($result['meta']['myfield'])) { ...
468 * - mode is DB_TABLEINFO_ORDERTABLE
469 * the same as above. but additionally
470 * ["ordertable"][table name][field name] index of field
473 * this is, because if you have fields from different
474 * tables with the same field name * they override each
475 * other with DB_TABLEINFO_ORDER
477 * you can combine DB_TABLEINFO_ORDER and
478 * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER |
479 * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL
482 // if $result is a string, then we want information about a
483 // table without a resultset
485 if (is_string($result)) {
486 $id = ibase_query($this->connection,"SELECT * FROM $result");
488 return $this->ibaseRaiseError();
490 } else { // else we want information about a resultset
493 return $this->ibaseRaiseError();
497 $count = @ibase_num_fields($id);
499 // made this IF due to performance (one if is faster than $count if's)
502 for ($i=0; $i<$count; $i++) {
503 $info = @ibase_field_info($id, $i);
504 $res[$i]['table'] = (is_string($result)) ? $result : '';
505 $res[$i]['name'] = $info['name'];
506 $res[$i]['type'] = $info['type'];
507 $res[$i]['len'] = $info['length'];
508 $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
512 $res["num_fields"]= $count;
514 for ($i=0; $i<$count; $i++) {
515 $info = @ibase_field_info($id, $i);
516 $res[$i]['table'] = (is_string($result)) ? $result : '';
517 $res[$i]['name'] = $info['name'];
518 $res[$i]['type'] = $info['type'];
519 $res[$i]['len'] = $info['length'];
520 $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : '';
521 if ($mode & DB_TABLEINFO_ORDER) {
522 $res['order'][$res[$i]['name']] = $i;
524 if ($mode & DB_TABLEINFO_ORDERTABLE) {
525 $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
530 // free the result only if we were called on a table
531 if (is_resource($id)) {
532 ibase_free_result($id);
538 // {{{ getSpecialQuery()
541 * Returns the query needed to get some backend info
542 * @param string $type What kind of info you want to retrieve
543 * @return string The SQL query string
545 function getSpecialQuery($type)
556 // {{{ ibaseRaiseError()
558 function ibaseRaiseError($errno = null, $errmsg = null)
560 if ($errmsg === null)
561 $errmsg = ibase_errmsg();
562 // memo for the interbase php module hackers: we need something similar
563 // to mysql_errno() to retrieve error codes instead of this ugly hack
564 if (preg_match('/^([^0-9\-]+)([0-9\-]+)\s+(.*)$/', $errmsg, $m)) {
565 if ($errno === null) {
566 $ibase_errno = (int)$m[2];
567 // try to interpret Interbase error code (that's why we need ibase_errno()
568 // in the interbase module to return the real error code)
569 switch ($ibase_errno) {
571 if (is_int(strpos($m[3], 'Table unknown'))) {
572 $errno = DB_ERROR_NOSUCHTABLE;
576 $errno = $this->errorCode($ibase_errno);
579 $errmsg = $m[2] . ' ' . $m[3];
582 return $this->raiseError($errno, null, null, $errmsg,