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

1. Press “Ctrl-Alt-T” to open a terminal window in Ubuntu.
2. Type "sudo apt-get install sqlite3"

  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 :

1. NULL = pas de donnée ...
2. INTEGER = un nombre entier
3. REAL = un nombre décimal ( avec point décimal )
4. TEXT = une chaîne de caractères
5. 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 1^er janvier 1970 à 0 heure, 0 minute, 0 seconde.

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

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.

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

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

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

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, ...)

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;

ALTER TABLE fiches ADD CONSTRAINT position UNIQUE (num_fard,numPosition);

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

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
;

Error: UNIQUE constraint failed: fiches2.id_fiche