Address "bouyancy" typo (should be "buoyancy")
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.autosuggest.search-normalize.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
4
5 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
6 DECLARE
7     normalizer      RECORD;
8     value           TEXT := '';
9 BEGIN
10
11     value := NEW.value;
12
13     IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
14         FOR normalizer IN
15             SELECT  n.func AS func,
16                     n.param_count AS param_count,
17                     m.params AS params
18               FROM  config.index_normalizer n
19                     JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
20               WHERE field = NEW.field AND m.pos < 0
21               ORDER BY m.pos LOOP
22                 EXECUTE 'SELECT ' || normalizer.func || '(' ||
23                     quote_literal( value ) ||
24                     CASE
25                         WHEN normalizer.param_count > 0
26                             THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
27                             ELSE ''
28                         END ||
29                     ')' INTO value;
30
31         END LOOP;
32
33         NEW.value := value;
34     END IF;
35
36     IF NEW.index_vector = ''::tsvector THEN
37         RETURN NEW;
38     END IF;
39
40     IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
41         FOR normalizer IN
42             SELECT  n.func AS func,
43                     n.param_count AS param_count,
44                     m.params AS params
45               FROM  config.index_normalizer n
46                     JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
47               WHERE field = NEW.field AND m.pos >= 0
48               ORDER BY m.pos LOOP
49                 EXECUTE 'SELECT ' || normalizer.func || '(' ||
50                     quote_literal( value ) ||
51                     CASE
52                         WHEN normalizer.param_count > 0
53                             THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
54                             ELSE ''
55                         END ||
56                     ')' INTO value;
57
58         END LOOP;
59     END IF;
60
61     IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
62         value :=  ARRAY_TO_STRING(
63             evergreen.regexp_split_to_array(value, E'\\W+'), ' '
64         );
65         value := public.search_normalize(value);
66     END IF;
67
68     NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
69
70     RETURN NEW;
71 END;
72 $$ LANGUAGE PLPGSQL;
73
74 -- Given a string such as a user might type into a search box, prepare
75 -- two changed variants for TO_TSQUERY(). See
76 -- http://www.postgresql.org/docs/9.0/static/textsearch-controls.html
77 -- The first variant is normalized to match indexed documents regardless
78 -- of diacritics.  The second variant keeps its diacritics for proper
79 -- highlighting via TS_HEADLINE().
80 CREATE OR REPLACE
81     FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
82 $$
83 DECLARE
84     orig_ended_in_space     BOOLEAN;
85     result                  RECORD;
86     plain                   TEXT;
87     normalized              TEXT;
88 BEGIN
89     orig_ended_in_space := orig ~ E'\\s$';
90
91     orig := ARRAY_TO_STRING(
92         evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
93     );
94
95     normalized := public.search_normalize(orig); -- also trim()s
96     plain := trim(orig);
97
98     IF NOT orig_ended_in_space THEN
99         plain := plain || ':*';
100         normalized := normalized || ':*';
101     END IF;
102
103     plain := ARRAY_TO_STRING(
104         evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
105     );
106     normalized := ARRAY_TO_STRING(
107         evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
108     );
109
110     RETURN ARRAY[normalized, plain];
111 END;
112 $$ LANGUAGE PLPGSQL;
113
114
115 -- Definition of OUT parameters changes, so must drop first
116 DROP FUNCTION IF EXISTS metabib.suggest_browse_entries (TEXT, TEXT, TEXT, INTEGER, INTEGER, INTEGER);
117
118 CREATE OR REPLACE
119     FUNCTION metabib.suggest_browse_entries(
120         raw_query_text  TEXT,   -- actually typed by humans at the UI level
121         search_class    TEXT,   -- 'alias' or 'class' or 'class|field..', etc
122         headline_opts   TEXT,   -- markup options for ts_headline()
123         visibility_org  INTEGER,-- null if you don't want opac visibility test
124         query_limit     INTEGER,-- use in LIMIT clause of interal query
125         normalization   INTEGER -- argument to TS_RANK_CD()
126     ) RETURNS TABLE (
127         value                   TEXT,   -- plain
128         field                   INTEGER,
129         buoyant_and_class_match BOOL,
130         field_match             BOOL,
131         field_weight            INTEGER,
132         rank                    REAL,
133         buoyant                 BOOL,
134         match                   TEXT    -- marked up
135     ) AS $func$
136 DECLARE
137     prepared_query_texts    TEXT[];
138     query                   TSQUERY;
139     plain_query             TSQUERY;
140     opac_visibility_join    TEXT;
141     search_class_join       TEXT;
142     r_fields                RECORD;
143 BEGIN
144     prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
145
146     query := TO_TSQUERY('keyword', prepared_query_texts[1]);
147     plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
148
149     IF visibility_org IS NOT NULL THEN
150         opac_visibility_join := '
151     JOIN asset.opac_visible_copies aovc ON (
152         aovc.record = mbedm.source AND
153         aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
154     )';
155     ELSE
156         opac_visibility_join := '';
157     END IF;
158
159     -- The following determines whether we only provide suggestsons matching
160     -- the user's selected search_class, or whether we show other suggestions
161     -- too. The reason for MIN() is that for search_classes like
162     -- 'title|proper|uniform' you would otherwise get multiple rows.  The
163     -- implication is that if title as a class doesn't have restrict,
164     -- nor does the proper field, but the uniform field does, you're going
165     -- to get 'false' for your overall evaluation of 'should we restrict?'
166     -- To invert that, change from MIN() to MAX().
167
168     SELECT
169         INTO r_fields
170             MIN(cmc.restrict::INT) AS restrict_class,
171             MIN(cmf.restrict::INT) AS restrict_field
172         FROM metabib.search_class_to_registered_components(search_class)
173             AS _registered (field_class TEXT, field INT)
174         JOIN
175             config.metabib_class cmc ON (cmc.name = _registered.field_class)
176         LEFT JOIN
177             config.metabib_field cmf ON (cmf.id = _registered.field);
178
179     -- evaluate 'should we restrict?'
180     IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
181         search_class_join := '
182     JOIN
183         metabib.search_class_to_registered_components($2)
184         AS _registered (field_class TEXT, field INT) ON (
185             (_registered.field IS NULL AND
186                 _registered.field_class = cmf.field_class) OR
187             (_registered.field = cmf.id)
188         )
189     ';
190     ELSE
191         search_class_join := '
192     LEFT JOIN
193         metabib.search_class_to_registered_components($2)
194         AS _registered (field_class TEXT, field INT) ON (
195             _registered.field_class = cmc.name
196         )
197     ';
198     END IF;
199
200     RETURN QUERY EXECUTE 'SELECT *, TS_HEADLINE(value, $7, $3) FROM (SELECT DISTINCT
201         mbe.value,
202         cmf.id,
203         cmc.buoyant AND _registered.field_class IS NOT NULL,
204         _registered.field = cmf.id,
205         cmf.weight,
206         TS_RANK_CD(mbe.index_vector, $1, $6),
207         cmc.buoyant
208     FROM metabib.browse_entry_def_map mbedm
209     JOIN metabib.browse_entry mbe ON (mbe.id = mbedm.entry)
210     JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
211     JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
212     '  || search_class_join || opac_visibility_join ||
213     ' WHERE $1 @@ mbe.index_vector
214     ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
215     LIMIT $5) x
216     ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
217     '   -- sic, repeat the order by clause in the outer select too
218     USING
219         query, search_class, headline_opts,
220         visibility_org, query_limit, normalization, plain_query
221         ;
222
223     -- sort order:
224     --  buoyant AND chosen class = match class
225     --  chosen field = match field
226     --  field weight
227     --  rank
228     --  buoyancy
229     --  value itself
230
231 END;
232 $func$ LANGUAGE PLPGSQL;
233
234
235 \qecho 
236 \qecho The following takes about a minute per 100,000 rows in
237 \qecho metabib.browse_entry on my development system, which is only a VM with
238 \qecho 4 GB of memory and 2 cores.
239 \qecho 
240 \qecho The following is a very loose estimate of how long the next UPDATE
241 \qecho statement would take to finish on MY machine, based on YOUR number
242 \qecho of rows in metabib.browse_entry:
243 \qecho 
244
245 SELECT (COUNT(id) / 100000.0) * INTERVAL '1 minute'
246     AS "approximate duration of following UPDATE statement"
247     FROM metabib.browse_entry;
248
249 UPDATE metabib.browse_entry SET index_vector = TO_TSVECTOR(
250     'keyword',
251     public.search_normalize(
252         ARRAY_TO_STRING(
253             evergreen.regexp_split_to_array(value, E'\\W+'), ' '
254         )
255     )
256 );
257
258 COMMIT;