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