Open-ILS/src/sql/Pg/011.schema.authority.sql

   1 /*
   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>
   7  *
   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.
  12  *
  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.
  17  *
  18  */
  19
  20 DROP SCHEMA IF EXISTS authority CASCADE;
  21
  22 BEGIN;
  23 CREATE SCHEMA authority;
  24
  25 CREATE TABLE authority.record_entry (
  26         id              BIGSERIAL       PRIMARY KEY,
  27         arn_source      TEXT            NOT NULL DEFAULT 'AUTOGEN',
  28         arn_value       TEXT            NOT NULL,
  29         creator         INT             NOT NULL DEFAULT 1,
  30         editor          INT             NOT NULL DEFAULT 1,
  31         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  32         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  33         active          BOOL            NOT NULL DEFAULT TRUE,
  34         deleted         BOOL            NOT NULL DEFAULT FALSE,
  35         source          INT,
  36         marc            TEXT            NOT NULL,
  37         last_xact_id    TEXT            NOT NULL,
  38         owner           INT
  39 );
  40 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
  41 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
  42 CREATE UNIQUE INDEX authority_record_unique_tcn ON authority.record_entry (arn_source,arn_value) WHERE deleted = FALSE OR deleted IS FALSE;
  43 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();
  44 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_901();
  45 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
  46 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);
  47
  48 CREATE TABLE authority.bib_linking (
  49     id          BIGSERIAL   PRIMARY KEY,
  50     bib         BIGINT      NOT NULL REFERENCES biblio.record_entry (id),
  51     authority   BIGINT      NOT NULL REFERENCES authority.record_entry (id)
  52 );
  53 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
  54 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
  55
  56 CREATE TABLE authority.record_note (
  57         id              BIGSERIAL       PRIMARY KEY,
  58         record          BIGINT          NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
  59         value           TEXT            NOT NULL,
  60         creator         INT             NOT NULL DEFAULT 1,
  61         editor          INT             NOT NULL DEFAULT 1,
  62         create_date     TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now(),
  63         edit_date       TIMESTAMP WITH TIME ZONE        NOT NULL DEFAULT now()
  64 );
  65 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
  66 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
  67 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
  68
  69 CREATE TABLE authority.rec_descriptor (
  70         id              BIGSERIAL PRIMARY KEY,
  71         record          BIGINT,
  72         record_status   TEXT,
  73         char_encoding   TEXT
  74 );
  75 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
  76
  77 CREATE TABLE authority.full_rec (
  78         id              BIGSERIAL       PRIMARY KEY,
  79         record          BIGINT          NOT NULL,
  80         tag             CHAR(3)         NOT NULL,
  81         ind1            TEXT,
  82         ind2            TEXT,
  83         subfield        TEXT,
  84         value           TEXT            NOT NULL,
  85         index_vector    tsvector        NOT NULL
  86 );
  87 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
  88 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
  89 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
  90 CREATE TRIGGER authority_full_rec_fti_trigger
  91         BEFORE UPDATE OR INSERT ON authority.full_rec
  92         FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
  93
  94 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
  95 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
  96 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
  97
  98 CREATE OR REPLACE VIEW authority.tracing_links AS
  99         SELECT  main.record AS record,
 100                 main.id AS main_id,
 101                 main.tag AS main_tag,
 102                 main.value AS main_value,
 103                 substr(link.value,1,1) AS relationship,
 104                 substr(link.value,2,1) AS use_restriction,
 105                 substr(link.value,3,1) AS deprecation,
 106                 substr(link.value,4,1) AS display_restriction,
 107                 link_value.id AS link_id,
 108                 link_value.tag AS link_tag,
 109                 link_value.value AS link_value
 110           FROM  authority.full_rec main
 111                 JOIN authority.full_rec link
 112                         ON (    link.record = main.record
 113                                 AND link.tag in ((main.tag::int + 400)::text, (main.tag::int + 300)::text)
 114                                 AND link.subfield = 'w' )
 115                 JOIN authority.full_rec link_value
 116                         ON (    link_value.record = main.record
 117                                 AND link_value.tag = link.tag
 118                                 AND link_value.subfield = 'a' )
 119           WHERE main.tag IN ('100','110','111','130','150','151','155','180','181','182','185')
 120                 AND main.subfield = 'a';
 121
 122 -- Function to generate an ephemeral overlay template from an authority record
 123 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT, BIGINT ) RETURNS TEXT AS $func$
 124
 125     use MARC::Record;
 126     use MARC::File::XML;
 127
 128     my $xml = shift;
 129     my $r = MARC::Record->new_from_xml( $xml );
 130
 131     return undef unless ($r);
 132
 133     my $id = shift() || $r->subfield( '901' => 'c' );
 134     $id =~ s/^\s*(?:\([^)]+\))?\s*(.+)\s*?$/$1/;
 135     return undef unless ($id); # We need an ID!
 136
 137     my $tmpl = MARC::Record->new();
 138
 139     my @rule_fields;
 140     for my $field ( $r->field( '1..' ) ) { # Get main entry fields from the authority record
 141
 142         my $tag = $field->tag;
 143         my $i1 = $field->indicator(1);
 144         my $i2 = $field->indicator(2);
 145         my $sf = join '', map { $_->[0] } $field->subfields;
 146         my @data = map { @$_ } $field->subfields;
 147
 148         my @replace_them;
 149
 150         # Map the authority field to bib fields it can control.
 151         if ($tag >= 100 and $tag <= 111) {       # names
 152             @replace_them = map { $tag + $_ } (0, 300, 500, 600, 700);
 153         } elsif ($tag eq '130') {                # uniform title
 154             @replace_them = qw/130 240 440 730 830/;
 155         } elsif ($tag >= 150 and $tag <= 155) {  # subjects
 156             @replace_them = ($tag + 500);
 157         } elsif ($tag >= 180 and $tag <= 185) {  # floating subdivisions
 158             @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/;
 159         } else {
 160             next;
 161         }
 162
 163         # Dummy up the bib-side data
 164         $tmpl->append_fields(
 165             map {
 166                 MARC::Field->new( $_, $i1, $i2, @data )
 167             } @replace_them
 168         );
 169
 170         # Construct some 'replace' rules
 171         push @rule_fields, map { $_ . $sf . '[0~\)' .$id . '$]' } @replace_them;
 172     }
 173
 174     # Insert the replace rules into the template
 175     $tmpl->append_fields(
 176         MARC::Field->new( '905' => ' ' => ' ' => 'r' => join(',', @rule_fields ) )
 177     );
 178
 179     $xml = $tmpl->as_xml_record;
 180     $xml =~ s/^<\?.+?\?>$//mo;
 181
 182     # Leave formatting intact for now
 183     #$xml =~ s/\n//sgo;
 184     #$xml =~ s/>\s+</></sgo;
 185
 186     return $xml;
 187
 188 $func$ LANGUAGE PLPERLU;
 189
 190 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
 191     SELECT authority.generate_overlay_template( marc, id ) FROM authority.record_entry WHERE id = $1;
 192 $func$ LANGUAGE SQL;
 193
 194 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( TEXT ) RETURNS TEXT AS $func$
 195     SELECT authority.generate_overlay_template( $1, NULL );
 196 $func$ LANGUAGE SQL;
 197
 198 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
 199 DECLARE
 200     moved_objects INT := 0;
 201     bib_id        INT := 0;
 202     bib_rec       biblio.record_entry%ROWTYPE;
 203     auth_link     authority.bib_linking%ROWTYPE;
 204 BEGIN
 205
 206     -- 1. Make source_record MARC a copy of the target_record to get auto-sync in linked bib records
 207     UPDATE authority.record_entry
 208       SET marc = (
 209         SELECT marc
 210           FROM authority.record_entry
 211           WHERE id = target_record
 212       )
 213       WHERE id = source_record;
 214
 215     -- 2. Update all bib records with the ID from target_record in their $0
 216     FOR bib_rec IN SELECT bre.* FROM biblio.record_entry bre
 217       INNER JOIN authority.bib_linking abl ON abl.bib = bre.id
 218       WHERE abl.authority = target_record LOOP
 219
 220         UPDATE biblio.record_entry
 221           SET marc = REGEXP_REPLACE(marc,
 222             E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
 223             E'\\1' || target_record || '<', 'g')
 224           WHERE id = bib_rec.id;
 225
 226           moved_objects := moved_objects + 1;
 227     END LOOP;
 228
 229     -- 3. "Delete" source_record
 230     DELETE FROM authority.record_entry
 231       WHERE id = source_record;
 232
 233     RETURN moved_objects;
 234 END;
 235 $func$ LANGUAGE plpgsql;
 236
 237 COMMIT;