1. Add table: serial.caption_and_pattern
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 210.schema.serials.sql
index 9637dad..29663bd 100644 (file)
 
 
-DROP SCHEMA serial CASCADE;
-
-CREATE TABLE asset.uri (
-       id      SERIAL  PRIMARY KEY,
-       href    TEXT    NOT NULL,
-       label   TEXT,
-       use     TEXT,
-       active  BOOL    NOT NULL DEFAULT TRUE
-);
-
-ALTER TABLE asset.call_number ADD COLUMN uri INT REFERENCES asset.uri (id) DEFERRABLE INITIALLY DEFERRED;
+DROP SCHEMA IF EXISTS serial CASCADE;
 
 BEGIN;
 
 CREATE SCHEMA serial;
 
+CREATE TABLE serial.record_entry (
+       id              BIGSERIAL       PRIMARY KEY,
+       record          BIGINT          REFERENCES biblio.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+       owning_lib      INT             NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
+       creator         INT             NOT NULL DEFAULT 1,
+       editor          INT             NOT NULL DEFAULT 1,
+       source          INT,
+       create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       active          BOOL            NOT NULL DEFAULT TRUE,
+       deleted         BOOL            NOT NULL DEFAULT FALSE,
+       marc            TEXT,
+       last_xact_id    TEXT            NOT NULL
+);
+CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
+CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
+CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_lib, deleted );
+
+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;
+
+CREATE TABLE serial.caption_and_pattern (
+       id           SERIAL       PRIMARY KEY,
+       record       BIGINT       NOT NULL
+                                 REFERENCES serial.record_entry (id)
+                                 ON DELETE CASCADE
+                                 DEFERRABLE INITIALLY DEFERRED,
+       type         TEXT         NOT NULL
+                                 CONSTRAINT cap_type CHECK ( type in
+                                 ( 'basic', 'supplement', 'index' )),
+       create_time  TIMESTAMPTZ  NOT NULL DEFAULT now(),
+       active       BOOL         NOT NULL DEFAULT FALSE,
+       pattern_code TEXT         NOT NULL,       -- must contain JSON
+       enum_1       TEXT,
+       enum_2       TEXT,
+       enum_3       TEXT,
+       enum_4       TEXT,
+       enum_5       TEXT,
+       enum_6       TEXT,
+       chron_1      TEXT,
+       chron_2      TEXT,
+       chron_3      TEXT,
+       chron_4      TEXT,
+       chron_5      TEXT
+);
+
 CREATE TABLE serial.subscription (
-       id              SERIAL  PRIMARY KEY,
-       callnumber      BIGINT  REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-       uri             INT     REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
-       start_date      DATE    NOT NULL,
-       end_date        DATE    NOT NULL
+       id                     SERIAL       PRIMARY KEY,
+       start_date             TIMESTAMP WITH TIME ZONE     NOT NULL,
+       end_date               TIMESTAMP WITH TIME ZONE,    -- interpret NULL as current subscription
+       record_entry           BIGINT       REFERENCES serial.record_entry (id)
+                                           ON DELETE SET NULL
+                                           DEFERRABLE INITIALLY DEFERRED,
+       expected_date_offset   INTERVAL
+       -- acquisitions/business-side tables link to here
 );
 
-CREATE TABLE serial.binding_unit (
-       id              SERIAL  PRIMARY KEY,
-       subscription    INT     NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       label           TEXT    NOT NULL,
-       CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
+--at least one distribution per org_unit holding issues
+CREATE TABLE serial.distribution (
+       id                    SERIAL  PRIMARY KEY,
+       subscription          INT     NOT NULL
+                                     REFERENCES serial.subscription (id)
+                                                                 ON DELETE CASCADE
+                                                                 DEFERRABLE INITIALLY DEFERRED,
+       holding_lib           INT     NOT NULL
+                                     REFERENCES actor.org_unit (id)
+                                                                 DEFERRABLE INITIALLY DEFERRED,
+       label                 TEXT    NOT NULL,
+       receive_call_number   BIGINT  REFERENCES asset.call_number (id)
+                                     DEFERRABLE INITIALLY DEFERRED,
+       receive_unit_template INT     REFERENCES asset.copy_template (id)
+                                     DEFERRABLE INITIALLY DEFERRED,
+       bind_call_number      BIGINT  REFERENCES asset.call_number (id)
+                                     DEFERRABLE INITIALLY DEFERRED,
+       bind_unit_template    INT     REFERENCES asset.copy_template (id)
+                                     DEFERRABLE INITIALLY DEFERRED,
+       unit_label_base       TEXT,
+       unit_label_suffix     TEXT
+);
+
+CREATE TABLE serial.stream (
+       id              SERIAL  PRIMARY KEY,
+       distribution    INT     NOT NULL
+                               REFERENCES serial.distribution (id)
+                               ON DELETE CASCADE
+                               DEFERRABLE INITIALLY DEFERRED,
+       routing_label   TEXT
+);
+
+CREATE UNIQUE INDEX label_once_per_dist
+       ON serial.stream (distribution, routing_label)
+       WHERE routing_label IS NOT NULL;
+
+CREATE TABLE serial.routing_list_user (
+       id             SERIAL       PRIMARY KEY,
+       stream         INT          NOT NULL
+                                   REFERENCES serial.stream
+                                   ON DELETE CASCADE
+                                   DEFERRABLE INITIALLY DEFERRED,
+       pos            INT          NOT NULL DEFAULT 1,
+       reader         INT          REFERENCES actor.usr
+                                   ON DELETE CASCADE
+                                   DEFERRABLE INITIALLY DEFERRED,
+       department     TEXT,
+       note           TEXT,
+       CONSTRAINT one_pos_per_routing_list UNIQUE ( stream, pos ),
+       CONSTRAINT reader_or_dept CHECK
+       (
+           -- Recipient is a person or a department, but not both
+               (reader IS NOT NULL AND department IS NULL) OR
+               (reader IS NULL AND department IS NOT NULL)
+       )
 );
 
 CREATE TABLE serial.issuance (
-       id              SERIAL  PRIMARY KEY,
-       subscription    INT     NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       target_copy     BIGINT  REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       binding_unit    INT     REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       label           TEXT
+       id              SERIAL    PRIMARY KEY,
+       creator         INT       NOT NULL
+                                 REFERENCES actor.usr (id)
+                                                         DEFERRABLE INITIALLY DEFERRED,
+       editor          INT       NOT NULL
+                                 REFERENCES actor.usr (id)
+                                 DEFERRABLE INITIALLY DEFERRED,
+       create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       subscription    INT       NOT NULL
+                                 REFERENCES serial.subscription (id)
+                                 ON DELETE CASCADE
+                                 DEFERRABLE INITIALLY DEFERRED,
+       label           TEXT,
+       date_published  TIMESTAMP WITH TIME ZONE,
+       holding_code    TEXT,
+       holding_type    TEXT      CONSTRAINT valid_holding_type CHECK
+                                 (
+                                     holding_type IS NULL
+                                     OR holding_type IN ('basic','supplement','index')
+                                 ),
+       holding_link_id INT
+       -- TODO: add columns for separate enumeration/chronology values
+);
+
+CREATE TABLE serial.unit (
+       label           TEXT,
+       label_sort_key  TEXT,
+       contents        TEXT    NOT NULL
+) INHERITS (asset.copy);
+
+ALTER TABLE serial.unit ADD PRIMARY KEY (id);
+
+CREATE TABLE serial.item (
+       id              SERIAL  PRIMARY KEY,
+       creator         INT     NOT NULL
+                               REFERENCES actor.usr (id)
+                               DEFERRABLE INITIALLY DEFERRED,
+       editor          INT     NOT NULL
+                               REFERENCES actor.usr (id)
+                               DEFERRABLE INITIALLY DEFERRED,
+       create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
+       issuance        INT     NOT NULL
+                               REFERENCES serial.issuance (id)
+                               ON DELETE CASCADE
+                               DEFERRABLE INITIALLY DEFERRED,
+       stream          INT     NOT NULL
+                               REFERENCES serial.stream (id)
+                               ON DELETE CASCADE
+                               DEFERRABLE INITIALLY DEFERRED,
+       unit            INT     REFERENCES serial.unit (id)
+                               ON DELETE SET NULL
+                               DEFERRABLE INITIALLY DEFERRED,
+       uri             INT     REFERENCES asset.uri (id)
+                               ON DELETE SET NULL
+                               DEFERRABLE INITIALLY DEFERRED,
+       date_expected   TIMESTAMP WITH TIME ZONE,
+       date_received   TIMESTAMP WITH TIME ZONE
+);
+
+CREATE TABLE serial.item_note (
+       id          SERIAL  PRIMARY KEY,
+       item        INT     NOT NULL
+                           REFERENCES serial.item (id)
+                           ON DELETE CASCADE
+                           DEFERRABLE INITIALLY DEFERRED,
+       creator     INT     NOT NULL
+                           REFERENCES actor.usr (id)
+                           DEFERRABLE INITIALLY DEFERRED,
+       create_date TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
+       pub         BOOL    NOT NULL    DEFAULT FALSE,
+       title       TEXT    NOT NULL,
+       value       TEXT    NOT NULL
 );
 
 CREATE TABLE serial.bib_summary (
-       id                      SERIAL  PRIMARY KEY,
-       call_number             INT     UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       generated_coverage      TEXT    NOT NULL,
-       textual_holdings        TEXT
+       id                  SERIAL  PRIMARY KEY,
+       distribution        INT     NOT NULL
+                                   REFERENCES serial.distribution (id)
+                                   ON DELETE CASCADE
+                                   DEFERRABLE INITIALLY DEFERRED,
+       generated_coverage  TEXT    NOT NULL,
+       textual_holdings    TEXT
 );
 
 CREATE TABLE serial.sup_summary (
-       id                      SERIAL  PRIMARY KEY,
-       call_number             INT     UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       generated_coverage      TEXT    NOT NULL,
-       textual_holdings        TEXT
+       id                  SERIAL  PRIMARY KEY,
+       distribution        INT     NOT NULL
+                                   REFERENCES serial.distribution (id)
+                                   ON DELETE CASCADE
+                                   DEFERRABLE INITIALLY DEFERRED,
+       generated_coverage  TEXT    NOT NULL,
+       textual_holdings    TEXT
 );
 
 CREATE TABLE serial.index_summary (
-       id                      SERIAL  PRIMARY KEY,
-       call_number             INT     UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
-       generated_coverage      TEXT    NOT NULL,
-       textual_holdings        TEXT
+       id                  SERIAL  PRIMARY KEY,
+       distribution        INT     NOT NULL
+                                   REFERENCES serial.distribution (id)
+                                   ON DELETE CASCADE
+                                   DEFERRABLE INITIALLY DEFERRED,
+       generated_coverage  TEXT    NOT NULL,
+       textual_holdings    TEXT
 );
 
 COMMIT;
-