Open-ILS/src/sql/Pg/upgrade/0679.schema.autosuggest.search-normalize.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0679', :eg_version);
   4
   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;
   9
  10 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
  11 DECLARE
  12     normalizer      RECORD;
  13     value           TEXT := '';
  14 BEGIN
  15
  16     value := NEW.value;
  17
  18     IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
  19         FOR normalizer IN
  20             SELECT  n.func AS func,
  21                     n.param_count AS param_count,
  22                     m.params AS params
  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
  26               ORDER BY m.pos LOOP
  27                 EXECUTE 'SELECT ' || normalizer.func || '(' ||
  28                     quote_literal( value ) ||
  29                     CASE
  30                         WHEN normalizer.param_count > 0
  31                             THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
  32                             ELSE ''
  33                         END ||
  34                     ')' INTO value;
  35
  36         END LOOP;
  37
  38         NEW.value := value;
  39     END IF;
  40
  41     IF NEW.index_vector = ''::tsvector THEN
  42         RETURN NEW;
  43     END IF;
  44
  45     IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
  46         FOR normalizer IN
  47             SELECT  n.func AS func,
  48                     n.param_count AS param_count,
  49                     m.params AS params
  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
  53               ORDER BY m.pos LOOP
  54                 EXECUTE 'SELECT ' || normalizer.func || '(' ||
  55                     quote_literal( value ) ||
  56                     CASE
  57                         WHEN normalizer.param_count > 0
  58                             THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
  59                             ELSE ''
  60                         END ||
  61                     ')' INTO value;
  62
  63         END LOOP;
  64     END IF;
  65
  66     IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
  67         value :=  ARRAY_TO_STRING(
  68             evergreen.regexp_split_to_array(value, E'\\W+'), ' '
  69         );
  70         value := public.search_normalize(value);
  71     END IF;
  72
  73     NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
  74
  75     RETURN NEW;
  76 END;
  77 $$ LANGUAGE PLPGSQL;
  78
  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().
  85 CREATE OR REPLACE
  86     FUNCTION metabib.autosuggest_prepare_tsquery(orig TEXT) RETURNS TEXT[] AS
  87 $$
  88 DECLARE
  89     orig_ended_in_space     BOOLEAN;
  90     result                  RECORD;
  91     plain                   TEXT;
  92     normalized              TEXT;
  93 BEGIN
  94     orig_ended_in_space := orig ~ E'\\s$';
  95
  96     orig := ARRAY_TO_STRING(
  97         evergreen.regexp_split_to_array(orig, E'\\W+'), ' '
  98     );
  99
 100     normalized := public.search_normalize(orig); -- also trim()s
 101     plain := trim(orig);
 102
 103     IF NOT orig_ended_in_space THEN
 104         plain := plain || ':*';
 105         normalized := normalized || ':*';
 106     END IF;
 107
 108     plain := ARRAY_TO_STRING(
 109         evergreen.regexp_split_to_array(plain, E'\\s+'), ' & '
 110     );
 111     normalized := ARRAY_TO_STRING(
 112         evergreen.regexp_split_to_array(normalized, E'\\s+'), ' & '
 113     );
 114
 115     RETURN ARRAY[normalized, plain];
 116 END;
 117 $$ LANGUAGE PLPGSQL;
 118
 119
 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);
 122
 123 CREATE OR REPLACE
 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()
 131     ) RETURNS TABLE (
 132         value                   TEXT,   -- plain
 133         field                   INTEGER,
 134         buoyant_and_class_match BOOL,
 135         field_match             BOOL,
 136         field_weight            INTEGER,
 137         rank                    REAL,
 138         buoyant                 BOOL,
 139         match                   TEXT    -- marked up
 140     ) AS $func$
 141 DECLARE
 142     prepared_query_texts    TEXT[];
 143     query                   TSQUERY;
 144     plain_query             TSQUERY;
 145     opac_visibility_join    TEXT;
 146     search_class_join       TEXT;
 147     r_fields                RECORD;
 148 BEGIN
 149     prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
 150
 151     query := TO_TSQUERY('keyword', prepared_query_texts[1]);
 152     plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
 153
 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))
 159     )';
 160     ELSE
 161         opac_visibility_join := '';
 162     END IF;
 163
 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().
 172
 173     SELECT
 174         INTO r_fields
 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)
 179         JOIN
 180             config.metabib_class cmc ON (cmc.name = _registered.field_class)
 181         LEFT JOIN
 182             config.metabib_field cmf ON (cmf.id = _registered.field);
 183
 184     -- evaluate 'should we restrict?'
 185     IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
 186         search_class_join := '
 187     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)
 193         )
 194     ';
 195     ELSE
 196         search_class_join := '
 197     LEFT JOIN
 198         metabib.search_class_to_registered_components($2)
 199         AS _registered (field_class TEXT, field INT) ON (
 200             _registered.field_class = cmc.name
 201         )
 202     ';
 203     END IF;
 204
 205     RETURN QUERY EXECUTE 'SELECT *, TS_HEADLINE(value, $7, $3) FROM (SELECT DISTINCT
 206         mbe.value,
 207         cmf.id,
 208         cmc.buoyant AND _registered.field_class IS NOT NULL,
 209         _registered.field = cmf.id,
 210         cmf.weight,
 211         TS_RANK_CD(mbe.index_vector, $1, $6),
 212         cmc.buoyant
 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
 220     LIMIT $5) x
 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
 223     USING
 224         query, search_class, headline_opts,
 225         visibility_org, query_limit, normalization, plain_query
 226         ;
 227
 228     -- sort order:
 229     --  buoyant AND chosen class = match class
 230     --  chosen field = match field
 231     --  field weight
 232     --  rank
 233     --  buoyancy
 234     --  value itself
 235
 236 END;
 237 $func$ LANGUAGE PLPGSQL;
 238
 239
 240 \qecho
 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.
 244 \qecho
 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:
 248 \qecho
 249
 250 SELECT (COUNT(id) / 100000.0) * INTERVAL '1 minute'
 251     AS "approximate duration of following UPDATE statement"
 252     FROM metabib.browse_entry;
 253
 254 UPDATE metabib.browse_entry SET index_vector = TO_TSVECTOR(
 255     'keyword',
 256     public.search_normalize(
 257         ARRAY_TO_STRING(
 258             evergreen.regexp_split_to_array(value, E'\\W+'), ' '
 259         )
 260     )
 261 );
 262
 263 COMMIT;