3 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0796', :eg_version); -- berick/dbwells
5 ALTER TABLE vandelay.bib_queue ADD COLUMN match_bucket
6 INTEGER REFERENCES container.biblio_record_entry_bucket(id)
7 ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED;
9 CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$
11 incoming_existing_id TEXT;
12 test_result vandelay.match_set_test_result%ROWTYPE;
17 IF TG_OP IN ('INSERT','UPDATE') AND NEW.imported_as IS NOT NULL THEN
21 DELETE FROM vandelay.bib_match WHERE queued_record = NEW.id;
23 SELECT q.match_set INTO match_set FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
25 IF match_set IS NOT NULL THEN
26 NEW.quality := vandelay.measure_record_quality( NEW.marc, match_set );
29 -- Perfect matches on 901$c exit early with a match with high quality.
30 incoming_existing_id :=
31 oils_xpath_string('//*[@tag="901"]/*[@code="c"][1]', NEW.marc);
33 IF incoming_existing_id IS NOT NULL AND incoming_existing_id != '' THEN
34 SELECT id INTO tmp_rec FROM biblio.record_entry WHERE id = incoming_existing_id::bigint;
35 IF tmp_rec IS NOT NULL THEN
36 INSERT INTO vandelay.bib_match (queued_record, eg_record, match_score, quality)
41 -- note: no match_set means quality==0
42 vandelay.measure_record_quality( b.marc, match_set )
43 FROM biblio.record_entry b
44 WHERE id = incoming_existing_id::bigint;
48 IF match_set IS NULL THEN
52 SELECT q.match_bucket INTO match_bucket FROM vandelay.bib_queue q WHERE q.id = NEW.queue;
54 FOR test_result IN SELECT * FROM
55 vandelay.match_set_test_marcxml(match_set, NEW.marc, match_bucket) LOOP
57 INSERT INTO vandelay.bib_match ( queued_record, eg_record, match_score, quality )
62 vandelay.measure_record_quality( b.marc, match_set )
63 FROM biblio.record_entry b
64 WHERE id = test_result.record;
70 $func$ LANGUAGE PLPGSQL;
73 DROP FUNCTION IF EXISTS vandelay.match_set_test_marcxml(INTEGER, TEXT);
75 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
76 match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER
77 ) RETURNS SETOF vandelay.match_set_test_result AS $$
88 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
89 svf_rstore := vandelay.extract_rec_attrs(record_xml);
91 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
92 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
94 -- generate the where clause and return that directly (into wq), and as
95 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
96 wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore);
98 query_ := 'SELECT DISTINCT(record), ';
100 -- qrows table is for the quality bits we add to the SELECT clause
101 SELECT ARRAY_TO_STRING(
102 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
103 ) INTO coal FROM _vandelay_tmp_qrows;
105 -- our query string so far is the SELECT clause and the inital FROM.
106 -- no JOINs yet nor the WHERE clause
107 query_ := query_ || coal || ' AS quality ' || E'\n';
109 -- jrows table is for the joins we must make (and the real text conditions)
110 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
111 FROM _vandelay_tmp_jrows;
113 -- add those joins and the where clause to our query.
114 query_ := query_ || joins || E'\n';
116 -- join the record bucket
117 IF bucket_id IS NOT NULL THEN
118 query_ := query_ || 'JOIN container.biblio_record_entry_bucket_item ' ||
119 'brebi ON (brebi.target_biblio_record_entry = record ' ||
120 'AND brebi.bucket = ' || bucket_id || E')\n';
123 query_ := query_ || 'JOIN biblio.record_entry bre ON (bre.id = record) ' || 'WHERE ' || wq || ' AND not bre.deleted';
125 -- this will return rows of record,quality
126 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
130 DROP TABLE _vandelay_tmp_qrows;
131 DROP TABLE _vandelay_tmp_jrows;