26d9b530bf5bb5b3f57644c0784cb375cbb5205b
[working/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 TABLE search.relevance_adjustment (
25     id          SERIAL  PRIMARY KEY,
26     active      BOOL    NOT NULL DEFAULT TRUE,
27     field       INT     NOT NULL REFERENCES config.metabib_field (id) DEFERRABLE INITIALLY DEFERRED,
28     bump_type   TEXT    NOT NULL CHECK (bump_type IN ('word_order','first_word','full_match')),
29     multiplier  NUMERIC NOT NULL DEFAULT 1.0
30 );
31 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
32
33 CREATE TYPE search.search_result AS ( id BIGINT, rel NUMERIC, record INT, total INT, checked INT, visible INT, deleted INT, excluded INT );
34 CREATE TYPE search.search_args AS ( id INT, field_class TEXT, field_name TEXT, table_alias TEXT, term TEXT, term_type TEXT );
35
36 CREATE OR REPLACE FUNCTION search.query_parser_fts (
37
38     param_search_ou INT,
39     param_depth     INT,
40     param_query     TEXT,
41     param_statuses  INT[],
42     param_locations INT[],
43     param_offset    INT,
44     param_check     INT,
45     param_limit     INT,
46     metarecord      BOOL,
47     staff           BOOL,
48     param_pref_ou   INT DEFAULT NULL
49 ) RETURNS SETOF search.search_result AS $func$
50 DECLARE
51
52     current_res         search.search_result%ROWTYPE;
53     search_org_list     INT[];
54     luri_org_list       INT[];
55     tmp_int_list        INT[];
56
57     check_limit         INT;
58     core_limit          INT;
59     core_offset         INT;
60     tmp_int             INT;
61
62     core_result         RECORD;
63     core_cursor         REFCURSOR;
64     core_rel_query      TEXT;
65
66     total_count         INT := 0;
67     check_count         INT := 0;
68     deleted_count       INT := 0;
69     visible_count       INT := 0;
70     excluded_count      INT := 0;
71
72 BEGIN
73
74     check_limit := COALESCE( param_check, 1000 );
75     core_limit  := COALESCE( param_limit, 25000 );
76     core_offset := COALESCE( param_offset, 0 );
77
78     -- core_skip_chk := COALESCE( param_skip_chk, 1 );
79
80     IF param_search_ou > 0 THEN
81         IF param_depth IS NOT NULL THEN
82             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
83         ELSE
84             SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
85         END IF;
86
87         SELECT array_accum(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
88
89     ELSIF param_search_ou < 0 THEN
90         SELECT array_accum(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
91
92         FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
93             SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
94             luri_org_list := luri_org_list || tmp_int_list;
95         END LOOP;
96
97         SELECT array_accum(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
98
99     ELSIF param_search_ou = 0 THEN
100         -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
101     END IF;
102
103     IF param_pref_ou IS NOT NULL THEN
104         SELECT array_accum(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
105         luri_org_list := luri_org_list || tmp_int_list;
106     END IF;
107
108     OPEN core_cursor FOR EXECUTE param_query;
109
110     LOOP
111
112         FETCH core_cursor INTO core_result;
113         EXIT WHEN NOT FOUND;
114         EXIT WHEN total_count >= core_limit;
115
116         total_count := total_count + 1;
117
118         CONTINUE WHEN total_count NOT BETWEEN  core_offset + 1 AND check_limit + core_offset;
119
120         check_count := check_count + 1;
121
122         PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
123         IF NOT FOUND THEN
124             -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
125             deleted_count := deleted_count + 1;
126             CONTINUE;
127         END IF;
128
129         PERFORM 1
130           FROM  biblio.record_entry b
131                 JOIN config.bib_source s ON (b.source = s.id)
132           WHERE s.transcendant
133                 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
134
135         IF FOUND THEN
136             -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
137             visible_count := visible_count + 1;
138
139             current_res.id = core_result.id;
140             current_res.rel = core_result.rel;
141
142             tmp_int := 1;
143             IF metarecord THEN
144                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
145             END IF;
146
147             IF tmp_int = 1 THEN
148                 current_res.record = core_result.records[1];
149             ELSE
150                 current_res.record = NULL;
151             END IF;
152
153             RETURN NEXT current_res;
154
155             CONTINUE;
156         END IF;
157
158         PERFORM 1
159           FROM  asset.call_number cn
160                 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
161                 JOIN asset.uri uri ON (map.uri = uri.id)
162           WHERE NOT cn.deleted
163                 AND cn.label = '##URI##'
164                 AND uri.active
165                 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
166                 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
167                 AND cn.owning_lib IN ( SELECT * FROM unnest( luri_org_list ) )
168           LIMIT 1;
169
170         IF FOUND THEN
171             -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
172             visible_count := visible_count + 1;
173
174             current_res.id = core_result.id;
175             current_res.rel = core_result.rel;
176
177             tmp_int := 1;
178             IF metarecord THEN
179                 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
180             END IF;
181
182             IF tmp_int = 1 THEN
183                 current_res.record = core_result.records[1];
184             ELSE
185                 current_res.record = NULL;
186             END IF;
187
188             RETURN NEXT current_res;
189
190             CONTINUE;
191         END IF;
192
193         IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
194
195             PERFORM 1
196               FROM  asset.call_number cn
197                     JOIN asset.copy cp ON (cp.call_number = cn.id)
198               WHERE NOT cn.deleted
199                     AND NOT cp.deleted
200                     AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
201                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
202                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
203               LIMIT 1;
204
205             IF NOT FOUND THEN
206                 PERFORM 1
207                   FROM  biblio.peer_bib_copy_map pr
208                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
209                   WHERE NOT cp.deleted
210                         AND cp.status IN ( SELECT * FROM unnest( param_statuses ) )
211                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
212                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
213                   LIMIT 1;
214
215                 IF NOT FOUND THEN
216                 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
217                     excluded_count := excluded_count + 1;
218                     CONTINUE;
219                 END IF;
220             END IF;
221
222         END IF;
223
224         IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
225
226             PERFORM 1
227               FROM  asset.call_number cn
228                     JOIN asset.copy cp ON (cp.call_number = cn.id)
229               WHERE NOT cn.deleted
230                     AND NOT cp.deleted
231                     AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
232                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
233                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
234               LIMIT 1;
235
236             IF NOT FOUND THEN
237                 PERFORM 1
238                   FROM  biblio.peer_bib_copy_map pr
239                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
240                   WHERE NOT cp.deleted
241                         AND cp.location IN ( SELECT * FROM unnest( param_locations ) )
242                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
243                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
244                   LIMIT 1;
245
246                 IF NOT FOUND THEN
247                     -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
248                     excluded_count := excluded_count + 1;
249                     CONTINUE;
250                 END IF;
251             END IF;
252
253         END IF;
254
255         IF staff IS NULL OR NOT staff THEN
256
257             PERFORM 1
258               FROM  asset.opac_visible_copies
259               WHERE circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
260                     AND record IN ( SELECT * FROM unnest( core_result.records ) )
261               LIMIT 1;
262
263             IF NOT FOUND THEN
264                 PERFORM 1
265                   FROM  biblio.peer_bib_copy_map pr
266                         JOIN asset.opac_visible_copies cp ON (cp.copy_id = pr.target_copy)
267                   WHERE cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
268                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
269                   LIMIT 1;
270
271                 IF NOT FOUND THEN
272
273                     -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
274                     excluded_count := excluded_count + 1;
275                     CONTINUE;
276                 END IF;
277             END IF;
278
279         ELSE
280
281             PERFORM 1
282               FROM  asset.call_number cn
283                     JOIN asset.copy cp ON (cp.call_number = cn.id)
284               WHERE NOT cn.deleted
285                     AND NOT cp.deleted
286                     AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
287                     AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
288               LIMIT 1;
289
290             IF NOT FOUND THEN
291
292                 PERFORM 1
293                   FROM  biblio.peer_bib_copy_map pr
294                         JOIN asset.copy cp ON (cp.id = pr.target_copy)
295                   WHERE NOT cp.deleted
296                         AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
297                         AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
298                   LIMIT 1;
299
300                 IF NOT FOUND THEN
301
302                     PERFORM 1
303                       FROM  asset.call_number cn
304                             JOIN asset.copy cp ON (cp.call_number = cn.id)
305                       WHERE cn.record IN ( SELECT * FROM unnest( core_result.records ) )
306                             AND NOT cp.deleted
307                       LIMIT 1;
308
309                     IF FOUND THEN
310                         -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
311                         excluded_count := excluded_count + 1;
312                         CONTINUE;
313                     END IF;
314                 END IF;
315
316             END IF;
317
318         END IF;
319
320         visible_count := visible_count + 1;
321
322         current_res.id = core_result.id;
323         current_res.rel = core_result.rel;
324
325         tmp_int := 1;
326         IF metarecord THEN
327             SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
328         END IF;
329
330         IF tmp_int = 1 THEN
331             current_res.record = core_result.records[1];
332         ELSE
333             current_res.record = NULL;
334         END IF;
335
336         RETURN NEXT current_res;
337
338         IF visible_count % 1000 = 0 THEN
339             -- RAISE NOTICE ' % visible so far ... ', visible_count;
340         END IF;
341
342     END LOOP;
343
344     current_res.id = NULL;
345     current_res.rel = NULL;
346     current_res.record = NULL;
347     current_res.total = total_count;
348     current_res.checked = check_count;
349     current_res.deleted = deleted_count;
350     current_res.visible = visible_count;
351     current_res.excluded = excluded_count;
352
353     CLOSE core_cursor;
354
355     RETURN NEXT current_res;
356
357 END;
358 $func$ LANGUAGE PLPGSQL;
359
360 COMMIT;
361