Blog de Serge Girard (aka SergioMaster)

Il y a peu une question posée sur le forum à propos de SQLite a titillé ma curiosité.
Comment faire une recherche dans une table SQLite en ne prenant pas en compte les caractères accentués (diacritiques) ?

Bien sûr une solution aurait été de compiler SQLite afin de rajouter des collations, des fonctions ou même l'usage d'expressions régulières, en bref modifier le moteur même ,vous avouerez que c'est une opération lourde sans parler de la portabilité par la suite.

Fort des indications de la page docwiki http://docwiki.embarcadero.com/RADSt...e_avec_FireDAC
La page est de taille importante et les informations utiles bien loin du début ce qui fait que beaucoup de nous peuvent passer à côté nous arrêtant souvent à ceci

Fonctionnalités manquantes de SQLite

les développeurs recherchent souvent les fonctionnalités suivantes, qui ne sont pas fournies dans SQLite :

Procédures stockées.
Atteindre un ensemble de fonctions pré-intégrées.
Système de sécurité, notamment un concept utilisateur et un concept droits d'accès données cryptée, protégée par mot de passe et des rappels spéciaux pour filtrer les actions des utilisateurs.
Classements (format ASCII et binaire uniquement).

Firedac répond à la plupart de ces demandes, mais comment ?
Le chapitre qui nous intéresse est bien plus bas
http://docwiki.embarcadero.com/RADSt..._moteur_SQLite

Ce fut pour moi l'occasion de découvrir dans la palette des composants Firedac un certain nombre de composants spécifiques à SQLite mais aussi les exemples associés.

Oui, il y en avait, encore fallait-il avoir un objectif pour les trouver !

Parti d'abord vers les classements (collation, NOCASE) un gros écueil se dressait : l'opérateur LIKE ne fait pas appel à la collation. De plus, particularité de l'opérateur LIKE et de la fonction du même nom, elles sont déjà insensible à la casse mais pas au diacritiques.

Une première solution, serait d'avoir dans la table deux colonnes, l'une contenant les données avec les diacritiques, l'autre sans. Avantage à ne pas négliger un index sur la colonne sans diacritique est possible, donc rapidité de recherche. Inconvénient, cette colonne il va falloir la remplir en même temps que celle avec diacritiques sans procédure interne à SQLite (toujours le même dilemme) il faut que ce soit le programme de remplissage qui s'en charge.

La deuxième solution consiste à utiliser les extensions proposées par Firedac. Dans ce cas l'ajout de fonctions personnalisées, le composant TFDSQLiteFunction.

La suite de cet article montre une solution possible.

Cahier des charges : Soit une base de données SQLite contenant une table avec des titres de livres, implémenter une fonction de recherche sans tenir compte des diacritiques.

Structure de la table : pour l'exemple je vais utiliser la base de données de calibre (D:\serge\Documents\Bibliothèque calibre\metadata.db) de mes ebooks

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE books ( id      INTEGER PRIMARY KEY AUTOINCREMENT,
                             title     TEXT NOT NULL DEFAULT 'Unknown' COLLATE NOCASE,
                             sort      TEXT COLLATE NOCASE,
                             timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                             pubdate   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                             series_index REAL NOT NULL DEFAULT 1.0,
                             author_sort TEXT COLLATE NOCASE,
                             isbn TEXT DEFAULT "" COLLATE NOCASE,
                             lccn TEXT DEFAULT "" COLLATE NOCASE,
                             path TEXT NOT NULL DEFAULT "",
                             flags INTEGER NOT NULL DEFAULT 1,
                             uuid TEXT,
                             has_cover BOOL DEFAULT 0,
                             last_modified TIMESTAMP NOT NULL DEFAULT "2000-01-01 00:00:00+00:00");

plus d'infos sur la structure http://tutocalibre.free.fr/tuto_sql.php
Remarque en passant, avez-vous vu cette colonne sort ? Elle correspond à la première solution que j'avais évoquée : une colonne pouvant contenir une valeur différente (dans Calibre pour le tri)

Interface utilisateur, succintement reproduire la fonction de recherche de Calibre.


N'y voyez que par affinité l'utilisation de FMX plutôt que VCL, cela ne change en rien le fond.



Première étape : Déposez un composant TFDPhysSQLiteDriver. Par défaut, en déposant un TFDConnection de type SQLite les unités nécessaires avient déjà été déclarées toutefois la seconde étape nécessite l'accés au driver. (Je n'ai pas trouvé le moyen d'accéder au driver directement à partir du TFDConnection)

NOTA : Le problème de tri est facilement solutionnable en utilisant COLLATE NOCASE dans la clause de tri, toutefois c'est une piste que je suivrai dans un prochain billet : l'utilisation de classements "personnalisés", point de départ de la discussion d'origine d'ailleurs.

Seconde étape : Deux solutions, soit déposer un TFDSQLiteFonction, soit déclarer dans la listes des clauses uses l'unité FireDAC.Phys.SQLiteWrapper. Dans l'exemple proposé (téléchargeable ici si vous n'avez pas installé les exemples de code)
comme dans la vidéo indiquée dans la page du DocWiki

le composant est utilisé directement. J'ai préféré faire la création de la fonction au runtime donc seule la déclaration dans la liste des unités utilisées suffit.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
  private
    { Déclarations privées }
    aFDSQLiteFunction : TFDSQLiteFunction;
    procedure SQLSansAccent(AFunc: TSQLiteFunctionInstance;
      AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
    function OterDiacritiques(WS : String) : String;

Pour la fonction OterDiacritiques j'ai utilisé celle proposée par Papy214 telle que décrite ici , efficace en peu de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
function TForm1.OterDiacritiques(WS: String): String;
var  K: TArray<Byte>;
     S : String;
begin
  S:=Lowercase(WS);
  K := TEncoding.Convert(TEncoding.Unicode, TEncoding.ASCII, TEncoding.Unicode.GetBytes(S));
  result:=StringOf(K);
end;

Toute l'astuce est dans la création de la fonction (ici à l'exécution)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
procedure TForm1.SQLSansAccent(AFunc: TSQLiteFunctionInstance;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
Aoutput.asWideString:=OterDiacritiques(Ainputs[0].AsWideString);
end;

procedure TForm1.FDPhysSQLiteDriverLink1DriverCreated(Sender: TObject);
begin
// La création de la fonction se fera à l'éxécution du programme 
aFDSQLiteFunction:=TFDSQLiteFunction.Create(Self);
aFDSQLiteFunction.DriverLink:=FDPhysSQLiteDriverLink1;  // liaison avec le driver
aFDSQLiteFunction.OnCalculate:=SQLsansAccent;            // appel de la fonction
aFDSQLiteFunction.FunctionName:='sansaccent';             // nom qui sera utilisé dans les SQL
aFDSQLiteFunction.ArgumentsCount:=1;                         // nombre d'arguments 
aFDSQLiteFunction.Active:=True;                                   // activation de la fonction  
end;

Veillez bien à activer la fonction. Dans le code ci-dessus il faut que ce soit en dernière ligne. Si vous déposez un composant TFDSQLiteFunction sachez que toute modification de propriété désactive cette fonction, vérifiez donc qu'elle sera correctement activée soit par code soit au design sous peine d'obtenir une erreur à l'exécution du SQL y faisant appel.

Utilisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
procedure TForm3.SearchEditButton1Click(Sender: TObject);
begin
if edtCherche.Text.IsEmpty
  then QueryTitres.Open('SELECT Title Titre FROM BOOKS ORDER BY Title COLLATE NOCASE')
  else QueryTitres.Open('SELECT Title Titre FROM BOOKS WHERE Like(:a,sansaccent(Title)) ORDER BY Title COLLATE NOCASE',
                        ['%'+OterDiacritiques(edtcherche.Text)+'%']);
end;

Quid du nombre de paramètres et d'une utilisation de ceux-ci ? Dans la discussion indiquée plus haut, Andnotor soulevait la problèmatique des ligatures (œ, æ, ß), j'ai voulu aussi pouvoir ajouter ça dans ma fonction personnalisée et faire en sorte que cela soit paramétrable.

Voilà donc une proposition, j'ai tout d'abord ajouté quelques constantes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
  const DIACRITIQUES =1;  // ôter diacritiques
  const LOWER_CASE   =2;  // insensibilité à la casse
  const FR_LIGATURES =4;  // ligatures "françaises"
  const AUTRES_LIGATURES =8; // ligatures autres, il y en a beaucoup 
  const NO_SPACE     =16;   // ne pas tenir compte des espaces

  const LIGATURES_FR  : TArray<Char> = ['&','Æ',Char(223),'Œ','œ'];   // doit être en ordre
  const LIGATURES_FREQ : TArray<String> = ['et','AE','ss','OE','oe'];
  const LIGATURES_OTHER : TArray<Char> = ['ij','IJ'];                   // doit être en ordre
  const LIGATURES_OTHEQ : TArray<String> = ['ij','IJ'];

Puis écrit une procédure plus complexe proscrivant tout appel à des fonctions spécifique Windows.

Comme le faisait remarquer ShaiLeTroll

Envoyé par ShaiLeTroll

Dire que l'on faisait tout ça il y a dix ans, en été 2007 : /delphi/langage/y-t-plus-rapide-enlever-accents
ou encore delphi/langage/supprimer-caracteres-speciaux-d-string

d'autres codifications sont possibles. Mais mon but est plus de montrer comment utiliser plusieurs paramètres dans la fonction utilisateur que je vais créer pour SQLite que de me pencher sur un alogorithme optimal. J'aurais pu utiliser des StringReplace mais il m'était évident que trop de StringReplace serait consommateur de temps et qu'il valait mieux passer par une analyse caractère par caractère de la chaine et des tableaux de remplacement.

Voici donc ma nouvelle fonction pouvant prendre en compte des options.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
function TForm1.OterDiacritiques(const WS : String; const Options : Integer =0): String;
var  K: TArray<Byte>;
     S : String;
     p : integer;
     Ligatures : TArray<Char>;
     LigaturesEq : TArray<String>;
begin
  if (Options AND FR_LIGATURES)>0 then
        begin
          Ligatures:=TArray<Char>.Create();
          LigaturesEQ:=TArray<String>.Create();
          Ligatures:= Ligatures + LIGATURES_FR;
          LigaturesEQ:=LigaturesEQ +LIGATURES_FREQ;
        end;
  if (Options AND AUTRES_LIGATURES)>0 then
   begin
     if not assigned(Ligatures) then Ligatures:=TArray<Char>.Create();
     if not assigned(Ligatures) then LigaturesEQ:=TArray<String>.Create();
     Ligatures:=Ligatures+LIGATURES_OTHER;
     LigaturesEQ:=LigaturesEQ+LIGATURES_OTHEQ;
   end;
  S:=WS;
  if (Options AND LOWER_CASE)>0  then S:=lowercase(S);
  if (Options AND NO_SPACE>0) then  StringReplace(S,' ','',[rfReplaceAll]);

  if (Options AND DIACRITIQUES)>0 then
     begin
      K := TEncoding.Convert(TEncoding.Unicode, TEncoding.ASCII, TEncoding.Unicode.GetBytes(S));
      S := StringOf(K);
     end;
 if  ((Options AND FR_LIGATURES)>0)
  OR ((Options AND AUTRES_LIGATURES)>0) then
 begin
   var SPrime : String :='';
   for var i :=1 to Length(S)
     do begin
       if TArray.BinarySearch(ligatures,S[i],p) then
         begin
           SPrime:=SPrime+LigaturesEQ[p];
         end
       else SPrime:=SPrime+S[i];
     end;
     S:=SPrime;
 end;
 result:=S;
end;

La fonction que je vais ajouter au moteur SQLite est alors modifiée pour prendre en compte le second paramètre ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
procedure TForm1.SQLSansAccent(AFunc: TSQLiteFunctionInstance;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
Aoutput.asWideString:=OterDiacritiques(Ainputs[0].AsWideString,Ainputs[1].asInteger);
end;

Et je remplace alors mon code de création de ma fonction ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
procedure TForm1.FDPhysSQLiteDriverLink1DriverCreated(Sender: TObject);
begin
aFDSQLiteFunction:=TFDSQLiteFunction.Create(Self);
aFDSQLiteFunction.DriverLink:=FDPhysSQLiteDriverLink1;
aFDSQLiteFunction.OnCalculate:=SQLsansAccentPlus;
aFDSQLiteFunction.FunctionName:='sansaccentv2';
aFDSQLiteFunction.ArgumentsCount:=2;
aFDSQLiteFunction.Active:=True;
end;

L'utilisation se fait ainsi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
procedure TForm1.SearchEditButton1Click(Sender: TObject);
begin
if edtCherche.Text.IsEmpty
  then QueryTitres.Open('SELECT Title Titre FROM BOOKS ORDER BY Title COLLATE NOCASE')
  else QueryTitres.Open('SELECT Title Titre FROM BOOKS WHERE Like(:a,sansaccentv2(Title,:d)) ORDER BY Title COLLATE NOCASE',
                        ['%'+OterDiacritiques(edtcherche.Text)+'%',
                         LOWER_CASE AND FR_LIGATURES]);         
end;

En conclusion, une fois compris le principe il est facile de créer des fonctions utilisateurs pour SQLite grâce à Firedac et cela ouvre de belles perspectives comme la possibilité d'utiliser des expressions régulières, des fonctionnalités comme SoundEx, etc.