Gérer ses contacts via SQLite en PureBasic

Franck LEFEVRE

Cet article vous permettra de gérer une base de contacts via PureBasic et SQLite. Du côté PureBasic, ce code nous permettra d'apprendre à utiliser les bases de données en PureBasic.
Commentez ♪

- Titre : Gérer ses contacts via SQLite en PureBasic

- Auteur : Franck LEFEVRE

- Parution : 4 novembre 2009

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2009 LEFEVRE Franck. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Prérequis

- III. Principe

- IV. L'interface graphique

- V. Les fonctions

- V-A. DB_Init()

- V-B. DB_DeleteContact()

- V-C. DB_InsertContact()

- V-D. DB_SelectContact()

- V-E. DB_UpdateContact()

- VI. Compilation et exemple

- VII. Conclusion

- VIII. Remerciements

I - Introduction

Cet article va vous apprendre à utiliser les bases de données avec PureBasic, et principalement SQLite. L'interface graphique et le code le gérant ne seront pas expliqués.

Mais qu'est-ce donc que SQLite ?
SQLite est un gestionnaire de base de données sans serveur, utilisant un fichier comme stockage. Il n'y a rien à déployer ou à configurer, il est tout de suite opérationnel. SQLite est largement utilisé dans l'industrie et est considéré comme un des meilleurs gestionnaires de base de données embarqué. Pour plus d'informations: http://www.sqlite.org. Voici quelques une des caractéristiques de SQLite :

- Gestion des transactions

- Gestion des clefs étrangères

- Support de base de données de plusieurs téraoctets

- Aucune dépendance

- Multiplateforme : Unix, MacOs et Windows

II - Prérequis

Le prérequis principal est de savoir utiliser les fenêtres sous PureBasic, vu que cette partie du code ne vous sera pas expliquée. Le second prérequis est de connaître les principes des bases de données. En cela, voici quelques articles intéressants :

- LE SQL de A à Z : 1re partie - bases de données, SQL et types de données

- LE SQL de A à Z : 2e partie - le simple (?) SELECT et les fonctions SQL

- LE SQL de A à Z : 6e partie - les mises à jour en SQL - INSERT, DELETE et UPDATE

III - Principe

En SQL, il existe quatre types de requêtes principales : DELETE, INSERT, SELECT, UPDATE.

Une requête de type SELECT permet de faire une SELECT-ion dans une ou plusieurs bases de données, en définissant un à plusieurs critères.
Une requête de type INSERT permet de faire une INSERT-ion de lignes dans une ou plusieurs bases de données.
Une requête de type UPDATE permet de mettre à jour un ou plusieurs champs dans une à plusieurs lignes d'une base de données.
Une requête de type DELETE permet de supprimer une à plusieurs lignes dans une base de données en fonction d'un à plusieurs critères.

Finalement, SELECT est le seul type de requêtes à renvoyer des données, car les trois autres ne font que des mises à jour internes aux bases de données. À partir de cela, deux fonctions PureBasic sont intéressantes : DatabaseQuery et DatabaseUpdate. DatabaseQuery sera utilisé avec les requêtes de type SELECT. DatabaseUpdate sera utilisé avec les requêtes de type DELETE, INSERT et UPDATE.

IV - L'interface graphique

Même sans aller en profondeur dans l'étude de la GUI, je vais vous présenter l'interface graphique. Elle est basée sur une fenêtre principale qui chargera les informations du carnet d'adresses et du contact sélectionné. Le chargement initial se fera via la fonction DB_Init().Une listview liste tous les contacts et en fonction de la sélection, l'application appellera DB_SelectContact(). Quatre boutons accompagnent cette interface :

- le bouton « Reset » permettra de réinitialiser le contenu des StringGadget ;

- le bouton « Ajouter » permettra d'insérer un nouveau contact. L'application appellera la fonction DB_InsertContact() ;

- le bouton « Mettre à jour » permettra de mettre à jour le contact courant. L'application appellera la fonction DB_UpdateContact() ;

- le bouton « Supprimer » permettra d'effacer le contact courant. L'application appellera la fonction DB_DeleteContact().

Sous Ubuntu 8.04 :

Sous Windows XP :

V - Les fonctions

V-A - DB_Init()

On initialise l'accès aux bases de données SQLite via la fonction UseSQLiteDatabase().

Ensuite, on teste l'existence du fichier SQLite et en cas de non-présence, on crée le fichier.

If FileSize(#PB_Compiler_FilePath+"GestionContacts.sqlite") < 0
plFile = CreateFile(#PB_Any, #PB_Compiler_FilePath+"GestionContacts.sqlite")
If plFile
CloseFile(plFile)
EndIf
EndIf

Ensuite, on se connecte à la base via OpenDatabase(). Le premier paramètre est l'identifiant. Le second est le chemin vers la base de données SQLite. Les troisième et quatrième paramètres sont des champs vides, car SQLite ne gère pas les connexions avec des utilisateurs. Le cinquième paramètre est le plugin : #PB_Database_SQLite pour les bases SQLite.

glDBSQLite = OpenDatabase(#PB_Any, #PB_Compiler_FilePath+"GestionContacts.sqlite", "", "", #PB_Database_SQLite)
If glDBSQLite = 0
MessageRequester("DVP - Gestion de Contacts", DatabaseError())
End
EndIf

Ensuite, si le fichier vient d'être créé, on crée la table. Il suffit de faire une requête CREATE TABLE et vu qu'il n'y a aucun retour de données comme les requêtes SELECT, on utilise la fonction DatabaseUpdate().

If plFile
psSQLRequest = "CREATE TABLE IF NOT EXISTS contacts ("
psSQLRequest + "id_contact INTEGER PRIMARY KEY AUTOINCREMENT Not NULL, "
psSQLRequest + "contact_nom TEXT Not NULL, "
psSQLRequest + "contact_prenom TEXT Not NULL, "
psSQLRequest + "contact_job TEXT, "
psSQLRequest + "contact_company TEXT, "
psSQLRequest + "contact_address TEXT, "
psSQLRequest + "contact_postalcode TEXT, "
psSQLRequest + "contact_city TEXT, "
psSQLRequest + "contact_country TEXT, "
psSQLRequest + "contact_phone TEXT, "
psSQLRequest + "contact_fax TEXT, "
psSQLRequest + "contact_mail TEXT, "
psSQLRequest + "contact_photo BLOB, "
psSQLRequest + "contact_photo_size INTEGER"
psSQLRequest + ")"
; Debug psSQLRequest
If DatabaseUpdate(glDBSQLite, psSQLRequest) = 0
MessageRequester("DVP - Gestion de Contacts - DB_Init()", DatabaseError())
EndIf
EndIf

Pour finir, on charge le contenu de la base - simplement l'identifiant, le nom et le prénom du contact - dans la listview. Contrairement aux requêtes sans retour de données, on n'utilise pas DatabaseUpdate(), on utilise DatabaseQuery(). Dès lors, une boucle While… Wend avec un NextDatabaseRow() permet de parcourir les enregistrements retournés par le SELECT. Pour récupérer le contenu d'une colonne dans un enregistrement, il faut les GetDatabase* où * correspond au type : String, Float, Long, etc. Ces fonctions utilisent deux paramètres : l'un est l'identifiant de la base et l'autre, le numéro de la colonne (la première colonne étant la colonne 0).

If DatabaseQuery(glDBSQLite, "SELECT * FROM contacts") >< 0
While NextDatabaseRow(glDBSQLite)
AddGadgetItem(#Listview_0, CountGadgetItems(#Listview_0), GetDatabaseString(glDBSQLite, 1) + " " + GetDatabaseString(glDBSQLite, 2))
SetGadgetItemData(#Listview_0, CountGadgetItems(#Listview_0)-1, GetDatabaseLong(glDBSQLite, 0))
Wend
FinishDatabaseQuery(glDBSQLite)
EndIf

Code Complet :

Procedure DB_Init()
Protected plFile.l
Protected psSQLRequest.s
UseSQLiteDatabase()
If FileSize(#PB_Compiler_FilePath+"GestionContacts.sqlite") < 0
plFile = CreateFile(#PB_Any, #PB_Compiler_FilePath+"GestionContacts.sqlite")
If plFile
CloseFile(plFile)
EndIf
EndIf

glDBSQLite = OpenDatabase(#PB_Any, #PB_Compiler_FilePath+"GestionContacts.sqlite", "", "", #PB_Database_SQLite)
If glDBSQLite = 0
MessageRequester("DVP - Gestion de Contacts", DatabaseError())
End
ElseIf plFile
psSQLRequest = "CREATE TABLE IF NOT EXISTS contacts ("
psSQLRequest + "id_contact INTEGER PRIMARY KEY AUTOINCREMENT Not NULL, "
psSQLRequest + "contact_nom TEXT Not NULL, "
psSQLRequest + "contact_prenom TEXT Not NULL, "
psSQLRequest + "contact_job TEXT, "
psSQLRequest + "contact_company TEXT, "
psSQLRequest + "contact_address TEXT, "
psSQLRequest + "contact_postalcode TEXT, "
psSQLRequest + "contact_city TEXT, "
psSQLRequest + "contact_country TEXT, "
psSQLRequest + "contact_phone TEXT, "
psSQLRequest + "contact_fax TEXT, "
psSQLRequest + "contact_mail TEXT, "
psSQLRequest + "contact_photo BLOB, "
psSQLRequest + "contact_photo_size INTEGER"
psSQLRequest + ")"
; Debug psSQLRequest
If DatabaseUpdate(glDBSQLite, psSQLRequest) = 0
MessageRequester("DVP - Gestion de Contacts - DB_Init()", DatabaseError())
EndIf
EndIf

If DatabaseQuery(glDBSQLite, "SELECT * FROM contacts") >< 0
While NextDatabaseRow(glDBSQLite)
AddGadgetItem(#Listview_0, CountGadgetItems(#Listview_0), GetDatabaseString(glDBSQLite, 1) + " " + GetDatabaseString(glDBSQLite, 2))
SetGadgetItemData(#Listview_0, CountGadgetItems(#Listview_0)-1, GetDatabaseLong(glDBSQLite, 0))
Wend
FinishDatabaseQuery(glDBSQLite)
EndIf
EndProcedure

V-B - DB_DeleteContact()

Une fonction sans retour telle qu'une requête DELETE sera utilisée avec la fonction DatabaseUpdate(). Si la fonction DatabaseUpdate() retourne 0, alors il y a eu un problème. Dans ce cas-là, il est possible d'utiliser la fonction DatabaseError(). Cette fonction vous permettra de récupérer la dernière erreur récupérée lors de l'exécution d'une requête.

Mais si la fonction DatabaseUpdate() s'exécute correctement, on efface l'élément sélectionné de la ListView.

Code complet :

Procedure DB_DeleteContact(GadgetItem.l)
Protected psSQLRequest.s
psSQLRequest = "DELETE FROM contacts WHERE id_contact="+Str(GetGadgetItemData(#Listview_0, GadgetItem))
;Debug psSQLRequest
If DatabaseUpdate(glDBSQLite, psSQLRequest) = 0
MessageRequester("DVP - Gestion de Contacts - DB_DeleteContact()", DatabaseError())
Else
RemoveGadgetItem(#Listview_0, GadgetItem)
EndIf
EndProcedure

V-C - DB_InsertContact()

On utilise toujours DatabaseUpdate(), car une requête INSERT ne retourne aucun enregistrement.

Mais au niveau de l'interface graphique, on a besoin de faire une requête SELECT. Pourquoi ? Car comme précisé ci-dessus, une requête INSERT ne retourne aucun enregistrement et par conséquent, il est impossible de connaître l'ID du contact associé lors de la requête INSERT.

Ce code est assez simple. On exécute la requête INSERT. Si cela ne passe pas bien, on affiche une boîte de dialogue avec l'erreur. Sinon on lance une requête SELECT afin de récupérer le dernier enregistrement inséré dans la table CONTACTS. Pour cela, il suffit d'utiliser la procédure stockée MAX qui permet de ne garder que l'élément maximal de la colonne précisée. Ainsi, on ajoute le nom et le prénom de l'enregistrement sélectionné dans la ListView auquel on associe via SetGadgetItemData() l'identifiant du contact.

Il me reste à vous expliquer l'insertion de blobs. Mais qu'est-ce qu'un blob ? C'est un type de donnée permettant le stockage de données binaires. Dans notre cas, ce sera des images. Pour cela, on va utiliser la fonction SetDatabaseBlob(). Cette fonction remplace dans la requête lancée par DatabaseUpdate() ou DatabaseQuery() le '?' par une zone mémoire. Donc dans SetDatabaseBlob(), les paramètres sont les suivants :

- #Database : l'identifiant de la base de données ;

- StatementIndex.l : l'index du blob dans la requête (de 0 à n-1 blobs dans la requête) ;

- *Buffer : la zone mémoire ;

- BufferLength : la taille de la zone mémoire.

Il est conseillé de créer un champ qui stockera la taille de la zone mémoire.

Code complet :

Procedure DB_InsertContact()
Protected psSQLRequest.s
psSQLRequest = "INSERT INTO contacts"
psSQLRequest + "(contact_nom, contact_prenom, contact_job, contact_company, contact_address, "
psSQLRequest + "contact_postalcode, contact_city, contact_country, contact_phone, contact_fax, contact_mail, contact_photo, contact_photo_size)"
psSQLRequest + "VALUES ("
psSQLRequest + "'"+GetGadgetText(#String_0)+"',"
psSQLRequest + "'"+GetGadgetText(#String_1)+"',"
psSQLRequest + "'"+GetGadgetText(#String_2)+"',"
psSQLRequest + "'"+GetGadgetText(#String_3)+"',"
psSQLRequest + "'"+GetGadgetText(#String_4)+"',"
psSQLRequest + "'"+GetGadgetText(#String_5)+"',"
psSQLRequest + "'"+GetGadgetText(#String_6)+"',"
psSQLRequest + "'"+GetGadgetText(#String_7)+"',"
psSQLRequest + "'"+GetGadgetText(#String_8)+"',"
psSQLRequest + "'"+GetGadgetText(#String_9)+"',"
psSQLRequest + "'"+GetGadgetText(#String_10)+"',"
psSQLRequest + "?,"
If glImageMemory
psSQLRequest + Str(MemorySize(glImageMemory))
Else
psSQLRequest + "0"
EndIf
psSQLRequest + ")"
;Debug psSQLRequest
If glImageMemory
SetDatabaseBlob(glDBSQLite, 0, glImageMemory, MemorySize(glImageMemory))
Else
ReplaceString(psSQLRequest, "?", "'0'")
EndIf
If DatabaseUpdate(glDBSQLite, psSQLRequest) = 0
MessageRequester("DVP - Gestion de Contacts - DB_InsertContact()", DatabaseError())
EndIf

; Ajoute le dernier element ajouté à la base
If DatabaseQuery(glDBSQLite, "SELECT max(id_contact), contact_nom, contact_prenom FROM contacts") >< 0
While NextDatabaseRow(glDBSQLite)
AddGadgetItem(#Listview_0, CountGadgetItems(#Listview_0), GetDatabaseString(glDBSQLite, 1) + " " + GetDatabaseString(glDBSQLite, 2))
SetGadgetItemData(#Listview_0, CountGadgetItems(#Listview_0)-1, GetDatabaseLong(glDBSQLite, 0))
Wend
FinishDatabaseQuery(glDBSQLite)
Else
MessageRequester("DVP - Gestion de Contacts - DB_InsertContact", DatabaseError())
EndIf
EndProcedure

V-D - DB_SelectContact()

Vu que cette commande récupère les informations du contact, on a besoin d'utiliser une requête SELECT. Et par conséquent, on a besoin d'un enregistrement donc de la fonction DatabaseQuery().

Si la fonction DatabaseQuery() retourne 0, il y a une erreur. Sinon, une boucle While… Wend permettra de récupérer les enregistrements. Pour passer d'un enregistrement à l'autre, il suffit d'utiliser la fonction NextDatabaseRow(). Ainsi, grâce à la boucle While… Wend, dès que la fonction NextDatabaseRow() retournera 0, ce qui veut dire qu'il n'y a plus d'enregistrements, le programme sortira de la boucle principale.

Dès lors, il nous reste une fonction à utiliser : FinishDatabaseQuery(). Cette fonction permet de clore une fonction DatabaseQuery() et de libérer les ressources associées. Par conséquent, il n'est plus possible d'utiliser les fonctions FirstDatabaseRow() et NextDatabaseRow().

Grâce au dernier champ qui contient la taille de la zone mémoire, on alloue celle-ci. Ensuite, grâce à GetDatabaseBlob(), on choisit quelle colonne d'où extraire la zone mémoire via le deuxième paramètre. Les deux derniers paramètres permettent de configurer la zone mémoire.

Code complet :

Procedure DB_SelectContact(GadgetItem.l)
Protected psSQLRequest.s
Protected plFileImageSize.l
Protected plFileImage.l
psSQLRequest = "SELECT * FROM contacts WHERE id_contact="+Str(GetGadgetItemData(#Listview_0, GadgetItem))
;Debug psSQLRequest
If DatabaseQuery(glDBSQLite, psSQLRequest) >< 0
While NextDatabaseRow(glDBSQLite)
SetGadgetText(#String_0, GetDatabaseString(glDBSQLite, 1))
SetGadgetText(#String_1, GetDatabaseString(glDBSQLite, 2))
SetGadgetText(#String_2, GetDatabaseString(glDBSQLite, 3))
SetGadgetText(#String_3, GetDatabaseString(glDBSQLite, 4))
SetGadgetText(#String_4, GetDatabaseString(glDBSQLite, 5))
SetGadgetText(#String_5, GetDatabaseString(glDBSQLite, 6))
SetGadgetText(#String_6, GetDatabaseString(glDBSQLite, 7))
SetGadgetText(#String_7, GetDatabaseString(glDBSQLite, 8))
SetGadgetText(#String_8, GetDatabaseString(glDBSQLite, 9))
SetGadgetText(#String_9, GetDatabaseString(glDBSQLite, 10))
SetGadgetText(#String_10, GetDatabaseString(glDBSQLite, 11))
plFileImageSize = GetDatabaseLong(glDBSQLite, 13)
If plFileImageSize > 0
glImageMemory = AllocateMemory(plFileImageSize)
GetDatabaseBlob(glDBSQLite, 12, glImageMemory, plFileImageSize)
plFileImage = CatchImage(#PB_Any, glImageMemory, plFileImageSize)
If plFileImage
ResizeImage(plFileImage, 160, 240)
SetGadgetState(#Image_0, ImageID(plFileImage))
FreeImage(plFileImage)
EndIf
Else
SetGadgetState(#Image_0, 0)
EndIf
Wend
FinishDatabaseQuery(glDBSQLite)
Else
MessageRequester("DVP - Gestion de Contacts - DB_SelectContact", DatabaseError())
EndIf
EndProcedure

V-E - DB_UpdateContact()

On retourne à l'utilisation d'une requête UPDATE. Donc, on utilise la fonction DatabaseUpdate(). Comme toujours, en cas d'erreur, on peut récupérer l'erreur via la fonction DatabaseError().

Côté interface graphique, un simple SetGadgetItemText() permet de modifier le nom et le prénom de l'élément sélectionné dans la ListView.

Code complet :

Procedure DB_UpdateContact(GadgetItem.l)
Protected psSQLRequest.s
psSQLRequest = "UPDATE contacts SET "
psSQLRequest + "contact_nom='"+GetGadgetText(#String_0)+"', "
psSQLRequest + "contact_prenom='"+GetGadgetText(#String_1)+"', "
psSQLRequest + "contact_job='"+GetGadgetText(#String_2)+"', "
psSQLRequest + "contact_company='"+GetGadgetText(#String_3)+"', "
psSQLRequest + "contact_address='"+GetGadgetText(#String_4)+"', "
psSQLRequest + "contact_postalcode='"+GetGadgetText(#String_5)+"', "
psSQLRequest + "contact_city='"+GetGadgetText(#String_6)+"', "
psSQLRequest + "contact_country='"+GetGadgetText(#String_7)+"', "
psSQLRequest + "contact_phone='"+GetGadgetText(#String_8)+"', "
psSQLRequest + "contact_fax='"+GetGadgetText(#String_9)+"', "
psSQLRequest + "contact_mail='"+GetGadgetText(#String_10)+"', "
psSQLRequest + "contact_photo = ?,"
If glImageMemory
psSQLRequest + "contact_photo_size = " + Str(MemorySize(glImageMemory))
Else
psSQLRequest + "contact_photo_size = 0"
EndIf
psSQLRequest + " WHERE id_contact="+Str(GetGadgetItemData(#Listview_0, GadgetItem))
;Debug psSQLRequest
If glImageMemory
SetDatabaseBlob(glDBSQLite, 0, glImageMemory, MemorySize(glImageMemory))
Else
ReplaceString(psSQLRequest, "?", "'0'")
EndIf
If DatabaseUpdate(glDBSQLite, psSQLRequest) = 0
MessageRequester("DVP - Gestion de Contacts - DB_UpdateContact()", DatabaseError())
Else
SetGadgetItemText(#Listview_0, GadgetItem, GetGadgetText(#String_0)+" "+GetGadgetText(#String_1))
EndIf
EndProcedure

VI - Compilation et exemple

Téléchargez la source du tutoriel :
Source du tutoriel
Par défaut, toutes les options sont enregistrées dans le fichier.
Compilez votre fichier et automatiquement, le programme devrait être fonctionnel.

VII - Conclusion

Voilà, nous arrivons à la fin de cet article. L'avantage est que ce code est fonctionnel sous Windows (testé sous Windows XP), Linux (testé sous Ubuntu 8.04) et sûrement sous Mac Os.
Ce code est facilement améliorable, et tout cela avec des petites idées simples :

- gérer l'import/export de fichiers CSV, Texte, VCard (format standard de gestion de contacts) ;

- utiliser ODBC ou PostGreSQL pour se connecter à d'autres types de bases ;

- crypter les données ;

- gérer d'autres informations ou données sur le contact ;

- pouvoir associer des fichiers externes avec un contact.

J'espère que cet article permettra aux gens d'avoir un nouveau point de vue sur PureBasic.
Et maintenant à vos claviers !
N'oubliez pas les forums dédiés à SQLite et PureBasic.

VIII - Remerciements

Merci à Comtois pour la relecture.
Enfin je dis un grand merci à l'équipe de DVP pour leur travail.