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