diff options
author | Yorhel <git@yorhel.nl> | 2010-03-13 15:34:24 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-03-13 15:34:24 +0100 |
commit | b8fbb458cdbf6635c45675a3797292110a739f31 (patch) | |
tree | af39f8ae60ba4fa49c92d41a1dc3484d990914b9 /util | |
parent | d5bb5bfc9fd4a043be20bb0e7ba68dbb39157862 (diff) |
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.
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/all.sql | 3 | ||||
-rw-r--r-- | util/sql/func.sql | 46 | ||||
-rw-r--r-- | util/updates/update_2.12.sql | 9 |
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(); + |