Allow non-case-sensitive searching of strings in UI translation

I heavily support this and willing to fight for this! :-)
So, the idea would be a checkbox next to the search box that enables/disables the case-sensitive search
This has been an issue since Drupal 6, sometimes you do not know if a certain word started with a capital letter, so it makes sense to allow this.

By default, it should be case-sensitive

I'm one of "a number of us"..

This makes sense because the search field should return all relevant forms of the search string, allowing translators to translate both upper and lower case variations at the same time.

Otherwise, the way it's implemented now (wrongly), a translator will have to search for both upper and lower case variations to make sure everything is translated correctly across all strings on the site.

Comment/question to the mockup in comment #1 by Nick_vh

Do we need a checkbox?
Or should we just return both the upper and lower case versions when somebody performs a search?

I can't see any use case when somebody does NOT want to see both the upper and lower case versions of what they search for, so to me it makes most sense to always return any case variation of the string somebody searches for.

depends, from what point on does this become a performance issue? Do we have data that can support this?

Nick_vh.. Never meant to insinuate it was a performance issue, and after reading my post again I can't see how that came up... Anyway, I'll try to reiterate so that the issue becomes clearer.

The issue is that users need to perform *two searches* to catch both the capitalized and lower case versions of any word.
This can be avoided if the search simply returns all variations that matches the letters and not the particular case.

For example if I want to translate a site containing the strings "Log in" (as a link) and "to log in..." (in some sort of description) the search needs to be performed both with "log" and "Log" to make sure I catch everything while I'm translating.

Ok, so the source and translation columns are stored as BLOBs rather than TEXTs if you check out your schemas. The reason for this that I recall is that for the 99% lookup (t(), format_plural() and friends), we always need to do a case sensitive lookup, so rather than doing the case sensitivity runtime (with the BINARY keyword), we store the value itself as binary, so it is naturally looked up like that. Doing case insensitive lookups on BLOB fields sounds like a performance problem, since you need to uppercase or lowercase each column value and then compare it to the uppercase/lowercase value of your search string respectively. That could be a pretty slow operation.

So to solve this I see two options:

A. We either convert the BLOB columns to TEXT columns and do a BINARY match from t() et. al. Needs benchmarks that this does not slow down performance. It might or might not be equivalent to current performance.

B. Or we keep the BLOB column but do uppercase/lowercase likes. This is likely more of a hit for performance BUT not in the crucial frontend part of the site, its merely for this single admin UI. However, the performance decrease can still be prohibitive.

So needs performance testing either way.

Also, for the UI I think making it just do case insensitive searches with no option for case sensitive is best. No need to clutter up the UI with more options, and people definitely rather have insensitive searches.

Putting in the right module queue too.

tagging

assign the issue to myself

I've made some quick performance testing, on a locale_source table with about 15000 record: I've searched for 5200 existing partial string, the results are the following (with MySQL):

For the original method (case sensitive search in blob field): 14.24 sec
db_query('SELECT source FROM locales_sources_orig WHERE source LIKE \'%' . $string . '%\'');

Case insensitive, lower case strings stored in blobs: 20.92 sec
db_query('SELECT source FROM locales_sources_2blob WHERE source_lower LIKE \'%' . strtolower($string) . '%\'');

Case insensitive, lower case strings stored in text: 90.14 sec
db_query('SELECT source FROM locales_sources_string WHERE source_lower LIKE \'%' . strtolower($string) . '%\'');

So I think we should use an other blob field to store the lowercase values. This can a little slows down the searching on the GUI, but it will not touch the t() and related searching.

That sounds like could slow down importing considerably too :/

Here is the first patch for the lowercase blob based insensitive searching.

- schema updated
- update hook
- search in the new fields
- some test updated too

still missing:
- update tests
- tests for searching

As I see for importing we need to write an other blob field converted to lowercase, so I've made a quick performance test for this too:

I've write about 12000 real string (taken from a working D7 site), and write them to the locales_source table:

With the original table (only the original strings are stored as blob) the writing was 8.69 sec.
With the additional blob field for searching it was 8.88 sec.

#14: 1635298-Allow_non_case_sensitive_searching_of_strings_in_UI_translation-14-8.x.patch queued for re-testing.

#14: 1635298-Allow_non_case_sensitive_searching_of_strings_in_UI_translation-14-8.x.patch queued for re-testing.

Note: Ignore the failure result text in the above patch ... the test was manually killed because it was cycling indefinately on one of our testbots; and the result text above is due to how we killed it.

Not sure why it was re-queued if it failed testing the first two times ... if there was something strange about the test results on those first two runs, please let us know.

@jthorson: Thanks for the feedback! I guess it looked puzzling how would any patch cause testbot to not be able to check out Drupal from git. How could this patch cause it? If it could not, then retesting it should possibly find testbot at a happier place and work, or at least give real testing feedback. Do you know how would the git checkout failures be related to the patch and what can we do about it in the patch?

There was a fatal error because of a MySQL insert error in the t(), but only in certain cases (this needs automated tests)

+++ b/core/modules/locale/lib/Drupal/locale/PoDatabaseWriter.php
@@ -283,6 +286,7 @@ class PoDatabaseWriter implements PoWriterInterface {
diff --git a/core/modules/locale/locale.install b/core/modules/locale/locale.install

diff --git a/core/modules/locale/locale.install b/core/modules/locale/locale.install
index 334b401..b9a291d 100644

index 334b401..b9a291d 100644
--- a/core/modules/locale/locale.install

--- a/core/modules/locale/locale.install
+++ b/core/modules/locale/locale.install

+++ b/core/modules/locale/locale.install
+++ b/core/modules/locale/locale.install
@@ -64,6 +64,12 @@ function locale_schema() {

@@ -64,6 +64,12 @@ function locale_schema() {
         'not null' => TRUE,
         'description' => 'The original string in English.',
       ),
+      'source_lower' => array(
+        'type' => 'text',
+        'mysql_type' => 'blob',
+        'not null' => TRUE,
+        'description' => 'The lowercase string in English for searching on the UI translation page.',
+      ),

@@ -623,6 +636,52 @@ function locale_update_8010() {
+function locale_update_8011() {
+  // Sets field for lowercase strings in locales_source table.
+  $spec = array(
+    'type' => 'text',
+    'mysql_type' => 'blob',
+    'not null' => TRUE,
+    'description' => 'The lowercase string in English for searching on the UI translation page.',
+  );
+  db_add_field('locales_source', 'source_lower', $spec);

BLOB can not be NOT NULL, because you can add default value. Set it to FALSE.

+++ b/core/modules/locale/locale.install
@@ -623,6 +636,52 @@ function locale_update_8010() {
+  while($source = $results->fetchAssoc()) {
+    db_update('locales_source')
+      ->fields(array('source_lower' => strtolower($source['source'])))
+      ->condition('lid', $source['lid'])
+      ->execute();
+  }
+
+  // Updates locales_target table with the lowercase translations.
+  $results = db_select('locales_target', 's')
+    ->fields('s', array('lid', 'translation', 'language'))
+    ->execute();
+
+  // Updates translation records one by one because LOWER() isn't a standard SQL function.
+  while($translation = $results->fetchAssoc()) {
+    db_update('locales_target')
+      ->fields(array('translation_lower' => strtolower($translation['translation'])))
+      ->condition('lid', $translation['lid'])
+      ->condition('language', $translation['language'])
+      ->execute();
+  }
+}

Why not use $sandbox? If locales_source contains many string it will run to long.

Corrected the not null problem.

OK, I have no idea why the updates fails:

We have a 'blob' and 'not null' in the schema, and it works on new install.
But with db_add_field() in the update hook it throws an error. BUT: If I just create a new table with a 'blob', 'not null' field and then add an another to the table, it works... So I'll keep searching, but now here is a patch without the update hook, just to test the other parts of the patch.

We discussed it is possible to do the comparison on the fly, and the performance for that did not seem to be prohibitive. The only "little" (hahaha) issue is that the code devised was only working on MySQL. Asked Désiré to post updates, so we can point SQL experts here to help figure this out in a more compatible way :)

Moving off of sprint given no activity and not being critical to drive more activity here ATM.

I just ran into this, searching for "read more" when I should have searched for "Read more".

Added brackets around issue number

I have created some custom translation interface in d7 and to make search case insensitive I did this:
$query->where("CONVERT(r.source USING utf8) like :search", array(':search'=> '%' . db_like($filterString) . '%'));

regarding speed:
SELECT source FROM locales_source WHERE source LIKE '%string%' 0.0129 sec
SELECT source FROM locales_source WHERE CONVERT( source USING utf8 ) LIKE '%string%' 0.0321 sec
actually speed is not accurate. depending on word it was different. and sometimes one query is faster than another. but no big difference. It would go between 0.01 and 0.06s
and I have 45,757 records

Big +1 from me.
Also crosslinking related patch: #2269503: Translation overview page: Allow exact match (or better, anchoring at start or end)

In case anyone lands on this page via Google and is looking for a quick and easy way to make the existing string search case insensitive, I've created a sandbox module that does exactly that (for Drupal 7):

https://www.drupal.org/sandbox/nicrodgers/2593305

Tested on MySQL, and from limited performance tests on sites with between 20,000 and 50,000 strings, search performance is still speedy.

It's obviously just an interim quick-fix solution until it can be addressed with a more permanent solution in locale itself.

@MantasK Where do you place that line?

Hi,

I have looked in the code, and found that the reason this behaviour is happening is because of this line:

locale/src/StringDatabaseStorage.php:441 - StringDatabaseStorage:dbStringSelect

$filter->condition($this->dbFieldTable($field) . '.' . $field, '%' . db_like($string) . '%', 'LIKE');

Now, if this function serves the admin pages only, and not site wide t() function,
We could theoretically change it to:

$filter->where('LOWER(' . $this->dbFieldTable($field) . '.' . $field . ') LIKE \'%' . db_like(strtolower($string)) . '%\'');

(the quotes looks rather ugly.. but you get the idea)

This should have minimal impact of performance.
further more, as we support mysql, sqlite3 and pgsql, all of those engines use LOWER function, so that supposed to be safe to use.

Hello,

i made up an easy solution and wanted to share it with you. Since there is no issue with the BINARY format itself i do not see a task to change it here. The problem is the search query. So i created a view on the locales_source which formats the "source" column in utf8mb4 and then made an override for the StringDatabaseStorage.php in my custom module. Now i can route the query against the locales_source_ci view where the 'LIKE' behaves case-insensitive as wanted.

  CREATE VIEW locales_source_ci as
      SELECT lid, CONVERT(source USING utf8mb4) as source, context, version
      FROM locales_source

  protected function dbStringSelect(array $conditions, array $options = []) {
    // Start building the query with source table and check whether we need to
    // join the target table too.
    $query = $this->connection->select('locales_source_ci', 's', $this->options)
      ->fields('s');

I made a module out of that with auto installing an dropping the view and also a dropdown for searching case-sensitive and case-insensitive, so if there is any interest in it i can upload it somehow.