GERBELOTBARILLON.COM

Parce qu'il faut toujours un commencement...

SQLite3

SQLite 3

Il existe un certain nombre de gestionnaires de bases de données relationnelles mais aucun n’offre l’universalité de SQLite. Nous pourrions citer Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, Oracle, Informix ou d’autres encore. Tous ont des spécificités intéressantes mais aucun n’est aussi souple environnement personnel ou mobile.

SQLite3 met à votre disposition un shell pour gérer interactivement vos bases et leurs données. Vous pouvez passer des commandes d'environnement (pour la mise en forme des résultats de requêtes) ou des commandes de manipulation de données (ajout / suppression / modification / création). Le gros avantage de SQLite3 par rapport aux autres gestionnaires est sa faible empreinte mémoire et disque et sa portabilité par le fait que la base est simplement un fichier qui se suffit à lui-même. De plus, il est possible d'interagir avec cette base à travers de nombreux langages de programmation tels que C, C++, C#, java, python, et tant d’autres. Vous trouverez les informations au sein des nombreuses documentations du site de SQLite3.

Les pré requis d'installation

Nous allons nous contenter de décrire l’installation de SQLite en version 3 sur l’environnement Windows. Il faut donc tout d’abord se rendre sur le site de SQLite, dans la zone de téléchargement (www.sqlite.org/download.html) afin de récupérer les paquets précompilés pour Windows. Ce sont les paquets portant les noms sqlite-shell-win32-*.zip et sqlite-dll-win32-*.zip ainsi que l'archive sqlite3-amalgation-*.zip

Créer ensuite un dossier C:\>sqlite3 sur votre disque dur et renseigner le PATH afin d'ajouter ce dossier dans la liste des dossiers recherchés par les applications. Y copier les fichiers dézippés depuis les 2 archives récupérées précédemment.

Une fois ceci fait, vous pouvez tester le bon fonctionnement en exécutant la commande sqlite3 depuis une commande MS-DOS. Le shell de sqlite3 devrait apparaître et vous permettre de saisir des commandes de gestion de base de données. Je vous laisse consulter les documentations pour connaître l’ensemble des commandes du shell et celles spécifiques au langage SQL supporté par SQLite version 3.

Le shell interactif SQLite

Même si l’API C de SQLite permet la création à la volée d’un fichier de base de données, nous allons faire les choses dans l’ordre et créer manuellement cette base. Lancez la commande sqlite3 depuis une commande MS-DOS. Cela devrait fonctionner si vous avez suivi les informations du paragraphe précédent. Nous arrivons alors sur l’interface du shell interactif de SQLite. Les commandes de ce shell commencent toutes par un ‘.’ :

Pour générer la base de test, que nous nommerons test.db, il faut saisir la commande suivante sous DOS : sqlite3 test.db. Cela crée le fichier en ouvrant le shell interactif. Il vous suffit ensuite de saisir les commandes SQL suivantes : 

CREATE TABLE user (codeuser integer primary key autoincrement not null, name text, age integer);
Le but n’est pas de faire un cours sur les systèmes de bases de données ici, mais de créer une table avec un code qui s’incrémente automatiquement à chaque insertion d’objet, ainsi que d’un nom et de l’âge de l’utilisateur.

Pour insérer une donnée, il suffit ensuite de faire : 

INSERT INTO user(name, age) VALUES(‘user1’, 20);
INSERT INTO user(name, age) VALUES(‘user2’, 30);
Pour afficher la liste des éléments saisis, faire : SELECT * FROM user;

Utilisation de SQLite en langage C

Et voilà pour le début...directement en langage C, sans fioriture... 


#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName)
{
	int i;

	for (i=0; i<argc; i++)
		printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
	printf("\n");

	return(0);
}


int main(int argc, char *argv[])
{
	sqlite3 *db;
	int error;
	char *sql;
	char *errMsg = NULL;
	int rc;

	error = sqlite3_open("test.db", &db);
	if (error < 0) fprintf(stderr, "Erreur d'ouverture de la base\n");
	else
	{
		fprintf(stderr, "Base ouverte correctement\n");
	}

	sql = "select * from user";

	rc = sqlite3_exec(db, sql, callback, NULL, &errMsg);
	if (rc != SQLITE_OK)
	{
		fprintf(stderr, "SQL Error : %s\n", errMsg);
		sqlite3_free(errMsg);
	}
	else
	{
		fprintf(stderr, "Operation done successfully\n");
	}

	sqlite3_close(db);

	return 0;
}

Compilation du programme

Sur ma configuration à base de GCC sous Windows, la compilation s’effectue via la ligne de commande. Pour cela, ouvrez la console MSYS et déplacez-vous dans le dossier qui contient votre programme.

Pour compiler avec SQLite il suffit d’utiliser la commande de ne pas oublier de lier le fichier objet avec la bibliothèque d’édition de liens sqlite3. Par exemple : gcc prog.c -o prog -lsqlite3 Cela va générer le programme nommé prog et qui vous permettra, oh joie, de pouvoir interagir sur une base de données au format SQLite.

Création de la bibliothèque d'édition de liens

Pour utiliser sqlite3 avec les API du langage C, il faut recréer la bibliothèque de linkage pour MingW. Pour cela, aller dans le dossier c:\>sqlite3 et lancer la commande suivante : dlltool -D sqlite3.dll -d sqlite3.def -l libsqlite3.a

Utilisation de SQLite en langage C# (CSharp)

Nativement le langage C# ne permet pas d'accéder aux bases de données SQLite. Il faut simplement télécharger les DLL de gestion sur le site http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki en fonction du Framework .Net dont vous diposez. Les explications détaillées se trouvent sur le site donc elles ne seront pas reprises ici.

Une fois que les éléments ont été téléchargés et mis en place sur le système, pour la prise en charge de SQLite dans C# il suffit de deux choses :

  1. Ajouter une référence à la DLL que vous venez de télécharger en cliquant droit sur la ligne Références de l'explorateur de solutions puis Ajouter une référence.... Il ne vous reste plus qu'à aller chercher le fichier System.Data.SQLite.dll fraîchement téléchargé.
  2. Inclure une directive Using System.Data.SQLite en début de classe afin de pouvoir utiliser les méthodes mises à disposition dans cette dll.

Le driver SQLite que vous venez de mettre en place met à disposition un ensemble de commandes de gestion et de manipulation des objets de la base :

Création d'une base de données

Dans le cas où vous auriez besoin de créer une base de données, il suffit de faire SQLiteConnection.CreateFile("mydb.sqlite"). Cela ne fait que créer un fichier au format SQLite.

Connexion à une base de données

Comme pour toutes les connexions aux bases de données, vous allez avoir besoin d'une chaîne d'initialisation spécifique à SQLite. Vous pouvez retrouver toutes les différentes déclinaisons et paramètres sur le site https://www.connectionstrings.com/sqlite/. Généralement la chaîne de connexion basique (la première de la liste) est celle que vous utiliserez le plus fréquemment. Ce sera quelque chose du genre string connection_string = "Data Source=db.sqlite;version=3;";

Exemple complet en C#

Un programme valant mieux qu'un beau discours, voici un exemple complet en C# qui permettra de voir les différents aspects de la connexion à l'usage d'une table SQLite avec C#. 


using System.Data.SQLite;

namespace test
{
public static void Main()
{
	SQLiteConnection dbconn;
	SqliteCommand cmd;

	if (!File.Exists("lgbooks.sqlite")) { SQLiteConnection.CreateFile("lgbooks.sqlite"); }
	string connection_string = "Data Source=lgbooks.sqlite;version=3;";
	dbconn = new SQLiteConnection(connection_string);
	dbconn.Open();

	cmd = dbconn.CreateCommand();
	cmd.CommandText = "select codeauteur from auteur where nom=:nm and prenom=:pm";
	cmd.Parameters.Add(new SQLiteParameter("nm", nom));
	cmd.Parameters.Add(new SQLiteParameter("pm", prenom));
	reader = cmd.ExecuteReader();
	if (reader.Read())
	{
		codeauteur = Int32.Parse(reader["codeauteur"].ToString());
		//MessageBox.Show("Auteur dans boucle : " + reader["codeauteur"].ToString());
	}
	reader.Close();

	// On ajoute l'entrée dans la table ECRIRE (codelivre, codeauteur, dateecriture)
	cmd = dbconn.CreateCommand();
	cmd.CommandText = "insert into ecrire(codeauteur,codelivre,dateecriture) values(@ca, @cl, @de)";
	cmd.Parameters.Add(new SQLiteParameter("@cl", codelivre));
	cmd.Parameters.Add(new SQLiteParameter("@ca", codeauteur));
	cmd.Parameters.Add(new SQLiteParameter("@de", livre.AnneeEcriture));
	cmd.ExecuteNonQuery();
		
	dbconn.CLose();
}
}

Utilisation de SQLite3 avec Python

Par défaut Python, comme la majorité des langages, ne dispose pas de l'accès à SQLite3 en natif. Il faut installer la bibliothèque sqlite3. Pour cela, le plus simple est d'utiliser pip pip install sqlite3.

Indépendamment des types Python, SQLite gère seulement 4 types :

Exemple de code python pour sqlite3

import sqlite3 # import de la bibliothèque sqlite3 (pip nstall sqlite3)

# Connexion à la base base.db ou création si elle n'existe pas
conn = sqlite3.connect('base.db')
# Déclaration d'un curseur pour les requêtes
curs = conn.cursor()

# Création d'une table par une requête d'exécution
curs.execute("create table citation(id INTEGER PRIMARY KEY AUTOINCREMENT, auteur TEXT NOT NULL, citation TEXT NOT NULL)")

# Insertion d'une donnée dans la table
curs.execute("insert into citation(auteur, citation) values('Inconnu','Un égoïste est quelq'un qui pense plus à lui qu'à moi.')")
conn.commit()

# Lecture depuis une table et récupération de tous les enregistrements
conn.execute("select * from citation")
result = curs.fetchall()
print(result)


# Ajout d'une donnée par séquence
curs.execute("insert into citation(auteur, citation) values(?, ?)", ('auteur inconnu', 'citation de cet auteur'))
conn.commit()

# Ajout d'une donnée depuis un dictionnaire
# Ce qui est important est que les éléments de values(:auteur, :citation) aient le même nom que les clés du dictionnaire.
donnees = [{'x': 'auteur', 'citation': 'aeiou sont les bases'}]
for d in donnees:
	curs.execute("insert into citation(auteur, citation) values(:auteur, :citation)", d)
conn.commit()

# Ajout en masse
paquet_de_donnees = [
	('Anonymous','Although gold dust is precious, it clouds vision.'),	
	('Felon','There is no good or bad but thinking makes it so.'),	
	('Anonymous','No one is lucky, some were patient.'),	
	('Bertrand Russell','The time you enjoy wasting is not wasted time.')
]

curs.executemany("insert into citation(auteur, citation) values(?,?)", paquet_de_donnees)
conn.commit()


# Lecture d'éléments un par un
curs.execute("select * from citation")
while True:
	row = c.fetchone()
	
	if row == None:
		break
	
	print(row[1] + "--" + row[2])

# Exemple de lecture en bloc
curs.execute("select * from citation")
rows = c.fetchall()
for row in rows:
	print("%s %s" % (row["auteur"], row["citation"]))


# Lecture d'éléments sur critère
curs.execute("select * from citation where auteur=?", ('Inconnu, ))
for row in curs:
	print(row)
Par défaut les résultat sont des tupes auxquels on accède par les index du type row[n]. Pour pouvoir utiliser les notations par clé ou nom de champ, il faut acitver le mode row_factory. Les données seront alors du type Sqlite3.Row au lieu de tuple. 
conn.row_factory = sqlite3.Row
curs = conn.cursor()
curs.execute('select x, y from myTable')
row = curs.fetchone()
print(row.keys())
print(row['y'])
curs.close()
Pour revenir au mode par défaut, faire simplement conn.row_factory = None

Les valeurs textuelles renvoyées sont normalement du type unicode. Pour changer ce comportement :

Utilisation de SQLite avec PureBasic

La prise en charge de SQLite est incluse nativement dans PureBasic. Il suffit de réaliser l'inclusion en début de programme pour cela par UseSQLiteDatabase().

Pour ouvrir le fichier SQLite, il faut utiliser la commande OpenDatabase(). Si le fichier n'existe pas, il faudra manuellement le créer par appel à la commande CreateFile(). Le fichier vide pourra ainsi stocker les éléments de SQLite.

Exemple de code PureBasic pour sqlite3

UseSQLiteDatabase()
Filename$ = "mydb.db"
if OpenDatabase(0, Filename$, "", "")
	Debug "Connecté à la base sqlite"
	if DatabaseUpdate(0, "Create Table citation(auteur VARCHAR(255), citation VARCHAR(255));")
		Debug "La table a été créée"
	EndIf
EndIf

Pour le reste des opérations, le très bon site d'assistance de l'éditeur fourmille d'exemples donc n'hésitez pas si vous souhaitez regarder de plus près ce langage super intéressant qu'est PureBasic. https://www.purebasic.com/french/documentation/database/index.html

Liens intéressants

www.sqlite.org
Page principale du projet SQLite. Liens vers les documentations et les téléchargements.
ZetCode SQLite C tutorial
Tutoriel d'usage de SQLite interfacé avec le langage C. Assez complet et didactique.
Tutorialspoint SQLite C/C++ Interface
Tutoriel d'usage de la bibliothèque sqlite3 avec le langage C/C++. Plutôt complet, comme souvent chez tutorialspoint