From 5bb62f5560ee913efe56c1fcb8c1a855204cc658 Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Sun, 29 Jan 2017 15:56:21 +0100 Subject: [ticket/15055] Use unicode column types where necessary PHPBB3-15055 --- phpBB/phpbb/db/tools/mssql.php | 28 ++++++++++++++-------------- 1 file changed, 14 insertions(+), 14 deletions(-) (limited to 'phpBB/phpbb/db/tools/mssql.php') diff --git a/phpBB/phpbb/db/tools/mssql.php b/phpBB/phpbb/db/tools/mssql.php index 23b49aab44..6f800f730d 100644 --- a/phpBB/phpbb/db/tools/mssql.php +++ b/phpBB/phpbb/db/tools/mssql.php @@ -49,18 +49,18 @@ class mssql extends tools 'STEXT' => '[varchar] (3000)', 'TEXT' => '[varchar] (8000)', 'MTEXT' => '[text]', - 'XSTEXT_UNI'=> '[varchar] (100)', - 'STEXT_UNI' => '[varchar] (255)', - 'TEXT_UNI' => '[varchar] (4000)', - 'MTEXT_UNI' => '[text]', + 'XSTEXT_UNI'=> '[nvarchar] (100)', + 'STEXT_UNI' => '[nvarchar] (255)', + 'TEXT_UNI' => '[nvarchar] (4000)', + 'MTEXT_UNI' => '[ntext]', 'TIMESTAMP' => '[int]', 'DECIMAL' => '[float]', 'DECIMAL:' => '[float]', 'PDECIMAL' => '[float]', 'PDECIMAL:' => '[float]', - 'VCHAR_UNI' => '[varchar] (255)', - 'VCHAR_UNI:'=> '[varchar] (%d)', - 'VCHAR_CI' => '[varchar] (255)', + 'VCHAR_UNI' => '[nvarchar] (255)', + 'VCHAR_UNI:'=> '[nvarchar] (%d)', + 'VCHAR_CI' => '[nvarchar] (255)', 'VARBINARY' => '[varchar] (255)', ), @@ -80,18 +80,18 @@ class mssql extends tools 'STEXT' => '[varchar] (3000)', 'TEXT' => '[varchar] (8000)', 'MTEXT' => '[text]', - 'XSTEXT_UNI'=> '[varchar] (100)', - 'STEXT_UNI' => '[varchar] (255)', - 'TEXT_UNI' => '[varchar] (4000)', - 'MTEXT_UNI' => '[text]', + 'XSTEXT_UNI'=> '[nvarchar] (100)', + 'STEXT_UNI' => '[nvarchar] (255)', + 'TEXT_UNI' => '[nvarchar] (4000)', + 'MTEXT_UNI' => '[ntext]', 'TIMESTAMP' => '[int]', 'DECIMAL' => '[float]', 'DECIMAL:' => '[float]', 'PDECIMAL' => '[float]', 'PDECIMAL:' => '[float]', - 'VCHAR_UNI' => '[varchar] (255)', - 'VCHAR_UNI:'=> '[varchar] (%d)', - 'VCHAR_CI' => '[varchar] (255)', + 'VCHAR_UNI' => '[nvarchar] (255)', + 'VCHAR_UNI:'=> '[nvarchar] (%d)', + 'VCHAR_CI' => '[nvarchar] (255)', 'VARBINARY' => '[varchar] (255)', ), ); -- cgit v1.2.1 From 635befa00e0d9791137a2a500260b578021f60b8 Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Sun, 29 Jan 2017 15:56:45 +0100 Subject: [ticket/15055] Drop primary keys when necessary and fix test comparisons PHPBB3-15055 --- phpBB/phpbb/db/tools/mssql.php | 35 +++++++++++++++++++++++++++++++++++ 1 file changed, 35 insertions(+) (limited to 'phpBB/phpbb/db/tools/mssql.php') diff --git a/phpBB/phpbb/db/tools/mssql.php b/phpBB/phpbb/db/tools/mssql.php index 6f800f730d..0dfb09b1ba 100644 --- a/phpBB/phpbb/db/tools/mssql.php +++ b/phpBB/phpbb/db/tools/mssql.php @@ -448,6 +448,10 @@ class mssql extends tools } } + // Drop primary keys depending on this column + $result = $this->mssql_get_drop_default_primary_key_queries($table_name, $column_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); @@ -684,6 +688,37 @@ class mssql extends tools return $statements; } + /** + * Get queries to drop the primary keys depending on the specified column + * + * We need to drop primary keys depending on this column before being able + * to delete them. + * + * @param string $table_name + * @param string $column_name + * @return array Array with SQL statements + */ + protected function mssql_get_drop_default_primary_key_queries($table_name, $column_name) + { + $statements = array(); + + $sql = "SELECT ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME + FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc + JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name + WHERE tc.TABLE_NAME = '{$table_name}' + AND tc.CONSTRAINT_TYPE = 'Primary Key'"; + + $result = $this->db->sql_query($sql); + + while ($primary_key = $this->db->sql_fetchrow($result)) + { + $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $primary_key['CONSTRAINT_NAME'] . ']'; + } + $this->db->sql_freeresult($result); + + return $statements; + } + /** * Checks to see if column is an identity column * -- cgit v1.2.1 From 400fc0f73d03010d3bf28d2b1db5d789dc085334 Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Mon, 25 Dec 2017 18:49:31 +0100 Subject: [ticket/15055] Only drop dependent PK indexes and fix more tests for mssql PHPBB3-15055 --- phpBB/phpbb/db/tools/mssql.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/tools/mssql.php') diff --git a/phpBB/phpbb/db/tools/mssql.php b/phpBB/phpbb/db/tools/mssql.php index 0dfb09b1ba..1e4d3aee2f 100644 --- a/phpBB/phpbb/db/tools/mssql.php +++ b/phpBB/phpbb/db/tools/mssql.php @@ -706,7 +706,8 @@ class mssql extends tools FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name WHERE tc.TABLE_NAME = '{$table_name}' - AND tc.CONSTRAINT_TYPE = 'Primary Key'"; + AND tc.CONSTRAINT_TYPE = 'Primary Key' + AND ccu.COLUMN_NAME = '{$column_name}'"; $result = $this->db->sql_query($sql); -- cgit v1.2.1 From ee8b72d733a3e096f35ec65a7eaf3c63a237cb4b Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Sun, 31 Dec 2017 14:00:36 +0100 Subject: [ticket/15055] Properly support index length check on mssql PHPBB3-15055 --- phpBB/phpbb/db/tools/mssql.php | 20 ++++++++++++++++---- 1 file changed, 16 insertions(+), 4 deletions(-) (limited to 'phpBB/phpbb/db/tools/mssql.php') diff --git a/phpBB/phpbb/db/tools/mssql.php b/phpBB/phpbb/db/tools/mssql.php index 1e4d3aee2f..b84c0db403 100644 --- a/phpBB/phpbb/db/tools/mssql.php +++ b/phpBB/phpbb/db/tools/mssql.php @@ -545,10 +545,7 @@ class mssql extends tools { $statements = array(); - if ($this->mssql_is_sql_server_2000()) - { - $this->check_index_name_length($table_name, $index_name); - } + $this->check_index_name_length($table_name, $index_name); // remove index length $column = preg_replace('#:.*$#', '', $column); @@ -558,6 +555,21 @@ class mssql extends tools return $this->_sql_run_sql($statements); } + /** + * {@inheritdoc} + */ + protected function get_max_index_name_length() + { + if ($this->mssql_is_sql_server_2000()) + { + return parent::get_max_index_name_length(); + } + else + { + return 128; + } + } + /** * {@inheritDoc} */ -- cgit v1.2.1