Vérifier les déclencheurs d’audit SQLite pour iOS sur un Mac cloud

Le développement métier a été réalisé sous Windows, puis le projet est transféré sur un Mac cloud pour produire la version iOS. Les mises à jour de la base peuvent sembler fonctionner, alors que la table d’audit contient des entrées en trop, en oublie certaines ou conduit l’interface à afficher un état erroné après une annulation de transaction. Plutôt que d’attendre la recette de l’application complète pour enquêter, mieux vaut isoler les déclencheurs SQLite dans un jeu de test minimal et exécutable, puis rejouer les vérifications avec la connexion réellement utilisée par l’application.

Définir d’abord ce que représente une entrée d’audit

Cet article ne journalise que les « changements de titre validés par une transaction », et non chaque clic sur Enregistrer. Réécrire une valeur identique ne doit pas ajouter d’entrée ; remplacer une chaîne par une valeur nulle doit être journalisé ; l’annulation de la transaction métier doit aussi annuler les entrées d’audit. Formalisez ces trois conditions dans un contrat avant d’implémenter le déclencheur.

Préparez l’outil en ligne de commande sqlite3 sur le Mac cloud et exécutez sqlite3 --version pour consigner sa version. Si l’application embarque sa propre bibliothèque de base de données, exécutez aussi SELECT sqlite_version() via sa connexion : la version de l’outil en ligne de commande ne permet pas de connaître celle réellement utilisée sous iOS.

Un déclencheur qui écrit dans une table d’audit ordinaire participe à la même transaction que l’écriture métier. Il convient au suivi des changements validés, mais pas à la conservation indépendante des tentatives échouées, et ne constitue pas un journal d’audit de sécurité infalsifiable.

Utilisez uniquement des données créées pour les tests, sans copier la base de production. Les anciennes et nouvelles valeurs de la table d’audit peuvent contenir du contenu utilisateur : la facilité de diagnostic ne justifie pas leur conservation indéfinie.

Créer un jeu SQL qui échoue explicitement

Enregistrez le contenu suivant dans trigger-check.sql. Grâce à une contrainte, la table temporaire d’assertions transforme tout comptage incorrect en erreur SQL. Afficher les résultats des requêtes sans vérifier le code de sortie ne suffit pas à établir un contrôle bloquant.

.bail on
PRAGMA recursive_triggers = OFF;

CREATE TABLE note (
    id INTEGER PRIMARY KEY,
    title TEXT
);

CREATE TABLE note_audit (
    seq INTEGER PRIMARY KEY,
    note_id INTEGER NOT NULL,
    old_title TEXT,
    new_title TEXT
);

CREATE TRIGGER note_title_audit
AFTER UPDATE OF title ON note
WHEN OLD.title IS NOT NEW.title
BEGIN
    INSERT INTO note_audit(note_id, old_title, new_title)
    VALUES(NEW.id, OLD.title, NEW.title);
END;

CREATE TEMP TABLE assertion (
    ok INTEGER NOT NULL CHECK(ok = 1)
);

INSERT INTO note VALUES(1, 'draft');
UPDATE note SET title = title WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 0 FROM note_audit;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;
INSERT INTO assertion
SELECT old_title IS 'draft' AND new_title IS NULL
FROM note_audit WHERE seq = 1;

UPDATE note SET title = NULL WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 1 FROM note_audit;

UPDATE note SET title = 'ready' WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

BEGIN;
UPDATE note SET title = 'temporary' WHERE id = 1;
ROLLBACK;

INSERT INTO assertion
SELECT title IS 'ready' FROM note WHERE id = 1;
INSERT INTO assertion
SELECT count(*) = 2 FROM note_audit;

Exécutez sqlite3 :memory: < trigger-check.sql. En cas de réussite, la commande peut ne rien afficher ; en cas d’échec, elle doit renvoyer un code de sortie non nul. Les commandes commençant par un point sont propres à l’outil en ligne de commande : elles ne peuvent pas être transmises telles quelles à l’interface d’exécution SQL de l’application.

La clé de cet exemple est IS NOT : cet opérateur distingue les valeurs nulles des valeurs non nulles. Si vous le remplacez par !=, les comparaisons impliquant NULL ne renverront pas vrai, ce qui peut laisser des changements sans entrée d’audit.

Prouver l’efficacité du contrôle avec des contre-exemples

Commencez par supprimer la condition WHEN, puis relancez le test : la première mise à jour sans changement doit provoquer un échec d’assertion. Remplacez ensuite la condition par OLD.title != NEW.title : le cas de transition vers une valeur nulle doit échouer. Si aucune de ces deux erreurs n’est détectée, vérifiez que le script ne masque pas le code de sortie et que le fichier exécuté n’est pas encore l’ancienne version.

Autre piège : les noms de colonnes dans UPDATE OF. SQLite accepte des noms de colonnes inexistants dans cette clause et ne refuse pas de créer le déclencheur pour une simple faute de frappe. Remplacez temporairement title par titel dans cette clause de l’exemple : une véritable modification du titre doit alors faire échouer le test, faute d’entrée d’audit.

Ne vous contentez pas de compter les déclencheurs. Lisez leur définition effective dans sqlite_schema, vérifiez les colonnes avec PRAGMA table_info(note), puis provoquez au moins un changement réel pour chaque champ audité. C’est ainsi que vous détecterez les déclencheurs présents, mais inopérants.

Intégrer la récursion et les mises à jour multilignes à la recette

Le déclencheur actuel écrit uniquement dans la table d’audit, sans modifier à nouveau note : il ne dépend donc pas de sa propre récursion. Exécutez le jeu de test avec la récursion réglée successivement sur OFF et sur ON ; les résultats doivent être identiques. Lors de l’initialisation de la connexion de l’application, définissez explicitement ce réglage, puis relisez sa valeur, sans vous fier aux paramètres par défaut de l’outil.

Si vous ajoutez ensuite un déclencheur qui « corrige le titre après la mise à jour », cette nouvelle écriture dans la table source peut provoquer une récursion ou des entrées d’audit supplémentaires. Ne masquez pas le problème en ajustant la profondeur de récursion : décidez d’abord si la normalisation doit avoir lieu dans l’application avant l’écriture ou être confiée à un seul déclencheur.

Insérez ensuite plusieurs enregistrements ayant des clés primaires distinctes, puis modifiez tous leurs titres avec une seule instruction de mise à jour. Les déclencheurs SQLite s’exécutent ligne par ligne : vérifiez le contenu du journal pour chaque clé primaire, sans supposer que les numéros de séquence des entrées reflètent l’ordre du traitement métier. Prévoyez aussi des cas distincts pour la chaîne vide, la valeur nulle et une mise à jour ne trouvant aucun enregistrement.

Effectuer les dernières vérifications avec la connexion iOS

Une fois le jeu SQL validé en ligne de commande, répartissez la création des tables et les opérations dans des tests d’intégration de la base de données, en utilisant la couche d’accès aux données et l’interface de transaction réelles du projet. Vérifiez le code de retour de chaque exécution, sans transformer les erreurs de contrainte en résultats vides. Lorsqu’un échec est attendu, vérifiez également que la connexion peut encore exécuter des requêtes.

Ajoutez enfin une injection de défaillance lors de l’écriture d’audit : dans une base réservée aux tests, ajoutez une contrainte qui refuse une nouvelle valeur précise, puis mettez à jour la table métier. Vérifiez que la mise à jour renvoie une erreur et qu’aucune validation partielle ne touche les données métier ou le journal. Le code appelant doit ensuite terminer explicitement la transaction ou l’annuler : ne supposez pas que toute erreur annule automatiquement la transaction entière.

À la livraison, conservez le jeu de test, la définition effective des déclencheurs, les versions de la base de données dans les deux environnements et les résultats des cas d’échec. Une fois ces vérifications effectuées pendant la période de location, il suffira de rejouer les mêmes tests contractuels au prochain changement de machine, plutôt que de juger la fiabilité de l’audit au seul fait que « le bouton Enregistrer n’a affiché aucune erreur ».

Questions fréquentes