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