3 SELECT evergreen.upgrade_deps_block_check('0679', :eg_version);
5 -- Address typo in column name
6 ALTER TABLE config.metabib_class ADD COLUMN buoyant BOOL DEFAULT FALSE NOT NULL;
7 UPDATE config.metabib_class SET buoyant = bouyant;
8 ALTER TABLE config.metabib_class DROP COLUMN bouyant;
10 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
18 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
20 SELECT n.func AS func,
21 n.param_count AS param_count,
23 FROM config.index_normalizer n
24 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
25 WHERE field = NEW.field AND m.pos < 0
27 EXECUTE 'SELECT ' || normalizer.func || '(' ||
28 quote_literal( value ) ||
30 WHEN normalizer.param_count > 0
31 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
41 IF NEW.index_vector = ''::tsvector THEN
45 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
47 SELECT n.func AS func,
48 n.param_count AS param_count,
50 FROM config.index_normalizer n
51 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
52 WHERE field = NEW.field AND m.pos >= 0
54 EXECUTE 'SELECT ' || normalizer.func || '(' ||
55 quote_literal( value ) ||
57 WHEN normalizer.param_count > 0
58 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
66 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
67 value := ARRAY_TO_STRING(
68 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
70 value := public.search_normalize(value);
73 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
79 -- Given a string such as a user might type into a search box, prepare
80 -- two changed variants for TO_TSQUERY(). See
81 -- http://www.postgresql.org/docs/9.0/static/textsearch-controls.html
82 -- The first variant is normalized to match indexed documents regardless
83 -- of diacritics. The second variant keeps its diacritics for proper
84 -- highlighting via TS_HEADLINE().
86 FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
89 orig_ended_in_space BOOLEAN;
94 orig_ended_in_space := orig ~ E'\\s$';
96 orig := ARRAY_TO_STRING(
97 evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
100 normalized := public.search_normalize(orig); -- also trim()s
103 IF NOT orig_ended_in_space THEN
104 plain := plain || ':*';
105 normalized := normalized || ':*';
108 plain := ARRAY_TO_STRING(
109 evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
111 normalized := ARRAY_TO_STRING(
112 evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
115 RETURN ARRAY[normalized, plain];
120 -- Definition of OUT parameters changes, so must drop first
121 DROP FUNCTION IF EXISTS metabib.suggest_browse_entries (TEXT, TEXT, TEXT, INTEGER, INTEGER, INTEGER);
124 FUNCTION metabib.suggest_browse_entries(
125 raw_query_text TEXT, -- actually typed by humans at the UI level
126 search_class TEXT, -- 'alias' or 'class' or 'class|field..', etc
127 headline_opts TEXT, -- markup options for ts_headline()
128 visibility_org INTEGER,-- null if you don't want opac visibility test
129 query_limit INTEGER,-- use in LIMIT clause of interal query
130 normalization INTEGER -- argument to TS_RANK_CD()
134 buoyant_and_class_match BOOL,
136 field_weight INTEGER,
139 match TEXT -- marked up
142 prepared_query_texts TEXT[];
145 opac_visibility_join TEXT;
146 search_class_join TEXT;
149 prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
151 query := TO_TSQUERY('keyword', prepared_query_texts[1]);
152 plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
154 IF visibility_org IS NOT NULL THEN
155 opac_visibility_join := '
156 JOIN asset.opac_visible_copies aovc ON (
157 aovc.record = mbedm.source AND
158 aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
161 opac_visibility_join := '';
164 -- The following determines whether we only provide suggestsons matching
165 -- the user's selected search_class, or whether we show other suggestions
166 -- too. The reason for MIN() is that for search_classes like
167 -- 'title|proper|uniform' you would otherwise get multiple rows. The
168 -- implication is that if title as a class doesn't have restrict,
169 -- nor does the proper field, but the uniform field does, you're going
170 -- to get 'false' for your overall evaluation of 'should we restrict?'
171 -- To invert that, change from MIN() to MAX().
175 MIN(cmc.restrict::INT) AS restrict_class,
176 MIN(cmf.restrict::INT) AS restrict_field
177 FROM metabib.search_class_to_registered_components(search_class)
178 AS _registered (field_class TEXT, field INT)
180 config.metabib_class cmc ON (cmc.name = _registered.field_class)
182 config.metabib_field cmf ON (cmf.id = _registered.field);
184 -- evaluate 'should we restrict?'
185 IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
186 search_class_join := '
188 metabib.search_class_to_registered_components($2)
189 AS _registered (field_class TEXT, field INT) ON (
190 (_registered.field IS NULL AND
191 _registered.field_class = cmf.field_class) OR
192 (_registered.field = cmf.id)
196 search_class_join := '
198 metabib.search_class_to_registered_components($2)
199 AS _registered (field_class TEXT, field INT) ON (
200 _registered.field_class = cmc.name
205 RETURN QUERY EXECUTE 'SELECT *, TS_HEADLINE(value, $7, $3) FROM (SELECT DISTINCT
208 cmc.buoyant AND _registered.field_class IS NOT NULL,
209 _registered.field = cmf.id,
211 TS_RANK_CD(mbe.index_vector, $1, $6),
213 FROM metabib.browse_entry_def_map mbedm
214 JOIN metabib.browse_entry mbe ON (mbe.id = mbedm.entry)
215 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
216 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
217 ' || search_class_join || opac_visibility_join ||
218 ' WHERE $1 @@ mbe.index_vector
219 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
221 ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
222 ' -- sic, repeat the order by clause in the outer select too
224 query, search_class, headline_opts,
225 visibility_org, query_limit, normalization, plain_query
229 -- buoyant AND chosen class = match class
230 -- chosen field = match field
237 $func$ LANGUAGE PLPGSQL;
241 \qecho The following takes about a minute per 100,000 rows in
242 \qecho metabib.browse_entry on my development system, which is only a VM with
243 \qecho 4 GB of memory and 2 cores.
245 \qecho The following is a very loose estimate of how long the next UPDATE
246 \qecho statement would take to finish on MY machine, based on YOUR number
247 \qecho of rows in metabib.browse_entry:
250 SELECT (COUNT(id) / 100000.0) * INTERVAL '1 minute'
251 AS "approximate duration of following UPDATE statement"
252 FROM metabib.browse_entry;
254 UPDATE metabib.browse_entry SET index_vector = TO_TSVECTOR(
256 public.search_normalize(
258 evergreen.regexp_split_to_array(value, E'\\W+'), ' '