aboutsummaryrefslogtreecommitdiffstats
path: root/phpBB/phpbb/db/tools.php
diff options
context:
space:
mode:
Diffstat (limited to 'phpBB/phpbb/db/tools.php')
-rw-r--r--phpBB/phpbb/db/tools.php640
1 files changed, 473 insertions, 167 deletions
diff --git a/phpBB/phpbb/db/tools.php b/phpBB/phpbb/db/tools.php
index 3a7207e743..a983ed91b5 100644
--- a/phpBB/phpbb/db/tools.php
+++ b/phpBB/phpbb/db/tools.php
@@ -34,6 +34,12 @@ class tools
var $dbms_type_map = array();
/**
+ * Is the used MS SQL Server a SQL Server 2000?
+ * @var bool
+ */
+ protected $is_sql_server_2000;
+
+ /**
* Get the column types for every database we support
*
* @return array
@@ -251,6 +257,36 @@ class tools
'VARBINARY' => 'blob',
),
+ 'sqlite3' => array(
+ 'INT:' => 'INT(%d)',
+ 'BINT' => 'BIGINT(20)',
+ 'UINT' => 'INTEGER UNSIGNED',
+ 'UINT:' => 'INTEGER UNSIGNED',
+ 'TINT:' => 'TINYINT(%d)',
+ 'USINT' => 'INTEGER UNSIGNED',
+ 'BOOL' => 'INTEGER UNSIGNED',
+ 'VCHAR' => 'VARCHAR(255)',
+ 'VCHAR:' => 'VARCHAR(%d)',
+ 'CHAR:' => 'CHAR(%d)',
+ 'XSTEXT' => 'TEXT(65535)',
+ 'STEXT' => 'TEXT(65535)',
+ 'TEXT' => 'TEXT(65535)',
+ 'MTEXT' => 'MEDIUMTEXT(16777215)',
+ 'XSTEXT_UNI'=> 'TEXT(65535)',
+ 'STEXT_UNI' => 'TEXT(65535)',
+ 'TEXT_UNI' => 'TEXT(65535)',
+ 'MTEXT_UNI' => 'MEDIUMTEXT(16777215)',
+ 'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
+ 'DECIMAL' => 'DECIMAL(5,2)',
+ 'DECIMAL:' => 'DECIMAL(%d,2)',
+ 'PDECIMAL' => 'DECIMAL(6,3)',
+ 'PDECIMAL:' => 'DECIMAL(%d,3)',
+ 'VCHAR_UNI' => 'VARCHAR(255)',
+ 'VCHAR_UNI:'=> 'VARCHAR(%d)',
+ 'VCHAR_CI' => 'VARCHAR(255)',
+ 'VARBINARY' => 'BLOB',
+ ),
+
'postgres' => array(
'INT:' => 'INT4',
'BINT' => 'INT8',
@@ -293,7 +329,7 @@ class tools
* 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', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
+ var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite', 'sqlite3');
/**
* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
@@ -304,10 +340,10 @@ class tools
/**
* Constructor. Set DB Object and set {@link $return_statements return_statements}.
*
- * @param \phpbb\db\driver\driver $db Database connection
+ * @param \phpbb\db\driver\driver_interface $db Database connection
* @param bool $return_statements True if only statements should be returned and no SQL being executed
*/
- public function __construct(\phpbb\db\driver\driver $db, $return_statements = false)
+ public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
{
$this->db = $db;
$this->return_statements = $return_statements;
@@ -383,6 +419,13 @@ class tools
WHERE type = "table"';
break;
+ case 'sqlite3':
+ $sql = 'SELECT name
+ FROM sqlite_master
+ WHERE type = "table"
+ AND name <> "sqlite_sequence"';
+ break;
+
case 'mssql':
case 'mssql_odbc':
case 'mssqlnative':
@@ -467,9 +510,6 @@ class tools
// 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;
@@ -486,6 +526,15 @@ class tools
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
foreach ($table_data['COLUMNS'] as $column_name => $column_data)
{
@@ -516,12 +565,6 @@ class tools
$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
if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
{
@@ -536,13 +579,9 @@ class tools
switch ($this->sql_layer)
{
case 'firebird':
- $table_sql .= "\n);";
- $statements[] = $table_sql;
- break;
-
case 'mssql':
case 'mssqlnative':
- $table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '');
+ $table_sql .= "\n);";
$statements[] = $table_sql;
break;
}
@@ -565,6 +604,7 @@ class tools
case 'mysql_41':
case 'postgres':
case 'sqlite':
+ case 'sqlite3':
$table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
break;
@@ -602,6 +642,7 @@ class tools
case 'mysql_40':
case 'sqlite':
+ case 'sqlite3':
$table_sql .= "\n);";
$statements[] = $table_sql;
break;
@@ -720,7 +761,7 @@ class tools
$sqlite = false;
// For SQLite we need to perform the schema changes in a much more different way
- if ($this->db->sql_layer == 'sqlite' && $this->return_statements)
+ if (($this->db->sql_layer == 'sqlite' || $this->db->sql_layer == 'sqlite3') && $this->return_statements)
{
$sqlite_data = array();
$sqlite = true;
@@ -896,7 +937,7 @@ class tools
}
}
- // Add unqiue indexes?
+ // Add unique indexes?
if (!empty($schema_changes['add_unique_index']))
{
foreach ($schema_changes['add_unique_index'] as $table => $index_array)
@@ -1138,6 +1179,7 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
$sql = "SELECT sql
FROM sqlite_master
WHERE type = 'table'
@@ -1271,6 +1313,7 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
$sql = "PRAGMA index_list('" . $table_name . "');";
$col = 'name';
break;
@@ -1291,6 +1334,7 @@ class tools
case 'oracle':
case 'postgres':
case 'sqlite':
+ case 'sqlite3':
$row[$col] = substr($row[$col], strlen($table_name) + 1);
break;
}
@@ -1307,7 +1351,7 @@ class tools
}
/**
- * Check if a specified index exists in table. Does not return PRIMARY KEY and UNIQUE indexes.
+ * 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
@@ -1375,6 +1419,7 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
$sql = "PRAGMA index_list('" . $table_name . "');";
$col = 'name';
break;
@@ -1388,7 +1433,7 @@ class tools
continue;
}
- if ($this->sql_layer == 'sqlite' && !$row['unique'])
+ if (($this->sql_layer == 'sqlite' || $this->sql_layer == 'sqlite3') && !$row['unique'])
{
continue;
}
@@ -1416,6 +1461,7 @@ class tools
case 'firebird':
case 'postgres':
case 'sqlite':
+ case 'sqlite3':
$row[$col] = substr($row[$col], strlen($table_name) + 1);
break;
}
@@ -1627,11 +1673,17 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
$return_array['primary_key_set'] = false;
if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
{
$sql .= ' INTEGER PRIMARY KEY';
$return_array['primary_key_set'] = true;
+
+ if ($this->sql_layer === 'sqlite3')
+ {
+ $sql .= ' AUTOINCREMENT';
+ }
}
else
{
@@ -1768,67 +1820,63 @@ class tools
break;
case 'sqlite':
-
if ($inline && $this->return_statements)
{
return $column_name . ' ' . $column_data['column_type_sql'];
}
- if (version_compare(sqlite_libversion(), '3.0') == -1)
+ $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+ if (empty($recreate_queries))
{
- $sql = "SELECT sql
- FROM sqlite_master
- WHERE type = 'table'
- AND name = '{$table_name}'
- ORDER BY type DESC, name;";
- $result = $this->db->sql_query($sql);
-
- if (!$result)
- {
- break;
- }
+ break;
+ }
- $row = $this->db->sql_fetchrow($result);
- $this->db->sql_freeresult($result);
+ $statements[] = 'begin';
- $statements[] = 'begin';
+ $sql_create_table = array_shift($recreate_queries);
- // Create a backup table and populate it, destroy the existing one
- $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
- $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
- $statements[] = 'DROP TABLE ' . $table_name;
+ // Create a backup table and populate it, destroy the existing one
+ $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
+ $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
+ $statements[] = 'DROP TABLE ' . $table_name;
- preg_match('#\((.*)\)#s', $row['sql'], $matches);
+ preg_match('#\((.*)\)#s', $sql_create_table, $matches);
- $new_table_cols = trim($matches[1]);
- $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
- $column_list = array();
+ $new_table_cols = trim($matches[1]);
+ $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
+ $column_list = array();
- foreach ($old_table_cols as $declaration)
+ foreach ($old_table_cols as $declaration)
+ {
+ $entities = preg_split('#\s+#', trim($declaration));
+ if ($entities[0] == 'PRIMARY')
{
- $entities = preg_split('#\s+#', trim($declaration));
- if ($entities[0] == 'PRIMARY')
- {
- continue;
- }
- $column_list[] = $entities[0];
+ continue;
}
+ $column_list[] = $entities[0];
+ }
- $columns = implode(',', $column_list);
+ $columns = implode(',', $column_list);
- $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
+ $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
- // create a new table and fill it up. destroy the temp one
- $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
- $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
- $statements[] = 'DROP TABLE ' . $table_name . '_temp';
+ // create a new table and fill it up. destroy the temp one
+ $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
+ $statements = array_merge($statements, $recreate_queries);
- $statements[] = 'commit';
- }
- else
+ $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
+ $statements[] = 'DROP TABLE ' . $table_name . '_temp';
+
+ $statements[] = 'commit';
+ break;
+
+ case 'sqlite3':
+ if ($inline && $this->return_statements)
{
- $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
+ return $column_name . ' ' . $column_data['column_type_sql'];
}
+
+ $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
break;
}
@@ -1850,23 +1898,46 @@ class tools
case 'mssql':
case 'mssqlnative':
- // remove default cosntraints first
- // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
- $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";
+ // We need the data here
+ $old_return_statements = $this->return_statements;
+ $this->return_statements = true;
+
+ $indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
+
+ // Drop any indexes
+ $recreate_indexes = array();
+ if (!empty($indexes))
+ {
+ foreach ($indexes as $index_name => $index_data)
+ {
+ $result = $this->sql_index_drop($table_name, $index_name);
+ $statements = array_merge($statements, $result);
+ if (sizeof($index_data) > 1)
+ {
+ // Remove this column from the index and recreate it
+ $recreate_indexes[$index_name] = array_diff($index_data, array($column_name));
+ }
+ }
+ }
+
+ // Drop default value constraint
+ $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
+ $statements = array_merge($statements, $result);
+
+ // Remove the column
$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
+
+ if (!empty($recreate_indexes))
+ {
+ // Recreate indexes after we removed the column
+ foreach ($recreate_indexes as $index_name => $index_data)
+ {
+ $result = $this->sql_create_index($table_name, $index_name, $index_data);
+ $statements = array_merge($statements, $result);
+ }
+ }
+
+ $this->return_statements = $old_return_statements;
break;
case 'mysql_40':
@@ -1883,67 +1954,61 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
if ($inline && $this->return_statements)
{
return $column_name;
}
- if (version_compare(sqlite_libversion(), '3.0') == -1)
+ $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name);
+ if (empty($recreate_queries))
{
- $sql = "SELECT sql
- FROM sqlite_master
- WHERE type = 'table'
- AND name = '{$table_name}'
- ORDER BY type DESC, name;";
- $result = $this->db->sql_query($sql);
-
- if (!$result)
- {
- break;
- }
+ break;
+ }
- $row = $this->db->sql_fetchrow($result);
- $this->db->sql_freeresult($result);
+ $statements[] = 'begin';
- $statements[] = 'begin';
+ $sql_create_table = array_shift($recreate_queries);
- // Create a backup table and populate it, destroy the existing one
- $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
- $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
- $statements[] = 'DROP TABLE ' . $table_name;
+ // Create a backup table and populate it, destroy the existing one
+ $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
+ $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
+ $statements[] = 'DROP TABLE ' . $table_name;
- preg_match('#\((.*)\)#s', $row['sql'], $matches);
+ preg_match('#\((.*)\)#s', $sql_create_table, $matches);
- $new_table_cols = trim($matches[1]);
- $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
- $column_list = array();
+ $new_table_cols = trim($matches[1]);
+ $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
+ $column_list = array();
- foreach ($old_table_cols as $declaration)
+ foreach ($old_table_cols as $declaration)
+ {
+ $entities = preg_split('#\s+#', trim($declaration));
+ if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
{
- $entities = preg_split('#\s+#', trim($declaration));
- if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
- {
- continue;
- }
- $column_list[] = $entities[0];
+ continue;
}
+ $column_list[] = $entities[0];
+ }
- $columns = implode(',', $column_list);
-
- $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
-
- // create a new table and fill it up. destroy the temp one
- $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
- $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
- $statements[] = 'DROP TABLE ' . $table_name . '_temp';
+ $columns = implode(',', $column_list);
- $statements[] = 'commit';
- }
- else
+ $new_table_cols = trim(preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols));
+ if (substr($new_table_cols, -1) === ',')
{
- $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
+ // Remove the comma from the last entry again
+ $new_table_cols = substr($new_table_cols, 0, -1);
}
+
+ // create a new table and fill it up. destroy the temp one
+ $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
+ $statements = array_merge($statements, $recreate_queries);
+
+ $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
+ $statements[] = 'DROP TABLE ' . $table_name . '_temp';
+
+ $statements[] = 'commit';
break;
}
@@ -1973,6 +2038,7 @@ class tools
case 'oracle':
case 'postgres':
case 'sqlite':
+ case 'sqlite3':
$statements[] = 'DROP INDEX ' . $table_name . '_' . $index_name;
break;
}
@@ -2069,7 +2135,7 @@ class tools
$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;
@@ -2079,35 +2145,29 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
if ($inline && $this->return_statements)
{
return $column;
}
- $sql = "SELECT sql
- FROM sqlite_master
- WHERE type = 'table'
- AND name = '{$table_name}'
- ORDER BY type DESC, name;";
- $result = $this->db->sql_query($sql);
-
- if (!$result)
+ $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+ if (empty($recreate_queries))
{
break;
}
- $row = $this->db->sql_fetchrow($result);
- $this->db->sql_freeresult($result);
-
$statements[] = 'begin';
+ $sql_create_table = array_shift($recreate_queries);
+
// Create a backup table and populate it, destroy the existing one
- $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
+ $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
$statements[] = 'DROP TABLE ' . $table_name;
- preg_match('#\((.*)\)#s', $row['sql'], $matches);
+ preg_match('#\((.*)\)#s', $sql_create_table, $matches);
$new_table_cols = trim($matches[1]);
$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
@@ -2127,6 +2187,8 @@ class tools
// create a new table and fill it up. destroy the temp one
$statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
+ $statements = array_merge($statements, $recreate_queries);
+
$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
$statements[] = 'DROP TABLE ' . $table_name . '_temp';
@@ -2157,6 +2219,7 @@ class tools
case 'postgres':
case 'oracle':
case 'sqlite':
+ case 'sqlite3':
$statements[] = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
break;
@@ -2167,7 +2230,7 @@ class tools
case 'mssql':
case 'mssqlnative':
- $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
+ $statements[] = 'CREATE UNIQUE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
break;
}
@@ -2200,6 +2263,7 @@ class tools
case 'postgres':
case 'oracle':
case 'sqlite':
+ case 'sqlite3':
$statements[] = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
break;
@@ -2220,7 +2284,7 @@ class tools
case 'mssql':
case 'mssqlnative':
- $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
+ $statements[] = 'CREATE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
break;
}
@@ -2291,6 +2355,7 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
$sql = "PRAGMA index_info('" . $table_name . "');";
$col = 'name';
break;
@@ -2310,6 +2375,7 @@ class tools
case 'oracle':
case 'postgres':
case 'sqlite':
+ case 'sqlite3':
$row[$col] = substr($row[$col], strlen($table_name) + 1);
break;
}
@@ -2348,28 +2414,46 @@ class tools
case 'mssql':
case 'mssqlnative':
+ // We need the data here
+ $old_return_statements = $this->return_statements;
+ $this->return_statements = true;
+
+ $indexes = $this->mssql_get_existing_indexes($table_name, $column_name);
+
+ // Drop any indexes
+ if (!empty($indexes))
+ {
+ foreach ($indexes as $index_name => $index_data)
+ {
+ $result = $this->sql_index_drop($table_name, $index_name);
+ $statements = array_merge($statements, $result);
+ }
+ }
+
+ // Drop default value constraint
+ $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
+ $statements = array_merge($statements, $result);
+
+ // Change the column
$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
if (!empty($column_data['default']))
{
- // Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage
- $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
- SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]'
- EXEC(@cmd)";
+ // Add new default value constraint
+ $statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $this->db->sql_escape($column_data['default']) . ' FOR [' . $column_name . ']';
+ }
+
+ if (!empty($indexes))
+ {
+ // Recreate indexes after we changed the column
+ foreach ($indexes as $index_name => $index_data)
+ {
+ $result = $this->sql_create_index($table_name, $index_name, $index_data);
+ $statements = array_merge($statements, $result);
+ }
}
+
+ $this->return_statements = $old_return_statements;
break;
case 'mysql_40':
@@ -2445,35 +2529,29 @@ class tools
break;
case 'sqlite':
+ case 'sqlite3':
if ($inline && $this->return_statements)
{
return $column_name . ' ' . $column_data['column_type_sql'];
}
- $sql = "SELECT sql
- FROM sqlite_master
- WHERE type = 'table'
- AND name = '{$table_name}'
- ORDER BY type DESC, name;";
- $result = $this->db->sql_query($sql);
-
- if (!$result)
+ $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
+ if (empty($recreate_queries))
{
break;
}
- $row = $this->db->sql_fetchrow($result);
- $this->db->sql_freeresult($result);
-
$statements[] = 'begin';
+ $sql_create_table = array_shift($recreate_queries);
+
// Create a temp table and populate it, destroy the existing one
- $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
+ $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
$statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
$statements[] = 'DROP TABLE ' . $table_name;
- preg_match('#\((.*)\)#s', $row['sql'], $matches);
+ preg_match('#\((.*)\)#s', $sql_create_table, $matches);
$new_table_cols = trim($matches[1]);
$old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
@@ -2491,8 +2569,10 @@ class tools
$columns = implode(',', $column_list);
- // create a new table and fill it up. destroy the temp one
+ // Create a new table and fill it up. destroy the temp one
$statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
+ $statements = array_merge($statements, $recreate_queries);
+
$statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
$statements[] = 'DROP TABLE ' . $table_name . '_temp';
@@ -2503,4 +2583,230 @@ class tools
return $this->_sql_run_sql($statements);
}
+
+ /**
+ * Get queries to drop the default constraints of a column
+ *
+ * We need to drop the default constraints of a column,
+ * before being able to change their type or deleting them.
+ *
+ * @param string $table_name
+ * @param string $column_name
+ * @return array Array with SQL statements
+ */
+ protected function mssql_get_drop_default_constraints_queries($table_name, $column_name)
+ {
+ $statements = array();
+ if ($this->mssql_is_sql_server_2000())
+ {
+ // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
+ // Deprecated in SQL Server 2005
+ $sql = "SELECT so.name AS def_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}')";
+ }
+ 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);
+ while ($row = $this->db->sql_fetchrow($result))
+ {
+ $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
+ }
+ $this->db->sql_freeresult($result);
+
+ return $statements;
+ }
+
+ /**
+ * Get a list with existing indexes for the column
+ *
+ * @param string $table_name
+ * @param string $column_name
+ * @return array Array with Index name => columns
+ */
+ protected function mssql_get_existing_indexes($table_name, $column_name)
+ {
+ $existing_indexes = array();
+ if ($this->mssql_is_sql_server_2000())
+ {
+ // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
+ // Deprecated in SQL Server 2005
+ $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
+ FROM sysindexes ix
+ INNER JOIN sysindexkeys ixc
+ ON ixc.id = ix.id
+ AND ixc.indid = ix.indid
+ INNER JOIN syscolumns cols
+ ON cols.colid = ixc.colid
+ AND cols.id = ix.id
+ WHERE ix.id = object_id('{$table_name}')
+ AND cols.name = '{$column_name}'";
+ }
+ else
+ {
+ $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
+ FROM sys.indexes ix
+ INNER JOIN sys.index_columns ixc
+ ON ixc.object_id = ix.object_id
+ AND ixc.index_id = ix.index_id
+ INNER JOIN sys.columns cols
+ ON cols.column_id = ixc.column_id
+ AND cols.object_id = ix.object_id
+ WHERE ix.object_id = object_id('{$table_name}')
+ AND cols.name = '{$column_name}'";
+ }
+
+ $result = $this->db->sql_query($sql);
+ $existing_indexes = array();
+ while ($row = $this->db->sql_fetchrow($result))
+ {
+ $existing_indexes[$row['phpbb_index_name']] = array();
+ }
+ $this->db->sql_freeresult($result);
+
+ if (empty($existing_indexes))
+ {
+ return array();
+ }
+
+ if ($this->mssql_is_sql_server_2000())
+ {
+ $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
+ FROM sysindexes ix
+ INNER JOIN sysindexkeys ixc
+ ON ixc.id = ix.id
+ AND ixc.indid = ix.indid
+ INNER JOIN syscolumns cols
+ ON cols.colid = ixc.colid
+ AND cols.id = ix.id
+ WHERE ix.id = object_id('{$table_name}')
+ AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
+ }
+ else
+ {
+ $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
+ FROM sys.indexes ix
+ INNER JOIN sys.index_columns ixc
+ ON ixc.object_id = ix.object_id
+ AND ixc.index_id = ix.index_id
+ INNER JOIN sys.columns cols
+ ON cols.column_id = ixc.column_id
+ AND cols.object_id = ix.object_id
+ WHERE ix.object_id = object_id('{$table_name}')
+ AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
+ }
+
+ $result = $this->db->sql_query($sql);
+
+ while ($row = $this->db->sql_fetchrow($result))
+ {
+ $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
+ }
+ $this->db->sql_freeresult($result);
+
+ return $existing_indexes;
+ }
+
+ /**
+ * Is the used MS SQL Server a SQL Server 2000?
+ *
+ * @return bool
+ */
+ protected function mssql_is_sql_server_2000()
+ {
+ if ($this->is_sql_server_2000 === null)
+ {
+ $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
+ $result = $this->db->sql_query($sql);
+ $properties = $this->db->sql_fetchrow($result);
+ $this->db->sql_freeresult($result);
+ $this->is_sql_server_2000 = $properties['mssql_version'][0] == '8';
+ }
+
+ return $this->is_sql_server_2000;
+ }
+
+ /**
+ * Returns the Queries which are required to recreate a table including indexes
+ *
+ * @param string $table_name
+ * @param string $remove_column When we drop a column, we remove the column
+ * from all indexes. If the index has no other
+ * column, we drop it completly.
+ * @return array
+ */
+ protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '')
+ {
+ $queries = array();
+
+ $sql = "SELECT sql
+ FROM sqlite_master
+ WHERE type = 'table'
+ AND name = '{$table_name}'";
+ $result = $this->db->sql_query($sql);
+ $sql_create_table = $this->db->sql_fetchfield('sql');
+ $this->db->sql_freeresult($result);
+
+ if (!$sql_create_table)
+ {
+ return array();
+ }
+ $queries[] = $sql_create_table;
+
+ $sql = "SELECT sql
+ FROM sqlite_master
+ WHERE type = 'index'
+ AND tbl_name = '{$table_name}'";
+ $result = $this->db->sql_query($sql);
+ while ($sql_create_index = $this->db->sql_fetchfield('sql'))
+ {
+ if ($remove_column)
+ {
+ $match = array();
+ preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match);
+ if (!isset($match[1]))
+ {
+ continue;
+ }
+
+ // Find and remove $remove_column from the index
+ $columns = explode(', ', $match[1]);
+ $found_column = array_search($remove_column, $columns);
+ if ($found_column !== false)
+ {
+ unset($columns[$found_column]);
+
+ // If the column list is not empty add the index to the list
+ if (!empty($columns))
+ {
+ $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index);
+ }
+ }
+ else
+ {
+ $queries[] = $sql_create_index;
+ }
+ }
+ else
+ {
+ $queries[] = $sql_create_index;
+ }
+ }
+ $this->db->sql_freeresult($result);
+
+ return $queries;
+ }
}