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.control_set (
26 id SERIAL PRIMARY KEY,
27 name TEXT NOT NULL UNIQUE, -- i18n
28 description TEXT -- i18n
31 CREATE TABLE authority.control_set_authority_field (
32 id SERIAL PRIMARY KEY,
33 main_entry INT REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
34 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
36 sf_list TEXT NOT NULL,
37 name TEXT NOT NULL, -- i18n
38 description TEXT -- i18n
41 CREATE TABLE authority.control_set_bib_field (
42 id SERIAL PRIMARY KEY,
43 authority_field INT NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
47 CREATE TABLE authority.thesaurus (
48 code TEXT PRIMARY KEY, -- MARC21 thesaurus code
49 control_set INT NOT NULL REFERENCES authority.control_set (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
50 name TEXT NOT NULL UNIQUE, -- i18n
51 description TEXT -- i18n
54 CREATE TABLE authority.record_entry (
55 id BIGSERIAL PRIMARY KEY,
56 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
57 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
58 creator INT NOT NULL DEFAULT 1,
59 editor INT NOT NULL DEFAULT 1,
60 active BOOL NOT NULL DEFAULT TRUE,
61 deleted BOOL NOT NULL DEFAULT FALSE,
63 control_set INT REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
65 last_xact_id TEXT NOT NULL,
68 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
69 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
70 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
71 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();
72 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
73 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
74 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);
76 CREATE TABLE authority.bib_linking (
77 id BIGSERIAL PRIMARY KEY,
78 bib BIGINT NOT NULL REFERENCES biblio.record_entry (id),
79 authority BIGINT NOT NULL REFERENCES authority.record_entry (id)
81 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
82 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
84 CREATE TABLE authority.record_note (
85 id BIGSERIAL PRIMARY KEY,
86 record BIGINT NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
88 creator INT NOT NULL DEFAULT 1,
89 editor INT NOT NULL DEFAULT 1,
90 create_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
91 edit_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
93 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
94 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
95 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
97 CREATE TABLE authority.rec_descriptor (
98 id BIGSERIAL PRIMARY KEY,
104 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
106 CREATE TABLE authority.full_rec (
107 id BIGSERIAL PRIMARY KEY,
108 record BIGINT NOT NULL,
109 tag CHAR(3) NOT NULL,
114 index_vector tsvector NOT NULL
116 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
117 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
118 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
119 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
120 CREATE TRIGGER authority_full_rec_fti_trigger
121 BEFORE UPDATE OR INSERT ON authority.full_rec
122 FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
124 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
125 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
126 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
128 CREATE OR REPLACE VIEW authority.tracing_links AS
129 SELECT main.record AS record,
131 main.tag AS main_tag,
132 main.value AS main_value,
133 substr(link.value,1,1) AS relationship,
134 substr(link.value,2,1) AS use_restriction,
135 substr(link.value,3,1) AS deprecation,
136 substr(link.value,4,1) AS display_restriction,
137 link_value.id AS link_id,
138 link_value.tag AS link_tag,
139 link_value.value AS link_value
140 FROM authority.full_rec main
141 JOIN authority.full_rec link
142 ON (link.record = main.record
143 AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
144 AND link.subfield = 'w' )
145 JOIN authority.full_rec link_value
146 ON (link_value.record = main.record
147 AND link_value.tag = link.tag
148 AND link_value.subfield = 'a' )
149 WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
150 AND main.subfield = 'a';
152 -- Function to generate an ephemeral overlay template from an authority record
153 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
156 use MARC::File::XML (BinaryEncoding => 'UTF-8');
159 MARC::Charset->assume_unicode(1);
162 my $r = MARC::Record->new_from_xml( $xml );
164 return undef unless ($r);
166 my $id = shift() || $r->subfield( '901' => 'c' );
167 $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
168 return undef unless ($id); # We need an ID!
170 my $tmpl = MARC::Record->new();
171 $tmpl->encoding( 'UTF-8' );
174 for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
176 my $tag = $field->tag;
177 my $i1 = $field->indicator(1);
178 my $i2 = $field->indicator(2);
179 my $sf = join '', map { $_->[0] } $field->subfields;
180 my @data = map { @$_ } $field->subfields;
184 # Map the authority field to bib fields it can control.
185 if ($tag >= 100 and $tag <= 111) { # names
186 @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
187 } elsif ($tag eq '130') { # uniform title
188 @replace_them = qw/130 240 440 730 830/;
189 } elsif ($tag >= 150 and $tag <= 155) { # subjects
190 @replace_them = ($tag + 500);
191 } elsif ($tag >= 180 and $tag <= 185) { # floating subdivisions
192 @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/;
197 # Dummy up the bib-side data
198 $tmpl->append_fields(
200 MARC::Field->new( $_, $i1, $i2, @data )
204 # Construct some 'replace' rules
205 push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
208 # Insert the replace rules into the template
209 $tmpl->append_fields(
210 MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
213 $xml = $tmpl->as_xml_record;
214 $xml =~ s/^<\?.+?\?>$//mo;
216 $xml =~ s/>\s+</></sgo;
220 $func$ LANGUAGE PLPERLU;
222 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
223 SELECT authority.generate_overlay_template( marc, id ) FROM authority.record_entry WHERE id = $1;
226 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT ) RETURNS TEXT AS $func$
227 SELECT authority.generate_overlay_template( $1, NULL );
230 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
232 moved_objects INT := 0;
234 bib_rec biblio.record_entry%ROWTYPE;
235 auth_link authority.bib_linking%ROWTYPE;
239 -- Defining our terms:
240 -- "target record" = the record that will survive the merge
241 -- "source record" = the record that is sacrifing its existence and being
242 -- replaced by the target record
244 -- 1. Update all bib records with the ID from target_record in their $0
247 FROM biblio.record_entry bre
248 JOIN authority.bib_linking abl ON abl.bib = bre.id
249 WHERE abl.authority = source_record
252 UPDATE biblio.record_entry
253 SET marc = REGEXP_REPLACE(
255 E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
256 E'\\1' || target_record || '<',
259 WHERE id = bib_rec.id;
261 moved_objects := moved_objects + 1;
264 -- 2. Grab the current value of reingest on same MARC flag
265 SELECT enabled INTO ingest_same
266 FROM config.internal_flag
267 WHERE name = 'ingest.reingest.force_on_same_marc'
270 -- 3. Temporarily set reingest on same to TRUE
271 UPDATE config.internal_flag
273 WHERE name = 'ingest.reingest.force_on_same_marc'
276 -- 4. Make a harmless update to target_record to trigger auto-update
277 -- in linked bibliographic records
278 UPDATE authority.record_entry
280 WHERE id = target_record;
282 -- 5. "Delete" source_record
283 DELETE FROM authority.record_entry WHERE id = source_record;
285 -- 6. Set "reingest on same MARC" flag back to initial value
286 UPDATE config.internal_flag
287 SET enabled = ingest_same
288 WHERE name = 'ingest.reingest.force_on_same_marc'
291 RETURN moved_objects;
293 $func$ LANGUAGE plpgsql;