From aa67259b9078efe7b71bab0a4e082d2f6e1d9dd7 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 23 Sep 2011 15:57:58 -0400 Subject: [PATCH] Provide a full suite of search/browse top/center alpha/rank functions based on authority tag, bib tag or browse axis Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/011.schema.authority.sql | 266 ++++++++++++++++++++++----- 1 file changed, 216 insertions(+), 50 deletions(-) diff --git a/Open-ILS/src/sql/Pg/011.schema.authority.sql b/Open-ILS/src/sql/Pg/011.schema.authority.sql index 13955cfcf8..15c1a9b93b 100644 --- a/Open-ILS/src/sql/Pg/011.schema.authority.sql +++ b/Open-ILS/src/sql/Pg/011.schema.authority.sql @@ -132,7 +132,7 @@ CREATE INDEX authority_full_rec_tag_part_idx ON authority.full_rec (SUBSTRING(ta CREATE INDEX authority_full_rec_subfield_a_idx ON authority.full_rec (value) WHERE subfield = 'a'; CREATE TRIGGER authority_full_rec_fti_trigger BEFORE UPDATE OR INSERT ON authority.full_rec - FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); CREATE INDEX authority_full_rec_index_vector_idx ON authority.full_rec USING GIST (index_vector); /* Enable LIKE to use an index for database clusters with locales other than C or POSIX */ @@ -233,7 +233,7 @@ CREATE TABLE authority.simple_heading ( ); CREATE TRIGGER authority_simple_heading_fti_trigger BEFORE UPDATE OR INSERT ON authority.simple_heading - FOR EACH ROW EXECUTE PROCEDURE tsearch2(index_vector, value); + FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('keyword'); CREATE INDEX authority_simple_heading_index_vector_idx ON authority.full_rec USING GIST (index_vector); CREATE INDEX authority_simple_heading_value_idx ON authority.simple_heading (value); @@ -328,54 +328,6 @@ index to defend against duplicated authority records from the same thesaurus. $$; -CREATE OR REPLACE FUNCTION authority.find_start_pivot( a TEXT, q TEXT, lim INT DEFAULT 10, offs INT DEFAULT 0 ) RETURNS TABLE (rank NUMERIC, id BIGINT, record BIGINT, atag INT, value TEXT, sort_value TEXT) AS $$ -BEGIN - RETURN QUERY - SELECT ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric - + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END - + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END, - ash.id, - ash.record, - ash.atag, - ash.value, - ash.sort_value - FROM authority.simple_heading ash - JOIN authority.control_set_authority_field acsaf ON (acsaf.id = ash.atag) - JOIN authority.browse_axis_authority_field_map abaafm ON (abaafm.field = acsaf.id), - public.naco_normalize(q) t(term), - plainto_tsquery(q) ptsq(term) - WHERE abaafm.axis = a - AND ash.sort_value >= t.term OR ash.value >= t.term - ORDER BY ash.sort_value, 1 DESC - LIMIT lim - OFFSET offs; -END; -$$ LANGUAGE PLPGSQL ROWS 10; - -CREATE OR REPLACE FUNCTION authority.find_search_pivot( a TEXT, q TEXT, lim INT DEFAULT 10, offs INT DEFAULT 0 ) RETURNS TABLE (rank NUMERIC, id BIGINT, record BIGINT, atag INT, value TEXT, sort_value TEXT) AS $$ -BEGIN - RETURN QUERY - SELECT ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric - + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END - + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END, - ash.id, - ash.record, - ash.atag, - ash.value, - ash.sort_value - FROM authority.simple_heading ash - JOIN authority.control_set_authority_field acsaf ON (acsaf.id = ash.atag) - JOIN authority.browse_axis_authority_field_map abaafm ON (abaafm.field = acsaf.id), - public.naco_normalize(q) t(term), - plainto_tsquery(q) ptsq(term) - WHERE abaafm.axis = a - AND ash.index_vector @@ ptsq.term - ORDER BY 1 DESC, ash.sort_value - LIMIT lim - OFFSET offs; -END; -$$ LANGUAGE PLPGSQL ROWS 10; - -- Adding indexes using oils_xpath_string() for the main entry tags described in -- authority.control_set_authority_field would speed this up, if we ever want to use it, though -- the existing index on authority.normalize_heading() helps already with a record in hand @@ -551,4 +503,218 @@ BEGIN END; $func$ LANGUAGE plpgsql; + +-- Support function used to find the pivot for alpha-heading-browse style searching +CREATE OR REPLACE FUNCTION authority.simple_heading_find_pivot( a INT[], q TEXT ) RETURNS TEXT AS $$ +DECLARE + sort_value_row RECORD; + value_row RECORD; + t_term TEXT; +BEGIN + + t_term := public.naco_normalize(q); + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO sort_value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.sort_value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + SELECT CASE WHEN ash.sort_value LIKE t_term || '%' THEN 1 ELSE 0 END + + CASE WHEN ash.value LIKE t_term || '%' THEN 1 ELSE 0 END AS rank, + ash.sort_value + INTO value_row + FROM authority.simple_heading ash + WHERE ash.atag = ANY (a) + AND ash.value >= t_term + ORDER BY rank DESC, ash.sort_value + LIMIT 1; + + IF value_row.rank > sort_value_row.rank THEN + RETURN value_row.sort_value; + ELSE + RETURN sort_value_row.sort_value; + END IF; +END; +$$ LANGUAGE PLPGSQL; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_center( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; + boffset INT DEFAULT 0; + aoffset INT DEFAULT 0; + blimit INT DEFAULT 0; + alimit INT DEFAULT 0; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page = 0 THEN + blimit := pagesize / 2; + alimit := blimit; + + IF pagesize % 2 <> 0 THEN + alimit := alimit + 1; + END IF; + ELSE + blimit := pagesize; + alimit := blimit; + + boffset := pagesize / 2; + aoffset := boffset; + + IF pagesize % 2 <> 0 THEN + boffset := boffset + 1; + END IF; + END IF; + + IF page <= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT blimit + OFFSET ABS(page) * pagesize - boffset + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT alimit + OFFSET ABS(page) * pagesize - aoffset; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_center( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 9 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_center((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_browse_top( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ +DECLARE + pivot_sort_value TEXT; +BEGIN + + pivot_sort_value := authority.simple_heading_find_pivot(atag_list,q); + + IF page < 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT id FROM ( + SELECT ash.id, + row_number() over () + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value < pivot_sort_value + ORDER BY ash.sort_value DESC + LIMIT pagesize + OFFSET (ABS(page) - 1) * pagesize + ) x ORDER BY row_number DESC; + END IF; + + IF page >= 0 THEN + RETURN QUERY + -- "bottom" half of the browse results + SELECT ash.id + FROM authority.simple_heading ash + WHERE ash.atag = ANY (atag_list) + AND ash.sort_value >= pivot_sort_value + ORDER BY ash.sort_value + LIMIT pagesize + OFFSET ABS(page) * pagesize ; + END IF; +END; +$$ LANGUAGE PLPGSQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_browse_top( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_browse_top((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_rank( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ts_rank_cd(ash.index_vector,ptsq.term,14)::numeric + + CASE WHEN ash.sort_value LIKE t.term || '%' THEN 2 ELSE 0 END + + CASE WHEN ash.value LIKE t.term || '%' THEN 1 ELSE 0 END DESC + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_rank( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_rank((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + +CREATE OR REPLACE FUNCTION authority.simple_heading_search_heading( atag_list INT[], q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT ash.id + FROM authority.simple_heading ash, + public.naco_normalize($2) t(term), + plainto_tsquery('keyword'::regconfig,$2) ptsq(term) + WHERE ash.atag = ANY ($1) + AND ash.index_vector @@ ptsq.term + ORDER BY ash.sort_value + LIMIT $4 + OFFSET $4 * $3; +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.axis_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(field) FROM authority.browse_axis_authority_field_map WHERE axis = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.btag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(authority_field) FROM authority.control_set_bib_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + +CREATE OR REPLACE FUNCTION authority.atag_search_heading( a TEXT, q TEXT, page INT DEFAULT 0, pagesize INT DEFAULT 10 ) RETURNS SETOF BIGINT AS $$ + SELECT * FROM authority.simple_heading_search_heading((SELECT ARRAY_ACCUM(id) FROM authority.control_set_authority_field WHERE tag = $1), $2, $3, $4) +$$ LANGUAGE SQL ROWS 10; + + COMMIT; + -- 2.11.0