3 -- XXXX.schema.vandelay.import-match-no-like-any.sql
5 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
7 CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml(
8 match_set_id INTEGER, record_xml TEXT
9 ) RETURNS SETOF vandelay.match_set_test_result AS $$
20 tags_rstore := vandelay.flatten_marc_hstore(record_xml);
21 svf_rstore := vandelay.extract_rec_attrs(record_xml);
23 CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER);
24 CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT);
26 -- generate the where clause and return that directly (into wq), and as
27 -- a side-effect, populate the _vandelay_tmp_[qj]rows tables.
28 wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore);
30 query_ := 'SELECT DISTINCT(bre.id) AS record, ';
32 -- qrows table is for the quality bits we add to the SELECT clause
33 SELECT ARRAY_TO_STRING(
34 ARRAY_ACCUM('COALESCE(n' || q::TEXT || '.quality, 0)'), ' + '
35 ) INTO coal FROM _vandelay_tmp_qrows;
37 -- our query string so far is the SELECT clause and the inital FROM.
38 -- no JOINs yet nor the WHERE clause
39 query_ := query_ || coal || ' AS quality ' || E'\n' ||
40 'FROM biblio.record_entry bre ';
42 -- jrows table is for the joins we must make (and the real text conditions)
43 SELECT ARRAY_TO_STRING(ARRAY_ACCUM(j), E'\n') INTO joins
44 FROM _vandelay_tmp_jrows;
46 -- add those joins and the where clause to our query.
47 query_ := query_ || joins || E'\n' || 'WHERE ' || wq || ' AND not bre.deleted';
49 -- this will return rows of record,quality
50 RAISE WARNING '%', query_;
51 FOR rec IN EXECUTE query_ USING tags_rstore, svf_rstore LOOP
55 DROP TABLE _vandelay_tmp_qrows;
56 DROP TABLE _vandelay_tmp_jrows;
62 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set(
67 root vandelay.match_set_point;
69 SELECT * INTO root FROM vandelay.match_set_point
70 WHERE parent IS NULL AND match_set = match_set_id;
72 RETURN vandelay.get_expr_from_match_set_point(root, tags_rstore);
76 CREATE OR REPLACE FUNCTION vandelay.get_expr_from_match_set_point(
77 node vandelay.match_set_point,
85 child vandelay.match_set_point;
87 SELECT ARRAY_ACCUM(id) INTO children FROM vandelay.match_set_point
88 WHERE parent = node.id;
90 IF ARRAY_LENGTH(children, 1) > 0 THEN
91 this_op := vandelay._get_expr_render_one(node);
94 WHILE children[i] IS NOT NULL LOOP
95 SELECT * INTO child FROM vandelay.match_set_point
96 WHERE id = children[i];
98 q := q || ' ' || this_op || ' ';
101 q := q || vandelay.get_expr_from_match_set_point(child, tags_rstore);
105 ELSIF node.bool_op IS NULL THEN
106 PERFORM vandelay._get_expr_push_qrow(node);
107 PERFORM vandelay._get_expr_push_jrow(node, tags_rstore);
108 RETURN vandelay._get_expr_render_one(node);
115 CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow(
116 node vandelay.match_set_point,
126 -- remember $1 is tags_rstore, and $2 is svf_rstore
130 IF node.tag IS NOT NULL THEN
131 caseless := (node.tag IN ('020', '022', '024'));
133 IF node.subfield IS NOT NULL THEN
134 tagkey := tagkey || node.subfield;
152 my_alias := 'n' || node.id::TEXT;
154 jrow := 'LEFT JOIN (SELECT *, ' || node.quality ||
155 ' AS quality FROM metabib.';
156 IF node.tag IS NOT NULL THEN
157 jrow := jrow || 'full_rec) ' || my_alias || ' ON (' ||
158 my_alias || '.record = bre.id AND ' || my_alias || '.tag = ''' ||
160 IF node.subfield IS NOT NULL THEN
161 jrow := jrow || ' AND ' || my_alias || '.subfield = ''' ||
162 node.subfield || '''';
164 jrow := jrow || ' AND (';
166 jrow := jrow || vandelay._node_tag_comparisons(caseless, my_alias, op, tags_rstore, tagkey);
167 jrow := jrow || '))';
169 jrow := jrow || 'record_attr) ' || my_alias || ' ON (' ||
170 my_alias || '.id = bre.id AND (' ||
171 my_alias || '.attrs->''' || node.svf ||
172 ''' ' || op || ' $2->''' || node.svf || '''))';
174 INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow);
178 CREATE OR REPLACE FUNCTION vandelay._node_tag_comparisons(
191 vals := tags_rstore->tagkey;
196 IF vals[i] IS NULL THEN
199 result := result || ' OR ';
204 result := result || 'LOWER(' || my_alias || '.value) ' || op;
206 result := result || my_alias || '.value ' || op;
209 result := result || ' ' || COALESCE('''' || vals[i] || '''', 'NULL');
211 IF vals[i] IS NULL THEN
222 -- drop old versions of these functions with fewer args
223 DROP FUNCTION vandelay.get_expr_from_match_set( INTEGER );
224 DROP FUNCTION vandelay.get_expr_from_match_set_point( vandelay.match_set_point );
225 DROP FUNCTION vandelay._get_expr_push_jrow( vandelay.match_set_point );