Improved VN search

This adds a new column to the vn table: c_search, which holds the
normalized titles for speedy search results using LIKE.

Also split some functions from VNDB::Func that didn't require YAWF into
a VNDBUtil module, so Multi can also make use of them. The normalization
functions are the same for Multi and VNDB, after all.

The API and Multi::IRC still use the old search, these should be updated
as well.

3 files changed, 58 insertions, 0 deletions

diff --git a/util/sql/all.sql b/util/sql/all.sql
index 9a9eace0..fd1f0a73 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -74,6 +74,9 @@ CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON producers
 CREATE TRIGGER notify_dbedit              AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE notify_dbedit();
 CREATE TRIGGER notify_announce            AFTER  INSERT           ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce();
 
+CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+
 
 -- Sequences used for ID generation of items not in the DB
 CREATE SEQUENCE covers_seq;
diff --git a/util/sql/func.sql b/util/sql/func.sql
index f9f0e490..7b8b9302 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -586,6 +586,52 @@ $$ LANGUAGE plpgsql;
 
 
 
+-- Check for updates to vn.c_search
+-- 1. NOTIFY is sent when vn.c_search goes from non-NULL to NULL
+-- vn.c_search is set to NULL when:
+-- 2. UPDATE on VN with the hidden field going from TRUE to FALSE
+-- 3. VN add/edit of which the title/original/alias fields differ from previous revision
+-- 4. Release gets hidden or unhidden
+-- 5. Release add/edit of which the title/original/vn fields differ from the previous revision
+CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$
+BEGIN
+  IF TG_TABLE_NAME = 'vn' THEN
+    -- 1.
+    IF NEW.c_search IS NULL AND NOT NEW.hidden THEN
+      NOTIFY vnsearch;
+    -- 2.
+    ELSIF NEW.hidden IS DISTINCT FROM OLD.hidden THEN
+      UPDATE vn SET c_search = NULL WHERE id = NEW.id;
+    -- 3.
+    ELSIF NEW.latest IS DISTINCT FROM OLD.latest THEN
+      IF EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2
+        WHERE v1.id = OLD.latest AND v2.id = NEW.latest
+          AND (v1.title IS DISTINCT FROM v2.title OR v1.original IS DISTINCT FROM v2.original OR v1.alias IS DISTINCT FROM v2.alias)
+      ) THEN
+        UPDATE vn SET c_search = NULL WHERE id = NEW.id;
+      END IF;
+    END IF;
+  ELSIF TG_TABLE_NAME = 'releases' THEN
+    -- 4. & 5.
+    IF NEW.hidden IS DISTINCT FROM OLD.hidden OR (
+       NEW.latest IS DISTINCT FROM OLD.latest AND (
+         EXISTS(
+          SELECT 1 FROM releases_rev r1, releases_rev r2
+           WHERE r1.id = OLD.latest AND r2.id = NEW.latest
+             AND (r1.title IS DISTINCT FROM r2.title OR r1.original IS DISTINCT FROM r2.original)
+         )
+         OR EXISTS(SELECT vid FROM releases_vn WHERE rid = OLD.latest EXCEPT SELECT vid FROM releases_vn WHERE rid = NEW.latest)
+         OR (SELECT COUNT(*) FROM releases_vn WHERE rid = OLD.latest) <> (SELECT COUNT(*) FROM releases_vn WHERE rid = NEW.latest)
+    )) THEN
+      UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest);
+    END IF;
+  END IF;
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
 
 
 ----------------------------------------------------------
diff --git a/util/updates/update_2.12.sql b/util/updates/update_2.12.sql
new file mode 100644
index 00000000..4d3dc4a4
--- /dev/null
+++ b/util/updates/update_2.12.sql
@@ -0,0 +1,9 @@
+
+-- cache for search
+ALTER TABLE vn ADD COLUMN c_search text;
+
+\i util/sql/func.sql
+
+CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON vn            FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+CREATE TRIGGER vn_vnsearch_notify         AFTER  UPDATE           ON releases      FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify();
+