-- $HeadURL$ $LastChangedRevision$

------------------------------------------------------------------------------
--
--  Schema version
--
------------------------------------------------------------------------------

pragma user_version = 2;

------------------------------------------------------------------------------
--
--  Table pkgtypes
--
------------------------------------------------------------------------------

create table pkgtypes (
    -- Primary key columns
    pkgtype            char not null,
    -- Other columns
    -- Primary keys
    primary key (pkgtype)
    -- Foreign keys (those commented out are implemented as triggers)
    -- Constraints (those commented out are implemented as triggers)
);
-- Other triggers
-- Static data
insert into pkgtypes (pkgtype) values ('deb');
insert into pkgtypes (pkgtype) values ('rpm');

------------------------------------------------------------------------------
--
--  Table script_archs
--
------------------------------------------------------------------------------

create table script_archs (
    -- Primary key columns
    pkgtype            char not null,
    -- Other columns
    arch               char not null,
    -- Primary keys
    primary key (pkgtype)
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (pkgtype)   references pkgtypes(pkgtype)
    -- Constraints (those commented out are implemented as triggers)
);
-- Other triggers
-- Static data
insert into script_archs(pkgtype,arch) values ('deb','all');    -- this means there exist *_all.deb files
insert into script_archs(pkgtype,arch) values ('rpm','noarch'); -- this means there exist *_noarch.rpm files

------------------------------------------------------------------------------
--
--  Table logicals
--
------------------------------------------------------------------------------

create table logicals (
    -- Primary key columns
    logical            char not null,
    -- Other columns
    -- Primary keys
    primary key (logical),
    -- Foreign keys (those commented out are implemented as triggers)
    -- Constraints (those commented out are implemented as triggers)
    constraint block_inserting_illegal_value check(logical == 'true' or logical == 'false')
);
-- Other triggers
-- Static data
insert into logicals (logical) values ('true');
insert into logicals (logical) values ('false');
    
------------------------------------------------------------------------------
--
--  Table repotypes
--
------------------------------------------------------------------------------

create table repotypes (
    -- Primary key columns
    repotype           char not null,
    -- Other columns
    -- Primary keys
    primary key (repotype),
    -- Foreign keys (those commented out are implemented as triggers)
    -- Constraints (those commented out are implemented as triggers)
    constraint block_inserting_illegal_value check(repotype == 'owned' or repotype == 'mirrored' or repotype == 'accessed')
);
-- Other triggers
-- Static data
insert into repotypes (repotype) values ('owned');    -- this means owned (and mirrored and accessed)
insert into repotypes (repotype) values ('mirrored'); -- this means not owned but mirrored (and accessed)
insert into repotypes (repotype) values ('accessed'); -- this means not owner nor mirrored but accessed
    
------------------------------------------------------------------------------
--
--  Table statuses
--
------------------------------------------------------------------------------

create table statuses (
    -- Primary key columns
    status           char not null,
    -- Other columns
    -- Primary keys
    primary key (status),
    -- Foreign keys (those commented out are implemented as triggers)
    -- Constraints (those commented out are implemented as triggers)
    constraint block_inserting_illegal_value check(status == 'available' or status == 'deleting' or status == 'updating' or status == 'pending')
);
-- Other triggers
-- Static data
insert into statuses (status) values ('available');  -- this means all ok
insert into statuses (status) values ('deleting');   -- this means deletion process started and running
insert into statuses (status) values ('updating');   -- this means create/edit process started and running
insert into statuses (status) values ('pending');    -- this means edit edit process required and not running
    
------------------------------------------------------------------------------
--
--  Table repos
--
------------------------------------------------------------------------------

create table repos (
    -- Primary key columns
    repo               char not null, 
    -- Other columns
    status             char not null, 
    pid                integer,
    pkgtype            char, 
    repotype           char, 
    is_distro          char,
    config_gz          blob, 
    config_compiled_ts integer,
    repoed_ts          integer,
    -- Primary keys
    primary key (repo),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (status)    references statuses(status),
    foreign key (pkgtype)   references pkgtypes(pkgtype),
    foreign key (repotype)  references repotypes(repotype),
    foreign key (is_distro) references logicals(logical),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check(status != 'available' or pkgtype   not null),
    constraint block_availing_incomplete_record check(status != 'available' or is_distro not null),
    constraint block_availing_incomplete_record check(status != 'available' or repotype  not null),
    constraint block_availing_incomplete_record check(status != 'available' or repoed_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
    -- Constraint pre_delete_check_mirrors check(count(mirrors.repo) == 0)
    -- Constraint pre_delete_check_hosts   check(count(hosts.distro) ... == 0)
    
);
-- Other triggers
create trigger block_repo_delete_while_repo_is_referenced_by_mirror before update of status on repos 
    when ((select count(*) from mirrors where mirrors.repo == old.repo and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: a mirror is referencing this repo');
    end;
create trigger block_repo_delete_while_repo_is_referenced_by_access before update of status on repos 
    when ((select count(*) from accesses where accesses.repo == old.repo and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: an access is referencing this repo');
    end;
-- constraints will block these anyway, but this makes the message nicer
create trigger block_repo_insert_with_bad_pkgtype before update of pkgtype on repos 
    when ((select count(*) from pkgtypes where pkgtypes.pkgtype == new.pkgtype) == 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: illegal package type');
    end;
create trigger block_repo_insert_with_bad_repotype before update of repotype on repos 
    when ((select count(*) from repotypes where repotypes.repotype == new.repotype) == 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: illegal repo type');
    end;
create trigger block_repo_insert_with_bad_is_distro before update of is_distro on repos 
    when ((select count(*) from logicals where logicals.logical == new.is_distro) == 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: illegal is-distro flag');
    end;
-- Static data

------------------------------------------------------------------------------
--
--  Table extendeds (non-core and type-specific repo attributes)
--
------------------------------------------------------------------------------

create table extendeds (
    -- Primary key columns
    repo               char not null,
    -- Other columns
    path               char,       -- owned
    mirror_cmd         char,       -- mirrored
    mirror_dir         char,       -- owned or mirrored
    freeze_dir         char,       -- owned or mirrored
    indirect_dir       char,       -- owned or mirrored
    url                char,       -- any
    cidr               char,       -- owned or mirrored
    distro             char,       -- any and is_distro
    origin             char,       -- owned and deb
    label              char,       -- owned and deb
    signer             char,       -- owned and deb
    -- Primary keys
    primary key (repo),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo) references repos(repo),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_inserting_incomplete_record check(url not null)
    --constraint ... There are many similar constraints for the other fields, but they all require
    -- referring to fields in repos to know whether they should be applied or not. So they have been
    -- implemented as triggers.
);
create trigger block_repoconfig_insert_while_mirror_cmd_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) == 'mirrored' and new.mirror_cmd isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: mirror_cmd required but not provided');
    end;
create trigger block_repoconfig_insert_while_path_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) == 'owned' and new.path isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: path required but not provided');
    end;
create trigger block_repoconfig_insert_while_mirror_dir_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) != 'accessed' and new.mirror_dir isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: mirror_dir required but not provided');
    end;
create trigger block_repoconfig_insert_while_freeze_dir_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) != 'accessed' and new.freeze_dir isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: freeze_dir required but not provided');
    end;
create trigger block_repoconfig_insert_while_indirect_dir_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) != 'accessed' and new.indirect_dir isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: freeze_dir required but not provided');
    end;
create trigger block_repoconfig_insert_while_cidr_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) != 'accessed' and new.cidr isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: cidr required but not provided');
    end;
create trigger block_repoconfig_insert_while_origin_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) == 'owned' and (select pkgtype from repos where repos.repo == new.repo) == 'deb' and new.origin isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: origin required but not provided');
    end;
create trigger block_repoconfig_insert_while_label_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) == 'owned' and (select pkgtype from repos where repos.repo == new.repo) == 'deb' and new.label isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: label required but not provided');
    end;
create trigger block_repoconfig_insert_while_signer_not_specified before insert on extendeds
    when ((select repotype from repos where repos.repo == new.repo) == 'owned' and (select pkgtype from repos where repos.repo == new.repo) == 'deb' and new.signer isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: signer required but not provided');
    end;
create trigger block_repoconfig_insert_while_distro_not_specified before insert on extendeds
    when ((select is_distro from repos where repos.repo == new.repo) == 'true' and new.distro isnull)
    begin
        select raise(abort, 'REPORT_TO_USER: distro required but not provided');
    end;
-- Other triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table releases (release-specific repo attributes)
--
------------------------------------------------------------------------------

create table releases (
    -- Primary key columns
    repo               char not null,
    release            char not null,
    -- Other columns
    status             char not null,
    layout             char,
    released_ts        integer,
    -- Primary keys
    primary key (repo,release),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo)   references repos(repo),
    foreign key (status) references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_releasing_incomplete_record check (status != 'available' or layout not null),
    constraint block_releasing_incomplete_record check (status != 'available' or released_ts not null)
);
-- Other triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table ports (port-specific repo attributes)
--
------------------------------------------------------------------------------

create table ports (
    -- Primary key columns
    repo               char not null,
    release            char not null,
    port           char not null,
    -- Other columns
    -- Primary keys
    primary key (repo,release, port),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo,release) references releases(repo,release)
    -- Constraints (those commented out are implemented as triggers)
);
-- Other triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table ports (section-specific repo attributes)
--
------------------------------------------------------------------------------

create table sections (
    -- Primary key columns
    repo               char not null,
    release            char not null,
    section            char not null,
    -- Other columns
    -- Primary keys
    primary key (repo,release,section),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo,release) references releases(repo,release)
    -- Constraints (those commented out are implemented as triggers)
);
-- Other triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table compatibilities
--
------------------------------------------------------------------------------

create table compatibilities (
    -- Primary key columns
    repo               char not null,
    release            char not null,
    pkgtype            char not null,
    distro             char,               -- null means not relevant
    distro_release     char,               -- null means not relevant
    -- Other columns
    -- Primary keys
    primary key (repo, release, pkgtype, distro, distro_release),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo,release) references releases(repo,release)
    -- This one *can* be done implemented here, but for symmetry we group it with two others
    -- that can't (that in turn have been rolled in to one trigger).
    --foreign key (pkgtype) references pkgtypes(pkgtype)
    --foreign key (repo,'false') references repos(repo,is_distro),
    --foreign key (pkgtype,distro) references select pkgtype,distro from repos,extendeds
    --foreign key (pkgtype,distro,release) references select pkgtype,distro,release from repos,extendeds,releases
    -- Constraints (those commented out are implemented as triggers)
    --constraint compatibilities_distro_repo       check(extendeds.is_distro is true where extendeds.repo == distro_repo)
);
-- Only non-distros have compatibilities
create trigger block_compatibilities_insert_while_is_distro_true before insert on compatibilities 
    when ((select is_distro from repos where repos.repo == new.repo) == 'true') 
    begin
        select raise(abort, 'REPORT_TO_USER: distro repos may not have compatibilities');
    end;
-- Other triggers

------------------------------------------------------------------------------
--
--  Table mirrors
--
------------------------------------------------------------------------------

create table mirrors (
    -- Primary key columns
    repo               char not null,
    -- Other columns
    status             char not null,
    pid                integer,
    mirrored_ts        integer,
    -- Primary keys
    primary key (repo),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo)   references repos(repo),
    foreign key (status) references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
);
-- Other triggers
create trigger block_mirror_update_while_freeze_update_in_progress before update of status on mirrors 
    when ((select count(*) from freezes where freezes.repo == new.repo and freezes.status == 'updating') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: freeze update in progress');
    end;
create trigger block_mirror_delete_while_mirror_is_referenced_by_freeze before update of status on mirrors 
    when ((select count(*) from freezes where freezes.repo == old.repo and new.status == 'deleting') != 0) 
    begin
        -- freezes have FK relationships to mirrors *only* in the repo column, not the mirrored_ts column.
        -- So there is no *real* dependendency of a freeze on its mirror (the reason it's not enforced
        -- is because the timestamp of the mirror will change when the mirror is updated). But we do 
        -- want to prevent the entire mirror being wiped out while there is still a freeze of the mirror
        -- (as it was at *some* time in the past).  So "this" in this error message is a bit of a lie.
        select raise(abort, 'REPORT_TO_USER: a freeze is referencing this mirror');
    end;
-- Static data

------------------------------------------------------------------------------
--
--  Table freezes
--
------------------------------------------------------------------------------

create table freezes (
    -- Primary key columns
    repo               char     not null,
    freeze             integer  not null,
    -- Other columns
    status             char     not null,
    pid                integer,
    frozen_ts          integer,
    -- Primary keys
    primary key (repo, freeze),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo)   references mirrors(repo),  -- FK to mirrors (rather than repos) ensures repo *is* mirrored
    foreign key (status) references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check (status != 'available' or frozen_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
    -- Constraint: *only* at moment of insertion there is an FK freezes.freeze references (or rather: is a function of)
    -- mirrors.mirrored_ts (is a function of, because the mirrored_ts gets reformatted from seconds since epoch to
    -- YYMMDDHHMMSS, which effectively makes it no longer a FK relationship).
);
-- Other triggers
create trigger block_freeze_insert_while_mirror_update_in_progress before insert on freezes 
    when ((select count(*) from mirrors where mirrors.repo == new.repo and mirrors.status == 'updating') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: mirror update in progress');
    end;
create trigger block_freeze_delete_while_freeze_is_referenced_by_indirect before update of status on freezes 
    when ((select count(*) from indirects where indirects.repo == old.repo and indirects.freeze == old.freeze and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: a indirect is referencing this freeze');
    end;
-- Static data

------------------------------------------------------------------------------
--
--  Table hosts
--
------------------------------------------------------------------------------

create table hosts (
    -- Primary key columns
    host               char not null,
    -- Other columns
    status             char not null,
    pid                integer,
    pkgtype            char,
    distro             char,
    release            char,
    port               char,
    hosted_ts          integer,
    -- Primary keys
    primary key (host),
    -- Foreign keys (those commented out are implemented as triggers)
    -- There is no 'foreign key (distro,release,port) references distroports(distro,release,port)' because hosts
    -- can be inserted *ahead* of their distros. Such hosts will be resolved (or rather ignored) at 
    -- indirect-time.
    foreign key (status)  references statuses(status),
    foreign key (pkgtype) references pkgtypes(pkgtype),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check (status != 'available' or pkgtype   not null),
    constraint block_availing_incomplete_record check (status != 'available' or distro    not null),
    constraint block_availing_incomplete_record check (status != 'available' or release   not null),
    constraint block_availing_incomplete_record check (status != 'available' or port      not null),
    constraint block_availing_incomplete_record check (status != 'available' or hosted_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
);
-- Triggers
create trigger block_host_delete_while_host_is_referenced_by_indirect before update of status on hosts 
    when ((select count(*) from indirects where indirects.host == old.host and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: a indirect is referencing this host');
    end;
create trigger block_host_delete_while_host_is_referenced_by_access before update of status on hosts 
    when ((select count(*) from accesses where accesses.host == old.host and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: an access is referencing this host');
    end;
-- The above FK contraint on pkgtype will stop the user from inserting a host with a bad package type,
-- but the error message just says 'database error: near line 11: foreign key constraint failed', which
-- we here customise.
create trigger custom_error_message_for_bad_pkgtype_1 before insert on hosts
    when new.pkgtype notnull and (select count(*) from pkgtypes where new.pkgtype isnull or pkgtypes.pkgtype == new.pkgtype) == 0
    begin
        select raise(abort, 'REPORT_TO_USER: invalid pkgtype');
    end;
create trigger custom_error_message_for_bad_pkgtype_2 before update of pkgtype on hosts
    when new.pkgtype notnull and (select count(*) from pkgtypes where new.pkgtype isnull or pkgtypes.pkgtype == new.pkgtype) == 0
    begin
        select raise(abort, 'REPORT_TO_USER: invalid pkgtype');
    end;
-- Static data

------------------------------------------------------------------------------
--
--  Table indirects
--
------------------------------------------------------------------------------

create table indirects (
    -- Primary key columns
    repo               char not null,
    host               char not null,
    -- Other columns
    status             char not null,
    pid                integer,
    freeze             integer,
    indirected_ts      integer,
    -- Primary keys
    primary key (repo,host),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo,freeze) references freezes(repo,freeze),
    foreign key (host)        references hosts(host),
    foreign key (status)      references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check (status != 'available' or freeze        not null),
    constraint block_availing_incomplete_record check (status != 'available' or indirected_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
);
-- Triggers
create trigger block_indirect_delete_while_indirect_is_referenced_by_share before update of status on indirects 
    when ((select count(*) from shares where shares.repo == old.repo and shares.host == old.host and new.status == 'deleting') != 0) 
    begin
        select raise(abort, 'REPORT_TO_USER: a share is referencing this indirect');
    end;
-- Static data

------------------------------------------------------------------------------
--
--  View release_distro_compatibilities
--
------------------------------------------------------------------------------

-- The 'indirect' directive only needs to see the repository and repo attributes
-- relevant for working out compatibility (i.e. pkgtype, distro, release and port).
-- But the 'access' directive needs that *and* it needs to know the repo's
-- release that is compatible and the repo pkgtype, because it will need to
-- generate apt/rpm config files and those things mention those attributes.
-- For this reason, we select these extra attributes; 'indirect' will not make
-- use of them; 'access' will.

-- From where we assemble this compatibility info differs for whether the 
-- repo is for an distro or not. First distros:

create view release_distro_compatibilities_distros as 
    -- the OS repo/release ...
    select repos.repo                     as repo,
           ports.release                  as release,
    -- ... (and here's some extra info you'll need about it) ...
           repos.pkgtype                  as pkgtype,
    -- is compatible with hosts with the following distro installed:
           repos.pkgtype                  as distro_pkgtype,
           extendeds.distro               as distro,
           release                        as distro_release,
           port                           as distro_port 
    from repos,extendeds,ports 
    where repos.repo    == extendeds.repo          and 
          repos.repo    == ports.repo              and 
          status        == 'available'             and
          is_distro     == 'true';

-- Next non-distros:

create view release_distro_compatibilities_non_distros as 
    -- the non-OS repo/release ...
    select repos.repo                     as repo,
           ports.release                  as release,
    -- ... (and here's some extra info you'll need about it) ...
           repos.pkgtype                  as pkgtype,
    -- is compatible with hosts with the following distro's repo installed:
    -- (wait! shouldn't that be just "with the following distro installed"?)
           compatibilities.pkgtype        as distro_pkgtype,
           compatibilities.distro         as distro,
           compatibilities.distro_release as distro_release,
           ports.port                     as distro_port 
    from repos,extendeds,ports,compatibilities 
    -- no need to check that the repo is not a distro because we're taking this info from the compatibilities table and that *only* contains non-distros.
    where status        == 'available'             and
          repos.repo    == ports.repo              and
          repos.repo    == extendeds.repo          and
          repos.repo    == compatibilities.repo    and
          ports.release == compatibilities.release;

-- Finally we combine those into one lookup table:

create view release_distro_compatibilities as
    select * from release_distro_compatibilities_distros union
    select * from release_distro_compatibilities_non_distros;

------------------------------------------------------------------------------
--
--  View port_file_compatibilities
--
------------------------------------------------------------------------------

-- The 'insert' directive needs to know into which repo/release/port/sections
-- a particular file may be inserted. This view provides this information.

create view port_file_compatibilities_binary_archs as 
    select repos.repo       as repo,
           releases.release as release,
           ports.port       as port,
           sections.section as section,
           repos.pkgtype    as pkgtype,
           ports.port       as arch
    from repos,releases,sections,ports 
    where repos.repo       == releases.repo    and 
          repos.repo       == sections.repo    and 
          releases.release == sections.release and 
          repotype         == 'owned'          and 
          repos.status     == 'available'      and 
          ports.repo       == repos.repo       and 
          ports.release    == releases.release;

create view port_file_compatibilities_script_archs as 
    select repos.repo        as repo,
           releases.release  as release,
           ports.port        as port,
           sections.section  as section,
           repos.pkgtype     as pkgtype,
           script_archs.arch as arch
    from repos,releases,sections,ports,script_archs  
    where repos.repo       == releases.repo    and
          repos.repo       == sections.repo    and
          releases.release == sections.release and
          repos.repo       == ports.repo       and
          releases.release == ports.release    and
          repotype         == 'owned'          and
          repos.status     == 'available'      and
          repos.pkgtype    == script_archs.pkgtype;

create view port_file_compatibilities as 
    select * from port_file_compatibilities_binary_archs union 
    select * from port_file_compatibilities_script_archs;

------------------------------------------------------------------------------
--
--  Table shares
--
------------------------------------------------------------------------------

create table shares (
    -- Primary key columns
    repo               char not null,
    host               char not null,
    -- Other columns
    status             char not null,
    pid                integer,
    shared_ts          integer,
    -- Primary keys
    primary key (repo,host),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo,host) references indirects(repo,host),
    foreign key (status)    references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check (status != 'available' or shared_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
);
-- Triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table accesses
--
------------------------------------------------------------------------------

create table accesses (
    -- Primary key columns
    repo               char not null,
    -- Other columns
    status             char not null,
    pid                integer,
    host               char,
    accessed_ts        integer,
    -- Primary keys
    primary key (repo),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo)   references repos(repo),
    foreign key (host)   references hosts(host),
    foreign key (status) references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_availing_incomplete_record check (status != 'available' or host        not null),
    constraint block_availing_incomplete_record check (status != 'available' or accessed_ts not null),
    constraint block_availing_incomplete_record check(((status = 'available' or status = 'pending') and pid isnull) or ((status != 'available' and status != 'pending') and pid not null))
);
-- Triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table files
--
------------------------------------------------------------------------------

create table files (
    -- Primary key columns
    file               char not null,
    -- Other columns
    status             char not null,
    pkgtype            char,
    name               char,
    arch               char,      -- can even be of port for which there are no repos hence no FK
    version            char,
    dir                char,
    filed_ts           integer,
    -- Primary keys
    primary key (file),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (pkgtype) references pkgtypes(pkgtype),
    foreign key (status)  references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_filing_incomplete_record check (status != 'available' or pkgtype  not null),
    constraint block_filing_incomplete_record check (status != 'available' or name     not null),
    constraint block_filing_incomplete_record check (status != 'available' or arch     not null),
    constraint block_filing_incomplete_record check (status != 'available' or version  not null),
    constraint block_filing_incomplete_record check (status != 'available' or dir      not null),
    constraint block_filing_incomplete_record check (status != 'available' or filed_ts not null)
);
-- Triggers
-- Static data

------------------------------------------------------------------------------
--
--  Table controls
--
------------------------------------------------------------------------------

create table controls (
    -- Primary key columns
    repo               char not null,
    release            char not null,
    port               char not null,
    name               char not null,
    version            char not null,
    -- Other columns
    status             char not null,
    section            char,
    dir                char,
    file               char,
    controlled_ts      integer,
    -- Primary keys
    primary key (repo, release, port, name, version),
    -- Foreign keys (those commented out are implemented as triggers)
    foreign key (repo)              references repos(repo),
    -- These next three are commented out because they block repoedit (which deletes from releases
    -- and ports before repopulating them)
    -- foreign key (repo,release)      references releases(repo,release),
    -- foreign key (repo,release,port) references ports(repo,release,port),
    -- foreign key (status)            references statuses(status),
    -- Constraints (those commented out are implemented as triggers)
    constraint block_filing_incomplete_record check (status != 'available' or section       not null),
    constraint block_filing_incomplete_record check (status != 'available' or dir           not null),
    constraint block_filing_incomplete_record check (status != 'available' or file          not null),
    constraint block_filing_incomplete_record check (status != 'available' or controlled_ts not null)
);
-- Triggers
-- Static data
-- Static data