From 9c24dbbac71ad16f0d7c27856e52aa7e22978798 Mon Sep 17 00:00:00 2001 From: Robin Appelman Date: Tue, 9 Sep 2014 13:57:02 +0200 Subject: [PATCH] Introduce cross-db ILIKE adding ILIKE to AdapterSQLSrv add test case for ILIKE with wildcard Make sqlite LIKE case sensitive on default Implement ILIKE for sqlite Use ILIKE in cache search Fix ILIKE without wildcards for oracle --- lib/private/db/adaptermysql.php | 17 +++++++++ lib/private/db/adapteroci8.php | 12 +++--- lib/private/db/adaptersqlite.php | 1 + lib/private/db/adaptersqlsrv.php | 1 + lib/private/db/connectionfactory.php | 5 ++- lib/private/db/sqlitesessioninit.php | 42 +++++++++++++++++++++ lib/private/files/cache/cache.php | 14 +------ tests/lib/db.php | 55 ++++++++++++++++++++++++++++ 8 files changed, 128 insertions(+), 19 deletions(-) create mode 100644 lib/private/db/adaptermysql.php create mode 100644 lib/private/db/sqlitesessioninit.php diff --git a/lib/private/db/adaptermysql.php b/lib/private/db/adaptermysql.php new file mode 100644 index 00000000000..0b6e6a5e969 --- /dev/null +++ b/lib/private/db/adaptermysql.php @@ -0,0 +1,17 @@ + + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + + +namespace OC\DB; + +class AdapterMySQL extends Adapter { + public function fixupStatement($statement) { + $statement = str_replace(' ILIKE ', ' COLLATE utf8_general_ci LIKE ', $statement); + return $statement; + } +} diff --git a/lib/private/db/adapteroci8.php b/lib/private/db/adapteroci8.php index bc226e979ec..db7e66e7913 100644 --- a/lib/private/db/adapteroci8.php +++ b/lib/private/db/adapteroci8.php @@ -11,18 +11,20 @@ namespace OC\DB; class AdapterOCI8 extends Adapter { public function lastInsertId($table) { - if($table !== null) { + if ($table !== null) { $suffix = '_SEQ'; - $table = '"'.$table.$suffix.'"'; + $table = '"' . $table . $suffix . '"'; } return $this->conn->realLastInsertId($table); } const UNIX_TIMESTAMP_REPLACEMENT = "(cast(sys_extract_utc(systimestamp) as date) - date'1970-01-01') * 86400"; + public function fixupStatement($statement) { - $statement = str_replace( '`', '"', $statement ); - $statement = str_ireplace( 'NOW()', 'CURRENT_TIMESTAMP', $statement ); - $statement = str_ireplace( 'UNIX_TIMESTAMP()', self::UNIX_TIMESTAMP_REPLACEMENT, $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); return $statement; } } diff --git a/lib/private/db/adaptersqlite.php b/lib/private/db/adaptersqlite.php index fa6d308ae32..f73f364df54 100644 --- a/lib/private/db/adaptersqlite.php +++ b/lib/private/db/adaptersqlite.php @@ -11,6 +11,7 @@ namespace OC\DB; class AdapterSqlite extends Adapter { public function fixupStatement($statement) { + $statement = preg_replace('/`(\w+)` ILIKE \?/', 'LOWER($1) LIKE LOWER(?)', $statement); $statement = str_replace( '`', '"', $statement ); $statement = str_ireplace( 'NOW()', 'datetime(\'now\')', $statement ); $statement = str_ireplace( 'UNIX_TIMESTAMP()', 'strftime(\'%s\',\'now\')', $statement ); 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/lib/private/db/connectionfactory.php b/lib/private/db/connectionfactory.php index 033065bcb77..0a3c1118839 100644 --- a/lib/private/db/connectionfactory.php +++ b/lib/private/db/connectionfactory.php @@ -26,7 +26,7 @@ class ConnectionFactory { 'wrapperClass' => 'OC\DB\Connection', ), 'mysql' => array( - 'adapter' => '\OC\DB\Adapter', + 'adapter' => '\OC\DB\AdapterMySQL', 'charset' => 'UTF8', 'driver' => 'pdo_mysql', 'wrapperClass' => 'OC\DB\Connection', @@ -89,6 +89,9 @@ class ConnectionFactory { case 'oci': $eventManager->addEventSubscriber(new \Doctrine\DBAL\Event\Listeners\OracleSessionInit); break; + case 'sqlite3': + $eventManager->addEventSubscriber(new SQLiteSessionInit); + break; } $connection = \Doctrine\DBAL\DriverManager::getConnection( array_merge($this->getDefaultConnectionParams($type), $additionalConnectionParams), diff --git a/lib/private/db/sqlitesessioninit.php b/lib/private/db/sqlitesessioninit.php new file mode 100644 index 00000000000..7e1166be95b --- /dev/null +++ b/lib/private/db/sqlitesessioninit.php @@ -0,0 +1,42 @@ + + * This file is licensed under the Affero General Public License version 3 or + * later. + * See the COPYING-README file. + */ + +namespace OC\DB; + +use Doctrine\DBAL\Event\ConnectionEventArgs; +use Doctrine\DBAL\Events; +use Doctrine\Common\EventSubscriber; + +class SQLiteSessionInit implements EventSubscriber { + /** + * @var bool + */ + private $caseSensitiveLike; + + /** + * Configure case sensitive like for each connection + * + * @param bool $caseSensitiveLike + */ + public function __construct($caseSensitiveLike = true) { + $this->caseSensitiveLike = $caseSensitiveLike; + } + + /** + * @param ConnectionEventArgs $args + * @return void + */ + public function postConnect(ConnectionEventArgs $args) { + $sensitive = ($this->caseSensitiveLike) ? 'true' : 'false'; + $args->getConnection()->executeUpdate('PRAGMA case_sensitive_like = ' . $sensitive); + } + + public function getSubscribedEvents() { + return array(Events::postConnect); + } +} diff --git a/lib/private/files/cache/cache.php b/lib/private/files/cache/cache.php index cfa3e916185..7ea00325a10 100644 --- a/lib/private/files/cache/cache.php +++ b/lib/private/files/cache/cache.php @@ -464,19 +464,7 @@ class Cache { `mimetype`, `mimepart`, `size`, `mtime`, `encrypted`, `unencrypted_size`, `etag`, `permissions` FROM `*PREFIX*filecache` - WHERE `storage` = ? AND '; - $dbtype = \OC_Config::getValue( 'dbtype', 'sqlite' ); - if($dbtype === 'oci') { - //remove starting and ending % from the pattern - $pattern = '^'.str_replace('%', '.*', $pattern).'$'; - $sql .= 'REGEXP_LIKE(`name`, ?, \'i\')'; - } else if($dbtype === 'pgsql') { - $sql .= '`name` ILIKE ?'; - } else if ($dbtype === 'mysql') { - $sql .= '`name` COLLATE utf8_general_ci LIKE ?'; - } else { - $sql .= '`name` LIKE ?'; - } + WHERE `storage` = ? AND `name` ILIKE ?'; $result = \OC_DB::executeAudited($sql, array($this->getNumericStorageId(), $pattern) ); diff --git a/tests/lib/db.php b/tests/lib/db.php index 4b1a474c4ef..2e236295ff3 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'; @@ -241,4 +246,54 @@ 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()); + + $query = OC_DB::prepare("SELECT * FROM `$table` WHERE `fullname` ILIKE ?"); + $result = $query->execute(array('foo')); + $this->assertCount(0, $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()); + } } -- 2.39.5