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