【sqlite3】deleteしても.dbファイルのサイズは減らない？

質問

ＤＢ及びＳＱＬに詳しい方、教えてください。

sqliete3を使ってます。
insertしたレコードをdeleteしても.dbファイルのサイズは小さくならない
様なので、テーブルのカラムに削除フラグを設けて、削除はdeleteコマン
ドでレコードを削除する代わりに、フラグのupdateとし、
レコード挿入の場合は、フラグが立ってるレコードを見つけて、そのコー
ドを更新（フラグが立ってるレコードが無い場合はinsert）とする仕様に
してみようかと考えてます。
もちろんselect時は、フラグの立ってるレコードをwhere句で除外するつも
りです。

そこで、質問なのですが、フラグの立ってるレコードを一つselectして該
当レコードをupdateするには、どのようなＳＱＬになるでしょうか？

フラグが立つレコードは複数もしくは無い事が考えられるので、例えば、
update table01 set col = 'hogehoge' where flag = 1;
では上手く機能しないと考えます。
複数レコードが選択された場合は、任意のレコードを選んで更新、また、
レコードが一つも選択されなかった場合の条件判定(後のinnsert)は、
どのように記述したらよいのでしょうか？

※そもそも、そんな事しなくても他にもっと良い方法があるよ。
　といった場合は、その方法を伝授してください。

それでは、ご指導のほど、宜しくお願いいたします。

回答No.1

mattalix · ベストアンサー

素直にvacuum

補足

mattalixさん　ご回答有難うございます。

vacuum知りませんでした。

deleteの後のトリガーに登録するんですか？

回答No.2

copymaster

mattalixさんが回答されているとおりですが、
SQLite、PostgreSQLなどの追記型のデータベースは、deleteで論理削除し、vacuumで物理削除するしくみです。
(ちなみに、PostgreSQLは、
vaccumで、deleteを再利用可能領域に変換する=>ファイルサイズ変更なし、
vaccum fullで、ファイルサイズ詰めです。SQLiteは、vaccumでファイル
サイズも小さくなります。）

「deleteの後のトリガーに登録」？ということについては、
SQLiteをどのようなケースでご利用されているか、
（デスクトップアプリケーションの組込みDBか、Webアプリケーションのバックエンドか）わかりませんが、
deleteした時に、物理ファイル内のデータも削除しなくてはならないなど
特別な理由がない限り、vacuumの処理は、deleteのタイミングごとに行うというよりも、むしろデータベースのメンテナンスとして定期的にバッチ処理で行うのが一般的かと思います。
（vaccum処理対象のレコード数が多いと、それなりに処理に時間がかかるためです。）
つまり、デスクトップアプリケーションならば、アプリ起動時や終了時に自動実行するか、ユーザがメニューから「最適化」処理として呼び出せるようにする、Webアプリケーションならば、サイトのメンテナンス日などにスケジューラで実行させる(Linuxならばcron、Windowsならばタスクから、vaccum処理を記載したプログラムを呼び出す)などです。

補足

copymasterさん、ご回答有難うございました。

＞（vaccum処理対象のレコード数が多いと、それなりに処理に時間がかかるためです。）

つまりは、sqlite3はvacuum処理中はトランザクション（selectやupdate,delete）を受け付けないのでしょうか？