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	iklaric	120	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);