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. شركة نقل عفش
    اهم شركات مكافحة حشرات بالخبر كذلك معرض اهم شركة مكافحة حشرات بالدمام والخبر والجبيل والخبر والاحساء والقطيف كذلك شركة رش حشرات بالدمام ومكافحة الحشرات بالخبر
    شركة مكافحة حشرات بالدمام
    شركة تنظيف خزانات بجدة الجوهرة من افضل شركات تنظيف الخزانات بجدة حيث ان تنظيف خزانات بجدة يحتاج الى مهارة فى كيفية غسيل وتنظيف الخزانات الكبيرة والصغيرة بجدة على ايدى متخصصين فى تنظيف الخزانات بجدة
    شركة تنظيف خزانات بجدة
    شركة كشف تسربات المياه بالدمام
    شركة نقل عفش واثاث

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

    ReplyDelete
  5. Thanks for sharing this marvelous post. I m very pleased to read this article.Sign in pogo

    ReplyDelete

  6. Before you plan to install antivirus software on your device, you are required to take
    few important steps to avoid software conflicts with the previously installed versions. please visit
    norton activation key
    or directly visit
    norton.com/setup .
    you can find more topics there.We love to help you.

    ReplyDelete
  7. Pogo support are giving a proper guidence how to fix your Gadgets Security.

    ReplyDelete
  8. It is easy to understand, detailed and meticulous! I have had a lot of harvest after watching this article from you! I feel it interesting, your post gave me a new perspective! I have read many other articles about the same topic, but your article convinced me! I hope you continue to have high quality articles like this to share with veryone!
    mac shortcuts

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

    ReplyDelete
  10. 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

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

  12. our Company is the best in P.R Management using Images, Videos, and stories. we have the
    connection with more than 10000 Blogger and Outreach managers to Provides you the
    best promotion and exposer.
    P.R Management

    ReplyDelete
  13. cara menggugurkan hamil kandungan dengan cepat dan akurat terbukti ampuh untuk melunturkan janin kehamilan muda 1 minggu hingga 1 , 2 , 3 dan 4 bulan
    obat penggugur kandungan adalah suatu bentuk proses berakhirnya kehamilan dengan dikeluarkannya janin ( fetus ) atau embrio sebelum memiliki kemampuan untuk berkembang dan bertahan hidup diluar rahim , sehingga bisa menyebabkan kematiannya pada janin
    Disini akan kami beritahukan kepada Anda langkah cara cepat menggugurkan kandungan dengan cepat dan selamat adalah KURET dan obat aborsi
    Kunyit memang tidak di baik di konsumsi oleh ibu hamil yang sedang hamil muda sehubungan dengan sifatnya untuk melancarkan haid , dan di percayai sebagai obat telat datang bulan
    jual obat aborsi paling manjur Misiprostol Cytotec asli terbukti ampuh melunturkan janin kuat tanpa kuret dengan bersih untuk usia 1 , 2 , 3 sampai 4 bulan dengan aman
    cara mencegah kehamilan Cara Mencegah Kehamilan saat berhubungan suami istri perlu perencanaan
    cara menggugurkan hamil

    ReplyDelete
  14. Thank you so much for giving pieces of information. If you have any problems with Setup & Configure Dlink Wireless Router. I can help you to easily Setup & Configure Dlink Wireless Router.to know how to visit here.Setup & Configure Dlink Wireless Router

    ReplyDelete
  15. 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
  16. Thanks for the study about how to
    access Netgear wireless router.
    If you furtherer face setting problem visit my page.

    ReplyDelete
  17. 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
  18. Petsability is the Best Online Pet Medicine Company. You can find Best joint supplements for dogs, probiotics for cats, cat separation anxiety, dog calming spray, pet vitamins and supplements, hip and joint supplements for dogs, Natural Medicines for Anxiety, Joint Pain, Digestion, and Kidney failure for Dogs & Cats. Shop Now

    ReplyDelete