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.control_set (
  26     id          SERIAL  PRIMARY KEY,
  27     name        TEXT    NOT NULL UNIQUE, -- i18n
  28     description TEXT                     -- i18n
  29 );
  30
  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,
  35     tag         CHAR(3) NOT NULL,
  36     sf_list     TEXT    NOT NULL,
  37     name        TEXT    NOT NULL, -- i18n
  38     description TEXT              -- i18n
  39 );
  40
  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,
  44     tag             CHAR(3) NOT NULL
  45 );
  46
  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
  52 );
  53
  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,
  62     source          INT,
  63     control_set     INT     REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  64     marc            TEXT    NOT NULL,
  65     last_xact_id    TEXT    NOT NULL,
  66     owner           INT
  67 );
  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);
  75
  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)
  80 );
  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 );
  83
  84 CREATE TABLE authority.record_note (
  85     id          BIGSERIAL   PRIMARY KEY,
  86     record      BIGINT      NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
  87     value       TEXT        NOT NULL,
  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()
  92 );
  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 );
  96
  97 CREATE TABLE authority.rec_descriptor (
  98     id              BIGSERIAL PRIMARY KEY,
  99     record          BIGINT,
 100     record_status   TEXT,
 101     encoding_level  TEXT,
 102     thesaurus       TEXT
 103 );
 104 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
 105
 106 CREATE TABLE authority.full_rec (
 107     id              BIGSERIAL   PRIMARY KEY,
 108     record          BIGINT      NOT NULL,
 109     tag             CHAR(3)     NOT NULL,
 110     ind1            TEXT,
 111     ind2            TEXT,
 112     subfield        TEXT,
 113     value           TEXT        NOT NULL,
 114     index_vector    tsvector    NOT NULL
 115 );
 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);
 123
 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);
 127
 128 -- Intended to be used in a unique index on authority.record_entry like so:
 129 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
 130 --   ON authority.record_entry (authority.normalize_heading(marc))
 131 --   WHERE deleted IS FALSE or deleted = FALSE;
 132 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
 133 DECLARE
 134     acsaf           authority.control_set_authority_field%ROWTYPE;
 135     tag_used        TEXT;
 136     sf              TEXT;
 137     thes_code       TEXT;
 138     cset            INT;
 139     heading_text    TEXT;
 140     tmp_text        TEXT;
 141 BEGIN
 142     thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
 143     IF thes_code IS NULL THEN
 144         thes_code := '|';
 145     END IF;
 146
 147     SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
 148     IF NOT FOUND THEN
 149         cset = 1;
 150     END IF;
 151
 152     heading_text := '';
 153     FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
 154         tag_use := acsaf.tag;
 155         FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
 156             tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
 157             IF tmp_text IS NOT NULL THEN
 158                 heading_text := heading_text || E'\U2021' || sf || ' ' || tmp_text;
 159             END IF;
 160         END LOOP;
 161         EXIT WHEN heading_text <> '';
 162     END LOOP;
 163
 164     IF heading_text <> '' THEN
 165         heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
 166     ELSE
 167         heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
 168     END IF;
 169
 170     RETURN heading_text;
 171 END;
 172 $func$ LANGUAGE PLPGSQL IMMUTABLE;
 173
 174 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
 175 Extract the authority heading, thesaurus, and NACO-normalized values
 176 from an authority record. The primary purpose is to build a unique
 177 index to defend against duplicated authority records from the same
 178 thesaurus.
 179 $$;
 180
 181 -- Adding indexes using oils_xpath_string() for the main entry tags described in
 182 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
 183 -- the existing index on authority.normalize_heading() helps already with a record in hand
 184 CREATE OR REPLACE VIEW authority.tracing_links AS
 185     SELECT  main.record AS record,
 186             main.id AS main_id,
 187             main.tag AS main_tag,
 188             oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
 189             authority.normalize_heading(are.marc) AS normalized_main_value,
 190             substr(link.value,1,1) AS relationship,
 191             substr(link.value,2,1) AS use_restriction,
 192             substr(link.value,3,1) AS deprecation,
 193             substr(link.value,4,1) AS display_restriction,
 194             link.id AS link_id,
 195             link.tag AS link_tag,
 196             oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value
 197       FROM  authority.full_rec main
 198             JOIN authority.record_entry are ON (main.record = are.id)
 199             JOIN authority.control_set_authority_field main_entry
 200                 ON (main_entry.tag = main.tag
 201                     AND main_entry.main_entry IS NULL
 202                     AND main.subfield = 'a' )
 203             JOIN authority.control_set_authority_field sub_entry
 204                 ON (main_entry.id = sub_entry.main_entry)
 205             JOIN authority.full_rec link
 206                 ON (link.record = main.record
 207                     AND link.tag = sub_entry.tag
 208                     AND link.subfield = 'w' );
 209
 210 -- Function to generate an ephemeral overlay template from an authority record
 211 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
 212 DECLARE
 213     cset                INT;
 214     main_entry          authority.control_set_authority_field%ROWTYPE;
 215     bib_field           authority.control_set_bib_field%ROWTYPE;
 216     auth_id             INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
 217     replace_data        XML[] DEFAULT '{}'::XML[];
 218     replace_rules       TEXT[] DEFAULT '{}'::TEXT[];
 219     auth_field          TEXT;
 220 BEGIN
 221     IF auth_id IS NULL THEN
 222         RETURN NULL;
 223     END IF;
 224
 225     -- Default to the LoC controll set
 226     SELECT COALESCE(control_set,1) INTO cset FROM authority.record_entry WHERE id = auth_id;
 227
 228     FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
 229         auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml);
 230         IF ARRAY_LENGTH(auth_field) > 0 THEN
 231             FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
 232                 replace_data := replace_data || XMLELEMENT( name datafield, bib_field.tag AS tag, XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
 233                 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
 234             END LOOP;
 235             EXIT;
 236         END IF;
 237     END LOOP;
 238
 239     RETURN XMLELEMENT(
 240         name record,
 241         XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns)
 242         XMLELEMENT( name leader, '00881nam a2200193   4500'),
 243         replace_data,
 244         XMLELEMENT(
 245             name datafield,
 246             '905' AS tag,
 247             ' ' AS ind1,
 248             ' ' AS ind2,
 249             XMLELEMENT(
 250                 name subfield,
 251                 'r' AS code,
 252                 ARRAY_TO_STRING(replace_rules,',')
 253             )
 254         )
 255     )::TEXT;
 256 END;
 257 $f$ STABLE LANGUAGE PLPGSQL;
 258
 259 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
 260 DECLARE
 261     moved_objects INT := 0;
 262     bib_id        INT := 0;
 263     bib_rec       biblio.record_entry%ROWTYPE;
 264     auth_link     authority.bib_linking%ROWTYPE;
 265     ingest_same   boolean;
 266 BEGIN
 267
 268     -- Defining our terms:
 269     -- "target record" = the record that will survive the merge
 270     -- "source record" = the record that is sacrifing its existence and being
 271     --   replaced by the target record
 272
 273     -- 1. Update all bib records with the ID from target_record in their $0
 274     FOR bib_rec IN
 275             SELECT  bre.*
 276               FROM  biblio.record_entry bre
 277                     JOIN authority.bib_linking abl ON abl.bib = bre.id
 278               WHERE abl.authority = source_record
 279         LOOP
 280
 281         UPDATE  biblio.record_entry
 282           SET   marc = REGEXP_REPLACE(
 283                     marc,
 284                     E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
 285                     E'\\1' || target_record || '<',
 286                     'g'
 287                 )
 288           WHERE id = bib_rec.id;
 289
 290           moved_objects := moved_objects + 1;
 291     END LOOP;
 292
 293     -- 2. Grab the current value of reingest on same MARC flag
 294     SELECT  enabled INTO ingest_same
 295       FROM  config.internal_flag
 296       WHERE name = 'ingest.reingest.force_on_same_marc'
 297     ;
 298
 299     -- 3. Temporarily set reingest on same to TRUE
 300     UPDATE  config.internal_flag
 301       SET   enabled = TRUE
 302       WHERE name = 'ingest.reingest.force_on_same_marc'
 303     ;
 304
 305     -- 4. Make a harmless update to target_record to trigger auto-update
 306     --    in linked bibliographic records
 307     UPDATE  authority.record_entry
 308       SET   deleted = FALSE
 309       WHERE id = target_record;
 310
 311     -- 5. "Delete" source_record
 312     DELETE FROM authority.record_entry WHERE id = source_record;
 313
 314     -- 6. Set "reingest on same MARC" flag back to initial value
 315     UPDATE  config.internal_flag
 316       SET   enabled = ingest_same
 317       WHERE name = 'ingest.reingest.force_on_same_marc'
 318     ;
 319
 320     RETURN moved_objects;
 321 END;
 322 $func$ LANGUAGE plpgsql;
 323
 324 COMMIT;