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でマッピングされづらいものなので、過去の操作を取り扱うことが主目的の場合には、無理に正規化を行わない方が都合が良いケースが多いと考えられます。

54 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. This comment has been removed by the author.

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

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

    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. This comment has been removed by the author.

    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. This comment has been removed by the author.

    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
  19. Top 12 Fastest Trains in the World Here are the world’s fastest trains in commercial service, ranked by their operating speed Top 12 Fastest Trains in the World

    ReplyDelete
  20. أفضل شركة نقل في المدينة المنورة ونجارين مبهورين في عملية التفكيك والتركيب من الأثاث والأثاث.

    ReplyDelete
  21. Webroot.com/safe is a protection software solution that communicates with the cloud avoiding the hassle to manage the signature updates to deploy. for office setup visit office.com/setup

    ReplyDelete
  22. Using the Images Background Remover service, you can get the best possible solutions. The photos will be livelier and you can use them anywhere. The importance of Background Remover Online is undeniable for every photo. Before we move to the benefits of having the service, it is better to know about the service.

    ReplyDelete
  23. 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
  24. We are an independent company who providing technical support for for Norton products.If you are Facing problems your Norton products or having any issues during the work do call us and our company will fix your problem and you need any suggestion regarding any Norton product feel-free to call us on our toll-free numbers for further information.
    www.norton.com/setup

    ReplyDelete
  25. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information.
    norton.com/setup

    ReplyDelete
  26. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information.

    norton.com/setup
    mcafee.com/activate
    office.com/setup

    ReplyDelete
  27. 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.
    Packers and Movers in Kharghar
    Packers and Movers in Dombivli
    Packers and Movers in Thane West

    ReplyDelete
  28. 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
  29. 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
  30. 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
  31. Indian Packers and Movers in Mumbai give packing and moving services like loading & unloading, packing & unpacking, car carriers, transportation, domestic &local shifting, international shifting, Industrial shifting, corporate shifting, Warehousing, etc.
    Movers and Packers in Mumbai
    Packers and Movers in Khar Road
    Packers and Movers in Vile parle
    Packers and Movers in Andheri
    Packers and Movers in Jogeshwari

    ReplyDelete
  32. This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information.

    | norton.com/setup |
    | mcafee.com/activate |
    | office.com/setup |

    ReplyDelete
  33. We the biggest Photoshop Service As images of products are the biggest weapon for a business company. Perfect photoshop service means a lot to this professional business purpose. Do Visit to have more knowledge about unique service.

    ReplyDelete
  34. Banner Design Service is a focusing fact for any kinds of business institutions. Banner show your products identity which is provided by you. Manage your banner design service creatively by any professional designer to highlight your products. Creative banner grabs your customer’s attention.

    ReplyDelete
  35. Clipping path is a must for any kinds of product images. Clipping Path Service can provide you with your desired product with a high-end view which will help you to attract both the organic customers and paid to add customers. Do clipping path for your business growth and show your customers what you have!

    ReplyDelete
  36. Image retouching service is on of the great services of Adobe Photoshop service. Removing spots or blemishes from images, as well as more advanced color correction work. We can remove unsightly marks, stain,Image retouching fold from any surface and ensure that your images are perfect in every way. We can eliminate the irregularities from the skin and also adjust the image color shades or erase it. We do this by creating an additional selection (path or a mask) of the model’s skin (face, hands, legs, etc.) Our designers can measure and correct the color values. Clone and Spot Healing tools remove irregularities. We can reduce and eliminate bags under the eyes, pimples, tattoos, birthmarks, etc

    ReplyDelete
  37. Creative Banner is a focusing fact for any kinds of business institutions. Banner show your products identity which is provided by you. Manage your banner design service creatively by any professional designer to highlight your products. Creative banner grabs your customer’s attention.

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

  39. Creative Banner is a focusing fact for any kinds of business institutions. Banner show your products identity which is provided by you. Manage your banner design service creatively by any professional designer to highlight your products. Cr eative banner grabs your customer’s attention.

    ReplyDelete
  40. Here is a good Weblog You might Come across Fascinating that we Encourage You
    เว็บไซต์คาสิโนออนไลน์ที่ได้คุณภาพอับดับ 1 ของประเทศ
    เป็นเว็บไซต์การพนันออนไลน์ที่มีคนมา สมัคร Gclub Royal1688
    และยังมีหวยให้คุณได้เล่น สมัครหวยออนไลน์ ได้เลย
    สมัครสมาชิกที่นี่ >>> Gclub Royal1688
    ร่วมลงทุนสมัครเอเย่นคาสิโนกับทีมงานของเราได้เลย

    ReplyDelete
  41. Enjoyed studying this, very good stuff, thanks.
    โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร Gclub กับทางทีมงานของเราเพียงเท่านั้น
    ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
    สมัครสล็อตออนไลน์ >>> goldenslot
    สนใจร่วมลงทุนกับเรา สมัครเอเย่น Gclub คลิ๊กได้เลย

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

    ReplyDelete


  43. 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
  44. Great information and thanks for bringing this material.
    fortivacreditcard

    ReplyDelete

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