クラウドMacでiOSのSQLite監査トリガーを検証する

Windowsで業務機能を開発し、iOS向けのビルドをクラウドMacで行う場合、データベースの更新は正常に見えても、監査テーブルには余分な記録や記録漏れが生じていることがあります。ロールバック後に画面が誤った状態を表示する可能性もあります。ビルド全体の受け入れテストまで原因調査を先送りせず、まずSQLiteトリガーを最小限の実行可能なテストに切り出し、その後、アプリが実際に使う接続でも同じ検証を行いましょう。

監査記録が何を表すのかを先に定義する

この記事で記録するのは「コミット済みのタイトル変更」であり、ユーザーが保存ボタンを押した回数ではありません。同じ値を書き戻してもログを追加しないこと、文字列からNULLへの変更は記録すること、業務トランザクションをロールバックしたらログも取り消すこと。この3つを先に契約として定めてから、トリガーを実装します。

クラウドMacにコマンドラインツールの sqlite3 を用意し、sqlite3 --version を実行してバージョンを記録します。アプリにデータベースライブラリを組み込んでいる場合は、アプリの接続でも SELECT sqlite_version() を実行してください。コマンドラインツールのバージョンが、iOSで実際に動作するバージョンと同じとは限りません。

トリガーによる通常の監査テーブルへの書き込みは、業務データの書き込みと同じトランザクション内で行われます。コミット済みの変更を追跡する用途には適していますが、失敗した操作を独立して保存する用途には向かず、改ざんできないセキュリティ監査の仕組みにもなりません。

テストには人工的に作成したレコードだけを使い、本番データベースはコピーしません。監査テーブルの変更前後の値にはユーザーのコンテンツが含まれる可能性があるため、調査の便宜を理由に無期限で保持してはいけません。

不整合をSQLエラーにできるテストを作る

以下を trigger-check.sql として保存します。一時的なアサーションテーブルの制約によって、件数の不一致をSQLエラーに変えます。クエリ結果を表示するだけで終了コードを確認しなければ、失敗時に後続処理を止めるゲートにはなりません。

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

sqlite3 :memory: < trigger-check.sql を実行します。成功時は何も出力されなくても構いませんが、失敗時にはゼロ以外の終了コードが返る必要があります。ここで使っているドットコマンドはコマンドラインシェル専用なので、アプリのデータベース実行APIにそのまま渡すことはできません。

この例の要点は IS NOT です。NULLとNULL以外の値を区別できます。!= に置き換えると、NULL を含む比較が真にならず、ログが記録されない可能性があります。

意図的に誤りを入れ、ゲートが機能することを確かめる

まず WHEN 条件を削除して再実行します。最初の、値が変わらない更新でアサーションが失敗するはずです。次に条件を OLD.title != NEW.title に変更すると、NULLへの変更を検証するケースが失敗するはずです。どちらの誤りも検出されない場合は、スクリプトが終了コードを握りつぶしていないか、古いファイルを実行し続けていないかを確認してください。

もう一つの落とし穴は、UPDATE OF に指定する列名です。SQLiteは定義内に存在しない列名があっても許容し、スペルミスだけを理由にトリガーの作成を拒否しません。例のこの部分にある title を一時的に titel に変えてください。実際にタイトルを更新すると、ログが記録されないためテストが失敗するはずです。

トリガーの数だけを確認して終わらせないでください。sqlite_schema から実際の定義を読み取り、PRAGMA table_info(note) で列を照合したうえで、監査対象の各フィールドに少なくとも一度は実際の変更を加えます。これにより、「存在するが動作しない」ケースも検出できます。

再帰と複数行更新も受け入れ条件に含める

現在のトリガーは監査テーブルに書き込むだけで、note を再び変更しないため、自己再帰には依存しません。再帰設定を OFF と ON にしてそれぞれテストを実行し、結果が同じになることを確認します。アプリの接続を初期化するときは、この設定を明示的に指定して読み戻し、ツールのデフォルト値に依存しないようにします。

後から「更新後にタイトルを補正する」トリガーを追加すると、元のテーブルへの再書き込みによって再帰や余分なログが発生する可能性があります。再帰の深さを調整して問題を隠すのではなく、正規化をアプリの書き込み前に行うのか、単一のトリガーに任せるのかを先に明確にしてください。

続いて、異なる主キーを持つレコードを複数挿入し、1つの更新文ですべてのタイトルを変更します。SQLiteのトリガーは行ごとに実行されるため、ログの連番が業務処理の順序を表すと考えず、主キーごとにログの内容を確認してください。空文字列、NULL、対象レコードが1件もない更新についても、それぞれテストケースを用意します。

iOSアプリの接続で最終確認を行う

コマンドラインでのテストに合格したら、テーブル作成と各操作をデータベースの結合テストに移し、プロジェクトで実際に使っているデータベースラッパーとトランザクションAPIで実行します。各実行の戻りコードを確認し、制約違反を空の結果に置き換えないようにしてください。想定どおりに失敗した場合も、その接続で引き続きクエリを実行できることを検証します。

最後に、監査書き込みの失敗を意図的に発生させるテストを追加します。専用のテストデータベースで、特定の新しい値を拒否する制約を追加し、業務テーブルを更新します。更新がエラーを返し、業務データの値とログが部分的にコミットされていないことを確認してください。その後、呼び出し側で明示的にトランザクションを終了またはロールバックします。どのエラーでも自動的にトランザクション全体が取り消されるとは限りません。

引き渡し時には、テスト一式、実際のトリガー定義、両環境のデータベースバージョン、失敗ケースの実行結果を残します。レンタル期間中にこれらを確認しておけば、次にマシンを変更するときも、同じ契約に基づくテストを再実行するだけで済みます。「保存ボタンを押してもエラーが出なかった」という理由だけで、監査の信頼性を判断する必要はありません。

よくある質問