Ignorer les accents dans la recherche

bruk · September 6, 2019, 12:02pm

Bonjour,

Est-il possible de faire une recherche insensible aux accents pour obtenir les mots avec ou sans accents?

Par exemple, si on fait une recherche avec le nom Raphael, j’aimerais obtenir Raphaël.

Merci d’avance.

Francois · September 6, 2019, 2:51pm

Sur postgreSQL il n’y a pas à ma connaissance de collation de type “accent insensitive”.
La question est à poser à un DBA. La collation sert en général pour le tri et la recherche case insensitive.

Il doit être possible de faire des choses en spécifique dans un search-spec:

naccent(t.name) = unaccent('João')

mais qui ne marchera que sur postgreSQL

ou de retirer les accents lors du save… mettre en majuscule sans accent… ou de faire des recherches phonétiques avec des librairies tierces.

Simplicité génère des requêtes SQL99, sauf cas particulier pour postgreSQL :

“ilike” au lieu de “like”
index fulltext pour la recherche globale

pour le reste c’est la base de données qui implémente les règles qu’elle veut.

bruk · September 13, 2019, 7:37am

C’est une bonne idée de pouvoir utiliser une fonction comme unaccent dans un search-spec car nous allons garder les accents qui se trouvent dans le nom et prénom.

En interne, on utiliser une fonction Postgresql qui s’exécute sans problème depuis DBeaver mais cela généré une erreur depuis l’accès Admin / Exploitation / Accueil / onglet Action/Database Access.

voici la fonction

CREATE OR REPLACE FUNCTION public.lower_unaccent(text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $function$

SELECT lower(translate($1
     , '¹²³áàâãäåāăąÀÁÂÃÄÅĀĂĄÆćčç©ĆČÇĐÐèéêёëēĕėęěÈÉÊËЁĒĔĖĘĚ€ğĞıìíîïìĩīĭÌÍÎÏЇÌĨĪĬłŁńňñŃŇÑòóôõöōŏőøÒÓÔÕÖŌŎŐØŒř®ŘšşșßŠŞȘùúûüũūŭůÙÚÛÜŨŪŬŮýÿÝŸžżźŽŻŹ'
     , '123aaaaaaaaaaaaaaaaaaacccccccddeeeeeeeeeeeeeeeeeeeeeggiiiiiiiiiiiiiiiiiillnnnnnnooooooooooooooooooorrrsssssssuuuuuuuuuuuuuuuuyyyyzzzzzz'
     ));
$function$
;

et l’erreur

Error: Unterminated dollar quote started at position 98 in SQL 
CREATE OR REPLACE FUNCTION public.lower_unaccent(text) RETURNS text LANGUAGE sql IMMUTABLE AS $function$ SELECT lower(translate($1 , '¹²³áàâãäåāăąÀÁÂÃÄÅĀĂĄÆćčç©ĆČÇĐÐèéêёëēĕėęěÈÉÊËЁĒĔĖĘĚ€ğĞıìíîïìĩīĭÌÍÎÏЇÌĨĪĬłŁńňñŃŇÑòóôõöōŏőøÒÓÔÕÖŌŎŐØŒř®ŘšşșßŠŞȘùúûüũūŭůÙÚÛÜŨŪŬŮýÿÝŸžżźŽŻŹ' , '123aaaaaaaaaaaaaaaaaaacccccccddeeeeeeeeeeeeeeeeeeeeeggiiiiiiiiiiiiiiiiiillnnnnnnooooooooooooooooooorrrsssssssuuuuuuuuuuuuuuuuyyyyzzzzzz' )). 
Expected terminating $$

Est-ce que vous avez des idées à ce sujet?
Merci d’avance

david · September 13, 2019, 8:32am

La page database access n’est pas faite pour autre chose que du SQL standard (select, update, delete, create, alter, drop), pour passer des commandes complexes PL/SQL faites le directement via psql

bruk · September 13, 2019, 8:37am

Merci. nous, on a une base de données “embedded”, donc je dois exécute la commande depuis la machine physique. C’est ça?

david · September 13, 2019, 8:48am

Je ne comprends pas ce que vous voulez dire par “embedded” s’agissant de PostgreSQL…

Envoyez moi le résultat de l’appel au /health section [Database], merci

bruk · September 13, 2019, 8:48am


[Database]
Vendor=3
ProductName=PostgreSQL
ProductVersion=9.2.24
DriverName=PostgreSQL JDBC Driver
DriverVersion=42.2.6
DBDate=2019-09-13 10:45:50
DBDateOffset=0
DBPatchLevel=P23
UsingBLOBs=true

david · September 13, 2019, 8:53am

Je ne vois pas en quoi cette base est “embedded”… on parle d’une instance sur quel serveur ?

david · September 13, 2019, 8:54am

PS: “embedded” veut dire “qui s’execute dans le même processus”, donc pour Simplicité ça n’a de sens que pour une base comme HSQLDB qui s’exécute dans la même JVM que Tomcat. Quand on a une base PostgreSQL, MySQL/MariaDB, SQL server ou Oracle ça ne peut pas être “embedded”

bruk · September 13, 2019, 9:01am

d’accord, je comprends ce que vous voulez dire !!! je vais voir avec mes collègues qui s’occupent de nos bases !

david · September 13, 2019, 9:14am

Je repose la question => de quel serveur parle-t-on ?

S’il s’agit de votre serveur SIM de dev les bases sont crées localement par le SIM, s’il s’agit des serveurs de vali et de prod il s’agit de bases gérées ailleurs.

bruk · September 13, 2019, 9:18am

Je parle de mon serveur SIM de dev.

david · September 13, 2019, 9:21am

Sur un SIM vous avez accès direct à la base via la commande sim db <moninstance> (qui dans le cas de PostgreSQL lance un psql).

bruk · September 13, 2019, 9:23am

Merci, c’est clair. Je vous tiens au courant.

Francois · September 13, 2019, 9:51am

Je pense que vous prenez une TRES mauvaise direction par solution applicative.

Si vous créez des requêtes spécifiques pour PG dans votre code, il faudra bien tester que la base est PG à chaque fois, car si vous déployez sur une autre base (base dev hsql par exemple), vous aurez des erreurs SQL. Votre application doit être agnostique à la base (si un jour vous souhaitez migrer sur MariaDB par exemple, aucun impact de code).

Et également les problèmes de performance de faire des recherches sur des “fonctions de colonnes” et non des “colonnes indexées” = full scan systématique + conversion d’accents à chaque requêtes = une catastrophe conceptuelle viable que si vous n’avez que très peu de données dans votre table !

La solution doit d’abord se chercher au niveau de la collation de vos colonnes /configuration de la base elle-même ou des tables.
Du genre “COLLATE French_CI_AI”

CI : Case Insensitive
AI : Accent Insensitive

C’est une question à poser à votre DBA pour savoir comment sont configurées vos bases.

https://docs.postgresql.fr/10/collation.html

A défaut choisissez plutôt une base qui permet ce mode recherche nativement par collation.
.

david · September 13, 2019, 10:20am

Oui, effectivement, sur votre demande initiale je suis du même avis que @Francois

bruk · October 4, 2019, 2:07pm

Hello,

Je reviens sur ce poste pour valider un code qui j’ai mis dans le search-spec.

Comme vous l’avez mentionné, je sais que ce n’est pas la bonne solution mais on aimerait évaluer le deux solutions proposées dans ce poste.

Je me suis inspiré de ce poste pour faire le code suivant :

Cette solution marche très bien mais j’aimerais le valider

voici le code

public void preSearch(){
	
	try{
		if (isMainInstance() && getGrant().getDBVendor("jdbc/simplicite") == Globals.DB_POSTGRESQL) {	 
			
			ObjectField ln = getField("DomsecoBoHabitantNomOfficiel");
			ObjectField fn = getField("DomsecoBoHabitantPrenomOfficiel");
			
			/* La première fois que le Hook preseach() est appelé */
			if(ln.isFiltered() && fn.isFiltered()){
				
				nomFiltre = ln.getFilter().trim();
				prenomFiltre = fn.getFilter().trim();
				
				String s1 = "lower_unaccent(t.hab_nom_officiel) = lower_unaccent( '" +  nomFiltre + "')" ;
				String s2 = " and ";
				String s3 = "lower_unaccent(t.hab_prenom_officiel) = lower_unaccent( '" +  prenomFiltre + "')" ;
				
				this.setSearchSpec(s1 + s2 + s3 ); 
				
				/*Si on fait une recherche par un nom et un prénom qui contiennent des accents,
				ils restent comme des filtres dans la liste.
				Donc, je le vide.
				*/
				ln.setFilter("%");
				fn.setFilter("%");
			}
			else if (ln.isFiltered() && !fn.isFiltered()){
				nomFiltre = ln.getFilter().trim();
				ln.setFilter("");
				this.setSearchSpec("lower_unaccent(t.hab_nom_officiel) =  lower_unaccent( '" +  nomFiltre + "')  ");
			}
			else if(!ln.isFiltered() && fn.isFiltered()){
				prenomFiltre = fn.getFilter().trim();
				fn.setFilter("");
				this.setSearchSpec("lower_unaccent(t.hab_prenom_officiel) = lower_unaccent( '" +  prenomFiltre + "')" );
			}
			else{
				this.setSearchSpec("1=1"); 
			}
			
			/* La deuxième fois que le Hook preseach() est appelé */
			if(nomFiltre.length()>0 && prenomFiltre.length()>0){
				String s1 = "lower_unaccent(t.hab_nom_officiel) = lower_unaccent( '" +  nomFiltre + "')" ;
				String s2 = " and ";
				String s3 = "lower_unaccent(t.hab_prenom_officiel) = lower_unaccent( '" +  prenomFiltre + "')" ;
				
				this.setSearchSpec(s1 + s2 + s3 ); 
			}
			else if (nomFiltre.length()>0){
				
				this.setSearchSpec("lower_unaccent(t.hab_nom_officiel) = lower_unaccent( '" +  nomFiltre + "')");
				
			}
			else if (prenomFiltre.length()>0){
				
				this.setSearchSpec("lower_unaccent(t.hab_prenom_officiel) = lower_unaccent( '" +  prenomFiltre + "')" );
			}else{
				
				this.setSearchSpec("1=1"); 
			}
		}
	}
	catch(Exception e){
		AppLog.error(getClass(), "preSearch", "Erreur" ,e, getGrant());
	}
}

et dans le postSearch()

public List<String[]> postSearch(List<String[]> rows){
	
	if(nomFiltre.length()>0){
		getField("DomsecoBoHabitantNomOfficiel").setFilter(nomFiltre);
		nomFiltre="";
	}
	
	if(prenomFiltre.length()>0){
		getField("DomsecoBoHabitantPrenomOfficiel").setFilter(prenomFiltre);
		prenomFiltre="";
	} 
	
	return rows
}

Je vous remercie d’avance.

Francois · October 7, 2019, 1:17pm

Ca doit fonctionner, quelques remarques :

ce code ne marche que si votre objet n’a pas de search spec par ailleurs (typiquement dans le postLoad), auquel cas il faut plutôt utiliser le setAdditionalSearchSpec sur le champ nom ou prenom (c’est comme une search spec globale mais appliqué à un champ).
il faut toujours encoder vos host values avec Tool.toSQL, sinon vous risquez des injections SQL par l’utilisateur. exemple :

this.setSearchSpec("lower_unaccent(t.hab_nom_officiel) = lower_unaccent( '" + Tool.toSQL(nomFiltre) + "') ");

dans postSearch, remettre l’objet exactement comme avant le search.
donc en plus de repositionner les filtres, retirer la search spec :
this.setSearchSpec("1=1");
Et symétriquement faire le postSearch que dans les mêmes conditions :
if (isMainInstance() && getGrant().getDBVendor("jdbc/simplicite") == Globals.DB_POSTGRESQL)...
demandez à votre DBA s’il est possible d’indexer le lower_unaccent(nom,prenom)

david · October 7, 2019, 1:30pm

NB: Comme on parle du datasource principal, plutôt que getGrant().getDBVendor("jdbc/simplicite") utilisez plus simplement getGrant().getDBVendor(), réservez l’usage de getGrant().getDBVendor("<datasource name>") à vos éventuels datasources secondaires spécifiques.

bruk · October 7, 2019, 1:36pm

Merci @Francois pour les remarques.

Je vais les appliquer maintenant.

Concernant la première remarque, pour l’instante mon objet n’a pas de search spec dans les autres hook, mais je vais la noter dans la documentation techniques !!

Merci encore et ce ticket peut être clos.