Exemple #1
0
 /**
  * 
  * 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;
 }
Exemple #2
0
 /**
  * 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;
 }