Comment écrivez-vous une requête insensible à la casse pour MySQL et Postgres?

J’exécute une firebase database MySQL localement pour le développement, mais je déploie sur Heroku qui utilise Postgres. Heroku gère presque tout, mais mes déclarations Like insensibles à la casse deviennent sensibles à la casse. Je pourrais utiliser les instructions iLike, mais ma firebase database MySQL locale ne peut pas gérer cela.

Quelle est la meilleure façon d’écrire une requête insensible à la casse compatible avec MySQL et Postgres? Ou dois-je écrire des instructions Like et iLike distinctes en fonction de la firebase database à laquelle mon application parle?

select * from foo where upper(bar) = upper(?); 

Si vous définissez le paramètre en majuscule dans l’appelant, vous pouvez éviter le deuxième appel de fonction.

La morale de cette histoire est la suivante: n’utilisez pas une stack de logiciels différente pour le développement et la production. Jamais.

Vous allez vous retrouver avec des bogues que vous ne pouvez pas reproduire en dev; vos tests seront sans valeur. Juste ne le fais pas.

L’utilisation d’un moteur de firebase database différent est hors de question – il y aura plus de cas où il se comporte différemment que LIKE (avez-vous vérifié les classements utilisés par les bases de données? Sont-ils identiques dans CHAQUE CAS? oublier ORDER BY sur les colonnes varchar fonctionnant de la même manière

Utilisez Arel:

 Author.where(Author.arel_table[:name].matches("%foo%")) 

matches utiliseront l’opérateur ILIKE pour Postgres et LIKE pour tout le rest.

En postgres, vous pouvez le faire:

 SELECT whatever FROM mytable WHERE something ILIKE 'match this'; 

Je ne suis pas sûr qu’il y ait un équivalent pour MySQL, mais vous pouvez toujours faire ce qui est un peu moche mais devrait fonctionner à la fois dans MySQL et postgres:

 SELECT whatever FROM mytable WHERE UPPER(something) = UPPER('match this'); 

Il y a plusieurs réponses dont aucune n’est très satisfaisante.

  • LOWER (bar) = LOWER (?) Fonctionnera sur MySQL et Postgres, mais sera probablement très performant sur MySQL : MySQL n’utilisera pas ses index à cause de la fonction LOWER. Sur Postgres, vous pouvez append un index fonctionnel (sur LOWER (barre) ), mais MySQL ne le supporte pas.
  • MySQL (sauf si vous avez défini un classement sensible à la casse) effectuera automatiquement une correspondance insensible à la casse et utilisera ses index. ( barre =? ).
  • À partir de votre code en dehors de la firebase database, conservez les champs bar et bar_lower , où bar_lower contient le résultat de la barre inférieure . (Cela peut également être possible en utilisant des déclencheurs de firebase database). (Voir une discussion de cette solution sur Drupal ). Ceci est maladroit mais fonctionne au moins de la même manière sur presque toutes les bases de données.

REGEXP est insensible à la casse (sauf s’il est utilisé avec BINARY), et peut être utilisé comme ça …

  SELECT id FROM person WHERE name REGEXP 'john'; 

… pour correspondre à ‘John’, ‘JOHN’, ‘John’, etc.

Si vous utilisez PostgreSQL 8.4, vous pouvez utiliser le module citext pour créer des champs de texte insensibles à la casse.

Vous pouvez également envisager de consulter le plug- in searchlogic , qui fait le commutateur LIKE / ILIKE pour vous.

Vous pouvez également utiliser ~ * dans postgres si vous souhaitez faire correspondre une sous-chaîne dans un bloc. ~ correspond à la sous-chaîne sensible à la casse, ~ * sous-chaîne insensible à la casse. C’est une opération lente, mais pourrais-je la trouver utile pour les recherches.

 Select * from table where column ~* 'UnEvEn TeXt'; Select * from table where column ~ 'Uneven text'; 

Les deux allaient bash “Un texte irrégulier ici” Seul le premier aurait frappé “Some

La conversion en version supérieure est la meilleure, car elle couvre la syntaxe compatible avec les 3 backends de firebase database Rails les plus utilisés. PostgreSQL, MySQL et SQLite supportent tous cette syntaxe. L’inconvénient (mineur) est que vous devez mettre en majuscule votre chaîne de recherche dans votre application ou dans votre chaîne de conditions, ce qui la rend un peu plus laide, mais je pense que la compatibilité que vous gagnez en vaut la peine.

MySQL et SQLite3 ont tous deux un opérateur LIKE insensible à la casse. Seul PostgreSQL possède un opérateur LIKE sensible à la casse et un opérateur ILIKE spécifique à PostgreSQL (par le manuel) pour les recherches insensibles à la casse. Vous pouvez spécifier ILIKE insead de LIKE dans vos conditions sur l’application Rails, mais sachez que l’application ne fonctionnera plus sous MySQL ou SQLite.

Une troisième option pourrait être de vérifier quel moteur de firebase database vous utilisez et de modifier la chaîne de recherche en conséquence. Cela pourrait être mieux fait en piratant les adaptateurs de connexion ActiveRecord / monkeypatch et que l’adaptateur PostgreSQL modifie la chaîne de requête pour remplacer “LIKE” par “ILIKE” avant l’exécution de la requête. Cette solution est cependant la plus compliquée et, à la lumière de moyens plus simples, comme la mise en majuscule des deux termes, je pense que ce n’est pas le cas (bien que vous obtiendrez beaucoup de points pour le faire de cette façon).