Open-ILS/src/reporter/config.sql

   1 DROP SCHEMA reporter CASCADE;
   2
   3 CREATE SCHEMA reporter;
   4
   5 BEGIN;
   6
   7 CREATE TABLE reporter.stage1 (
   8         id              serial                          primary key,
   9         filename        text                            not null,
  10         owner           int                             not null,
  11         pub             bool                            not null
  12                                                         default true,
  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 TABLE reporter.stage2 (
  20         id              serial                          primary key,
  21         stage1          int                             not null
  22                                                         references reporter.stage1 (id)
  23                                                                 on delete restrict
  24                                                                 deferrable
  25                                                                 initially deferred,
  26         filename        text                            not null,
  27         owner           int                             not null,
  28         pub             bool                            not null
  29                                                         default false,
  30         create_date     timestamp with time zone        not null
  31                                                         default now(),
  32         edit_date       timestamp with time zone        not null
  33                                                         default now()
  34 );
  35
  36 CREATE OR REPLACE FUNCTION reporter.force_edit_date_update () RETURNS TRIGGER AS $$
  37         BEGIN
  38                 NEW.edit_date = NOW();
  39                 RETURN NEW;
  40         END;
  41 $$ LANGUAGE PLPGSQL;
  42
  43 CREATE TRIGGER force_edit_date_update_trig
  44         BEFORE UPDATE ON reporter.stage2
  45         FOR EACH ROW
  46         EXECUTE PROCEDURE reporter.force_edit_date_update ();
  47
  48 CREATE TABLE reporter.stage3 (
  49         id              serial                          primary key,
  50         stage2          int                             not null
  51                                                         references reporter.stage2 (id)
  52                                                                 on delete restrict
  53                                                                 deferrable
  54                                                                 initially deferred,
  55         filename        text                            not null,
  56         owner           int                             not null,
  57         pub             bool                            not null
  58                                                         default false,
  59         create_date     timestamp with time zone        not null
  60                                                         default now()
  61 );
  62
  63 COMMIT;
  64