Open-ILS/src/sql/Pg/upgrade/0136.schema.in-db-ingest.sql

   1 BEGIN;
   2
   3 INSERT INTO config.upgrade_log (version) VALUES ('0136'); -- miker
   4
   5 CREATE TABLE config.biblio_fingerprint (
   6         id                      SERIAL  PRIMARY KEY,
   7         name            TEXT    NOT NULL,
   8         xpath           TEXT    NOT NULL,
   9     first_word  BOOL    NOT NULL DEFAULT FALSE,
  10         format          TEXT    NOT NULL DEFAULT 'marcxml'
  11 );
  12
  13 INSERT INTO config.biblio_fingerprint (name, xpath, format)
  14     VALUES (
  15         'Title',
  16         '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
  17             '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
  18             '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
  19             '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
  20             '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
  21         'marcxml'
  22     );
  23
  24 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
  25     VALUES (
  26         'Author',
  27         '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
  28             '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
  29             '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
  30             '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
  31             '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
  32         'marcxml',
  33         TRUE
  34     );
  35
  36 CREATE OR REPLACE FUNCTION biblio.extract_quality ( marc TEXT, best_lang TEXT, best_type TEXT ) RETURNS INT AS $func$
  37 DECLARE
  38     qual        INT;
  39     ldr         TEXT;
  40     tval        TEXT;
  41     tval_rec    RECORD;
  42     bval        TEXT;
  43     bval_rec    RECORD;
  44     type_map    RECORD;
  45     ff_pos      RECORD;
  46     ff_tag_data TEXT;
  47 BEGIN
  48
  49     IF marc IS NULL OR marc = '' THEN
  50         RETURN NULL;
  51     END IF;
  52
  53     -- First, the count of tags
  54     qual := ARRAY_UPPER(oils_xpath('*[local-name()="datafield"]', marc), 1);
  55
  56     -- now go through a bunch of pain to get the record type
  57     IF best_type IS NOT NULL THEN
  58         ldr := (oils_xpath('//*[local-name()="leader"]/text()', marc))[1];
  59
  60         IF ldr IS NOT NULL THEN
  61             SELECT * INTO tval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'Type' LIMIT 1; -- They're all the same
  62             SELECT * INTO bval_rec FROM config.marc21_ff_pos_map WHERE fixed_field = 'BLvl' LIMIT 1; -- They're all the same
  63
  64
  65             tval := SUBSTRING( ldr, tval_rec.start_pos + 1, tval_rec.length );
  66             bval := SUBSTRING( ldr, bval_rec.start_pos + 1, bval_rec.length );
  67
  68             -- RAISE NOTICE 'type %, blvl %, ldr %', tval, bval, ldr;
  69
  70             SELECT * INTO type_map FROM config.marc21_rec_type_map WHERE type_val LIKE '%' || tval || '%' AND blvl_val LIKE '%' || bval || '%';
  71
  72             IF type_map.code IS NOT NULL THEN
  73                 IF best_type = type_map.code THEN
  74                     qual := qual + qual / 2;
  75                 END IF;
  76
  77                 FOR ff_pos IN SELECT * FROM config.marc21_ff_pos_map WHERE fixed_field = 'Lang' AND rec_type = type_map.code ORDER BY tag DESC LOOP
  78                     ff_tag_data := SUBSTRING((oils_xpath('//*[@tag="' || ff_pos.tag || '"]/text()',marc))[1], ff_pos.start_pos + 1, ff_pos.length);
  79                     IF ff_tag_data = best_lang THEN
  80                             qual := qual + 100;
  81                     END IF;
  82                 END LOOP;
  83             END IF;
  84         END IF;
  85     END IF;
  86
  87     -- Now look for some quality metrics
  88     -- DCL record?
  89     IF ARRAY_UPPER(oils_xpath('//*[@tag="040"]/*[@code="a" and contains(.,"DLC")]', marc), 1) = 1 THEN
  90         qual := qual + 10;
  91     END IF;
  92
  93     -- From OCLC?
  94     IF (oils_xpath('//*[@tag="003"]/text()', marc))[1] ~* E'oclo?c' THEN
  95         qual := qual + 10;
  96     END IF;
  97
  98     RETURN qual;
  99
 100 END;
 101 $func$ LANGUAGE PLPGSQL;
 102
 103 CREATE OR REPLACE FUNCTION biblio.extract_fingerprint ( marc text ) RETURNS TEXT AS $func$
 104 DECLARE
 105         idx             config.biblio_fingerprint%ROWTYPE;
 106         xfrm            config.xml_transform%ROWTYPE;
 107         prev_xfrm       TEXT;
 108         transformed_xml TEXT;
 109         xml_node        TEXT;
 110         xml_node_list   TEXT[];
 111         raw_text        TEXT;
 112     output_text TEXT := '';
 113 BEGIN
 114
 115     IF marc IS NULL OR marc = '' THEN
 116         RETURN NULL;
 117     END IF;
 118
 119         -- Loop over the indexing entries
 120         FOR idx IN SELECT * FROM config.biblio_fingerprint ORDER BY format, id LOOP
 121
 122                 SELECT INTO xfrm * from config.xml_transform WHERE name = idx.format;
 123
 124                 -- See if we can skip the XSLT ... it's expensive
 125                 IF prev_xfrm IS NULL OR prev_xfrm <> xfrm.name THEN
 126                         -- Can't skip the transform
 127                         IF xfrm.xslt <> '---' THEN
 128                                 transformed_xml := xslt_process(marc,xfrm.xslt);
 129                         ELSE
 130                                 transformed_xml := marc;
 131                         END IF;
 132
 133                         prev_xfrm := xfrm.name;
 134                 END IF;
 135
 136                 raw_text := COALESCE(
 137             naco_normalize(
 138                 ARRAY_TO_STRING(
 139                     oils_xpath(
 140                         '//text()',
 141                         (oils_xpath(
 142                             idx.xpath,
 143                             transformed_xml,
 144                             ARRAY[ARRAY[xfrm.prefix, xfrm.namespace_uri]]
 145                         ))[1]
 146                     ),
 147                     ''
 148                 )
 149             ),
 150             ''
 151         );
 152
 153         raw_text := REGEXP_REPLACE(raw_text, E'\\[.+?\\]', E'');
 154         raw_text := REGEXP_REPLACE(raw_text, E'\\mthe\\M|\\man?d?d\\M', E'', 'g'); -- arg! the pain!
 155
 156         IF idx.first_word IS TRUE THEN
 157             raw_text := REGEXP_REPLACE(raw_text, E'^(\\w+).*?$', E'\\1');
 158         END IF;
 159
 160                 output_text := output_text || REGEXP_REPLACE(raw_text, E'\\s+', '', 'g');
 161
 162         END LOOP;
 163
 164     RETURN output_text;
 165
 166 END;
 167 $func$ LANGUAGE PLPGSQL;
 168
 169 -- BEFORE UPDATE OR INSERT trigger for biblio.record_entry
 170 CREATE OR REPLACE FUNCTION biblio.fingerprint_trigger () RETURNS TRIGGER AS $func$
 171 BEGIN
 172
 173     -- For TG_ARGV, first param is language (like 'eng'), second is record type (like 'BKS')
 174
 175     IF NEW.deleted IS TRUE THEN -- we don't much care, then, do we?
 176         RETURN NEW;
 177     END IF;
 178
 179     NEW.fingerprint := biblio.extract_fingerprint(NEW.marc);
 180     NEW.quality := biblio.extract_quality(NEW.marc, TG_ARGV[0], TG_ARGV[1]);
 181
 182     RETURN NEW;
 183
 184 END;
 185 $func$ LANGUAGE PLPGSQL;
 186
 187 -- AFTER UPDATE OR INSERT trigger for biblio.record_entry
 188 CREATE OR REPLACE FUNCTION biblio.indexing_ingest_or_delete () RETURNS TRIGGER AS $func$
 189 DECLARE
 190     ind_data        metabib.field_entry_template%ROWTYPE;
 191     old_mr          INT;
 192     tmp_mr          metabib.metarecord%ROWTYPE;
 193     source_count    INT;
 194     deleted_mrs     INT[];
 195     uris            TEXT[];
 196     uri_xml         TEXT;
 197     uri_label       TEXT;
 198     uri_href        TEXT;
 199     uri_use         TEXT;
 200     uri_owner       TEXT;
 201     uri_owner_id    INT;
 202     uri_id          INT;
 203     uri_cn_id       INT;
 204     uri_map_id      INT;
 205 BEGIN
 206
 207     DELETE FROM metabib.metarecord_source_map WHERE source = NEW.id; -- Rid ourselves of the search-estimate-killing linkage
 208
 209     IF NEW.deleted IS TRUE THEN
 210         RETURN NEW; -- and we're done
 211     END IF;
 212
 213     IF TG_OP = 'UPDATE' THEN -- Clean out the cruft
 214         DELETE FROM metabib.title_field_entry WHERE source = NEW.id;
 215         DELETE FROM metabib.author_field_entry WHERE source = NEW.id;
 216         DELETE FROM metabib.subject_field_entry WHERE source = NEW.id;
 217         DELETE FROM metabib.keyword_field_entry WHERE source = NEW.id;
 218         DELETE FROM metabib.series_field_entry WHERE source = NEW.id;
 219         DELETE FROM metabib.full_rec WHERE record = NEW.id;
 220         DELETE FROM metabib.rec_descriptor WHERE record = NEW.id;
 221
 222     END IF;
 223
 224     -- Shove the flattened MARC in
 225     INSERT INTO metabib.full_rec (record, tag, ind1, ind2, subfield, value)
 226         SELECT record, tag, ind1, ind2, subfield, value FROM biblio.flatten_marc( NEW.id );
 227
 228     -- And now the indexing data
 229     FOR ind_data IN SELECT * FROM biblio.extract_metabib_field_entry( NEW.id ) LOOP
 230         IF ind_data.field_class = 'title' THEN
 231             INSERT INTO metabib.title_field_entry (field, source, value)
 232                 VALUES (ind_data.field, ind_data.source, ind_data.value);
 233         ELSIF ind_data.field_class = 'author' THEN
 234             INSERT INTO metabib.author_field_entry (field, source, value)
 235                 VALUES (ind_data.field, ind_data.source, ind_data.value);
 236         ELSIF ind_data.field_class = 'subject' THEN
 237             INSERT INTO metabib.subject_field_entry (field, source, value)
 238                 VALUES (ind_data.field, ind_data.source, ind_data.value);
 239         ELSIF ind_data.field_class = 'keyword' THEN
 240             INSERT INTO metabib.keyword_field_entry (field, source, value)
 241                 VALUES (ind_data.field, ind_data.source, ind_data.value);
 242         ELSIF ind_data.field_class = 'series' THEN
 243             INSERT INTO metabib.series_field_entry (field, source, value)
 244                 VALUES (ind_data.field, ind_data.source, ind_data.value);
 245         END IF;
 246     END LOOP;
 247
 248     -- Then, the rec_descriptor
 249     INSERT INTO metabib.rec_descriptor (record, item_type, item_form, bib_level, control_type, enc_level, audience, lit_form, type_mat, cat_form, pub_status, item_lang, vr_format, date1, date2)
 250         SELECT  NEW.id,
 251                 biblio.marc21_extract_fixed_field( NEW.id, 'Type' ),
 252                 biblio.marc21_extract_fixed_field( NEW.id, 'Form' ),
 253                 biblio.marc21_extract_fixed_field( NEW.id, 'BLvl' ),
 254                 biblio.marc21_extract_fixed_field( NEW.id, 'Ctrl' ),
 255                 biblio.marc21_extract_fixed_field( NEW.id, 'ELvl' ),
 256                 biblio.marc21_extract_fixed_field( NEW.id, 'Audn' ),
 257                 biblio.marc21_extract_fixed_field( NEW.id, 'LitF' ),
 258                 biblio.marc21_extract_fixed_field( NEW.id, 'TMat' ),
 259                 biblio.marc21_extract_fixed_field( NEW.id, 'Desc' ),
 260                 biblio.marc21_extract_fixed_field( NEW.id, 'DtSt' ),
 261                 biblio.marc21_extract_fixed_field( NEW.id, 'Lang' ),
 262                 (   SELECT  v.value
 263                       FROM  biblio.marc21_physical_characteristics( NEW.id) p
 264                             JOIN config.marc21_physical_characteristic_subfield_map s ON (s.id = p.subfield)
 265                             JOIN config.marc21_physical_characteristic_value_map v ON (v.id = p.value)
 266                       WHERE p.ptype = 'v' AND s.subfield = 'e'    ),
 267                 biblio.marc21_extract_fixed_field( NEW.id, 'Date1'),
 268                 biblio.marc21_extract_fixed_field( NEW.id, 'Date2');
 269
 270     -- On to URIs ...
 271     uris := oils_xpath('//*[@tag="856" and (@ind1="4" or @ind1="1") and (@ind2="0" or @ind2="1")]',NEW.marc);
 272     IF ARRAY_UPPER(uris,1) > 0 THEN
 273         FOR i IN 1 .. ARRAY_UPPER( uris ) LOOP
 274             -- First we pull infot out of the 856
 275             uri_xml     := uris[i];
 276
 277             uri_href    := (oils_xpath('//*[@code="u"]/text()',uri_xml))[1];
 278             CONTINUE WHEN uri_href IS NULL;
 279
 280             uri_label   := (oils_xpath('//*[@code="y"]/text()|//*[@code="3"]/text()|//*[@code="u"]/text()',uri_xml))[1];
 281             CONTINUE WHEN uri_label IS NULL;
 282
 283             uri_owner   := (oils_xpath('//*[@code="9"]/text()|//*[@code="w"]/text()|//*[@code="n"]/text()',uri_xml))[1];
 284             CONTINUE WHEN uri_owner IS NULL;
 285
 286             uri_use     := (oils_xpath('//*[@code="z"]/text()|//*[@code="2"]/text()|//*[@code="n"]/text()',uri_xml))[1];
 287
 288             uri_owner := REGEXP_REPLACE(uri_owner, $re$^.*?\((\w+)\).*$$re$, E'\\1');
 289
 290             SELECT id INTO uri_owner_id FROM actor.org_unit WHERE shortname = uri_owner;
 291             CONTINUE WHEN NOT FOUND;
 292
 293             -- now we look for a matching uri
 294             SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
 295             IF NOT FOUND THEN -- create one
 296                 INSERT INTO asset.uri (label, href, use_restriction) VALUES (uri_label, uri_href, uri_use);
 297                 SELECT id INTO uri_id FROM asset.uri WHERE label = uri_label AND href = uri_href AND use_restriction = uri_use AND active;
 298             END IF;
 299
 300             -- we need a call number to link through
 301             SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted;
 302             IF NOT FOUND THEN
 303                 INSERT INTO asset.call_number (owning_lib, record, create_date, edit_date, creator, editor, label)
 304                     VALUES (uri_owner_id, NEW.id, 'now', 'now', NEW.editor, NEW.editor, '##URI##');
 305                 SELECT id INTO uri_cn_id FROM asset.call_number WHERE owning_lib = uri_owner_id AND record = NEW.id AND label = '##URI##' AND NOT deleted;
 306             END IF;
 307
 308             -- now, link them if they're not already
 309             SELECT id INTO uri_map_id FROM asset.uri_call_number_map WHERE call_number = uri_cn_id AND uri = uri_id;
 310             IF NOT FOUND THEN
 311                 INSERT INTO asset.uri_call_number_map (call_number, uri) VALUES (uri_cn_id, uri_id);
 312             END IF;
 313
 314         END LOOP;
 315     END IF;
 316
 317     -- And, finally, metarecord mapping!
 318
 319     FOR tmp_mr IN SELECT  m.* FROM  metabib.metarecord m JOIN metabib.metarecord_source_map s ON (s.metarecord = m.id) WHERE s.source = NEW.id LOOP
 320
 321         IF old_mr IS NULL AND NEW.fingerprint = tmp_mr.fingerprint THEN -- Find the first fingerprint-matching
 322             old_mr := tmp_mr.id;
 323         ELSE
 324             SELECT COUNT(*) INTO source_count FROM metabib.metarecord_source_map WHERE metarecord = tmp_mr.id;
 325             IF source_count = 0 THEN -- No other records
 326                 deleted_mrs := ARRAY_APPEND(deleted_mrs, tmp_mr.id);
 327                 DELETE FROM metabib.metarecord WHERE id = tmp_mr.id;
 328             END IF;
 329         END IF;
 330
 331     END LOOP;
 332
 333     IF old_mr IS NULL THEN -- we found no suitable, preexisting MR based on old source maps
 334         SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint; -- is there one for our current fingerprint?
 335         IF old_mr IS NULL THEN -- nope, create one and grab its id
 336             INSERT INTO metabib.metarecord ( fingerprint, master_record ) VALUES ( NEW.fingerprint, NEW.id );
 337             SELECT id INTO old_mr FROM metabib.metarecord WHERE fingerprint = NEW.fingerprint;
 338         ELSE -- indeed there is. update it with a null cache and recalcualated master record
 339             UPDATE  metabib.metarecord
 340               SET   mods = NULL,
 341                     master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
 342               WHERE id = old_mr;
 343         END IF;
 344     ELSE -- there was one we already attached to, update its mods cache and master_record
 345         UPDATE  metabib.metarecord
 346           SET   mods = NULL,
 347                 master_record = ( SELECT id FROM biblio.record_entry WHERE fingerprint = NEW.fingerprint ORDER BY quality DESC LIMIT 1)
 348           WHERE id = old_mr;
 349     END IF;
 350
 351     INSERT INTO metabib.metarecord_source_map (metarecord, source) VALUES (old_mr, NEW.id); -- new source mapping
 352
 353     UPDATE action.hold_request SET target = old_mr WHERE target IN ( SELECT explode_array(deleted_mrs) ) AND hold_type = 'M'; -- if we had to delete any MRs above, make sure their holds are moved
 354
 355     RETURN NEW;
 356
 357 END;
 358 $func$ LANGUAGE PLPGSQL;
 359
 360 CREATE OR REPLACE FUNCTION reporter.simple_rec_trigger () RETURNS TRIGGER AS $func$
 361 BEGIN
 362     IF TG_OP = 'DELETE' THEN
 363         PERFORM reporter.simple_rec_delete(NEW.id);
 364     ELSE
 365         PERFORM reporter.simple_rec_update(NEW.id);
 366     END IF;
 367
 368     RETURN NEW;
 369 END;
 370 $func$ LANGUAGE PLPGSQL;
 371
 372 CREATE TRIGGER fingerprint_tgr BEFORE INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.fingerprint_trigger ('eng','BKS');
 373 CREATE TRIGGER aaa_indexing_ingest_or_delete AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE biblio.indexing_ingest_or_delete ();
 374 CREATE TRIGGER bbb_simple_rec_trigger AFTER INSERT OR UPDATE ON biblio.record_entry FOR EACH ROW EXECUTE PROCEDURE reporter.simple_rec_trigger ();
 375
 376 COMMIT;
 377
 378 -- Ingest triggers.  Not installed on all systems.  Delete outside the transaction
 379 DROP TRIGGER zzz_update_materialized_simple_rec_delete_tgr ON biblio.record_entry;
 380
 381