Open-ILS/src/reporter/config.sql

   1 DROP SCHEMA reporter CASCADE;
   2
   3 CREATE SCHEMA reporter;
   4
   5 BEGIN;
   6
   7 CREATE TABLE reporter.stage2 (
   8         id              serial                          primary key,
   9         stage1          text                            not null,
  10         params          text                            not null,
  11         owner           int                             not null,
  12         pub             bool                            not null
  13                                                         default false,
  14         create_date     timestamp with time zone        not null
  15                                                         default now(),
  16         edit_date       timestamp with time zone        not null
  17                                                         default now()
  18 );
  19
  20 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
  21         BEGIN
  22                 NEW.edit_date = NOW();
  23                 RETURN NEW;
  24         END;
  25 $$ LANGUAGE PLPGSQL;
  26
  27 CREATE TRIGGER force_edit_date_update_trig
  28         BEFORE UPDATE ON reporter.stage2
  29         FOR EACH ROW
  30         EXECUTE PROCEDURE reporter.force_edit_date_update ();
  31
  32 CREATE TABLE reporter.stage3 (
  33         id              serial                          primary key,
  34         stage2          int                             not null
  35                                                         references reporter.stage2 (id)
  36                                                                 on delete restrict
  37                                                                 deferrable
  38                                                                 initially deferred,
  39         params          text                            not null,
  40         owner           int                             not null,
  41         pub             bool                            not null
  42                                                         default false,
  43         create_date     timestamp with time zone        not null
  44                                                         default now(),
  45         runtime timestamp with time zone        default now(),
  46         recurrence      interval
  47 );
  48
  49 CREATE TABLE reporter.run_queue (
  50         id              serial                          primary key,
  51         stage3          int                             not null
  52                                                         references reporter.stage3 (id)
  53                                                                 on delete restrict
  54                                                                 deferrable
  55                                                                 initially deferred,
  56         queue_time      timestamp with time zone        not null default now(),
  57         run_time        timestamp with time zone,
  58         complete_time   timestamp with time zone,
  59         state           text                            check (state in ('wait','running','complete'))
  60 );
  61
  62 CREATE TABLE reporter.output (
  63         id              int             primary key,
  64         stage3          int             not null
  65                                         references reporter.stage3 (id)
  66                                                 on delete restrict
  67                                                 deferrable
  68                                                 initially deferred,
  69         queue_time      timestamp with time zone        not null default now(),
  70         run_time        timestamp with time zone        not null,
  71         complete_time   timestamp with time zone        not null
  72 );
  73
  74 COMMIT;
  75