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	alter table backups
2	add column id int;
3	create sequence backupsseq;
4	select setval('backupsseq', 1, false);
5	update backups set id=nextVal('backupsseq');
6
7	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	create sequence archive_id_seq;
18	create sequence dvd_nr;
19
20	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);