From 16e6fdc1344d46d47cb117522b1c7762cdac9a7c Mon Sep 17 00:00:00 2001 From: Lebbeous Fogle-Weekley Date: Thu, 12 Jan 2012 17:56:24 -0500 Subject: [PATCH] stored procedure for autosuggest take: query string, like 'foo' or 'foo & ba:*', ready for to_tsquery() search_class (class|field|field...) markup options for ts_headline() optional org unit for opac visibility test return: record id marked up matched value metabib field where found rank search_class bouyancy I think this is mainly Right, except for the ranking/sorting, which I will revisit. But aside from sorting, the results of this function make sense to me. Signed-off-by: Lebbeous Fogle-Weekley --- .../sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql | 134 +++++++++++++++++++++ 1 file changed, 134 insertions(+) diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql index 11b613c448..730b089ef5 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.bib_autosuggest.sql @@ -259,4 +259,138 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; +-- This mimics a specific part of QueryParser, turning the first part of a +-- classed search (search_class) into a set of classes and possibly fields. +-- search_class might look like "author" or "title|proper" or "ti|uniform" +-- or "au" or "au|corporate|personal" or anything like that, where the first +-- element of the list you get by separating on the "|" character is either +-- a registered class (config.metabib_class) or an alias +-- (config.metabib_search_alias), and the rest of any such elements are +-- fields (config.metabib_field). +CREATE OR REPLACE + FUNCTION metabib.search_class_to_registered_components(search_class TEXT) + RETURNS SETOF RECORD AS $func$ +DECLARE + search_parts TEXT[]; + field_name TEXT; + search_part_count INTEGER; + rec RECORD; + registered_class config.metabib_class%ROWTYPE; + registered_alias config.metabib_search_alias%ROWTYPE; + registered_field config.metabib_field%ROWTYPE; +BEGIN + search_parts := REGEXP_SPLIT_TO_ARRAY(search_class, E'\\|'); + + search_part_count := ARRAY_LENGTH(search_parts, 1); + IF search_part_count = 0 THEN + RETURN; + ELSE + SELECT INTO registered_class + * FROM config.metabib_class WHERE name = search_parts[1]; + IF FOUND THEN + IF search_part_count < 2 THEN -- all fields + rec := (registered_class.name, NULL::INTEGER); + RETURN NEXT rec; + RETURN; -- done + END IF; + FOR field_name IN SELECT * + FROM UNNEST(search_parts[2:search_part_count]) LOOP + SELECT INTO registered_field + * FROM config.metabib_field + WHERE name = field_name AND + field_class = registered_class.name; + IF FOUND THEN + rec := (registered_class.name, registered_field.id); + RETURN NEXT rec; + END IF; + END LOOP; + ELSE + -- maybe we have an alias? + SELECT INTO registered_alias + * FROM config.metabib_search_alias WHERE alias=search_parts[1]; + IF NOT FOUND THEN + RETURN; + ELSE + IF search_part_count < 2 THEN -- return w/e the alias says + rec := ( + registered_alias.field_class, registered_alias.field + ); + RETURN NEXT rec; + RETURN; -- done + ELSE + FOR field_name IN SELECT * + FROM UNNEST(search_parts[2:search_part_count]) LOOP + SELECT INTO registered_field + * FROM config.metabib_field + WHERE name = field_name AND + field_class = registered_alias.field_class; + IF FOUND THEN + rec := ( + registered_alias.field_class, + registered_field.id + ); + RETURN NEXT rec; + END IF; + END LOOP; + END IF; + END IF; + END IF; + END IF; +END; +$func$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE + FUNCTION metabib.browse_biblio_record_entry( + query_text TEXT, -- 'foo' or 'foo & ba:*',ready for to_tsquery() + search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc + headline_opts TEXT, -- markup options for ts_headline() + visibility_org INTEGER -- null if you don't want opac visibility test + ) RETURNS TABLE ( + record BIGINT, + match TEXT, -- marked up + field INTEGER, + rank REAL, + bouyant BOOL + ) AS $func$ +DECLARE + query TSQUERY; + opac_visibility_join TEXT; +BEGIN + query := TO_TSQUERY(query_text); + + IF visibility_org IS NOT NULL THEN + opac_visibility_join := ' + JOIN asset.opac_visible_copies aovc ON ( + aovc.record = mbedm.source AND + aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4)) + )'; + ELSE + opac_visibility_join := ''; + END IF; + + RETURN QUERY EXECUTE 'SELECT DISTINCT + mbedm.source, + TS_HEADLINE(mbe.value, $1, $3), + cmf.id, + TS_RANK_CD(mbe.index_vector, $1), + cmc.bouyant + FROM metabib.browse_entry_def_map mbedm + JOIN metabib.browse_entry mbe ON (mbe.id = mbedm.entry) + JOIN config.metabib_field cmf ON (cmf.id = mbedm.def) + JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name) + JOIN + metabib.search_class_to_registered_components($2) + AS _registered (field_class TEXT, field INT) ON ( + (_registered.field IS NULL AND + _registered.field_class = cmf.field_class) OR + (_registered.field = cmf.id) + )' || opac_visibility_join || ' + WHERE $1 @@ mbe.index_vector + ORDER BY 4, 5 DESC + ' USING query, search_class, headline_opts, visibility_org; + +END; +$func$ LANGUAGE PLPGSQL; + COMMIT; -- 2.11.0