trunk/sql/workflow_schema.sql

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