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