From 1771bfc2f2c790bc5d7a439095290cb4c97edf1a Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Tue, 9 Sep 2014 13:57:02 +0200 Subject: Introduce cross-db ILIKE --- tests/lib/db.php | 15 +++++++++++++++ 1 file changed, 15 insertions(+) (limited to 'tests') diff --git a/tests/lib/db.php b/tests/lib/db.php index 1f62413cbe4..893d42cdbe9 100644 --- a/tests/lib/db.php +++ b/tests/lib/db.php @@ -263,4 +263,19 @@ class Test_DB extends PHPUnit_Framework_TestCase { $query = OC_DB::prepare("UPDATE `*PREFIX*{$this->table2}` SET `uri` = ? WHERE `fullname` = ?"); return $query->execute(array($uri, $fullname)); } + + public function testILIKE() { + $table = "*PREFIX*{$this->table2}"; + + $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $query->execute(array('fooBAR', 'foo', 'bar')); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('foobar')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foobar')); + $this->assertCount(1, $result->fetchAll()); + } } -- cgit v1.2.3 From ca35d86c5a85485a418916ebf236b575d4401136 Mon Sep 17 00:00:00 2001 From: Thomas Müller Date: Tue, 16 Sep 2014 15:44:21 +0200 Subject: adding ILIKE to AdapterSQLSrv --- lib/private/db/adaptersqlsrv.php | 1 + tests/lib/db.php | 5 +++++ 2 files changed, 6 insertions(+) (limited to 'tests') diff --git a/lib/private/db/adaptersqlsrv.php b/lib/private/db/adaptersqlsrv.php index a6bc0e21052..1ac9badab94 100644 --- a/lib/private/db/adaptersqlsrv.php +++ b/lib/private/db/adaptersqlsrv.php @@ -11,6 +11,7 @@ namespace OC\DB; class AdapterSQLSrv extends Adapter { public function fixupStatement($statement) { + $statement = str_replace(' ILIKE ', ' COLLATE Latin1_General_CI_AS LIKE ', $statement); $statement = preg_replace( "/\`(.*?)`/", "[$1]", $statement ); $statement = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $statement ); $statement = str_replace( 'LENGTH(', 'LEN(', $statement ); diff --git a/tests/lib/db.php b/tests/lib/db.php index 893d42cdbe9..22792930f0a 100644 --- a/tests/lib/db.php +++ b/tests/lib/db.php @@ -27,6 +27,11 @@ class Test_DB extends PHPUnit_Framework_TestCase { */ private $table3; + /** + * @var string + */ + private $table4; + public function setUp() { $dbfile = OC::$SERVERROOT.'/tests/data/db_structure.xml'; -- cgit v1.2.3 From 95815c0b57c0d255a1ad5dd71d8f925fc5cc4588 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Tue, 16 Sep 2014 21:03:03 +0200 Subject: add test case for ILIKE with wildcard --- tests/lib/db.php | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'tests') diff --git a/tests/lib/db.php b/tests/lib/db.php index 22792930f0a..d4270737d86 100644 --- a/tests/lib/db.php +++ b/tests/lib/db.php @@ -283,4 +283,35 @@ class Test_DB extends PHPUnit_Framework_TestCase { $result = $query->execute(array('foobar')); $this->assertCount(1, $result->fetchAll()); } + + public function testILIKEWildcard() { + $table = "*PREFIX*{$this->table2}"; + + $query = OC_DB::prepare("INSERT INTO `$table` (`fullname`, `uri`, `carddata`) VALUES (?, ?, ?)"); + $query->execute(array('FooBAR', 'foo', 'bar')); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('%bar')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('foo%')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` LIKE ?"); + $result = $query->execute(array('%ba%')); + $this->assertCount(0, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('%bar')); + $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foo%')); + $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('%ba%')); + $this->assertCount(1, $result->fetchAll()); + } } -- cgit v1.2.3 From a85f0ae2da4344826ee04f79bc362eb5d1a86f67 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Thu, 18 Sep 2014 15:09:57 +0200 Subject: Fix ILIKE without wildcards for oracle --- lib/private/db/adapteroci8.php | 2 +- tests/lib/db.php | 4 ++++ 2 files changed, 5 insertions(+), 1 deletion(-) (limited to 'tests') diff --git a/lib/private/db/adapteroci8.php b/lib/private/db/adapteroci8.php index fb4ea1bcde9..db7e66e7913 100644 --- a/lib/private/db/adapteroci8.php +++ b/lib/private/db/adapteroci8.php @@ -21,7 +21,7 @@ class AdapterOCI8 extends Adapter { const UNIX_TIMESTAMP_REPLACEMENT = "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400"; public function fixupStatement($statement) { - $statement = preg_replace('/`(\w+)` ILIKE \?/', 'REGEXP_LIKE(`$1`, TRIM(BOTH \'%\' FROM ?), \'i\')', $statement); + $statement = preg_replace('/`(\w+)` ILIKE \?/', 'REGEXP_LIKE(`$1`, \'^\' || REPLACE(?, \'%\', \'.*\') || \'$\', \'i\')', $statement); $statement = str_replace('`', '"', $statement); $statement = str_ireplace('NOW()', 'CURRENT_TIMESTAMP', $statement); $statement = str_ireplace('UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $statement); diff --git a/tests/lib/db.php b/tests/lib/db.php index d4270737d86..fb673b8092b 100644 --- a/tests/lib/db.php +++ b/tests/lib/db.php @@ -282,6 +282,10 @@ class Test_DB extends PHPUnit_Framework_TestCase { $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); $result = $query->execute(array('foobar')); $this->assertCount(1, $result->fetchAll()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foo')); + $this->assertCount(0, $result->fetchAll()); } public function testILIKEWildcard() { -- cgit v1.2.3