diff options
Diffstat (limited to 'phpBB/phpbb/db/tools.php')
-rw-r--r-- | phpBB/phpbb/db/tools.php | 640 |
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; + } } |