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
  22
  23 CREATE TABLE reporter.date_dim AS
  24         SELECT
  25                 EXTRACT('year' FROM date_label)::INT AS year,
  26                 EXTRACT('month' FROM date_label)::INT AS month,
  27                 EXTRACT('day' FROM date_label)::INT AS day
  28         FROM
  29                 (SELECT '1900-01-01'::date + g.x AS date_label
  30                    FROM GENERATE_SERIES(0, EXTRACT('days' FROM NOW() + '10 years'::INTERVAL - '1900-01-01'::TIMESTAMP WITH TIME ZONE)::INT) g(x)) as y
  31         ORDER BY 1,2,3;
  32
  33
  34 CREATE TABLE reporter.time_dim AS
  35         SELECT
  36                 a.x AS hour,
  37                 b.x AS minute,
  38                 c.x AS second
  39         FROM
  40                    GENERATE_SERIES(0, 23) as a(x),
  41                    GENERATE_SERIES(0, 59) as b(x),
  42                    GENERATE_SERIES(0, 59) as c(x)
  43         order by 1,2,3;
  44
  45
  46 CREATE TABLE reporter.stage2 (
  47         id              serial                          primary key,
  48         stage1          text                            not null,
  49         params          text                            not null,
  50         owner           int                             not null,
  51         pub             bool                            not null
  52                                                         default false,
  53         create_date     timestamp with time zone        not null
  54                                                         default now(),
  55         edit_date       timestamp with time zone        not null
  56                                                         default now()
  57 );
  58
  59 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
  60         BEGIN
  61                 NEW.edit_date = NOW();
  62                 RETURN NEW;
  63         END;
  64 $$ LANGUAGE PLPGSQL;
  65
  66 CREATE TRIGGER force_edit_date_update_trig
  67         BEFORE UPDATE ON reporter.stage2
  68         FOR EACH ROW
  69         EXECUTE PROCEDURE reporter.force_edit_date_update ();
  70
  71 CREATE TABLE reporter.stage3 (
  72         id              serial                          primary key,
  73         stage2          int                             not null
  74                                                         references reporter.stage2 (id)
  75                                                                 on delete restrict
  76                                                                 deferrable
  77                                                                 initially deferred,
  78         params          text                            not null,
  79         owner           int                             not null,
  80         disable         bool                            not null
  81                                                         default false,
  82         pub             bool                            not null
  83                                                         default false,
  84         create_date     timestamp with time zone        not null
  85                                                         default now(),
  86         runtime timestamp with time zone        default now(),
  87         recurrence      interval
  88 );
  89
  90 CREATE TABLE reporter.output (
  91         id              serial                          primary key,
  92         stage3          int                             not null
  93                                                         references reporter.stage3 (id)
  94                                                                 on delete restrict
  95                                                                 deferrable
  96                                                                 initially deferred,
  97         queue_time      timestamp with time zone        not null default now(),
  98         run_time        timestamp with time zone,
  99         run_pid         int,
 100         query           text,
 101         error           text,
 102         error_time      timestamp with time zone,
 103         complete_time   timestamp with time zone,
 104         state           text                            check (state in ('wait','running','complete','error'))
 105 );
 106
 107 COMMIT;
 108