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 authority CASCADE;
21 CREATE SCHEMA authority;
23 CREATE TABLE authority.record_entry (
24 id BIGSERIAL PRIMARY KEY,
25 arn_source TEXT NOT NULL DEFAULT 'AUTOGEN',
26 arn_value TEXT NOT NULL,
27 creator INT NOT NULL DEFAULT 1,
28 editor INT NOT NULL DEFAULT 1,
29 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
30 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
31 active BOOL NOT NULL DEFAULT TRUE,
32 deleted BOOL NOT NULL DEFAULT FALSE,
35 last_xact_id TEXT NOT NULL
37 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
38 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
39 CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted = FALSE OR deleted IS FALSE;
40 CREATE TRIGGER a_marcxml_is_well_formed BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.check_marcxml_well_formed();
42 CREATE TABLE authority.record_note (
43 id BIGSERIAL PRIMARY KEY,
44 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
46 creator INT NOT NULL DEFAULT 1,
47 editor INT NOT NULL DEFAULT 1,
48 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
49 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
51 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
52 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
53 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
55 CREATE TABLE authority.rec_descriptor (
56 id BIGSERIAL PRIMARY KEY,
61 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
63 CREATE TABLE authority.full_rec (
64 id BIGSERIAL PRIMARY KEY,
65 record BIGINT NOT NULL,
71 index_vector tsvector NOT NULL
73 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
74 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
75 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
76 CREATE TRIGGER authority_full_rec_fti_trigger
77 BEFORE UPDATE OR INSERT ON authority.full_rec
78 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
80 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
81 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
82 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
84 CREATE OR REPLACE VIEW authority.tracing_links AS
85 SELECT main.record AS record,
88 main.value AS main_value,
89 substr(link.value,1,1) AS relationship,
90 substr(link.value,2,1) AS use_restriction,
91 substr(link.value,3,1) AS deprecation,
92 substr(link.value,4,1) AS display_restriction,
93 link_value.id AS link_id,
94 link_value.tag AS link_tag,
95 link_value.value AS link_value
96 FROM authority.full_rec main
97 JOIN authority.full_rec link
98 ON ( link.record = main.record
99 AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
100 AND link.subfield = 'w' )
101 JOIN authority.full_rec link_value
102 ON ( link_value.record = main.record
103 AND link_value.tag = link.tag
104 AND link_value.subfield = 'a' )
105 WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
106 AND main.subfield = 'a';