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         display_grouping      TEXT    NOT NULL DEFAULT 'chron'
 109                                       CHECK (display_grouping IN ('enum', 'chron')),
 110         receive_call_number   BIGINT  REFERENCES asset.call_number (id)
 111                                       DEFERRABLE INITIALLY DEFERRED,
 112         receive_unit_template INT     REFERENCES asset.copy_template (id)
 113                                       DEFERRABLE INITIALLY DEFERRED,
 114         bind_call_number      BIGINT  REFERENCES asset.call_number (id)
 115                                       DEFERRABLE INITIALLY DEFERRED,
 116         bind_unit_template    INT     REFERENCES asset.copy_template (id)
 117                                       DEFERRABLE INITIALLY DEFERRED,
 118         unit_label_prefix     TEXT,
 119         unit_label_suffix     TEXT
 120 );
 121 CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry);
 122 CREATE INDEX serial_distribution_sub_idx ON serial.distribution (subscription);
 123 CREATE INDEX serial_distribution_holding_lib_idx ON serial.distribution (holding_lib);
 124
 125 CREATE TABLE serial.distribution_note (
 126         id           SERIAL PRIMARY KEY,
 127         distribution INT    NOT NULL
 128                             REFERENCES serial.distribution (id)
 129                             ON DELETE CASCADE
 130                             DEFERRABLE INITIALLY DEFERRED,
 131         creator      INT    NOT NULL
 132                             REFERENCES actor.usr (id)
 133                             DEFERRABLE INITIALLY DEFERRED,
 134         create_date  TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
 135         pub          BOOL   NOT NULL DEFAULT FALSE,
 136         title        TEXT   NOT NULL,
 137         value        TEXT   NOT NULL
 138 );
 139 CREATE INDEX serial_distribution_note_dist_idx ON serial.distribution_note (distribution);
 140
 141 CREATE TABLE serial.stream (
 142         id              SERIAL  PRIMARY KEY,
 143         distribution    INT     NOT NULL
 144                                 REFERENCES serial.distribution (id)
 145                                 ON DELETE CASCADE
 146                                 DEFERRABLE INITIALLY DEFERRED,
 147         routing_label   TEXT
 148 );
 149 CREATE INDEX serial_stream_dist_idx ON serial.stream (distribution);
 150
 151 CREATE UNIQUE INDEX label_once_per_dist
 152         ON serial.stream (distribution, routing_label)
 153         WHERE routing_label IS NOT NULL;
 154
 155 CREATE TABLE serial.routing_list_user (
 156         id             SERIAL       PRIMARY KEY,
 157         stream         INT          NOT NULL
 158                                     REFERENCES serial.stream
 159                                     ON DELETE CASCADE
 160                                     DEFERRABLE INITIALLY DEFERRED,
 161         pos            INT          NOT NULL DEFAULT 1,
 162         reader         INT          REFERENCES actor.usr
 163                                     ON DELETE CASCADE
 164                                     DEFERRABLE INITIALLY DEFERRED,
 165         department     TEXT,
 166         note           TEXT,
 167         CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
 168         CONSTRAINT reader_or_dept CHECK
 169         (
 170             -- Recipient is a person or a department, but not both
 171                 (reader IS NOT NULL AND department IS NULL) OR
 172                 (reader IS NULL AND department IS NOT NULL)
 173         )
 174 );
 175 CREATE INDEX serial_routing_list_user_stream_idx ON serial.routing_list_user (stream);
 176 CREATE INDEX serial_routing_list_user_reader_idx ON serial.routing_list_user (reader);
 177
 178 CREATE TABLE serial.issuance (
 179         id              SERIAL    PRIMARY KEY,
 180         creator         INT       NOT NULL
 181                                   REFERENCES actor.usr (id)
 182                                                           DEFERRABLE INITIALLY DEFERRED,
 183         editor          INT       NOT NULL
 184                                   REFERENCES actor.usr (id)
 185                                   DEFERRABLE INITIALLY DEFERRED,
 186         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 187         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 188         subscription    INT       NOT NULL
 189                                   REFERENCES serial.subscription (id)
 190                                   ON DELETE CASCADE
 191                                   DEFERRABLE INITIALLY DEFERRED,
 192         label           TEXT,
 193         date_published  TIMESTAMP WITH TIME ZONE,
 194         caption_and_pattern INT   REFERENCES serial.caption_and_pattern (id)
 195                                   DEFERRABLE INITIALLY DEFERRED,
 196         holding_code    TEXT      CONSTRAINT issuance_holding_code_check CHECK (
 197                                     holding_code IS NULL OR could_be_serial_holding_code(holding_code)
 198                                   ),
 199         holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
 200                                   (
 201                                       holding_type IS NULL
 202                                       OR holding_type IN ('basic','supplement','index')
 203                                   ),
 204         holding_link_id INT -- probably defunct
 205         -- TODO: add columns for separate enumeration/chronology values
 206 );
 207 ALTER TABLE serial.issuance ADD CHECK (holding_code IS NULL OR evergreen.is_json(holding_code));
 208 CREATE INDEX serial_issuance_sub_idx ON serial.issuance (subscription);
 209 CREATE INDEX serial_issuance_caption_and_pattern_idx ON serial.issuance (caption_and_pattern);
 210 CREATE INDEX serial_issuance_date_published_idx ON serial.issuance (date_published);
 211
 212 CREATE TABLE serial.unit (
 213         sort_key          TEXT,
 214         detailed_contents TEXT    NOT NULL,
 215         summary_contents  TEXT    NOT NULL
 216 ) INHERITS (asset.copy);
 217 ALTER TABLE serial.unit ADD PRIMARY KEY (id);
 218 CREATE UNIQUE INDEX unit_barcode_key ON serial.unit (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
 219 CREATE INDEX unit_cn_idx ON serial.unit (call_number);
 220 CREATE INDEX unit_avail_cn_idx ON serial.unit (call_number);
 221 CREATE INDEX unit_creator_idx  ON serial.unit ( creator );
 222 CREATE INDEX unit_editor_idx   ON serial.unit ( editor );
 223
 224 -- must create this rule explicitly; it is not inherited from asset.copy
 225 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;
 226
 227 -- must create this trigger explicitly; it is not inherited from asset.copy
 228 CREATE TRIGGER autogenerate_placeholder_barcode
 229    BEFORE INSERT OR UPDATE ON serial.unit
 230    FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode()
 231 ;
 232
 233 -- must create this trigger explicitly; it is not inherited from asset.copy
 234 CREATE TRIGGER sunit_status_changed_trig
 235     BEFORE UPDATE ON serial.unit
 236     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
 237
 238 -- ditto
 239 CREATE TRIGGER sunit_created_trig
 240     BEFORE INSERT ON serial.unit
 241     FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
 242
 243 CREATE TABLE serial.item (
 244         id              SERIAL  PRIMARY KEY,
 245         creator         INT     NOT NULL
 246                                 REFERENCES actor.usr (id)
 247                                 DEFERRABLE INITIALLY DEFERRED,
 248         editor          INT     NOT NULL
 249                                 REFERENCES actor.usr (id)
 250                                 DEFERRABLE INITIALLY DEFERRED,
 251         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 252         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
 253         issuance        INT     NOT NULL
 254                                 REFERENCES serial.issuance (id)
 255                                 ON DELETE CASCADE
 256                                 DEFERRABLE INITIALLY DEFERRED,
 257         stream          INT     NOT NULL
 258                                 REFERENCES serial.stream (id)
 259                                 ON DELETE CASCADE
 260                                 DEFERRABLE INITIALLY DEFERRED,
 261         unit            INT     REFERENCES serial.unit (id)
 262                                 ON DELETE SET NULL
 263                                 DEFERRABLE INITIALLY DEFERRED,
 264         uri             INT     REFERENCES asset.uri (id)
 265                                 ON DELETE SET NULL
 266                                 DEFERRABLE INITIALLY DEFERRED,
 267         date_expected   TIMESTAMP WITH TIME ZONE,
 268         date_received   TIMESTAMP WITH TIME ZONE,
 269         status          TEXT    CONSTRAINT valid_status CHECK
 270                                 (
 271                                     status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received')
 272                                 ) DEFAULT 'Expected',
 273         shadowed        BOOL    NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels
 274 );
 275 CREATE INDEX serial_item_stream_idx ON serial.item (stream);
 276 CREATE INDEX serial_item_issuance_idx ON serial.item (issuance);
 277 CREATE INDEX serial_item_unit_idx ON serial.item (unit);
 278 CREATE INDEX serial_item_uri_idx ON serial.item (uri);
 279 CREATE INDEX serial_item_date_received_idx ON serial.item (date_received);
 280 CREATE INDEX serial_item_status_idx ON serial.item (status);
 281
 282 CREATE TABLE serial.item_note (
 283         id          SERIAL  PRIMARY KEY,
 284         item        INT     NOT NULL
 285                             REFERENCES serial.item (id)
 286                             ON DELETE CASCADE
 287                             DEFERRABLE INITIALLY DEFERRED,
 288         creator     INT     NOT NULL
 289                             REFERENCES actor.usr (id)
 290                             DEFERRABLE INITIALLY DEFERRED,
 291         create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 292         pub         BOOL    NOT NULL    DEFAULT FALSE,
 293         title       TEXT    NOT NULL,
 294         value       TEXT    NOT NULL
 295 );
 296 CREATE INDEX serial_item_note_item_idx ON serial.item_note (item);
 297
 298 CREATE TABLE serial.basic_summary (
 299         id                  SERIAL  PRIMARY KEY,
 300         distribution        INT     NOT NULL
 301                                     REFERENCES serial.distribution (id)
 302                                     ON DELETE CASCADE
 303                                     DEFERRABLE INITIALLY DEFERRED,
 304         generated_coverage  TEXT    NOT NULL,
 305         textual_holdings    TEXT,
 306         show_generated      BOOL    NOT NULL DEFAULT TRUE
 307 );
 308 CREATE INDEX serial_basic_summary_dist_idx ON serial.basic_summary (distribution);
 309
 310 CREATE TABLE serial.supplement_summary (
 311         id                  SERIAL  PRIMARY KEY,
 312         distribution        INT     NOT NULL
 313                                     REFERENCES serial.distribution (id)
 314                                     ON DELETE CASCADE
 315                                     DEFERRABLE INITIALLY DEFERRED,
 316         generated_coverage  TEXT    NOT NULL,
 317         textual_holdings    TEXT,
 318         show_generated      BOOL    NOT NULL DEFAULT TRUE
 319 );
 320 CREATE INDEX serial_supplement_summary_dist_idx ON serial.supplement_summary (distribution);
 321
 322 CREATE TABLE serial.index_summary (
 323         id                  SERIAL  PRIMARY KEY,
 324         distribution        INT     NOT NULL
 325                                     REFERENCES serial.distribution (id)
 326                                     ON DELETE CASCADE
 327                                     DEFERRABLE INITIALLY DEFERRED,
 328         generated_coverage  TEXT    NOT NULL,
 329         textual_holdings    TEXT,
 330         show_generated      BOOL    NOT NULL DEFAULT TRUE
 331 );
 332 CREATE INDEX serial_index_summary_dist_idx ON serial.index_summary (distribution);
 333
 334 CREATE VIEW serial.any_summary AS
 335     SELECT
 336         'basic' AS summary_type, id, distribution,
 337         generated_coverage, textual_holdings, show_generated
 338     FROM serial.basic_summary
 339     UNION
 340     SELECT
 341         'index' AS summary_type, id, distribution,
 342         generated_coverage, textual_holdings, show_generated
 343     FROM serial.index_summary
 344     UNION
 345     SELECT
 346         'supplement' AS summary_type, id, distribution,
 347         generated_coverage, textual_holdings, show_generated
 348     FROM serial.supplement_summary ;
 349
 350
 351 CREATE TABLE serial.materialized_holding_code (
 352     id BIGSERIAL PRIMARY KEY,
 353     issuance INTEGER NOT NULL REFERENCES serial.issuance (id) ON DELETE CASCADE,
 354     subfield CHAR,
 355     value TEXT
 356 );
 357
 358 CREATE OR REPLACE FUNCTION serial.materialize_holding_code() RETURNS TRIGGER
 359 AS $func$
 360 use strict;
 361
 362 use MARC::Field;
 363 use JSON::XS;
 364
 365 if (not defined $_TD->{new}{holding_code}) {
 366     elog(WARNING, 'NULL in "holding_code" column of serial.issuance allowed for now, but may not be useful');
 367     return;
 368 }
 369
 370 # Do nothing if holding_code has not changed...
 371
 372 if ($_TD->{new}{holding_code} eq $_TD->{old}{holding_code}) {
 373     # ... unless the following internal flag is set.
 374
 375     my $flag_rv = spi_exec_query(q{
 376         SELECT * FROM config.internal_flag
 377         WHERE name = 'serial.rematerialize_on_same_holding_code' AND enabled
 378     }, 1);
 379     return unless $flag_rv->{processed};
 380 }
 381
 382
 383 my $holding_code = (new JSON::XS)->decode($_TD->{new}{holding_code});
 384
 385 my $field = new MARC::Field('999', @$holding_code); # tag doesnt matter
 386
 387 my $dstmt = spi_prepare(
 388     'DELETE FROM serial.materialized_holding_code WHERE issuance = $1',
 389     'INT'
 390 );
 391 spi_exec_prepared($dstmt, $_TD->{new}{id});
 392
 393 my $istmt = spi_prepare(
 394     q{
 395         INSERT INTO serial.materialized_holding_code (
 396             issuance, subfield, value
 397         ) VALUES ($1, $2, $3)
 398     }, qw{INT CHAR TEXT}
 399 );
 400
 401 foreach ($field->subfields) {
 402     spi_exec_prepared(
 403         $istmt,
 404         $_TD->{new}{id},
 405         $_->[0],
 406         $_->[1]
 407     );
 408 }
 409
 410 return;
 411
 412 $func$ LANGUAGE 'plperlu';
 413
 414 CREATE INDEX assist_holdings_display
 415     ON serial.materialized_holding_code (issuance, subfield);
 416
 417 CREATE TRIGGER materialize_holding_code
 418     AFTER INSERT OR UPDATE ON serial.issuance
 419     FOR EACH ROW EXECUTE PROCEDURE serial.materialize_holding_code() ;
 420 COMMIT;
 421