storage/sqlite/init.sql

   1 -- We have to opt into this before creating any tables, or before a vacuum to enable it. It means we
   2 -- can trim the database file size with partial vacuums without having to do a full vacuum, which
   3 -- locks everything.
   4 pragma auto_vacuum=incremental;
   5
   6 create table if not exists blob (
   7     name text,
   8     last_used timestamp default (datetime('now')),
   9     data blob,
  10     primary key (name)
  11 );
  12
  13 create table if not exists blob_meta (
  14     key text primary key,
  15     value
  16 );
  17
  18 create index if not exists blob_last_used on blob(last_used);
  19
  20 -- While sqlite *seems* to be faster to get sum(length(data)) instead of
  21 -- sum(length(data)), it may still require a large table scan at start-up or with a
  22 -- cold-cache. With this we can be assured that it doesn't.
  23 insert or ignore into blob_meta values ('size', 0);
  24
  25 create table if not exists setting (
  26     name primary key on conflict replace,
  27     value
  28 );
  29
  30 create table if not exists tag (
  31     blob_name references blob(name),
  32     tag_name,
  33     value,
  34     primary key (blob_name, tag_name)
  35 );
  36
  37 create view if not exists deletable_blob as
  38 with recursive excess (
  39     usage_with,
  40     last_used,
  41     blob_rowid,
  42     data_length
  43 ) as (
  44     select *
  45     from (
  46         select
  47             (select value from blob_meta where key='size') as usage_with,
  48             last_used,
  49             rowid,
  50             length(data)
  51         from blob order by last_used, rowid limit 1
  52     )
  53     where usage_with > (select value from setting where name='capacity')
  54     union all
  55     select
  56         usage_with-data_length as new_usage_with,
  57         blob.last_used,
  58         blob.rowid,
  59         length(data)
  60     from excess join blob
  61     on blob.rowid=(select rowid from blob where (last_used, rowid) > (excess.last_used, blob_rowid))
  62     where new_usage_with > (select value from setting where name='capacity')
  63 )
  64 select * from excess;