bug hunting and a (small) new set of widget code
[Evergreen.git] / Open-ILS / src / reporter / config.sql
1 DROP SCHEMA reporter CASCADE;
2
3 CREATE SCHEMA reporter;
4
5 BEGIN;
6
7 CREATE OR REPLACE VIEW reporter.date_series AS
8         SELECT  CAST('1900/01/01' AS DATE) + x AS date,
9                 CAST('1900/01/01' AS DATE) + x AS date_label
10           FROM  GENERATE_SERIES(
11                         0,
12                         CAST( EXTRACT( 'days' FROM CAST( NOW() - CAST( '1900/01/01' AS DATE ) AS INTERVAL ) ) AS INT )
13                 ) AS g(x);
14
15 CREATE OR REPLACE VIEW reporter.date_hour_series AS
16         SELECT  CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour,
17                 CAST(date + CAST(h || ' hours' AS INTERVAL) AS TIMESTAMP WITH TIME ZONE) AS date_hour_label
18           FROM  reporter.date_series,
19                 GENERATE_SERIES(0,23) g(h);
20
21 CREATE TABLE reporter.stage2 (
22         id              serial                          primary key,
23         stage1          text                            not null, 
24         params          text                            not null,
25         owner           int                             not null,
26         pub             bool                            not null
27                                                         default false,
28         create_date     timestamp with time zone        not null
29                                                         default now(),
30         edit_date       timestamp with time zone        not null
31                                                         default now()
32 );
33
34 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
35         BEGIN
36                 NEW.edit_date = NOW();
37                 RETURN NEW;
38         END;
39 $$ LANGUAGE PLPGSQL;
40
41 CREATE TRIGGER force_edit_date_update_trig
42         BEFORE UPDATE ON reporter.stage2
43         FOR EACH ROW
44         EXECUTE PROCEDURE reporter.force_edit_date_update ();
45
46 CREATE TABLE reporter.stage3 (
47         id              serial                          primary key,
48         stage2          int                             not null 
49                                                         references reporter.stage2 (id)
50                                                                 on delete restrict
51                                                                 deferrable
52                                                                 initially deferred,
53         params          text                            not null,
54         owner           int                             not null,
55         pub             bool                            not null
56                                                         default false,
57         create_date     timestamp with time zone        not null
58                                                         default now(),
59         runtime timestamp with time zone        default now(),
60         recurrence      interval
61 );
62
63 CREATE TABLE reporter.output (
64         id              serial                          primary key,
65         stage3          int                             not null
66                                                         references reporter.stage3 (id)
67                                                                 on delete restrict
68                                                                 deferrable
69                                                                 initially deferred,
70         queue_time      timestamp with time zone        not null default now(),
71         run_time        timestamp with time zone,
72         run_pid         int,
73         query           text,
74         error           text,
75         error_time      timestamp with time zone,
76         complete_time   timestamp with time zone,
77         state           text                            check (state in ('wait','running','complete','error'))
78 );
79
80 COMMIT;
81