3 SELECT evergreen.upgrade_deps_block_check('0680', :eg_version);
5 -- Not much use in having identifier-class fields be suggestions. Credit for the idea goes to Ben Shum.
6 UPDATE config.metabib_field SET browse_field = FALSE WHERE id < 100 AND field_class = 'identifier';
9 ---------------------------------------------------------------------------
10 -- The rest of this was tested on Evergreen Indiana's dev server, which has
11 -- a large data set of 2.6M bibs, and was instrumental in sussing out the
12 -- needed adjustments. Thanks, EG-IN!
13 ---------------------------------------------------------------------------
15 -- GIN indexes are /much/ better for prefix matching, which is important for browse and autosuggest
16 DROP INDEX metabib.metabib_browse_entry_index_vector_idx;
17 CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIN (index_vector);
20 -- We need thes to make the autosuggest limiting joins fast
21 CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def);
22 CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry);
23 CREATE INDEX browse_entry_def_map_source_idx ON metabib.browse_entry_def_map (source);
25 -- In practice this will always be ~1 row, and the default of 1000 causes terrible plans
26 ALTER FUNCTION metabib.search_class_to_registered_components(text) ROWS 1;
28 -- Reworking of the generated query to act in a sane manner in the face of large datasets
30 FUNCTION metabib.suggest_browse_entries(
31 raw_query_text TEXT, -- actually typed by humans at the UI level
32 search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc
33 headline_opts TEXT, -- markup options for ts_headline()
34 visibility_org INTEGER,-- null if you don't want opac visibility test
35 query_limit INTEGER,-- use in LIMIT clause of interal query
36 normalization INTEGER -- argument to TS_RANK_CD()
40 buoyant_and_class_match BOOL,
45 match TEXT -- marked up
48 prepared_query_texts TEXT[];
51 opac_visibility_join TEXT;
52 search_class_join TEXT;
55 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
57 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
58 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
60 IF visibility_org IS NOT NULL THEN
61 opac_visibility_join := '
62 JOIN asset.opac_visible_copies aovc ON (
63 aovc.record = x.source AND
64 aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
67 opac_visibility_join := '';
70 -- The following determines whether we only provide suggestsons matching
71 -- the user's selected search_class, or whether we show other suggestions
72 -- too. The reason for MIN() is that for search_classes like
73 -- 'title|proper|uniform' you would otherwise get multiple rows. The
74 -- implication is that if title as a class doesn't have restrict,
75 -- nor does the proper field, but the uniform field does, you're going
76 -- to get 'false' for your overall evaluation of 'should we restrict?'
77 -- To invert that, change from MIN() to MAX().
81 MIN(cmc.restrict::INT) AS restrict_class,
82 MIN(cmf.restrict::INT) AS restrict_field
83 FROM metabib.search_class_to_registered_components(search_class)
84 AS _registered (field_class TEXT, field INT)
86 config.metabib_class cmc ON (cmc.name = _registered.field_class)
88 config.metabib_field cmf ON (cmf.id = _registered.field);
90 -- evaluate 'should we restrict?'
91 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
92 search_class_join := '
94 metabib.search_class_to_registered_components($2)
95 AS _registered (field_class TEXT, field INT) ON (
96 (_registered.field IS NULL AND
97 _registered.field_class = cmf.field_class) OR
98 (_registered.field = cmf.id)
102 search_class_join := '
104 metabib.search_class_to_registered_components($2)
105 AS _registered (field_class TEXT, field INT) ON (
106 _registered.field_class = cmc.name
111 RETURN QUERY EXECUTE '
120 TS_HEADLINE(value, $7, $3)
121 FROM (SELECT DISTINCT
124 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
125 _registered.field = cmf.id AS restrict,
127 TS_RANK_CD(mbe.index_vector, $1, $6),
130 FROM metabib.browse_entry_def_map mbedm
132 -- Start with a pre-limited set of 10k possible suggestions. More than that is not going to be useful anyway
133 JOIN (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000) mbe ON (mbe.id = mbedm.entry)
135 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
136 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
137 ' || search_class_join || '
138 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
139 LIMIT 1000) AS x -- This outer limit makes testing for opac visibility usably fast
140 ' || opac_visibility_join || '
141 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
143 ' -- sic, repeat the order by clause in the outer select too
145 query, search_class, headline_opts,
146 visibility_org, query_limit, normalization, plain_query
150 -- buoyant AND chosen class = match class
151 -- chosen field = match field
158 $func$ LANGUAGE PLPGSQL;