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.explode_array(anyarray) RETURNS SETOF anyelement AS $BODY$
37 SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;
39 LANGUAGE 'sql' IMMUTABLE;
41 CREATE OR REPLACE FUNCTION search.query_parser_fts (
47 param_locations INT[],
54 ) RETURNS SETOF search.search_result AS $func$
57 current_res search.search_result%ROWTYPE;
58 search_org_list INT[];
66 core_cursor REFCURSOR;
71 deleted_count INT := 0;
72 visible_count INT := 0;
73 excluded_count INT := 0;
77 check_limit := COALESCE( param_check, 1000 );
78 core_limit := COALESCE( param_limit, 25000 );
79 core_offset := COALESCE( param_offset, 0 );
81 -- core_skip_chk := COALESCE( param_skip_chk, 1 );
83 IF param_search_ou > 0 THEN
84 IF param_depth IS NOT NULL THEN
85 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou, param_depth );
87 SELECT array_accum(distinct id) INTO search_org_list FROM actor.org_unit_descendants( param_search_ou );
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 ELSIF param_search_ou = 0 THEN
92 -- reserved for user lassos (ou_buckets/type='lasso') with ID passed in depth ... hack? sure.
95 OPEN core_cursor FOR EXECUTE param_query;
99 FETCH core_cursor INTO core_result;
101 EXIT WHEN total_count >= core_limit;
103 total_count := total_count + 1;
105 CONTINUE WHEN total_count NOT BETWEEN core_offset + 1 AND check_limit + core_offset;
107 check_count := check_count + 1;
109 PERFORM 1 FROM biblio.record_entry b WHERE NOT b.deleted AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
111 -- RAISE NOTICE ' % were all deleted ... ', core_result.records;
112 deleted_count := deleted_count + 1;
117 FROM biblio.record_entry b
118 JOIN config.bib_source s ON (b.source = s.id)
120 AND b.id IN ( SELECT * FROM search.explode_array( core_result.records ) );
123 -- RAISE NOTICE ' % were all transcendant ... ', core_result.records;
124 visible_count := visible_count + 1;
126 current_res.id = core_result.id;
127 current_res.rel = core_result.rel;
131 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
135 current_res.record = core_result.records[1];
137 current_res.record = NULL;
140 RETURN NEXT current_res;
146 FROM asset.call_number cn
147 JOIN asset.uri_call_number_map map ON (map.call_number = cn.id)
148 JOIN asset.uri uri ON (map.uri = uri.id)
150 AND cn.label = '##URI##'
152 AND ( param_locations IS NULL OR array_upper(param_locations, 1) IS NULL )
153 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
154 AND cn.owning_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
158 -- RAISE NOTICE ' % have at least one URI ... ', core_result.records;
159 visible_count := visible_count + 1;
161 current_res.id = core_result.id;
162 current_res.rel = core_result.rel;
166 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
170 current_res.record = core_result.records[1];
172 current_res.record = NULL;
175 RETURN NEXT current_res;
180 IF param_statuses IS NOT NULL AND array_upper(param_statuses, 1) > 0 THEN
183 FROM asset.call_number cn
184 JOIN asset.copy cp ON (cp.call_number = cn.id)
187 AND cp.status IN ( SELECT * FROM search.explode_array( param_statuses ) )
188 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
189 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
193 -- RAISE NOTICE ' % were all status-excluded ... ', core_result.records;
194 excluded_count := excluded_count + 1;
200 IF param_locations IS NOT NULL AND array_upper(param_locations, 1) > 0 THEN
203 FROM asset.call_number cn
204 JOIN asset.copy cp ON (cp.call_number = cn.id)
207 AND cp.location IN ( SELECT * FROM search.explode_array( param_locations ) )
208 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
209 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
213 -- RAISE NOTICE ' % were all copy_location-excluded ... ', core_result.records;
214 excluded_count := excluded_count + 1;
220 IF staff IS NULL OR NOT staff THEN
223 FROM asset.call_number cn
224 JOIN asset.copy cp ON (cp.call_number = cn.id)
225 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
226 JOIN asset.copy_location cl ON (cp.location = cl.id)
227 JOIN config.copy_status cs ON (cp.status = cs.id)
234 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
235 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
239 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
240 excluded_count := excluded_count + 1;
247 FROM asset.call_number cn
248 JOIN asset.copy cp ON (cp.call_number = cn.id)
249 JOIN actor.org_unit a ON (cp.circ_lib = a.id)
250 JOIN asset.copy_location cl ON (cp.location = cl.id)
253 AND cp.circ_lib IN ( SELECT * FROM search.explode_array( search_org_list ) )
254 AND cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
260 FROM asset.call_number cn
261 WHERE cn.record IN ( SELECT * FROM search.explode_array( core_result.records ) )
265 -- RAISE NOTICE ' % were all visibility-excluded ... ', core_result.records;
266 excluded_count := excluded_count + 1;
274 visible_count := visible_count + 1;
276 current_res.id = core_result.id;
277 current_res.rel = core_result.rel;
281 SELECT COUNT(DISTINCT s.source) INTO tmp_int FROM metabib.metarecord_source_map s WHERE s.metarecord = core_result.id;
285 current_res.record = core_result.records[1];
287 current_res.record = NULL;
290 RETURN NEXT current_res;
292 IF visible_count % 1000 = 0 THEN
293 -- RAISE NOTICE ' % visible so far ... ', visible_count;
298 current_res.id = NULL;
299 current_res.rel = NULL;
300 current_res.record = NULL;
301 current_res.total = total_count;
302 current_res.checked = check_count;
303 current_res.deleted = deleted_count;
304 current_res.visible = visible_count;
305 current_res.excluded = excluded_count;
309 RETURN NEXT current_res;
312 $func$ LANGUAGE PLPGSQL;