simple setup for reporter thingy
[Evergreen.git] / Open-ILS / src / reporter / config.sql
1 DROP SCEMA reporter CASCADE;
2 CREATE SCHEMA reporter;
3
4 BEGIN;
5
6 CREATE TABLE reporter.stage2 (
7         id              serial                          primary key,
8         stage1          text                            not null,
9         filename        text                            not null,
10         owner           int                             not null,
11         pub             bool                            not null
12                                                         default false,
13         create_date     timestamp with time zone        not null
14                                                         default now(),
15         edit_date       timestamp with time zone        not null
16                                                         default now()
17 );
18
19 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
20         BEGIN
21                 NEW.edit_date = NOW();
22                 RETURN NEW;
23         END;
24 $$ LANGUAGE PLPGSQL;
25
26 CREATE TRIGGER force_edit_date_update_trig
27         BEFORE UPDATE ON reporter.stage2
28         FOR EACH ROW
29         EXECUTE PROCEDURE reporter.force_edit_date_update ();
30
31 CREATE TABLE reporter.stage3 (
32         id              serial                          primary key,
33         stage2          int                             not null 
34                                                         references reporter.stage2 (id)
35                                                                 on delete restrict
36                                                                 deferrable
37                                                                 initially deferred,
38         filename        text                            not null,
39         owner           int                             not null,
40         pub             bool                            not null
41                                                         default false,
42         create_date     timestamp with time zone        not null
43                                                         default now()
44 );
45
46 COMMIT;
47