SQLite - PRAGMA : Un guide complet pour les débutants

Salut à toi, enthousiaste de la base de données ! Aujourd'hui, nous allons entreprendre un voyage passionnant dans le monde des PRAGMAs de SQLite. Ne t'inquiète pas si tu n'avais jamais entendu parler de PRAGMAs auparavant - à la fin de ce tutoriel, tu les utiliseras comme un pro !

Qu'est-ce qu'un PRAGMA ?

Avant de plonger dedans, penchons-nous sur les bases. Dans SQLite, un PRAGMA est une commande spéciale qui nous permet de modifier le comportement de la bibliothèque SQLite ou d'interroger son état interne. Pense aux PRAGMAs comme au panneau de contrôle de ta base de données SQLite - ils te permettent de régler les paramètres et d'obtenir des informations sur le fonctionnement de ta base de données.

Maintenant, explorons certains des PRAGMAs les plus couramment utilisés dans SQLite.

PRAGMA auto_vacuum

Le PRAGMA auto_vacuum est comme avoir un colocataire maniaque pour ta base de données. Il nettoie et organise automatiquement le fichier de la base de données lorsque tu supprimes des données.

PRAGMA auto_vacuum = FULL;

Cette commande règle le mode auto_vacuum sur FULL, ce qui signifie que SQLite fera tout son possible pour maintenir ton fichier de base de données aussi petit que possible.

PRAGMA cache_size

Le PRAGMA cache_size est comme donner à ta base de données un plus grand cerveau. Il détermine le nombre de pages que SQLite garde en mémoire pour un accès plus rapide.

PRAGMA cache_size = 10000;

Cette commande règle la taille du cache à 10 000 pages. Plus de cache signifie une performance plus rapide, mais cela utilise également plus de mémoire.

PRAGMA case_sensitive_like

Ce PRAGMA détermine si l'opérateur LIKE est sensible à la casse ou non. C'est comme enseigner à ta base de données à lire avec ou sans ses lunettes.

PRAGMA case_sensitive_like = TRUE;

Avec ce paramètre, 'Apple' LIKE 'app%' serait faux, tandis que 'Apple' LIKE 'App%' serait vrai.

PRAGMA count_changes

Le PRAGMA count_changes est comme un compteur de points pour tes opérations de base de données. Il te dit combien de lignes ont été modifiées par une instruction INSERT, UPDATE ou DELETE.

PRAGMA count_changes = ON;

Après avoir défini cela, lorsque tu exécuteras une instruction UPDATE, par exemple, SQLite te dira combien de lignes ont été affectées.

PRAGMA database_list

Ce PRAGMA est comme un annuaire pour ta base de données. Il liste toutes les bases de données attachées, y compris la base principale et toute autre que tu as connectée.

PRAGMA database_list;

Cela te montrera une liste de toutes les bases de données attachées, leurs chemins de fichiers et leurs noms internes.

PRAGMA encoding

Le PRAGMA encoding règle ou interroge l'encodage de texte utilisé par la base de données. C'est comme choisir dans quelle langue ta base de données parle.

PRAGMA encoding = "UTF-8";

Cela règle l'encodage de la base de données sur UTF-8, ce qui est un choix commun pour prendre en charge plusieurs langues.

PRAGMA freelist_count

Ce PRAGMA te dit combien de pages inutilisées se trouvent dans le fichier de la base de données. C'est comme vérifier combien d'espace libre tu as dans ton unité de stockage.

PRAGMA freelist_count;

Cela renverra le nombre de pages inutilisées dans le fichier de la base de données.

PRAGMA index_info et PRAGMA index_list

Ces PRAGMAs sont comme avoir une bibliothécaire pour tes indexes de base de données. Ils fournissent des informations sur la structure et le contenu de tes indexes.

PRAGMA index_list(nom_de_table);
PRAGMA index_info(nom_d'index);

La première commande liste tous les indexes sur une table, tandis que la seconde donne des informations détaillées sur un index spécifique.

PRAGMA journal_mode

Le PRAGMA journal_mode contrôle comment SQLite suit les transactions. C'est comme choisir entre différents types de systèmes de sauvegarde.

PRAGMA journal_mode = WAL;

Cela règle le mode journal sur Write-Ahead Logging (WAL), ce qui peut améliorer les performances dans de nombreux cas.

PRAGMA max_page_count et PRAGMA page_count

Ces PRAGMAs traitent du nombre de pages dans ton fichier de base de données. Ils sont comme régler et vérifier le nombre maximum de pages dans un livre.

PRAGMA max_page_count = 1000000;
PRAGMA page_count;

La première commande règle le nombre maximum de pages, tandis que la seconde te dit combien de pages sont actuellement en usage.

PRAGMA page_size

Ce PRAGMA règle ou interroge la taille de page de la base de données. C'est comme décider combien de place chaque page dans ton livre devrait avoir.

PRAGMA page_size = 4096;

Cela règle la taille de page à 4096 octets, ce qui est un choix commun.

PRAGMA parser_trace et PRAGMA sql_trace

Ces PRAGMAs sont comme activer le mode de débogage pour ta base de données. Ils fournissent des informations détaillées sur la façon dont SQLite traite tes commandes.

PRAGMA parser_trace = ON;
PRAGMA sql_trace = ON;

Ces commandes peuvent être très utiles lorsque tu essaies de comprendre pourquoi une requête particulière ne fonctionne pas comme prévu.

PRAGMA recursive_triggers

Ce PRAGMA contrôle si les triggers peuvent déclencher récursivement d'autres triggers. C'est comme permettre des effets domino dans ta base de données.

PRAGMA recursive_triggers = ON;

Avec ce paramètre, un trigger peut provoquer un autre trigger à se déclencher, ce qui pourrait provoquer un autre trigger, et ainsi de suite.

PRAGMA schema_version et PRAGMA user_version

Ces PRAGMAs te permettent de régler et d'interroger les numéros de version pour ton schéma de base de données. Ils sont comme des étiquettes de version pour ta structure de base de données.

PRAGMA schema_version;
PRAGMA user_version = 1;

La première commande interroge la version actuelle du schéma, tandis que la seconde règle la version utilisateur à 1.

PRAGMA secure_delete

Ce PRAGMA contrôle combien de fois SQLite écrase les données supprimées. C'est comme choisir entre déchiqueter tes documents ou les jeter à la poubelle.

PRAGMA secure_delete = ON;

Avec ce paramètre, SQLite écrasera les données supprimées avec des zéros, rendant plus difficile leur récupération.

PRAGMA synchronous

Le PRAGMA synchronous contrôle combien soigneusement SQLite écrit les données sur le disque. C'est un équilibre entre la sécurité et la vitesse.

PRAGMA synchronous = NORMAL;

Cela règle un niveau modéré de sécurité, équilibrant entre la pleine sécurité (FULL) et la vitesse maximale (OFF).

PRAGMA temp_store et PRAGMA temp_store_directory

Ces PRAGMAs contrôlent où et comment SQLite stocke les données temporaires. C'est comme choisir où mettre ton papier brouillon pendant que tu travailles.

PRAGMA temp_store = MEMORY;
PRAGMA temp_store_directory = '/chemin/vers/le/répertoire';

La première commande dit à SQLite de stocker les données temporaires en mémoire, tandis que la seconde spécifie un répertoire pour les fichiers temporaires.

PRAGMA writable_schema

Ce PRAGMA contrôle si tu peux modifier directement la table sqlite_master. C'est comme avoir une clé maîtresse pour ta structure de base de données.

PRAGMA writable_schema = ON;

Sois très prudent avec celui-là ! Il te permet de changer la structure fondamentale de ta base de données, ce qui peut être dangereux si ce n'est pas fait correctement.

Voici un tableau résumant tous les PRAGMAs que nous avons discutés :

PRAGMA	Description
auto_vacuum	Contrôle le nettoyage automatique de la base de données
cache_size	Définit le nombre de pages à conserver en mémoire
case_sensitive_like	Contrôle la sensibilité à la casse de l'opérateur LIKE
count_changes	Signale le nombre de lignes modifiées par les opérations
database_list	Liste les bases de données attachées
encoding	Définit ou interroge l'encodage de texte de la base de données
freelist_count	Signale le nombre de pages inutilisées dans la base de données
index_info	Fournit des informations sur un index spécifique
index_list	Liste tous les indexes sur une table
journal_mode	Contrôle comment les transactions sont suivies
max_page_count	Définit le nombre maximum de pages dans la base de données
page_count	Signale le nombre actuel de pages dans la base de données
page_size	Définit ou interroge la taille de page de la base de données
parser_trace	Active les informations détaillées de parsing
recursive_triggers	Permet aux triggers de déclencher récursivement d'autres triggers
schema_version	Définit ou interroge le numéro de version du schéma
secure_delete	Contrôle l'effacement des données supprimées
sql_trace	Active les informations détaillées d'exécution SQL
synchronous	Contrôle soigneusement les données sont écrites sur le disque
temp_store	Contrôle où les données temporaires sont stockées
temp_store_directory	Définit le répertoire pour les fichiers temporaires
user_version	Définit ou interroge le numéro de version utilisateur
writable_schema	Permet la modification directe de la table sqlite_master

Et voilà ! Tu es maintenant bien équipé pour commencer à expérimenter avec les PRAGMAs de SQLite. Souviens-toi, ces outils puissants peuvent affecter significativement le comportement et les performances de ta base de données, donc toujours tester soigneusement avant de les utiliser en environnement de production. Bon codage !

Credits: Image by storyset