Source for file fbsql.php
Documentation is available at fbsql.php
// +----------------------------------------------------------------------+
// +----------------------------------------------------------------------+
// | Copyright (c) 1998-2004 Manuel Lemos, Tomas V.V.Cox, |
// | Stig. S. Bakken, Lukas Smith, Frank M. Kromann |
// | All rights reserved. |
// +----------------------------------------------------------------------+
// | MDB is a merge of PEAR DB and Metabases that provides a unified DB |
// | API as well as database abstraction for PHP applications. |
// | This LICENSE is in the BSD license style. |
// | Redistribution and use in source and binary forms, with or without |
// | modification, are permitted provided that the following conditions |
// | Redistributions of source code must retain the above copyright |
// | notice, this list of conditions and the following disclaimer. |
// | Redistributions in binary form must reproduce the above copyright |
// | notice, this list of conditions and the following disclaimer in the |
// | documentation and/or other materials provided with the distribution. |
// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
// | Lukas Smith nor the names of his contributors may be used to endorse |
// | or promote products derived from this software without specific prior|
// | written permission. |
// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
// | POSSIBILITY OF SUCH DAMAGE. |
// +----------------------------------------------------------------------+
// | Author: Lukas Smith <smith@dybnet.de> |
// +----------------------------------------------------------------------+
// $Id: fbsql.php,v 1.7.4.8 2004/04/08 17:19:01 lsmith Exp $
if(!defined('MDB_MANAGER_FBSQL_INCLUDED'))
define('MDB_MANAGER_FBSQL_INCLUDED', 1 );
require_once('MDB/Modules/Manager/Common.php');
* MDB FrontBase driver for the management modules
* @author Lukas Smith <smith@dybnet.de>
* @author Frank M. Kromann <frank@kromann.info>
class MDB_Manager_fbsql extends MDB_Manager_Common
* @param object $dbs database object that is extended by this class
* @param string $name name of the database that should be created
* @return mixed MDB_OK on success, a MDB error on failure
function createDatabase (&$db, $name)
if (!@fbsql_create_db ($name, $db->connection )) {
return($db->fbsqlRaiseError ());
* drop an existing database
* @param object $dbs database object that is extended by this class
* @param string $name name of the database that should be dropped
* @return mixed MDB_OK on success, a MDB error on failure
function dropDatabase (&$db, $name)
if (!@fbsql_stop_db ($name, $db->connection )) {
return($db->fbsqlRaiseError ());
if (!@fbsql_drop_db ($name, $db->connection )) {
return($db->fbsqlRaiseError ());
return($db->disconnect ());
* @param object $dbs database object that is extended by this class
* @param string $name Name of the database that should be created
* @param array $fields Associative array that contains the definition of each field of the new table
* The indexes of the array entries are the names of the fields of the table an
* the array entry values are associative arrays like those that are meant to be
* passed with the field definitions to get[Type]Declaration() functions.
* @return mixed MDB_OK on success, a MDB error on failure
function createTable (&$db, $name, $fields)
if (!isset ($name) || !strcmp($name, '')) {
if (count($fields) == 0 ) {
if (MDB::isError($query_fields = $db->getFieldDeclarationList ($fields))) {
$query = " CREATE TABLE $name ($query_fields)";
return($db->query ($query));
* @param object $dbs database object that is extended by this class
* @param string $name name of the table that should be dropped
* @return mixed MDB_OK on success, a MDB error on failure
function dropTable (&$db, $name)
return($db->query (" DROP TABLE $name CASCADE" ));
* alter an existing table
* @param object $dbs database object that is extended by this class
* @param string $name name of the table that is intended to be changed.
* @param array $changes associative array that contains the details of each type
* of change that is intended to be performed. The types of
* changes that are currently supported are defined as follows:
* New name for the table.
* Associative array with the names of fields to be added as
* indexes of the array. The value of each entry of the array
* should be set to another associative array with the properties
* of the fields to be added. The properties of the fields should
* be the same as defined by the Metabase parser.
* Additionally, there should be an entry named Declaration that
* is expected to contain the portion of the field declaration already
* in DBMS specific SQL code as it is used in the CREATE TABLE statement.
* Associative array with the names of fields to be removed as indexes
* of the array. Currently the values assigned to each entry are ignored.
* An empty array should be used for future compatibility.
* Associative array with the names of fields to be renamed as indexes
* of the array. The value of each entry of the array should be set to
* another associative array with the entry named name with the new
* field name and the entry named Declaration that is expected to contain
* the portion of the field declaration already in DBMS specific SQL code
* as it is used in the CREATE TABLE statement.
* Associative array with the names of the fields to be changed as indexes
* of the array. Keep in mind that if it is intended to change either the
* name of a field and any other properties, the ChangedFields array entries
* should have the new names of the fields as array indexes.
* The value of each entry of the array should be set to another associative
* array with the properties of the fields to that are meant to be changed as
* array entries. These entries should be assigned to the new values of the
* respective properties. The properties of the fields should be the same
* as defined by the Metabase parser.
* If the default property is meant to be added, removed or changed, there
* should also be an entry with index ChangedDefault assigned to 1. Similarly,
* if the notnull constraint is to be added or removed, there should also be
* an entry with index ChangedNotNull assigned to 1.
* Additionally, there should be an entry named Declaration that is expected
* to contain the portion of the field changed declaration already in DBMS
* specific SQL code as it is used in the CREATE TABLE statement.
* 'AddedFields' => array(
* 'Declaration' => 'quota INT'
* 'RemovedFields' => array(
* 'file_limit' => array(),
* 'time_limit' => array()
* 'ChangedFields' => array(
* 'Declaration' => "gender CHAR(1) DEFAULT 'M'"
* 'RenamedFields' => array(
* 'Declaration' => "gender CHAR(1) DEFAULT 'M'"
* @param boolean $check indicates whether the function should just check if the DBMS driver
* can perform the requested table alterations if the value is true or
* actually perform them otherwise.
* @return mixed MDB_OK on success, a MDB error on failure
function alterTable (&$db, $name, $changes, $check)
for($change = 0 ,reset($changes);
$change < count($changes);
next($changes), $change++ )
'Alter table: change type "'.Key ($changes). '" not yet supported'));
$query = (isset ($changes['name']) ? 'RENAME AS '. $changes['name'] : '');
if (isset ($changes['AddedFields'])) {
$fields = $changes['AddedFields'];
for($field = 0 , reset($fields);
$query .= 'ADD '. $fields[key($fields)]['Declaration'];
if (isset ($changes['RemovedFields'])) {
$fields = $changes['RemovedFields'];
for($field = 0 ,reset($fields);
$query .= 'DROP '. key($fields);
$renamed_fields = array ();
if (isset ($changes['RenamedFields'])) {
$fields = $changes['RenamedFields'];
for($field = 0 ,reset($fields);
$renamed_fields[$fields[key($fields)]['name']] = key($fields);
if (isset ($changes['ChangedFields'])) {
$fields = $changes['ChangedFields'];
for($field = 0 ,reset($fields);
if (isset ($renamed_fields[key($fields)])) {
$field_name = $renamed_fields[key($fields)];
unset ($renamed_fields[key($fields)]);
$field_name = key($fields);
$query .= " CHANGE $field_name ". $fields[key($fields)]['Declaration'];
if (count($renamed_fields))
for($field = 0 ,reset($renamed_fields);
$field< count($renamed_fields);
next($renamed_fields), $field++ )
$old_field_name = $renamed_fields[Key ($renamed_fields)];
$query .= " CHANGE $old_field_name ". $changes['RenamedFields'][$old_field_name]['Declaration'];
return($db->query (" ALTER TABLE $name $query" ));
* @param object $dbs database object that is extended by this class
* @return mixed data array on success, a MDB error on failure
function listDatabases (&$db)
$result = $db->queryCol ('SHOW DATABASES');
* @param object $dbs database object that is extended by this class
* @return mixed data array on success, a MDB error on failure
$result = $db->queryCol ('SELECT DISTINCT USER FROM USER');
* list all tables in the current database
* @param object $dbs database object that is extended by this class
* @return mixed data array on success, a MDB error on failure
function listTables (&$db)
$table_names = $db->queryCol ('SHOW TABLES');
for($i = 0 , $j = count($table_names), $tables = array (); $i < $j; ++ $i)
if (!$this->_isSequenceName ($db, $table_names[$i]))
$tables[] = $table_names[$i];
* list all fields in a tables in the current database
* @param object $dbs database object that is extended by this class
* @param string $table name of table that should be used in method
* @return mixed data array on success, a MDB error on failure
function listTableFields (&$db, $table)
$result = $db->query (" SHOW COLUMNS FROM $table" );
$columns = $db->getColumnNames ($result);
$db->freeResult ($columns);
if(!isset ($columns['field'])) {
$db->freeResult ($result);
'List table fields: show columns does not return the table field names'));
$field_column = $columns['field'];
for($fields = array (), $field = 0; !$db->endOfResult ($result); ++ $field) {
$field_name = $db->fetch ($result, $field, $field_column);
if ($field_name != $db->dummy_primary_key )
$db->freeResult ($result);
// {{{ getTableFieldDefinition()
* get the stucture of a field into an array
* @param object $dbs database object that is extended by this class
* @param string $table name of table that should be used in method
* @param string $field_name name of field that should be used in method
* @return mixed data array on success, a MDB error on failure
function getTableFieldDefinition (&$db, $table, $field_name)
if ($field_name == $db->dummy_primary_key ) {
'Get table field definiton: '. $db->dummy_primary_key. ' is an hidden column'));
$result = $db->query (" SHOW COLUMNS FROM $table" );
$columns = $db->getColumnNames ($result);
$db->freeResult ($columns);
if (!isset ($columns[$column = 'field'])
|| !isset ($columns[$column = 'type']))
$db->freeResult ($result);
'Get table field definition: show columns does not return the column '. $column));
$field_column = $columns['field'];
$type_column = $columns['type'];
while (is_array($row = $db->fetchInto ($result))) {
if ($field_name == $row[$field_column]) {
$db_type = strtok($db_type, '(), ');
if ($db_type == 'national') {
if($decimal == 'binary') {
} elseif ($length == '1') {
} elseif (strstr($db_type, 'text'))
foreach($matches[0 ] as $value) {
'List table fields: unknown database attribute type'));
if (isset ($columns['null'])
&& $row[$columns['null']] != 'YES')
if (isset ($columns['default'])
&& isset ($row[$columns['default']]))
$default = $row[$columns['default']];
for($field_choices = array (), $datatype = 0; $datatype < count($type); $datatype++ ) {
$field_choices[$datatype] = array ('type' => $type[$datatype]);
$field_choices[$datatype]['notnull'] = 1;
$field_choices[$datatype]['default'] = $default;
if($type[$datatype] != 'boolean'
&& $type[$datatype] != 'time'
&& $type[$datatype] != 'date'
&& $type[$datatype] != 'timestamp')
$field_choices[$datatype]['length'] = $length;
$definition[0 ] = $field_choices;
if (isset ($columns['extra'])
&& isset ($row[$columns['extra']])
&& $row[$columns['extra']] == 'auto_increment')
$implicit_sequence = array ();
$implicit_sequence['on'] = array ();
$implicit_sequence['on']['table'] = $table;
$implicit_sequence['on']['field'] = $field_name;
$definition[1 ]['name'] = $table. '_'. $field_name;
$definition[1 ]['definition'] = $implicit_sequence;
if (isset ($columns['key'])
&& isset ($row[$columns['key']])
&& $row[$columns['key']] == 'PRI')
// check that its not just a unique field
foreach($indexes as $index) {
if ($index['Key_name'] == 'PRIMARY' && $index['Column_name'] == $field_name) {
$implicit_index = array ();
$implicit_index['unique'] = 1;
$implicit_index['FIELDS'][$field_name] = '';
$definition[2 ]['name'] = $field_name;
$definition[2 ]['definition'] = $implicit_index;
$db->freeResult ($result);
if(!$db->options ['autofree']) {
$db->freeResult ($result);
'Get table field definition: it was not specified an existing table column'));
* get the stucture of a field into an array
* @param object $dbs database object that is extended by this class
* @param string $table name of the table on which the index is to be created
* @param string $name name of the index to be created
* @param array $definition associative array that defines properties of the index to be created.
* Currently, only one property named FIELDS is supported. This property
* is also an associative with the names of the index fields as array
* indexes. Each entry of this array is set to another type of associative
* array that specifies properties of the index that are specific to
* Currently, only the sorting property is supported. It should be used
* to define the sorting direction of the index. It may be set to either
* ascending or descending.
* Not all DBMS support index sorting direction configuration. The DBMS
* drivers of those that do not support it ignore this property. Use the
* function support() to determine whether the DBMS driver can manage indexes.
* 'sorting' => 'ascending'
* 'last_login' => array()
* @return mixed MDB_OK on success, a MDB error on failure
function createIndex (&$db, $table, $name, $definition)
$query = "CREATE ". (isset ($definition['unique']) ? 'UNIQUE INDEX' : 'INDEX')." $name on $table (";
for($field = 0 , reset($definition['FIELDS']);
$field < count($definition['FIELDS']);
$field++ , next($definition['FIELDS']))
$query .= key($definition['FIELDS']);
return($db->query ($query));
* @param object $dbs database object that is extended by this class
* @param string $table name of table that should be used in method
* @param string $name name of the index to be dropped
* @return mixed MDB_OK on success, a MDB error on failure
function dropIndex (&$db, $table, $name)
return($db->query (" ALTER TABLE $table DROP INDEX $name" ));
// {{{ listTableIndexes()
* list all indexes in a table
* @param object $dbs database object that is extended by this class
* @param string $table name of table that should be used in method
* @return mixed data array on success, a MDB error on failure
function listTableIndexes (&$db, $table)
if(MDB::isError($result = $db->query (" SHOW INDEX FROM $table" ))) {
$indexes_all = $db->fetchCol ($result, 'Key_name');
for($found = $indexes = array (), $index = 0 , $indexes_all_cnt = count($indexes_all);
$index < $indexes_all_cnt;
if ($indexes_all[$index] != 'PRIMARY'
&& !isset ($found[$indexes_all[$index]]))
$indexes[] = $indexes_all[$index];
$found[$indexes_all[$index]] = 1;
// {{{ getTableIndexDefinition()
* get the stucture of an index into an array
* @param object $dbs database object that is extended by this class
* @param string $table name of table that should be used in method
* @param string $index_name name of index that should be used in method
* @return mixed data array on success, a MDB error on failure
function getTableIndexDefinition (&$db, $table, $index_name)
if($index_name == 'PRIMARY') {
return($db->raiseError (MDB_ERROR_MANAGER, NULL , NULL , 'Get table index definition: PRIMARY is an hidden index'));
if(MDB::isError($result = $db->query (" SHOW INDEX FROM $table" ))) {
$key_name = $row['Key_name'];
if(!strcmp($index_name, $key_name)) {
if(!$row['Non_unique']) {
$definition[$index_name]['unique'] = 1;
$column_name = $row['Column_name'];
$definition['FIELDS'][$column_name] = array ();
if(isset ($row['Collation'])) {
$definition['FIELDS'][$column_name]['sorting'] = ($row['Collation'] == 'A' ? 'ascending' : 'descending');
$db->freeResult ($result);
if (!isset ($definition['FIELDS'])) {
return($db->raiseError (MDB_ERROR_MANAGER, NULL , NULL , 'Get table index definition: it was not specified an existing table index'));
* @param object $dbs database object that is extended by this class
* @param string $seq_name name of the sequence to be created
* @param string $start start value of the sequence; default is 1
* @return mixed MDB_OK on success, a MDB error on failure
function createSequence (&$db, $seq_name, $start)
$sequence_name = $db->getSequenceName ($seq_name);
$res = $db->query (" CREATE TABLE $sequence_name
(". $db->options ['sequence_col_name']. " INTEGER DEFAULT UNIQUE, PRIMARY KEY(". $db->options ['sequence_col_name']. "))");
$res = $db->query (" set unique = 1 for $sequence_name" );
$res = $db->query (" INSERT INTO $sequence_name VALUES (". ($start-1 ). ')');
$result = $db->query (" DROP TABLE $sequence_name" );
return($db->raiseError (MDB_ERROR, NULL , NULL ,
'Create sequence: could not drop inconsistent sequence table ('.
$result->getMessage (). ' ('. $result->getUserinfo (). '))'));
return($db->raiseError (MDB_ERROR, NULL , NULL ,
'Create sequence: could not create sequence table ('.
$res->getMessage (). ' ('. $res->getUserinfo (). '))'));
* @param object $dbs database object that is extended by this class
* @param string $seq_name name of the sequence to be dropped
* @return mixed MDB_OK on success, a MDB error on failure
function dropSequence (&$db, $seq_name)
$sequence_name = $db->getSequenceName ($seq_name);
return($db->query (" DROP TABLE $sequence_name CASCADE" ));
* list all sequences in the current database
* @param object $dbs database object that is extended by this class
* @return mixed data array on success, a MDB error on failure
function listSequences (&$db)
$table_names = $db->queryCol ('SHOW TABLES');
for($i = 0 , $j = count($table_names), $sequences = array (); $i < $j; ++ $i)
if ($sqn = $this->_isSequenceName ($db, $table_names[$i]))
Documentation generated on Mon, 11 Mar 2019 10:15:23 -0400 by phpDocumentor 1.4.4. PEAR Logo Copyright © PHP Group 2004.
|