trunk/sql/workflow_schema.sql

alter table backups 
        add column id int;
create sequence backupsseq;
select setval('backupsseq', 1, false);
update backups set id=nextVal('backupsseq');

create table archive
(
        id                      int not null,
        dvd_nr          int not null,
        note            text,
        username        varchar(20) not null,
        date            timestamp,
        primary key(id)
);

create sequence archive_id_seq;
create sequence dvd_nr;

create table archive_backup
(
        archive_id      int not null,
        backup_id       int not null,
        status          text,
        primary key(archive_id, backup_id)
);

create table workflows(
        id                      int not null,
        step_id         int not null,
        start           timestamp,
        stop            timestamp,
        username        varchar(20),
        archive_id      int not null,
        running         boolean default true,
        primary key(id)
);

create table workflow_step
(
        step_id         int not null,
        code            text,
        next_step       int,
        stop            boolean default false,
        primary key(step_id)
);

alter table workflow_step
        add constraint fk_workflow_next_step
        foreign key(next_step)
        references workflow_step(step_id);

alter table workflows
        add constraint fk_workflows_step_id
        foreign key(step_id)
        references workflow_step(step_id);
        
alter table workflows
        add constraint fk_workflows_archive_id
        foreign key(archive_id)
        references archive(id); 


create table workflow_log
(
        workflow_id             int not null,
        step_id                 int not null,
        date                    timestamp not null,
        status                  text,
        primary key(workflow_id, step_id)
);

alter table workflow_log
        add constraint fk_workflow_log_workflow_id
        foreign key (workflow_id)
        references workflows(id);
        
alter table workflow_log
        add constraint fk_workflow_log_step_id
        foreign key (step_id)
        references      workflow_step(step_id);
1	iklaric	121	alter table backups
2			add column id int;
3			create sequence backupsseq;
4	dpavlin	139	select setval('backupsseq', 1, false);
5	iklaric	121	update backups set id=nextVal('backupsseq');
6
7	iklaric	120	create table archive
8			(
9			id int not null,
10			dvd_nr int not null,
11			note text,
12			username varchar(20) not null,
13			date timestamp,
14			primary key(id)
15			);
16
17	iklaric	121	create sequence archive_id_seq;
18			create sequence dvd_nr;
19
20	iklaric	120	create table archive_backup
21			(
22			archive_id int not null,
23			backup_id int not null,
24			status text,
25			primary key(archive_id, backup_id)
26			);
27
28			create table workflows(
29			id int not null,
30			step_id int not null,
31			start timestamp,
32			stop timestamp,
33			username varchar(20),
34			archive_id int not null,
35			running boolean default true,
36			primary key(id)
37			);
38
39			create table workflow_step
40			(
41			step_id int not null,
42			code text,
43			next_step int,
44			stop boolean default false,
45			primary key(step_id)
46			);
47
48			alter table workflow_step
49			add constraint fk_workflow_next_step
50			foreign key(next_step)
51			references workflow_step(step_id);
52
53			alter table workflows
54			add constraint fk_workflows_step_id
55			foreign key(step_id)
56			references workflow_step(step_id);
57
58			alter table workflows
59			add constraint fk_workflows_archive_id
60			foreign key(archive_id)
61			references archive(id);
62
63
64
65			create table workflow_log
66			(
67			workflow_id int not null,
68			step_id int not null,
69			date timestamp not null,
70			status text,
71			primary key(workflow_id, step_id)
72			);
73
74			alter table workflow_log
75			add constraint fk_workflow_log_workflow_id
76			foreign key (workflow_id)
77			references workflows(id);
78
79			alter table workflow_log
80			add constraint fk_workflow_log_step_id
81			foreign key (step_id)
82			references workflow_step(step_id);