SQLite-Audit-Trigger für iOS auf einem Cloud-Mac prüfen

Die Anwendungslogik wird unter Windows entwickelt, der iOS-Build anschließend auf einem Cloud-Mac erstellt. Dabei können Datenbankänderungen auf den ersten Blick korrekt aussehen, während in der Audit-Tabelle Einträge doppelt auftauchen oder fehlen. Nach einem Rollback zeigt die Oberfläche womöglich sogar einen falschen Zustand an. Statt solche Fehler erst bei der Abnahme des gesamten Builds zu suchen, sollte man den SQLite-Trigger zunächst mit einer minimalen, ausführbaren Test-Fixture prüfen und die Tests danach über die tatsächlich von der Anwendung verwendete Verbindung wiederholen.

Zuerst festlegen, was ein Audit-Eintrag bedeutet

Hier sollen ausschließlich „per Commit bestätigte Titeländerungen“ protokolliert werden, nicht jeder Klick auf „Speichern“. Wird derselbe Wert erneut geschrieben, darf kein zusätzlicher Eintrag entstehen. Der Wechsel von einer Zeichenfolge zu NULL muss dagegen erfasst werden. Wird die fachliche Transaktion zurückgerollt, müssen auch ihre Audit-Einträge verschwinden. Diese drei Bedingungen werden zuerst als verbindlicher Vertrag formuliert; erst danach wird der Trigger implementiert.

Stellen Sie auf dem Cloud-Mac das Kommandozeilenwerkzeug sqlite3 bereit und halten Sie die Version mit sqlite3 --version fest. Bindet die Anwendung eine eigene Datenbankbibliothek ein, muss zusätzlich über die Anwendungsverbindung SELECT sqlite_version() abgefragt werden. Die Version des Kommandozeilenwerkzeugs sagt nichts darüber aus, welche Version unter iOS tatsächlich läuft.

Der Trigger schreibt in eine gewöhnliche Audit-Tabelle innerhalb derselben Transaktion wie die fachliche Änderung. Damit lassen sich bestätigte Änderungen nachvollziehen. Fehlgeschlagene Versuche lassen sich so jedoch nicht unabhängig speichern; auch ein manipulationssicheres Sicherheits-Audit entsteht dadurch nicht.

Verwenden Sie für die Tests ausschließlich synthetische Datensätze und keine Kopie der Produktionsdatenbank. Alte und neue Werte in der Audit-Tabelle können Benutzerinhalte enthalten und dürfen nicht aus Bequemlichkeit bei der Fehlersuche unbegrenzt aufbewahrt werden.

Eine SQL-Fixture erstellen, die bei Fehlern tatsächlich abbricht

Speichern Sie den folgenden Inhalt als trigger-check.sql. Die temporäre Assertion-Tabelle macht über eine Constraint-Verletzung aus einer falschen Anzahl von Einträgen einen SQL-Fehler. Wer nur Abfrageergebnisse ausgibt, aber den Exit-Code nicht prüft, hat damit noch kein verlässliches Prüfkriterium.

.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;

Führen Sie sqlite3 :memory: < trigger-check.sql aus. Bei Erfolg ist keine Ausgabe erforderlich; bei einem Fehler muss der Prozess einen Exit-Code ungleich null zurückgeben. Die Punktbefehle gehören zur Kommandozeilen-Shell und dürfen nicht unverändert an die Datenbankschnittstelle der Anwendung übergeben werden.

Entscheidend ist hier IS NOT: Damit lassen sich NULL- und Nicht-NULL-Werte unterscheiden. Wird stattdessen != verwendet, ergeben Vergleiche mit NULL nicht wahr. Dadurch können Audit-Einträge ausbleiben.

Mit Negativkontrollen nachweisen, dass die Prüfung greift

Entfernen Sie zunächst die WHEN-Bedingung und führen Sie den Test erneut aus. Schon die erste Aktualisierung ohne Wertänderung muss eine Assertion-Verletzung auslösen. Ändern Sie die Bedingung anschließend in OLD.title != NEW.title. Nun muss der Test für den Wechsel zu NULL fehlschlagen. Wird keiner der beiden Fehler erkannt, prüfen Sie, ob das Skript den Exit-Code ignoriert oder weiterhin eine alte Datei ausgeführt wird.

Eine weitere Falle sind die Spaltennamen in UPDATE OF: SQLite lässt dort auch Namen nicht vorhandener Spalten zu und verweigert die Trigger-Erstellung nicht allein wegen eines Tippfehlers. Ersetzen Sie im Beispiel vorübergehend title durch titel. Bei einer tatsächlichen Titeländerung muss der Test dann wegen des fehlenden Audit-Eintrags scheitern.

Prüfen Sie nicht nur die Anzahl der Trigger. Lesen Sie ihre tatsächlichen Definitionen aus sqlite_schema, gleichen Sie die Spalten mit PRAGMA table_info(note) ab und sorgen Sie dafür, dass jedes überwachte Feld mindestens einmal tatsächlich geändert wird. Nur so wird auch der Fall „vorhanden, aber wirkungslos“ abgedeckt.

Rekursion und Mehrzeilen-Updates in die Abnahme aufnehmen

Der aktuelle Trigger schreibt ausschließlich in die Audit-Tabelle und verändert note nicht erneut. Er ist daher nicht auf Selbstrekursion angewiesen. Führen Sie die Fixture sowohl mit der Rekursionseinstellung OFF als auch mit ON aus; das Ergebnis muss identisch sein. Setzen Sie diese Einstellung bei der Initialisierung der Anwendungsverbindung ausdrücklich und lesen Sie sie anschließend zurück, statt sich auf die Standardwerte eines Werkzeugs zu verlassen.

Kommt später ein Trigger hinzu, der den Titel nach einer Aktualisierung korrigiert, kann das erneute Schreiben in die Quelltabelle Rekursion oder zusätzliche Audit-Einträge auslösen. Kaschieren Sie solche Probleme nicht durch eine angepasste Rekursionstiefe. Legen Sie zuerst fest, ob die Normalisierung vor dem Schreiben in der Anwendung erfolgt oder von genau einem Trigger übernommen wird.

Fügen Sie außerdem mehrere Datensätze mit unterschiedlichen Primärschlüsseln ein und ändern Sie alle Titel mit einer einzigen Update-Anweisung. SQLite führt Trigger für jede Zeile einzeln aus. Prüfen Sie deshalb für jeden Primärschlüssel den zugehörigen Audit-Inhalt, statt anzunehmen, dass die laufende Nummer der Einträge die fachliche Verarbeitungsreihenfolge wiedergibt. Auch für leere Zeichenfolgen, NULL-Werte und Updates ohne passende Datensätze sind eigene Testfälle erforderlich.

Die letzte Prüfung über die iOS-Verbindung durchführen

Nachdem die Kommandozeilen-Fixture erfolgreich durchgelaufen ist, übernehmen Sie Tabellenerstellung und Datenoperationen als getrennte Schritte in die Datenbank-Integrationstests. Verwenden Sie dabei die tatsächliche Datenbankabstraktion und Transaktionsschnittstelle des Projekts. Prüfen Sie den Rückgabecode jeder Ausführung und wandeln Sie Constraint-Fehler nicht in leere Ergebnisse um. Bei einem erwarteten Fehler muss außerdem geprüft werden, ob sich über die Verbindung anschließend weiterhin Abfragen ausführen lassen.

Ergänzen Sie zum Schluss eine gezielte Fehlerinjektion für das Schreiben der Audit-Einträge: Fügen Sie in einer eigens dafür vorgesehenen Testdatenbank einen Constraint hinzu, der einen bestimmten neuen Wert ablehnt, und aktualisieren Sie dann die fachliche Tabelle. Stellen Sie sicher, dass die Aktualisierung einen Fehler zurückgibt und weder der fachliche Wert noch die Audit-Einträge teilweise übernommen wurden. Anschließend muss der Aufrufer die Transaktion ausdrücklich beenden oder zurückrollen. Es darf nicht vorausgesetzt werden, dass jeder Fehler automatisch die gesamte Transaktion zurücknimmt.

Bewahren Sie für die Übergabe die Fixture, die tatsächlichen Trigger-Definitionen, die Datenbankversionen beider Umgebungen und die Ergebnisse der Fehlerfälle auf. Sind diese Prüfungen innerhalb der Mietdauer abgeschlossen, muss beim nächsten Maschinenwechsel nur derselbe Vertrag erneut getestet werden. Ob das Audit zuverlässig arbeitet, lässt sich dann unabhängig davon beurteilen, ob beim Klick auf „Speichern“ eine Fehlermeldung erscheint.

Häufig gestellte Fragen