Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
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.
Comment | File | Size | Author |
---|---|---|---|
#1 | 2462175.patch | 1.27 KB | amateescu |
Comments
Comment #1
amateescu CreditAttribution: amateescu commentedIt 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:
And after:
Comment #2
dawehnerIts a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?
Comment #3
dawehnerIts a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?
Comment #4
dawehnerIts a bit sad that we have to hardcode the DB driver, is there no way to fix it somehow on the database level?
Comment #5
amateescu CreditAttribution: amateescu commentedWe 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}:
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 ownSUBSTR()
for that matter).What we could do on the database level is a
preg_match()
onSUBSTRING($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.Comment #6
dawehnerMh, agreed!
Comment #7
alexpottThis 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!
Comment #10
lpryszcz CreditAttribution: lpryszcz commentedI'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...