2 * Copyright (C) 2007-2010 Equinox Software, Inc.
3 * Mike Rylander <miker@esilibrary.com>
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.
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.
18 DROP SCHEMA IF EXISTS search CASCADE;
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
31 CREATE UNIQUE INDEX bump_once_per_field_idx ON search.relevance_adjustment ( field, bump_type );
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 );
36 CREATE OR REPLACE FUNCTION search.query_parser_fts (
42 param_locations INT[],
49 param_pref_ou INT DEFAULT NULL
50 ) RETURNS SETOF search.search_result AS $func$
53 current_res search.search_result%ROWTYPE;
54 search_org_list INT[];
64 core_cursor REFCURSOR;
69 deleted_count INT := 0;
70 visible_count INT := 0;
71 excluded_count INT := 0;
75 check_limit := COALESCE( param_check, 1000 );
76 core_limit := COALESCE( param_limit, 25000 );
77 core_offset := COALESCE( param_offset, 0 );
79 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
81 IF param_search_ou > 0 THEN
82 IF param_depth IS NOT NULL THEN
83 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
85 SELECT ARRAY_AGG(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
88 SELECT ARRAY_AGG(distinct id) INTO luri_org_list FROM actor.org_unit_ancestors( param_search_ou );
90 ELSIF param_search_ou < 0 THEN
91 SELECT ARRAY_AGG(distinct org_unit) INTO search_org_list FROM actor.org_lasso_map WHERE lasso = -param_search_ou;
93 FOR tmp_int IN SELECT * FROM UNNEST(search_org_list) LOOP
94 SELECT ARRAY_AGG(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors( tmp_int );
95 luri_org_list := luri_org_list || tmp_int_list;
98 SELECT ARRAY_AGG(DISTINCT x.id) INTO luri_org_list FROM UNNEST(luri_org_list) x(id);
100 ELSIF param_search_ou = 0 THEN
101 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
104 IF param_pref_ou IS NOT NULL THEN
105 SELECT array_agg(distinct id) INTO tmp_int_list FROM actor.org_unit_ancestors(param_pref_ou);
106 luri_org_list := luri_org_list || tmp_int_list;
109 OPEN core_cursor FOR EXECUTE param_query;
113 FETCH core_cursor INTO core_result;
115 EXIT WHEN total_count >= core_limit;
117 total_count := total_count + 1;
119 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
121 check_count := check_count + 1;
123 IF NOT deleted_search THEN
125 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
127 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
128 deleted_count := deleted_count + 1;
133 FROM biblio.record_entry b
134 JOIN config.bib_source s ON (b.source = s.id)
136 AND b.id IN ( SELECT * FROM unnest( core_result.records ) );
139 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
140 visible_count := visible_count + 1;
142 current_res.id = core_result.id;
143 current_res.rel = core_result.rel;
147 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
151 current_res.record = core_result.records[1];
153 current_res.record = NULL;
156 RETURN NEXT current_res;
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)
166 AND cn.label = '##URI##'
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 ) )
174 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
175 visible_count := visible_count + 1;
177 current_res.id = core_result.id;
178 current_res.rel = core_result.rel;
182 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
186 current_res.record = core_result.records[1];
188 current_res.record = NULL;
191 RETURN NEXT current_res;
196 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
199 FROM asset.call_number cn
200 JOIN asset.copy cp ON (cp.call_number = cn.id)
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 ) )
210 FROM biblio.peer_bib_copy_map pr
211 JOIN asset.copy cp ON (cp.id = pr.target_copy)
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 ) )
219 -- RAISE NOTICE ' % and multi-home linked records were all status-excluded ... ', core_result.records;
220 excluded_count := excluded_count + 1;
227 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
230 FROM asset.call_number cn
231 JOIN asset.copy cp ON (cp.call_number = cn.id)
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 ) )
241 FROM biblio.peer_bib_copy_map pr
242 JOIN asset.copy cp ON (cp.id = pr.target_copy)
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 ) )
250 -- RAISE NOTICE ' % and multi-home linked records were all copy_location-excluded ... ', core_result.records;
251 excluded_count := excluded_count + 1;
258 IF staff IS NULL OR NOT staff THEN
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 ) )
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 ) )
276 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
277 excluded_count := excluded_count + 1;
285 FROM asset.call_number cn
286 JOIN asset.copy cp ON (cp.call_number = cn.id)
289 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
290 AND cn.record IN ( SELECT * FROM unnest( core_result.records ) )
296 FROM biblio.peer_bib_copy_map pr
297 JOIN asset.copy cp ON (cp.id = pr.target_copy)
299 AND cp.circ_lib IN ( SELECT * FROM unnest( search_org_list ) )
300 AND pr.peer_record IN ( SELECT * FROM unnest( core_result.records ) )
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 ) )
313 -- RAISE NOTICE ' % and multi-home linked records were all visibility-excluded ... ', core_result.records;
314 excluded_count := excluded_count + 1;
325 visible_count := visible_count + 1;
327 current_res.id = core_result.id;
328 current_res.rel = core_result.rel;
332 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
336 current_res.record = core_result.records[1];
338 current_res.record = NULL;
341 RETURN NEXT current_res;
343 IF visible_count % 1000 = 0 THEN
344 -- RAISE NOTICE ' % visible so far ... ', visible_count;
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;
360 RETURN NEXT current_res;
363 $func$ LANGUAGE PLPGSQL;