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