trunk/sql/workflow_schema.sql

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