/**
*
* Alter columns and indexes of a table based on DB_Table column and index
* arrays.
*
* @static
*
* @access public
*
* @param object &$db A PEAR DB/MDB2 object.
*
* @param string $table The table name to connect to in the database.
*
* @param mixed $column_set A DB_Table $this->col array.
*
* @param mixed $index_set A DB_Table $this->idx array.
*
* @return bool|object True if altering was successful or a PEAR_Error on
* failure.
*
*/
function alter(&$db, $table, $column_set, $index_set)
{
$phptype = $db->phptype;
if (is_subclass_of($db, 'db_common')) {
$backend = 'db';
$reverse =& $db;
// workaround for missing index and constraint information methods
// in PEAR::DB ==> use adopted code from MDB2's driver classes
require_once 'DB/Table/Manager/' . $phptype . '.php';
$classname = 'DB_Table_Manager_' . $phptype;
$dbtm =& new $classname();
$dbtm->_db =& $db;
// pass database instance to the 'workaround' class
$manager =& $dbtm;
$table_info_mode = DB_TABLEINFO_FULL;
$ok_const = DB_OK;
} elseif (is_subclass_of($db, 'mdb2_driver_common')) {
$backend = 'mdb2';
$db->loadModule('Reverse');
$manager =& $db->manager;
$reverse =& $db->reverse;
$table_info_mode = MDB2_TABLEINFO_FULL;
$ok_const = MDB2_OK;
}
// get table info
$tableInfo = $reverse->tableInfo($table, $table_info_mode);
if (PEAR::isError($tableInfo)) {
return $tableInfo;
}
$tableInfoOrder = array_change_key_case($tableInfo['order'], CASE_LOWER);
// emulate MDB2 Reverse extension for PEAR::DB as backend
if (is_subclass_of($db, 'db_common')) {
$reverse =& $dbtm;
}
// check (and alter) columns
if (is_null($column_set)) {
$column_set = array();
}
foreach ($column_set as $colname => $val) {
$colname = strtolower(trim($colname));
// check the column name
$name_check = DB_Table_Manager::_validateColumnName($colname);
if (PEAR::isError($name_check)) {
return $name_check;
}
// check the column's existence
$column_exists = DB_Table_Manager::_columnExists($colname, $tableInfoOrder, 'alter');
if (PEAR::isError($column_exists)) {
return $column_exists;
}
if ($column_exists === false) {
// add the column
$definition = DB_Table_Manager::_getColumnDefinition($backend, $phptype, $val);
if (PEAR::isError($definition)) {
return $definition;
}
$changes = array('add' => array($colname => $definition));
if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) {
echo "(alter) New table field will be added ({$colname}):\n";
var_dump($changes);
echo "\n";
}
$result = $manager->alterTable($table, $changes, false);
if (PEAR::isError($result)) {
return $result;
}
continue;
}
// check whether the column type is a known type
$type_check = DB_Table_Manager::_validateColumnType($phptype, $val['type']);
if (PEAR::isError($type_check)) {
return $type_check;
}
// check whether the column has the right type
$type_check = DB_Table_Manager::_checkColumnType($phptype, $colname, $val['type'], $tableInfoOrder, $tableInfo, 'alter');
if (PEAR::isError($type_check)) {
return $type_check;
}
if ($type_check === false) {
// change the column type
$definition = DB_Table_Manager::_getColumnDefinition($backend, $phptype, $val);
if (PEAR::isError($definition)) {
return $definition;
}
$changes = array('change' => array($colname => array('type' => null, 'definition' => $definition)));
if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) {
echo "(alter) Table field's type will be changed ({$colname}):\n";
var_dump($changes);
echo "\n";
}
$result = $manager->alterTable($table, $changes, false);
if (PEAR::isError($result)) {
return $result;
}
continue;
}
}
// get information about indexes / constraints
$table_indexes = DB_Table_Manager::getIndexes($db, $table);
if (PEAR::isError($table_indexes)) {
return $table_indexes;
}
// check (and alter) indexes / constraints
if (is_null($index_set)) {
$index_set = array();
}
foreach ($index_set as $idxname => $val) {
list($type, $cols) = DB_Table_Manager::_getIndexTypeAndColumns($val, $idxname);
$newIdxName = '';
// check the index definition
$index_check = DB_Table_Manager::_validateIndexName($idxname, $table, $phptype, $type, $cols, $column_set, $newIdxName);
if (PEAR::isError($index_check)) {
return $index_check;
}
// check whether the index has the right type and has all
// specified columns
$index_check = DB_Table_Manager::_checkIndex($idxname, $newIdxName, $type, $cols, $table_indexes, 'alter');
if (PEAR::isError($index_check)) {
return $index_check;
}
if ($index_check === false) {
// (1) drop wrong index/constraint
// (2) add right index/constraint
if ($backend == 'mdb2') {
// save user defined 'idxname_format' option
$idxname_format = $db->getOption('idxname_format');
$db->setOption('idxname_format', '%s');
}
// drop index/constraint only if it exists
foreach (array('normal', 'unique', 'primary') as $idx_type) {
if (array_key_exists(strtolower($newIdxName), $table_indexes[$idx_type])) {
if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) {
echo "(alter) Index/constraint will be deleted (name: '{$newIdxName}', type: '{$idx_type}').\n";
}
if ($idx_type == 'normal') {
$result = $manager->dropIndex($table, $newIdxName);
} else {
$result = $manager->dropConstraint($table, $newIdxName);
}
if (PEAR::isError($result)) {
if ($backend == 'mdb2') {
// restore user defined 'idxname_format' option
$db->setOption('idxname_format', $idxname_format);
}
return $result;
}
break;
}
}
// prepare index/constraint definition
$indexes = array();
if ($backend == 'mdb2') {
// array with column names as keys
$idx_cols = array();
foreach ($cols as $col) {
$idx_cols[$col] = array();
}
switch ($type) {
case 'primary':
$indexes['primary'][$newIdxName] = array('fields' => $idx_cols, 'primary' => true);
break;
case 'unique':
$indexes['unique'][$newIdxName] = array('fields' => $idx_cols, 'unique' => true);
break;
case 'normal':
$indexes['normal'][$newIdxName] = array('fields' => $idx_cols);
break;
}
} else {
$indexes[] = DB_Table_Manager::getDeclareForIndex($phptype, $type, $newIdxName, $table, $cols);
}
// create index/constraint
if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) {
echo "(alter) New index/constraint will be created (name: '{$newIdxName}', type: '{$type}'):\n";
var_dump($indexes);
echo "\n";
}
$result = DB_Table_Manager::_createIndexesAndContraints($db, $backend, $table, $indexes);
if ($backend == 'mdb2') {
// restore user defined 'idxname_format' option
$db->setOption('idxname_format', $idxname_format);
}
if (PEAR::isError($result)) {
return $result;
}
continue;
}
}
return true;
}
/**
* Gets column and index definitions by querying database
*
* Upon return, column definitions are stored in $this->col[$table],
* and index definitions in $this->idx[$table].
*
* Calls DB/MDB2::tableInfo() for column definitions, and uses
* the DB_Table_Manager class to obtain index definitions.
*
* @param string $table name of table
*
* @return mixed true on success, PEAR Error on failure
* @access public
*/
function getTableDefinition($table)
{
/*
// postgres strip the schema bit from the
if (!empty($options['generator_strip_schema'])) {
$bits = explode('.', $table,2);
$table = $bits[0];
if (count($bits) > 1) {
$table = $bits[1];
}
}
*/
if ($this->backend == 'db') {
$defs = $this->db->tableInfo($table);
if (PEAR::isError($defs)) {
return $defs;
}
$this->columns[$table] = $defs;
} else {
// Temporarily change 'portability' MDB2 option
$portability = $this->db->getOption('portability');
$this->db->setOption('portability', MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_FIX_CASE);
$this->db->loadModule('Manager');
$this->db->loadModule('Reverse');
// Columns
$defs = $this->db->reverse->tableInfo($table);
if (PEAR::isError($defs)) {
return $defs;
}
// rename the 'length' key, so it matches db's return.
foreach ($defs as $k => $v) {
if (isset($defs[$k]['length'])) {
$defs[$k]['len'] = $defs[$k]['length'];
}
}
$this->columns[$table] = $defs;
// Temporarily set 'idxname_format' MDB2 option to $this->idx_format
$idxname_format = $this->db->getOption('idxname_format');
$this->db->setOption('idxname_format', $this->idxname_format);
}
// Default - no auto increment column
$this->auto_inc_col[$table] = null;
// Loop over columns to create $this->col[$table]
$this->col[$table] = array();
foreach ($defs as $t) {
$name = $t['name'];
$col = array();
switch (strtoupper($t['type'])) {
case 'INT2':
// postgres
// postgres
case 'TINYINT':
case 'TINY':
//mysql
//mysql
case 'SMALLINT':
$col['type'] = 'smallint';
break;
case 'INT4':
// postgres
// postgres
case 'SERIAL4':
// postgres
// postgres
case 'INT':
case 'SHORT':
// mysql
// mysql
case 'INTEGER':
case 'MEDIUMINT':
case 'YEAR':
$col['type'] = 'integer';
break;
case 'BIGINT':
case 'LONG':
// mysql
// mysql
case 'INT8':
// postgres
// postgres
case 'SERIAL8':
// postgres
$col['type'] = 'bigint';
break;
case 'REAL':
case 'NUMERIC':
case 'NUMBER':
// oci8
// oci8
case 'FLOAT':
// mysql
// mysql
case 'FLOAT4':
// real (postgres)
$col['type'] = 'single';
break;
case 'DOUBLE':
case 'DOUBLE PRECISION':
// double precision (firebird)
// double precision (firebird)
case 'FLOAT8':
// double precision (postgres)
$col['type'] = 'double';
break;
case 'DECIMAL':
case 'MONEY':
// mssql and maybe others
$col['type'] = 'decimal';
break;
case 'BIT':
case 'BOOL':
case 'BOOLEAN':
$col['type'] = 'boolean';
break;
case 'STRING':
case 'CHAR':
$col['type'] = 'char';
break;
case 'VARCHAR':
case 'VARCHAR2':
case 'TINYTEXT':
$col['type'] = 'varchar';
break;
case 'TEXT':
case 'MEDIUMTEXT':
case 'LONGTEXT':
$col['type'] = 'clob';
break;
case 'DATE':
$col['type'] = 'date';
break;
case 'TIME':
$col['type'] = 'time';
break;
case 'DATETIME':
// mysql
// mysql
case 'TIMESTAMP':
$col['type'] = 'timestamp';
break;
case 'ENUM':
case 'SET':
// not really but oh well
// not really but oh well
case 'TIMESTAMPTZ':
// postgres
// postgres
case 'BPCHAR':
// postgres
// postgres
case 'INTERVAL':
// postgres (eg. '12 days')
// postgres (eg. '12 days')
case 'CIDR':
// postgres IP net spec
// postgres IP net spec
case 'INET':
// postgres IP
// postgres IP
case 'MACADDR':
// postgress network Mac address.
// postgress network Mac address.
case 'INTEGER[]':
// postgres type
// postgres type
case 'BOOLEAN[]':
// postgres type
$col['type'] = 'varchar';
break;
default:
$col['type'] = $t['type'] . ' (Unknown type)';
break;
}
// Set length and scope if required
if (in_array($col['type'], array('char', 'varchar', 'decimal'))) {
if (isset($t['len'])) {
$col['size'] = (int) $t['len'];
} elseif ($col['type'] == 'varchar') {
$col['size'] = 255;
// default length
} elseif ($col['type'] == 'char') {
$col['size'] = 128;
// default length
} elseif ($col['type'] == 'decimal') {
$col['size'] = 15;
// default length
}
if ($col['type'] == 'decimal') {
$col['scope'] = 2;
}
}
if (isset($t['notnull'])) {
if ($t['notnull']) {
$col['require'] = true;
}
}
if (isset($t['autoincrement'])) {
$this->auto_inc_col[$table] = $name;
}
if (isset($t['flags'])) {
$flags = $t['flags'];
if (preg_match('/not[ _]null/i', $flags)) {
$col['require'] = true;
}
if (preg_match("/(auto_increment|nextval\\()/i", $flags)) {
$this->auto_inc_col[$table] = $name;
}
}
$require = isset($col['require']) ? $col['require'] : false;
if ($require) {
if (isset($t['default'])) {
$default = $t['default'];
$type = $col['type'];
if (in_array($type, array('smallint', 'integer', 'bigint'))) {
$default = (int) $default;
} elseif (in_array($type, array('single', 'double'))) {
$default = (double) $default;
} elseif ($type == 'boolean') {
$default = (int) $default ? 1 : 0;
}
$col['default'] = $default;
}
}
$this->col[$table][$name] = $col;
}
// Make array with lower case column array names as keys
$col_lc = array();
foreach ($this->col[$table] as $name => $def) {
$name_lc = strtolower($name);
$col_lc[$name_lc] = $name;
}
// Constraints/Indexes
$DB_indexes = DB_Table_Manager::getIndexes($this->db, $table);
if (PEAR::isError($DB_indexes)) {
return $DB_indexes;
}
// Check that index columns correspond to valid column names.
// Try to correct problems with capitalization, if necessary.
foreach ($DB_indexes as $type => $indexes) {
foreach ($indexes as $name => $fields) {
foreach ($fields as $key => $field) {
// If index column is not a valid column name
if (!array_key_exists($field, $this->col[$table])) {
// Try a case-insensitive match
$field_lc = strtolower($field);
if (isset($col_lc[$field_lc])) {
$correct = $col_lc[$field_lc];
$DB_indexes[$type][$name][$key] = $correct;
} else {
$code = DB_TABLE_GENERATOR_ERR_INDEX_COL;
$return =& DB_Table_Generator::throwError($code, $field);
}
}
}
}
}
// Generate index definitions, if any, as php code
$n_idx = 0;
$u = array();
$this->idx[$table] = array();
$this->primary_key[$table] = null;
foreach ($DB_indexes as $type => $indexes) {
if (count($indexes) > 0) {
foreach ($indexes as $name => $fields) {
$this->idx[$table][$name] = array();
$this->idx[$table][$name]['type'] = $type;
if (count($fields) == 1) {
$key = $fields[0];
} else {
$key = array();
foreach ($fields as $value) {
$key[] = $value;
}
}
$this->idx[$table][$name]['cols'] = $key;
if ($type == 'primary') {
$this->primary_key[$table] = $key;
}
}
}
}
if ($this->backend == 'mdb2') {
// Restore original MDB2 'idxname_format' and 'portability'
$this->db->setOption('idxname_format', $idxname_format);
$this->db->setOption('portability', $portability);
}
return true;
}
