Locale DB schema case insensitive (blob) only on MySQL not on other databases

Problem/Motivation

#2503755: Switch from user login block to login menu link and search block in standard profile got committed and then needed to be reverted because there was a single test failure in both SQLite and Postgres, that was only discovered after commit (because we don't test every patch against those two DBs, but we do test HEAD against them).

After a long search, the root cause was discovered at #2503755-142: Switch from user login block to login menu link and search block in standard profile:

I found the root cause.

There are two.

On MySQL, ToolbarAdminMenuTest::testLocaleTranslationSubtreesHashCacheClear(), where it saves a translation ($this->drupalPostForm('admin/config/regional/translate', $edit, t('Save translations'));) is posting the translation for Menus to the first text area on this page:

On SQLite, it looks like this:

On SQLite, but without this patch's DefaultMenuLinkTreeManipulators and MenuLinkBase hunks, it looks like this:

Root cause one: config_translation behaves differently in SQLite/Postgres vs. Mysql

Note how there is one match in MySQL versus 3 in SQLite. Note how MySQL appears to be doing case-sensitive matching, and SQLite case-insensitive.

Root cause two: different ordering with this patch

Somehow, the changes in DefaultMenuLinkTreeManipulators and/or MenuLinkBase are causing a different ordering of those 3 matches.

Conclusion

The test assumes there will be only one match. It's only by chance that HEAD is passing, because in HEAD the first match just happens to be the right one. It should actually only show one match.
So the test translates the WRONG string, and therefore the toolbar menu is the same before and after, and hence the subtrees hash doesn't change on SQLite/Postgres.

Clearly, there are much deeper problems at play here.

This was one giant WTF! It'd be funny if it wasn't just before RC and I now have to somehow fix SQLite support in code totally unrelated to this issue…

Then, Gábor Hojtsy did a further analysis, figuring out why it's behaving differently on SQLite/Postgres, i.e. where the DB mismatch exists, over at #2503755-144: Switch from user login block to login menu link and search block in standard profile:

Looking at the locale schema (the bug has nothing to do with config translation):

function locale_schema() {
  $schema['locales_source'] = array(
    'description' => 'List of English source strings.',
    'fields' => array(
      // ...
      'source' => array(
        'type' => 'text',
        'mysql_type' => 'blob',
        'not null' => TRUE,
        'description' => 'The original string in English.',
      ),
      // ...
    ),
    'primary key' => array('lid'),
    'indexes' => array(
      'source_context' => array(array('source', 30), 'context'),
    ),
  );

  $schema['locales_target'] = array(
    'description' => 'Stores translated versions of strings.',
    'fields' => array(
      // ...
      'translation' => array(
        'type' => 'text',
        'mysql_type' => 'blob',
        'not null' => TRUE,
        'description' => 'Translation string value in this language.',
      ),
      // ...
  );

So in both cases, specifies blob only for MySQL specifically. I don't know if there was a reason to not specify blob for other DB engines given the 'type' key itself supports blob as per our docs:

 *     - 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int',
 *       'float', 'numeric', or 'serial'. Most types just map to the according
 *       database engine specific datatypes. Use 'serial' for auto incrementing
 *       fields. This will expand to 'INT auto_increment' on MySQL.
 *       A special 'varchar_ascii' type is also available for limiting machine
 *       name field to US ASCII characters.
 *     - 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to
 *       use a record type not included in the officially supported list
 *       of types above, you can specify a type for each database
 *       backend. In this case, you can leave out the type parameter,
 *       but be advised that your schema will fail to load on backends that
 *       do not have a type specified. A possible solution can be to
 *       use the "text" type as a fallback.

As per our database.api.php. OTOH looking at the SQLite schema driver, it would only make a text field case insensitive if we did explictly provide a binary key with a FALSE value:

      if (in_array($spec['sqlite_type'], array('VARCHAR', 'TEXT'))) {
        if (isset($spec['length'])) {
          $sql .= '(' . $spec['length'] . ')';
        }

        if (isset($spec['binary']) && $spec['binary'] === FALSE) {
          $sql .= ' COLLATE NOCASE_UTF8';
        }
      }

Did not find the relevant code in the PgSQL driver.

Proposed resolution

Adjust locale.module's DB schema per Gábor's findings.

Remaining tasks

TBD

User interface changes

None.

API changes

None.

Data model changes

None.