tourlourou

Introduction :

Très ignorant en matière de bases de données, et surtout de composants d'accès aux données, je n'avais que des besoins basiques (à l'époque sous Delphi 5). Je me suis donc attaché à développer une interface simpliste pour le gestionnaire SQLite de sqlite.org, afin de mieux comprendre les mécanismes en jeu.

Ce SGBD libre tient dans une seule DLL (initialement pour Windows 32 bits et Linux), gérant des bases locales mono-utilisateur contenues dans un seul fichier, de manière transactionnelle avec une syntaxe SQL. C'est un moteur très utilisé dans le monde embarqué (smartphones, etc.).

Il s'est d'abord agi de réaliser une interface minimale (wrapper pour l'API) permettant la création ou la connexion à une base de données et les interactions indispensables. Ce wrapper réalise le lien entre le programme et la bibliothèque ; il expose les déclarations nécessaires et sert d'interface, l'implémentation de la logique étant ensuite déléguée à l'application ou à une couche intermédiaire (la librairie que j'ai développée, par exemple).

On trouve ici une documentation en anglais, bien structurée, avec notamment la description de l'API SQLite, qui référence les types, constantes et fonctions de l'interface C/C++. Un petit travail de conversion s'impose donc pour une utilisation en Pascal. A noter aussi, sur l'utilisation de l'API à partir de Delphi, l'unité et le tutoriel de mestressat, ou SQLiteDB de Gabriel@wargan.com.

Ecriture du wrapper :

Je n'ai pas réalisé d'interface exhaustive ni fidèle, mais limitée à mes besoins, et parfois plus parlante dans sa logique Pascalienne ou pour les noms de fonctions ou arguments (en outre préfixés par "a").

Il faudra télécharger la dernière version 32 bits pour Windows de la librairie sur le site, ici la version 3.26.00, au jour de la rédaction de ce billet.

Déclarations de base :

Code Pascal :

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

1
2
3
4
5
6
7
8
9
unit lySQLite3Intf;
 
{$mode objfpc}{$H+}
 
interface   
 
const
  DllName    = 'sqlite3.dll' ;  // Library Name
  MinVersion = 3007013;

Les fonctions utilisent de nombreux codes de retour, dont voici un extrait, le premier indiquant une opération réussie*:

Code Pascal :

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

1
2
3
const
  SQLITE_OK         = 0;   // Successful result
  SQLITE_ERROR      = 1;   // SQL error or missing database

Quelques types de données et prototypes de fonctions CallBack sont utilisés par les fonctions de SQLite et doivent être déclarés avant celles-ci. Les fonctions de rappel, dites CallBack - passées en paramètre à une fonction - permettent de déclencher un traitement personnalisé lors d'événements survenant dans du code externe.

Code Pascal :

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

1
2
3
4
5
6
7
8
type
  PSQLiteDB   = Pointer; // sur "struct sqlite3" (représentation interne de la BDD dans la librairie)
 
  PSQLiteBLOB = Pointer;  // sur "struct sqlite3_blob" (handle interne sur un BLOB dans la librairie)
 
  TSQLiteNewRow   = function(aSender: TObject; aColCount: integer; aValues: Pointer; aNames: Pointer): integer; cdecl;     
 
  TSQLiteProgress = function(aSender: TObject): integer; cdecl;

La convention de passage des paramètres requise par la bibliothèque SQLite est celle du langage C, dite cdecl - pour « C declaration » - indiquée au compilateur par ce mot réservé en fin de la déclaration de fonction.
Nota : j'ai pris la liberté de déclarer le paramètre aSender de type TObject au lieu de Pointer ou LongInt, le cast (transtypage) que cela m'évite restant aisé en cas de besoin.

Fonctions essentielles :

Il ne reste plus qu'à déclarer les fonctions elles-mêmes, en commençant par les principales, de connexion/déconnexion. Le chemin de la base (du fichier), comme toutes les chaînes passées à la librairie, devait être codé au format UTF-8 (il existe désormais des fonctions pour l'UTF16).
NB : ouvrir une base en indiquant son chemin la crée (ie crée le fichier) si elle n'existait pas.

Code Pascal :

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

1
2
3
function sqlite3_open(aFileName: PChar; var aDB: PSQLiteDB): integer; cdecl; external DllName;
 
function sqlite3_close(aDB: PSQLiteDB): integer; cdecl; external DllName;

Compte tenu du rôle souvent central de la BDD dans une application, mon choix s'est porté sur une liaison statique, qui rend disponibles les fonctions concernées tout du long de son exécution. Le mot-clé external en fin de fonction indique au compilateur que l'implémentation est externe, et la constante DllName où la trouver (nom de la bibliothèque) à l'éditeur de liens.

La plupart des appels retournant un code, il convient d'en tester la bonne exécution. La connexion à une base se fait au moyen de la fonction Open en passant simplement son chemin (elle la crée si elle n'existe pas) et un paramètre qui sera le pointeur identifiant la connexion pour les requêtes ultérieures. Il suffit de passer ce handle à la procédure Close pour se déconnecter.

En cas de succès, Open retourne SQLITE_OK; sinon, un code d'erreur indiquant le plus souvent une erreur d'accès (répertoire non valide, format de fichier incorrect, etc. : SQLITE_CANTOPEN, SQLITE_CORRUPT, SQLITE_NOTADB...).

Dans tous les cas où le handle aDB est affecté, il faut libérer les ressources associées en le passant à Close, même en cas d'erreur d'ouverture. Cela n'est pas nécessaire s'il vaut nil, en cas d'échec d'allocation des ressources, mais appeler Close en lui passant nil n'engendre pas d'erreur, donc mieux vaut le faire systématiquement.

L'appel à Close annule une transaction en cours. En cas de ressources non libérées concernant une requête pré-compilée ou des BLOBs (voir plus loin), la fonction échouera en retournant SQLITE_BUSY.

Voici ensuite de quoi exécuter simplement une requête :

Code Pascal :

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

function sqlite3_exec(aDB: PSQLiteDB; aSQL: PChar; aCallBack: TSQLiteNewRow; aSender: TObject; var aErrorMessage: PChar): integer; cdecl; external DllName;

La requête est transmise sous forme de chaîne de caractères (UTF-8) à la base identifiée par son handle de connexion. Nota : plusieurs requêtes séparées par des points-virgules peuvent être transmises dans la même chaîne. En cas d'erreur SQL, le traitement s'arrêtera, ignorant les requêtes suivantes.

La fonction exec fait appel à une fonction CallBack pour chaque ligne de résultat retournée (passer nil pour ne rien récupérer). Le paramètre aSender sera passé en argument à la CallBack pour lui permettre d'identifier l'émetteur de la requête (plusieurs bases peuvent être ouvertes simultanément et la récupération des données faire appel à la même CallBack).
Nota*: ici aussi, le type TObject du paramètre aSender est une liberté personnelle.

La CallBack est invoquée à chaque nouvelle ligne de résultat. aColCount renvoie le nombre de colonnes, dont on trouve les valeurs sous forme de tableau de chaînes dans aValues et les noms des colonnes dans aNames. On récupère donc des champs au format TEXT (SQLite utilise un typage dynamique). Pour récupérer la ligne suivante, la CallBack doit retourner SQLITE_OK ; toute autre valeur interrompra l'exécution (SQLITE_ABORT est la plus parlante).

En cas de succès, exec retourne SQLITE_OK et aErrorMessage vaudra nil. En cas d'erreur, il contiendra le message d'erreur, à libérer par l'appel à sqlite3_free (voir plus loin). Le code de retour pourra valoir SQLITE_ERROR (erreur SQL ou base absente), SQLITE_READONLY (tentative d'écriture dans une table en lecture seule), SQLITE_ABORT (interruption demandée par la CallBack de récupération des lignes), SQLITE_INTERRUPT (interruption demandée par la CallBack passée au gestionnaire de progression), SQLITE_CONSTRAINT (en cas de violation de contrainte).
Attention : il ne faut ni modifier aSQL ni fermer la connexion aDB tant que s'exécute exec.

Il existe un autre moyen pour exécuter une requête, en la faisant pré-compiler par le moteur de SQLite avant de l'exécuter par étapes : cette approche est développée par exemple dans le tutoriel de mestressat, et permet de se passer des CallBack, au prix de la récupération des résultats grâce à des fonctions adressant chaque colonne de chaque nouvelle ligne. On notera au passage que SQLite ne propose pas de procédures stockées, mais permet de définir (et même redéfinir) des fonctions disponibles pour les requêtes SQL, ainsi que de collationnement/agrégation. Pour répondre à mes besoins, je me suis initialement contenté de déclarer la fonction ci-dessus et de ne pas interfacer toutes celles-ci. Cette fonction exec de SQLite est en soi un wrapper autour de toutes celles-ci, pour l'exécution de requêtes en une étape et la récupération de valeurs au format TEXT exclusivement.

On trouve ensuite, des fonctions relatives à l'exécution des requêtes, à savoir une CallBack de gestion de la progression, et une fonction d'arrêt :

Code Pascal :

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

1
2
3
procedure sqlite3_interrupt(aDB: PSQLiteDB); cdecl; external DllName; 
 
procedure sqlite3_progress_handler(aDB: PSQLiteDB; aInterval: integer; aCallBack: TSQLiteProgress; aSender: TObject); cdecl; external DllName;

Pour une requête longue que l'utilisateur voudrait interrompre, il suffit d'appeler Interrupt. Du fait d'un délai possible, il n'est pas garanti que la requête ne parvienne pas malgré tout à son terme (la valeur de retour SQLITE_INTERRUPT signale l'interruption).

Pour des requêtes longues, on peut fixer un progress_handler qui déclenchera une CallBack à intervalles fixés (soit toutes les aInterval instructions de la machine virtuelle de traitement). Cette CallBack pourra permettre la mise à jour de l'interface du programme appelant (barre de progression, par exemple) et devra lui affecter une valeur de retour indiquant à la librairie si elle doit poursuivre le traitement (SQLITE_OK) ou non (toute autre valeur, SQLITE_INTERRUPT étant la plus parlante).

Le gestionnaire de progression passera le paramètre aSender à la CallBack pour l'identifier.
Nota : ici aussi, le type TObject du paramètre aSender est une liberté personnelle.

Gestion des BLOBs :

Des fonctions sont nécessaires pour gérer les champs de type BLOB (Binary Large Objects) :

Code Pascal :

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

1
2
3
4
5
6
7
8
9
function sqlite3_blob_open(aDB: PSQLiteDB; aDbName, aTable, aColumn: PChar; aRow: int64; aWrite: LongBool; var aBLOB: PSQLiteBLOB): integer; cdecl; external DllName;
 
function sqlite3_blob_close(aBLOB: PSQLiteBLOB): integer; cdecl; external DllName;
 
function sqlite3_blob_bytes(aBLOB: PSQLiteBLOB): integer; cdecl; external DllName; // taille en octets du BLOB pointé
 
function sqlite3_blob_read(aBLOB: PSQLiteBLOB; const Buffer; Size, Offset: integer): integer; cdecl; external DllName;
 
function sqlite3_blob_write(aBLOB: PSQLiteBLOB; const Buffer; Size, Offset: integer): integer; cdecl; external DllName;

Un champ BLOB nécessite d'être ouvert en le sélectionnant précisément : noms de la base ('main', 'temp', ou alias d'une seconde base ouverte par la requête ATTACH), de la table, de la colonne, et rowid (clef primaire entière unique interne identifiant chaque ligne d'une table). Il peut être ouvert en lecture seule ou en lecture/écriture (traduction personnelle du paramètre Flags: int en aWrite: LongBool). Il est identifié par le handle interne retourné.

Attention : ne pas refermer un champ BLOB risque d'entraîner une fuite mémoire.

La fonction zeroblob(N) utilisable dans une requête crée un BLOB de taille N octets, tous initialisés à 0. Une fois créé seulement, son contenu peut être modifié (mais pas étendu) avec la fonction d'écriture. Les fonctions d'écriture/lecture font appel à un buffer et s'adressent à une portion du BLOB désignée par sa taille et sa position (Size et Offset). Pour connaître la taille d'un BLOB ouvert, une fonction la renvoie simplement.

Obtention des messages d'erreur :

La gestion des messages d'erreur fait appel à quelques fonctions :

Code Pascal :

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

1
2
3
4
5
function sqlite3_errmsg: PChar; cdecl; external DllName;
procedure sqlite3_free(P: Pointer); cdecl; external DllName;
function sqlite3_errstr(aErrCode: integer): PChar; cdecl; external DllName; 
 
function sqlite3_extended_result_codes(aDB: PSQLiteDB; aExtended: LongBool): integer; cdecl; external DllName;

Nous avons déjà évoqué la nécessité de libérer les messages d'erreur reçus dans le paramètre aErrorMessage de la fonction exec en les passant à free. On peut obtenir au besoin les messages d'erreur (UTF-8, en anglais) grâce à errmsg (pas de libération nécessaire), et activer les codes d'erreurs étendus (désactivés par défaut pour assurer la rétrocompatibilité), sur 16 bits au lieu de 8, grâce à extended_result_codes. Errstr renvoie l'intitulé (UTF-8, en anglais) correspondant à un code d'erreur.

Fonctions auxiliaires :

Plusieurs fonctions d'information sont liées aux modifications de la base, à la suite de requêtes de type INSERT, DELETE ou UPDATE :

Code Pascal :

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

1
2
3
4
function sqlite3_last_insert_rowid(aDB: PSQLiteDB): int64; cdecl; external DllName;
function sqlite3_changes(aDB: PSQLiteDB): integer; cdecl; external DllName; // suite à l'Exec
 
function sqlite3_total_changes(aDB: PSQLiteDB): integer; cdecl; external DllName; // depuis l'ouverture de la base

On a accès au nombre de lignes affectées par ces instructions à la suite d'un exec, ou depuis la connexion à la base. Mais le plus important est certainement de connaître le rowid attribué lors du dernier INSERT (par exemple, pour un BLOB qu'on va initialiser à la taille voulue lors de l'INSERT, avant de l'ouvrir pour son écriture).

On dispose également de fonctions renseignant sur la configuration de la librairie :

Code Pascal :

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

1
2
3
4
5
function sqlite3_libversion_number: integer; cdecl; external DllName; // sous la forme 3007013 (3*10^6+7*10^3+13) pour la 3.7.13
 
function sqlite3_libversion: PChar; cdecl; external DllName; 
 
function sqlite_libencoding: PChar; cdecl; external DllName;

Les plus importantes portant sur la version, puisque l'encodage des chaînes est de base UTF-8, compatible avec Lazarus, si on ne procède pas à un autre réglage. Il peut être nécessaire de contrôler la version de la librairie utilisée, pour s'assurer qu'elle implémente des fonctions qu'on souhaite adresser. Pour être sûr de cette capacité, on peut distribuer (librement) la dll.

Quelques autres fonctions peuvent être à découvrir dans l'unité, mais les briques essentielles pour une utilisation de base sont ici posées.

Du fait de l'exploitation de la bibliothèque, la section d'implémentation de l'unité se résume à un code d'initialisation destiné à prévenir l'utilisation avec une version antérieure à celle voulue, comme évoqué plus haut.
Nota : en fait, la compatibilité est probablement assurée à partir de la version 3.6.7 (voire avant), mais n'a pas été testée.

Code Pascal :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
implementation
 
uses
  SysUtils;
 
initialization
  if MinVersion > sqlite3_libversion_number
  then raise Exception.Create('Utilisation prévue seulement pour SQLite 3.7.13 ou supérieure ; ici : ' + sqlite3_libversion);
 
finalization
  // RAS
 
end.

Utilisation de la librairie :

Un court exemple va maintenant montrer comment agencer les briques présentées ci-dessus pour exploiter SQLite.
Pour ceci, rien de tel qu'une fiche avec un bouton et un Memo !
Chacun pourra redéfinir la constante BddPath qui contient le chemin du fichier (c'est-à-dire la base de données) :

Code Pascal :

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
unit Unit1;
 
{$mode objfpc}{$H+}
 
interface
 
uses
  Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls,
  lySqlite3Intf; 
 
type
 
  { TForm1 }
 
  TForm1 = class(TForm)
    Button1: TButton;
    Memo1: TMemo;
    procedure Button1Click(Sender: TObject);
  private
    { private declarations }
  public
    { public declarations }
  end;
 
const
  BddPath = 'c:\userfiles\test1.bdd';
 
var
  Form1: TForm1;

La section implémentation va définir plusieurs fonctions utiles au dialogue avec la base. Tout d'abord, une fonction CallBack (OnNewRow) qui sera appelée à chaque nouvelle ligne de résultat d'un appel à exec, et sera chargée d'afficher la valeur de chaque champ après le nom de la colonne.
On illustre ici l'utilisation du paramètre aSender et la manipulation des valeurs retournées sous forme de tableaux de chaînes :

Code Pascal :

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
implementation
 
{$R *.lfm}
 
function OnNewRow(aSender: TObject; aColCount: integer; aValues: Pointer; aNames: Pointer): integer; cdecl;
var
  PNames, PValues: ^PChar;
  i: Integer;
  s: string;
begin
  if aSender is TForm1
  then TForm1(aSender).Memo1.Lines.Add('----- Nouvelle ligne -----')
  else raise Exception.Create('Pas le bon Sender ?');
 
  PValues := aValues;
  Pnames := aNames;
 
  for i:=0 to aColCount-1
  do begin
    s:=StrPas(PNames^)+' = '+StrPas(PValues^);
    TForm1(aSender).Memo1.Lines.Add(s);
    Inc(PValues);
    Inc(PNames);
  end;
 
  Result := SQLITE_OK;
 
end;

Pour simplifier la gestion des erreurs, l'appel à la fonction exec est encapsulé dans une procédure DoSQLExec qui se charge de lui transmettre les paramètres, de tester la valeur de retour et de lever en cas d'erreur une exception renseignée avec reprise de l'erreur et du message (libéré convenablement) :

Code Pascal :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
procedure DoSQLExec(aDB: PSQLiteDB; aQuery: PChar; aCallBack: TSQLiteNewRow; aSender: TObject);
var
  i: integer;
  ErrMsg: PChar;
  S: string;
begin
  i := sqlite3_exec(aDB, aQuery, aCallBack, aSender, ErrMsg);
  if i <> SQLITE_OK
  then begin
    S := StrPas(ErrMsg);
    sqlite3_free(ErrMsg);
    raise Exception.Create('Erreur '+IntToStr(i)+' ('+sqlite3_errstr(i)+') : '#10#13+S);
  end;
end;

Dernière étape, dans le code du bouton, pour la démonstration :
1) ouverture (création au besoin) de la base,
2) test d'une commande SQL directe, non reliée à une base,
3) test d'une requête SQL concernant la base,
4) test de création/consultation de table,
5) test de la gestion des erreurs.

Code Pascal :

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
47
48
49
50
51
52
53
54
{ TForm1 }
 
procedure TForm1.Button1Click(Sender: TObject);
var
  i: integer;
  DB: PSQLiteDB;
begin
  try
    try
      Memo1.Clear;
 
      i := sqlite3_open(BddPath, DB);
 
      if i <> SQLITE_OK
      then raise Exception.Create('Erreur '+IntToStr(i)+' ('+sqlite3_errstr(i)+') : '#10#13+sqlite3_errmsg);
 
      Memo1.Lines.Add('************* test SQL direct *************');
      DoSQLExec(DB, 'SELECT date(''now'')', @OnNewRow, self);
 
      Memo1.Lines.Add('************* test PRAGMA sur base *************');
      DoSQLExec(DB, 'PRAGMA encoding', @OnNewRow, self);
 
      Memo1.Lines.Add('************* test SELECT sur table *************');
      DoSQLExec(DB, 'CREATE TABLE IF NOT EXISTS employes ( id INTEGER PRIMARY KEY , nom TEXT , age INTEGER )', nil, nil);
      DoSQLExec(DB, 'DELETE FROM employes', nil, nil);
 
      DoSQLExec(DB, 'INSERT INTO employes ( nom , age ) VALUES ( ''toto'' , 50 )', nil, nil);
      DoSQLExec(DB, 'INSERT INTO employes ( nom , age ) VALUES ( ''tati'' , 25 )', nil, nil);
 
      DoSQLExec(DB, 'SELECT * FROM employes', @OnNewRow, self);
 
      Memo1.Lines.Add('************* test erreur SQL *************');
      DoSQLExec(DB, 'INSERT INTO employes ( nom , age ) VALUES ( tutu , 75 )', nil, nil);
 
    finally
 
      i := sqlite3_close(DB);
 
      if i <> SQLITE_OK
      then raise Exception.Create('Erreur '+IntToStr(i)+' ('+sqlite3_errstr(i)+') : '#10#13+sqlite3_errmsg);
 
    end;
  except
 
    on E: Exception
    do begin
      MessageDlg('Test SQLite', E.Message, mtError, [mbOK], EmptyStr);
      Memo1.Lines.Add(E.Message);
    end;
 
  end;
end;
 
end.

Vous trouverez les unités ici : Billet_numero_1.zip

Conclusion :

Au final, nous avons vu une façon d'assembler ces briques pour utiliser SQLite à partir de cette interface sommaire. Ceci n'est pas si simple, concernant la récupération des résultats de requêtes, fastidieuse, qui fait appel à des fonctions CallBack, ainsi que pour la gestion des BLOBs.
Ceci a motivé les développements ultérieurs, dont une classe gérant les requêtes paramétrées, puis l'encapsulation des appels dans un objet, qui feront l'objet de prochains billets.
Le code, prévu pour Lazarus, est très aisément adaptable pour Delphi, ainsi que pour la version 64 Bits de la librairie SQLite.