Open-ILS/src/sql/Pg/210.schema.serials.sql

   1
   2
   3 DROP SCHEMA IF EXISTS serial CASCADE;
   4
   5 BEGIN;
   6
   7 CREATE SCHEMA serial;
   8
   9 CREATE TABLE serial.record_entry (
  10         id              BIGSERIAL       PRIMARY KEY,
  11         record          BIGINT          REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  12         owning_lib      INT             NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
  13         creator         INT             NOT NULL DEFAULT 1,
  14         editor          INT             NOT NULL DEFAULT 1,
  15         source          INT,
  16         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  17         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  18         active          BOOL            NOT NULL DEFAULT TRUE,
  19         deleted         BOOL            NOT NULL DEFAULT FALSE,
  20         marc            TEXT,
  21         last_xact_id    TEXT            NOT NULL
  22 );
  23 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
  24 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
  25 CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
  26
  27 CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id;
  28
  29 CREATE TABLE serial.subscription (
  30         id                     SERIAL       PRIMARY KEY,
  31         start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
  32         end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
  33         record_entry           BIGINT       REFERENCES biblio.record_entry (id)
  34                                             ON DELETE SET NULL
  35                                             DEFERRABLE INITIALLY DEFERRED,
  36         expected_date_offset   INTERVAL
  37         -- acquisitions/business-side tables link to here
  38 );
  39
  40
  41 CREATE TABLE serial.caption_and_pattern (
  42         id           SERIAL       PRIMARY KEY,
  43         subscription INT          NOT NULL
  44                                   REFERENCES serial.subscription (id)
  45                                   ON DELETE CASCADE
  46                                   DEFERRABLE INITIALLY DEFERRED,
  47         type         TEXT         NOT NULL
  48                                   CONSTRAINT cap_type CHECK ( type in
  49                                   ( 'basic', 'supplement', 'index' )),
  50         create_time  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  51         active       BOOL         NOT NULL DEFAULT FALSE,
  52         pattern_code TEXT         NOT NULL,       -- must contain JSON
  53         enum_1       TEXT,
  54         enum_2       TEXT,
  55         enum_3       TEXT,
  56         enum_4       TEXT,
  57         enum_5       TEXT,
  58         enum_6       TEXT,
  59         chron_1      TEXT,
  60         chron_2      TEXT,
  61         chron_3      TEXT,
  62         chron_4      TEXT,
  63         chron_5      TEXT
  64 );
  65
  66 --at least one distribution per org_unit holding issues
  67 CREATE TABLE serial.distribution (
  68         id                    SERIAL  PRIMARY KEY,
  69         record_entry          BIGINT  REFERENCES serial.record_entry (id)
  70                                                                   ON DELETE SET NULL
  71                                                                   DEFERRABLE INITIALLY DEFERRED,
  72         subscription          INT     NOT NULL
  73                                       REFERENCES serial.subscription (id)
  74                                                                   ON DELETE CASCADE
  75                                                                   DEFERRABLE INITIALLY DEFERRED,
  76         holding_lib           INT     NOT NULL
  77                                       REFERENCES actor.org_unit (id)
  78                                                                   DEFERRABLE INITIALLY DEFERRED,
  79         label                 TEXT    NOT NULL,
  80         receive_call_number   BIGINT  REFERENCES asset.call_number (id)
  81                                       DEFERRABLE INITIALLY DEFERRED,
  82         receive_unit_template INT     REFERENCES asset.copy_template (id)
  83                                       DEFERRABLE INITIALLY DEFERRED,
  84         bind_call_number      BIGINT  REFERENCES asset.call_number (id)
  85                                       DEFERRABLE INITIALLY DEFERRED,
  86         bind_unit_template    INT     REFERENCES asset.copy_template (id)
  87                                       DEFERRABLE INITIALLY DEFERRED,
  88         unit_label_base       TEXT,
  89         unit_label_suffix     TEXT
  90 );
  91 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
  92
  93 CREATE TABLE serial.stream (
  94         id              SERIAL  PRIMARY KEY,
  95         distribution    INT     NOT NULL
  96                                 REFERENCES serial.distribution (id)
  97                                 ON DELETE CASCADE
  98                                 DEFERRABLE INITIALLY DEFERRED,
  99         routing_label   TEXT
 100 );
 101
 102 CREATE UNIQUE INDEX label_once_per_dist
 103         ON serial.stream (distribution, routing_label)
 104         WHERE routing_label IS NOT NULL;
 105
 106 CREATE TABLE serial.routing_list_user (
 107         id             SERIAL       PRIMARY KEY,
 108         stream         INT          NOT NULL
 109                                     REFERENCES serial.stream
 110                                     ON DELETE CASCADE
 111                                     DEFERRABLE INITIALLY DEFERRED,
 112         pos            INT          NOT NULL DEFAULT 1,
 113         reader         INT          REFERENCES actor.usr
 114                                     ON DELETE CASCADE
 115                                     DEFERRABLE INITIALLY DEFERRED,
 116         department     TEXT,
 117         note           TEXT,
 118         CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
 119         CONSTRAINT reader_or_dept CHECK
 120         (
 121             -- Recipient is a person or a department, but not both
 122                 (reader IS NOT NULL AND department IS NULL) OR
 123                 (reader IS NULL AND department IS NOT NULL)
 124         )
 125 );
 126
 127 CREATE TABLE serial.issuance (
 128         id              SERIAL    PRIMARY KEY,
 129         creator         INT       NOT NULL
 130                                   REFERENCES actor.usr (id)
 131                                                           DEFERRABLE INITIALLY DEFERRED,
 132         editor          INT       NOT NULL
 133                                   REFERENCES actor.usr (id)
 134                                   DEFERRABLE INITIALLY DEFERRED,
 135         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 136         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 137         subscription    INT       NOT NULL
 138                                   REFERENCES serial.subscription (id)
 139                                   ON DELETE CASCADE
 140                                   DEFERRABLE INITIALLY DEFERRED,
 141         label           TEXT,
 142         date_published  TIMESTAMP WITH TIME ZONE,
 143         holding_code    TEXT,
 144         holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
 145                                   (
 146                                       holding_type IS NULL
 147                                       OR holding_type IN ('basic','supplement','index')
 148                                   ),
 149         holding_link_id INT
 150         -- TODO: add columns for separate enumeration/chronology values
 151 );
 152
 153 CREATE TABLE serial.unit (
 154         label           TEXT,
 155         label_sort_key  TEXT,
 156         contents        TEXT    NOT NULL
 157 ) INHERITS (asset.copy);
 158
 159 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
 160
 161 CREATE TABLE serial.item (
 162         id              SERIAL  PRIMARY KEY,
 163         creator         INT     NOT NULL
 164                                 REFERENCES actor.usr (id)
 165                                 DEFERRABLE INITIALLY DEFERRED,
 166         editor          INT     NOT NULL
 167                                 REFERENCES actor.usr (id)
 168                                 DEFERRABLE INITIALLY DEFERRED,
 169         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 170         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 171         issuance        INT     NOT NULL
 172                                 REFERENCES serial.issuance (id)
 173                                 ON DELETE CASCADE
 174                                 DEFERRABLE INITIALLY DEFERRED,
 175         stream          INT     NOT NULL
 176                                 REFERENCES serial.stream (id)
 177                                 ON DELETE CASCADE
 178                                 DEFERRABLE INITIALLY DEFERRED,
 179         unit            INT     REFERENCES serial.unit (id)
 180                                 ON DELETE SET NULL
 181                                 DEFERRABLE INITIALLY DEFERRED,
 182         uri             INT     REFERENCES asset.uri (id)
 183                                 ON DELETE SET NULL
 184                                 DEFERRABLE INITIALLY DEFERRED,
 185         date_expected   TIMESTAMP WITH TIME ZONE,
 186         date_received   TIMESTAMP WITH TIME ZONE
 187 );
 188
 189 CREATE TABLE serial.item_note (
 190         id          SERIAL  PRIMARY KEY,
 191         item        INT     NOT NULL
 192                             REFERENCES serial.item (id)
 193                             ON DELETE CASCADE
 194                             DEFERRABLE INITIALLY DEFERRED,
 195         creator     INT     NOT NULL
 196                             REFERENCES actor.usr (id)
 197                             DEFERRABLE INITIALLY DEFERRED,
 198         create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 199         pub         BOOL    NOT NULL    DEFAULT FALSE,
 200         title       TEXT    NOT NULL,
 201         value       TEXT    NOT NULL
 202 );
 203
 204 CREATE TABLE serial.bib_summary (
 205         id                  SERIAL  PRIMARY KEY,
 206         distribution        INT     NOT NULL
 207                                     REFERENCES serial.distribution (id)
 208                                     ON DELETE CASCADE
 209                                     DEFERRABLE INITIALLY DEFERRED,
 210         generated_coverage  TEXT    NOT NULL,
 211         textual_holdings    TEXT
 212 );
 213
 214 CREATE TABLE serial.sup_summary (
 215         id                  SERIAL  PRIMARY KEY,
 216         distribution        INT     NOT NULL
 217                                     REFERENCES serial.distribution (id)
 218                                     ON DELETE CASCADE
 219                                     DEFERRABLE INITIALLY DEFERRED,
 220         generated_coverage  TEXT    NOT NULL,
 221         textual_holdings    TEXT
 222 );
 223
 224 CREATE TABLE serial.index_summary (
 225         id                  SERIAL  PRIMARY KEY,
 226         distribution        INT     NOT NULL
 227                                     REFERENCES serial.distribution (id)
 228                                     ON DELETE CASCADE
 229                                     DEFERRABLE INITIALLY DEFERRED,
 230         generated_coverage  TEXT    NOT NULL,
 231         textual_holdings    TEXT
 232 );
 233
 234 COMMIT;