Chapitre 8.2 — Le schéma de Drill & les migrations

Où on en est : vous savez parler à la base. Reste à décider ce qu’elle contient — et surtout comment elle évoluera SANS perdre l’historique des utilisateurs (vous, d’abord).

⏱️ TL;DR — Deux tables : sessions (une partie jouée) et answers (chaque réponse, reliée par foreign key ON DELETE CASCADE). Les migrations suivent le pattern PRAGMA user_version : la base connaît sa version, onInit rejoue les migrations manquantes dans l’ordre, chacune dans une transaction. Index sur les colonnes de tri/filtre. C’est le même concept que les migrations Doctrine/Prisma — en 30 lignes maison.

🎯 Objectifs

• Concevoir le schéma sessions/answers (et savoir pourquoi answers existe).
• Écrire le moteur de migrations user_version — définitif pour toutes vos apps.
• Poser foreign keys, index, et les activer correctement (foreign_keys=ON).
• Savoir ajouter une colonne en v2 sans rien casser.

Le schéma

-- v1 du schéma Drill
CREATE TABLE sessions (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  theme         TEXT    NOT NULL,             -- slug ('symfony', 'melange'…)
  score         INTEGER NOT NULL,
  total         INTEGER NOT NULL,             -- nombre de questions de la session
  correct_count INTEGER NOT NULL,
  best_streak   INTEGER NOT NULL,
  played_at     INTEGER NOT NULL              -- epoch ms
);
 
CREATE TABLE answers (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id  INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  question_id TEXT    NOT NULL,               -- 'sf-0001' — l'id STABLE du repo (ch. 7.3 !)
  correct     INTEGER NOT NULL                -- 0/1
);
 
CREATE INDEX idx_sessions_theme_date ON sessions(theme, played_at DESC);
CREATE INDEX idx_answers_question ON answers(question_id);

Décisions de design, une à une :

• Pourquoi answers ? Les stats fines : « taux de réussite sur sf-0001 », « questions les plus ratées » — le carburant de la révision espacée de Drill v2 (P19). Sans answers, ces questions seraient sans réponse à jamais (on ne reconstruit pas le passé).
• question_id = l’id du repo : le pont entre la base locale et les banques GitHub — la raison du « ids stables, jamais réutilisés » du contrat de contenu (ch. 7.3).
• played_at en epoch ms (INTEGER) : tri naturel, formatage côté TS — pas de dates string.
• Index : sessions(theme, played_at DESC) sert LES deux requêtes du quotidien (historique par thème, dernier record) ; answers(question_id) servira les agrégats par question.
• ON DELETE CASCADE : supprimer une session emporte ses réponses — mais SQLite n’applique les FK que si on les active (ci-dessous, le piège classique).

Le moteur de migrations

Le contrat : chaque évolution du schéma est une migration numérotée ; la base stocke sa version ; au démarrage on rejoue ce qui manque, dans l’ordre :

// lib/db/migrations.ts
import type { SQLiteDatabase } from 'expo-sqlite'
 
const MIGRATIONS: string[] = [
  // v1 — schéma initial
  `
  CREATE TABLE sessions (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    theme         TEXT    NOT NULL,
    score         INTEGER NOT NULL,
    total         INTEGER NOT NULL,
    correct_count INTEGER NOT NULL,
    best_streak   INTEGER NOT NULL,
    played_at     INTEGER NOT NULL
  );
  CREATE TABLE answers (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id  INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    question_id TEXT    NOT NULL,
    correct     INTEGER NOT NULL
  );
  CREATE INDEX idx_sessions_theme_date ON sessions(theme, played_at DESC);
  CREATE INDEX idx_answers_question ON answers(question_id);
  `,
  // v2, v3… s'ajouteront ICI, jamais en modifiant v1
]
 
export async function migrate(db: SQLiteDatabase) {
  await db.execAsync('PRAGMA journal_mode = WAL')      // perfs écriture
  await db.execAsync('PRAGMA foreign_keys = ON')       // ← SANS ça, les FK sont décoratives !
 
  const row = await db.getFirstAsync<{ user_version: number }>('PRAGMA user_version')
  const current = row?.user_version ?? 0
 
  for (let v = current; v < MIGRATIONS.length; v++) {
    await db.withTransactionAsync(async () => {
      await db.execAsync(MIGRATIONS[v])
      await db.execAsync(`PRAGMA user_version = ${v + 1}`)
    })
  }
}

Branché dans onInit du SQLiteProvider (chapitre 8.1) : à chaque démarrage, l’app amène la base à la version courante — installation neuve (0 → N) comme mise à jour (k → N), même code.

Les règles gravées :

1. On n’édite JAMAIS une migration livrée — on en ajoute une nouvelle (les bases des utilisateurs ont déjà exécuté les anciennes).
2. Une migration = une transaction : elle passe entière ou pas du tout ; user_version n’avance que si le SQL a réussi.
3. PRAGMA foreign_keys = ON à chaque ouverture (réglage par connexion, pas par base) — l’oubli le plus répandu de tout l’écosystème SQLite.
4. WAL (write-ahead logging) : lectures non bloquées par les écritures — gratuit, toujours.

L’évolution type : la v2

Drill v1.1 voudra le temps moyen de réponse. Le geste :

const MIGRATIONS = [
  `…v1 inchangée…`,
  // v2 — temps de réponse par answer
  `ALTER TABLE answers ADD COLUMN response_ms INTEGER NOT NULL DEFAULT 0;`,
]

ALTER TABLE ADD COLUMN + DEFAULT : les lignes existantes reçoivent 0 (sémantique « inconnu » assumée), les nouvelles écrivent la vraie valeur. Les utilisateurs ne perdent rien, l’app v1 restée en arrière ne voit simplement pas la colonne. (SQLite ne sait pas tout en ALTER — renommer/supprimer une colonne passe par une table temporaire ; on garde ça pour le jour où, la doc SQLite décrit la recette des « 12 étapes ».)

⚠️ Piège — En DEV, quand vous tâtonnez sur le schéma, la tentation est de modifier la migration v1 et de réinstaller. OK tant que rien n’est livré — mais dès que Drill tourne en APK sur votre téléphone avec un historique auquel vous tenez (ça arrive vite !), la discipline s’applique à vous aussi : nouvelle migration, jamais d’édition. Astuce dev : adb shell pm clear = base vierge pour re-tester le chemin 0 → N complet.

💡 Pour un dev Next.js — C’est Prisma Migrate / Doctrine Migrations, réduit à l’os : un compteur (user_version) + un tableau ordonné + des transactions. Le comprendre à nu ici rend les gros outils transparents ensuite — et pour une app mobile, ces 30 lignes couvrent 100 % du besoin réel.

✏️ Exercices

1. Vérifiez le CASCADE : insérez une session + 3 answers, supprimez la session avec un simple DELETE (sans transaction manuelle), comptez les answers restantes. Puis refaites le test en commentant PRAGMA foreign_keys = ON.

2. Écrivez la migration v2 réelle (response_ms) ET la modification de la chaîne d’écriture (store → insertSession) pour alimenter la colonne. Le chrono existe déjà quelque part — où ?

3. Simulez le parcours d’un utilisateur : app v1 (base v1, 5 sessions), mise à jour vers l’app v2. Déroulez ce que fait migrate() ligne par ligne, et ce que voit l’utilisateur.

🧠 Quiz

1. Pourquoi la table answers, alors que sessions a déjà correct_count ?

2. Décrivez le pattern user_version.

3. Quelle règle absolue régit les migrations déjà livrées ?

4. Que faut-il activer pour que ON DELETE CASCADE fonctionne ?

5. Comment ajoute-t-on une colonne sans casser l’existant ?

👉 Chapitre suivant : 8.3 — Stats & records — le SQL qui transforme l’historique en écrans : records, progression, taux de réussite.