2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Copyright (C) 2010 Laurentian University
5 * Mike Rylander <miker@esilibrary.com>
6 * Dan Scott <dscott@laurentian.ca>
8 * This program is free software; you can redistribute it and/or
9 * modify it under the terms of the GNU General Public License
10 * as published by the Free Software Foundation; either version 2
11 * of the License, or (at your option) any later version.
13 * This program is distributed in the hope that it will be useful,
14 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 * GNU General Public License for more details.
20 DROP SCHEMA IF EXISTS authority CASCADE;
23 CREATE SCHEMA authority;
25 CREATE TABLE authority.record_entry (
26 id BIGSERIAL PRIMARY KEY,
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,
38 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
39 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
40 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
41 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 TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
43 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
44 CREATE RULE protect_authority_rec_delete AS ON DELETE TO authority.record_entry DO INSTEAD (UPDATE authority.record_entry SET deleted = TRUE WHERE OLD.id = authority.record_entry.id);
46 CREATE TABLE authority.bib_linking (
47 id BIGSERIAL PRIMARY KEY,
48 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
49 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
51 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
52 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
54 CREATE TABLE authority.record_note (
55 id BIGSERIAL PRIMARY KEY,
56 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
58 creator INT NOT NULL DEFAULT 1,
59 editor INT NOT NULL DEFAULT 1,
60 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
61 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
63 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
64 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
65 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
67 CREATE TABLE authority.rec_descriptor (
68 id BIGSERIAL PRIMARY KEY,
73 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
75 CREATE TABLE authority.full_rec (
76 id BIGSERIAL PRIMARY KEY,
77 record BIGINT NOT NULL,
83 index_vector tsvector NOT NULL
85 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
86 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
87 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
88 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
89 CREATE TRIGGER authority_full_rec_fti_trigger
90 BEFORE UPDATE OR INSERT ON authority.full_rec
91 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
93 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
94 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
95 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
97 CREATE OR REPLACE VIEW authority.tracing_links AS
98 SELECT main.record AS record,
100 main.tag AS main_tag,
101 main.value AS main_value,
102 substr(link.value,1,1) AS relationship,
103 substr(link.value,2,1) AS use_restriction,
104 substr(link.value,3,1) AS deprecation,
105 substr(link.value,4,1) AS display_restriction,
106 link_value.id AS link_id,
107 link_value.tag AS link_tag,
108 link_value.value AS link_value
109 FROM authority.full_rec main
110 JOIN authority.full_rec link
111 ON ( link.record = main.record
112 AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
113 AND link.subfield = 'w' )
114 JOIN authority.full_rec link_value
115 ON ( link_value.record = main.record
116 AND link_value.tag = link.tag
117 AND link_value.subfield = 'a' )
118 WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
119 AND main.subfield = 'a';
121 -- Function to generate an ephemeral overlay template from an authority record
122 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
125 use MARC::File::XML (BinaryEncoding => 'UTF-8');
128 MARC::Charset->assume_unicode(1);
131 my $r = MARC::Record->new_from_xml( $xml );
133 return undef unless ($r);
135 my $id = shift() || $r->subfield( '901' => 'c' );
136 $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
137 return undef unless ($id); # We need an ID!
139 my $tmpl = MARC::Record->new();
140 $tmpl->encoding( 'UTF-8' );
143 for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
145 my $tag = $field->tag;
146 my $i1 = $field->indicator(1);
147 my $i2 = $field->indicator(2);
148 my $sf = join '', map { $_->[0] } $field->subfields;
149 my @data = map { @$_ } $field->subfields;
153 # Map the authority field to bib fields it can control.
154 if ($tag >= 100 and $tag <= 111) { # names
155 @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
156 } elsif ($tag eq '130') { # uniform title
157 @replace_them = qw/130 240 440 730 830/;
158 } elsif ($tag >= 150 and $tag <= 155) { # subjects
159 @replace_them = ($tag + 500);
160 } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions
161 @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/;
166 # Dummy up the bib-side data
167 $tmpl->append_fields(
169 MARC::Field->new( $_, $i1, $i2, @data )
173 # Construct some 'replace' rules
174 push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
177 # Insert the replace rules into the template
178 $tmpl->append_fields(
179 MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
182 $xml = $tmpl->as_xml_record;
183 $xml =~ s/^<\?.+?\?>$//mo;
185 $xml =~ s/>\s+</></sgo;
189 $func$ LANGUAGE PLPERLU;
191 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
192 SELECT authority.generate_overlay_template( marc, id ) FROM authority.record_entry WHERE id = $1;
195 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT ) RETURNS TEXT AS $func$
196 SELECT authority.generate_overlay_template( $1, NULL );
199 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
201 moved_objects INT := 0;
203 bib_rec biblio.record_entry%ROWTYPE;
204 auth_link authority.bib_linking%ROWTYPE;
208 -- Defining our terms:
209 -- "target record" = the record that will survive the merge
210 -- "source record" = the record that is sacrifing its existence and being
211 -- replaced by the target record
213 -- 1. Update all bib records with the ID from target_record in their $0
214 FOR bib_rec IN SELECT bre.* FROM biblio.record_entry bre
215 INNER JOIN authority.bib_linking abl ON abl.bib = bre.id
216 WHERE abl.authority = source_record LOOP
218 UPDATE biblio.record_entry
219 SET marc = REGEXP_REPLACE(marc,
220 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
221 E'\\1' || target_record || '<', 'g')
222 WHERE id = bib_rec.id;
224 moved_objects := moved_objects + 1;
227 -- 2. Grab the current value of reingest on same MARC flag
228 SELECT enabled INTO ingest_same
229 FROM config.internal_flag
230 WHERE name = 'ingest.reingest.force_on_same_marc'
233 -- 3. Temporarily set reingest on same to TRUE
234 UPDATE config.internal_flag
236 WHERE name = 'ingest.reingest.force_on_same_marc'
239 -- 4. Make a harmless update to target_record to trigger auto-update
240 -- in linked bibliographic records
241 UPDATE authority.record_entry
243 WHERE id = target_record;
245 -- 5. "Delete" source_record
246 DELETE FROM authority.record_entry
247 WHERE id = source_record;
249 -- 6. Set "reingest on same MARC" flag back to initial value
250 UPDATE config.internal_flag
251 SET enabled = ingest_same
252 WHERE name = 'ingest.reingest.force_on_same_marc'
255 RETURN moved_objects;
257 $func$ LANGUAGE plpgsql;