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