guix/store/schema.sql

   1 create table if not exists ValidPaths (
   2     id               integer primary key autoincrement not null,
   3     path             text unique not null,
   4     hash             text not null,
   5     registrationTime integer not null,
   6     deriver          text,
   7     narSize          integer
   8 );
   9
  10 create table if not exists Refs (
  11     referrer  integer not null,
  12     reference integer not null,
  13     primary key (referrer, reference),
  14     foreign key (referrer) references ValidPaths(id) on delete cascade,
  15     foreign key (reference) references ValidPaths(id) on delete restrict
  16 );
  17
  18 create index if not exists IndexReferrer on Refs(referrer);
  19 create index if not exists IndexReference on Refs(reference);
  20
  21 -- Paths can refer to themselves, causing a tuple (N, N) in the Refs
  22 -- table.  This causes a deletion of the corresponding row in
  23 -- ValidPaths to cause a foreign key constraint violation (due to `on
  24 -- delete restrict' on the `reference' column).  Therefore, explicitly
  25 -- get rid of self-references.
  26 create trigger if not exists DeleteSelfRefs before delete on ValidPaths
  27   begin
  28     delete from Refs where referrer = old.id and reference = old.id;
  29   end;
  30
  31 create table if not exists DerivationOutputs (
  32     drv  integer not null,
  33     id   text not null, -- symbolic output id, usually "out"
  34     path text not null,
  35     primary key (drv, id),
  36     foreign key (drv) references ValidPaths(id) on delete cascade
  37 );
  38
  39 create index if not exists IndexDerivationOutputs on DerivationOutputs(path);
  40
  41 create table if not exists FailedPaths (
  42     path text primary key not null,
  43     time integer not null
  44 );