From d533e6d7e314d880a1bd818a23a7ad54da8a0bb0 Mon Sep 17 00:00:00 2001 From: Petr Skoda Date: Wed, 25 Aug 2010 20:26:23 +0000 Subject: [PATCH] MDL-23925 hopefully finalised unit tests for case and accent insensitive problems --- lib/dml/simpletest/testdml.php | 67 +++++++++++++++++++--------------- 1 file changed, 37 insertions(+), 30 deletions(-) diff --git a/lib/dml/simpletest/testdml.php b/lib/dml/simpletest/testdml.php index 1b8c77623af..d59c06949fa 100755 --- a/lib/dml/simpletest/testdml.php +++ b/lib/dml/simpletest/testdml.php @@ -2061,7 +2061,7 @@ class dml_test extends UnitTestCase { $this->assertEqual(count($records), 2); } - function test_unique_binary() { + function test_unique_index_collation_trouble() { // note: this is a work in progress, we should probably move this to ddl test $DB = $this->tdb; @@ -2077,15 +2077,27 @@ class dml_test extends UnitTestCase { $dbman->create_table($table); $this->tables[$tablename] = $table; + $DB->insert_record($tablename, array('name'=>'aaa')); + + try { + $DB->insert_record($tablename, array('name'=>'AAA')); + } catch (Exception $e) { + //TODO: ignore case insensitive uniqueness problems for now + //$this->fail("Unique index is case sensitive - this may cause problems in some tables"); + } + try { - $DB->insert_record($tablename, array('name'=>'aaa')); $DB->insert_record($tablename, array('name'=>'aäa')); $DB->insert_record($tablename, array('name'=>'aáa')); - $DB->insert_record($tablename, array('name'=>'AAA')); - $DB->insert_record($tablename, array('name'=>'aÄa')); $this->assertTrue(true); } catch (Exception $e) { - $this->fail("Collation uniqueness problem detected - default collation is expected to be case sensitive and accent sensitive."); + $family = $DB->get_dbfamily(); + if ($family === 'mysql' or $family === 'mssql') { + $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages. This is usually caused by accent insensitive default collation."); + } else { + // this should not happen, PostgreSQL and Oracle do not support accent insensitive uniqueness. + $this->fail("Unique index is accent insensitive, this may cause problems for non-ascii languages."); + } throw($e); } } @@ -2099,26 +2111,21 @@ class dml_test extends UnitTestCase { $table->add_field('id', XMLDB_TYPE_INTEGER, '10', XMLDB_UNSIGNED, XMLDB_NOTNULL, XMLDB_SEQUENCE, null); $table->add_field('name', XMLDB_TYPE_CHAR, '255', null, null, null, null); - $table->add_field('descr', XMLDB_TYPE_CHAR, '255', null, null, null, null); $table->add_key('primary', XMLDB_KEY_PRIMARY, array('id')); $dbman->create_table($table); $this->tables[$tablename] = $table; - $DB->insert_record($tablename, array('name'=>'aaa', 'descr'=>'Aaa')); - $DB->insert_record($tablename, array('name'=>'aáa', 'descr'=>'ááá')); - $DB->insert_record($tablename, array('name'=>'aäa', 'descr'=>'aäa')); - $DB->insert_record($tablename, array('name'=>'AAA', 'descr'=>'AAA')); + $DB->insert_record($tablename, array('name'=>'aaa')); + $DB->insert_record($tablename, array('name'=>'aáa')); + $DB->insert_record($tablename, array('name'=>'aäa')); + $DB->insert_record($tablename, array('name'=>'bbb')); + $DB->insert_record($tablename, array('name'=>'BBB')); - // get_records() is supposed to use binary comparison - $records = $DB->get_records($tablename, array('name'=>"aaa")); - $this->assertEqual(count($records), 1, 'SQL operator = is expected to be case and accent sensitive'); - $records = $DB->get_records($tablename, array('name'=>"aäa")); - $this->assertEqual(count($records), 1, 'SQL operator = is expected to be case and accent sensitive'); + $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('aaa')); + $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be accent sensitive'); - $bool = $DB->record_exists($tablename, array('name'=>"aaa")); - $this->assertTrue($bool, 'SQL operator = is expected to be case and accent sensitive'); - $bool = $DB->record_exists($tablename, array('name'=>"AaA")); - $this->assertFalse($bool, 'SQL operator = is expected to be case and accent sensitive'); + $records = $DB->get_records_sql("SELECT * FROM {{$tablename}} WHERE name = ?", array('bbb')); + $this->assertEqual(count($records), 1, 'SQL operator "=" is expected to be case sensitive'); } function test_sql_like() { @@ -2143,37 +2150,37 @@ class dml_test extends UnitTestCase { $DB->insert_record($tablename, array('name'=>'aüi')); $DB->insert_record($tablename, array('name'=>'aÜi')); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false); $records = $DB->get_records_sql($sql, array("%dup_r%")); $this->assertEqual(count($records), 2); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); $records = $DB->get_records_sql($sql, array("%dup%")); $this->assertEqual(count($records), 1); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?'); // defaults + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?'); // defaults $records = $DB->get_records_sql($sql, array("%dup%")); $this->assertEqual(count($records), 1); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true); $records = $DB->get_records_sql($sql, array("ouc\\_")); $this->assertEqual(count($records), 1); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true, '|'); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true, '|'); $records = $DB->get_records_sql($sql, array($DB->sql_like_escape("ouc%", '|'))); $this->assertEqual(count($records), 1); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, true); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, true); $records = $DB->get_records_sql($sql, array('aui')); $this->assertEqual(count($records), 1); - // we do not require accent insensitivness yet, just make sure it does not throw errors - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', true, false); + // TODO: we do not require accent insensitivness yet, just make sure it does not throw errors + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', true, false); $records = $DB->get_records_sql($sql, array('aui')); - $this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not really a problem now.'); - $sql = "SELECT * FROM {".$tablename."} WHERE ".$DB->sql_like('name', '?', false, false); + //$this->assertEqual(count($records), 2, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); + $sql = "SELECT * FROM {{$tablename}} WHERE ".$DB->sql_like('name', '?', false, false); $records = $DB->get_records_sql($sql, array('aui')); - $this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not really a problem now.'); + //$this->assertEqual(count($records), 3, 'Accent insensitive LIKE searches may not be supported in all databases, this is not a problem.'); } function test_sql_ilike() { -- 2.17.1