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