Delphi et les bases de données SQLite en utilisant uniquement l'API de la dll sqlite3

mestressat

DESCRIPTIF : Ce document a pour but de montrer comment exploiter une base de données SQLite en utilisant uniquement l'API de la dll sqlite3.

- Titre : Delphi et les bases de données SQLite en utilisant uniquement l'API de la dll sqlite3

- Auteur : mestressat

- Parution : 12 février 2009

- Licence :

Sommaire

- I. PREREQUIS

- II. FONCTIONNEMENT DE LA DLL / UTILISATION A PARTIR D'UN PROGRAMME DELPHI

- II-A. Déclaration dans l'unité

- II-B. Déclaration dans une unité externe

- III. FONCTIONS DE BASE

- III-A. Ouverture/Création d'une base de données

- III-B. Fermeture d'une base de données

- III-C. Ajout/Modification/Manipulation des données

- III-C-1. Structure commune

- III-C-2. Préalable = Instructions SQL de création de table

- III-C-3. Instructions SQL de remplissage de table

- III-C-4. Sélection d'enregistrements

- III-C-5. Parcours et lecture des enregistrements

- III-C-6. Modification d'un enregistrement

- III-C-7. Suppression d'un enregistrement

- IV. EXECUTION RAPIDE

- V. REQUETES SQL ET PARAMETRES (BIND)

- VI. FONCTIONS DE TRI

- VII. FONCTIONS AVANCEES

- VII-A. Utilisation des fonctions SQL

- VII-B. Création d'une fonction simple

- VII-C. Création d'une fonction d'agrégats

- VIII. CODE SOURCE EXEMPLE

- IX. PLUS DE FONCTIONS

- X. LIENS

I - PREREQUIS

L'utilisation d'une base de données sqlite ne nécessite aucun composant spécifique. Les exemples qui suivent peuvent donc être réalisés à partir des versions personnelles de Delphi (A titre d'exemple, le code source ci-après proposé a été écrit et testé uniquement à partir de Delphi 6 Edition personnel).
Seule la dll sqlite3.dll permettant la manipulation d'une base de données sqlite est nécessaire.

Téléchargement de la dll :
La dll nécessaire à la manipulation d'une base sqlite peut être téléchargée sur le site officiel à partir de la page suivante : http://www.sqlite.org/download.html
Il convient ensuite de choisir la version compilée pour Windows. A l'époque du présent tutoriel, le fichier à télécharger se nomme : sqlitedll-3_6_7.zip

Après avoir extrait la dll sqlite3.dll du fichier zip, cette dll doit être placée soit dans le répertoire système de Windows, soit dans le répertoire où est exécuté le programme faisant appel à la dll.

II - FONCTIONNEMENT DE LA DLL / UTILISATION A PARTIR D'UN PROGRAMME DELPHI

SQLite est une bibliothèque de fonctions, implémentant la majorité de la norme SQL 92, qui ne nécessite aucune procédure d'installation et de configuration, ce qui inclut l'absence de gestion de comptes et de droits utilisateurs.
Le descriptif de la totalité des fonctions est disponible ici : http://www.sqlite.org/c3ref/funclist.html

Pour être utilisées à partir de Delphi, les fonctions utilisées doivent au préalable être déclarées selon la norme suivante :

function nom_de_la_fonction (arguments:type_arguments):type_retour; cdecl; external 'sqlite3.dll';

II-A - Déclaration dans l'unité

La déclaration peut être faite dans l'unité juste avant la partie "implémentation".

Exemple :

type
TForm1 = class(TForm)
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

function sqlite3_open(filename: pchar; var db: pointer): integer; cdecl; external 'sqlite3.dll';

implementation

{$R *.dfm}

II-B - Déclaration dans une unité externe

Par soucis de simplification et pour une plus grande facilité de lecture et de maintenance du code source, il est cependant préférable de regrouper les déclarations des fonctions (et des constantes utilisées par plusieurs de ces fonctions) dans une unité externe.

Vous pouvez télécharger l'unité sqlite3dll.pas regroupant l'ensemble desdites fonctions et constantes ici.
[Note : Beaucoup des fonctions contenues dans cette unité n'ont pas été testées. Leur déclaration peut donc s'avérer incorrecte. Ces déclarations seront éventuellement corrigées ultérieurement au fur et à mesure de mes recherches et de vos observations.]

Dans ce cas, l'utilisation de l'unité externe doit être déclarée dans la partie "uses".

Exemple :

unit Unit1;

interface

uses
//Interface graphique
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs,
//Déclarations des fonctions de la dll sqlite3.dll
sqlite3dll;

type
TForm1 = class(TForm)
private
{ Private declarations }
public
{ Public declarations }
end;

III - FONCTIONS DE BASE

La manipulation d'une base de données comporte habituellement au minimum trois étapes :
- L'ouverture de la base de données (où sa création si celle-ci n'existe pas) ;
- Le remplissage, la modification ou la manipulation des données qui se fait en ce qui concerne la base sqlite au moyen d'instructions SQL ;
- Et enfin la fermeture de la base de données

III-A - Ouverture/Création d'une base de données

L'ouverture d'une base de donnée se fait désormais au moyen de la fonction "sqlite3_open_v2". Les options suivantes sont disponibles :
- SQLITE_OPEN_READONLY : Lecture seule (Si la base n'existe pas elle n'est pas créée)
- SQLITE_OPEN_READWRITE : Lecture et écriture (Si la base n'existe pas elle n'est pas créée)
- SQLITE_OPEN_CREATE : Lecture et écriture (Si la base n'existe pas elle est créée)

Exemple :

var
DB:pointer;

begin
CodeResult:=sqlite3_open_v2(
//Emplacement base
PChar(chemin_access_base_de_donnees),
//Handle base
DB,
//Options ouverture
SQLITE_OPEN_READWRITE+SQLITE_OPEN_CREATE,
// sqlite3_vfs object
nil);
if CodeResult<>SQLITE_OK then begin
MessageDlg(sqlite3_errmsg(DB), mtError, [mbOK], 0);
Exit ;
end;
end;

III-B - Fermeture d'une base de données

La fermeture d'une base de donnée se fait au moyen de la fonction "sqlite3_close". L'indication de la base de données à fermer se fait par le passage en argument du pointer attribué à ladite base lors de l'ouverture.

Exemple :

var
DB:pointer;

begin
//Ouverture de la base de donnée :
sqlite3_open_v2(PChar(chemin_access_base_de_donnees),DB,SQLITE_OPEN_READWRITE,nil);
//Fermeture de la base de donnée :
sqlite3_close(DB);
end;

III-C - Ajout/Modification/Manipulation des données

Les opérations de création, modification et manipulation des données d'une base vont essentiellement se faire au moyen d'instructions SQL.
Précision étant ici faite que ces opérations ne pourront être effectuées que sur une base précédemment ouverte (Pour les exemples qui vont suivre, il est ici indiqué que le pointer de la base de donnée sera stocké dans la variable DB tel qu'indiqué ci-dessus lors de l'ouverture de la base).
Toutes ces opérations seront réalisées suivant une structure commune, seules les instructions SQL différants.

III-C-1 - Structure commune

Classiquement, les requêtes SQL seront passées suivant la structure suivante :
- préparation de la requête au moyen de la fonction "sqlite3_prepare_v2" ;
- exécution de la requête au moyen de la fonction "sqlite3_step" ;
- validation de la requête au moyen de la fonction "sqlite3_finalize".

Exemple :

var
DB:pointer;                 //Handle de la base de donnée
Stmt:pointer;               //Handle de la requête SQL
IgnoreNextStmt:pointer;
sSQL:String;                //Instructions SQL

begin
//Ouverture de la base de donnée :
sqlite3_open(PChar(chemin_access_base_de_donnees),DB);
//Préparation de la requête :
sqlite3_prepare_v2(
DB,                //Handle base de donnée
PChar(sSQL),       //Requête
-1,                //Longueur chaine SQL (-1 : autodetect)
Stmt,              //Handle requête
IgnoreNextStmt);   //Préparation prochaine requête
//Exécution de la requête :
sqlite3_step(Stmt);
//Validation de la requête :
sqlite3_finalize(Stmt);
//Fermeture de la base de donnée :
sqlite3_close(DB);
end;

Cette structure sera à respecter pour chaque instruction SQL devant être exécutée. La différence portera uniquement sur la chaîne sSQL.

III-C-2 - Préalable = Instructions SQL de création de table

Comme dans toute base, les données sont stockées dans des tables contenant eux même des champs.
Ces tables sont créées au moyen de l'instruction SQL "CREATE TABLE".

Exemple :

{
Création d'une table nommée "Testable" comprenant :
- un champs de numérotation automatique incrémental nommé "ID" de type Integer et constituant la clé primaire de la base de donnée
- un champs nommé "OtherID" de type Integer ne pouvant contenir de chaîne vide.
- un champs nommé "Name" de type String d'une longueur maximale de 255 caractères.
- un champs nommé "Number" de type double.
- un champs nommé "Autre" de type blob pouvant notamment stocker des fichiers
}

sSQL := 'CREATE TABLE TestTable ([ID] INTEGER PRIMARY KEY,[OtherID] INTEGER NULL, [Name] VARCHAR (255),[Number] FLOAT, [autre] BLOB);';

III-C-3 - Instructions SQL de remplissage de table

L'ajout de données à une table se fait au moyen d'une suite de trois instructions SQL :
- une première instruction "BEGIN"
- une deuxième instruction "INSERT INTO ... VALUES"
- une troisième instruction "COMMIT"
La première instruction sert à ouvrir une transaction avec la base de données et la dernière instruction à appliquer la ou les insertions faites. Il n'est cependant pas nécessaire d'ouvrir la transaction et d'appliquer l'insertion pour chaque ajout.
En revanche, pour chacune des trois instructions BEGIN/INSERT/COMMIT, la structure commune ci-dessus détaillée de type prepare/step/finalize devra être respectée.

Exemple :

{
Ajout à la table nommée "Testable" d'un nouvel enregistrement
}
sName:='Une chaîne de caractères";
sOtherID:=inttostr(1 + Random(9));
sNumber:=StringReplace(floattostr(random), ',', '.', [rfReplaceAll, rfIgnoreCase]);
sSQL:='INSERT INTO TestTable(Name,OtherID,Number) VALUES ("'+sName+'",'+sOtherID+','+sNumber+',"'+sNotes+'");';

III-C-4 - Sélection d'enregistrements

L'accès aux enregistrements contenus dans une table s'effectue au moyen de l'instruction SQL "SELECT"
La fonction "SELECT" peut retourner l'ensemble des enregistrements d'une table (*) ou certains seulement en fonction de critères passés par l'instruction SQL "WHERE"
A noter que les base de données sqlite3 contiennent un champs ROWID (même si celui-ci n'a pas été précisé lors de la création de la base) contenant le numéro unique de chaque enregistrement
La manipulation des enregistrements sélectionnés se fait ensuite par l'attribution à la requête lors sa création d'un pointer (stmt)

Exemple :

{
Selection de tous les enregistrements (*) contenus dans la table TestTable, ainsi que du numéro unique de chaque enregistrement (ROWID)
}
//Préparation requête
sSQL:='SELECT rowid,* FROM TestTable',
//Exécution requête de sélection
sqlite3_prepare_v2(
DB,               //Handle base de donnée
PChar(sSQL),      //Requête
-1,               //Longueur chaine SQL (-1 : autodetect)
Stmt,             //Handle requête
IgnoreNextStmt);  //Préparation prochaine requête
//Positionnement sur le premier enregistrement contenu dans la sélection
sqlite3_step(Stmt);

III-C-5 - Parcours et lecture des enregistrements

PARCOURS : Le parcours des enregistrements contenus dans une base de données sqlite3 ne peut être effectué qu'au moyen de deux fonctions :
- la fonction "sqlite3_reset" de réinitialisation de la requête.
- la "sqlite3_step" de positionnement sur l'enregistrement suivant.
Les fonctions classiquement utilisées pour les déplacements au travers des enregistrements contenus dans une base de données sont :
- Le premier
- Le dernier
- Le précédent
- Le suivant
Toutefois, compte tenu des deux seules fonctions reset et step disponibles, l'implémentation des fonctions de déplacement ne pourra être réalisée facilement qui si sont connus :
- Le nombre total d'enregistrements contenus dans la requête (Par le biais d'une variable RowCount)
- La position courante (Par le biais d'une variable CurrentRow)
Ces deux données devront être gérées et mises à jour à chaque déplacement.

LECTURE : La lecture des enregistrements s'effectue au moyen des fonctions sqlite3_column_*
Lors de la lecture, il est souvent nécessaire de connaître le type des données contenues dans un champs. Sous sqlite, 4 types sont disponibles :
- INTEGER
- FLOAT
- TEXT
- BLOB
Il faut toutefois observer que la définition d'un type n'empêche pas que le champ contienne des données d'un autre type. Par exemple, un champ de type Integer pourra parfaitement contenir du texte !!
Si le champs ne contient aucune donnée, le type retourné est NULL

Type des données : Le type des données contenues dans un champ peut être déterminé au moyen de la fonction sqlite3_column_type.

Exemple :

var
r:integer;
iResult:Integer;
dResult:Double;
tResult,nResult:String;

r := sqlite3_step(Stmt);

if r = SQLITE_ROW then begin

for i := 0 to sqlite3_column_count(Stmt) - 1 do begin

//Détermination du type de donnée
case sqlite3_column_type(Stmt, i) of
SQLITE_INTEGER: begin
iResult:=sqlite3_column_int(Stmt, 0);
end;
SQLITE_FLOAT: begin
dResult:=sqlite3_column_double(Stmt, 1);
end;
SQLITE_TEXT: begin
tResult:=sqlite3_column_text(Stmt, 2);
end;
SQLITE_NULL: begin
nResult:='(Vide)';
end;
SQLITE_BLOB: begin
//traitement du blob
end;
end;
end;

end;

A noter que quel que soit le type des données contenues dans un champs, le résultat peut être obtenu au moyen de n'importe quelle fonction sqlite3_column_*. Par exemple, le résultat d'une donnée de type integer peut être obtenu au moyen de la fonction "column_text". La conversion entre les type de données est faite en interne par la dll.

LE PREMIER : Le positionnement sur le premier enregistrement s'effectue au moyen de des fonctions sqlite3_reset et sqlite3_step.

Exemple :

//Remise à 0 de la requête
sqlite3_reset(Stmt);
CurrentRow:=0;
//Positionnement du curseur sur le premier enregistrement
sqlite3_step(Stmt);
CurrentRow:=1;
//Lecture du résultat
CurrentID:=sqlite3_column_int(Stmt, 0);        //Colonne 0 = ROWID
GetTextValue:=sqlite3_column_text(Stmt, 1);    //Colonne 1
//...

LE PRECEDENT : L'API sqlite ne dispose d'aucune fonction de positionnement à rebours.
Pour être réalisé, il convient donc :
- de se repositionner sur le premier enregistrement au moyen des fonctions "sqlite3_reset" et "sqlite3_step"
- puis de parcourir les enregistrements au moyen de la fonction "sqlite3_step" jusqu'à l'enregistrement "position actuelle - 1".
D'ou la nécessité de connaître la position de l'enregistrement avant l'exécution de la fonction (Au moyen de la variable CurrentRow gérée tout au long des fonctions de parcours des enregistrements).

Exemple :

//Remise à 0 de la requête
x:=0;
sqlite3_reset(Stmt);
//Parcours des enregistrements jusqu'au n-1
repeat
begin
x:=x+1;
r:=sqlite3_step(Stmt);
end;
until x=CurrentRow-1;
CurrentRow:=CurrentRow-1;
//Lecture du résultat
CurrentID:=sqlite3_column_int(Stmt, 0);      //Colonne 0 = ROWID
GetIntValue:=sqlite3_column_int(Stmt, 2);    //Colonne 2
//...

LE SUIVANT : Le positionnement sur l'enregistrement suivant s'effectue au moyen de la fonction "sqlite3_step"

Exemple :

//Positionnement du curseur sur l'enregistrement suivant
sqlite3_step(Stmt);
CurrentRow:=CurrentRow+1;
//Lecture du résultat
GetDoubleValue:=sqlite3_column_double(Stmt, 3);            //Colonne 3
//...

LE DERNIER : L'API sqlite ne dispose d'aucune fonction de positionnement sur le dernier enregistrement. Pour réaliser cette fonction, la difficulté tient au fait qu'il n'est pas possible de savoir lors de l'exécution de la fonction "sqlite3_step" s'il s'agit du dernier enregistrement.
Seule l'exécution d'un "sqlite3_step" retournant une valeur différente de SQLITE_ROW permet de déduire qu'il n'existe plus d'enregistrement ; Mais le dernier enregistrement ayant alors été dépassé, les fonctions sqlite3_column_* ne retournent plus de valeur ; Et aucune fonction de positionnement à rebours n'étant disponible, il convient que le dernier enregistrement ne soit pas dépassé.
D'ou la nécessité de connaître à l'avance le nombre d'enregistrements (Au moyen de la variable RowCount gérée tout au long des fonctions de parcours des enregistrements) et de les parcourir au moyen de la fonction "sqlite3_step" jusqu'au dernier.

Exemple :

//Parcours des enregistrements jusqu'au dernier
repeat
begin
sqlite3_step(Stmt);
CurrentRow:=CurrentRow+1;
end;
until CurrentRow=RowCount;
//Lecture du résultat
//...

III-C-6 - Modification d'un enregistrement

La modification d'un enregistrement s'effectue au moyen de l'instruction SQL "UPDATE"
Toutefois, la fonction "UPDATE" ne peut être exécuté que s'il n'existe pas de fonction "SELECT" en cours d'exécution.
Pour réaliser la fonction d'enregistrement, il est donc nécessaire :
- de fermer au préalable si nécessaire la requête "SELECT" en cours au moyen de la fonction "sqlite3_finalize"
- d'exécuter la fonction "UPDATE" conformément à la structure commune ci-dessus détaillée de type prepare/step/finalize.
- de rouvrir si besoin est la requête "SELECT" et de se repositionner sur l'enregistrement en cours. D'où la nécessité de connaître l'enregistrement en cours (CurrentRow) ainsi qu'il a déjà été précisé lors de la description des fonctions de parcours des enregistrements.

Exemple :

//Fermeture du select en cours
sqlite3_finalize(Stmt);
//Préparation requête SQL de modification (UPDATE)
sSQL:='UPDATE TestTable SET Letter="A", Number=1 WHERE ROWID='+inttostr(CurrentID)+';';
//Application des modifications
sqlite3_prepare_v2(
DB,
PChar(sSQL),
-1,
Stmt,
IgnoreNextStmt);
sqlite3_step(Stmt);
sqlite3_finalize(Stmt);
//Réouverture de la requête SELECT
sqlite3_prepare_v2(
DB,
'SELECT rowid,* FROM TestTable',
-1,
Stmt,
IgnoreNextStmt);
//Repositionnement sur l'enregistrement courant
for x:=1 to CurrentRow do sqlite3_step(Stmt);

III-C-7 - Suppression d'un enregistrement

La suppression d'un enregistrement s'effectue au moyen de l'instruction SQL "DELETE"
La fonction "DELETE" produit ses effets immédiatement sur les requêtes "SELECT" en cours d'exécution.
Si une requête "SELECT" est toutefois en cours d'exécution, il convient de ne pas oublier de repositionner le curseur sur un enregistrement autre que celui venant d'être supprimé.

Exemple :

var DeleteStmt:pointer;                     //Handle Requête suppression

sSQL:='DELETE FROM TestTable WHERE ROWID='+inttostr(CurrentID)+';';
//Application de la suppression
sqlite3_prepare_v2(
DB,
PChar(sSQL),
-1,
DeleteStmt,
IgnoreNextStmt);
sqlite3_step(DeleteStmt);
sqlite3_finalize(DeleteStmt);

IV - EXECUTION RAPIDE

La procédure prepare/step/finalize est le moyen le plus détaillé d'exécuter des fonctions SQL. L'inconvénient de la longueur du code à développer est compensé par la possibilité de contrôler l'exécution de chaque étape et d'obtenir en retour la confirmation de la bonne exécution de la requête ou un message d'erreur correspondant.
Toutefois, il n'est pas toujours nécessaire de contrôler la bonne exécution de chaque étape de l'exécution d'une requête.
Il existe alors un moyen rapide d'exécuter une instruction SQL, voir une multitude d'instructions SQL : la fonction "sqlite3_exec".
Lorsque plusieurs instructions sont passées en paramètres, la fonction "exec" s'exécute jusqu'à la fin ou s'arrête si une erreur est rencontrée.

Exemple :

var
SQLInstructions:String;        //Contenu script SQL
ExecCode:integer;              //Résultat lors de l'exécution
GetErrorMsg:PChar;             //Message d'erreur
(&#8230;)

ExecCode:=sqlite3_exec(
DB,                            //Handle base de donnée
PChar(SQLInstructions),        //Instructions SQL
nil,                           //Callback (Fonction appelé pour chaque résultat d'une requête.
nil,                           //
GetErrorMsg);                  //Message en cas d'erreur

if ExecCode<> SQLITE_OK then begin
MessageDlg(GetErrorMsg, mtWarning, [mbOK], 0);
exit;
end;

V - REQUETES SQL ET PARAMETRES (BIND)

Un moyen utile de créer une requête SQL est d'utiliser des variables à l'intérieur de la requête.

Ces variables peuvent être définies au choix dans la requête SQL ainsi qu'il suit :
? -> Variable accessible uniquement par son Index - Index attribué incrémentalement
?UnNombre -> Variable accessible uniquement par son Index - Index Défini par UnNombre
:UnNom -> Variable accessible par son Index ou par son nom - Index attribué incrémentalement

Exemple :

sSQL:='SELECT COUNT(ROWID) FROM testtable WHERE ((testtable.id >= ?2) AND (testtable.id <= ?) AND (testtable.name Like :nom_variable));';

Pour connaître le nombre de variables contenues dans une requête, il faut utiliser la fonction "sqlite3_bind_parameter_count". A noter que cette fonction donne non pas le nombre exact de variable, mais l'index le plus élevé.
(Dans l'exemple qui précède, l'utilisation de la variable ?2 décale l'affectation des index suivant, et le bind_parameter_count retourne donc 4).

Le remplacement des variables par leur valeur se fait ensuite au moyen des fonctions "sqlite3_bind_*"

Exemple :

sqlite3_prepare_v2(
DB,                    //Handle DBB
PChar(sSQL),           //Requête
-1,                    //Longueur chaine SQL (-1 : autodetect)
Stmt,                  //Résultat
IgnoreNextStmt);       //Préparation prochaine requête

//REMPLACEMENT DES VARIABLES PAR DU CONTENU

//Détermination des valeurs
ibind1:=1;
ibind2:=26;
sbind:='_';

//Remplacement
sqlite3_bind_int(       //BIND D'UN INTEGER
Stmt,                   //Handle de la requête
2,                      //Index de la variable (En l'espèce celle portant l'index 2 définie par ?2)
ibind1);                //Valeur de remplacement

sqlite3_bind_int(       //BIND D'UN INTEGER
Stmt,                   //Handle de la requête
3,                      //Index de la variable (En l'espèce celle portant l'index 3 définie incrémentalement par ?)
ibind2);                //Valeur de remplacement

sqlite3_bind_text(      //BIND D'UN TEXT
Stmt,                   //Handle de la requête
//Index de la variable recherché à partir du nom de la variable(En l'espèce celle portant l'index 4 définie incrémentalement par :nom_variable)
sqlite3_bind_parameter_index(stmt,':nom_variable'),
pchar(sbind),           //Valeur de remplacement
-1,                     //Nombre de bytes retournés (-1=S'arrête au premier "zero terminator")
//Destructeur après utilisation (SQLITE_STATIC ou SQLITE_TRANSIENT)
SQLITE_TRANSIENT);

Pour réutiliser une requête en ne changeant que la valeur des variables, il suffit de réinitialiser la requête au moyen de la fonction "sqlite3_reset" puis de réaffecter les variables au moyen des fonctions sqlite3_bind_*"

VI - FONCTIONS DE TRI

Il est souvent utile de trier les données retournées par une requête. Le tri peut cependant être ordonné selon diverses méthode.

Par défaut, SQLITE propose 7 méthodes de tri :
- BINARY
- NOCASE
- RTRIM
- SYSTEM
- SYSTEMNOCASE
- USER
- USERNOCASE

L'appel à la méthode de tri se fait via l'instruction SQL COLLATE

Exemple :

//COLLATION BINARY
sSQL:='SELECT * FROM MyTable ORDER BY MyColumn COLLATE BINARY;';

Toutes les méthodes de collation n'étant pas implémentées sous sqlite, il est possible d'ajouter des collations à partir de DELPHI.
La fonction de collation ainsi créée sera ajoutée à la base via la fonction sqlite3_create_collation".

FONCTION : La première étape consiste donc à créer la fonction sous DELPHI.
La fonction devra nécessairement avoir comme paramètres :
UserData:Pointer;
l1: Integer; const s1: Pointer;
l2: Integer; const s2: Pointer;

Exemple :

function AnotherCollation(
UserData: Pointer;
l1: Integer; const s1: Pointer;
l2: Integer; const s2: Pointer): Integer;cdecl;
begin
Result := CompareStringA(LOCALE_SYSTEM_DEFAULT, NORM_IGNORECASE, s1, l1, s2, l2) - 2;
end;

IMPLEMENTATION : Pour pouvoir être utilisée à partir d'une requête SQL, la collation doit être ajoutée à la base. Attention, la collation n'est pas incluse dans la base. Après chaque fermeture de la base de donnée, il est nécessaire de la redéclarer pour pouvoir l'utiliser à nouveau.
La déclaration de la collation se fait ainsi qu'il suit :

Exemple :

sqlite3_create_collation(
DB,                   //Handle DBB
'MyCollation',        //Nom de la collation qui sera utilisée dans la requête SQL
SQLITE_UTF8,          //Format d'encodage pour Callback
self,                 //User data
@AnotherCollation);   //Collation Callback

UTILISATION : Une fois déclarée, la collation peut être utilisée à partir d'une requête SQL avec la méthode prepare/step/finalize

Pour plus de renseignements sur les collations : https://sqlpro.developpez.com/cours/sqlserver/collations/

VII - FONCTIONS AVANCEES

Les instructions SQL offrent par défaut un grand nombre d'expression simple ou d'agrégats permettant d'effectuer une multitude d'opération sur les enregistrements.
Il est en outre possible d'ajouter des fonctions n'existant pas par défaut.

VII-A - Utilisation des fonctions SQL

Comme vu précédemment, les fonctions SQL sont accessibles à partir de sqlite grâce à la fonction "sqlite3_prepare_v2"

Exemple :

{
Utilisation de la fonction SQL "SUM" faisant la somme des valeurs supérieures à 9 contenues dans le champs Note de la table TestTable.
}
//Requête SQL
sSQL:='SELECT SUM(Note) FROM testtable WHERE Note>9;'
//Exécution de la fonction SQL
sqlite3_prepare_v2(
DB,                     //Handle DBB
PChar(sSQL),            //Requête
-1,                     //Longueur chaine SQL (-1 : autodetect)
Stmt,                   //Résultat
IgnoreNextStmt);        //Préparation prochaine requête
sqlite3_step(Stmt);
//Lecture du résultat
iResult:=sqlite3_column_int(Stmt, 0);
//Fermeture de la requête
sqlite3_finalize(Stmt);

VII-B - Création d'une fonction simple

Toutes les fonctions SQL n'étant pas implémentées sous sqlite, il est possible d'ajouter des fonctions à partir de DELPHI.
La fonction ainsi créée sera ajoutée à la base via la fonction "sqlite3_create_function", puis appelée comme n'importe qu'elle autre fonction au moyen de "sqlite3_prepare_v2"

FONCTION : La première étape consiste donc à créer la fonction sous DELPHI. A titre d'exemple, il est ici créé une fonction de concaténation de chaînes. Le renvoi du résultat se fait au moyen des fonctions "sqlite3_result_*"

Exemple :

procedure SimpleFunction(pCtx: pointer; nArgs: Integer; Args: PPointerArray);cdecl;

var
pArg: Pointer;                        //Argument reçu
sReturn:String;                       //Valeur de retour
x:integer;                            //Compteur
//Pour retourner une chaîne - Mais il est possible de retourner un autre type de résultat
buf: string;
SomethingToDo:String;                 //Buffer de formatage du résultat à retourner

begin
//Concaténation de tous les arguments
for x:=0 to nArgs-1 do begin
pArg:=Args[x];
if SomethingToDo='' then
SomethingToDo:=sqlite3_value_text(pArg)
else
SomethingToDo:=SomethingToDo + ' ' + sqlite3_value_text(pArg);
end;
//Chaîne retournée
sReturn:= SomethingToDo;
buf := sReturn;
sqlite3_result_text(pCtx, Pointer(buf), Length(buf), SQLITE_TRANSIENT);
//Exemple pour retourner un integer : sqlite3_result_int(pCtx,1);
end;

IMPLEMENTATION : Pour pouvoir être utilisée à partir d'une requête SQL, la fonction doit être ajoutée à la base. Attention, la fonction n'est pas inclus dans la base. Après chaque fermeture de la base de donnée, il est nécessaire de la redéclarer pour pouvoir l'utiliser à nouveau.
La déclaration de la fonction se fait ainsi qu'il suit :

Exemple :

sqlite3_create_function(
DB,                               //Handle DBB
'MySimpleFunction',               //Nom de la fonction qui sera utilisée dans la requête SQL
-1,                               //Nombre d'argument (-1=Autodetect)
SQLITE_UTF8,                      //Format d'encodage pour Callback
nil,                              //User data
@SimpleFunction,                  //Fonction Callback (Nom de la fonction Ne pas oublier le @)
nil,                              //Step Callback (Sans objet)
nil);                             //Final Callback (Sans objet)

UTILISATION : Une fois déclarée, la fonction peut être utilisée à partir d'une requête SQL.

Exemple :

//Requête SQL
sSQL:='SELECT MySimpleFunction("Voici",3,"Arguments");';
//Exécution de la fonction SQL
sqlite3_prepare_v2(
DB,                    //Handle DBB
PChar(sSQL),           //Requête
-1,                    //Longueur chaîne SQL (-1 : autodetect)
Stmt,                  //Résultat
IgnoreNextStmt);       //Préparation prochaine requête
sqlite3_step(Stmt);
//Lecture du résultat
sResult:=sqlite3_column_text(Stmt, 0);

VII-C - Création d'une fonction d'agrégats

D'une manière similaire aux fonctions simples, il est possible d'ajouter des fonctions d'agrégats, c'est à dire des fonctions qui vont manipuler ou retourner plusieurs enregistrements.
Tout comme une fonction simple, une fonction d'agrégat sera ajoutée à la base via la fonction "sqlite3_create_function", puis appelée comme n'importe qu'elle autre fonction au moyen de "sqlite3_prepare_v2".
La différence avec les fonctions simple tient au fait qu'elle nécessite l'écriture de deux fonctions :
- Une fonction appelée pour chaque enregistrement retourné par la requête (Fonction Step)
- Une fonction appelée pour traiter la totalité des enregistrements retournés (Fonction Finalize)

La première étape consiste donc à créer les fonctions step et finalize sous DELPHI. A titre d'exemple, il est ici créé une fonction de calcul d'écart-type.

FONCTION STEP : La fonction step va permettre de stocker les enregistrements retournés par la requête. Il convient donc de créer un type record dans lequel le stockage va se faire. Ce type est totalement libre et est à adapter en fonction des éléments nécessaires à la fonction.

Exemple :

//Forme de la structure sous laquelle les enregistrements vont être stockés
Type
TStdDevCtx = record                   //Stockage dans un record comprenant :
sum:double;                           //Addition des valeurs
ArrayResult:array of Double;          //Tableau de stockage des valeurs
cnt:Integer;                          //Nombre d'enregistrement
end;
PStdDevCtx = ^TStdDevCtx;

var
p:PStdDevCtx;                         //Structure de stockage des enregistrements

Une fois la forme de stockage définie, il convient de créer la fonction step qui sera appelée pour chaque enregistrement retourné. La fonction step aura nécessairement 3 arguments contenant un pointer, le nombre d'argument, les données.
La fonction step sert principalement à collecter les données.
A l'intérieur de la fonction step, la récupération des données passées en argument se fait au moyen des fonctions sqlite3_value_*. Le handle des données doit être au préalable récupéré au moyen de la fonction "sqlite3_aggregate_context"

Exemple :

procedure StdDevStep(context: pointer; argc: Integer; argv: PPointerArray);cdecl;
//Type + Var nécessaires
var
p:PstdDevCtx;
//&#8230;
begin
if argc<1 then exit;
p:=sqlite3_aggregate_context(context,Sizeof(p^));
//Remplissage de la structure de stockage
if (p<> nil) and (argv[0]<> nil) then begin
x:=sqlite3_value_double(argv[0]);
p.sum:=p.sum+x;
p.cnt:=p.cnt+1;
SetLength(p.ArrayResult,Length(p.ArrayResult)+1);
p.ArrayResult[Length(p.ArrayResult)-1]:=x;
end;
end;

FONCTION FINALIZE : Tout comme la fonction step, la fonction finalize doit reprendre le même type que celui ayant servi à stocker les données lors de la fonction step. C'est la fonction finalize qui va permettre de traiter les données collectées au moyen de la fonction step.
La fonction finalize est appelée après qu'il n'y ait plus d'enregistrement à traiter.
Le handle des données doit être au préalable récupéré au moyen de la fonction "sqlite3_aggregate_context".
Le renvoi du résultat se fait au moyen des fonctions "sqlite3_result_*"

Exemple :

procedure stdevFinalize(context: pointer);cdecl;
//Type + Var nécessaires
var
p:PstdDevCtx;
//&#8230;
begin
p:=sqlite3_aggregate_context(context,Sizeof(p^));
if (p<>nil) and (p.cnt>0) then begin
//Moyenne
for i:= Low(p.ArrayResult) to High (p.ArrayResult) do begin
dSum:=dSum+p.ArrayResult[i];
end;
dMean:=dSum/Length(p.ArrayResult);
//Variance
dSum:=0;
for i:= Low(p.ArrayResult) to High (p.ArrayResult) do begin
dSum:=dSum+((p.ArrayResult[i]-dMean)*(p.ArrayResult[i]-dMean))
end;
dVariance:=dSum/(dMean-1);
//Ecart-type
StdDevResult:=sqrt(dVariance);
sqlite3_result_double(context,StdDevResult);          //Retour du réultat
end;
end;

IMPLEMENTATION : Pour pouvoir être utilisée à partir d'une requête SQL, la fonction d'agrégat doit être ajoutée à la base. Attention, la fonction n'est pas inclus dans la base. Après chaque fermeture de la base de donnée, il est nécessaire de la redéclarer pour pouvoir l'utiliser à nouveau.
La déclaration de la fonction d'agrégat se fait ainsi qu'il suit :

Exemple :

sqlite3_create_function(
DB,                        //Handle DBB
'MyAggregateFunction',     //Nom de la fonction qui sera utilisée dans la requête SQL
-1,                        //Nombre d'argument (-1=Autodetect)
SQLITE_UTF8,               //Format d'encodage pour Callback
nil,                       //User data
nil,                       //Fonction callback (Sans objet)
@stdDevStep,               //Step callback (Nom de la fonction)
@stdevFinalize);           //Final callback (Nom de la fonction Finalize)

UTILISATION : Une fois déclarée, la fonction d'agrégat peut être utilisée à partir d'une requête SQL.

Exemple :

//Requête SQL
sSQL:='SELECT MyAggregateFunction (Notes) FROM MyTable;'
//Exécution de la fonction SQL
sqlite3_prepare_v2(
DB,                        //Handle DBB
PChar(sSQL),               //Requête
-1,                        //Longueur chaîne SQL (-1 : autodetect)
Stmt,                      //Résultat
IgnoreNextStmt);           //Préparation prochaine requête
sqlite3_step(Stmt);
//Lecture du résultat
sResult:=sqlite3_column_text(Stmt, 0);

VIII - CODE SOURCE EXEMPLE

Le code source d'exemples destinés à mieux illustrer les explications qui précèdent est disponible ici.

IX - PLUS DE FONCTIONS

J'espère que ce tutoriel vous aura permis de vous familiariser avec les principales fonctions de SQLITE. Il existe bien sur un grand nombre d'autres fonctions dont vous trouverez ici un descriptif sommaire.
N'hésitez pas à me faire part de vos critiques pour améliorer ce tutoriel.

X - LIENS

Pour plus de renseignements, vous pouvez consulter les sites suivants :
- Site officiel : http://www.sqlite.org/
- Un wrapper complet : http://www.yunqa.de/delphi/doku.php/products/sqlite3/index
- Présentation en français : http://fr.wikipedia.org/wiki/SQLite
- Composants SQLITE : http://www.syndarn.com/sqliteanddelphi.htm