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();
41 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
43 CREATE TABLE authority.bib_linking (
44 id BIGSERIAL PRIMARY KEY,
45 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
46 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
48 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
49 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
51 CREATE TABLE authority.record_note (
52 id BIGSERIAL PRIMARY KEY,
53 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
55 creator INT NOT NULL DEFAULT 1,
56 editor INT NOT NULL DEFAULT 1,
57 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
58 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
60 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
61 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
62 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
64 CREATE TABLE authority.rec_descriptor (
65 id BIGSERIAL PRIMARY KEY,
70 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
72 CREATE TABLE authority.full_rec (
73 id BIGSERIAL PRIMARY KEY,
74 record BIGINT NOT NULL,
80 index_vector tsvector NOT NULL
82 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
83 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
84 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
85 CREATE TRIGGER authority_full_rec_fti_trigger
86 BEFORE UPDATE OR INSERT ON authority.full_rec
87 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
89 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
90 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
91 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
93 CREATE OR REPLACE VIEW authority.tracing_links AS
94 SELECT main.record AS record,
97 main.value AS main_value,
98 substr(link.value,1,1) AS relationship,
99 substr(link.value,2,1) AS use_restriction,
100 substr(link.value,3,1) AS deprecation,
101 substr(link.value,4,1) AS display_restriction,
102 link_value.id AS link_id,
103 link_value.tag AS link_tag,
104 link_value.value AS link_value
105 FROM authority.full_rec main
106 JOIN authority.full_rec link
107 ON ( link.record = main.record
108 AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
109 AND link.subfield = 'w' )
110 JOIN authority.full_rec link_value
111 ON ( link_value.record = main.record
112 AND link_value.tag = link.tag
113 AND link_value.subfield = 'a' )
114 WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
115 AND main.subfield = 'a';
117 -- Function to generate an ephemeral overlay template from an authority record
118 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
124 my $r = MARC::Record->new_from_xml( $xml );
126 return undef unless ($r);
128 my $id = shift() || $r->subfield( '901' => 'c' );
129 $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
130 return undef unless ($id); # We need an ID!
132 my $tmpl = MARC::Record->new();
135 for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
137 my $tag = $field->tag;
138 my $i1 = $field->indicator(1);
139 my $i2 = $field->indicator(2);
140 my $sf = join '', map { $_->[0] } $field->subfields;
141 my @data = map { @$_ } $field->subfields;
145 # Map the authority field to bib fields it can control.
146 if ($tag >= 100 and $tag <= 111) { # names
147 @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
148 } elsif ($tag eq '130') { # uniform title
149 @replace_them = qw/130 240 440 730 830/;
150 } elsif ($tag >= 150 and $tag <= 155) { # subjects
151 @replace_them = ($tag + 500);
152 } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions
153 @replace_them = qw/100 400 600 700 800 110 410 610 710 810 111 411 611 711 811 130 240 440 730 830 650 651 655/;
158 # Dummy up the bib-side data
159 $tmpl->append_fields(
161 MARC::Field->new( $_, $i1, $i2, @data )
165 # Construct some 'replace' rules
166 push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
169 # Insert the replace rules into the template
170 $tmpl->append_fields(
171 MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
174 $xml = $tmpl->as_xml_record;
175 $xml =~ s/^<\?.+?\?>$//mo;
177 # Leave formatting intact for now
179 #$xml =~ s/>\s+</></sgo;
183 $func$ LANGUAGE PLPERLU;
185 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
186 SELECT authority.generate_overlay_template( marc, id ) FROM authority.record_entry WHERE id = $1;
189 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT ) RETURNS TEXT AS $func$
190 SELECT authority.generate_overlay_template( $1, NULL );