| Line 131 | Line 131 |
|---|
),
'mssql' => array(
|
),
'mssql' => array(
|
| | 'INT:' => '[int]',
'BINT' => '[float]',
'UINT' => '[int]',
'UINT:' => '[int]',
'TINT:' => '[int]',
'USINT' => '[int]',
'BOOL' => '[int]',
'VCHAR' => '[varchar] (255)',
'VCHAR:' => '[varchar] (%d)',
'CHAR:' => '[char] (%d)',
'XSTEXT' => '[varchar] (1000)',
'STEXT' => '[varchar] (3000)',
'TEXT' => '[varchar] (8000)',
'MTEXT' => '[text]',
'XSTEXT_UNI'=> '[varchar] (100)',
'STEXT_UNI' => '[varchar] (255)',
'TEXT_UNI' => '[varchar] (4000)',
'MTEXT_UNI' => '[text]',
'TIMESTAMP' => '[int]',
'DECIMAL' => '[float]',
'DECIMAL:' => '[float]',
'PDECIMAL' => '[float]',
'PDECIMAL:' => '[float]',
'VCHAR_UNI' => '[varchar] (255)',
'VCHAR_UNI:'=> '[varchar] (%d)',
'VCHAR_CI' => '[varchar] (255)',
'VARBINARY' => '[varchar] (255)',
),
'mssqlnative' => array(
|
'INT:' => '[int]',
'BINT' => '[float]',
'UINT' => '[int]',
| 'INT:' => '[int]',
'BINT' => '[float]',
'UINT' => '[int]',
|
| Line 261 | Line 291 |
|---|
* A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
* @var array
*/
|
* A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
* @var array
*/
|
var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
| var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
|
/**
* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
|
/**
* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
|
| Line 305 | Line 335 |
|---|
case 'mssql':
case 'mssql_odbc':
$this->sql_layer = 'mssql';
|
case 'mssql':
case 'mssql_odbc':
$this->sql_layer = 'mssql';
|
| | break;
case 'mssqlnative':
$this->sql_layer = 'mssqlnative';
|
break;
default:
$this->sql_layer = $this->db->sql_layer;
break;
}
|
break;
default:
$this->sql_layer = $this->db->sql_layer;
break;
}
|
| | }
/**
* Gets a list of tables in the database.
*
* @return array Array of table names (all lower case)
*/
function sql_list_tables()
{
switch ($this->db->sql_layer)
{
case 'mysql':
case 'mysql4':
case 'mysqli':
$sql = 'SHOW TABLES';
break;
case 'sqlite':
$sql = 'SELECT name
FROM sqlite_master
WHERE type = "table"';
break;
case 'mssql':
case 'mssql_odbc':
case 'mssqlnative':
$sql = "SELECT name
FROM sysobjects
WHERE type='U'";
break;
case 'postgres':
$sql = 'SELECT relname
FROM pg_stat_user_tables';
break;
case 'firebird':
$sql = 'SELECT rdb$relation_name
FROM rdb$relations
WHERE rdb$view_source is null
AND rdb$system_flag = 0';
break;
case 'oracle':
$sql = 'SELECT table_name
FROM USER_TABLES';
break;
}
$result = $this->db->sql_query($sql);
$tables = array();
while ($row = $this->db->sql_fetchrow($result))
{
$name = current($row);
$tables[$name] = $name;
}
$this->db->sql_freeresult($result);
return $tables;
|
}
/**
| }
/**
|
| Line 357 | Line 451 |
|---|
// Determine if we have created a PRIMARY KEY in the earliest
$primary_key_gen = false;
|
// Determine if we have created a PRIMARY KEY in the earliest
$primary_key_gen = false;
|
// Determine if the table must be created with TEXTIMAGE
$create_textimage = false;
| |
// Determine if the table requires a sequence
$create_sequence = false;
|
// Determine if the table requires a sequence
$create_sequence = false;
|
| Line 368 | Line 459 |
|---|
switch ($this->sql_layer)
{
case 'mssql':
|
switch ($this->sql_layer)
{
case 'mssql':
|
| | case 'mssqlnative':
|
$table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
break;
default:
$table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
break;
|
$table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
break;
default:
$table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
break;
|
| | }
if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
{
if (!isset($table_data['PRIMARY_KEY']))
{
$table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment');
$table_data['PRIMARY_KEY'] = 'mssqlindex';
}
|
}
// Iterate through the columns to create a table
| }
// Iterate through the columns to create a table
|
| Line 381 | Line 482 |
|---|
{
// here lies an array, filled with information compiled on the column's data
$prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
|
{
// here lies an array, filled with information compiled on the column's data
$prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
|
| |
if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "${column_name}_gen"
{
trigger_error("Index name '${column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR);
}
|
// here we add the definition of the new column to the list of columns
switch ($this->sql_layer)
{
case 'mssql':
|
// here we add the definition of the new column to the list of columns
switch ($this->sql_layer)
{
case 'mssql':
|
| | case 'mssqlnative':
|
$columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
break;
| $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
break;
|
| Line 398 | Line 505 |
|---|
if (!$primary_key_gen)
{
$primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
|
if (!$primary_key_gen)
{
$primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
|
}
// create textimage DDL based off of the existance of certain column types
if (!$create_textimage)
{
$create_textimage = isset($prepared_column['textimage']) && $prepared_column['textimage'];
| |
}
// create sequence DDL based off of the existance of auto incrementing columns
| }
// create sequence DDL based off of the existance of auto incrementing columns
|
| Line 420 | Line 521 |
|---|
switch ($this->sql_layer)
{
case 'firebird':
|
switch ($this->sql_layer)
{
case 'firebird':
|
| | case 'mssql':
case 'mssqlnative':
|
$table_sql .= "\n);";
|
$table_sql .= "\n);";
|
$statements[] = $table_sql;
break;
case 'mssql':
$table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '');
| |
$statements[] = $table_sql;
break;
}
| $statements[] = $table_sql;
break;
}
|
| Line 453 | Line 551 |
|---|
case 'firebird':
case 'mssql':
|
case 'firebird':
case 'mssql':
|
| | case 'mssqlnative':
|
// We need the data here
$old_return_statements = $this->return_statements;
$this->return_statements = true;
| // We need the data here
$old_return_statements = $this->return_statements;
$this->return_statements = true;
|
| Line 500 | Line 599 |
|---|
break;
case 'oracle':
|
break;
case 'oracle':
|
$table_sql .= "\n);";
| $table_sql .= "\n)";
|
$statements[] = $table_sql;
// do we need to add a sequence and a tigger for auto incrementing columns?
| $statements[] = $table_sql;
// do we need to add a sequence and a tigger for auto incrementing columns?
|
| Line 518 | Line 617 |
|---|
$trigger .= "BEGIN\n";
$trigger .= "\tSELECT {$table_name}_seq.nextval\n";
$trigger .= "\tINTO :new.{$create_sequence}\n";
|
$trigger .= "BEGIN\n";
$trigger .= "\tSELECT {$table_name}_seq.nextval\n";
$trigger .= "\tINTO :new.{$create_sequence}\n";
|
$trigger .= "\tFROM dual\n";
| $trigger .= "\tFROM dual;\n";
|
$trigger .= "END;";
$statements[] = $trigger;
| $trigger .= "END;";
$statements[] = $trigger;
|
| Line 528 | Line 627 |
|---|
case 'firebird':
if ($create_sequence)
{
|
case 'firebird':
if ($create_sequence)
{
|
$statements[] = "CREATE SEQUENCE {$table_name}_seq;";
| $statements[] = "CREATE GENERATOR {$table_name}_gen;";
$statements[] = "SET GENERATOR {$table_name}_gen TO 0;";
$trigger = "CREATE TRIGGER t_$table_name FOR $table_name\n";
$trigger .= "BEFORE INSERT\nAS\nBEGIN\n";
$trigger .= "\tNEW.{$create_sequence} = GEN_ID({$table_name}_gen, 1);\nEND;";
$statements[] = $trigger;
|
}
break;
}
| }
break;
}
|
| Line 573 | Line 678 |
|---|
* drop_columns: Removing/Dropping columns
* add_primary_keys: adding primary keys
* add_unique_index: adding an unique index
|
* drop_columns: Removing/Dropping columns
* add_primary_keys: adding primary keys
* add_unique_index: adding an unique index
|
* add_index: adding an index
| * add_index: adding an index (can be column:index_size if you need to provide size)
|
*
* The values are in this format:
* {TABLE NAME} => array(
| *
* The values are in this format:
* {TABLE NAME} => array(
|
| Line 598 | Line 703 |
|---|
{
$sqlite_data = array();
$sqlite = true;
|
{
$sqlite_data = array();
$sqlite = true;
|
| | }
// Drop tables?
if (!empty($schema_changes['drop_tables']))
{
foreach ($schema_changes['drop_tables'] as $table)
{
// only drop table if it exists
if ($this->sql_table_exists($table))
{
$result = $this->sql_table_drop($table);
if ($this->return_statements)
{
$statements = array_merge($statements, $result);
}
}
}
}
// Add tables?
if (!empty($schema_changes['add_tables']))
{
foreach ($schema_changes['add_tables'] as $table => $table_data)
{
$result = $this->sql_create_table($table, $table_data);
if ($this->return_statements)
{
$statements = array_merge($statements, $result);
}
}
|
}
// Change columns?
| }
// Change columns?
|
| Line 643 | Line 778 |
|---|
{
foreach ($columns as $column_name => $column_data)
{
|
{
foreach ($columns as $column_name => $column_data)
{
|
// Only add the column if it does not exist yet, else change it (to be consistent)
| // Only add the column if it does not exist yet
|
if ($column_exists = $this->sql_column_exists($table, $column_name))
{
|
if ($column_exists = $this->sql_column_exists($table, $column_name))
{
|
$result = $this->sql_column_change($table, $column_name, $column_data, true);
| continue;
// This is commented out here because it can take tremendous time on updates
// $result = $this->sql_column_change($table, $column_name, $column_data, true);
|
}
else
{
| }
else
{
|
| Line 657 | Line 794 |
|---|
{
if ($column_exists)
{
|
{
if ($column_exists)
{
|
$sqlite_data[$table]['change_columns'][] = $result;
| continue;
// $sqlite_data[$table]['change_columns'][] = $result;
|
}
else
{
| }
else
{
|
| Line 679 | Line 817 |
|---|
{
foreach ($indexes as $index_name)
{
|
{
foreach ($indexes as $index_name)
{
|
| | if (!$this->sql_index_exists($table, $index_name))
{
continue;
}
|
$result = $this->sql_index_drop($table, $index_name);
if ($this->return_statements)
| $result = $this->sql_index_drop($table, $index_name);
if ($this->return_statements)
|
| Line 732 | Line 875 |
|---|
}
}
|
}
}
|
// Add unqiue indexes?
| // Add unique indexes?
|
if (!empty($schema_changes['add_unique_index']))
{
foreach ($schema_changes['add_unique_index'] as $table => $index_array)
{
foreach ($index_array as $index_name => $column)
{
|
if (!empty($schema_changes['add_unique_index']))
{
foreach ($schema_changes['add_unique_index'] as $table => $index_array)
{
foreach ($index_array as $index_name => $column)
{
|
| | if ($this->sql_unique_index_exists($table, $index_name))
{
continue;
}
|
$result = $this->sql_create_unique_index($table, $index_name, $column);
if ($this->return_statements)
| $result = $this->sql_create_unique_index($table, $index_name, $column);
if ($this->return_statements)
|
| Line 756 | Line 904 |
|---|
{
foreach ($index_array as $index_name => $column)
{
|
{
foreach ($index_array as $index_name => $column)
{
|
| | if ($this->sql_index_exists($table, $index_name))
{
continue;
}
|
$result = $this->sql_create_index($table, $index_name, $column);
if ($this->return_statements)
| $result = $this->sql_create_index($table, $index_name, $column);
if ($this->return_statements)
|
| Line 914 | Line 1067 |
|---|
}
/**
|
}
/**
|
* Check if a specified column exist
| * Gets a list of columns of a table.
|
*
|
*
|
* @param string $table Table to check the column at
* @param string $column_name The column to check
| * @param string $table Table name
|
*
|
*
|
* @return bool True if column exists, else false
| * @return array Array of column names (all lower case)
|
*/
|
*/
|
function sql_column_exists($table, $column_name)
| function sql_list_columns($table)
|
{
|
{
|
| | $columns = array();
|
switch ($this->sql_layer)
{
case 'mysql_40':
case 'mysql_41':
|
switch ($this->sql_layer)
{
case 'mysql_40':
case 'mysql_41':
|
| |
$sql = "SHOW COLUMNS FROM $table";
|
$sql = "SHOW COLUMNS FROM $table";
|
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
// lower case just in case
if (strtolower($row['Field']) == $column_name)
{
$this->db->sql_freeresult($result);
return true;
}
}
$this->db->sql_freeresult($result);
return false;
| |
break;
// PostgreSQL has a way of doing this in a much simpler way but would
| break;
// PostgreSQL has a way of doing this in a much simpler way but would
|
| Line 952 | Line 1092 |
|---|
WHERE c.relname = '{$table}'
AND a.attnum > 0
AND a.attrelid = c.oid";
|
WHERE c.relname = '{$table}'
AND a.attnum > 0
AND a.attrelid = c.oid";
|
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
// lower case just in case
if (strtolower($row['attname']) == $column_name)
{
$this->db->sql_freeresult($result);
return true;
}
}
$this->db->sql_freeresult($result);
return false;
| |
break;
// same deal with PostgreSQL, we must perform more complex operations than
// we technically could
case 'mssql':
|
break;
// same deal with PostgreSQL, we must perform more complex operations than
// we technically could
case 'mssql':
|
| | case 'mssqlnative':
|
$sql = "SELECT c.name
FROM syscolumns c
LEFT JOIN sysobjects o ON c.id = o.id
WHERE o.name = '{$table}'";
|
$sql = "SELECT c.name
FROM syscolumns c
LEFT JOIN sysobjects o ON c.id = o.id
WHERE o.name = '{$table}'";
|
| | break;
case 'oracle':
$sql = "SELECT column_name
FROM user_tab_columns
WHERE LOWER(table_name) = '" . strtolower($table) . "'";
break;
case 'firebird':
$sql = "SELECT RDB\$FIELD_NAME as FNAME
FROM RDB\$RELATION_FIELDS
WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'";
break;
case 'sqlite':
$sql = "SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND name = '{$table}'";
|
$result = $this->db->sql_query($sql);
|
$result = $this->db->sql_query($sql);
|
| |
if (!$result)
{
return false;
}
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
preg_match('#\((.*)\)#s', $row['sql'], $matches);
$cols = trim($matches[1]);
$col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
foreach ($col_array as $declaration)
{
$entities = preg_split('#\s+#', trim($declaration));
if ($entities[0] == 'PRIMARY')
{
continue;
}
$column = strtolower($entities[0]);
$columns[$column] = $column;
}
return $columns;
break;
}
$result = $this->db->sql_query($sql);
|
while ($row = $this->db->sql_fetchrow($result))
{
|
while ($row = $this->db->sql_fetchrow($result))
{
|
// lower case just in case
if (strtolower($row['name']) == $column_name)
| $column = strtolower(current($row));
$columns[$column] = $column;
}
$this->db->sql_freeresult($result);
return $columns;
}
/**
* Check whether a specified column exist in a table
*
* @param string $table Table to check
* @param string $column_name Column to check
*
* @return bool True if column exists, false otherwise
*/
function sql_column_exists($table, $column_name)
{
$columns = $this->sql_list_columns($table);
return isset($columns[$column_name]);
}
/**
* Check if a specified index exists in table. Does not return PRIMARY KEY and UNIQUE indexes.
*
* @param string $table_name Table to check the index at
* @param string $index_name The index name to check
*
* @return bool True if index exists, else false
*/
function sql_index_exists($table_name, $index_name)
{
if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
{
$sql = "EXEC sp_statistics '$table_name'";
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
if ($row['TYPE'] == 3)
{
if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
|
{
$this->db->sql_freeresult($result);
return true;
|
{
$this->db->sql_freeresult($result);
return true;
|
| | }
|
}
}
$this->db->sql_freeresult($result);
|
}
}
$this->db->sql_freeresult($result);
|
| |
|
return false;
|
return false;
|
| | }
switch ($this->sql_layer)
{
case 'firebird':
$sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
FROM RDB\$INDICES
WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "'
AND RDB\$UNIQUE_FLAG IS NULL
AND RDB\$FOREIGN_KEY IS NULL";
$col = 'index_name';
break;
case 'postgres':
$sql = "SELECT ic.relname as index_name
FROM pg_class bc, pg_class ic, pg_index i
WHERE (bc.oid = i.indrelid)
AND (ic.oid = i.indexrelid)
AND (bc.relname = '" . $table_name . "')
AND (i.indisunique != 't')
AND (i.indisprimary != 't')";
$col = 'index_name';
break;
case 'mysql_40':
case 'mysql_41':
$sql = 'SHOW KEYS
FROM ' . $table_name;
$col = 'Key_name';
|
break;
case 'oracle':
|
break;
case 'oracle':
|
$sql = "SELECT column_name
FROM user_tab_columns
WHERE LOWER(table_name) = '" . strtolower($table) . "'";
| $sql = "SELECT index_name
FROM user_indexes
WHERE table_name = '" . strtoupper($table_name) . "'
AND generated = 'N'
AND uniqueness = 'NONUNIQUE'";
$col = 'index_name';
break;
case 'sqlite':
$sql = "PRAGMA index_list('" . $table_name . "');";
$col = 'name';
break;
}
|
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
|
$result = $this->db->sql_query($sql);
while ($row = $this->db->sql_fetchrow($result))
{
|
// lower case just in case
if (strtolower($row['column_name']) == $column_name)
| if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && !$row['Non_unique'])
{
continue;
}
// These DBMS prefix index name with the table name
switch ($this->sql_layer)
{
case 'firebird':
case 'oracle':
case 'postgres':
case 'sqlite':
$row[$col] = substr($row[$col], strlen($table_name) + 1);
break;
}
if (strtolower($row[$col]) == strtolower($index_name))
|
{
$this->db->sql_freeresult($result);
return true;
}
}
$this->db->sql_freeresult($result);
|
{
$this->db->sql_freeresult($result);
return true;
}
}
$this->db->sql_freeresult($result);
|
| |
|
return false;
|
return false;
|
break;
| }
|
|
|
case 'firebird':
$sql = "SELECT RDB\$FIELD_NAME as FNAME
FROM RDB\$RELATION_FIELDS
WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'";
| /**
* Check if a specified index exists in table. Does not return PRIMARY KEY indexes.
*
* @param string $table_name Table to check the index at
* @param string $index_name The index name to check
*
* @return bool True if index exists, else false
*/
function sql_unique_index_exists($table_name, $index_name)
{
if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
{
$sql = "EXEC sp_statistics '$table_name'";
|
$result = $this->db->sql_query($sql);
|
$result = $this->db->sql_query($sql);
|
| |
|
while ($row = $this->db->sql_fetchrow($result))
{
|
while ($row = $this->db->sql_fetchrow($result))
{
|
// lower case just in case
if (strtolower($row['fname']) == $column_name)
| // Usually NON_UNIQUE is the column we want to check, but we allow for both
if ($row['TYPE'] == 3)
{
if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
|
{
$this->db->sql_freeresult($result);
return true;
|
{
$this->db->sql_freeresult($result);
return true;
|
| | }
|
}
}
$this->db->sql_freeresult($result);
return false;
|
}
}
$this->db->sql_freeresult($result);
return false;
|
| | }
switch ($this->sql_layer)
{
case 'firebird':
$sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
FROM RDB\$INDICES
WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "'
AND RDB\$UNIQUE_FLAG IS NOT NULL
AND RDB\$FOREIGN_KEY IS NULL";
$col = 'index_name';
break;
case 'postgres':
$sql = "SELECT ic.relname as index_name, i.indisunique
FROM pg_class bc, pg_class ic, pg_index i
WHERE (bc.oid = i.indrelid)
AND (ic.oid = i.indexrelid)
AND (bc.relname = '" . $table_name . "')
AND (i.indisprimary != 't')";
$col = 'index_name';
break;
case 'mysql_40':
case 'mysql_41':
$sql = 'SHOW KEYS
FROM ' . $table_name;
$col = 'Key_name';
break;
case 'oracle':
$sql = "SELECT index_name, table_owner
FROM user_indexes
WHERE table_name = '" . strtoupper($table_name) . "'
AND generated = 'N'
AND uniqueness = 'UNIQUE'";
$col = 'index_name';
|
break;
|
break;
|
// ugh, SQLite
| |
case 'sqlite':
|
case 'sqlite':
|
$sql = "SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND name = '{$table}'";
| $sql = "PRAGMA index_list('" . $table_name . "');";
$col = 'name';
break;
}
|
$result = $this->db->sql_query($sql);
|
$result = $this->db->sql_query($sql);
|
| | while ($row = $this->db->sql_fetchrow($result))
{
if (($this->sql_layer == 'mysql_40' || $this->sql_layer == 'mysql_41') && ($row['Non_unique'] || $row[$col] == 'PRIMARY'))
{
continue;
}
|
|
|
if (!$result)
| if ($this->sql_layer == 'sqlite' && !$row['unique'])
|
{
|
{
|
return false;
| continue;
|
}
|
}
|
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
| if ($this->sql_layer == 'postgres' && $row['indisunique'] != 't')
{
continue;
}
|
|
|
preg_match('#\((.*)\)#s', $row['sql'], $matches);
$cols = trim($matches[1]);
$col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
foreach ($col_array as $declaration)
| // These DBMS prefix index name with the table name
switch ($this->sql_layer)
|
{
|
{
|
$entities = preg_split('#\s+#', trim($declaration));
if ($entities[0] == 'PRIMARY')
| case 'oracle':
// Two cases here... prefixed with U_[table_owner] and not prefixed with table_name
if (strpos($row[$col], 'U_') === 0)
|
{
|
{
|
continue;
| $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1);
|
}
|
}
|
| | else if (strpos($row[$col], strtoupper($table_name)) === 0)
{
$row[$col] = substr($row[$col], strlen($table_name) + 1);
}
break;
|
|
|
if (strtolower($entities[0]) == $column_name)
| case 'firebird':
case 'postgres':
case 'sqlite':
$row[$col] = substr($row[$col], strlen($table_name) + 1);
break;
}
if (strtolower($row[$col]) == strtolower($index_name))
|
{
|
{
|
| | $this->db->sql_freeresult($result);
|
return true;
}
}
|
return true;
}
}
|
| | $this->db->sql_freeresult($result);
|
return false;
|
return false;
|
break;
}
| |
}
/**
| }
/**
|
| Line 1100 | Line 1447 |
|---|
*/
function sql_prepare_column_data($table_name, $column_name, $column_data)
{
|
*/
function sql_prepare_column_data($table_name, $column_name, $column_data)
{
|
| | if (strlen($column_name) > 30)
{
trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
}
|
// Get type
if (strpos($column_data[0], ':') !== false)
{
| // Get type
if (strpos($column_data[0], ':') !== false)
{
|
| Line 1187 | Line 1539 |
|---|
break;
case 'mssql':
|
break;
case 'mssql':
|
| | case 'mssqlnative':
|
$sql .= " {$column_type} ";
$sql_default = " {$column_type} ";
| $sql .= " {$column_type} ";
$sql_default = " {$column_type} ";
|
| Line 1331 | Line 1684 |
|---|
switch ($this->sql_layer)
{
case 'firebird':
|
switch ($this->sql_layer)
{
case 'firebird':
|
| | // Does not support AFTER statement, only POSITION (and there you need the column position)
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD "' . strtoupper($column_name) . '" ' . $column_data['column_type_sql'];
break;
case 'mssql':
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD "' . strtoupper($column_name) . '" ' . $column_data['column_type_sql'];
break;
case 'mssql':
|
| | case 'mssqlnative':
// Does not support AFTER, only through temporary table
|
$statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
break;
case 'mysql_40':
case 'mysql_41':
|
$statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
break;
case 'mysql_40':
case 'mysql_41':
|
$statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
| $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : '';
$statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after;
|
break;
case 'oracle':
|
break;
case 'oracle':
|
| | // Does not support AFTER, only through temporary table
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
break;
case 'postgres':
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
break;
case 'postgres':
|
| | // Does not support AFTER, only through temporary table
|
if (version_compare($this->db->sql_server_info(true), '8.0', '>='))
{
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
| if (version_compare($this->db->sql_server_info(true), '8.0', '>='))
{
$statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
|
| Line 1455 | Line 1814 |
|---|
break;
case 'mssql':
|
break;
case 'mssql':
|
| | case 'mssqlnative':
$sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
// Remove default constraints
if ($row['mssql_version'][0] == '8') // SQL Server 2000
{
// http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
// Deprecated in SQL Server 2005
$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
SET @drop_default_name =
(SELECT so.name FROM sysobjects so
JOIN sysconstraints sc ON so.id = sc.constid
WHERE object_name(so.parent_obj) = '{$table_name}'
AND so.xtype = 'D'
AND sc.colid = (SELECT colid FROM syscolumns
WHERE id = object_id('{$table_name}')
AND name = '{$column_name}'))
IF @drop_default_name <> ''
BEGIN
SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
EXEC(@cmd)
END";
}
else
{
$sql = "SELECT dobj.name AS def_name
FROM sys.columns col
LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
WHERE col.object_id = object_id('{$table_name}')
AND col.name = '{$column_name}'
AND dobj.name IS NOT NULL";
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
if ($row)
{
$statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
}
}
|
$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
break;
| $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
break;
|
| Line 1464 | Line 1867 |
|---|
break;
case 'oracle':
|
break;
case 'oracle':
|
$statements[] = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
| $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
|
break;
case 'postgres':
| break;
case 'postgres':
|
| Line 1549 | Line 1952 |
|---|
switch ($this->sql_layer)
{
case 'mssql':
|
switch ($this->sql_layer)
{
case 'mssql':
|
| | case 'mssqlnative':
|
$statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
break;
| $statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
break;
|
| Line 1614 | Line 2018 |
|---|
$statements[] = "DROP SEQUENCE {$row['referenced_name']}";
}
$this->db->sql_freeresult($result);
|
$statements[] = "DROP SEQUENCE {$row['referenced_name']}";
}
$this->db->sql_freeresult($result);
|
| | break;
|
case 'postgres':
// PGSQL does not "tightly" bind sequences and tables, we must guess...
|
case 'postgres':
// PGSQL does not "tightly" bind sequences and tables, we must guess...
|
| Line 1652 | Line 2057 |
|---|
break;
case 'mssql':
|
break;
case 'mssql':
|
| | case 'mssqlnative':
|
$sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
$sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
$sql .= '[' . implode("],\n\t\t[", $column) . ']';
|
$sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
$sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY CLUSTERED (";
$sql .= '[' . implode("],\n\t\t[", $column) . ']';
|
$sql .= ') ON [PRIMARY]';
| $sql .= ')';
|
$statements[] = $sql;
break;
|
$statements[] = $sql;
break;
|
| Line 1729 | Line 2135 |
|---|
function sql_create_unique_index($table_name, $index_name, $column)
{
$statements = array();
|
function sql_create_unique_index($table_name, $index_name, $column)
{
$statements = array();
|
| |
$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
{
$max_length = strlen($table_prefix) + 24;
trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
}
|
switch ($this->sql_layer)
{
|
switch ($this->sql_layer)
{
|
| Line 1741 | Line 2154 |
|---|
case 'mysql_40':
case 'mysql_41':
|
case 'mysql_40':
case 'mysql_41':
|
$statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
| $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
|
break;
case 'mssql':
|
break;
case 'mssql':
|
$statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
| case 'mssqlnative':
$statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
|
break;
}
| break;
}
|
| Line 1758 | Line 2172 |
|---|
function sql_create_index($table_name, $index_name, $column)
{
$statements = array();
|
function sql_create_index($table_name, $index_name, $column)
{
$statements = array();
|
| |
$table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
if (strlen($table_name . $index_name) - strlen($table_prefix) > 24)
{
$max_length = strlen($table_prefix) + 24;
trigger_error("Index name '{$table_name}_$index_name' on table '$table_name' is too long. The maximum is $max_length characters.", E_USER_ERROR);
}
// remove index length unless MySQL4
if ('mysql_40' != $this->sql_layer)
{
$column = preg_replace('#:.*$#', '', $column);
}
|
switch ($this->sql_layer)
{
|
switch ($this->sql_layer)
{
|
| Line 1769 | Line 2196 |
|---|
break;
case 'mysql_40':
|
break;
case 'mysql_40':
|
| | // add index size to definition as required by MySQL4
foreach ($column as $i => $col)
{
if (false !== strpos($col, ':'))
{
list($col, $index_size) = explode(':', $col);
$column[$i] = "$col($index_size)";
}
}
// no break
|
case 'mysql_41':
|
case 'mysql_41':
|
$statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
| $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
|
break;
case 'mssql':
|
break;
case 'mssql':
|
$statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
| case 'mssqlnative':
$statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
|
break;
}
| break;
}
|
| Line 1791 | Line 2229 |
|---|
{
$index_array = array();
|
{
$index_array = array();
|
if ($this->sql_layer == 'mssql')
| if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
|
{
$sql = "EXEC sp_statistics '$table_name'";
$result = $this->db->sql_query($sql);
| {
$sql = "EXEC sp_statistics '$table_name'";
$result = $this->db->sql_query($sql);
|
| Line 1895 | Line 2333 |
|---|
}
else
{
|
}
else
{
|
| | // TODO: try to change pkey without removing trigger, generator or constraints. ATM this query may fail.
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
}
break;
case 'mssql':
|
$statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type'];
}
break;
case 'mssql':
|
| | case 'mssqlnative':
|
$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
if (!empty($column_data['default']))
{
|
$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
if (!empty($column_data['default']))
{
|
| | $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
$result = $this->db->sql_query($sql);
$row = $this->db->sql_fetchrow($result);
$this->db->sql_freeresult($result);
|
// Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
|
// Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
|
| | if ($row['mssql_version'][0] == '8') // SQL Server 2000
{
|
$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
SET @drop_default_name =
(SELECT so.name FROM sysobjects so
| $statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
SET @drop_default_name =
(SELECT so.name FROM sysobjects so
|
| Line 1921 | Line 2368 |
|---|
END
SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
EXEC(@cmd)";
|
END
SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
EXEC(@cmd)";
|
| | }
else
{
$statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000)
SET @drop_default_name =
(SELECT dobj.name FROM sys.columns col
LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
WHERE col.object_id = object_id('{$table_name}')
AND col.name = '{$column_name}'
AND dobj.name IS NOT NULL)
IF @drop_default_name <> ''
BEGIN
SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']'
EXEC(@cmd)
END
SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
EXEC(@cmd)";
}
|
}
break;
| }
break;
|
