DB Case Sensitivity: Allow BINARY attribute in MySQL

Thanks, this should be covered by database tests though.

Thanks, this looks good to me.

This looks good to me too. Would be nice to get a couple more +1s though.

Updated the docs to make the data type clearer. Side note it should be Boolean not boolean because it's an eponym but I'll save that for a separate issue and do a core wide find and replace.

I'll add that this has my +1, but I'll kick it back to CNR so someone who didn't touch the patch can double check it.

Critical because it is blocking #966210: DB Case Sensitivity: system_update_7061() fails on inserting files with same name but different case, this part of the patch has just been moved here for easier review.

Sorry for bumping this back, but I am very uncomfortable with this. The current situation is a total mess that this patch doesn't even come close to resolving.

Before the patch:

MySQL: case insensitive, accent insensitive collation; affects the = operator and LIKE operator
PostgreSQL: collation only affects sort, ILIKE is used instead of LIKE (which is case insensitive but accent sensitive)
SQLite: the BINARY collation is the default: binary, exact matching for =, exact prefix for LIKE

After the patch:

MySQL:
 - For non-binary columns: case insensitive, accent insensitive collation; affects the = operator and LIKE operator
 - For binary columns: binary, exact matching for =, exact prefix for LIKE
PostgreSQL: for both non-binary and binary columns: collation only affects sort, ILIKE is used instead of LIKE (which is case insensitive but accent sensitive)
SQLite: for both non-binary and binary columns: binary, exact matching for =, exact prefix for LIKE

So it seems to me that we need to extend support for PostgreSQL and SQLite:

• For PostgreSQL: we need to implement the binary column as a real bytea, but how to make the ILIKE operator conditional?
• For SQLite: we should at least use the NOCASE collation for non-binary column, pending implementation of better collations (see my patch in https://bugs.php.net/bug.php?id=55226)

Damien, well how would you propose we deal with #966210: DB Case Sensitivity: system_update_7061() fails on inserting files with same name but different case? Because the upgrade path is still broken.

Sub.

From what I see, the different DBs cannot be exactly the same. I'd suggest to keep the current behaviors and only use "case sensitive like" when needed, i.e. for filenames.

what about adding a 'collate' field in the schema definition ?

[made some edits]

I've spent a few hours researching this. Please correct any mistakes.

We need to have the option to index char and varchar columns with and without case sensitivity.

PostgreSQL may be able to have both indexes for a single column: http://archives.postgresql.org/pgsql-sql/2003-09/msg00395.php , but MySQL cannot: http://lists.mysql.com/mysql/205551 as it is specified in the collation of the column.

So we need an option in the schema for whether or not a column should be case sensitive.

For case sensitive columns:
MySQL - use utf8_bin collation / BINARY keyword
PostgreSQL - default
SQLite - default

For case insensitive columns:
MySQL - use utf8_general_ci

PostgreSQL:

• convert indexes to index LOWER()
• convert = to "LOWER() = LOWER()"
• convert LIKE to "LOWER() LIKE LOWER()" - (ILIKE can't use an index - another link: http://archives.postgresql.org/pgsql-novice/2010-06/msg00136.php)

SQLite - use NOCASE collation

Update to my previous post:

I don't think the database layer has access to the schema information, so for case insensitive columns ILIKE would have to be specified in condition() calls. This would then be converted to LIKE for mysql and sqlite. For postresql it would be converted to "LOWER() LIKE LOWER()" (to use the index).

Bump. This critical still has no patch and solution.

#19 db-backend has ability to retrieve info from information_schema see schema.inc

I think we can extend LIKE with ILIKE or similar operator

#14 points to postgresql and sqlite - pgsql conversion to bytea could be doen in follow-up but sqlite still depends on unresolved https://bugs.php.net/bug.php?id=55226

There's no controversy over the patch in #9 being the correct fix for the critical MySQL upgrade issue, correct? Given that inconsistent collation behavior among the database engines is a pre-existing condition that is not made any worse by this patch, why don't we open a separate major issue for normalizing behavior across the other engines, and commit this one?

Tagging.

Tagging since the blocks #966210: DB Case Sensitivity: system_update_7061() fails on inserting files with same name but different case.

Agree with mikeryan in #21.

I've broken this out into a new issue #1518506: Normalize how case sensitivity is handled across database engines.

In the meantime, can we take another look at #9?

#9: db_1237252.patch queued for re-testing.

Agreed, triggered a request of the patch in #9, I don't think he will apply anymore :)

Here's a re-roll for the patch from #9.

Works for me and there was pretty widespread consensus previously. RTBC.

Since this is now strictly concerning mysql, it would be a +1 from me.

This doesn't appear to address #14 and #18, both of which are valid concerns. This feels more like a MySQL hack than a proper solution to still more database fugliness.

Update: Talked with catch a bit more in IRC. I totally missed the link to #1518506: Normalize how case sensitivity is handled across database engines above, probably because it was not colored. :-) Given that the follow-up is critical, I can deal with this as a temporary solution to un-bork the update process.

I agree with getting the easier fix in to unblock the upgrade path fixes, those two {files} upgrade issues are very painful.

Committed/pushed to 8.x, marking for backport to 7.x.

Same against 7.x.

Reroll.

Drupal.org is hitting this, according to #1481026-11: Get servers in place for Drupal 7 staging/dev environments.

I'm using #35 in an upgrade process, myself. Marking RTBC, but could hear from others, possibly.

hrmph I didn't get commit credit after all :p Looks RTBC to me.

Great, thanks a lot, all. mfb, I gave you credit in D7 anyway. :)

Committed and pushed to 7.x. Thanks!

x