This issue is part of #2454513: [meta] Make Drupal 8 work with SQLite.

We just added a case-insensitive collation to SQLite in #2454733: Add a user-space case-insensitive collation to the SQLite driver, but apparently it doesn't take the column collation into account for SUBSTRING() expressions, so we need to add it manually.

CommentFileSizeAuthor
#1 2462175.patch1.27 KBamateescu

Comments

amateescu’s picture

amateescu commented
Status: Active » Needs review
StatusFileSize
new 2462175.patch1.27 KB

It took a bit of debugging since I was convinced that this test should pass after #2454733: Add a user-space case-insensitive collation to the SQLite driver, but it's a simple fix after all.

Test run before:

Test summary
------------

Drupal\views\Tests\GlossaryTest           31 passes   3 fails        9 messages

Test run duration: 14 sec

And after:

Test summary
------------

Drupal\views\Tests\GlossaryTest           34 passes                  3 messages

Test run duration: 14 sec
dawehner’s picture

dawehner
Primary language German
commented 
+++ b/core/modules/views/src/Plugin/views/argument/StringArgument.php
@@ -176,7 +177,15 @@ protected function summaryQuery() {
+    // @see Drupal\Core\Database\Driver\sqlite\Connection::open()
+    if (Database::getConnection()->databaseType() == 'sqlite' && $this->options['case'] != 'none') {
+      $formula .= ' COLLATE NOCASE_UTF8';
+    }

Its a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?

dawehner’s picture

dawehner
Primary language German
commented 
+++ b/core/modules/views/src/Plugin/views/argument/StringArgument.php
@@ -176,7 +177,15 @@ protected function summaryQuery() {
+    // @see Drupal\Core\Database\Driver\sqlite\Connection::open()
+    if (Database::getConnection()->databaseType() == 'sqlite' && $this->options['case'] != 'none') {
+      $formula .= ' COLLATE NOCASE_UTF8';
+    }

Its a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?

dawehner’s picture

dawehner
Primary language German
commented 
+++ b/core/modules/views/src/Plugin/views/argument/StringArgument.php
@@ -176,7 +177,15 @@ protected function summaryQuery() {
+    // @see Drupal\Core\Database\Driver\sqlite\Connection::open()
+    if (Database::getConnection()->databaseType() == 'sqlite' && $this->options['case'] != 'none') {
+      $formula .= ' COLLATE NOCASE_UTF8';
+    }

Its a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?

amateescu’s picture

amateescu commented

We are doing something at the database level, see #2454733: Add a user-space case-insensitive collation to the SQLite driver. This is the structure of {node_field_data}:

CREATE TABLE node_field_data (
  nid INTEGER NOT NULL CHECK (nid>= 0),
  vid INTEGER NOT NULL CHECK (vid>= 0),
  type VARCHAR(32) NOT NULL,
  langcode VARCHAR(12) NOT NULL,
  title VARCHAR(255) COLLATE NOCASE_UTF8 NOT NULL,
  uid INTEGER NULL CHECK (uid>= 0) DEFAULT NULL,
  status INTEGER NOT NULL,
  created INTEGER NOT NULL,
  changed INTEGER NOT NULL,
  promote INTEGER NOT NULL,
  sticky INTEGER NOT NULL,
  default_langcode INTEGER NOT NULL,
  PRIMARY KEY (nid, langcode)
)

so SQLite uses the collation for simple WHERE expression, otherwise the test mentioned in the issue above would not pass.

The problem here is that it doesn't use the collation specified for the 'title' column even for an equally simple SUBSTRING() on 'node_field_data.title' (or SQLite's own SUBSTR() for that matter).

What we could do on the database level is a preg_match() on SUBSTRING($field , ... ) and friends, take the field name, check the information schema to see if it's supposed to be a case-sensitive column and automatically append the ' COLLATE NOCASE_UTF8' string if so. But given that we'd have to do that on every single query, I think the alternative of doing it manually by the caller like the patch is doing is much more preferable.

dawehner’s picture

dawehner
Primary language German
commented
Status: Needs review » Reviewed & tested by the community

What we could do on the database level is a preg_match() on SUBSTRING($field , ... ) and friends, take the field name, check the information schema to see if it's supposed to be a case-sensitive column and automatically append the ' COLLATE NOCASE_UTF8' string if so. But given that we'd have to do that on every single query, I think the alternative of doing it manually by the caller like the patch is doing is much more preferable.

Mh, agreed!

alexpott’s picture

alexpott
he/they
Primary language English
Location 🇪🇺🌍
commented
Status: Reviewed & tested by the community » Fixed

This issue is a normal bug fix, and doesn't include any disruptive changes, so it is allowed per https://www.drupal.org/core/beta-changes. Committed c127131 and pushed to 8.0.x. Thanks!

  • alexpott committed c127131 on 8.0.x 
    Issue #2462175 by amateescu: SQLite: Fix case sensitivity in Views'...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

Anonymous’s picture

Anonymous (not verified) commented

I've realised that NOCASE_UTF8 is invalid in sqlite3, therefore drupal8. In the result you can't vacuum, dump & recover the tables containing NOCASE_UTF8 in their schema. Simple sed on db replacing `NOCASE_UTF8` with `NOCASE ` does the job. Could you maybe remove those statements from DB schema in the future?
More info here: https://bioinfoexpert.com/2019/02/28/investigate-reduce-the-size-of-drup...