make it possible to override the default collation

- allows admins to configure it for edge cases like accent sensitivity

Signed-off-by: Arthur Schiwon <blizzz@arthur-schiwon.de>

4 files changed, 31 insertions, 9 deletions

diff --git a/config/config.sample.php b/config/config.sample.php
index 5f3f33dfa41..bcb8dfd0aa9 100644
--- a/config/config.sample.php
+++ b/config/config.sample.php
@@ -1554,6 +1554,24 @@ $CONFIG = [
 'mysql.utf8mb4' => false,
 
 /**
+ * For search queries in the database, a default collation – depending on the
+ * character set – is chosen. In some cases a different behaviour is desired,
+ * for instances when a accent sensitive search is desired.
+ *
+ * MariaDB and MySQL have an overlap in available collations, but also
+ * incompatible ones, also depending on the version of the database server.
+ *
+ * This option allows to override the automatic choice. Example:
+ *
+ * 'mysql.collation' => 'utf8mb4_0900_as_ci',
+ *
+ * This setting has no effect on setup or creating tables. In those cases
+ * always utf8[mb4]_bin is being used. This setting is only taken into
+ * consideration in SQL queries that utilize LIKE comparison operators.
+ */
+'mysql.collation' => null,
+
+/**
  * Database types that are supported for installation.
  *
  * Available:
diff --git a/lib/private/DB/AdapterMySQL.php b/lib/private/DB/AdapterMySQL.php
index 43da88b4b74..b4be5c2e96a 100644
--- a/lib/private/DB/AdapterMySQL.php
+++ b/lib/private/DB/AdapterMySQL.php
@@ -25,7 +25,7 @@ namespace OC\DB;
 class AdapterMySQL extends Adapter {
 
 	/** @var string */
-	protected $charset;
+	protected $collation;
 
 	/**
 	 * @param string $tableName
@@ -39,16 +39,16 @@ class AdapterMySQL extends Adapter {
 	}
 
 	public function fixupStatement($statement) {
-		$statement = str_replace(' ILIKE ', ' COLLATE ' . $this->getCharset() . '_general_ci LIKE ', $statement);
+		$statement = str_replace(' ILIKE ', ' COLLATE ' . $this->getCollation() . ' LIKE ', $statement);
 		return $statement;
 	}
 
-	protected function getCharset() {
-		if (!$this->charset) {
+	protected function getCollation(): string {
+		if (!$this->collation) {
 			$params = $this->conn->getParams();
-			$this->charset = isset($params['charset']) ? $params['charset'] : 'utf8';
+			$this->collation = $params['collation'] ?? (($params['charset'] ?? 'utf8') . '_general_ci');
 		}
 
-		return $this->charset;
+		return $this->collation;
 	}
 }
diff --git a/lib/private/DB/ConnectionFactory.php b/lib/private/DB/ConnectionFactory.php
index 53e488b5f09..b4c7597f6d4 100644
--- a/lib/private/DB/ConnectionFactory.php
+++ b/lib/private/DB/ConnectionFactory.php
@@ -89,6 +89,10 @@ class ConnectionFactory {
 		if ($this->config->getValue('mysql.utf8mb4', false)) {
 			$this->defaultConnectionParams['mysql']['charset'] = 'utf8mb4';
 		}
+		$collationOverride = $this->config->getValue('mysql.collation', null);
+		if ($collationOverride) {
+			$this->defaultConnectionParams['mysql']['collation'] = $collationOverride;
+		}
 	}
 
 	/**
diff --git a/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
index 3a0f45bcde7..e917ad3ad3a 100644
--- a/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
+++ b/lib/private/DB/QueryBuilder/ExpressionBuilder/MySqlExpressionBuilder.php
@@ -31,7 +31,7 @@ use OCP\DB\QueryBuilder\IQueryBuilder;
 class MySqlExpressionBuilder extends ExpressionBuilder {
 
 	/** @var string */
-	protected $charset;
+	protected $collation;
 
 	/**
 	 * @param ConnectionAdapter $connection
@@ -41,7 +41,7 @@ class MySqlExpressionBuilder extends ExpressionBuilder {
 		parent::__construct($connection, $queryBuilder);
 
 		$params = $connection->getInner()->getParams();
-		$this->charset = isset($params['charset']) ? $params['charset'] : 'utf8';
+		$this->collation = $params['collation'] ?? (($params['charset'] ?? 'utf8') . '_general_ci');
 	}
 
 	/**
@@ -50,6 +50,6 @@ class MySqlExpressionBuilder extends ExpressionBuilder {
 	public function iLike($x, $y, $type = null): string {
 		$x = $this->helper->quoteColumnName($x);
 		$y = $this->helper->quoteColumnName($y);
-		return $this->expressionBuilder->comparison($x, ' COLLATE ' . $this->charset . '_general_ci LIKE', $y);
+		return $this->expressionBuilder->comparison($x, ' COLLATE ' . $this->collation . ' LIKE', $y);
 	}
 }