Kazuho's Weblog: さらば、愛しき論理削除。MySQLで大福帳型データベースを実現するツール「daifuku」を作ってみた

Tuesday, March 31, 2015

さらば、愛しき論理削除。MySQLで大福帳型データベースを実現するツール「daifuku」を作ってみた

先のエントリ「論理削除はなぜ「筋が悪い」か」で書いたとおり、データベースに対して行われた操作を記録し、必要に応じて参照したり取り消したりしたいという要求は至極妥当なものですが、多くのRDBは、そのために簡単に使える仕組みを提供していません。

ないのなら、作ってみようホトトギス

というわけで作った。

github.com/kazuho/daifuku^注1

daifukuは、RDBに対して加えられた変更をトランザクション単位でRDB内にJSONとして記録するためのストアドやトリガを生成するコマンドです。

% daifuku dbname tbl1 tbl2 > setup.sql

のように実行すると、指定されたテーブル（ここではtbl1とtbl2）にセットすべきトリガや、更新ログを記録するためのテーブル「daifuku_log」を生成するCREATE TABLEステートメントなど、必要なSQL文をsetup.sqlファイルに出力します。

次に出力内容を確認し、mysqlのルートユーザ権限でSQLを実行すると、準備は完了。

% mysql -u root < setup.sql

あとは、トランザクションの先頭でdaifuku_begin()プロシージャを呼び出せば、以降同一のトランザクション内で加えられた変更は、全てdaifuku_logテーブルに単一のJSON形式の配列として記録されます。

daifuku_begin()には、任意の文字列を渡し、RDBの変更とあわせて記録することができるので、更新の意図や操作を行った者の名前等を記録することにより、監査や障害分析を柔軟に行うことが可能になります。

また、記録されるトランザクションログのid（daifuku_log.id）は@daifuku_idというセッション変数に保存されるので、アプリケーションではその値をアプリケーション側のテーブルに記録することで、操作ログをUIに表示したり、Undo機能^注2を実装したりすることも可能でしょう。

実際に使ってみた例が以下になります。トランザクション内で行ったdirect_messageテーブルとnotificationテーブルに対する操作がJSON形式でdaifuku_logテーブルに保存されていることが確認できます^注3。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> call daifuku_begin('');
Query OK, 1 row affected (0.00 sec)

mysql> insert into direct_message (from_user,to_user,body) values (2,1,'WTF!!!');
Query OK, 1 row affected (0.01 sec)

mysql> insert into notification (user,body) values (2,'@yappo sent a new message');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from daifuku_log\G
*************************** 1. row ***************************
    id: 4
  info: 
action: [
    [
        "insert",
        "direct_message",
        {},
        {
            "id":        "2",
            "from_user": "2",
            "to_user":   "1",
            "body":      ["V1RGISEh"]
        }
    ],[
        "insert",
        "notification",
        {},
        {
            "id":   "2",
            "user": "2",
            "body": ["QHlhcHBvIHNlbnQgYSBuZXcgbWVzc2FnZQ=="]
        }
    ]
]
1 row in set (0.00 sec)

大規模なコンシューマ向けウェブサービスでは、この種のトリガは使いづらいかもしれませんが、バックオフィス向けのソフトウェア等では工数削減と品質向上に役立つ可能性があると思います。

ってことで、それでは、have fun！

補遺：他のアプローチとの比較

同様の機能をアプリケーションロジックとして、あるいは手書きのトリガとして実装することも不可能ではありませんが、トリガを自動生成する daifuku のアプローチの方が、アプリケーション開発に必要な工数、バグ混入の可能性、データベースのロック時間の極小化等の点において優れていると考えられます。

また、過去の任意のタイミングにおけるデータベースの状態を参照する必要がある場合は、範囲型による時間表現を用いたデータベース設計を行うべきでしょうが、要件が操作の記録やUndoである場合には、そのような過剰な正規化は不要であり悪影響のほうが大きいです^注4。ってかMySQLには範囲型ないし。

注1: 名前の由来は大福帳型データベースです。
注2: トランザクション単位のrevertではなくdaifuku_logテーブルに記録されたログを逆順に適用していった際に以前のテーブルの状態に戻ることを保証したい場合は、分離レベルをシリアライザブルに設定しておく必要があります。この点において、更新ログを主、現時点での状態を示すテーブルを従とするアプローチに対し劣位であることは、先の記事でも触れたとおりです。
注3: 文字列型等、制御文字やUTF-8の範囲外の値を含む可能性のある型のデータについては、base64エンコードが行われ、それを示すために配列としてログに記録されます。詳しくはman daifukuをご参照ください。
注4: 操作＝トランザクションとは元来リレーションに１対１でマッピングされづらいものなので、過去の操作を取り扱うことが主目的の場合には、無理に正規化を行わない方が都合が良いケースが多いと考えられます。