DB_ibase enhancement
| From: | Lutz Brueckner | Date: | Sat, 02 Feb 2002 20:25:52 +0000 |
| Subject: | DB_ibase enhancement | ||
| Groups: | php.pear.dev | ||
| Request: | Send a blank email to pear-dev+get-4360@lists.php.net to get a copy of this message | ||
Hello,
the other day I have implemented the missing nextID() and tableInfo() methods
for the DB_ibase class. Ok, In fact I copied the pgsql methods and adjusted
them for the Interbase needings.
Although it needs some tests and reviews, I think it is usefull enough to
add
this to the pear cvs.
Attached is a diff against ibase.php,v 1.30 2002/01/22 14:43:30
Take care,
Lutz
*** ibase.php.orig Tue Jan 29 20:55:07 2002 --- ibase.php Sat Feb 2 11:38:15 2002 *************** *** 236,239 **** --- 236,499 ---- } + // {{{ nextId() + + /** + * Get the next value in a sequence. + * + * If the sequence does not exist, it will be created, + * unless $ondemand is false. + * + * @access public + * @param string $seq_name the name of the sequence + * @param bool $ondemand whether to create the sequence on demand + * @return a sequence integer, or a DB error + */ + function nextId($seq_name, $ondemand = true) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + $repeat = 0; + do { + $this->pushErrorHandling(PEAR_ERROR_RETURN); + $result = $this->query("SELECT GEN_ID(${sqn}_SEQ, 1) FROM RDB\$GENERATORS" + ." WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'"); + $this->popErrorHandling(); + if ($ondemand && DB::isError($result)) { + $repeat = 1; + $result = $this->createSequence($seq_name); + if (DB::isError($result)) { + return $this->raiseError($result); + } + } else { + $repeat = 0; + } + } while ($repeat); + if (DB::isError($result)) { + return $this->raiseError($result); + } + $arr = $result->fetchRow(DB_FETCHMODE_ORDERED); + $result->free(); + return $arr[0]; + } + + // }}} + + // {{{ createSequence() + + /** + * Create the sequence + * + * @param string $seq_name the name of the sequence + * @return mixed DB_OK on success or DB error on error + * @access public + */ + function createSequence($seq_name) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + $this->pushErrorHandling(PEAR_ERROR_RETURN); + $result = $this->query("CREATE GENERATOR ${sqn}_SEQ"); + $this->popErrorHandling(); + + return $result; + } + + // }}} + + // {{{ dropSequence() + + /** + * Drop a sequence + * + * @param string $seq_name the name of the sequence + * @return mixed DB_OK on success or DB error on error + * @access public + */ + function dropSequence($seq_name) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + return $this->query("DELETE FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'"); + } + + // }}} + + // {{{ _ibaseFieldFlags() + + /** + * get the Flags of a Field + * + * @param string $field_name the name of the field + * @param string $table_name the name of the table + * + * @return string The flags of the field ("primary_key", "unique_key", "not_null" + * "default", "computed" and "blob" are supported) + * @access private + */ + function _ibaseFieldFlags($field_name, $table_name) + { + + $sql = 'SELECT R.RDB$CONSTRAINT_TYPE CTYPE' + .' FROM RDB$INDEX_SEGMENTS I' + .' JOIN RDB$RELATION_CONSTRAINTS R ON I.RDB$INDEX_NAME=R.RDB$INDEX_NAME' + .' WHERE I.RDB$FIELD_NAME=\''.$field_name.'\'' + .' AND R.RDB$RELATION_NAME=\''.$table_name.'\''; + $result = ibase_query($this->connection, $sql); + if (empty($result)) { + return $this->raiseError(); + } + if ($obj = @ibase_fetch_object($result)) { + ibase_free_result($result); + if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'PRIMARY KEY') { + $flags = 'primary_key '; + } + if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'UNIQUE') { + $flags .= 'unique_key '; + } + } + + $sql = 'SELECT R.RDB$NULL_FLAG AS NFLAG,' + .' R.RDB$DEFAULT_SOURCE AS DSOURCE,' + .' F.RDB$FIELD_TYPE AS FTYPE,' + .' F.RDB$COMPUTED_SOURCE AS CSOURCE' + .' FROM RDB$RELATION_FIELDS R ' + .' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME' + .' WHERE R.RDB$RELATION_NAME=\''.$table_name.'\'' + .' AND R.RDB$FIELD_NAME=\''.$field_name.'\''; + $result = ibase_query($this->connection, $sql); + if (empty($result)) { + return $this->raiseError(); + } + if ($obj = @ibase_fetch_object($result)) { + ibase_free_result($result); + if (isset($obj->NFLAG)) { + $flags .= 'not_null '; + } + if (isset($obj->DSOURCE)) { + $flags .= 'default '; + } + if (isset($obj->CSOURCE)) { + $flags .= 'computed '; + } + if (isset($obj->FTYPE) && $obj->FTYPE == 261) { + $flags .= 'blob '; + } + } + + return trim($flags); + } + + // }}} + + // {{{ tableInfo() + + /** + * Returns information about a table or a result set + * + * NOTE: doesn't support 'flags'and 'table' if called from a db_result + * + * @param mixed $resource Interbase result identifier or table name + * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or + * DB_TABLEINFO_ORDER) + * + * @return array An array with all the information + */ + function tableInfo($result, $mode = null) + { + $count = 0; + $id = 0; + $res = array(); + + /* + * depending on $mode, metadata returns the following values: + * + * - mode is false (default): + * $result[]: + * [0]["table"] table name + * [0]["name"] field name + * [0]["type"] field type + * [0]["len"] field length + * [0]["flags"] field flags + * + * - mode is DB_TABLEINFO_ORDER + * $result[]: + * ["num_fields"] number of metadata records + * [0]["table"] table name + * [0]["name"] field name + * [0]["type"] field type + * [0]["len"] field length + * [0]["flags"] field flags + * ["order"][field name] index of field named "field name" + * The last one is used, if you have a field name, but no index. + * Test: if (isset($result['meta']['myfield'])) { ... + * + * - mode is DB_TABLEINFO_ORDERTABLE + * the same as above. but additionally + * ["ordertable"][table name][field name] index of field + * named "field name" + * + * this is, because if you have fields from different + * tables with the same field name * they override each + * other with DB_TABLEINFO_ORDER + * + * you can combine DB_TABLEINFO_ORDER and + * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER | + * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL + */ + + // if $result is a string, then we want information about a + // table without a resultset + + if (is_string($result)) { + $id = ibase_query($this->connection,"SELECT * FROM $result"); + if (empty($id)) { + return $this->raiseError($result); + } + } else { // else we want information about a resultset + $id = $result; + if (empty($id)) { + return $this->raiseError($result); + } + } + + $count = @ibase_num_fields($id); + + // made this IF due to performance (one if is faster than $count if's) + if (empty($mode)) { + + for ($i=0; $i<$count; $i++) { + $info = @ibase_field_info($id, $i); + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = $info['name']; + $res[$i]['type'] = $info['type']; + $res[$i]['len'] = $info['length']; + $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : ''; + } + + } else { // full + $res["num_fields"]= $count; + + for ($i=0; $i<$count; $i++) { + $info = @ibase_field_info($id, $i); + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = $info['name']; + $res[$i]['type'] = $info['type']; + $res[$i]['len'] = $info['length']; + $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : ''; + if ($mode & DB_TABLEINFO_ORDER) { + $res['order'][$res[$i]['name']] = $i; + } + if ($mode & DB_TABLEINFO_ORDERTABLE) { + $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i; + } + } + } + + // free the result only if we were called on a table + if (is_resource($id)) { + ibase_free_result($id); + } + return $res; + } + + // }}} + // {{{ getSpecialQuery()
*** ibase.php.orig Tue Jan 29 20:55:07 2002 --- ibase.php Sat Feb 2 11:38:15 2002 *************** *** 236,239 **** --- 236,499 ---- } + // {{{ nextId() + + /** + * Get the next value in a sequence. + * + * If the sequence does not exist, it will be created, + * unless $ondemand is false. + * + * @access public + * @param string $seq_name the name of the sequence + * @param bool $ondemand whether to create the sequence on demand + * @return a sequence integer, or a DB error + */ + function nextId($seq_name, $ondemand = true) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + $repeat = 0; + do { + $this->pushErrorHandling(PEAR_ERROR_RETURN); + $result = $this->query("SELECT GEN_ID(${sqn}_SEQ, 1) FROM RDB\$GENERATORS" + ." WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'"); + $this->popErrorHandling(); + if ($ondemand && DB::isError($result)) { + $repeat = 1; + $result = $this->createSequence($seq_name); + if (DB::isError($result)) { + return $this->raiseError($result); + } + } else { + $repeat = 0; + } + } while ($repeat); + if (DB::isError($result)) { + return $this->raiseError($result); + } + $arr = $result->fetchRow(DB_FETCHMODE_ORDERED); + $result->free(); + return $arr[0]; + } + + // }}} + + // {{{ createSequence() + + /** + * Create the sequence + * + * @param string $seq_name the name of the sequence + * @return mixed DB_OK on success or DB error on error + * @access public + */ + function createSequence($seq_name) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + $this->pushErrorHandling(PEAR_ERROR_RETURN); + $result = $this->query("CREATE GENERATOR ${sqn}_SEQ"); + $this->popErrorHandling(); + + return $result; + } + + // }}} + + // {{{ dropSequence() + + /** + * Drop a sequence + * + * @param string $seq_name the name of the sequence + * @return mixed DB_OK on success or DB error on error + * @access public + */ + function dropSequence($seq_name) + { + $sqn = strtoupper(preg_replace('/[^a-z0-9_]/i', '_', $seq_name)); + return $this->query("DELETE FROM RDB\$GENERATORS WHERE RDB\$GENERATOR_NAME='${sqn}_SEQ'"); + } + + // }}} + + // {{{ _ibaseFieldFlags() + + /** + * get the Flags of a Field + * + * @param string $field_name the name of the field + * @param string $table_name the name of the table + * + * @return string The flags of the field ("primary_key", "unique_key", "not_null" + * "default", "computed" and "blob" are supported) + * @access private + */ + function _ibaseFieldFlags($field_name, $table_name) + { + + $sql = 'SELECT R.RDB$CONSTRAINT_TYPE CTYPE' + .' FROM RDB$INDEX_SEGMENTS I' + .' JOIN RDB$RELATION_CONSTRAINTS R ON I.RDB$INDEX_NAME=R.RDB$INDEX_NAME' + .' WHERE I.RDB$FIELD_NAME=\''.$field_name.'\'' + .' AND R.RDB$RELATION_NAME=\''.$table_name.'\''; + $result = ibase_query($this->connection, $sql); + if (empty($result)) { + return $this->raiseError(); + } + if ($obj = @ibase_fetch_object($result)) { + ibase_free_result($result); + if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'PRIMARY KEY') { + $flags = 'primary_key '; + } + if (isset($obj->CTYPE) && trim($obj->CTYPE) == 'UNIQUE') { + $flags .= 'unique_key '; + } + } + + $sql = 'SELECT R.RDB$NULL_FLAG AS NFLAG,' + .' R.RDB$DEFAULT_SOURCE AS DSOURCE,' + .' F.RDB$FIELD_TYPE AS FTYPE,' + .' F.RDB$COMPUTED_SOURCE AS CSOURCE' + .' FROM RDB$RELATION_FIELDS R ' + .' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME' + .' WHERE R.RDB$RELATION_NAME=\''.$table_name.'\'' + .' AND R.RDB$FIELD_NAME=\''.$field_name.'\''; + $result = ibase_query($this->connection, $sql); + if (empty($result)) { + return $this->raiseError(); + } + if ($obj = @ibase_fetch_object($result)) { + ibase_free_result($result); + if (isset($obj->NFLAG)) { + $flags .= 'not_null '; + } + if (isset($obj->DSOURCE)) { + $flags .= 'default '; + } + if (isset($obj->CSOURCE)) { + $flags .= 'computed '; + } + if (isset($obj->FTYPE) && $obj->FTYPE == 261) { + $flags .= 'blob '; + } + } + + return trim($flags); + } + + // }}} + + // {{{ tableInfo() + + /** + * Returns information about a table or a result set + * + * NOTE: doesn't support 'flags'and 'table' if called from a db_result + * + * @param mixed $resource Interbase result identifier or table name + * @param int $mode A valid tableInfo mode (DB_TABLEINFO_ORDERTABLE or + * DB_TABLEINFO_ORDER) + * + * @return array An array with all the information + */ + function tableInfo($result, $mode = null) + { + $count = 0; + $id = 0; + $res = array(); + + /* + * depending on $mode, metadata returns the following values: + * + * - mode is false (default): + * $result[]: + * [0]["table"] table name + * [0]["name"] field name + * [0]["type"] field type + * [0]["len"] field length + * [0]["flags"] field flags + * + * - mode is DB_TABLEINFO_ORDER + * $result[]: + * ["num_fields"] number of metadata records + * [0]["table"] table name + * [0]["name"] field name + * [0]["type"] field type + * [0]["len"] field length + * [0]["flags"] field flags + * ["order"][field name] index of field named "field name" + * The last one is used, if you have a field name, but no index. + * Test: if (isset($result['meta']['myfield'])) { ... + * + * - mode is DB_TABLEINFO_ORDERTABLE + * the same as above. but additionally + * ["ordertable"][table name][field name] index of field + * named "field name" + * + * this is, because if you have fields from different + * tables with the same field name * they override each + * other with DB_TABLEINFO_ORDER + * + * you can combine DB_TABLEINFO_ORDER and + * DB_TABLEINFO_ORDERTABLE with DB_TABLEINFO_ORDER | + * DB_TABLEINFO_ORDERTABLE * or with DB_TABLEINFO_FULL + */ + + // if $result is a string, then we want information about a + // table without a resultset + + if (is_string($result)) { + $id = ibase_query($this->connection,"SELECT * FROM $result"); + if (empty($id)) { + return $this->raiseError($result); + } + } else { // else we want information about a resultset + $id = $result; + if (empty($id)) { + return $this->raiseError($result); + } + } + + $count = @ibase_num_fields($id); + + // made this IF due to performance (one if is faster than $count if's) + if (empty($mode)) { + + for ($i=0; $i<$count; $i++) { + $info = @ibase_field_info($id, $i); + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = $info['name']; + $res[$i]['type'] = $info['type']; + $res[$i]['len'] = $info['length']; + $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : ''; + } + + } else { // full + $res["num_fields"]= $count; + + for ($i=0; $i<$count; $i++) { + $info = @ibase_field_info($id, $i); + $res[$i]['table'] = (is_string($result)) ? $result : ''; + $res[$i]['name'] = $info['name']; + $res[$i]['type'] = $info['type']; + $res[$i]['len'] = $info['length']; + $res[$i]['flags'] = (is_string($result)) ? $this->_ibaseFieldFlags($info['name'], $result) : ''; + if ($mode & DB_TABLEINFO_ORDER) { + $res['order'][$res[$i]['name']] = $i; + } + if ($mode & DB_TABLEINFO_ORDERTABLE) { + $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i; + } + } + } + + // free the result only if we were called on a table + if (is_resource($id)) { + ibase_free_result($id); + } + return $res; + } + + // }}} + // {{{ getSpecialQuery()