Passons à la pratique et créons notre première base de données.
Installer SQLite
... sous Windows
Télécharger l'exécutable et la documentation sur la page download du site
officiel sqlite.org :
https://sqlite.org/download.html
Par exemple, sous Windows, sqlite-tools-win32-x86-3130000.zip ( juillet
2016 ) = A bundle of command-line tools for managing SQLite database files,
including the command-line shell program (="sqlite.exe"), the "sqldiff.exe" program, and
the "sqlite3_analyzer.exe" program
Par exemple, sqlite-doc-3130000.zip (juillet 2016) = Documentation as a
bundle of static HTML files.
Décompressez les deux fichiers.
La version 3.13 de l'exécutable - utilisée dans ce mini-cours-, pour Windows, se trouve
ici. La signature SHA-1 de l'exécutable téléchargable ici est
39472632a0b8cb3c596b03a50203c8b92541470b. Cet exécutable se présente sous une interface
console et contient un moteur de gestion de base de données.
La documentation en anglais, issue du site officiel, pour utiliser cet exécutable se
trouve ici
... sous Ubuntu
https://doc.ubuntu-fr.org/sqlite
- Press “Ctrl-Alt-T” to open a terminal window in Ubuntu.
- Type "sudo apt-get install sqlite3"
Exemple
Lecture des listes de paquets... Fait Construction de l'arbre des dépendances Lecture des informations d'état... Fait Les paquets suivants ont été installés automatiquement et ne sont plus nécessaires : libfprint-2-tod1 libllvm10 libllvm11 Veuillez utiliser « sudo apt autoremove » pour les supprimer. Paquets suggérés : sqlite3-doc Les NOUVEAUX paquets suivants seront installés : sqlite3 0 mis à jour, 1 nouvellement installés, 0 à enlever et 3 non mis à jour. Il est nécessaire de prendre 860 ko dans les archives. Après cette opération, 2.803 ko d'espace disque supplémentaires seront utilisés. Réception de :1 http://be.archive.ubuntu.com/ubuntu focal-updates/main amd64 sqlite3 amd64 3.31.1-4ubuntu0.2 [860 kB] 860 ko réceptionnés en 0s (2.923 ko/s) Sélection du paquet sqlite3 précédemment désélectionné. (Lecture de la base de données... 197675 fichiers et répertoires déjà installés.) Préparation du dépaquetage de .../sqlite3_3.31.1-4ubuntu0.2_amd64.deb ... Dépaquetage de sqlite3 (3.31.1-4ubuntu0.2) ... Paramétrage de sqlite3 (3.31.1-4ubuntu0.2) ... Traitement des actions différées (« triggers ») pour man-db (2.9.1-1) ...
Lancer et fermer SQLite
Avant de démarrer un moteur, une voiture, ... lancer un programme, il faut savoir comment
l'arrêter. Pour quitter le programme, il faudra taper, après le prompt
sqlite>
, .quit
ou .exit
, puis taper sur la
touche Enter
.
Le prompt est un terme informatique anglais que l'on peut traduire en français par invite de commande et qui indique que le programme est prêt à recevoir une saisie au clavier. Il se trouve toujours au début d'une ligne, est composé généralement du nom du programme et se termine souvent par le caractère > ( dans l'illustration ci-dessous, il est encadré en jaune ).
... sous Windows
Sous Windows, pour lancer le gestionnaire de bases de données SQLite, il suffit de double-cliquer sur l'exécutable sqlite3.exe. Au premier lancement, peut s'afficher le message suivant, émis par votre système d'exploitation :
Ce message anxiogène est affiché car, probablement, l'éditeur n'a pas compilé son programme via un compilateur Microsoft, ni payé une licence à Microsoft pour que ce message anxiogène n'apparaisse pas. Microsoft veille à la sécurité de ses utilisateurs et aussi, et surtout, à son chiffre d'affaire.
Si tel est le cas, décochez pour ne plus être ennuyé ultérieurement par l'affichage de ce message. S'affiche la console suivante :
Sous Windows, une console est une fenêtre d'invite de commande. Sous Linux, on parlera de terminal. On peut aussi l'appeler Interface en ligne de commande. La communication entre le programme et l'utilisateur se fait en mode texte.
La console ou le terminal peut être lancé dans le dossier contenant la base de donnée. (Recommandé)
Le texte affiché et les commandes à taper dans la console/terminal sont identiques sous Windows et sous Ubuntu.
... sous Ubuntu
Dans un terminal, lancer la commande suivante avant de taper les commandes propres à
SQLite :
sqlite3
(ne pas oublier le 3)
Pour fermer le programme, il suffit de taper .quit
:
Pour fermer la fenêtre, on peut cliquer sur la croix de fermeture. Cela entraînera la fermeture du programme. Mais, il est préférable de fermer le programme. Cela entraînera la fermeture de la fenêtre.
Pour obtenir la liste des dot-commands, il suffit de taper .help
dot signifie point. Les dot-commands sont les commandes qui commencent par point.
Il suffit de déplacer l'ascenseur droit de la fenêtre, pour lire toutes les dot-commands.
Les dot-commands ne sont pas des instructions SQL et ne se
terminent pas par un point-virgule. Ces commandes sont propres à SQLite. La commande
.exit
permet de quitter ce programme.
Avant de créer notre première table, il nous faut connaître les types de données de SQLite et les contraintes prévues par le SQL.
Les types de données de SQLite
SQLite ne connaît que 5 types de données :
- NULL = pas de donnée ...
- INTEGER = un nombre entier
- REAL = un nombre décimal ( avec point décimal )
- TEXT = une chaîne de caractères
- BLOB = une suite d'octets
Une colonne ne peut pas être déclarée de type NULL. Cela signifierait que la colonne ne peut contenir que des " pas de données ". Cela ne présente aucun intérêt.
Avec SQLite, les booléens doivent être codés comme en C : false ( = 0 ), true ( = 1 )
Avec SQLite, les dates doivent être codées soit comme une chaîne de caractères ayant le format suivant : "YYYY-MM-DD HH:MM:SS.SSS", soit comme un nombre entier positif de secondes depuis le 1er janvier 1970 à 0 heure, 0 minute, 0 seconde.
Compatibilité
Pour assurer une compatibilité avec les autres bases de données ( utilisant d'autres noms de type de données ), voici les règles appliquées :
Si le nom de type de données contient la chaîne "INT" - quelque soit la casse utilisée -, pour SQLite, le type sera INTEGER.
Si le nom de type de données contient la chaîne "CHAR", "CLOB" ou "TEXT" - quelque soit la casse utilisée -, pour SQLite, le type sera TEXT.
Les valeurs numériques indiquées entre parenthèses pour un type tel que varchar(15) seront ignorées par SQLite.
Si le nom de type de données n'est pas indiqué ou contient la chaîne "BLOB" - quelque soit la casse utilisée -, pour SQLite, le type sera BLOB.
Si le nom de type de données contient la chaîne "REAL", "FLOA", or "DOUB" - quelque soit la casse utilisée -, pour SQLite, le type sera REAL.
SQL : les contraintes
Les contraintes sont importantes car elles autorisent la base de données à refuser l'insertion, la modification ou la suppression d'un enregistrement si on tente de violer une de ces contraintes. Ces contraintes sont fixées par le DB administrator. La validité des données n'est pas soumise à la qualité de l'application qui alimente la base de données. Le DB administrator est placé au dessus du programmer
![](column-def.gif)
![](column-constraint.gif)
Principales contraintes :
- PRIMARY KEY
- UNIQUE
- NOT NULL
- FOREIGN KEY
PRIMARY KEY
Cette contrainte impose que, dans cette colonne, toute donnée existe et soit unique. En pratique, ce champ est quasi toujours de type INTEGER.
id_fiche INTEGER PRIMARY KEY,
Pour SQLITE, lorsqu'un champ est de type INTEGER et a comme contrainte primary key, il est aussi d'office autoincrement. Ceci permet au moteur de la base de données de choisir lui-même le nombre entier positif de la colonne, lors de l'insertion d'une nouvelle ligne. Ce nombre sera le dernier nombre attribué + 1. Soit 1 si la table est vide.
AUTOINCREMENT
- The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
- In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
- On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.
- If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
UNIQUE
-
Si la contrainte d'unité est appliqué au champ, elle est écrite dans la définition du champ.
MonChamp TEXT UNIQUE,
Un champ sur lequel est appliqué la contrainte PRIMARY KEY est alors d'office UNIQUE
-
Une contrainte d'unicité peut s'appliquer sur un plusieurs champs. Dans ce cas, elle est écrite a la fin des déclarations des champs.
UNIQUE (MonChamp1,MonChamp2),
NOT NULL
Cette contrainte interdit l'absence de données dans cette colonne.
Un champ sur lequel est appliqué la contrainte PRIMARY KEY ou UNIQUE est alors d'office NOT NULL
A unique constraint is satisfied if and only if
The following statement from SQL92 : A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.
That statement is ambiguous, having at least two possible interpretations :
- A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns.
- A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are NOT NULL.
SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does.
FOREIGN KEY
Cette contrainte est difficile à comprendre, mais elle est très importante. Il faudra la connaître. Vous serez probablement amené à relire cette section plusieurs fois.
Cette contrainte permet à deux tables ( principale et secondaire ) d'être cohérentes.
La table principale est généralement celle qui contient plusieurs colonnes sur lesquelles on effectue des tris.
Cette contrainte est écrite à la fin de la déclaration des colonnes de la table principale, comme ceci :
Syntaxe :
FOREIGN KEY(colonne1) REFERENCES table2(colonne2)
Exemple :
CREATE TABLE contacts( id_contact INTEGER PRIMARY KEY, nom TEXT NOT NULL, prenom TEXT, rue TEXT, num_localite INTEGER, dateAnni TEXT, FOREIGN KEY(num_localite) REFERENCES localites(id_localite) );
Il est logique de créer la table
externe (ici, localites) avant de créer la table interne (ici, contacts)
- colonne1 est le nom de la colonne de la table principale qui est généralement de type INTEGER et dont le nombre correspond à une chaîne de caractères dans la table secondaire. colonne1 n'a jamais la contrainte PRIMARY KEY.
- table2 est le nom de la table secondaire ( qui ne contient généralement que deux colonnes )
- colonne2 est le nom de la colonne de la table secondaire ( dont le nom débute généralement par id_et a généralement la contrainte PRIMARY KEY )
Les deux colonnes doivent être du même type de données.
En pratique, colonne1 et colonne2 sont de type INTEGER.
Pour comprendre, prenons un exemple.
La table principale " contacts " contient un colonne nommée " num_localite " qui contient le nombre entier positif. Par exemple, 17 correspondant à BRUXELLES dans la table " localites "
La table secondaire " localites " contient deux colonnes. La première se nomme id_localite est de type INTEGER, a la contrainte PRIMARY KEY. La seconde colonne se nomme "nom_localite". Dans cette table, 17 correspond à BRUXELLES.
Utilité de cette contrainte
Sans cette contrainte FOREIGN KEY sur la colonne num_localite de la table principale, lors de l'insertion d'une nouvelle ligne dans cette table principale, on pourrait mettre dans la colonne num_localite un nombre qui ne correspond à aucune localité.
Sans cette contrainte, on pourrait modifier une ligne de la table principale en remplaçant le nombre actuel de la colonne num_localite par un nombre qui n'existe pas dans la table secondaire.
Notez que cette contrainte n'interdit pas de supprimer des lignes dans la table principale, ni d'ajouter des lignes dans la table secondaire.
Mais, cette contrainte interdit de supprimer ou modifier une ligne de la table secondaire, si elle contient un nombre utilisé dans la table principale.
Bref, la contrainte FOREIGN KEY garantit la cohérence des données entre les deux tables.
Une table principale peut être reliée à plusieurs tables secondaires.
Une table secondaire peut être reliée à plusieurs tables principales.
SQL : CREATE TABLE ... ;
L'instruction CREATE TABLE ... ; est la première instruction SQL majeure.
CREATE TABLE localites(id smallint, nom varchar(25));
Ci-dessus, une instruction SQL qui crée une table nommée localites, qui contient deux colonnes. La première se nommera id et la seconde se nommera nom. Le type de données de la première colonne est smallint, celui de la seconde colonne est varchar(25)
Toutefois, compte tenu des règles de compatibilité - énoncées ci-dessus - cette instruction est traduite en :
CREATE TABLE localites(id INTEGER, nom TEXT);
Remarquez que, comme dans la plupart des langages de programmation, l'instruction se termine par un point-virgule.
Après le prompt sqlite>, tapez l'instruction ci-dessus ( ou faites un copier/coller ) puis tapez sur la touche Enter.
Félicitations ! Vous venez de créer votre première table. Pour le vérifier, tapez,
après l'invite de commande, .tables
( puis tapez sur la touche
Enter ), pour lister les tables contenues dans la base de données.
Voici le résultat :
Le programme affiche : localites
Syntaxe simplifiée pour créer une table :
CREATE TABLE nom_table(nom_colonne1 type_de_données, nom_colonne2 type_de_données, ...);
Syntaxe complète pour créer une table :
Ajoutons une autre table.
CREATE TABLE contacts(nom varchar(15), prenom varchar(15), rue varchar(40), codePostal varchar(4), localite smallint, dateAnniversaire varchar(10));
Il n'est pas obligatoire d'écrire l'instruction sur une seule ligne.
Mais, il est obligatoire de terminer cette instruction par un point-virgule.
Comme d'habitude, pour éviter tout problème, le nom d'une table ou d'un champ ( colonne ) ne contiendra aucun caractère accentué, spécial ou espace. De plus, ce nom ne sera pas un nom réservé pour ce type de base de données ( ni pour d'autres types de base de données, au cas où cette base de données serait exportée )
Dans le code source d'un programme, pour distinguer plus rapidement dans une requête
SQL les noms, ceux des tables et colonnes sont écrits en minuscules; et, ceux des noms
réservés en majuscules.
Notez qu'en ligne de commande, pour écrire plus vite, les mots réservés sont tapés en
minuscules.
Table names that begin with "sqlite_" are reserved for internal use.
Voir cet extrait de la documentation
officielle.
Pour constater la création de la seconde table, tapez, après l'invite de
commande, .tables
SQL : DROP TABLE ... ;
L'instruction DROP TABLE ... ; est la seconde instruction SQL majeure.
Syntaxe pour supprimer une table :
DROP TABLE nom_table;
Pour supprimer une table, il n'existe pas de syntaxe complexe.
Attention. La table et toutes ses données seront supprimées sans avertissement !
Supprimons la table localites et vérifions sa suppression en tapant
.tables
Vous commencez à bien utiliser SQLite. Mais, nous n'allons pas enregistrer ces tables.
Vous pouvez taper .exit
pour quitter SQLite. Demain, nous créerons de
"vraies" tables ( avec des contraintes ), nous les remplirons et les
enregistrerons.
SQL : ALTER TABLE ... ;
![](alter-table-stmt.gif)
L'instruction ALTER TABLE ... ; est la troisième instruction SQL majeure. Dans le cas de SQLITE, cette instruction est limitée à :
-
Renommer une table
-
Renommer une colonne
-
Ajouter une colonne (à la fin de la table)
ALTER TABLE conteneurs ADD COLUMN emplacement TEXT;
On ne peut pas ajouter la contrainte NOT NULL, lorsque la table contient déjà des enregistrements, car lors de l'ajout de cette colonne, les valeurs seront toutes NULL. Cette contrainte, NOT NULL, pourra être rajoutée après avoir donné une valeur au champ ajouté pour tous les enregistrements.
Après la création d'une table, il n'est pas possible d'ajouter une contrainte (UNIQUE, NOT NULL, ...)
Corriger une table
SQLite has limited ALTER TABLE support that you can use to add a column to the end of a table or to change the name of a table. If you want to make more complex changes in the structure of a table, you will have to recreate the table. You can save existing data to a temporary table, drop the old table, create the new table, then copy the data back in from the temporary table.
Exemple : ajout d'un NOT NULL
NB : Les requêtes SQL doivent tenir en une ligne. 1. Créer une table temporaire CREATE TABLE conteneurs2 (id_conteneur INTEGER PRIMARY KEY, nomConteneur TEXT NOT NULL UNIQUE, isRempli TEXT NOT NULL DEFAULT "N", emplacement TEXT NOT NULL ); 2. Remplir la table temporaire NSERT INTO conteneurs2 ( id_conteneur, nomConteneur, isRempli, emplacement ) SELECT id_conteneur, nomConteneur, isRempli, emplacement FROM conteneurs; 3. Vérifier qu'elle a bien été remplie SELECT * FROM conteneurs2; 4. Supprimer la table source (et pas la table temporaire !) DROP TABLE conteneurs; 5. Renommer la table temporaire par le nom de la table d'origine ALTER TABLE conteneurs2 RENAME TO conteneurs;
Exemple : ajout d'une contrainte d'unicité
SQLITE ne permet pas de modifier la structure de la base de données, de manière complexe. Ne peut pas être exécutée, la requête suivante :
ALTER TABLE fiches ADD CONSTRAINT position UNIQUE (num_fard,numPosition);
Une nouvelle table devra être créé par une requête SQL contenant :
CREATE TABLE fiches2 ( id_fiche INTEGER PRIMARY KEY, num_fard INTEGER, numPosition INTEGER NOT NULL, description TEXT NOT NULL DEFAULT "", num_typDoc INTEGER, num_producteur INTEGER, num_destinataire INTEGER, dateProduct NOT NULL DEFAULT CURRENT_DATE, motCles TEXT NOT NULL DEFAULT "", dateModifFich NOT NULL DEFAULT CURRENT_TIMESTAMP, dateDelete NOT NULL DEFAULT "2999-12-31", UNIQUE (num_fard,numPosition), FOREIGN KEY(num_fard) REFERENCES conteneurs(id_conteneur), FOREIGN KEY(num_typDoc) REFERENCES typDoc(id_typDoc), FOREIGN KEY(num_producteur) REFERENCES personnes(id_personne), FOREIGN KEY(num_destinataire) REFERENCES personnes(id_personne) );
Puis, réinjecter les données de la l'ancienne table,
INSERT INTO fiches2 ( id_fiche, num_fard, numPosition, num_typDoc, num_producteur, num_destinataire, dateProduct, dateModifFich, dateDelete, motCles, description ) SELECT id_fiche, num_fard, numPosition, num_typDoc, num_producteur, num_destinataire, dateProduct, dateModifFich, dateDelete, motCles, description FROM fiches ;
C'est l'occasion de modifier l'ordre des champs. Toutefois, les champs (dans l'INSERT et dans le SELECT) doivent correspondre en nombre, en type et être dans le même ordre.
Si l'ancienne table contient des enregistrements qui ne respectent la nouvelle contrainte, une erreur se produit.
Error: UNIQUE constraint failed: fiches2.id_fiche
Il convient alors de modifier (ou de supprimer) les enregistrements causant cette erreur, dans l'ancienne table.
Puis, de vérifier ce transfert (via un SELECT id_fiche FROM fiches2 WHERE
id_fiche>850;
), puis de supprimer l'ancienne table (DROP TABLE
fiches;
) et, enfin renommer la nouvelle table du nom de l'ancienne ( ALTER
TABLE fiches2 RENAME TO fiches;
).