プリザンターでBinaries_deletedテーブルに貯まり続けるゴミファイルを削除する方法

プリザンターのBinaryStorage.jsonのProviderをRdsで運用している場合にのみ効果がある方法です。Localで運用している場合は、本記事の方法では対応出来ません。

はじめに

プリザンターで、レコードは削除せずに添付ファイルだけ削除することありませんか？実はこの処理、プリザンター側の設定次第では、見ることも触ることも出来ないゴミデータを作ってしまいデータベースの肥大化の原因となることがあります。

どうして？

プリザンターでは、添付ファイルはBinariesテーブルに1ファイル1レコードの形で格納されています。ファイルの実体はBinフィールドにBASE64エンコードされて格納されています。
添付ファイルの削除を行ったとき、Binariesテーブルに格納されているレコードは、Binaries_deletedテーブルに移動されます。さて、移動された後のレコードは何に使われるか？と言われると、吊しの状態では何にも使われません。
添付ファイルの詳細設定を変更して「履歴に存在するファイルは削除しない」をONにすれば、履歴から添付ファイルにアクセスが出来きますが、吊しではこの機能はOFFなので、履歴からも見えなくなる=データベースに残っているけど触る手段がないゴミデータとなってしまいます。
レコード削除してゴミ箱から削除すると、ゴミ箱から削除するタイミングで、レコードに紐付くデータは全削除されるため、そのタイミングではデータは消えますが、添付ファイルの削除はするけどレコードは削除しない。ましてや削除してもゴミ箱からは消さないという運用は多いかなと思います。（現状のゴミ箱はサイトの管理権限がないと触れない＆時限削除の機能がないので、一般ユーザは操作する術がないため）

解決法

これは簡単に解決できます！
拡張SQLを使ってレコードに更新があったタイミングで、Binaries_deletedテーブルに格納されたレコードを削除してしまいます。これらをマニュアルに従ってファイルとして格納します。

DeletedBinariesOnUpdatedDelete.json

{
    "Description": "レコード更新後に削除済み添付ファイルをレコードごと消す",
    "OnUpdated": true,
    "CommandText": "-- Write an arbitrary SQL statement."
}

DeletedBinariesOnUpdatedDelete.json.sql

DELETE FROM
    [Binaries_deleted]
WHERE
    [ReferenceId] = {{Id}}

やっていることはとても簡単です。更新後にBinaries_deletedテーブルからレコードIDに対応するレコードをサクッと消しています。
このSQLでは全てのサイトで削除が実行されますが、任意でサイトIDの限定や、「履歴に存在するファイルは削除しない」を使用している場合で、それに該当するレコードだけ残しておくというもの可能です。サイトIDの限定については前述のマニュアルを参照してください。「履歴に存在するファイルは削除しない」がONの場合に除外する場合もSitesテーブルのSiteSettingsレコードと、Results_historyテーブルまたはIssues_histroyテーブルを組み合わせて条件を組み上げれば対応可能です¹が、本記事ではそこまではタッチしません。

まとめ

拡張SQLを使用することで、添付ファイルだけを削除した時に残るゴミファイルを削除することが出来るようになりました。データベースサイズは小さくしたいというニーズはあるかと思います。特にSQL ServerのExpress版を使用している場合、10GBのリミットがかけられているので効いてくるかと思います。
ただし、基本的に添付ファイルをいっぱい扱う場合は、どうしてもRdsモードで運用したい場合を除いては、Localモードでの運用することをオススメします。

1. RDBMS側でJSONが触れる必要があります。SQL ServerやPostgreSQLだといろいろと関数がサポートされているので扱いやすいです。詳しく知りたい方はお仕事お待ちしてます！ ↩