diff options
| author | Patrick Webster <noxwizard@phpbb.com> | 2014-03-03 00:37:22 -0600 |
|---|---|---|
| committer | Patrick Webster <noxwizard@phpbb.com> | 2014-03-28 17:05:37 -0500 |
| commit | aaa846cb3a008a76ad2e2d6f457de5995cd3a9db (patch) | |
| tree | ec18fc3f1d1d0236ece7f5f97a7edd74c0a3a8e5 | |
| parent | 31e610f0b1a9f22701660e72d63fe2290298d812 (diff) | |
| download | forums-aaa846cb3a008a76ad2e2d6f457de5995cd3a9db.tar forums-aaa846cb3a008a76ad2e2d6f457de5995cd3a9db.tar.gz forums-aaa846cb3a008a76ad2e2d6f457de5995cd3a9db.tar.bz2 forums-aaa846cb3a008a76ad2e2d6f457de5995cd3a9db.tar.xz forums-aaa846cb3a008a76ad2e2d6f457de5995cd3a9db.zip | |
[ticket/9725] Do not use deprecated views to remove default constraints
PHPBB3-9725
| -rw-r--r-- | phpBB/includes/db/db_tools.php | 116 |
1 files changed, 84 insertions, 32 deletions
diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php index 8372f90369..5645d04867 100644 --- a/phpBB/includes/db/db_tools.php +++ b/phpBB/includes/db/db_tools.php @@ -1815,22 +1815,49 @@ class phpbb_db_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"; + $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; @@ -2317,23 +2344,48 @@ class phpbb_db_tools 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 - $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)"; + 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 + 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)"; + } + 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; |