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