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