Tuesday, March 31, 2015

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

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

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

というわけで作った。


daifukuは、RDBに対して加えられた変更をトランザクション単位RDB内JSONとして記録するためのストアドやトリガを生成するコマンドです。
% daifuku dbname tbl1 tbl2 > setup.sql
のように実行すると、指定されたテーブル(ここではtbl1tbl2)にセットすべきトリガや、更新ログを記録するためのテーブル「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: 操作=トランザクションとは元来リレーションに1対1でマッピングされづらいものなので、過去の操作を取り扱うことが主目的の場合には、無理に正規化を行わない方が都合が良いケースが多いと考えられます。

25 comments:

  1. Daifuku tool seems has many useful features, thanks for sharing and taking example about it.
    192.168 1.1

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

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Thank you for sharing the information. Glad to see.
    instagram technology

    ReplyDelete

  8. Thanks for sharing this marvelous post. I m very pleased to read this article.
    We provide free service of sites below
    office.com/setup
    norton.com/setup
    IT support
    norton.com/nu16

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Thank you so much for giving pieces of information. If you are unable to Login Asus Wireless Router. I can help you to easily Login Asus Wireless Router. To know how to visit here: Login Asus Wireless Router

    ReplyDelete
  12. Thanks for the study about how to
    access Netgear wireless router.
    If you furtherer face setting problem visit my page.

    ReplyDelete
  13. Our support for HP devices through this HP Contact Support Phone Number will be available 7 days between 9:00 am to 6:00 EST. In-case of no response we will allow you to drop us a voicemail or type an email at support@hpcustomersupports.com, wherein we will assign you a dedicated technician who will get in touch with you within maximum 24 hours.

    ReplyDelete
  14. Your Belkin range extender can be set up wirelessly. in order to eliminate this interference and obstruction, you need a device like Belkin range extender setup. visit
    belkin range

    ReplyDelete
  15. Thanks for your stop at Indian Packers and Movers in Mumbai. We really know the intentions of the people who are moving their homes or offices in Mumbai. We are one of the best Packers And Movers in Mumbai that gives values to the suggestions and recommendations of the client.
    Packers and Movers in Mumbai
    Packers and Movers in Dadar
    Packers and Movers in Matunga Road
    Packers and Movers in Mahim
    Packers and Movers in Bandra

    ReplyDelete
  16. We are known manufacturers of modern classic chair. The designs are so attractive that you will be confused among which one to buy. Our products are well appreciated by top interior designers, esteemed customers and architects.
    Office Chair Supplier in Mumbai
    Visitor Chair Supplier in Mumbai
    Chair Dealers in Mumbai
    Top Chair Manufacturers in Mumbai
    Best Chair Manufacturers in Mumbai

    ReplyDelete
  17. A lesser known fact about packers and movers in India is the level of disorganization in the industry, which is the main reason there are so many unprofessional and fake movers who take up relocation jobs and either provide horrible service or vanish with the household items all together.
    Packers and Movers in Kukatpally
    Packers and Movers in Miyapur
    Packers and Movers in Sainikpuri
    Movers and Packers in Hyderabad
    Movers and Packers in Jubilee Hills

    ReplyDelete
  18. This topic is very interesting and I am interested but do not know where to find, thankfully you create this topic, hope everyone will help me slither io

    ReplyDelete
  19. Thanks for sharing the information it was very helpful for me run 3

    ReplyDelete


  20. Get laid with most stunning and gorgeous Indian call girls. We have so many exciting indian female escorts
    who are ready to come and be your companion for the day and night just visit indian sexy escorts

    ReplyDelete
  21. Great information and thanks for bringing this material.
    fortivacreditcard

    ReplyDelete

Note: Only a member of this blog may post a comment.