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

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.

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);';

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+'");';

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);

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
//...

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);

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);