Passer au contenu
SQLite

PRAGMA en pratique : Exemples concrets

Maintenant que nous avons exploré les différentes catégories de PRAGMA dans SQLite, voyons comment les utiliser dans des scénarios réels. Cette section présente des exemples pratiques qui illustrent comment les PRAGMA peuvent résoudre des problèmes concrets et améliorer vos applications SQLite.

Scénario 1 : Optimisation d’une application mobile

Les applications mobiles ont des contraintes spécifiques : ressources limitées, risque d’interruption soudaine et nécessité d’économiser la batterie. Voici comment configurer SQLite pour une application mobile :

-- Activer le mode WAL pour de meilleures performances et moins de blocage
PRAGMA journal_mode = WAL;


-- Régler la synchronisation pour équilibrer performance et sécurité
PRAGMA synchronous = NORMAL;


-- Limiter la taille du fichier WAL pour économiser l'espace
PRAGMA journal_size_limit = 2097152;  -- 2 Mo


-- Utiliser un cache modéré pour ne pas consommer trop de mémoire
PRAGMA cache_size = -2000;  -- Environ 2 Mo


-- Activer les clés étrangères pour l'intégrité des données
PRAGMA foreign_keys = ON;


-- Configurer un délai d'attente raisonnable
PRAGMA busy_timeout = 1000;  -- 1 seconde

Explication

Cette configuration offre un bon équilibre pour les applications mobiles :

  • Le mode WAL améliore les performances et la résistance aux interruptions
  • Une synchronisation NORMAL équilibre vitesse et durabilité
  • La limitation de la taille du journal évite de consommer trop d’espace de stockage
  • La taille de cache est modérée pour limiter l’utilisation de la mémoire

Scénario 2 : Site web à fort trafic

Pour un site web qui reçoit beaucoup de trafic et nécessite des performances élevées, la configuration suivante peut être utile :

-- Mode WAL pour permettre des lectures parallèles
PRAGMA journal_mode = WAL;


-- Points de contrôle automatiques fréquents
PRAGMA wal_autocheckpoint = 100;


-- Cache important pour les performances
PRAGMA cache_size = -50000;  -- Environ 50 Mo


-- Utiliser la mémoire mappée pour améliorer les performances de lecture
PRAGMA mmap_size = 1073741824;  -- 1 Go


-- Tables temporaires en mémoire
PRAGMA temp_store = MEMORY;


-- Synchronisation normale pour un bon équilibre
PRAGMA synchronous = NORMAL;


-- Permettre des threads auxiliaires
PRAGMA threads = 4;

Code d’initialisation de connexion (Node.js)

// Configuration de la connexion SQLite dans une application web Node.js
const sqlite3 = require('sqlite3').verbose();


function openOptimizedDatabase(dbPath) {
  return new Promise((resolve, reject) => {
    const db = new sqlite3.Database(dbPath, (err) => {
      if (err) return reject(err);


      // Configuration pour hautes performances
      const pragmas = [
        "PRAGMA journal_mode = WAL;",
        "PRAGMA wal_autocheckpoint = 100;",
        "PRAGMA cache_size = -50000;",
        "PRAGMA synchronous = NORMAL;",
        "PRAGMA temp_store = MEMORY;",
        "PRAGMA foreign_keys = ON;"
      ];


      // Exécuter les PRAGMA en série
      const runPragmas = (index) => {
        if (index >= pragmas.length) {
          return resolve(db);
        }


        db.run(pragmas[index], (err) => {
          if (err) {
            console.warn(`Erreur lors de l'exécution de ${pragmas[index]}:`, err);
          }
          runPragmas(index + 1);
        });
      };


      runPragmas(0);
    });
  });
}


// Utilisation
async function startServer() {
  const db = await openOptimizedDatabase('./data.sqlite');
  // Configurer le serveur web avec cette connexion...
}

Scénario 3 : Application de gestion financière avec données sensibles

Pour une application gérant des données financières sensibles, la sécurité et l’intégrité sont prioritaires :

-- Activer les clés étrangères pour l'intégrité référentielle
PRAGMA foreign_keys = ON;


-- Mode de journalisation fiable
PRAGMA journal_mode = WAL;


-- Synchronisation complète pour garantir la durabilité
PRAGMA synchronous = FULL;


-- Effacement sécurisé des données supprimées
PRAGMA secure_delete = ON;


-- Mécanisme de détection de corruption
PRAGMA integrity_check;


-- Schéma non fiable pour la sécurité
PRAGMA trusted_schema = OFF;

Fonction de vérification périodique (Python)

import sqlite3
import time
import logging


def check_database_integrity(db_path, max_attempts=3):
    """
    Fonction qui vérifie périodiquement l'intégrité de la base de données
    et effectue un point de contrôle WAL.
    """
    logging.info(f"Vérification de l'intégrité de la base de données {db_path}")


    attempt = 0
    while attempt < max_attempts:
        try:
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()


            # Vérifier l'intégrité
            cursor.execute("PRAGMA integrity_check;")
            result = cursor.fetchall()


            if result == [('ok',)]:
                logging.info("Vérification d'intégrité réussie")


                # Effectuer un point de contrôle WAL
                cursor.execute("PRAGMA wal_checkpoint(FULL);")
                checkpoint_result = cursor.fetchone()
                logging.info(f"Point de contrôle WAL terminé: {checkpoint_result}")


                conn.close()
                return True
            else:
                logging.error(f"Problèmes d'intégrité détectés: {result}")
                conn.close()
                attempt += 1
                time.sleep(1)  # Pause avant nouvelle tentative
        except Exception as e:
            logging.error(f"Erreur lors de la vérification: {e}")
            attempt += 1
            time.sleep(1)


    logging.critical(f"Échec de la vérification d'intégrité après {max_attempts} tentatives")
    return False


# Utilisation dans un planificateur de tâches
import schedule


def maintenance_quotidienne():
    check_database_integrity("finance.db")
    # Autres tâches de maintenance...


# Exécuter la maintenance tous les jours à 3h du matin
schedule.every().day.at("03:00").do(maintenance_quotidienne)

Scénario 4 : Migration de schéma de base de données

Lors de la mise à jour d’une application, il est souvent nécessaire de migrer le schéma de la base de données. Les PRAGMA peuvent aider à gérer ce processus :

-- Vérifier la version actuelle du schéma
PRAGMA user_version;


-- Mise à jour de la version (après les modifications de schéma)
PRAGMA user_version = 42;

Système de migration (JavaScript)

class DatabaseMigration {
  constructor(db) {
    this.db = db;
    this.migrations = [];
  }


  addMigration(version, migrationFn) {
    this.migrations.push({ version, migrationFn });
    // Trier les migrations par version
    this.migrations.sort((a, b) => a.version - b.version);
  }


  async migrate() {
    // Obtenir la version actuelle
    const currentVersion = await new Promise((resolve, reject) => {
      this.db.get("PRAGMA user_version;", (err, row) => {
        if (err) reject(err);
        else resolve(row.user_version);
      });
    });


    console.log(`Version actuelle de la base de données: ${currentVersion}`);


    // Trouver les migrations à appliquer
    const pendingMigrations = this.migrations.filter(m => m.version > currentVersion);


    if (pendingMigrations.length === 0) {
      console.log("La base de données est à jour");
      return;
    }


    // Appliquer les migrations dans l'ordre
    try {
      for (const migration of pendingMigrations) {
        console.log(`Application de la migration vers la version ${migration.version}...`);


        await new Promise((resolve, reject) => {
          this.db.run("BEGIN TRANSACTION;", err => {
            if (err) reject(err);
            else resolve();
          });
        });


        try {
          // Exécuter la fonction de migration
          await migration.migrationFn(this.db);


          // Mettre à jour la version
          await new Promise((resolve, reject) => {
            this.db.run(`PRAGMA user_version = ${migration.version};`, err => {
              if (err) reject(err);
              else resolve();
            });
          });


          await new Promise((resolve, reject) => {
            this.db.run("COMMIT;", err => {
              if (err) reject(err);
              else resolve();
            });
          });


          console.log(`Migration vers la version ${migration.version} réussie`);
        } catch (err) {
          // Annuler en cas d'erreur
          await new Promise((resolve) => {
            this.db.run("ROLLBACK;", () => resolve());
          });
          throw err;
        }
      }


      console.log("Toutes les migrations ont été appliquées avec succès");
    } catch (err) {
      console.error("Erreur lors de la migration:", err);
      throw err;
    }
  }
}


// Exemple d'utilisation
const migrator = new DatabaseMigration(db);


// Ajouter des migrations
migrator.addMigration(1, async (db) => {
  return new Promise((resolve, reject) => {
    db.run("CREATE TABLE utilisateurs (id INTEGER PRIMARY KEY, nom TEXT);", err => {
      if (err) reject(err);
      else resolve();
    });
  });
});


migrator.addMigration(2, async (db) => {
  return new Promise((resolve, reject) => {
    db.run("ALTER TABLE utilisateurs ADD COLUMN email TEXT;", err => {
      if (err) reject(err);
      else resolve();
    });
  });
});


// Exécuter les migrations
migrator.migrate().catch(console.error);

Scénario 5 : Débogage et diagnostic de base de données

Lors du développement ou du débogage d’applications, vous pouvez utiliser certains PRAGMA pour obtenir des informations précieuses sur votre base de données :

-- Obtenir la liste des tables
SELECT name FROM sqlite_schema WHERE type='table' AND name NOT LIKE 'sqlite_%';


-- Obtenir des informations sur une table
PRAGMA table_info(ma_table);


-- Lister les index d'une table
PRAGMA index_list(ma_table);


-- Vérifier les versions
PRAGMA user_version;
PRAGMA application_id;


-- Vérifier les statistiques (si disponible)
PRAGMA stats;


-- Vérifier les contraintes de clé étrangère
PRAGMA foreign_key_list(ma_table);

Outil d’audit de base de données (Python)

import sqlite3
import json
import os


def audit_database(db_path, output_file=None):
    """
    Fonction qui génère un rapport d'audit complet de la structure
    d'une base de données SQLite.
    """
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()


    db_info = {
        "file": {
            "path": os.path.abspath(db_path),
            "size": os.path.getsize(db_path),
            "last_modified": os.path.getmtime(db_path)
        },
        "pragmas": {},
        "tables": [],
        "indexes": [],
        "triggers": [],
        "views": []
    }


    # Collecter les informations PRAGMA
    for pragma in ["user_version", "application_id", "encoding",
                   "journal_mode", "page_size", "cache_size",
                   "auto_vacuum", "synchronous", "foreign_keys"]:
        cursor.execute(f"PRAGMA {pragma};")
        result = cursor.fetchone()
        if result:
            db_info["pragmas"][pragma] = dict(result) if hasattr(result, "keys") else result[0]


    # Lister les tables
    cursor.execute("SELECT name FROM sqlite_schema WHERE type='table' AND name NOT LIKE 'sqlite_%';")
    tables = [row[0] for row in cursor.fetchall()]


    # Analyser chaque table
    for table in tables:
        table_info = {"name": table, "columns": [], "foreign_keys": []}


        # Informations sur les colonnes
        cursor.execute(f"PRAGMA table_info({table});")
        for row in cursor.fetchall():
            column = dict(row)
            table_info["columns"].append(column)


        # Clés étrangères
        cursor.execute(f"PRAGMA foreign_key_list({table});")
        for row in cursor.fetchall():
            fk = dict(row)
            table_info["foreign_keys"].append(fk)


        # Index de la table
        cursor.execute(f"PRAGMA index_list({table});")
        table_indexes = []
        for idx_row in cursor.fetchall():
            idx = dict(idx_row)
            idx["columns"] = []


            # Colonnes de l'index
            cursor.execute(f"PRAGMA index_info({idx['name']});")
            for idx_info in cursor.fetchall():
                idx["columns"].append(dict(idx_info))


            table_indexes.append(idx)


        table_info["indexes"] = table_indexes
        db_info["tables"].append(table_info)


    # Collecter les vues
    cursor.execute("SELECT name, sql FROM sqlite_schema WHERE type='view';")
    for row in cursor.fetchall():
        db_info["views"].append({"name": row[0], "sql": row[1]})


    # Collecter les triggers
    cursor.execute("SELECT name, sql FROM sqlite_schema WHERE type='trigger';")
    for row in cursor.fetchall():
        db_info["triggers"].append({"name": row[0], "sql": row[1]})


    conn.close()


    # Écrire le rapport
    if output_file:
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(db_info, f, indent=2)


    return db_info


# Exemple d'utilisation
rapport = audit_database("ma_base.db", "rapport_audit.json")
print(f"Rapport généré avec {len(rapport['tables'])} tables et {sum(len(t['indexes']) for t in rapport['tables'])} index")

Bonnes pratiques pour les différents environnements

Environnement de développement

-- Mode de développement avec feedback immédiat
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -20000;

Environnement de test

-- Configuration de test avec vérifications d'intégrité
PRAGMA foreign_keys = ON;
PRAGMA integrity_check;
PRAGMA foreign_key_check;
PRAGMA journal_mode = DELETE;  -- Pour éviter les fichiers WAL durant les tests

Environnement de production

-- Configuration robuste pour la production
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;  -- ou FULL pour plus de sécurité
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;  -- 5 secondes de timeout
PRAGMA cache_size = -50000;  -- Ajuster selon la RAM disponible
PRAGMA wal_autocheckpoint = 1000;
PRAGMA trusted_schema = OFF;

Conclusion

Les exemples présentés dans cette section montrent comment les PRAGMA peuvent être intégrés dans des applications réelles pour améliorer les performances, garantir l’intégrité des données, faciliter la maintenance et le débogage.

Points clés à retenir :

  1. Adapter à votre cas d’utilisation : Il n’existe pas de configuration universelle. Adaptez les PRAGMA à vos besoins spécifiques.

  2. Documenter vos choix : Les PRAGMA impactent le comportement de la base de données. Documentez vos choix pour faciliter la maintenance.

  3. Tester dans des conditions réelles : Les gains de performance théoriques doivent être vérifiés dans des conditions d’utilisation réelles.

  4. Maintenir une stratégie de sauvegarde : Même avec les meilleures configurations, les sauvegardes régulières restent essentielles.

  5. Surveiller et ajuster : Les besoins évoluent avec la croissance de votre application. Surveillez les performances et ajustez les PRAGMA en conséquence.

Les PRAGMA sont un outil puissant qui vous permet d’adapter SQLite à presque tous les cas d’utilisation, des appareils embarqués aux serveurs web à fort trafic. En comprenant comment les utiliser efficacement, vous pouvez tirer le meilleur parti de cette base de données légère et polyvalente.