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') 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') 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') 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 6f6750b6294232e6cd11eb38d0cb8492d1e1245a Mon Sep 17 00:00:00 2001 From: Marc Alexander Date: Wed, 27 Dec 2017 09:04:15 +0100 Subject: [ticket/15055] Specify utf8 as character set in sqlsrv_connect This is needed to be able to correctly retrieve unicode data from the db. PHPBB3-15055 --- phpBB/phpbb/db/driver/mssqlnative.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/phpbb/db') diff --git a/phpBB/phpbb/db/driver/mssqlnative.php b/phpBB/phpbb/db/driver/mssqlnative.php index 50dce35baa..28bd3ceb42 100644 --- a/phpBB/phpbb/db/driver/mssqlnative.php +++ b/phpBB/phpbb/db/driver/mssqlnative.php @@ -50,7 +50,8 @@ class mssqlnative extends \phpbb\db\driver\mssql_base $this->db_connect_id = sqlsrv_connect($this->server, array( 'Database' => $this->dbname, 'UID' => $this->user, - 'PWD' => $sqlpassword + 'PWD' => $sqlpassword, + 'CharacterSet' => 'UTF-8' )); return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error(''); -- 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 ++++++++++++++++---- phpBB/phpbb/db/tools/tools.php | 15 +++++++++++++-- 2 files changed, 29 insertions(+), 6 deletions(-) (limited to 'phpBB/phpbb/db') 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} */ diff --git a/phpBB/phpbb/db/tools/tools.php b/phpBB/phpbb/db/tools/tools.php index 2f891e43d5..d21d34b8a9 100644 --- a/phpBB/phpbb/db/tools/tools.php +++ b/phpBB/phpbb/db/tools/tools.php @@ -1561,7 +1561,8 @@ class tools implements tools_interface */ protected function check_index_name_length($table_name, $index_name, $throw_error = true) { - if (strlen($index_name) > 30) + $max_index_name_length = $this->get_max_index_name_length(); + if (strlen($index_name) > $max_index_name_length) { // Try removing the table prefix if it's at the beginning $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config) @@ -1582,13 +1583,23 @@ class tools implements tools_interface if ($throw_error) { - trigger_error("Index name '$index_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR); + trigger_error("Index name '$index_name' on table '$table_name' is too long. The maximum is $max_index_name_length characters.", E_USER_ERROR); } } return $index_name; } + /** + * Get maximum index name length. Might vary depending on db type + * + * @return int Maximum index name length + */ + protected function get_max_index_name_length() + { + return 30; + } + /** * {@inheritDoc} */ -- cgit v1.2.1