Schema Changes: 3.15.12
Upgrade: 3.15.11 → 3.15.12
This release applied 2 migration(s) to the database schema.
| ID | Type | Description |
|---|---|---|
function |
correct volatility |
|
schema |
lp2004058 fix sql injections |
Migration Details
1518 — correct volatility
Type: function
View SQL
ALTER FUNCTION actor.org_unit_ancestors(INT) STABLE;
ALTER FUNCTION actor.org_unit_descendants(INT) STABLE;
ALTER FUNCTION actor.org_unit_descendants(INT,INT) STABLE;1519 — lp2004058 fix sql injections
Type: schema
View SQL
CREATE OR REPLACE FUNCTION acq.create_acq_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$CREATE SEQUENCE acq.$$ || quote_ident(sch || $$_$$ || tbl || $$_pkey_seq$$);
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION acq.create_acq_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE TABLE acq.$$ || sch || $$_$$ || tbl || $$_history (
audit_id BIGINT PRIMARY KEY,
audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
audit_action TEXT NOT NULL,
LIKE $$ || quote_ident(sch) || $$.$$ || quote_ident(tbl) || $$
);
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION acq.create_acq_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE OR REPLACE FUNCTION acq.audit_$$ || quote_ident(sch || $$_$$ || tbl || $$_func$$) || $$ ()
RETURNS TRIGGER AS $func$
INSERT INTO acq.$$ || quote_ident(sch || $$_$$ || tbl || $$_history$$) || $$
SELECT nextval($$ || quote_literal($$acq.$$ || sch || $$_$$ || tbl || $$_pkey_seq$$) || $$),
now(),
SUBSTR(TG_OP,1,1),
OLD.*;
RETURN NULL;
END;
$func$ LANGUAGE 'plpgsql';
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION acq.create_acq_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE TRIGGER audit_$$ || quote_ident(sch || $$_$$ || tbl || $$_update_trigger$$) || $$
AFTER UPDATE OR DELETE ON $$ || quote_ident(sch) || $$.$$ || quote_ident(tbl) || $$ FOR EACH ROW
EXECUTE PROCEDURE acq.audit_$$ || quote_ident(sch || $$_$$ || tbl || $$_func$$) || $$ ();
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION acq.create_acq_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE OR REPLACE VIEW acq.$$ || quote_ident(sch || $$_$$ || tbl || $$_lifecycle$$) || $$ AS
SELECT -1, now() as audit_time, '-' as audit_action, *
FROM $$ || sch || $$.$$ || tbl || $$
UNION ALL
SELECT *
FROM acq.$$ || quote_ident(sch || $$_$$ || tbl || $$_history$$) || $$;
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION auditor.create_auditor_seq ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$CREATE SEQUENCE auditor.$$ || quote_ident(sch || $$_$$ || tbl || $$_pkey_seq$$);
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE TABLE auditor.$$ || quote_ident(sch || $$_$$ || tbl || $$_history$$) || $$ (
audit_id BIGINT PRIMARY KEY,
audit_time TIMESTAMP WITH TIME ZONE NOT NULL,
audit_action TEXT NOT NULL,
audit_user INT,
audit_ws INT,
LIKE $$ || quote_ident(sch) || $$.$$ || quote_ident(tbl) || $$
);
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION auditor.create_auditor_func ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
DECLARE
column_list TEXT[];
SELECT INTO column_list array_agg(a.attname)
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
EXECUTE $$
CREATE OR REPLACE FUNCTION auditor.$$ || quote_ident($$audit_$$ || sch || $$_$$ || tbl || $$_func$$) || $$ ()
RETURNS TRIGGER AS $func$
INSERT INTO auditor.$$ || quote_ident(sch || $$_$$ || tbl || $$_history$$) || $$ ( audit_id, audit_time, audit_action, audit_user, audit_ws, $$
|| array_to_string(column_list, ', ') || $$ )
SELECT nextval($$ || quote_literal($$auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq$$) || $$),
now(),
SUBSTR(TG_OP,1,1),
eg_user,
eg_ws,
OLD.$$ || array_to_string(column_list, ', OLD.') || $$
FROM auditor.get_audit_info();
RETURN NULL;
END;
$func$ LANGUAGE 'plpgsql';
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION auditor.create_auditor_update_trigger ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
EXECUTE $$
CREATE TRIGGER $$ || quote_ident($$audit_$$ || sch || $$_$$ || tbl || $$_update_trigger$$) || $$
AFTER UPDATE OR DELETE ON $$ || quote_ident(sch) || $$.$$ || quote_ident(tbl) || $$ FOR EACH ROW
EXECUTE PROCEDURE auditor.$$ || quote_ident($$audit_$$ || sch || $$_$$ || tbl || $$_func$$) || $$ ();
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION auditor.create_auditor_lifecycle ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
DECLARE
column_list TEXT[];
SELECT INTO column_list array_agg(a.attname)
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
EXECUTE $$
CREATE VIEW auditor.$$ || quote_ident(sch || $$_$$ || tbl || $$_lifecycle$$) || $$ AS
SELECT -1 AS audit_id,
now() AS audit_time,
'-' AS audit_action,
-1 AS audit_user,
-1 AS audit_ws,
$$ || array_to_string(column_list, ', ') || $$
FROM $$ || quote_ident(sch) || $$.$$ || quote_ident(tbl) || $$
UNION ALL
SELECT audit_id, audit_time, audit_action, audit_user, audit_ws,
$$ || array_to_string(column_list, ', ') || $$
FROM auditor.$$ || quote_ident(sch || $$_$$ || tbl || $$_history$$) || $$;
$$;
RETURN TRUE;
END;
$creator$ LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
DECLARE
sortkey TEXT := '';
sortkey := NEW.label_sortkey;
IF NEW.label_class IS NULL THEN
NEW.label_class := COALESCE(
(
SELECT substring(value from E'\\d+')::integer
FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
), 1
);
END IF;
EXECUTE FORMAT('SELECT %s(%L)', acnc.normalizer::REGPROC, NEW.label)
FROM asset.call_number_class acnc
WHERE acnc.id = NEW.label_class
INTO sortkey;
NEW.label_sortkey = sortkey;
RETURN NEW;
END;
$func$ LANGUAGE PLPGSQL;
DROP FUNCTION IF EXISTS public.extract_marc_field(TEXT, BIGINT, TEXT, TEXT);
DROP FUNCTION IF EXISTS public.extract_marc_field(TEXT, BIGINT, TEXT);
CREATE OR REPLACE FUNCTION evergreen.extract_marc_field ( TEXT, BIGINT, TEXT, TEXT ) RETURNS TEXT AS $$
DECLARE
query TEXT;
output TEXT;
query := FORMAT($q$
SELECT regexp_replace(
oils_xpath_string(
%L,
marc,
' '
),
%L,
'',
'g')
FROM %s
WHERE id = %L
$q$, $3, $4, $1::REGCLASS, $2);
EXECUTE query INTO output;
-- RAISE NOTICE 'query: %, output; %', query, output;
RETURN output;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;
CREATE OR REPLACE FUNCTION evergreen.extract_marc_field ( TEXT, BIGINT, TEXT ) RETURNS TEXT AS $$
SELECT extract_marc_field($1,$2,$3,'');
$$ LANGUAGE SQL IMMUTABLE;
DROP FUNCTION IF EXISTS unapi.memoize(TEXT, BIGINT, TEXT, TEXT, TEXT[], TEXT, INT, HSTORE, HSTORE, BOOL);
CREATE OR REPLACE FUNCTION actor.usr_merge_rows( table_name TEXT, col_name TEXT, src_usr INT, dest_usr INT ) RETURNS VOID AS $$
DECLARE
sel TEXT;
upd TEXT;
del TEXT;
cur_row RECORD;
sel := FORMAT('SELECT id::BIGINT FROM %s WHERE %I = $1', table_name::REGCLASS, col_name);
upd := FORMAT('UPDATE %s SET %I = $1 WHERE id = $2', table_name::REGCLASS, col_name);
del := FORMAT('DELETE FROM %s WHERE id = $1', table_name::REGCLASS);
FOR cur_row IN EXECUTE sel USING src_usr LOOP
EXECUTE upd USING dest_usr, cur_row.id;
EXCEPTION WHEN unique_violation THEN
EXECUTE del USING cur_row.id;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
DROP FUNCTION IF EXISTS evergreen.change_db_setting(TEXT, TEXT[]);
ALTER TABLE config.metabib_class ADD CHECK (name ~ '^\w+$');
CREATE OR REPLACE FUNCTION search.symspell_lookup (
raw_input TEXT,
search_class TEXT,
verbosity INT DEFAULT NULL,
xfer_case BOOL DEFAULT NULL,
count_threshold INT DEFAULT NULL,
soundex_weight INT DEFAULT NULL,
pg_trgm_weight INT DEFAULT NULL,
kbdist_weight INT DEFAULT NULL
) RETURNS SETOF search.symspell_lookup_output AS $F$
DECLARE
prefix_length INT;
maxED INT;
good_suggs HSTORE;
word_list TEXT[];
edit_list TEXT[] := '{}';
seen_list TEXT[] := '{}';
output search.symspell_lookup_output;
output_list search.symspell_lookup_output[];
entry RECORD;
entry_key TEXT;
prefix_key TEXT;
sugg TEXT;
input TEXT;
word TEXT;
w_pos INT := -1;
smallest_ed INT := -1;
global_ed INT;
c_symspell_suggestion_verbosity INT;
c_min_suggestion_use_threshold INT;
c_soundex_weight INT;
c_pg_trgm_weight INT;
c_keyboard_distance_weight INT;
c_symspell_transfer_case BOOL;
SELECT cmc.min_suggestion_use_threshold,
cmc.soundex_weight,
cmc.pg_trgm_weight,
cmc.keyboard_distance_weight,
cmc.symspell_transfer_case,
cmc.symspell_suggestion_verbosity
INTO c_min_suggestion_use_threshold,
c_soundex_weight,
c_pg_trgm_weight,
c_keyboard_distance_weight,
c_symspell_transfer_case,
c_symspell_suggestion_verbosity
FROM config.metabib_class cmc
WHERE cmc.name = search_class;
IF NOT FOUND THEN RETURN; END IF; -- If the requested search class doesn't exist, we leave.
c_min_suggestion_use_threshold := COALESCE(count_threshold,c_min_suggestion_use_threshold);
c_symspell_transfer_case := COALESCE(xfer_case,c_symspell_transfer_case);
c_symspell_suggestion_verbosity := COALESCE(verbosity,c_symspell_suggestion_verbosity);
c_soundex_weight := COALESCE(soundex_weight,c_soundex_weight);
c_pg_trgm_weight := COALESCE(pg_trgm_weight,c_pg_trgm_weight);
c_keyboard_distance_weight := COALESCE(kbdist_weight,c_keyboard_distance_weight);
SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
prefix_length := COALESCE(prefix_length, 6);
SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
maxED := COALESCE(maxED, 3);
-- XXX This should get some more thought ... maybe search_normalize?
word_list := ARRAY_AGG(x.word) FROM search.query_parse_positions(raw_input) x;
-- Common case exact match test for preformance
IF c_symspell_suggestion_verbosity = 0 AND CARDINALITY(word_list) = 1 AND CHARACTER_LENGTH(word_list[1]) <= prefix_length THEN
EXECUTE
'SELECT '||search_class||'_suggestions AS suggestions,
'||search_class||'_count AS count,
prefix_key
FROM search.symspell_dictionary
WHERE prefix_key = $1
AND '||search_class||'_count >= $2
AND '||search_class||'_suggestions @> ARRAY[$1]'
INTO entry USING evergreen.lowercase(word_list[1]), c_min_suggestion_use_threshold;
IF entry.prefix_key IS NOT NULL THEN
output.lev_distance := 0; -- definitionally
output.prefix_key := entry.prefix_key;
output.prefix_key_count := entry.count;
output.suggestion_count := entry.count;
output.input := word_list[1];
IF c_symspell_transfer_case THEN
output.suggestion := search.symspell_transfer_casing(output.input, entry.prefix_key);
ELSE
output.suggestion := entry.prefix_key;
END IF;
output.norm_input := entry.prefix_key;
output.qwerty_kb_match := 1;
output.pg_trgm_sim := 1;
output.soundex_sim := 1;
RETURN NEXT output;
RETURN;
END IF;
END IF;
<<word_loop>>
FOREACH word IN ARRAY word_list LOOP
w_pos := w_pos + 1;
input := evergreen.lowercase(word);
IF CHARACTER_LENGTH(input) > prefix_length THEN
prefix_key := SUBSTRING(input FROM 1 FOR prefix_length);
edit_list := ARRAY[input,prefix_key] || search.symspell_generate_edits(prefix_key, 1, maxED);
ELSE
edit_list := input || search.symspell_generate_edits(input, 1, maxED);
END IF;
SELECT ARRAY_AGG(x ORDER BY CHARACTER_LENGTH(x) DESC) INTO edit_list FROM UNNEST(edit_list) x;
output_list := '{}';
seen_list := '{}';
global_ed := NULL;
<<entry_key_loop>>
FOREACH entry_key IN ARRAY edit_list LOOP
smallest_ed := -1;
IF global_ed IS NOT NULL THEN
smallest_ed := global_ed;
END IF;
FOR entry IN EXECUTE
'SELECT '||search_class||'_suggestions AS suggestions,
'||search_class||'_count AS count,
prefix_key
FROM search.symspell_dictionary
WHERE prefix_key = $1
AND '||search_class||'_suggestions IS NOT NULL'
USING entry_key
LOOP
SELECT HSTORE(
ARRAY_AGG(
ARRAY[s, evergreen.levenshtein_damerau_edistance(input,s,maxED)::TEXT]
ORDER BY evergreen.levenshtein_damerau_edistance(input,s,maxED) ASC
)
)
INTO good_suggs
FROM UNNEST(entry.suggestions) s
WHERE (ABS(CHARACTER_LENGTH(s) - CHARACTER_LENGTH(input)) <= maxEd
AND evergreen.levenshtein_damerau_edistance(input,s,maxED) BETWEEN 0 AND maxED)
AND NOT seen_list @> ARRAY[s];
CONTINUE WHEN good_suggs IS NULL;
FOR sugg, output.suggestion_count IN EXECUTE
'SELECT prefix_key, '||search_class||'_count
FROM search.symspell_dictionary
WHERE prefix_key = ANY ($1)
AND '||search_class||'_count >= $2'
USING AKEYS(good_suggs), c_min_suggestion_use_threshold
LOOP
IF NOT seen_list @> ARRAY[sugg] THEN
output.lev_distance := good_suggs->sugg;
seen_list := seen_list || sugg;
-- Track the smallest edit distance among suggestions from this prefix key.
IF smallest_ed = -1 OR output.lev_distance < smallest_ed THEN
smallest_ed := output.lev_distance;
END IF;
-- Track the smallest edit distance for all prefix keys for this word.
IF global_ed IS NULL OR smallest_ed < global_ed THEN
global_ed = smallest_ed;
END IF;
-- Only proceed if the edit distance is <= the max for the dictionary.
IF output.lev_distance <= maxED THEN
IF output.lev_distance > global_ed AND c_symspell_suggestion_verbosity <= 1 THEN
-- Lev distance is our main similarity measure. While
-- trgm or soundex similarity could be the main filter,
-- Lev is both language agnostic and faster.
--
-- Here we will skip suggestions that have a longer edit distance
-- than the shortest we've already found. This is simply an
-- optimization that allows us to avoid further processing
-- of this entry. It would be filtered out later.
CONTINUE;
END IF;
-- If we have an exact match on the suggestion key we can also avoid
-- some function calls.
IF output.lev_distance = 0 THEN
output.qwerty_kb_match := 1;
output.pg_trgm_sim := 1;
output.soundex_sim := 1;
ELSE
output.qwerty_kb_match := evergreen.qwerty_keyboard_distance_match(input, sugg);
output.pg_trgm_sim := similarity(input, sugg);
output.soundex_sim := difference(input, sugg) / 4.0;
END IF;
-- Fill in some fields
IF c_symspell_transfer_case THEN
output.suggestion := search.symspell_transfer_casing(word, sugg);
ELSE
output.suggestion := sugg;
END IF;
output.prefix_key := entry.prefix_key;
output.prefix_key_count := entry.count;
output.input := word;
output.norm_input := input;
output.word_pos := w_pos;
-- We can't "cache" a set of generated records directly, so
-- here we build up an array of search.symspell_lookup_output
-- records that we can revivicate later as a table using UNNEST().
output_list := output_list || output;
EXIT entry_key_loop WHEN smallest_ed = 0 AND c_symspell_suggestion_verbosity = 0; -- exact match early exit
CONTINUE entry_key_loop WHEN smallest_ed = 0 AND c_symspell_suggestion_verbosity = 1; -- exact match early jump to the next key
END IF; -- maxED test
END IF; -- suggestion not seen test
END LOOP; -- loop over suggestions
END LOOP; -- loop over entries
END LOOP; -- loop over entry_keys
-- Now we're done examining this word
IF c_symspell_suggestion_verbosity = 0 THEN
-- Return the "best" suggestion from the smallest edit
-- distance group. We define best based on the weighting
-- of the non-lev similarity measures and use the suggestion
-- use count to break ties.
RETURN QUERY
SELECT * FROM UNNEST(output_list)
ORDER BY lev_distance,
(soundex_sim * c_soundex_weight)
+ (pg_trgm_sim * c_pg_trgm_weight)
+ (qwerty_kb_match * c_keyboard_distance_weight) DESC,
suggestion_count DESC
LIMIT 1;
ELSIF c_symspell_suggestion_verbosity = 1 THEN
-- Return all suggestions from the smallest
-- edit distance group.
RETURN QUERY
SELECT * FROM UNNEST(output_list) WHERE lev_distance = smallest_ed
ORDER BY (soundex_sim * c_soundex_weight)
+ (pg_trgm_sim * c_pg_trgm_weight)
+ (qwerty_kb_match * c_keyboard_distance_weight) DESC,
suggestion_count DESC;
ELSIF c_symspell_suggestion_verbosity = 2 THEN
-- Return everything we find, along with relevant stats
RETURN QUERY
SELECT * FROM UNNEST(output_list)
ORDER BY lev_distance,
(soundex_sim * c_soundex_weight)
+ (pg_trgm_sim * c_pg_trgm_weight)
+ (qwerty_kb_match * c_keyboard_distance_weight) DESC,
suggestion_count DESC;
ELSIF c_symspell_suggestion_verbosity = 3 THEN
-- Return everything we find from the two smallest edit distance groups
RETURN QUERY
SELECT * FROM UNNEST(output_list)
WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) ORDER BY 1 LIMIT 2)
ORDER BY lev_distance,
(soundex_sim * c_soundex_weight)
+ (pg_trgm_sim * c_pg_trgm_weight)
+ (qwerty_kb_match * c_keyboard_distance_weight) DESC,
suggestion_count DESC;
ELSIF c_symspell_suggestion_verbosity = 4 THEN
-- Return everything we find from the two smallest edit distance groups that are NOT 0 distance
RETURN QUERY
SELECT * FROM UNNEST(output_list)
WHERE lev_distance IN (SELECT DISTINCT lev_distance FROM UNNEST(output_list) WHERE lev_distance > 0 ORDER BY 1 LIMIT 2)
ORDER BY lev_distance,
(soundex_sim * c_soundex_weight)
+ (pg_trgm_sim * c_pg_trgm_weight)
+ (qwerty_kb_match * c_keyboard_distance_weight) DESC,
suggestion_count DESC;
END IF;
END LOOP; -- loop over words
END;
$F$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION search.symspell_suggest (
raw_input TEXT,
search_class TEXT,
search_fields TEXT[] DEFAULT '{}',
max_ed INT DEFAULT NULL, -- per word, on average, between norm input and suggestion
verbosity INT DEFAULT NULL, -- 0=Best only; 1=
skip_correct BOOL DEFAULT NULL, -- only suggest replacement words for misspellings?
max_word_opts INT DEFAULT NULL, -- 0 means all combinations, probably want to restrict?
count_threshold INT DEFAULT NULL -- min count of records using the terms
) RETURNS SETOF search.symspell_lookup_output AS $F$
DECLARE
sugg_set search.symspell_lookup_output[];
parsed_query_set search.query_parse_position[];
entry RECORD;
auth_entry RECORD;
norm_count RECORD;
current_sugg RECORD;
auth_sugg RECORD;
norm_test TEXT;
norm_input TEXT;
norm_sugg TEXT;
query_part TEXT := '';
output search.symspell_lookup_output;
c_skip_correct BOOL;
c_variant_authority_suggestion BOOL;
c_symspell_transfer_case BOOL;
c_authority_class_restrict BOOL;
c_min_suggestion_use_threshold INT;
c_soundex_weight INT;
c_pg_trgm_weight INT;
c_keyboard_distance_weight INT;
c_suggestion_word_option_count INT;
c_symspell_suggestion_verbosity INT;
c_max_phrase_edit_distance INT;
-- Gather settings
SELECT cmc.min_suggestion_use_threshold,
cmc.soundex_weight,
cmc.pg_trgm_weight,
cmc.keyboard_distance_weight,
cmc.suggestion_word_option_count,
cmc.symspell_suggestion_verbosity,
cmc.symspell_skip_correct,
cmc.symspell_transfer_case,
cmc.max_phrase_edit_distance,
cmc.variant_authority_suggestion,
cmc.restrict
INTO c_min_suggestion_use_threshold,
c_soundex_weight,
c_pg_trgm_weight,
c_keyboard_distance_weight,
c_suggestion_word_option_count,
c_symspell_suggestion_verbosity,
c_skip_correct,
c_symspell_transfer_case,
c_max_phrase_edit_distance,
c_variant_authority_suggestion,
c_authority_class_restrict
FROM config.metabib_class cmc
WHERE cmc.name = search_class;
IF NOT FOUND THEN RETURN; END IF; -- If the requested search class doesn't exist, we leave.
-- Set up variables to use at run time based on params and settings
c_min_suggestion_use_threshold := COALESCE(count_threshold,c_min_suggestion_use_threshold);
c_max_phrase_edit_distance := COALESCE(max_ed,c_max_phrase_edit_distance);
c_symspell_suggestion_verbosity := COALESCE(verbosity,c_symspell_suggestion_verbosity);
c_suggestion_word_option_count := COALESCE(max_word_opts,c_suggestion_word_option_count);
c_skip_correct := COALESCE(skip_correct,c_skip_correct);
SELECT ARRAY_AGG(
x ORDER BY x.word_pos,
x.lev_distance,
(x.soundex_sim * c_soundex_weight)
+ (x.pg_trgm_sim * c_pg_trgm_weight)
+ (x.qwerty_kb_match * c_keyboard_distance_weight) DESC,
x.suggestion_count DESC
) INTO sugg_set
FROM search.symspell_lookup(
raw_input,
search_class,
c_symspell_suggestion_verbosity,
c_symspell_transfer_case,
c_min_suggestion_use_threshold,
c_soundex_weight,
c_pg_trgm_weight,
c_keyboard_distance_weight
) x
WHERE x.lev_distance <= c_max_phrase_edit_distance;
SELECT ARRAY_AGG(x) INTO parsed_query_set FROM search.query_parse_positions(raw_input) x;
IF search_fields IS NOT NULL AND CARDINALITY(search_fields) > 0 THEN
SELECT STRING_AGG(id::TEXT,',') INTO query_part FROM config.metabib_field WHERE name = ANY (search_fields);
IF CHARACTER_LENGTH(query_part) > 0 THEN query_part := 'AND field IN ('||query_part||')'; END IF;
END IF;
SELECT STRING_AGG(word,' ') INTO norm_input FROM search.query_parse_positions(evergreen.lowercase(raw_input)) WHERE NOT negated;
EXECUTE 'SELECT COUNT(DISTINCT source) AS recs
FROM metabib.' || search_class || '_field_entry
WHERE index_vector @@ plainto_tsquery($$simple$$,$1)' || query_part
INTO norm_count USING norm_input;
SELECT STRING_AGG(word,' ') INTO norm_test FROM UNNEST(parsed_query_set);
FOR current_sugg IN
SELECT *
FROM search.symspell_generate_combined_suggestions(
sugg_set,
parsed_query_set,
c_skip_correct,
c_suggestion_word_option_count
) x
LOOP
EXECUTE 'SELECT COUNT(DISTINCT source) AS recs
FROM metabib.' || search_class || '_field_entry
WHERE index_vector @@ to_tsquery($$simple$$,$1)' || query_part
INTO entry USING current_sugg.test;
SELECT STRING_AGG(word,' ') INTO norm_sugg FROM search.query_parse_positions(current_sugg.suggestion);
IF entry.recs >= c_min_suggestion_use_threshold AND (norm_count.recs = 0 OR norm_sugg <> norm_input) THEN
output.input := raw_input;
output.norm_input := norm_input;
output.suggestion := current_sugg.suggestion;
output.suggestion_count := entry.recs;
output.prefix_key := NULL;
output.prefix_key_count := norm_count.recs;
output.lev_distance := NULLIF(evergreen.levenshtein_damerau_edistance(norm_test, norm_sugg, c_max_phrase_edit_distance * CARDINALITY(parsed_query_set)), -1);
output.qwerty_kb_match := evergreen.qwerty_keyboard_distance_match(norm_test, norm_sugg);
output.pg_trgm_sim := similarity(norm_input, norm_sugg);
output.soundex_sim := difference(norm_input, norm_sugg) / 4.0;
RETURN NEXT output;
END IF;
IF c_variant_authority_suggestion THEN
FOR auth_sugg IN
SELECT DISTINCT m.value AS prefix_key,
m.sort_value AS suggestion,
v.value as raw_input,
v.sort_value as norm_input
FROM authority.simple_heading v
JOIN authority.control_set_authority_field csaf ON (csaf.id = v.atag)
JOIN authority.heading_field f ON (f.id = csaf.heading_field)
JOIN authority.simple_heading m ON (m.record = v.record AND csaf.main_entry = m.atag)
JOIN authority.control_set_bib_field csbf ON (csbf.authority_field = csaf.main_entry)
JOIN authority.control_set_bib_field_metabib_field_map csbfmfm ON (csbf.id = csbfmfm.bib_field)
JOIN config.metabib_field cmf ON (
csbfmfm.metabib_field = cmf.id
AND (c_authority_class_restrict IS FALSE OR cmf.field_class = search_class)
AND (search_fields = '{}'::TEXT[] OR cmf.name = ANY (search_fields))
)
WHERE v.sort_value = norm_sugg
LOOP
EXECUTE 'SELECT COUNT(DISTINCT source) AS recs
FROM metabib.' || search_class || '_field_entry
WHERE index_vector @@ plainto_tsquery($$simple$$,$1)' || query_part
INTO auth_entry USING auth_sugg.suggestion;
IF auth_entry.recs >= c_min_suggestion_use_threshold AND (norm_count.recs = 0 OR auth_sugg.suggestion <> norm_input) THEN
output.input := auth_sugg.raw_input;
output.norm_input := auth_sugg.norm_input;
output.suggestion := auth_sugg.suggestion;
output.prefix_key := auth_sugg.prefix_key;
output.suggestion_count := auth_entry.recs * -1; -- negative value here
output.lev_distance := 0;
output.qwerty_kb_match := 0;
output.pg_trgm_sim := 0;
output.soundex_sim := 0;
RETURN NEXT output;
END IF;
END LOOP;
END IF;
END LOOP;
RETURN;
END;
$F$ LANGUAGE PLPGSQL;