Open-ILS/src/sql/Pg/upgrade/0909.schema.authority_match_sets.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0909', :eg_version);
   4
   5 ALTER TABLE vandelay.authority_match
   6     ADD COLUMN match_score INT NOT NULL DEFAULT 0;
   7
   8 -- support heading=TRUE match set points
   9 ALTER TABLE vandelay.match_set_point
  10     ADD COLUMN heading BOOLEAN NOT NULL DEFAULT FALSE,
  11     DROP CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_bo,
  12     ADD CONSTRAINT vmsp_need_a_tag_or_a_ff_or_a_heading_or_a_bo
  13     CHECK (
  14         (tag IS NOT NULL AND svf IS NULL AND heading IS FALSE AND bool_op IS NULL) OR
  15         (tag IS NULL AND svf IS NOT NULL AND heading IS FALSE AND bool_op IS NULL) OR
  16         (tag IS NULL AND svf IS NULL AND heading IS TRUE AND bool_op IS NULL) OR
  17         (tag IS NULL AND svf IS NULL AND heading IS FALSE AND bool_op IS NOT NULL)
  18     );
  19
  20 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
  21     match_set_id INTEGER,
  22     tags_rstore HSTORE,
  23     auth_heading TEXT
  24 ) RETURNS TEXT AS $$
  25 DECLARE
  26     root vandelay.match_set_point;
  27 BEGIN
  28     SELECT * INTO root FROM vandelay.match_set_point
  29         WHERE parent IS NULL AND match_set = match_set_id;
  30
  31     RETURN vandelay.get_expr_from_match_set_point(
  32         root, tags_rstore, auth_heading);
  33 END;
  34 $$  LANGUAGE PLPGSQL;
  35
  36 -- backwards compat version so we don't have
  37 -- to modify vandelay.match_set_test_marcxml()
  38 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
  39     match_set_id INTEGER,
  40     tags_rstore HSTORE
  41 ) RETURNS TEXT AS $$
  42 BEGIN
  43     RETURN vandelay.get_expr_from_match_set(
  44         match_set_id, tags_rstore, NULL);
  45 END;
  46 $$  LANGUAGE PLPGSQL;
  47
  48
  49 DROP FUNCTION IF EXISTS
  50     vandelay.get_expr_from_match_set_point(vandelay.match_set_point, HSTORE);
  51
  52 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
  53     node vandelay.match_set_point,
  54     tags_rstore HSTORE,
  55     auth_heading TEXT
  56 ) RETURNS TEXT AS $$
  57 DECLARE
  58     q           TEXT;
  59     i           INTEGER;
  60     this_op     TEXT;
  61     children    INTEGER[];
  62     child       vandelay.match_set_point;
  63 BEGIN
  64     SELECT ARRAY_AGG(id) INTO children FROM vandelay.match_set_point
  65         WHERE parent = node.id;
  66
  67     IF ARRAY_LENGTH(children, 1) > 0 THEN
  68         this_op := vandelay._get_expr_render_one(node);
  69         q := '(';
  70         i := 1;
  71         WHILE children[i] IS NOT NULL LOOP
  72             SELECT * INTO child FROM vandelay.match_set_point
  73                 WHERE id = children[i];
  74             IF i > 1 THEN
  75                 q := q || ' ' || this_op || ' ';
  76             END IF;
  77             i := i + 1;
  78             q := q || vandelay.get_expr_from_match_set_point(
  79                 child, tags_rstore, auth_heading);
  80         END LOOP;
  81         q := q || ')';
  82         RETURN q;
  83     ELSIF node.bool_op IS NULL THEN
  84         PERFORM vandelay._get_expr_push_qrow(node);
  85         PERFORM vandelay._get_expr_push_jrow(node, tags_rstore, auth_heading);
  86         RETURN vandelay._get_expr_render_one(node);
  87     ELSE
  88         RETURN '';
  89     END IF;
  90 END;
  91 $$  LANGUAGE PLPGSQL;
  92
  93
  94 DROP FUNCTION IF EXISTS
  95     vandelay._get_expr_push_jrow(vandelay.match_set_point, HSTORE);
  96
  97 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
  98     node vandelay.match_set_point,
  99     tags_rstore HSTORE,
 100     auth_heading TEXT
 101 ) RETURNS VOID AS $$
 102 DECLARE
 103     jrow        TEXT;
 104     my_alias    TEXT;
 105     op          TEXT;
 106     tagkey      TEXT;
 107     caseless    BOOL;
 108     jrow_count  INT;
 109     my_using    TEXT;
 110     my_join     TEXT;
 111     rec_table   TEXT;
 112 BEGIN
 113     -- remember $1 is tags_rstore, and $2 is svf_rstore
 114     -- a non-NULL auth_heading means we're matching authority records
 115
 116     IF auth_heading IS NOT NULL THEN
 117         rec_table := 'authority.full_rec';
 118     ELSE
 119         rec_table := 'metabib.full_rec';
 120     END IF;
 121
 122     caseless := FALSE;
 123     SELECT COUNT(*) INTO jrow_count FROM _vandelay_tmp_jrows;
 124     IF jrow_count > 0 THEN
 125         my_using := ' USING (record)';
 126         my_join := 'FULL OUTER JOIN';
 127     ELSE
 128         my_using := '';
 129         my_join := 'FROM';
 130     END IF;
 131
 132     IF node.tag IS NOT NULL THEN
 133         caseless := (node.tag IN ('020', '022', '024'));
 134         tagkey := node.tag;
 135         IF node.subfield IS NOT NULL THEN
 136             tagkey := tagkey || node.subfield;
 137         END IF;
 138     END IF;
 139
 140     IF node.negate THEN
 141         IF caseless THEN
 142             op := 'NOT LIKE';
 143         ELSE
 144             op := '<>';
 145         END IF;
 146     ELSE
 147         IF caseless THEN
 148             op := 'LIKE';
 149         ELSE
 150             op := '=';
 151         END IF;
 152     END IF;
 153
 154     my_alias := 'n' || node.id::TEXT;
 155
 156     jrow := my_join || ' (SELECT *, ';
 157     IF node.tag IS NOT NULL THEN
 158         jrow := jrow  || node.quality ||
 159             ' AS quality FROM ' || rec_table || ' mfr WHERE mfr.tag = ''' ||
 160             node.tag || '''';
 161         IF node.subfield IS NOT NULL THEN
 162             jrow := jrow || ' AND mfr.subfield = ''' ||
 163                 node.subfield || '''';
 164         END IF;
 165         jrow := jrow || ' AND (';
 166         jrow := jrow || vandelay._node_tag_comparisons(caseless, op, tags_rstore, tagkey);
 167         jrow := jrow || ')) ' || my_alias || my_using || E'\n';
 168     ELSE    -- svf
 169         IF auth_heading IS NOT NULL THEN -- authority record
 170             IF node.heading AND auth_heading <> '' THEN
 171                 jrow := jrow || 'id AS record, ' || node.quality ||
 172                 ' AS quality FROM authority.record_entry are ' ||
 173                 ' WHERE are.heading = ''' || auth_heading || '''';
 174                 jrow := jrow || ') ' || my_alias || my_using || E'\n';
 175             END IF;
 176         ELSE -- bib record
 177             jrow := jrow || 'id AS record, ' || node.quality ||
 178                 ' AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = ''' ||
 179                 node.svf || ''' AND mraf.value ' || op || ' $2->''' || node.svf || ''') ' ||
 180                 my_alias || my_using || E'\n';
 181         END IF;
 182     END IF;
 183     INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
 184 END;
 185 $$ LANGUAGE PLPGSQL;
 186
 187
 188 CREATE OR REPLACE FUNCTION vandelay.match_set_test_authxml(
 189     match_set_id INTEGER, record_xml TEXT
 190 ) RETURNS SETOF vandelay.match_set_test_result AS $$
 191 DECLARE
 192     tags_rstore HSTORE;
 193     heading     TEXT;
 194     coal        TEXT;
 195     joins       TEXT;
 196     query_      TEXT;
 197     wq          TEXT;
 198     qvalue      INTEGER;
 199     rec         RECORD;
 200 BEGIN
 201     tags_rstore := vandelay.flatten_marc_hstore(record_xml);
 202
 203     SELECT normalize_heading INTO heading
 204         FROM authority.normalize_heading(record_xml);
 205
 206     CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
 207     CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
 208
 209     -- generate the where clause and return that directly (into wq), and as
 210     -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
 211     wq := vandelay.get_expr_from_match_set(
 212         match_set_id, tags_rstore, heading);
 213
 214     query_ := 'SELECT DISTINCT(record), ';
 215
 216     -- qrows table is for the quality bits we add to the SELECT clause
 217     SELECT STRING_AGG(
 218         'COALESCE(n' || q::TEXT || '.quality, 0)', ' + '
 219     ) INTO coal FROM _vandelay_tmp_qrows;
 220
 221     -- our query string so far is the SELECT clause and the inital FROM.
 222     -- no JOINs yet nor the WHERE clause
 223     query_ := query_ || coal || ' AS quality ' || E'\n';
 224
 225     -- jrows table is for the joins we must make (and the real text conditions)
 226     SELECT STRING_AGG(j, E'\n') INTO joins
 227         FROM _vandelay_tmp_jrows;
 228
 229     -- add those joins and the where clause to our query.
 230     query_ := query_ || joins || E'\n';
 231
 232     query_ := query_ || 'JOIN authority.record_entry are ON (are.id = record) '
 233         || 'WHERE ' || wq || ' AND not are.deleted';
 234
 235     -- this will return rows of record,quality
 236     FOR rec IN EXECUTE query_ USING tags_rstore LOOP
 237         RETURN NEXT rec;
 238     END LOOP;
 239
 240     DROP TABLE _vandelay_tmp_qrows;
 241     DROP TABLE _vandelay_tmp_jrows;
 242     RETURN;
 243 END;
 244 $$ LANGUAGE PLPGSQL;
 245
 246 CREATE OR REPLACE FUNCTION vandelay.measure_auth_record_quality
 247     ( xml TEXT, match_set_id INT ) RETURNS INT AS $_$
 248 DECLARE
 249     out_q   INT := 0;
 250     rvalue  TEXT;
 251     test    vandelay.match_set_quality%ROWTYPE;
 252 BEGIN
 253
 254     FOR test IN SELECT * FROM vandelay.match_set_quality
 255             WHERE match_set = match_set_id LOOP
 256         IF test.tag IS NOT NULL THEN
 257             FOR rvalue IN SELECT value FROM vandelay.flatten_marc( xml )
 258                 WHERE tag = test.tag AND subfield = test.subfield LOOP
 259                 IF test.value = rvalue THEN
 260                     out_q := out_q + test.quality;
 261                 END IF;
 262             END LOOP;
 263         END IF;
 264     END LOOP;
 265
 266     RETURN out_q;
 267 END;
 268 $_$ LANGUAGE PLPGSQL;
 269
 270
 271
 272 CREATE OR REPLACE FUNCTION vandelay.match_authority_record() RETURNS TRIGGER AS $func$
 273 DECLARE
 274     incoming_existing_id    TEXT;
 275     test_result             vandelay.match_set_test_result%ROWTYPE;
 276     tmp_rec                 BIGINT;
 277     match_set               INT;
 278 BEGIN
 279     IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
 280         RETURN NEW;
 281     END IF;
 282
 283     DELETE FROM vandelay.authority_match WHERE queued_record = NEW.id;
 284
 285     SELECT q.match_set INTO match_set FROM vandelay.authority_queue q WHERE q.id = NEW.queue;
 286
 287     IF match_set IS NOT NULL THEN
 288         NEW.quality := vandelay.measure_auth_record_quality( NEW.marc, match_set );
 289     END IF;
 290
 291     -- Perfect matches on 901$c exit early with a match with high quality.
 292     incoming_existing_id :=
 293         oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
 294
 295     IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
 296         SELECT id INTO tmp_rec FROM authority.record_entry WHERE id = incoming_existing_id::bigint;
 297         IF tmp_rec IS NOT NULL THEN
 298             INSERT INTO vandelay.authority_match (queued_record, eg_record, match_score, quality)
 299                 SELECT
 300                     NEW.id,
 301                     b.id,
 302                     9999,
 303                     -- note: no match_set means quality==0
 304                     vandelay.measure_auth_record_quality( b.marc, match_set )
 305                 FROM authority.record_entry b
 306                 WHERE id = incoming_existing_id::bigint;
 307         END IF;
 308     END IF;
 309
 310     IF match_set IS NULL THEN
 311         RETURN NEW;
 312     END IF;
 313
 314     FOR test_result IN SELECT * FROM
 315         vandelay.match_set_test_authxml(match_set, NEW.marc) LOOP
 316
 317         INSERT INTO vandelay.authority_match ( queued_record, eg_record, match_score, quality )
 318             SELECT
 319                 NEW.id,
 320                 test_result.record,
 321                 test_result.quality,
 322                 vandelay.measure_auth_record_quality( b.marc, match_set )
 323                 FROM  authority.record_entry b
 324                 WHERE id = test_result.record;
 325
 326     END LOOP;
 327
 328     RETURN NEW;
 329 END;
 330 $func$ LANGUAGE PLPGSQL;
 331
 332 CREATE TRIGGER zz_match_auths_trigger
 333     BEFORE INSERT OR UPDATE ON vandelay.queued_authority_record
 334     FOR EACH ROW EXECUTE PROCEDURE vandelay.match_authority_record();
 335
 336 CREATE OR REPLACE FUNCTION vandelay.auto_overlay_authority_record_with_best ( import_id BIGINT, merge_profile_id INT, lwm_ratio_value_p NUMERIC ) RETURNS BOOL AS $$
 337 DECLARE
 338     eg_id           BIGINT;
 339     lwm_ratio_value NUMERIC;
 340 BEGIN
 341
 342     lwm_ratio_value := COALESCE(lwm_ratio_value_p, 0.0);
 343
 344     PERFORM * FROM vandelay.queued_authority_record WHERE import_time IS NOT NULL AND id = import_id;
 345
 346     IF FOUND THEN
 347         -- RAISE NOTICE 'already imported, cannot auto-overlay'
 348         RETURN FALSE;
 349     END IF;
 350
 351     SELECT  m.eg_record INTO eg_id
 352       FROM  vandelay.authority_match m
 353             JOIN vandelay.queued_authority_record qr ON (m.queued_record = qr.id)
 354             JOIN vandelay.authority_queue q ON (qr.queue = q.id)
 355             JOIN authority.record_entry r ON (r.id = m.eg_record)
 356       WHERE m.queued_record = import_id
 357             AND qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC >= lwm_ratio_value
 358       ORDER BY  m.match_score DESC, -- required match score
 359                 qr.quality::NUMERIC / COALESCE(NULLIF(m.quality,0),1)::NUMERIC DESC, -- quality tie breaker
 360                 m.id -- when in doubt, use the first match
 361       LIMIT 1;
 362
 363     IF eg_id IS NULL THEN
 364         -- RAISE NOTICE 'incoming record is not of high enough quality';
 365         RETURN FALSE;
 366     END IF;
 367
 368     RETURN vandelay.overlay_authority_record( import_id, eg_id, merge_profile_id );
 369 END;
 370 $$ LANGUAGE PLPGSQL;
 371
 372
 373 COMMIT;