Optimisation des PRAGMA SQLite

Introduction

Dans ce laboratoire, vous explorerez le réglage de SQLite PRAGMA pour optimiser les performances et la fiabilité de la base de données. Vous apprendrez à configurer les aspects clés du comportement de SQLite à l'aide d'instructions PRAGMA. Nous aborderons la configuration du mode journal (journal mode), l'activation des vérifications de clés étrangères (foreign key checks), l'exécution de contrôles d'intégrité (integrity checks) et l'ajustement de la taille du cache (cache size).

Skills Graph

Créer une base de données et configurer le mode journal (Journal Mode)

Dans cette étape, vous allez créer une base de données SQLite et configurer son mode journal (journal mode). Le mode journal contrôle la manière dont SQLite gère les transactions et assure l'intégrité des données.

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

Créez une base de données SQLite nommée test.db et entrez dans l'interpréteur (shell) SQLite en utilisant la commande suivante :

sqlite3 test.db

Cette commande crée le fichier de base de données test.db (s'il n'existe pas) et ouvre l'outil de ligne de commande SQLite. Vous verrez une invite de commande comme celle-ci :

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

Maintenant, configurons le mode journal. SQLite propose plusieurs modes journal, notamment DELETE, TRUNCATE, PERSIST, MEMORY, WAL et OFF. WAL (Write-Ahead Logging) offre un bon équilibre entre performance et fiabilité.

Exécutez la commande SQL suivante pour définir le mode journal sur WAL :

PRAGMA journal_mode=WAL;

Cette commande configure la base de données pour utiliser Write-Ahead Logging. WAL améliore la concurrence et les performances en écrivant les modifications dans un fichier WAL distinct avant de les appliquer à la base de données.

Pour vérifier le mode journal, exécutez :

PRAGMA journal_mode;

Résultat attendu :

wal

Cela confirme que le mode journal est défini sur WAL.

Activer la prise en charge des clés étrangères (Foreign Key Support)

Dans cette étape, vous allez activer la prise en charge des clés étrangères dans votre base de données SQLite. Les clés étrangères appliquent les relations entre les tables et aident à maintenir l'intégrité des données.

Dans l'interpréteur SQLite (si vous l'avez quitté à l'étape précédente, reconnectez-vous avec sqlite3 test.db), exécutez la commande suivante :

PRAGMA foreign_keys = ON;

Cette commande active l'application des clés étrangères pour la connexion de base de données actuelle. Notez que vous devez activer les clés étrangères pour chaque nouvelle connexion à la base de données.

Pour vérifier que la prise en charge des clés étrangères est activée, exécutez :

PRAGMA foreign_keys;

Résultat attendu :

1

Ce résultat confirme que la prise en charge des clés étrangères est activée.

Créer des tables avec une relation de clé étrangère (Foreign Key Relationship)

Maintenant que la prise en charge des clés étrangères est activée, créons deux tables avec une relation de clé étrangère pour démontrer sa fonctionnalité.

Exécutez les commandes SQL suivantes pour créer une table users et une table orders :

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

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Ces commandes créent deux tables :

• users : Stocke les informations sur les utilisateurs avec les colonnes id (clé primaire) et name.
• orders : Stocke les informations sur les commandes avec les colonnes id (clé primaire), user_id (clé étrangère référençant users.id) et amount.

La clause FOREIGN KEY (user_id) REFERENCES users(id) établit une relation entre la table orders et la table users. Elle garantit que le user_id dans la table orders doit exister dans la colonne id de la table users.

Tester la contrainte de clé étrangère (Foreign Key Constraint)

Dans cette étape, vous allez tester la contrainte de clé étrangère pour voir comment elle empêche l'insertion de données invalides dans la base de données.

Tout d'abord, insérez un utilisateur dans la table users :

INSERT INTO users (id, name) VALUES (1, 'Alice');

Cette commande insère un nouvel utilisateur avec l'id 1 et le name 'Alice' dans la table users.

Maintenant, essayez d'insérer une commande dans la table orders avec un user_id qui n'existe pas dans la table users :

INSERT INTO orders (user_id, amount) VALUES (999, 100.0);

Étant donné que les vérifications de clé étrangère sont activées, cette commande échouera avec un message d'erreur :

Error: FOREIGN KEY constraint failed

Cela démontre que la contrainte de clé étrangère fonctionne correctement, vous empêchant de créer une commande pour un utilisateur inexistant.

Ensuite, insérez une commande valide avec le user_id existant :

INSERT INTO orders (user_id, amount) VALUES (1, 100.0);

Cette commande réussira car le user_id 1 existe dans la table users.

Effectuer une vérification d'intégrité (Integrity Check)

Dans cette étape, vous allez effectuer une vérification d'intégrité sur votre base de données SQLite pour vous assurer qu'il n'y a pas d'incohérences ou d'erreurs.

Exécutez la commande suivante dans l'interpréteur (shell) SQLite :

PRAGMA integrity_check;

Cette commande vérifie l'ensemble de la base de données pour détecter toute incohérence ou erreur. Si la base de données est saine, la sortie sera ok.

Résultat attendu :

ok

Si la base de données est corrompue, la sortie contiendra des messages d'erreur indiquant la nature et l'emplacement de la corruption.

Ajuster la taille du cache (Cache Size) et quitter

Dans cette dernière étape, vous allez ajuster la taille du cache utilisée par SQLite, puis quitter l'interpréteur (shell) SQLite.

La taille du cache détermine la quantité de mémoire que SQLite utilise pour stocker les données récemment consultées. Augmenter la taille du cache peut améliorer les performances, en particulier pour les charges de travail à forte lecture (read-heavy workloads).

Exécutez la commande suivante pour définir la taille du cache à 4 Mo (4096 Ko) :

PRAGMA cache_size = 4096;

Cette commande définit la taille du cache à 4 Mo pour la connexion de base de données actuelle.

Pour vérifier la taille du cache, exécutez :

PRAGMA cache_size;

Résultat attendu :

4096

Enfin, quittez l'interpréteur SQLite :

.exit

Cette commande ferme la connexion à la base de données et vous ramène à la ligne de commande.

Résumé

Dans ce labo, vous avez exploré le réglage (tuning) des PRAGMA de SQLite. Vous avez appris à configurer le mode de journalisation (journal mode) pour améliorer les performances et la fiabilité, à activer la prise en charge des clés étrangères (foreign key) pour garantir l'intégrité des données, à créer des tables avec des relations de clés étrangères, à tester les contraintes de clés étrangères, à effectuer des vérifications d'intégrité (integrity checks) pour identifier la corruption de la base de données et à ajuster la taille du cache (cache size) pour optimiser l'utilisation de la mémoire. Ces compétences fournissent une base solide pour travailler efficacement avec les bases de données SQLite.