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.browse_axis (
  55     code        TEXT    PRIMARY KEY,
  56     name        TEXT    UNIQUE NOT NULL, -- i18n
  57     sorter      TEXT    REFERENCES config.record_attr_definition (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  58     description TEXT
  59 );
  60
  61 CREATE TABLE authority.browse_axis_authority_field_map (
  62     id          SERIAL  PRIMARY KEY,
  63     axis        TEXT    NOT NULL REFERENCES authority.browse_axis (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  64     field       INT     NOT NULL REFERENCES authority.control_set_authority_field (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
  65 );
  66
  67 CREATE TABLE authority.record_entry (
  68     id              BIGSERIAL    PRIMARY KEY,
  69     create_date     TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT now(),
  70     edit_date       TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT now(),
  71     creator         INT     NOT NULL DEFAULT 1,
  72     editor          INT     NOT NULL DEFAULT 1,
  73     active          BOOL    NOT NULL DEFAULT TRUE,
  74     deleted         BOOL    NOT NULL DEFAULT FALSE,
  75     source          INT,
  76     control_set     INT     REFERENCES authority.control_set (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
  77     marc            TEXT    NOT NULL,
  78     last_xact_id    TEXT    NOT NULL,
  79     owner           INT
  80 );
  81 CREATE INDEX authority_record_entry_creator_idx ON authority.record_entry ( creator );
  82 CREATE INDEX authority_record_entry_editor_idx ON authority.record_entry ( editor );
  83 CREATE INDEX authority_record_deleted_idx ON authority.record_entry(deleted) WHERE deleted IS FALSE OR deleted = false;
  84 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();
  85 CREATE TRIGGER b_maintain_901 BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE evergreen.maintain_901();
  86 CREATE TRIGGER c_maintain_control_numbers BEFORE INSERT OR UPDATE ON authority.record_entry FOR EACH ROW EXECUTE PROCEDURE maintain_control_numbers();
  87
  88 CREATE TABLE authority.bib_linking (
  89     id          BIGSERIAL   PRIMARY KEY,
  90     bib         BIGINT      NOT NULL REFERENCES biblio.record_entry (id),
  91     authority   BIGINT      NOT NULL REFERENCES authority.record_entry (id)
  92 );
  93 CREATE INDEX authority_bl_bib_idx ON authority.bib_linking ( bib );
  94 CREATE UNIQUE INDEX authority_bl_bib_authority_once_idx ON authority.bib_linking ( authority, bib );
  95
  96 CREATE TABLE authority.record_note (
  97     id          BIGSERIAL   PRIMARY KEY,
  98     record      BIGINT      NOT NULL REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED,
  99     value       TEXT        NOT NULL,
 100     creator     INT         NOT NULL DEFAULT 1,
 101     editor      INT         NOT NULL DEFAULT 1,
 102     create_date TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT now(),
 103     edit_date   TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT now()
 104 );
 105 CREATE INDEX authority_record_note_record_idx ON authority.record_note ( record );
 106 CREATE INDEX authority_record_note_creator_idx ON authority.record_note ( creator );
 107 CREATE INDEX authority_record_note_editor_idx ON authority.record_note ( editor );
 108
 109 CREATE TABLE authority.rec_descriptor (
 110     id              BIGSERIAL PRIMARY KEY,
 111     record          BIGINT,
 112     record_status   TEXT,
 113     encoding_level  TEXT,
 114     thesaurus       TEXT
 115 );
 116 CREATE INDEX authority_rec_descriptor_record_idx ON authority.rec_descriptor (record);
 117
 118 CREATE TABLE authority.full_rec (
 119     id              BIGSERIAL   PRIMARY KEY,
 120     record          BIGINT      NOT NULL,
 121     tag             CHAR(3)     NOT NULL,
 122     ind1            TEXT,
 123     ind2            TEXT,
 124     subfield        TEXT,
 125     value           TEXT        NOT NULL,
 126     index_vector    tsvector    NOT NULL
 127 );
 128 CREATE INDEX authority_full_rec_record_idx ON authority.full_rec (record);
 129 CREATE INDEX authority_full_rec_tag_subfield_idx ON authority.full_rec (tag, subfield);
 130 CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(tag FROM 2));
 131 CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a';
 132 CREATE TRIGGER authority_full_rec_fti_trigger
 133     BEFORE UPDATE OR INSERT ON authority.full_rec
 134     FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value);
 135
 136 CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector);
 137 /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */
 138 CREATE INDEX authority_full_rec_value_tpo_index ON authority.full_rec (value text_pattern_ops);
 139 /* But we still need this (boooo) for paging using >, <, etc */
 140 CREATE INDEX authority_full_rec_value_index ON authority.full_rec (value);
 141
 142 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; DELETE FROM authority.full_rec WHERE record = OLD.id);
 143
 144 -- Intended to be used in a unique index on authority.record_entry like so:
 145 -- CREATE UNIQUE INDEX unique_by_heading_and_thesaurus
 146 --   ON authority.record_entry (authority.normalize_heading(marc))
 147 --   WHERE deleted IS FALSE or deleted = FALSE;
 148 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT, no_thesaurus BOOL ) RETURNS TEXT AS $func$
 149 DECLARE
 150     acsaf           authority.control_set_authority_field%ROWTYPE;
 151     tag_used        TEXT;
 152     sf              TEXT;
 153     thes_code       TEXT;
 154     cset            INT;
 155     heading_text    TEXT;
 156     tmp_text        TEXT;
 157 BEGIN
 158     thes_code := vandelay.marc21_extract_fixed_field(marcxml,'Subj');
 159     IF thes_code IS NULL THEN
 160         thes_code := '|';
 161     END IF;
 162
 163     SELECT control_set INTO cset FROM authority.thesaurus WHERE code = thes_code;
 164     IF NOT FOUND THEN
 165         cset = 1;
 166     END IF;
 167
 168     heading_text := '';
 169     FOR acsaf IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset AND main_entry IS NULL LOOP
 170         tag_used := acsaf.tag;
 171         FOR sf IN SELECT * FROM regexp_split_to_table(acsaf.sf_list,'') LOOP
 172             tmp_text := oils_xpath_string('//*[@tag="'||tag_used||'"]/*[@code="'||sf||'"]', marcxml);
 173             IF tmp_text IS NOT NULL AND tmp_text <> '' THEN
 174                 heading_text := heading_text || E'\u2021' || sf || ' ' || tmp_text;
 175             END IF;
 176         END LOOP;
 177         EXIT WHEN heading_text <> '';
 178     END LOOP;
 179
 180     IF thes_code = 'z' THEN
 181         thes_code := oils_xpath_string('//*[@tag="040"]/*[@code="f"][1]', marcxml);
 182     END IF;
 183
 184     IF heading_text <> '' THEN
 185         IF no_thesaurus IS TRUE THEN
 186             heading_text := tag_used || ' ' || public.naco_normalize(heading_text);
 187         ELSE
 188             heading_text := tag_used || '_' || thes_code || ' ' || public.naco_normalize(heading_text);
 189         END IF;
 190     ELSE
 191         heading_text := 'NOHEADING_' || thes_code || ' ' || MD5(marcxml);
 192     END IF;
 193
 194     RETURN heading_text;
 195 END;
 196 $func$ LANGUAGE PLPGSQL IMMUTABLE;
 197
 198 CREATE OR REPLACE FUNCTION authority.simple_normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
 199     SELECT authority.normalize_heading($1, TRUE);
 200 $func$ LANGUAGE SQL IMMUTABLE;
 201
 202 CREATE OR REPLACE FUNCTION authority.normalize_heading( marcxml TEXT ) RETURNS TEXT AS $func$
 203     SELECT authority.normalize_heading($1, FALSE);
 204 $func$ LANGUAGE SQL IMMUTABLE;
 205
 206 COMMENT ON FUNCTION authority.normalize_heading( TEXT ) IS $$
 207 Extract the authority heading, thesaurus, and NACO-normalized values
 208 from an authority record. The primary purpose is to build a unique
 209 index to defend against duplicated authority records from the same
 210 thesaurus.
 211 $$;
 212
 213 -- Adding indexes using oils_xpath_string() for the main entry tags described in
 214 -- authority.control_set_authority_field would speed this up, if we ever want to use it, though
 215 -- the existing index on authority.normalize_heading() helps already with a record in hand
 216 CREATE OR REPLACE VIEW authority.tracing_links AS
 217     SELECT  main.record AS record,
 218             main.id AS main_id,
 219             main.tag AS main_tag,
 220             oils_xpath_string('//*[@tag="'||main.tag||'"]/*[local-name()="subfield"]', are.marc) AS main_value,
 221             substr(link.value,1,1) AS relationship,
 222             substr(link.value,2,1) AS use_restriction,
 223             substr(link.value,3,1) AS deprecation,
 224             substr(link.value,4,1) AS display_restriction,
 225             link.id AS link_id,
 226             link.tag AS link_tag,
 227             oils_xpath_string('//*[@tag="'||link.tag||'"]/*[local-name()="subfield"]', are.marc) AS link_value,
 228             authority.normalize_heading(are.marc) AS normalized_main_value
 229       FROM  authority.full_rec main
 230             JOIN authority.record_entry are ON (main.record = are.id)
 231             JOIN authority.control_set_authority_field main_entry
 232                 ON (main_entry.tag = main.tag
 233                     AND main_entry.main_entry IS NULL
 234                     AND main.subfield = 'a' )
 235             JOIN authority.control_set_authority_field sub_entry
 236                 ON (main_entry.id = sub_entry.main_entry)
 237             JOIN authority.full_rec link
 238                 ON (link.record = main.record
 239                     AND link.tag = sub_entry.tag
 240                     AND link.subfield = 'w' );
 241
 242 -- Function to generate an ephemeral overlay template from an authority record
 243 CREATE OR REPLACE FUNCTION authority.generate_overlay_template (source_xml TEXT) RETURNS TEXT AS $f$
 244 DECLARE
 245     cset                INT;
 246     main_entry          authority.control_set_authority_field%ROWTYPE;
 247     bib_field           authority.control_set_bib_field%ROWTYPE;
 248     auth_id             INT DEFAULT oils_xpath_string('//*[@tag="901"]/*[local-name()="subfield" and @code="c"]', source_xml)::INT;
 249     replace_data        XML[] DEFAULT '{}'::XML[];
 250     replace_rules       TEXT[] DEFAULT '{}'::TEXT[];
 251     auth_field          XML[];
 252 BEGIN
 253     IF auth_id IS NULL THEN
 254         RETURN NULL;
 255     END IF;
 256
 257     -- Default to the LoC controll set
 258     SELECT control_set INTO cset FROM authority.record_entry WHERE id = auth_id;
 259
 260     -- if none, make a best guess
 261     IF cset IS NULL THEN
 262         SELECT  control_set INTO cset
 263           FROM  authority.control_set_authority_field
 264           WHERE tag IN (
 265                     SELECT  UNNEST(XPATH('//*[starts-with(@tag,"1")]/@tag',marc::XML)::TEXT[])
 266                       FROM  authority.record_entry
 267                       WHERE id = auth_id
 268                 )
 269           LIMIT 1;
 270     END IF;
 271
 272     -- if STILL none, no-op change
 273     IF cset IS NULL THEN
 274         RETURN XMLELEMENT(
 275             name record,
 276             XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
 277             XMLELEMENT( name leader, '00881nam a2200193   4500'),
 278             XMLELEMENT(
 279                 name datafield,
 280                 XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
 281                 XMLELEMENT(
 282                     name subfield,
 283                     XMLATTRIBUTES('d' AS code),
 284                     '901c'
 285                 )
 286             )
 287         )::TEXT;
 288     END IF;
 289
 290     FOR main_entry IN SELECT * FROM authority.control_set_authority_field WHERE control_set = cset LOOP
 291         auth_field := XPATH('//*[@tag="'||main_entry.tag||'"][1]',source_xml::XML);
 292         IF ARRAY_LENGTH(auth_field,1) > 0 THEN
 293             FOR bib_field IN SELECT * FROM authority.control_set_bib_field WHERE authority_field = main_entry.id LOOP
 294                 replace_data := replace_data || XMLELEMENT( name datafield, XMLATTRIBUTES(bib_field.tag AS tag), XPATH('//*[local-name()="subfield"]',auth_field[1])::XML[]);
 295                 replace_rules := replace_rules || ( bib_field.tag || main_entry.sf_list || E'[0~\\)' || auth_id || '$]' );
 296             END LOOP;
 297             EXIT;
 298         END IF;
 299     END LOOP;
 300
 301     RETURN XMLELEMENT(
 302         name record,
 303         XMLATTRIBUTES('http://www.loc.gov/MARC21/slim' AS xmlns),
 304         XMLELEMENT( name leader, '00881nam a2200193   4500'),
 305         replace_data,
 306         XMLELEMENT(
 307             name datafield,
 308             XMLATTRIBUTES( '905' AS tag, ' ' AS ind1, ' ' AS ind2),
 309             XMLELEMENT(
 310                 name subfield,
 311                 XMLATTRIBUTES('r' AS code),
 312                 ARRAY_TO_STRING(replace_rules,',')
 313             )
 314         )
 315     )::TEXT;
 316 END;
 317 $f$ STABLE LANGUAGE PLPGSQL;
 318
 319 CREATE OR REPLACE FUNCTION authority.generate_overlay_template ( BIGINT ) RETURNS TEXT AS $func$
 320     SELECT authority.generate_overlay_template( marc ) FROM authority.record_entry WHERE id = $1;
 321 $func$ LANGUAGE SQL;
 322
 323 CREATE OR REPLACE FUNCTION authority.merge_records ( target_record BIGINT, source_record BIGINT ) RETURNS INT AS $func$
 324 DECLARE
 325     moved_objects INT := 0;
 326     bib_id        INT := 0;
 327     bib_rec       biblio.record_entry%ROWTYPE;
 328     auth_link     authority.bib_linking%ROWTYPE;
 329     ingest_same   boolean;
 330 BEGIN
 331
 332     -- Defining our terms:
 333     -- "target record" = the record that will survive the merge
 334     -- "source record" = the record that is sacrifing its existence and being
 335     --   replaced by the target record
 336
 337     -- 1. Update all bib records with the ID from target_record in their $0
 338     FOR bib_rec IN
 339             SELECT  bre.*
 340               FROM  biblio.record_entry bre
 341                     JOIN authority.bib_linking abl ON abl.bib = bre.id
 342               WHERE abl.authority = source_record
 343         LOOP
 344
 345         UPDATE  biblio.record_entry
 346           SET   marc = REGEXP_REPLACE(
 347                     marc,
 348                     E'(<subfield\\s+code="0"\\s*>[^<]*?\\))' || source_record || '<',
 349                     E'\\1' || target_record || '<',
 350                     'g'
 351                 )
 352           WHERE id = bib_rec.id;
 353
 354           moved_objects := moved_objects + 1;
 355     END LOOP;
 356
 357     -- 2. Grab the current value of reingest on same MARC flag
 358     SELECT  enabled INTO ingest_same
 359       FROM  config.internal_flag
 360       WHERE name = 'ingest.reingest.force_on_same_marc'
 361     ;
 362
 363     -- 3. Temporarily set reingest on same to TRUE
 364     UPDATE  config.internal_flag
 365       SET   enabled = TRUE
 366       WHERE name = 'ingest.reingest.force_on_same_marc'
 367     ;
 368
 369     -- 4. Make a harmless update to target_record to trigger auto-update
 370     --    in linked bibliographic records
 371     UPDATE  authority.record_entry
 372       SET   deleted = FALSE
 373       WHERE id = target_record;
 374
 375     -- 5. "Delete" source_record
 376     DELETE FROM authority.record_entry WHERE id = source_record;
 377
 378     -- 6. Set "reingest on same MARC" flag back to initial value
 379     UPDATE  config.internal_flag
 380       SET   enabled = ingest_same
 381       WHERE name = 'ingest.reingest.force_on_same_marc'
 382     ;
 383
 384     RETURN moved_objects;
 385 END;
 386 $func$ LANGUAGE plpgsql;
 387
 388 COMMIT;