-DROP SCHEMA serial CASCADE;
+DROP SCHEMA IF EXISTS serial CASCADE;
BEGIN;
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,
edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
active BOOL NOT NULL DEFAULT TRUE,
deleted BOOL NOT NULL DEFAULT FALSE,
- marc TEXT NOT NULL,
+ 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 TABLE serial.full_rec (
- id BIGSERIAL PRIMARY KEY,
- record BIGINT NOT NULL REFERENCES serial.record_entry(id) DEFERRABLE INITIALLY DEFERRED,
- tag CHAR(3) NOT NULL,
- ind1 TEXT,
- ind2 TEXT,
- subfield TEXT,
- value TEXT NOT NULL,
- index_vector tsvector NOT NULL
-);
-CREATE INDEX serial_full_rec_record_idx ON serial.full_rec (record);
-CREATE INDEX serial_full_rec_tag_part_idx ON serial.full_rec (SUBSTRING(tag FROM 2));
-CREATE TRIGGER serial_full_rec_fti_trigger
- BEFORE UPDATE OR INSERT ON serial.full_rec
- FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
+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 INDEX serial_full_rec_index_vector_idx ON serial.full_rec USING GIST (index_vector);
-/* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
-CREATE INDEX serial_full_rec_value_tpo_index ON serial.full_rec (value text_pattern_ops);
+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 -- interpret NULL as current subscription
+ 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
+);
+
+--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 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)
+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,
- location BIGINT REFERENCES asset.copy_location(id) 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,
- subscription 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,
- subscription 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,
- subscription 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;
-
projects / working / Evergreen.git / blobdiff