2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS biblio CASCADE;
23 CREATE SEQUENCE biblio.autogen_tcn_value_seq;
24 CREATE OR REPLACE FUNCTION biblio.next_autogen_tcn_value () RETURNS TEXT AS $$
25 BEGIN RETURN 'AUTOGENERATED-' || nextval('biblio.autogen_tcn_value_seq'::TEXT); END;
28 CREATE OR REPLACE FUNCTION biblio.check_marcxml_well_formed () RETURNS TRIGGER AS $func$
31 IF xml_is_well_formed(NEW.marc) THEN
34 RAISE EXCEPTION 'Attempted to % MARCXML that is not well formed', TG_OP;
38 $func$ LANGUAGE PLPGSQL;
40 CREATE TABLE biblio.record_entry (
41 id BIGSERIAL PRIMARY KEY,
42 creator INT NOT NULL DEFAULT 1,
43 editor INT NOT NULL DEFAULT 1,
46 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
47 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
48 active BOOL NOT NULL DEFAULT TRUE,
49 deleted BOOL NOT NULL DEFAULT FALSE,
51 tcn_source TEXT NOT NULL DEFAULT 'AUTOGEN',
52 tcn_value TEXT NOT NULL DEFAULT biblio.next_autogen_tcn_value(),
54 last_xact_id TEXT NOT NULL,
58 CREATE INDEX biblio_record_entry_creator_idx ON biblio.record_entry ( creator );
59 CREATE INDEX biblio_record_entry_create_date_idx ON biblio.record_entry ( create_date );
60 CREATE INDEX biblio_record_entry_editor_idx ON biblio.record_entry ( editor );
61 CREATE INDEX biblio_record_entry_edit_date_idx ON biblio.record_entry ( edit_date );
62 CREATE INDEX biblio_record_entry_fp_idx ON biblio.record_entry ( fingerprint );
63 CREATE UNIQUE INDEX biblio_record_unique_tcn ON biblio.record_entry (tcn_value) WHERE deleted = FALSE OR deleted IS FALSE;
64 CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();
65 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
66 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
68 CREATE TABLE biblio.record_note (
69 id BIGSERIAL PRIMARY KEY,
70 record BIGINT NOT NULL,
72 creator INT NOT NULL DEFAULT 1,
73 editor INT NOT NULL DEFAULT 1,
74 pub BOOL NOT NULL DEFAULT FALSE,
75 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
76 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
78 CREATE INDEX biblio_record_note_record_idx ON biblio.record_note ( record );
79 CREATE INDEX biblio_record_note_creator_idx ON biblio.record_note ( creator );
80 CREATE INDEX biblio_record_note_editor_idx ON biblio.record_note ( editor );
82 CREATE TABLE biblio.peer_type (
83 id SERIAL PRIMARY KEY,
84 name TEXT NOT NULL UNIQUE -- i18n
87 CREATE TABLE biblio.peer_bib_copy_map (
88 id SERIAL PRIMARY KEY,
89 peer_type INT NOT NULL REFERENCES biblio.peer_type (id),
90 peer_record BIGINT NOT NULL REFERENCES biblio.record_entry (id),
91 target_copy BIGINT NOT NULL -- can't use fkey because of acp subtables
93 CREATE INDEX peer_bib_copy_map_record_idx ON biblio.peer_bib_copy_map (peer_record);
94 CREATE INDEX peer_bib_copy_map_copy_idx ON biblio.peer_bib_copy_map (target_copy);
96 CREATE TABLE biblio.monograph_part (
97 id SERIAL PRIMARY KEY,
98 record BIGINT NOT NULL REFERENCES biblio.record_entry (id),
100 label_sortkey TEXT NOT NULL,
101 deleted BOOL NOT NULL DEFAULT FALSE,
102 CONSTRAINT record_label_unique UNIQUE (record,label)
105 CREATE OR REPLACE FUNCTION biblio.normalize_biblio_monograph_part_sortkey () RETURNS TRIGGER AS $$
107 NEW.label_sortkey := REGEXP_REPLACE(
108 evergreen.lpad_number_substrings(
109 naco_normalize(NEW.label),
121 CREATE TRIGGER norm_sort_label BEFORE INSERT OR UPDATE ON biblio.monograph_part FOR EACH ROW EXECUTE PROCEDURE biblio.normalize_biblio_monograph_part_sortkey();