SQL Antipatterns ch12 Phantom Files
- Phantom Files
- Objective: Store Images or Other Bulky Media
- Antipattern: Assume You Must Use Files
- How to Recognize the Antipattern
- Legitimate Uses of the Antipattern
- Solution: Use BLOB Data Types As Needed
- 英語
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製品の多くがバックアップツールを提供している
- mysqldump (MySQL)
- rman (Oracle)
- pg_dump (PostgreSQL)
- .dump (SQLite)
- バックアップツールは
VARCHAR
がファイルパスを表していることなんか知らない - 外部ファイルのバックアップは別途行う必要がある
- トランザクションに追従しないことがここでも問題となる
- バックアップを開始した直後に外部画像ファイルが変更されるかもしれない
- DBのバックアップはバックアップ開始時点のレコード
- ファイルのバックアップは変更後のファイル
- トランザクションに追従しないことがここでも問題となる
Files Don't Obey SQL Access Privileges
- DB外のファイルに対しては、SQLの
GRANT
/REVOKE
による権限が適用されない
Files Are Not SQL Data Types
- ファイルパスはDBにとっては単なる文字列に過ぎない
- ファイルパスとしての正当性検証などはアプリケーションコードに記述しなければならない
- RDBの強みである「データ整合性の担保」が、ファイルパスに関しては享受できない
How to Recognize the Antipattern
- このアンチパターンを見つけるには、少しばかりの調査が必要
- ドキュメントをあたるか、設計者に聞いてみよ:
- バックアップ・リストア
- 手続きは?
- どうやって検証する?
- クリーンな環境や別サーバーでリストアのテストはしたか?
- 古くなった画像
- 蓄積する?削除する?
- 消す場合、どんな手続きで?
- 自動
- 手動
- アクセス権
- どのユーザが画像へのアクセスをもつ?
- どうやってアクセスする?
- アクセス権がない場合何が見える?
- ロールバック
- 画像への変更をキャンセルできるか?
- キャンセル時、アプリケーションは画像の状態を巻き戻すべきか?
- バックアップ・リストア
Legitimate Uses of the Antipattern
- 画像その他の巨大なファイルをDB外に置くメリット
- DBが小さくなる
- バックアップが速く、容量も小さくなる
- 画像のアドホックなプレビュー・編集が容易
- これらのメリットが重要で、先述の問題を飲み込めるなら、画像をDB外に置くのはアリ
- DB製品によっては外部ファイルを参照できるデータ型がある
- OracleのBFILE
- SQL Server 2008のFILESTREAM
- ケースバイケース
- BLOBカラム/DB外ファイルの一方を完全に排除してしまうべきではない
- 【補】「ファイルはDB外に置かなければならないと思うこと」がアンチパターン
- 一般論で考えるのではなく、アプリケーションごとに吟味せよ(意訳)
Solution: Use BLOB Data Types As Needed
- BLOBデータ型でファイルをテーブルに格納する
- 前述の問題を解決
- BLOBのファイルサイズは製品によりけり
- ファイル読み込み/書き出し連携できたりする
- MySQL
LOAD_FILE(ファイルパス)
関数SELECT INTO
文
- MySQL
英語
- deal-breakers
- 取引(deal)を破綻(break)させるもの
- = 飲み込めない条件