--  $HeadURL$ $LastChangedRevision$

PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE clients (
            client_id text,
            client_hostname text,
            pingable_flag text check (pingable_flag == 'true' or pingable_flag == 'false'),
            sshable_flag text check (sshable_flag == 'true' or sshable_flag == 'false'),
            primary key (client_id)
        );
CREATE TABLE methods (
            method text,
            primary key (method)
        );
CREATE TABLE media (
            medium_id text,
            medium_dir text,
            last_used_time integer,
            primary key (medium_id)
        );
CREATE TABLE sets (
            set_id text,
            max_size integer,
            max_increment_pc integer,
            min_retention_interval integer,
            min_retention_count integer,
            backup_schedule text,
            check_schedule text,
            method text,
            primary key (set_id),
            foreign key (method) references methods(method)
        );
CREATE TABLE dles (
            set_id text,
            dle_id text,
            client_id text,
            dle_dir text,
            check_status text check (check_status == 'true' or check_status == 'false'),
            primary key (set_id, dle_id),
            foreign key (set_id) references sets(set_id),
            foreign key (client_id) references clients(client_id)
        );
CREATE TABLE sets_media (
            set_id text,
            medium_id text,
            primary key (set_id, medium_id),
            foreign key (set_id) references sets(set_id),
            foreign key (medium_id) references media(medium_id)
        );
CREATE TABLE backed_up_files (
            set_id text,
            dle_id text,
            backup_start_timestamp integer,
            file text,
            primary key (set_id, dle_id, backup_start_timestamp, file),
            foreign key (set_id, dle_id) references dles(set_id, dle_id)
        );
CREATE TABLE increments (
            set_id text,
            dle_id text,
            increment_time,
            foreign key (set_id, dle_id) references dles(set_id, dle_id)
        );
CREATE VIEW increments_ranked AS select
            set_id,
            dle_id,
            increment_time,
            (SELECT COUNT(*) from increments AS i2 where i2.set_id == i1.set_id AND i2.dle_id == i1.dle_id AND i2.increment_time > i1.increment_time) AS rank
            FROM increments AS i1;
COMMIT;
