Fonctions personnalisées pour sqlite
Je fais mes programmes avec delphi 7 version personnelle que j’ai eue gratuitement dès sa sortie en 2002 et qui tourne toujours très bien sous Windows 7 (les deux 7 n’ont rien à voir !). Il manque à cette version personnelle des fonctions de base de données. Aussi j’ai acquis une licence Pro pour utiliser disqlite3 – l’un des portages sous delphi du gestionnaire de bases de données sqlite – réalisé par un Allemand nommé Ralph Junker qui a créé le site The Delphi Inspiration. Bien que la licence soit assez chère, c’est un investissement que je ne regrette pas, car il m’a permis de développer beaucoup de choses que je n’aurai pas pu faire sans. Et au final cette extension est beaucoup plus performante, souple, légère que les composants trop lourds et rigides qui avaient été fournis à l’époque avec la version delphi 7 professionnelle.
Récemment, j’ai eu besoin d’avoir recours à une fonctionnalité avancée de sqlite: créer ma propre fonction SQL. Il s’agit de créer une fonction personnalisée qui est utilisable dans une chaîne SQL comme les fonctions de base incluses dans le programme.
Par exemple, si vous avez une table nommée maTable, et un champ texte (en UTF16) nommé Prenom, vous pouvez écrire une requête SQL du type
SELECT * FROM maTable WHERE upper(Prenom) LIKE 'JUL%'
pour sélectionner non seulement les enregistrements dont le nom vaut JULES, JULIETTE, JULIEN, JULIE etc.., mais aussi ceux qui avaient été écrits en minuscules ou avec des casses aléatoires, comme Jules, Juliette, jules etc… Dans cet exemple la fonction upper est une fonction intégrée dans sqlite qui compare la chaîne JUL% au Prenom converti en majuscules, ce qui rend J égal à j pour la comparaison que fait l’opérateur LIKE.
Mon problème était que cette fonction upper est très basique et ne marche que pour les caractères ASCII. Pour des caractères accentués, elle ne marche pas: par exemple si vous voulez que le Prenom HERVÉ soit équivalent à Hervé. Ainsi dans maTable il y a des enregistrements dont le Prenom est Hervé, mais le code suivant ne donne aucun résultat :
SELECT * FROM maTable WHERE upper(Prenom)='HERVÉ'
En revanche le code suivant donne les enregistrements avec des prénoms Hervé, mais pas ceux qui ont un prénom HERVÉ:
SELECT * FROM maTable WHERE upper(Prenom)='HERVé'.
Cela résulte du fait que la fonction upper ne modifie que les 26 lettres de l’alphabet non accentuées, et ne met pas les caractères accentués en majuscule. Or il existe une fonction wideuppercase dans delphi (dans l’unité sysutils.pas) qui passe très bien non seulement tous les caractères accentués en majuscules, mais aussi les cédilles ou les œ. Aussi il serait très intéressant de disposer de quelque chose d’équivalent dans une requête SQL, d’où l’idée de fabriquer ma propre fonction, qu’on appellera wideupper pour des raisons évidentes.
Le problème est que la documentation pour ce faire est indigente. Celle fournie par Ralph Junker se contente de recopier la documentation générale de sqlite. La seule mention que j’y ai trouvée est la suivante:
An application may define additional functions written in C and added to the database engine using the sqlite3_create_function() API.
C’est assez maigre, et l’étude de la fonction sqlite3_create_function ne donne pas beaucoup plus d’idées claires car elle est totalement dans un contexte de programmation C. J’ai alors désespérément cherché quelques exemples sur Internet qui me mettraient sur la voie, mais je n’ai rien trouvé d’utile. Après beaucoup de tâtonnements, je suis finalement arrivé quand même à un résultat. Aussi pour aider tous ceux qui seraient dans le même cas que moi, je publie ici cet exemple qui pourra inspirer les programmeurs sous delphi qui utilisent sqlite.
La première chose est de déclarer dans votre programme la fonction souhaitée, en respectant les conditions qui permettront à sqlite d’en profiter. Cette fonction est ici la suivante:
procedure wideupper (ctx:Pointer;n:integer;args:sqlite3_value_ptr_array_ptr);
var z:widestring;
begin
z:= sqlite3_value_str16(args[0]);
z:= wideuppercase(z);
sqlite3_result_str16(ctx,z); // résultat de la fonction
end;
Avouez que c’est difficile d’inventer cela tout seul, et le trouver à partir de ce qu’on peut lire sur Internet était à peine plus facile. Il s’agit donc d’un procedure dans delphi qui prend comme arguments un pointeur, un entier et un tableau bizarre de pointeurs qui est heureusement défini dans l’unité disqlite3api. On peut se désintéresser de ces arguments qui seront transparents pour nous, au moins dans cet exemple.
La procédure proprement dite commence par récupérer une variable z widestring qui sera formée justement à partir du 1er argument d’indice 0 de ce tableau, args[0]. Ce sera la chaîne input de notre fonction, par exemple ‘Hervé’ pour reprendre l’exemple plus haut. Nous lui appliquerons tout naturellement la fonction wideuppercase précitée de delphi ,qui met correctement en majuscules les caractères accentués. Puis, la chaîne résultante, ‘HERVÉ’ dans notre exemple, sera donnée à sqlite3_result_str16, ce qui permettra à SQL de récupérer ce résultat.
Vous avez là en fin de compte le prototype de toutes les fonctions personnalisées portant sur des chaînes en widestring, que vous pouvez ajouter à SQLITE dans delphi. Il suffit de garder la première et la dernière ligne, et de remplacer la ligne du milieu par n’importe quel code qui prend en input une variable widestring z, et sort en output la variable z widestring modifiée.
Il reste alors une deuxième chose à faire, c’est de notifier à votre base de données qu’elle peut utiliser cette fonction. Le code que j’utilise est le suivant:
nerr:=sqlite3_create_function16(pBase,'wideupper',1,SQLITE_UTF16 OR SQLITE_DETERMINISTIC,nil,@wideupper,nil,nil);
Ce code doit être appelé après l’initialisation de votre base, par exemple juste après que vous ayez récupéré le pointeur pBase qui la désigne dans une instruction du genre:
nerr:=sqlite3_open16(pwidechar(wFichier),@pbase);
J’utilise ici la variable entière nerr pour récupérer le code d’erreur de l’instruction. Si tout s’est bien passé il doit être sqlite_Ok (soit la valeur 0). Et si tout s’est effectivement bien passé, vous pouvez maintenant utiliser des requêtes SQL du genre
SELECT * FROM maTable WHERE wideupper(Prenom)='HERVÉ'
qui donneront bien le résultat souhaité, en considérant que ‘Hervé’ et ‘HERVÉ’ sont équivalents.
Voilà ma petite contribution à la programmation SQLITE sous delphi.
A propos de l'auteur
