Gestion des transactions SQLite

Introduction

Dans ce labo, nous explorerons la gestion des transactions SQLite, en nous concentrant sur le maintien de l'intégrité des données grâce à l'utilisation de transactions. Nous apprendrons comment démarrer et valider (commit) des transactions, en veillant à ce que plusieurs opérations connexes soient traitées comme une seule unité atomique.

Ce labo vous guidera à travers le processus de démarrage d'une transaction en utilisant BEGIN TRANSACTION, d'apport de modifications à la base de données, puis de sauvegarde permanente de ces modifications à l'aide de l'instruction COMMIT. Vous apprendrez également comment annuler les modifications à l'aide de ROLLBACK et comment utiliser SAVEPOINT pour un contrôle plus granulaire. Cette expérience pratique renforcera votre compréhension de la manière dont les transactions garantissent l'atomicité, la cohérence, l'isolation et la durabilité (propriétés ACID) dans SQLite.

Créer une base de données et une table

Dans cette première étape, nous allons créer une base de données SQLite et une table pour stocker les données des utilisateurs. Cela fournira la base pour explorer la gestion des transactions dans les étapes suivantes.

Tout d'abord, ouvrez votre terminal dans la VM LabEx. Votre chemin d'accès par défaut est /home/labex/project.

Maintenant, créons une base de données SQLite nommée mydatabase.db. Exécutez la commande suivante pour créer le fichier de base de données et ouvrir l'outil de ligne de commande SQLite :

sqlite3 mydatabase.db

Vous verrez une invite indiquant que vous êtes maintenant à l'intérieur du shell SQLite :

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Ensuite, créez une table nommée users pour stocker les informations de base des utilisateurs. Cette table aura trois colonnes : id (un identifiant unique), name et balance. Entrez la commande SQL suivante à l'invite sqlite> et appuyez sur Entrée :

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

Cette commande configure la table users où :

• id est un entier qui sert de clé primaire (primary key) pour chaque utilisateur.
• name est un champ texte représentant le nom de l'utilisateur.
• balance est un nombre réel représentant le solde du compte de l'utilisateur.

Maintenant, insérez des données initiales dans la table users :

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

Ces commandes ajoutent deux utilisateurs, Alice et Bob, avec des soldes initiaux de 100.0 et 200.0, respectivement.

Pour confirmer que les données ont été ajoutées correctement, exécutez cette commande pour afficher tous les enregistrements de la table :

SELECT * FROM users;

Expected Output:

1|Alice|100.0
2|Bob|200.0

Cette sortie affiche l'id, le name et le balance pour chaque enregistrement. La commande SELECT * récupère toutes les colonnes de la table spécifiée.

Démarrer et valider (Commit) une transaction

Dans cette étape, vous apprendrez à démarrer et à valider (commit) une transaction. Les transactions sont essentielles pour garantir l'intégrité des données lors de l'exécution de plusieurs opérations connexes.

Pour démarrer une transaction, utilisez la commande BEGIN TRANSACTION :

BEGIN TRANSACTION;

Cette commande indique à SQLite de commencer à suivre les modifications, mais de ne pas les appliquer de manière permanente à la base de données tant que vous n'avez pas explicitement validé (commit) la transaction.

Maintenant, transférons 50.0 d'Alice à Bob :

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

Ces commandes mettent à jour les soldes d'Alice et de Bob au sein de la transaction. Le solde d'Alice est diminué de 50.0 et le solde de Bob est augmenté de 50.0.

Pour enregistrer les modifications, utilisez la commande COMMIT :

COMMIT;

Cette commande valide (commit) la transaction, rendant les modifications permanentes.

Vérifiez les modifications en interrogeant à nouveau la table :

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

Cette sortie confirme que la transaction a été validée (commit) avec succès et que les soldes d'Alice et de Bob ont été mis à jour en conséquence.

Annuler (Rollback) une transaction

Dans cette étape, vous apprendrez à utiliser la commande ROLLBACK pour annuler les modifications apportées lors d'une transaction. Ceci est essentiel pour gérer les erreurs et garantir l'intégrité des données.

Tout d'abord, commencez une nouvelle transaction :

BEGIN TRANSACTION;

Maintenant, essayons de transférer 100.0 d'Alice à Bob, mais cette fois, nous allons simuler une condition d'erreur :

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

Étant donné qu'Alice n'a que 50.0, la première mise à jour entraînera un solde négatif. Bien que SQLite n'applique pas de contraintes par défaut, nous simulerons une erreur se produisant après cette opération.

Pour annuler les modifications, utilisez la commande ROLLBACK :

ROLLBACK;

Cette commande annule (rollback) la transaction, annulant toutes les modifications apportées depuis l'instruction BEGIN TRANSACTION.

Vérifiez que les modifications ont été annulées en interrogeant la table users :

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

Cette sortie confirme que la transaction a été annulée (rollback) avec succès et que les soldes d'Alice et de Bob restent inchangés.

Implémenter des points de sauvegarde (Savepoints)

Dans cette étape, vous apprendrez à utiliser les points de sauvegarde (savepoints) au sein des transactions. Les points de sauvegarde vous permettent de créer des points intermédiaires dans une transaction vers lesquels vous pouvez effectuer une restauration (rollback), sans restaurer l'intégralité de la transaction.

Tout d'abord, commencez une nouvelle transaction :

BEGIN TRANSACTION;

Créez un point de sauvegarde nommé savepoint1 :

SAVEPOINT savepoint1;

Maintenant, transférons 20.0 d'Alice à Bob :

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

Créez un autre point de sauvegarde nommé savepoint2 :

SAVEPOINT savepoint2;

Ajoutons un nouvel utilisateur nommé 'Charlie' avec un solde initial de 300.0 :

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

Maintenant, disons que nous décidons que l'ajout de Charlie était une erreur. Nous pouvons effectuer une restauration (rollback) vers savepoint1, ce qui annulera l'instruction INSERT et le transfert de 20.0 entre Alice et Bob :

ROLLBACK TO SAVEPOINT savepoint1;

Vérifiez les modifications après la restauration (rollback) vers savepoint1 :

SELECT * FROM users;

Expected Output:

1|Alice|50.0
2|Bob|250.0

Vous devriez voir que Charlie n'est pas dans la table, et que les soldes d'Alice et de Bob sont revenus à leurs valeurs avant savepoint1.

Maintenant, transférons 10.0 d'Alice à Bob :

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

Enfin, validez (commit) la transaction :

COMMIT;

Ajouter une contrainte pour empêcher les soldes négatifs

Dans cette étape, vous allez ajouter une contrainte à la table users pour empêcher les soldes négatifs. Cela permettra d'assurer l'intégrité des données en empêchant les transactions invalides.

Puisque SQLite ne prend pas en charge l'ajout de contraintes aux tables existantes en utilisant ALTER TABLE ADD CONSTRAINT, nous devons recréer la table avec la contrainte. Commencez d'abord une transaction pour garantir que toutes les opérations sont atomiques :

BEGIN TRANSACTION;

Créez une nouvelle table avec la même structure mais incluant une contrainte CHECK :

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL CHECK (balance >= 0)
);

Copiez toutes les données de l'ancienne table dans la nouvelle table :

INSERT INTO users_new SELECT * FROM users;

Supprimez l'ancienne table :

DROP TABLE users;

Renommez la nouvelle table avec le nom d'origine :

ALTER TABLE users_new RENAME TO users;

Validez la transaction pour rendre les modifications permanentes :

COMMIT;

Maintenant, la table users possède une contrainte CHECK qui garantit que la colonne balance doit toujours être supérieure ou égale à 0.

Essayons de transférer 1000 de Alice à Bob, ce qui violera la contrainte :

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';

Cette fois, la déclaration UPDATE provoquera une erreur car elle viole la contrainte CHECK. Vous verrez un message d'erreur comme ceci : Erreur : Contrainte CHECK échouée : balance >= 0.

Annulez la transaction :

ROLLBACK;

Vérifiez que les modifications ont été annulées :

SELECT * FROM users;

Sortie attendue :

1|Alice|40.0
2|Bob|260.0

Ceci confirme que le ROLLBACK a bien remis la base de données à son état initial avant la transaction échouée.

Résumé

Dans ce labo, vous avez appris les principes fondamentaux de la gestion des transactions dans SQLite. Vous avez vu comment démarrer et valider (commit) des transactions, annuler (rollback) des modifications, implémenter des points de sauvegarde (savepoints) pour un contrôle plus précis, et ajouter des contraintes pour garantir l'intégrité des données. Ces compétences sont essentielles pour créer des applications de base de données robustes et fiables.