SQLアンチパターン～ファントムファイル
•
16 likes•9,985 views
社内SQLアンチパターン輪読会の資料、第11章ファントムファイル（幻のファイル）です。
SQLアンチパターン～ファントムファイル
- 10. 画像が表示されませんorz
- 11. 何故か！？
- 12. バックアップ対象 /var/upload/img/ データベース Hiyoko.jpg Roads.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg Ａ＝＝＝＝＝＝ ＝＝＝＝＝＝＝ ＝＝＝＝＝＝＝ ＝＝＝＝＝＝＝ アップロード アプリ
- 17. このアンチパターンに陥ると
- 18. /var/upload/img/ データベース Hiyoko.jpg Roads.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg ファイルの削除時の問題 この行を 削除しても 自動的には 削除されない
- 19. Aさんの処理 Bさんの処理 1.トランザクション開始 2.reportID:002のデータを 削除 3.Roads.jpgを削除 1.トランザクション開始 2.reportID:002のデータを参照 ・ 3.画像ファイルを参照 ・ ・ x.コミット /var/upload/img/ データベース Hiyoko.jpg Roads.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg トランザクション分離の問題 コメントは参照できるけど 画像が取得できない！？
- 20. Aさんの処理 1.トランザクション開始 2.reportID:002のデータを 削除 3.Roads.jpgを削除 例外発生 ロールバック! /var/upload/img/ データベース Hiyoko.jpg Roads.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg ロールバック時の問題 データベースのデータは 元に戻っても 画像は消えたまま
- 21. バックアップ バックアップ Aさんの処理 1.トランザクション開始 2.reportID:002のデータを 削除 3.Roads.jpgを削除 ・ ・ ・ x.コミット /var/upload/img/ データベース Hiyoko.jpg Roads.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg バックアップツール使用時における問題 reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg Hiyoko.jpg Penguis.jpg バックアップ ツール バックアップ コマンド トランザクションや 参照整合制約などを 考慮して矛盾なく バックアップ
- 22. /var/upload/img/ データベース Hiyoko.jpg Penguis.jpg reportID comment imagePath 001 XXXXXXX・・・ Penguins.jpg 002 XXXXXXX・・・ Roads.jpg 003 A======・・・ Hiyoko.jpg ファイルはSQLデータ型ではない SQLアクセス権限使用時における問題 単なる文字列 - 文字列であること以外に内容の保証はしない - BMSは実際のファイルが存在することの検証はし ない。 - ファイルが変更、削除、移動しても自動的に更新 されない。 - OSファイルの命名規則に従わない（大文字小文 字の区別や使用禁止文字）。 テーブルや列への アクセス権限とは無関係 OSファイルの適切なパーミッションを行う必要が ある。/var/www/html/配下などにファイルを保 存してしまうと予期せぬ参照が行われる可能性 がある。
- 23. アンチパターンの見つけ方
- 24. 次の質問への答えが曖昧であったり、即答できないようである ならば、きちんと検討されないまま、外部ファイルの使用が採用 されている可能性が高い。 • 「データバックアップとリストアの手順は確立されているか」、「バックアッ プの検証方法はどうか」、「クリーンサーバーやバックアップを実施した サーバーとは別サーバーで、データのリストアをテストしたか」 • 「画像が増える一方になるのではなく、不要になった時にシステムから削 除されるような仕組みになっているか」、「不要な画像ファイルを削除する 手順はあるか」、「その手順は自動化されているか、それとも手動か」 • 「画像ファイルへのアクセス権を持っているアプリケーションユーザは誰 か」、「アクセス権はどのように適用されているか」、「権限を持たない画 像を参照するリクエストをした場合、ユーザには何が表示されるか」 • 「画像に対する変更をキャンセルできるか」、「キャンセルできる場合、ア プリケーションは前の状態に復旧できるか」
- 26. アンチパターンを用いてよい場合
- 27. データベースの容量を減らせる
- 33. BLOB型を採用した場合、「アンチパターン」のすべての問題が 解決できる。 • 画像データはデータベースに格納されるので、読み込みのための追加の ステップは不要です。誤ったファイルパスを格納するというリスクもありま せん。 • 行を削除すると、自動的に画像も削除されます。 • 画像の変更は、コミットするまで、他のクライアントには見えません。 • トランザクションをロールバックすると、画像を以前の状態に復元できま す。 • 行の更新時は、ロックが行われます。このため、他のクライアントは同時 に同じ画像を更新できません。 • データベースのバックアップには、すべての画像が含まれます。 • SQLの権限管理によって、行と画像へのアクセスを管理できます。
- 34. Oracle MySQL PostgreSQL SQL Server BLOB型 最大4GB(9i) 最大128TB(10g) 最大64KB （※1） - - TINYBLOB型 - 最大255B - - MEDIUMBLOB型 - 最大16MB - - LONGBLOB型 - 最大4GB - - ラージオブジェクト - - 最大4GB - varbinary 型 - - - 最大2GB Image型 - - - 最大2GB Railsでカラムをbinaryで指定するとBLOB型（64KB）になる。64KB以上のオブジェクトを 保存したい場合は、limitで上限サイズを指定する。 ※1 ファイルを透過的に扱うためのデータ型としてBFILE(Oracle)やFILESTREAM(SQL Server)もある。尚、BFILEはトランザクション制御外である。 ※
- 40. CDP:Web Storageパターン ＆ CDP:URL Rewritingパターン ※ 但し、画像ファイルなどが第三者に参照されても問題ないか、ファイル名に連続性 がなく予測も難しい場合に限る。