LP#1505286: limit number of facets retrieved
[Evergreen.git] / Open-ILS / src / sql / Pg / 300.schema.staged_search.sql
1 /*
2  * Copyright (C) 2007-2010  Equinox Software, Inc.
3  * Mike Rylander <miker@esilibrary.com> 
4  *
5  * This program is free software; you can redistribute it and/or
6  * modify it under the terms of the GNU General Public License
7  * as published by the Free Software Foundation; either version 2
8  * of the License, or (at your option) any later version.
9  *
10  * This program is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13  * GNU General Public License for more details.
14  *
15  */
16
17
18 DROP SCHEMA IF EXISTS search CASCADE;
19
20 BEGIN;
21
22 CREATE SCHEMA search;
23
24 CREATE OR REPLACE FUNCTION evergreen.pg_statistics (tab TEXT, col TEXT) RETURNS TABLE(element TEXT, frequency INT) AS $$
25 BEGIN
26     -- This query will die on PG < 9.2, but the function can be created. We just won't use it where we can't.
27     RETURN QUERY
28         SELECT  e,
29                 f
30           FROM  (SELECT ROW_NUMBER() OVER (),
31                         (f * 100)::INT AS f
32                   FROM  (SELECT UNNEST(most_common_elem_freqs) AS f
33                           FROM  pg_stats
34                           WHERE tablename = tab
35                                 AND attname = col
36                         )x
37                 ) AS f
38                 JOIN (SELECT ROW_NUMBER() OVER (),
39                              e
40                        FROM (SELECT UNNEST(most_common_elems::text::text[]) AS e
41                               FROM  pg_stats
42                               WHERE tablename = tab
43                                     AND attname = col
44                             )y
45                 ) AS elems USING (row_number);
46 END;
47 $$ LANGUAGE PLPGSQL;
48
49 CREATE OR REPLACE FUNCTION evergreen.query_int_wrapper (INT[],TEXT) RETURNS BOOL AS $$
50 BEGIN
51     RETURN $1 @@ $2::query_int;
52 END;
53 $$ LANGUAGE PLPGSQL STABLE;
54
55 CREATE TABLE search.relevance_adjustment (
56     id          SERIAL  PRIMARY KEY,
57     active      BOOL    NOT NULL DEFAULT TRUE,
58     field       INT     NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED,
59     bump_type   TEXT    NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')),
60     multiplier  NUMERIC NOT NULL DEFAULT 1.0
61 );
62 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
63
64 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT );
65 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
66
67 CREATE OR REPLACE FUNCTION search.query_parser_fts (
68
69     param_search_ou INT,
70     param_depth     INT,
71     param_query     TEXT,
72     param_statuses  INT[],
73     param_locations INT[],
74     param_offset    INT,
75     param_check     INT,
76     param_limit     INT,
77     metarecord      BOOL,
78     staff           BOOL,
79     deleted_search  BOOL,
80     param_pref_ou   INT DEFAULT NULL
81 ) RETURNS SETOF search.search_result AS $func$
82 DECLARE
83
84     current_res         search.search_result%ROWTYPE;
85     search_org_list     INT[];
86     luri_org_list       INT[];
87     tmp_int_list        INT[];
88
89     check_limit         INT;
90     core_limit          INT;
91     core_offset         INT;
92     tmp_int             INT;
93
94     core_result         RECORD;
95     core_cursor         REFCURSOR;
96     core_rel_query      TEXT;
97
98     total_count         INT := 0;
99     check_count         INT := 0;
100     deleted_count       INT := 0;
101     visible_count       INT := 0;
102     excluded_count      INT := 0;
103
104     luri_as_copy        BOOL;
105 BEGIN
106
107     check_limit := COALESCE( param_check, 1000 );
108     core_limit  := COALESCE( param_limit, 25000 );
109     core_offset := COALESCE( param_offset, 0 );
110
111     SELECT COALESCE( enabled, FALSE ) INTO luri_as_copy FROM config.global_flag WHERE name = 'opac.located_uri.act_as_copy';
112
113     -- core_skip_chk := COALESCE( param_skip_chk, 1 );
114
115     IF param_search_ou > 0 THEN
116         IF param_depth IS NOT NULL THEN
117             SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
118         ELSE
119             SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
120         END IF;
121
122         IF luri_as_copy THEN
123             SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_full_path( param_search_ou );
124         ELSE
125             SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
126         END IF;
127
128     ELSIF param_search_ou < 0 THEN
129         SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
130
131         FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
132
133             IF luri_as_copy THEN
134                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( tmp_int );
135             ELSE
136                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
137             END IF;
138
139             luri_org_list := luri_org_list || tmp_int_list;
140         END LOOP;
141
142         SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
143
144     ELSIF param_search_ou = 0 THEN
145         -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
146     END IF;
147
148     IF param_pref_ou IS NOT NULL THEN
149             IF luri_as_copy THEN
150                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_full_path( param_pref_ou );
151             ELSE
152                 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( param_pref_ou );
153             END IF;
154
155         luri_org_list := luri_org_list || tmp_int_list;
156     END IF;
157
158     OPEN core_cursor FOR EXECUTE param_query;
159
160     LOOP
161
162         FETCH core_cursor INTO core_result;
163         EXIT WHEN NOT FOUND;
164         EXIT WHEN total_count >= core_limit;
165
166         total_count := total_count + 1;
167
168         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
169
170         check_count := check_count + 1;
171
172         IF NOT deleted_search THEN
173
174             PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
175             IF NOT FOUND THEN
176                 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
177                 deleted_count := deleted_count + 1;
178                 CONTINUE;
179             END IF;
180
181             PERFORM 1
182               FROM  biblio.record_entry b
183                     JOIN config.bib_source s ON (b.source = s.id)
184               WHERE s.transcendant
185                     AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
186
187             IF FOUND THEN
188                 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
189                 visible_count := visible_count + 1;
190
191                 current_res.id = core_result.id;
192                 current_res.rel = core_result.rel;
193
194                 tmp_int := 1;
195                 IF metarecord THEN
196                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
197                 END IF;
198
199                 IF tmp_int = 1 THEN
200                     current_res.record = core_result.records[1];
201                 ELSE
202                     current_res.record = NULL;
203                 END IF;
204
205                 RETURN NEXT current_res;
206
207                 CONTINUE;
208             END IF;
209
210             PERFORM 1
211               FROM  asset.call_number cn
212                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
213                     JOIN asset.uri uri ON (map.uri = uri.id)
214               WHERE NOT cn.deleted
215                     AND cn.label = '##URI##'
216                     AND uri.active
217                     AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
218                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
219                     AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
220               LIMIT 1;
221
222             IF FOUND THEN
223                 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
224                 visible_count := visible_count + 1;
225
226                 current_res.id = core_result.id;
227                 current_res.rel = core_result.rel;
228
229                 tmp_int := 1;
230                 IF metarecord THEN
231                     SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
232                 END IF;
233
234                 IF tmp_int = 1 THEN
235                     current_res.record = core_result.records[1];
236                 ELSE
237                     current_res.record = NULL;
238                 END IF;
239
240                 RETURN NEXT current_res;
241
242                 CONTINUE;
243             END IF;
244
245             IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
246
247                 PERFORM 1
248                   FROM  asset.call_number cn
249                         JOIN asset.copy cp ON (cp.call_number = cn.id)
250                   WHERE NOT cn.deleted
251                         AND NOT cp.deleted
252                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
253                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
254                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
255                   LIMIT 1;
256
257                 IF NOT FOUND THEN
258                     PERFORM 1
259                       FROM  biblio.peer_bib_copy_map pr
260                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
261                       WHERE NOT cp.deleted
262                             AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
263                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
264                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
265                       LIMIT 1;
266
267                     IF NOT FOUND THEN
268                     -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
269                         excluded_count := excluded_count + 1;
270                         CONTINUE;
271                     END IF;
272                 END IF;
273
274             END IF;
275
276             IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
277
278                 PERFORM 1
279                   FROM  asset.call_number cn
280                         JOIN asset.copy cp ON (cp.call_number = cn.id)
281                   WHERE NOT cn.deleted
282                         AND NOT cp.deleted
283                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
284                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
285                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
286                   LIMIT 1;
287
288                 IF NOT FOUND THEN
289                     PERFORM 1
290                       FROM  biblio.peer_bib_copy_map pr
291                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
292                       WHERE NOT cp.deleted
293                             AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
294                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
295                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
296                       LIMIT 1;
297
298                     IF NOT FOUND THEN
299                         -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
300                         excluded_count := excluded_count + 1;
301                         CONTINUE;
302                     END IF;
303                 END IF;
304
305             END IF;
306
307             IF staff IS NULL OR NOT staff THEN
308
309                 PERFORM 1
310                   FROM  asset.opac_visible_copies
311                   WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
312                         AND record IN ( SELECT * FROM unnest( core_result.records ) )
313                   LIMIT 1;
314
315                 IF NOT FOUND THEN
316                     PERFORM 1
317                       FROM  biblio.peer_bib_copy_map pr
318                             JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
319                       WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
320                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
321                       LIMIT 1;
322
323                     IF NOT FOUND THEN
324
325                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
326                         excluded_count := excluded_count + 1;
327                         CONTINUE;
328                     END IF;
329                 END IF;
330
331             ELSE
332
333                 PERFORM 1
334                   FROM  asset.call_number cn
335                         JOIN asset.copy cp ON (cp.call_number = cn.id)
336                   WHERE NOT cn.deleted
337                         AND NOT cp.deleted
338                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
339                         AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
340                   LIMIT 1;
341
342                 IF NOT FOUND THEN
343
344                     PERFORM 1
345                       FROM  biblio.peer_bib_copy_map pr
346                             JOIN asset.copy cp ON (cp.id = pr.target_copy)
347                       WHERE NOT cp.deleted
348                             AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
349                             AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
350                       LIMIT 1;
351
352                     IF NOT FOUND THEN
353
354                         PERFORM 1
355                           FROM  asset.call_number cn
356                                 JOIN asset.copy cp ON (cp.call_number = cn.id)
357                           WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
358                                 AND NOT cp.deleted
359                           LIMIT 1;
360
361                         IF NOT FOUND THEN
362                             -- Recheck Located URI visibility in the case of no "foreign" copies
363                             PERFORM 1
364                               FROM  asset.call_number cn
365                                     JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
366                                     JOIN asset.uri uri ON (map.uri = uri.id)
367                               WHERE NOT cn.deleted
368                                     AND cn.label = '##URI##'
369                                     AND uri.active
370                                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
371                                     AND cn.owning_lib NOT IN ( SELECT * FROM unnest( luri_org_list ) )
372                               LIMIT 1;
373
374                             IF FOUND THEN
375                                 -- RAISE NOTICE ' % were excluded for foreign located URIs... ', core_result.records;
376                                 excluded_count := excluded_count + 1;
377                                 CONTINUE;
378                             END IF;
379                         ELSE
380                             -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
381                             excluded_count := excluded_count + 1;
382                             CONTINUE;
383                         END IF;
384                     END IF;
385
386                 END IF;
387
388             END IF;
389
390         END IF;
391
392         visible_count := visible_count + 1;
393
394         current_res.id = core_result.id;
395         current_res.rel = core_result.rel;
396
397         tmp_int := 1;
398         IF metarecord THEN
399             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
400         END IF;
401
402         IF tmp_int = 1 THEN
403             current_res.record = core_result.records[1];
404         ELSE
405             current_res.record = NULL;
406         END IF;
407
408         RETURN NEXT current_res;
409
410         IF visible_count % 1000 = 0 THEN
411             -- RAISE NOTICE ' % visible so far ... ', visible_count;
412         END IF;
413
414     END LOOP;
415
416     current_res.id = NULL;
417     current_res.rel = NULL;
418     current_res.record = NULL;
419     current_res.total = total_count;
420     current_res.checked = check_count;
421     current_res.deleted = deleted_count;
422     current_res.visible = visible_count;
423     current_res.excluded = excluded_count;
424
425     CLOSE core_cursor;
426
427     RETURN NEXT current_res;
428
429 END;
430 $func$ LANGUAGE PLPGSQL;
431
432 CREATE OR REPLACE FUNCTION search.facets_for_record_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
433     SELECT id, value, count FROM (
434         SELECT mfae.field AS id,
435                mfae.value,
436                COUNT(DISTINCT mmrsm.source),
437                row_number() OVER (
438                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.source) DESC
439                ) AS rownum
440         FROM metabib.facet_entry mfae
441         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
442         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
443         WHERE mmrsm.source IN (SELECT * FROM unnest(hits))
444         AND cmf.facet_field
445         AND cmf.field_class NOT IN (SELECT * FROM unnest(ignore_facet_classes))
446         GROUP by 1, 2
447     ) all_facets
448     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
449 $$ LANGUAGE SQL;
450
451 CREATE OR REPLACE FUNCTION search.facets_for_metarecord_set(ignore_facet_classes TEXT[], hits BIGINT[]) RETURNS TABLE (id INT, value TEXT, count BIGINT) AS $$
452     SELECT id, value, count FROM (
453         SELECT mfae.field AS id,
454                mfae.value,
455                COUNT(DISTINCT mmrsm.metarecord),
456                row_number() OVER (
457                 PARTITION BY mfae.field ORDER BY COUNT(distinct mmrsm.metarecord) DESC
458                ) AS rownum
459         FROM metabib.facet_entry mfae
460         JOIN metabib.metarecord_source_map mmrsm ON (mfae.source = mmrsm.source)
461         JOIN config.metabib_field cmf ON (cmf.id = mfae.field)
462         WHERE mmrsm.metarecord IN (SELECT * FROM unnest(hits))
463         AND cmf.facet_field
464         AND cmf.field_class NOT IN (SELECT * FROM unnest(ignore_facet_classes))
465         GROUP by 1, 2
466     ) all_facets
467     WHERE rownum <= (SELECT COALESCE((SELECT value::INT FROM config.global_flag WHERE name = 'search.max_facets_per_field' AND enabled), 1000));
468 $$ LANGUAGE SQL;
469
470 COMMIT;