From 80a844d38ce17378bb12b2e84cc7db99932913f5 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sat, 1 Mar 2014 22:14:18 -0600 Subject: [ticket/9725] Fetch Azure db stats from proper table PHPBB3-9725 --- phpBB/includes/functions_admin.php | 16 ++++++++++++++++ 1 file changed, 16 insertions(+) (limited to 'phpBB/includes') diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php index 2f73858ea2..3e69a997a2 100644 --- a/phpBB/includes/functions_admin.php +++ b/phpBB/includes/functions_admin.php @@ -3057,8 +3057,24 @@ function get_database_size() case 'mssql': case 'mssql_odbc': case 'mssqlnative': + $sql = 'SELECT @@VERSION AS mssql_version'; + $result = $db->sql_query($sql); + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + $sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize FROM sysfiles'; + + if ($row) + { + // Azure stats are stored elsewhere + if (strpos($row['mssql_version'], 'SQL Azure') !== false) + { + $sql = 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize + FROM sys.dm_db_partition_stats'; + } + } + $result = $db->sql_query($sql, 7200); $database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false; $db->sql_freeresult($result); -- cgit v1.2.1 From 68ae8dfa97f81cd7febff92b38a5f6296d50c43f Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sat, 1 Mar 2014 22:29:08 -0600 Subject: [ticket/9725] Remove explicit filegroup designations PHPBB3-9725 --- phpBB/includes/db/db_tools.php | 21 ++++----------------- 1 file changed, 4 insertions(+), 17 deletions(-) (limited to 'phpBB/includes') diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php index e394b1ffa0..0518b7eb29 100644 --- a/phpBB/includes/db/db_tools.php +++ b/phpBB/includes/db/db_tools.php @@ -452,9 +452,6 @@ class phpbb_db_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; @@ -501,12 +498,6 @@ class phpbb_db_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']) { @@ -521,13 +512,9 @@ class phpbb_db_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; } @@ -2038,7 +2025,7 @@ class phpbb_db_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; @@ -2136,7 +2123,7 @@ class phpbb_db_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; } @@ -2189,7 +2176,7 @@ class phpbb_db_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; } -- cgit v1.2.1 From 83be9907013b9463f737f9761d85e3e6b907ad16 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sat, 1 Mar 2014 23:11:19 -0600 Subject: [ticket/9725] Create MSSQL primary keys if none exist PHPBB3-9725 --- phpBB/includes/db/db_tools.php | 9 +++++++++ 1 file changed, 9 insertions(+) (limited to 'phpBB/includes') diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php index 0518b7eb29..3a7ea2f945 100644 --- a/phpBB/includes/db/db_tools.php +++ b/phpBB/includes/db/db_tools.php @@ -523,6 +523,15 @@ class phpbb_db_tools // this means that we can add the one we really wanted instead if (!$primary_key_gen) { + 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'; + } + } + // Write primary key if (isset($table_data['PRIMARY_KEY'])) { -- cgit v1.2.1 From 31e610f0b1a9f22701660e72d63fe2290298d812 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sun, 2 Mar 2014 21:36:50 -0600 Subject: [ticket/9725] Move primary key creation to the correct location PHPBB3-9725 --- phpBB/includes/db/db_tools.php | 18 +++++++++--------- 1 file changed, 9 insertions(+), 9 deletions(-) (limited to 'phpBB/includes') diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php index 3a7ea2f945..8372f90369 100644 --- a/phpBB/includes/db/db_tools.php +++ b/phpBB/includes/db/db_tools.php @@ -468,6 +468,15 @@ class phpbb_db_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) { @@ -523,15 +532,6 @@ class phpbb_db_tools // this means that we can add the one we really wanted instead if (!$primary_key_gen) { - 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'; - } - } - // Write primary key if (isset($table_data['PRIMARY_KEY'])) { -- cgit v1.2.1 From aaa846cb3a008a76ad2e2d6f457de5995cd3a9db Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Mon, 3 Mar 2014 00:37:22 -0600 Subject: [ticket/9725] Do not use deprecated views to remove default constraints PHPBB3-9725 --- phpBB/includes/db/db_tools.php | 116 +++++++++++++++++++++++++++++------------ 1 file changed, 84 insertions(+), 32 deletions(-) (limited to 'phpBB/includes') 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; -- cgit v1.2.1 From 79492d41109eba2932c020bb5c9a90f19aac2321 Mon Sep 17 00:00:00 2001 From: Patrick Webster Date: Sun, 23 Mar 2014 22:17:52 -0500 Subject: [ticket/9725] Code sniffer fixes PHPBB3-9725 --- phpBB/includes/db/db_tools.php | 8 ++++---- 1 file changed, 4 insertions(+), 4 deletions(-) (limited to 'phpBB/includes') diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php index 5645d04867..8dce769395 100644 --- a/phpBB/includes/db/db_tools.php +++ b/phpBB/includes/db/db_tools.php @@ -472,7 +472,7 @@ class phpbb_db_tools { if (!isset($table_data['PRIMARY_KEY'])) { - $table_data['COLUMNS']['mssqlindex'] = array('UINT', NULL, 'auto_increment'); + $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment'); $table_data['PRIMARY_KEY'] = 'mssqlindex'; } } @@ -1819,7 +1819,7 @@ class phpbb_db_tools $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 { @@ -1851,7 +1851,7 @@ class phpbb_db_tools $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'] . ']'; @@ -2348,7 +2348,7 @@ class phpbb_db_tools $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 if ($row['mssql_version'][0] == '8') // SQL Server 2000 { -- cgit v1.2.1