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