3 SELECT evergreen.upgrade_deps_block_check('1071', :eg_version); --gmcharlt/kmlussier
5 CREATE OR REPLACE FUNCTION metabib.staged_browse(query text, fields integer[], context_org integer, context_locations integer[], staff boolean, browse_superpage_size integer, count_up_from_zero boolean, result_limit integer, next_pivot_pos integer)
6 RETURNS SETOF metabib.flat_browse_entry_appearance
15 result_row metabib.flat_browse_entry_appearance%ROWTYPE;
16 results_skipped INT := 0;
23 all_brecords BIGINT[];
24 all_arecords BIGINT[];
25 superpage_of_records BIGINT[];
30 unauthorized_entry RECORD;
32 IF count_up_from_zero THEN
39 SELECT x.c_attrs, x.b_attrs INTO c_tests, b_tests FROM asset.patron_default_visibility_mask() x;
42 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
43 IF b_tests <> '' THEN b_tests := b_tests || '&'; END IF;
45 SELECT ARRAY_AGG(id) INTO c_orgs FROM actor.org_unit_descendants(context_org);
47 c_tests := c_tests || search.calculate_visibility_attribute_test('circ_lib',c_orgs)
48 || '&' || search.calculate_visibility_attribute_test('owning_lib',c_orgs);
50 PERFORM 1 FROM config.internal_flag WHERE enabled AND name = 'opac.located_uri.act_as_copy';
52 b_tests := b_tests || search.calculate_visibility_attribute_test(
54 (SELECT ARRAY_AGG(id) FROM actor.org_unit_full_path(context_org) x)
57 b_tests := b_tests || search.calculate_visibility_attribute_test(
59 (SELECT ARRAY_AGG(id) FROM actor.org_unit_ancestors(context_org) x)
63 IF context_locations THEN
64 IF c_tests <> '' THEN c_tests := c_tests || '&'; END IF;
65 c_tests := c_tests || search.calculate_visibility_attribute_test('location',context_locations);
68 OPEN curs NO SCROLL FOR EXECUTE query;
73 IF result_row.pivot_point IS NOT NULL THEN
74 RETURN NEXT result_row;
80 SELECT INTO unauthorized_entry *
81 FROM metabib.browse_entry_simple_heading_map mbeshm
82 INNER JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
83 INNER JOIN authority.control_set_authority_field acsaf ON ( acsaf.id = ash.atag )
84 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
85 WHERE mbeshm.entry = rec.id
86 AND ahf.heading_purpose = 'variant';
88 -- Gather aggregate data based on the MBE row we're looking at now, authority axis
89 IF (unauthorized_entry.record IS NOT NULL) THEN
90 --unauthorized term belongs to an auth linked to a bib?
91 SELECT INTO all_arecords, result_row.sees, afields
92 ARRAY_AGG(DISTINCT abl.bib),
93 STRING_AGG(DISTINCT abl.authority::TEXT, $$,$$),
94 ARRAY_AGG(DISTINCT map.metabib_field)
95 FROM authority.bib_linking abl
96 INNER JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
97 map.authority_field = unauthorized_entry.atag
98 AND map.metabib_field = ANY(fields)
100 WHERE abl.authority = unauthorized_entry.record;
103 SELECT INTO all_arecords, result_row.sees, afields
104 ARRAY_AGG(DISTINCT abl.bib), -- bibs to check for visibility
105 STRING_AGG(DISTINCT aal.source::TEXT, $$,$$), -- authority record ids
106 ARRAY_AGG(DISTINCT map.metabib_field) -- authority-tag-linked CMF rows
108 FROM metabib.browse_entry_simple_heading_map mbeshm
109 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
110 JOIN authority.authority_linking aal ON ( ash.record = aal.source )
111 JOIN authority.bib_linking abl ON ( aal.target = abl.authority )
112 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
113 ash.atag = map.authority_field
114 AND map.metabib_field = ANY(fields)
116 JOIN authority.control_set_authority_field acsaf ON (
117 map.authority_field = acsaf.id
119 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
120 WHERE mbeshm.entry = rec.id
121 AND ahf.heading_purpose = 'variant';
125 -- Gather aggregate data based on the MBE row we're looking at now, bib axis
126 SELECT INTO all_brecords, result_row.authorities, bfields
127 ARRAY_AGG(DISTINCT source),
128 STRING_AGG(DISTINCT authority::TEXT, $$,$$),
129 ARRAY_AGG(DISTINCT def)
130 FROM metabib.browse_entry_def_map
132 AND def = ANY(fields);
134 SELECT INTO result_row.fields STRING_AGG(DISTINCT x::TEXT, $$,$$) FROM UNNEST(afields || bfields) x;
136 result_row.sources := 0;
137 result_row.asources := 0;
139 -- Bib-linked vis checking
140 IF ARRAY_UPPER(all_brecords,1) IS NOT NULL THEN
142 SELECT INTO result_row.sources COUNT(DISTINCT b.id)
143 FROM biblio.record_entry b
144 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
145 WHERE b.id = ANY(all_brecords[1:browse_superpage_size])
147 acvac.vis_attr_vector @@ c_tests::query_int
148 OR b.vis_attr_vector @@ b_tests::query_int
151 result_row.accurate := TRUE;
155 -- Authority-linked vis checking
156 IF ARRAY_UPPER(all_arecords,1) IS NOT NULL THEN
158 SELECT INTO result_row.asources COUNT(DISTINCT b.id)
159 FROM biblio.record_entry b
160 JOIN asset.copy_vis_attr_cache acvac ON (acvac.record = b.id)
161 WHERE b.id = ANY(all_arecords[1:browse_superpage_size])
163 acvac.vis_attr_vector @@ c_tests::query_int
164 OR b.vis_attr_vector @@ b_tests::query_int
167 result_row.aaccurate := TRUE;
171 IF result_row.sources > 0 OR result_row.asources > 0 THEN
173 -- The function that calls this function needs row_number in order
174 -- to correctly order results from two different runs of this
176 result_row.row_number := row_number;
178 -- Now, if row_counter is still less than limit, return a row. If
179 -- not, but it is less than next_pivot_pos, continue on without
180 -- returning actual result rows until we find
181 -- that next pivot, and return it.
183 IF row_counter < result_limit THEN
184 result_row.browse_entry := rec.id;
185 result_row.value := rec.value;
187 RETURN NEXT result_row;
189 result_row.browse_entry := NULL;
190 result_row.authorities := NULL;
191 result_row.fields := NULL;
192 result_row.value := NULL;
193 result_row.sources := NULL;
194 result_row.sees := NULL;
195 result_row.accurate := NULL;
196 result_row.aaccurate := NULL;
197 result_row.pivot_point := rec.id;
199 IF row_counter >= next_pivot_pos THEN
200 RETURN NEXT result_row;
205 IF count_up_from_zero THEN
206 row_number := row_number + 1;
208 row_number := row_number - 1;
211 -- row_counter is different from row_number.
212 -- It simply counts up from zero so that we know when
213 -- we've reached our limit.
214 row_counter := row_counter + 1;
218 $f$ LANGUAGE plpgsql ROWS 10;
220 CREATE OR REPLACE FUNCTION metabib.browse(search_field integer[], browse_term text, context_org integer DEFAULT NULL::integer, context_loc_group integer DEFAULT NULL::integer, staff boolean DEFAULT false, pivot_id bigint DEFAULT NULL::bigint, result_limit integer DEFAULT 10)
221 RETURNS SETOF metabib.flat_browse_entry_appearance
227 pivot_sort_value TEXT;
228 pivot_sort_fallback TEXT;
229 context_locations INT[];
230 browse_superpage_size INT;
231 results_skipped INT := 0;
235 forward_to_pivot INT;
237 -- First, find the pivot if we were given a browse term but not a pivot.
238 IF pivot_id IS NULL THEN
239 pivot_id := metabib.browse_pivot(search_field, browse_term);
242 SELECT INTO pivot_sort_value, pivot_sort_fallback
243 sort_value, value FROM metabib.browse_entry WHERE id = pivot_id;
245 -- Bail if we couldn't find a pivot.
246 IF pivot_sort_value IS NULL THEN
250 -- Transform the context_loc_group argument (if any) (logc at the
251 -- TPAC layer) into a form we'll be able to use.
252 IF context_loc_group IS NOT NULL THEN
253 SELECT INTO context_locations ARRAY_AGG(location)
254 FROM asset.copy_location_group_map
255 WHERE lgroup = context_loc_group;
258 -- Get the configured size of browse superpages.
259 SELECT INTO browse_superpage_size COALESCE(value::INT,100) -- NULL ok
260 FROM config.global_flag
261 WHERE enabled AND name = 'opac.browse.holdings_visibility_test_limit';
263 -- First we're going to search backward from the pivot, then we're going
264 -- to search forward. In each direction, we need two limits. At the
265 -- lesser of the two limits, we delineate the edge of the result set
266 -- we're going to return. At the greater of the two limits, we find the
267 -- pivot value that would represent an offset from the current pivot
268 -- at a distance of one "page" in either direction, where a "page" is a
269 -- result set of the size specified in the "result_limit" argument.
271 -- The two limits in each direction make four derived values in total,
272 -- and we calculate them now.
273 back_limit := CEIL(result_limit::FLOAT / 2);
274 back_to_pivot := result_limit;
275 forward_limit := result_limit / 2;
276 forward_to_pivot := result_limit - 1;
278 -- This is the meat of the SQL query that finds browse entries. We'll
279 -- pass this to a function which uses it with a cursor, so that individual
280 -- rows may be fetched in a loop until some condition is satisfied, without
281 -- waiting for a result set of fixed size to be collected all at once.
286 FROM metabib.browse_entry mbe
288 EXISTS ( -- are there any bibs using this mbe via the requested fields?
290 FROM metabib.browse_entry_def_map mbedm
291 WHERE mbedm.entry = mbe.id AND mbedm.def = ANY(' || quote_literal(search_field) || ')
292 ) OR EXISTS ( -- are there any authorities using this mbe via the requested fields?
294 FROM metabib.browse_entry_simple_heading_map mbeshm
295 JOIN authority.simple_heading ash ON ( mbeshm.simple_heading = ash.id )
296 JOIN authority.control_set_auth_field_metabib_field_map_refs map ON (
297 ash.atag = map.authority_field
298 AND map.metabib_field = ANY(' || quote_literal(search_field) || ')
300 JOIN authority.control_set_authority_field acsaf ON (
301 map.authority_field = acsaf.id
303 JOIN authority.heading_field ahf ON (ahf.id = acsaf.heading_field)
304 WHERE mbeshm.entry = mbe.id
305 AND ahf.heading_purpose IN (' || $$'variant'$$ || ')
306 -- and authority that variant is coming from is linked to a bib
309 FROM metabib.browse_entry_def_map mbedm2
310 WHERE mbedm2.authority = ash.record AND mbedm2.def = ANY(' || quote_literal(search_field) || ')
315 -- This is the variant of the query for browsing backward.
316 back_query := core_query ||
317 ' mbe.sort_value <= ' || quote_literal(pivot_sort_value) ||
318 ' ORDER BY mbe.sort_value DESC, mbe.value DESC LIMIT 1000';
320 -- This variant browses forward.
321 forward_query := core_query ||
322 ' mbe.sort_value > ' || quote_literal(pivot_sort_value) ||
323 ' ORDER BY mbe.sort_value, mbe.value LIMIT 1000';
325 -- We now call the function which applies a cursor to the provided
326 -- queries, stopping at the appropriate limits and also giving us
327 -- the next page's pivot.
329 SELECT * FROM metabib.staged_browse(
330 back_query, search_field, context_org, context_locations,
331 staff, browse_superpage_size, TRUE, back_limit, back_to_pivot
333 SELECT * FROM metabib.staged_browse(
334 forward_query, search_field, context_org, context_locations,
335 staff, browse_superpage_size, FALSE, forward_limit, forward_to_pivot
336 ) ORDER BY row_number DESC;
339 $f$ LANGUAGE plpgsql ROWS 10;