3 DROP SCHEMA serial CASCADE;
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 creator INT NOT NULL DEFAULT 1,
13 editor INT NOT NULL DEFAULT 1,
15 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
16 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
17 active BOOL NOT NULL DEFAULT TRUE,
18 deleted BOOL NOT NULL DEFAULT FALSE,
20 last_xact_id TEXT NOT NULL
22 CREATE INDEX serial_record_entry_creator_idx ON serial.record_entry ( creator );
23 CREATE INDEX serial_record_entry_editor_idx ON serial.record_entry ( editor );
25 CREATE TABLE serial.full_rec (
26 id BIGSERIAL PRIMARY KEY,
27 record BIGINT NOT NULL REFERENCES serial.record_entry(id) DEFERRABLE INITIALLY DEFERRED,
33 index_vector tsvector NOT NULL
35 CREATE INDEX serial_full_rec_record_idx ON serial.full_rec (record);
36 CREATE INDEX serial_full_rec_tag_part_idx ON serial.full_rec (SUBSTRING(tag FROM 2));
37 CREATE TRIGGER serial_full_rec_fti_trigger
38 BEFORE UPDATE OR INSERT ON serial.full_rec
39 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
41 CREATE INDEX serial_full_rec_index_vector_idx ON serial.full_rec USING GIST (index_vector);
42 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
43 CREATE INDEX serial_full_rec_value_tpo_index ON serial.full_rec (value text_pattern_ops);
45 CREATE TABLE serial.subscription (
46 id SERIAL PRIMARY KEY,
47 callnumber BIGINT REFERENCES asset.call_number (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
48 uri INT REFERENCES asset.uri (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
49 start_date DATE NOT NULL,
50 end_date DATE -- interpret NULL as current subscription
53 CREATE TABLE serial.binding_unit (
54 id SERIAL PRIMARY KEY,
55 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
57 CONSTRAINT bu_label_once_per_sub UNIQUE (subscription, label)
60 CREATE TABLE serial.issuance (
61 id SERIAL PRIMARY KEY,
62 subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
63 target_copy BIGINT REFERENCES asset.copy (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
64 location BIGINT REFERENCES asset.copy_location(id) DEFERRABLE INITIALLY DEFERRED,
65 binding_unit INT REFERENCES serial.binding_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
69 CREATE TABLE serial.bib_summary (
70 id SERIAL PRIMARY KEY,
71 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
72 generated_coverage TEXT NOT NULL,
76 CREATE TABLE serial.sup_summary (
77 id SERIAL PRIMARY KEY,
78 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
79 generated_coverage TEXT NOT NULL,
83 CREATE TABLE serial.index_summary (
84 id SERIAL PRIMARY KEY,
85 subscription INT UNIQUE NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
86 generated_coverage TEXT NOT NULL,