--  $HeadURL$ $LastChangedRevision$

PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE logical_values (
            logical_value text,
            primary key (logical_value)
        );
INSERT INTO "logical_values" VALUES('true');
INSERT INTO "logical_values" VALUES('false');
CREATE TABLE clients (
            client_id text,
            client_hostname text,
            resolvable_flag text,
            pingable_flag text,
            sshable_flag text,
            ssh_port integer,
            foreign key (resolvable_flag) references logical_values(logical_value),
            foreign key (pingable_flag) references logical_values(logical_value),
            foreign key (sshable_flag) references logical_values(logical_value),
            primary key (client_id)
        );
CREATE TABLE methods (
            method_id text,
            primary key (method_id)
        );
INSERT INTO "methods" VALUES('rsync');
INSERT INTO "methods" VALUES('rdiff_backup');
INSERT INTO "methods" VALUES('unison');
INSERT INTO "methods" VALUES('noop');
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,
            primary key (set_id)
        );
CREATE TABLE dles (
            set_id text,
            dle_id text,
            client_id text,
            dle_dir text,
            uuid text unique not null,
            check_status text,
            primary key (set_id, dle_id),
            foreign key (check_status) references logical_values(logical_value),
            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 flag_names (
            flag_id text,
            primary key (flag_id)
        );
INSERT INTO "flag_names" VALUES('list_files');
INSERT INTO "flag_names" VALUES('enabled');
INSERT INTO "flag_names" VALUES('method');
CREATE TABLE flags (
            flag_id text,
            flag_value text,
            set_id text,          -- null allowed
            dle_id text,          -- null allowed
	    primary key (flag_id,set_id,dle_id),
	    foreign key (flag_id)        references flag_names(flag_id),
	    foreign key (set_id)         references sets(set_id),
	    foreign key (set_id, dle_id) references dles(set_id, dle_id)
	);
INSERT INTO "flags" VALUES('list_files','0',NULL,NULL);
INSERT INTO "flags" VALUES('enabled','1',NULL,NULL);
INSERT INTO "flags" VALUES('method','rdiff_backup',NULL,NULL);
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;
