Passer au contenu
SQLite

Configuration de la base de données avec PRAGMA

Les PRAGMA de configuration vous permettent de définir les paramètres fondamentaux qui déterminent comment votre base de données SQLite fonctionne. Dans cette section, nous allons explorer les PRAGMA les plus utiles pour configurer votre base de données selon vos besoins spécifiques.

Encodage des caractères

PRAGMA encoding

L’encodage détermine comment les chaînes de caractères sont stockées dans la base de données. SQLite prend en charge trois encodages : UTF-8, UTF-16le et UTF-16be.

-- Consulter l'encodage actuel
PRAGMA encoding;


-- Définir l'encodage (uniquement avant de créer des tables)
PRAGMA encoding = 'UTF-8';

Note importante : L’encodage ne peut être modifié que sur une base de données vide, avant de créer des tables.

Modes de journalisation

PRAGMA journal_mode

Le mode de journalisation détermine comment SQLite assure la durabilité des données en cas de panne ou d’interruption.

-- Consulter le mode de journalisation actuel
PRAGMA journal_mode;


-- Définir le mode de journalisation
PRAGMA journal_mode = DELETE;  -- Mode par défaut
PRAGMA journal_mode = WAL;     -- Mode Write-Ahead Logging (souvent plus performant)
PRAGMA journal_mode = MEMORY;  -- Journal en mémoire (rapide mais moins sûr)
PRAGMA journal_mode = OFF;     -- Pas de journalisation (très risqué)

Les différents modes de journalisation

  • DELETE : Crée un fichier journal temporaire qui est supprimé à la fin de chaque transaction réussie.
  • TRUNCATE : Similaire à DELETE, mais tronque le fichier journal au lieu de le supprimer (plus rapide sur certains systèmes).
  • PERSIST : Garde le fichier journal mais le réinitialise après chaque transaction.
  • MEMORY : Stocke le journal en mémoire (plus rapide mais perdu si l’application se termine brutalement).
  • WAL (Write-Ahead Logging) : Écrit les changements dans un fichier séparé, permettant des lectures concurrentes pendant les écritures.
  • OFF : Désactive complètement la journalisation (dangereux, peut corrompre la base).

Le mode WAL est souvent recommandé pour les applications modernes car il offre un bon équilibre entre performance et sécurité.

PRAGMA journal_size_limit

Lorsque vous utilisez le mode WAL, vous pouvez limiter la taille du fichier journal :

-- Limiter la taille du fichier WAL à 1 Mo
PRAGMA journal_size_limit = 1048576;

Synchronisation avec le disque

PRAGMA synchronous

Ce PRAGMA contrôle la rigueur avec laquelle SQLite s’assure que les données sont écrites sur le disque.

-- Consulter le mode de synchronisation actuel
PRAGMA synchronous;


-- Définir le mode de synchronisation
PRAGMA synchronous = FULL;     -- Très sûr mais plus lent
PRAGMA synchronous = NORMAL;   -- Bon équilibre (par défaut)
PRAGMA synchronous = OFF;      -- Plus rapide mais risqué

Les niveaux de synchronisation

  • FULL (2) : SQLite attend que les données soient écrites sur le disque après chaque transaction.
  • NORMAL (1) : SQLite attend uniquement aux moments critiques.
  • OFF (0) : SQLite laisse le système d’exploitation gérer la synchronisation.

Auto-vacuum

PRAGMA auto_vacuum

Ce PRAGMA détermine comment l’espace libéré par les suppressions est géré.

-- Consulter le mode auto-vacuum actuel
PRAGMA auto_vacuum;


-- Définir le mode auto-vacuum
PRAGMA auto_vacuum = NONE;       -- Par défaut, ne libère pas l'espace
PRAGMA auto_vacuum = FULL;       -- Réorganise automatiquement la base
PRAGMA auto_vacuum = INCREMENTAL; -- Permet une réorganisation manuelle

Les modes d’auto-vacuum

  • NONE (0) : SQLite ne libère pas automatiquement l’espace du fichier de base de données.
  • FULL (1) : SQLite réorganise automatiquement la base pour libérer l’espace.
  • INCREMENTAL (2) : Permet d’utiliser le PRAGMA incremental_vacuum pour libérer progressivement l’espace.

PRAGMA incremental_vacuum

Si auto_vacuum est défini sur INCREMENTAL, vous pouvez libérer progressivement l’espace:

-- Libérer 10 pages
PRAGMA incremental_vacuum(10);

Taille de page

PRAGMA page_size

La taille de page peut avoir un impact significatif sur les performances et l’espace disque.

-- Consulter la taille de page actuelle
PRAGMA page_size;


-- Définir la taille de page (puissance de 2 entre 512 et 65536)
PRAGMA page_size = 4096;

Note importante : La taille de page ne peut être modifiée que sur une base de données vide ou après un VACUUM.

Identifiants d’application et de version

PRAGMA application_id

Ce PRAGMA permet de stocker un identifiant pour votre application dans la base de données.

-- Définir un identifiant d'application (entier 32 bits)
PRAGMA application_id = 0x12345678;

PRAGMA user_version

Permet de stocker un numéro de version défini par l’utilisateur :

-- Définir une version utilisateur
PRAGMA user_version = 42;

Ce PRAGMA est utile pour suivre les versions de schéma de base de données dans vos applications.

Cas pratique : Configuration d’une base de données optimisée pour un site web

Voici un exemple complet de configuration pour une base de données SQLite utilisée dans une application web :

-- Configurer l'encodage (sur une base de données vide)
PRAGMA encoding = 'UTF-8';


-- Utiliser le mode WAL pour de meilleures performances
PRAGMA journal_mode = WAL;
PRAGMA journal_size_limit = 5242880;  -- 5 Mo max pour le fichier WAL


-- Bon équilibre entre sécurité et performances
PRAGMA synchronous = NORMAL;


-- Gérer l'espace disque
PRAGMA auto_vacuum = INCREMENTAL;


-- Taille de page optimale pour la plupart des cas
PRAGMA page_size = 4096;


-- Identifiants pour la base de données
PRAGMA application_id = 0x12345678;  -- ID hexadécimal unique
PRAGMA user_version = 1;            -- Version du schéma

Bonnes pratiques pour la configuration

Lors de la configuration de votre base de données SQLite, gardez ces principes à l’esprit :

  1. Équilibrez performances et sécurité : Des paramètres comme synchronous=OFF peuvent améliorer les performances mais augmenter les risques.

  2. Configurez tôt : Certains PRAGMA comme encoding et page_size ne peuvent être modifiés qu’avant de créer des tables.

  3. Testez votre configuration : Les paramètres optimaux dépendent de votre cas d’utilisation spécifique.

  4. Documentez vos choix : Les PRAGMA de configuration peuvent affecter le comportement à long terme de votre base de données.

  5. Considérez le mode WAL : Le mode WAL offre souvent un bon équilibre entre performance et sécurité pour les applications modernes.

Dans la section suivante, nous explorerons comment utiliser les PRAGMA pour optimiser les performances de votre base de données SQLite.