SQLite3
Introduction aux bases de données relationnelles
Ce vendredi 28 sept 2018 nous avons fait une petite introduction aux bases de données relationnelles (SGBD) à la suite du petit défi posé par Olivier.
Les bases de données
Olivier enregistre le nombre de litres d’essences mis et le kilométrage de son véhicule dans une base de données. Qu’est-ce qu’une base de données ?
On peut voir une base de données comme un ensemble de feuilles de calculs. Chaque feuille de calcul s’appelle une table, et chaque table est composée de lignes (enregistrements) et de colonnes (champs).
Cependant, une base de données relationnelle est bien mieux adaptée pour travailler avec les relations entre les tabes qu’un tableur.
Imaginons un garage qui souhaite faire une liste de ses clients et de leurs véhicules. Nous pourrions créer une feuille de calcul avec des colonnes pour enregsitrer les véhicules : marque, modèle, kilométrage, mais aussi nom du propriétaire, adresse du propriétaire, email du propriétaire.
Déjà, on se rend compte que si un client a plusieurs véhicules, on va devoir dupliquer certaines informations. L’adresse du client, par exemple, apparaitra plusieurs fois, une fois par véhicule. Et si le client déménage, il faudra mettre à jour toutes ces lignes. C’est fastidieux, et il y a un énorme risque de simplement oublier de mettre à jour les autres véhicules.
La duplication des informations n’est jamais bonne. C’est toujours une source importante d’erreurs, et ça peut représenter énormément de place occupée inutilement. Pas dans le cas d’un garage, mais sur l’enregistrement, chaque seconde, de la mesure de soixante sondes de températures pendant dix ans, ce serait une perte énorme de devoir à chaque fois remettre le numéro de série de la sonde, sa date d’achat, la marque et le modèle, l’adresse du fournisseur…
Instinctivement, vous feriez sans doute, sur un tableur, une feuille pour les mesures et une pour les informations sur les sondes. Mais imaginons qu’un fabricant vous annonce que les sondes livrées depuis deux mois ont un défaut et qu’il faut ajouter 0.5°C à toutes les mesures. Comment retrouver les mesures des sondes concernées ? Impossible, il faudrait retrouver les sondes une par une pour modifier leurs mesures. Les bases de données permettent de faire ça en une seule commande.
SQLite3
SQLite3 est un moteur de base de données portable, léger, dont le principal avantage est qu’il enregistre une base de données dans un seul et unique fichier, et qu’il ne nécessite pas de serveur, contrairement à MySQL, MariaDB ou Postgres.
L’inconvénient est qu’il est plutôt limité par rapport aux autres moteurs, mais ça suffit dans la plupart des cas pour une petite base personnelle.
Le fait qu’il n’y ait pas de serveur impose qu’un seul utilisateur peut modifier la base en même temps. Encore une fois, ce n’est pas un problème dans un usage personnel.
L’installation de SQLite3 est très facile sur la plupart des distributions GNU/Linux. Il suffit d’utiliser le gestionnaire de paquets.
Sur Ubuntu par exemple : sudo apt-get install sqlite3
Il suffit ensuite de lancer sqlite3 avec en paramètre le nom du fichier, qui sera créé s’il n’existe pas : sqlite3 garage.db
Il y a ensuite des commandes spéciales dans sqlite, qui commencent par un point. Vous pouvez consulter la liste en tapant .help. A retenir : -# .tables pour voir la liste des tables de la base -# .headers on|off pour afficher le nom des colonnes -# .mode MODE pour changer la façon d’afficher les résultats, et parmi les modes les plus courants : list, column et csv
Le garage
Comme premier exemple, on utilisera le garage.
On créera alors une table Clients, qui a pour colonnes : un numéro de client (un nombre unique), le nom, le prénom, l’adresse et l’adresse mail. On créera aussi une table Voitures, avec les colonnes id (nombre unique qui identifie une voiture), l’immatriculation, la marque, le modèle, le kilométrage.
On aurait pu imaginer utiliser l’immatriculation comme référence unique, mais ce n’est pas une bonne idée. Une vieille immatriculation peut changer vers le nouveau système. Vous pouvez avoir des clients étrangers, dont le système de garanti pas que le numéro ne sera pas réutilisé (c’est le cas de la Suisse, à 50 km d’ici). Dans presque tous les cas, on préfère utiliser un numéro neutre, créé exprès, qu’on nommera ‘id’ par convention.
Mais il faut maintenant représenter la relation entre un véhicule et un client, car l’un des usages de cette base sera évidemment de retrouver le propriétaire d’un véhicule ou les véhicules d’un client. Il faut réfléchir un peu à cette relation. Un client peut-il avoir plusieurs véhicules ? Oui. Un véhicule peut-il appartenir à plusieurs propriétaires ? Non. Nous aurons alors une relation 1->* (un client -> un nombre quelconque de véhicules. Dans ce cas, la meilleure solution est d’ajouter une colonne ‘propriétaire’ à la table Voitures, dans laquelle nous mettrons le numéro du client.
Voyons les commandes qui permettent de faire ça : create table clients (id int, nom text, prenom text, adresse text, email text); create table voitures (id int, immat text, marque text, modele text, km int, proprio int);
Pour chaque table, on précise le nom de chaque colonne et son type (int pour integer, c’est-à-dire entier). La colonne proprio de la table voitures doit correspondre à un id de client. Normalement, il faudrait indiquer à la base que cette contrainte doit être respectée, mais ça sort un peu d’une introduction. Voyez les liens en fin d’article pour aller plus loin.
Insérons ensuite des valeurs : insert into clients values (1, “Dupont”, “Pierre”, “annecy”, “p.dupont@aol.com”); insert into clients values (2, “Jouasse”, “Henriette”, “paris”, “jh@free.fr”); insert into voitures values (1, “AA123BB”, “Pigeot”, “308”, 65800, 1); insert into voitures values (2, “911 XV 74”, “Porsche”, “911”, 13600, 1); insert into voitures values (3, “CZ 318 GU”, “Renault”, “Aventis”, 500, 2);
Nous pouvons admirer le résultat en affichant les contenus des tables : select * from voitures;
1|AA123BB|Pigeot|308|65800|1 2|911 XV 74|Porsche|911|13600|1 3|CZ 318 GU|Renault|Aventis|500|2
.headers on .mode column select * from clients;
id nom prenom adresse email
1 Dupont Pierre annecy p.dupont@aol.com 2 Jouasse Henriette paris jh@free.fr
Pour supprimer une table, utiliser drop table ; et pour supprimer un enregistrement X utiliser delete from where id=X;. Attention, les instructions sont exécutées et enregistrée immédiatement. Pas de retour en arrière possible.
Les recherches
Les recherches se font avec le mot-clef select. On indique ensuite le nom des colonnes à afficher (ou * pour toutes), puis avec from, de quelle table on veut les données.
On peut faire des calculs dans une recherche. Par exemple, pour savoir dans combien de kilomètres les voitures vont devoir venir faire la révision des 75000km, on peut taper : select immat, modele, (75000 - km) as revision from voitures order by revision;
immat modele revision
AA123BB 308 9200
911 XV 74 911 61400
CZ 318 GU Aventis 74500
En passant, j’ai donné un nom au résultat du calcul, et j’ai demandé à la base de trier les résultats pour avoir en premier les voitures les plus proches de la révision.
Cette requête peut me permettre d’envoyer un rappel aux clients pour qu’ils viennent faire leur révision chez moi. Je dois donc retrouver le mail des clients propriétaires des voitures dont la révision est dans moins de 10000 km.
Je vais essayer d’ajouter la table clients : select immat, modele, (75000 - km) as revision, email from voitures, clients order by revision;
immat modele revision email
AA123BB 308 9200 p.dupont@aol.com AA123BB 308 9200 jh@free.fr
911 XV 74 911 61400 p.dupont@aol.com 911 XV 74 911 61400 jh@free.fr
CZ 318 GU Aventis 74500 p.dupont@aol.com CZ 318 GU Aventis 74500 jh@free.fr
On voit que la base a fait quelque chose de déroutant au premier abord : elle a croisé tous les enregsitrements des deux tables. Par exemple, la 308 sort deux fois, une fois pour Pierre, une fois pour Henriette. En effet, j’ai oublié de lui dire comment faire correspondre les voitures et leur propriétaire. Ajoutons un filtre avec la clause where : select immat, modele, (75000 - km) as revision, email from voitures, clients where voitures.proprio=clients.id order by revision;
immat modele revision
AA123BB 308 9200
911 XV 74 911 61400
CZ 318 GU Aventis 74500
C’est mieux non ? Les résultats sont filtrés pour n’avoir que ceux dont l’id du client correspond au numéro de proprio.
Si ce n’est pas clair, essayez d’afficher ces deux colonnes dans les résultats.
Le défi d’Olivier
Il s’agit d’écrire une requête pour créer une colonne ‘Trip’ dont la valeur sera la différence de ‘Km’ et du ‘Km’ immédiatement inférieur. Dans la table donnée en exemple les valeurs de ‘Km’ sont ordonnées mais ce ne sera pas toujours le cas.
Tout d’abord, nous allons visualiser les données présentes avec .headers on .tables select * from Tiger;
Nous avons deux colonnes, Essence et Km, dans la table Tiger. Comme il y a pas mal de données, nous allons copier une petite partie de la table dans une nouvelle, disons seulement 5 enregistrements. Ce sera plus facile pour travailler. Et en passant, pour des raisons de préférences personnelles, je vais renommer les colonnes.
Grâce au order by et au limit, nous récupérons les 5 premiers kilométrages.
La difficulté ensuite est que nous voulons utiliser deux fois une même colonne, mais pas pour le même enregistrement (la même ligne). En effet, quelque chose comme select e, k, k-k as Trip from t ne donnera rien, Trip sera toujours égal à 0 car le moteur fera la soustraction sur la même ligne, avec deux valeurs de k égales.
La technique consiste à utiliser deux fois la même table, pour créer toutes les combinaisons de k1 et k2 possibles, et de retrouver la bonne. Nous sommes obligés de renommer temporairement, pour la requête, les tables, sinon le moteur va se mélanger les pédales. Les noms des colonnes n’étant plus uniques, il faut aussi préciser pour chaque colonne de quelle table elle vient. Ça se note ..
On obtient bien 25 valeurs (5x5), et parmi celles-ci il y a les bonnes. Maintenant il faut les trouver et filtrer. Il y a plusieurs façon de voir les choses.
Solution 1
La première peut être de chercher à mettre en correspondance une ligne avec la ligne précédente. Olivier n’a pas mis de colonne id, mais sqlite3 en mets une par défaut, qui s’appelle rowid ou oid (c’est la même chose). Il n’est pas très conseillé de les utiliser, pour différentes raisons trop longues à expliquer ici, mais essayons quand même.
Ça ne marche pas trop mal. Il n’y a plus que quatre enregsitrements, puisqu’en faisons une soustraction nous perdons forcément une valeur (il y a toujours un interval de moins que de bornes).
Malheureusement, les oid (ou les id si Olivier en avait mis), ne changent pas si on ordonne la table. Si les données ne sont insérées dans l’ordre croissant des kilométrages, nous aurons des résultats faux. Et SQLite3 ne permet pas de numéroter les colonnes d’un résultat (contrairement à postgres ou mysql).
Pour contourner ça, nous pouvons utiliser une table temporaire. En la créant à partir des valeurs triées de la table d’origine, le moteur va numéroter les enregistrement comme nous le souhaitons. Exemple :
Une table temporaire n’existe que dans une session, et n’est pas écrite dans le fichier. Dès qu’on fermera sqlite3, la table temporaire sera effacée. C’est ce que nous voulons ici.
La solution 1 est donc un ensemble de requêtes :
Solution 2
Une autre approche est de se dire que, pour chaque kilométrage, la distance qui nous intéresse est la plus petite supérieure à 0.
Nous allons donc créer une sous-requête qui calcule les distances et garde celles supérieures à 0 : c’est la clause WITH, qui est nommée d.
Puis dans la requête principale nous allons sélectionner la plus petite distance dans cette sous-requête qui correspond au kilométrage de l’enregistrement. Enfin il faut faire correspondre le kilométrage de la distance avec le kilométrage de la table Tiger :
Pour aller plus loin…
Pour continuer, n’hésitez pas à consulter les documentations de sqlite ( https://www.sqlite.org/docs.html ) et de postgres (en français : https://docs.postgresql.fr/ ).
Mais la meilleure façon d’apprendre, c’est de pratiquer :) Trouvez une petite application pratique et concrète aux bases de données, et mettez-la en pratique. Vous n’en manquez sûrement pas. Et si vous avez du mal à trouver, désinstallez votre tableur, ça va venir tout seul.