Merge pull request #10958 from owncloud/db-ilike

Introduce cross-db ILIKE

7 files changed, 73 insertions, 19 deletions

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 @@
+<?php
+/**
+ * Copyright (c) 2014 Robin Appelman <icewind@owncloud.com>
+ * 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 5b9c5a437da..3471fcf4042 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 dbbe58dbef8..589a1c0affd 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 @@
+<?php
+/**
+ * Copyright (c) 2014 Robin Appelman <icewind@owncloud.com>
+ * 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)
 		);