1 pragma auto_vacuum=incremental;
2 create table if not exists blob(
4 last_used timestamp default (datetime('now')),
9 create view if not exists deletable_blob as
10 with recursive excess_blob(
16 select * from (select (select sum(length(data)) from blob) as usage_with, last_used, rowid, length(data) from blob order by last_used, rowid limit 1)
17 where usage_with >= (select value from setting where name='capacity')
19 select usage_with-data_length, blob.last_used, blob.rowid, length(data) from excess_blob join blob
20 on blob.rowid=(select rowid from blob where (last_used, rowid) > (excess.last_used, blob_rowid))
21 where usage_with >= (select value from setting where name='capacity')
22 ) select * from excess;
24 CREATE TRIGGER if not exists trim_blobs_to_capacity_after_update after update on blob begin
25 delete from blob where rowid in (select blob_rowid from deletable_blob);
27 CREATE TRIGGER if not exists trim_blobs_to_capacity_after_insert after insert on blob begin
28 delete from blob where rowid in (select blob_rowid from deletable_blob);