/**
*
* Return the definition array for a column.
*
* @access private
*
* @param string $backend The name of the backend ('db' or 'mdb2').
*
* @param string $phptype The DB/MDB2 phptype key.
*
* @param mixed $column A single DB_Table column definition array.
*
* @return mixed|object Declaration string (DB), declaration array (MDB2) or a
* PEAR_Error with a description about the invalidity, otherwise.
*
*/
function _getColumnDefinition($backend, $phptype, $column)
{
static $max_scope;
// prepare variables
$type = isset($column['type']) ? $column['type'] : null;
$size = isset($column['size']) ? $column['size'] : null;
$scope = isset($column['scope']) ? $column['scope'] : null;
$require = isset($column['require']) ? $column['require'] : null;
$default = isset($column['default']) ? $column['default'] : null;
if ($backend == 'db') {
return DB_Table_Manager::getDeclare($phptype, $type, $size, $scope, $require, $default);
} else {
return DB_Table_Manager::getDeclareMDB2($type, $size, $scope, $require, $default, $max_scope);
}
}
/**
*
* Verifies the table based on $this->col and $this->idx.
*
* @access public
*
* @return mixed Boolean true if the verification was successful, and a
* PEAR_Error if verification failed.
*
* @see DB_Table_Manager::verify()
*
*/
function verify()
{
return DB_Table_Manager::verify($this->db, $this->table, $this->col, $this->idx);
}
/**
*
* Creates the table based on $this->col and $this->idx.
*
* @access public
*
* @param mixed $flag Boolean false to abort the create attempt from
* the start, 'drop' to drop the existing table and
* re-create it, or 'safe' to only create the table if it
* does not exist in the database.
*
* @return mixed Boolean false if there was no attempt to create the
* table, boolean true if the attempt succeeded, or a PEAR_Error if
* the attempt failed.
*
* @see DB_Table_Manager::create()
*
*/
function create($flag)
{
// are we OK to create the table?
$ok = false;
// check the create-flag
switch ($flag) {
case 'drop':
// forcibly drop an existing table
$this->db->query("DROP TABLE {$this->table}");
$ok = true;
break;
case 'safe':
// create only if table does not exist
$list = $this->db->getListOf('tables');
// ok to create only if table does not exist
$ok = !in_array($this->table, $list);
break;
default:
// unknown flag
return $this->throwError(DB_TABLE_ERR_CREATE_FLAG, "('{$flag}')");
}
// are we going to create the table?
if (!$ok) {
return false;
} else {
include_once 'DB/Table/Manager.php';
return DB_Table_Manager::create($this->db, $this->table, $this->col, $this->idx, $flag);
}
}
/**
* 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;
}
/**
*
* Create the table based on DB_Table column and index arrays.
*
* @static
*
* @access public
*
* @param object &$db A PEAR DB 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 mixed Boolean false if there was no attempt to create the
* table, boolean true if the attempt succeeded, and a PEAR_Error if
* the attempt failed.
*
*/
function create(&$db, $table, $column_set, $index_set)
{
// columns to be created
$column = array();
// indexes to be created
$index = array();
// is the table name too long?
if (strlen($table) > 30) {
return DB_Table::throwError(DB_TABLE_ERR_TABLE_STRLEN, " ('{$table}')");
}
// -------------------------------------------------------------
//
// validate each column mapping and build the individual
// definitions, and note column indexes as we go.
//
foreach ($column_set as $colname => $val) {
$colname = trim($colname);
// column name cannot be a reserved keyword
$reserved = in_array(strtoupper($colname), $GLOBALS['_DB_TABLE']['reserved']);
if ($reserved) {
return DB_Table::throwError(DB_TABLE_ERR_DECLARE_COLNAME, " ('{$colname}')");
}
// column must be no longer than 30 chars
if (strlen($colname) > 30) {
return DB_Table::throwError(DB_TABLE_ERR_DECLARE_STRLEN, "('{$colname}')");
}
// prepare variables
$type = isset($val['type']) ? $val['type'] : null;
$size = isset($val['size']) ? $val['size'] : null;
$scope = isset($val['scope']) ? $val['scope'] : null;
$require = isset($val['require']) ? $val['require'] : null;
$default = isset($val['default']) ? $val['default'] : null;
// get the declaration string
$result = DB_Table_Manager::getDeclare($db->phptype, $type, $size, $scope, $require, $default);
// did it work?
if (PEAR::isError($result)) {
$result->userinfo .= " ('{$colname}')";
return $result;
}
// add the declaration to the array of all columns
$column[] = "{$colname} {$result}";
}
// -------------------------------------------------------------
//
// validate the indexes.
//
foreach ($index_set as $idxname => $val) {
if (is_string($val)) {
// shorthand for index names: colname => index_type
$type = trim($val);
$cols = trim($idxname);
} elseif (is_array($val)) {
// normal: index_name => array('type' => ..., 'cols' => ...)
$type = isset($val['type']) ? $val['type'] : 'normal';
$cols = isset($val['cols']) ? $val['cols'] : null;
}
// index name cannot be a reserved keyword
$reserved = in_array(strtoupper($idxname), $GLOBALS['_DB_TABLE']['reserved']);
if ($reserved) {
return DB_Table::throwError(DB_TABLE_ERR_DECLARE_IDXNAME, "('{$idxname}')");
}
// are there any columns for the index?
if (!$cols) {
return DB_Table::throwError(DB_TABLE_ERR_IDX_NO_COLS, "('{$idxname}')");
}
// are there any CLOB columns, or any columns that are not
// in the schema?
settype($cols, 'array');
$valid_cols = array_keys($column_set);
foreach ($cols as $colname) {
if (!in_array($colname, $valid_cols)) {
return DB_Table::throwError(DB_TABLE_ERR_IDX_COL_UNDEF, "'{$idxname}' ('{$colname}')");
}
if ($column_set[$colname]['type'] == 'clob') {
return DB_Table::throwError(DB_TABLE_ERR_IDX_COL_CLOB, "'{$idxname}' ('{$colname}')");
}
}
// string of column names
$colstring = implode(', ', $cols);
// we prefix all index names with the table name,
// and suffix all index names with '_idx'. this
// is to soothe PostgreSQL, which demands that index
// names not collide, even when they indexes are on
// different tables.
$newIdxName = $table . '_' . $idxname . '_idx';
// now check the length; must be under 30 chars to
// soothe Oracle.
if (strlen($newIdxName) > 30) {
return DB_Table::throwError(DB_TABLE_ERR_IDX_STRLEN, "'{$idxname}' ('{$newIdxName}')");
}
// create index entry
if ($type == 'unique') {
$index[] = "CREATE UNIQUE INDEX {$newIdxName} ON {$table} ({$colstring})";
} elseif ($type == 'normal') {
$index[] = "CREATE INDEX {$newIdxName} ON {$table} ({$colstring})";
} else {
return DB_Table::throwError(DB_TABLE_ERR_IDX_TYPE, "'{$idxname}' ('{$type}')");
}
}
// -------------------------------------------------------------
//
// now for the real action: create the table and indexes!
//
// build the CREATE TABLE command
$cmd = "CREATE TABLE {$table} (\n\t";
$cmd .= implode(",\n\t", $column);
$cmd .= "\n)";
// attempt to create the table
$result = $db->query($cmd);
if (PEAR::isError($result)) {
return $result;
}
// attempt to create the indexes
foreach ($index as $cmd) {
$result = $db->query($cmd);
if (PEAR::isError($result)) {
return $result;
}
}
// we're done!
return true;
}
