Generalize substring search relevance + apply to most dropdown searches

This is a generalization of the search improvements made in
  7da2edeaa0f6cf7794f4f8f68960497dc1be893c and
  92235222dba4e5d0c7713d53ef12e0f10e371b83
And has been applied to the dropdown searches for producers, staff, tags
and traits.

For all those searches, exact matches are listed first, followed by
prefix matches, and then substring matches. Relevance is currently only
based on the primary name/title and ignores aliases (except for staff).
This is fixable, but not trivial, and I'm not sure it's all that useful.

1 files changed, 15 insertions, 0 deletions

diff --git a/util/sql/func.sql b/util/sql/func.sql
index 125455a2..ec109692 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -31,6 +31,21 @@ CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
 $$ LANGUAGE sql IMMUTABLE;
 
 
+-- Assigns a score to the relevance of a substring match, intended for use in
+-- an ORDER BY clause. Exact matches are ordered first, prefix matches after
+-- that, and finally a normal substring match. Not particularly fast, but
+-- that's to be expected of naive substring searches.
+-- Pattern must be escaped for use as a LIKE pattern.
+CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$
+SELECT CASE
+  WHEN str ILIKE      pattern      THEN 0
+  WHEN str ILIKE      pattern||'%' THEN 1
+  WHEN str ILIKE '%'||pattern||'%' THEN 2
+  ELSE 3
+END;
+$$ LANGUAGE SQL;
+
+
 -- update_vncache(id) - updates some c_* columns in the vn table
 CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
   UPDATE vn SET