勉強日記

チラ裏

SQL Antipatterns ch12 Phantom Files

pragprog.com


Phantom Files

Resources outsize the database are not managed by the databases.

  • HDDがブッ壊れました
  • DBをリストアしました
  • が、画像ファイルが一切なくなりました
  • /varファイルシステムはバックアップしてなかったよ」

Objective: Store Images or Other Bulky Media

  • 画像やその他メディアをアプリケーションで使用する
    • エンティティに紐付けられる
    • バグトラッキングシステムなら不具合のスクショとか

Antipattern: Assume You Must Use Files

  • 概念的には、画像は表の1属性である
  • しかしBLOBカラムは議論を呼ぶ
    • 「ファイルパスをVARCHARで格納しろ」
  • DB外にファイルとして格納されるのが普通
  • が、そのリスクをあえて示してみる

Files Don't Obey DELETE

  • レコードだけ削除され、画像が取り残されることがありうる
    • 自動で同期するしくみはない

Files Don't Obey Transaction Isolation

  • トランザクションをCOMMITする前にファイルの変更は即時反映されてしまう
  • 並列処理時に異常が生じうる

Files Don't Obey ROLLBACK

  • トランザクション内でDELETEし、ROLLBACKするケース
  • DELETEの瞬間にアプリケーション側でファイルを削除するとする
  • ROLLBACKしてもファイルは復活しない

Files Don't Obey Database Backup Tools

  • DB製品の多くがバックアップツールを提供している
  • バックアップツールはVARCHARがファイルパスを表していることなんか知らない
  • 外部ファイルのバックアップは別途行う必要がある
    • トランザクションに追従しないことがここでも問題となる
      • バックアップを開始した直後に外部画像ファイルが変更されるかもしれない
      • DBのバックアップはバックアップ開始時点のレコード
      • ファイルのバックアップは変更後のファイル

Files Don't Obey SQL Access Privileges

  • DB外のファイルに対しては、SQLGRANT/REVOKEによる権限が適用されない

Files Are Not SQL Data Types

  • ファイルパスはDBにとっては単なる文字列に過ぎない
  • ファイルパスとしての正当性検証などはアプリケーションコードに記述しなければならない
  • RDBの強みである「データ整合性の担保」が、ファイルパスに関しては享受できない

How to Recognize the Antipattern

  • このアンチパターンを見つけるには、少しばかりの調査が必要
  • ドキュメントをあたるか、設計者に聞いてみよ:
    • バックアップ・リストア
      • 手続きは?
      • どうやって検証する?
      • クリーンな環境や別サーバーでリストアのテストはしたか?
    • 古くなった画像
      • 蓄積する?削除する?
      • 消す場合、どんな手続きで?
        • 自動
        • 手動
    • アクセス権
      • どのユーザが画像へのアクセスをもつ?
      • どうやってアクセスする?
      • アクセス権がない場合何が見える?
    • ロールバック
      • 画像への変更をキャンセルできるか?
      • キャンセル時、アプリケーションは画像の状態を巻き戻すべきか?

Legitimate Uses of the Antipattern

  • 画像その他の巨大なファイルをDB外に置くメリット
    • DBが小さくなる
    • バックアップが速く、容量も小さくなる
    • 画像のアドホックなプレビュー・編集が容易
  • これらのメリットが重要で、先述の問題を飲み込めるなら、画像をDB外に置くのはアリ
  • DB製品によっては外部ファイルを参照できるデータ型がある
  • ケースバイケース
    • BLOBカラム/DB外ファイルの一方を完全に排除してしまうべきではない
    • 【補】「ファイルはDB外に置かなければならないと思うこと」がアンチパターン
  • 一般論で考えるのではなく、アプリケーションごとに吟味せよ(意訳)

Solution: Use BLOB Data Types As Needed

  • BLOBデータ型でファイルをテーブルに格納する
  • 前述の問題を解決
    • ファイルパスが不正であるというリスクがなくなる
    • 行を削除すれば自動的にファイルも消える
    • トランザクション内での画像への変更は、COMMITするまで他のトランザクションから見えない
    • ロールバックすれば画像も巻き戻る
    • 更新時にロックをかけられる
    • DBバックアップに画像も含まれる
    • SQLのアクセス権が画像にも適用される
  • BLOBのファイルサイズは製品によりけり
    • が、大抵の画像には十分
    • 同族のバリエーションがあったり
  • ファイル読み込み/書き出し連携できたりする
    • MySQL
      • LOAD_FILE(ファイルパス)関数
      • SELECT INTO

英語

  • deal-breakers
    • 取引(deal)を破綻(break)させるもの
    • = 飲み込めない条件