summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-04-08 18:34:07 +0200
committerYorhel <git@yorhel.nl>2011-04-08 18:34:07 +0200
commit9c0a0b51a115ab95fc4d78bf52d5785d6088ee85 (patch)
treec032dcec9638a26a26148d4e875334a404f81638
parentdf094a5eff934b6057def369dca30df616248c80 (diff)
Generate dbedit/dbdel notifications on character edits
-rw-r--r--ChangeLog1
-rw-r--r--Makefile8
-rw-r--r--util/sql/all.sql4
-rw-r--r--util/sql/func.sql38
-rw-r--r--util/updates/update_2.20.sql11
5 files changed, 44 insertions, 18 deletions
diff --git a/ChangeLog b/ChangeLog
index b9319ac9..20e11ace 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -3,6 +3,7 @@
- Order the traits groups on /i by their 'order' column
- Use same browsing-table on trait pages and char browser
- Added spoiler warning to character revision pages
+ - Generate dbedit/dbdel notifications on character edits
- CSS: Hide links in [spoiler] tags
- Bugfix: don't accidentally remove char traits when editing
- Bugfix: fixed possible SQL table name clash on history browser
diff --git a/Makefile b/Makefile
index d1ed33fe..568eb1b0 100644
--- a/Makefile
+++ b/Makefile
@@ -39,7 +39,7 @@
.PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart sql-import\
update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 update-2.15 update-2.16 update-2.17\
- update-2.18 update-2.19
+ update-2.18 update-2.19 update-2.20
all: dirs js skins robots data/config.pl
@@ -179,3 +179,9 @@ update-2.19: all
$(multi-stop)
${runpsql} < util/updates/update_2.19.sql
$(multi-start)
+
+update-2.20: all
+ $(multi-stop)
+ ${runpsql} < util/updates/update_2.20.sql
+ $(multi-start)
+
diff --git a/util/sql/all.sql b/util/sql/all.sql
index d05297da..a98fbf95 100644
--- a/util/sql/all.sql
+++ b/util/sql/all.sql
@@ -12,7 +12,7 @@ CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
CREATE TYPE language AS ENUM ('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh');
CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce');
-CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't');
+CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't');
CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'filter_vn', 'filter_release', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce');
CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
@@ -88,11 +88,13 @@ CREATE TRIGGER notify_pm AFTER INSERT ON threads_pos
CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel();
CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 75e5ecad..b0dbfff2 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -802,16 +802,16 @@ END;
$$ LANGUAGE plpgsql;
--- called on UPDATE vn / producers / releases when (NOT OLD.hidden AND NEW.hidden)
+-- called on UPDATE vn / producers / releases / chars when (NOT OLD.hidden AND NEW.hidden)
CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$
BEGIN
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
SELECT DISTINCT 'dbdel'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
- c.requester, NEW.id, c2.rev, x.title, c2.requester
+ (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype,
+ h.requester, NEW.id, h2.rev, x.title, h2.requester
-- look for changes of the deleted entry
-- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
- FROM changes c
+ FROM changes h
JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr
JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
@@ -821,12 +821,15 @@ BEGIN
UNION SELECT pr.id, pr2.name FROM producers_rev pr
JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
- ) x(id, title) ON c.id = x.id
+ UNION SELECT cr.id, cr2.name FROM chars_rev cr
+ JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
+ WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
+ ) x(id, title) ON h.id = x.id
-- join info about the deletion itself
- JOIN changes c2 ON c2.id = NEW.latest
- WHERE c.requester <> 1 -- exclude Multi
+ JOIN changes h2 ON h2.id = NEW.latest
+ WHERE h.requester <> 1 -- exclude Multi
-- exclude the user who deleted the entry
- AND c.requester <> c2.requester;
+ AND h.requester <> h2.requester;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
@@ -862,16 +865,16 @@ END;
$$ LANGUAGE plpgsql;
--- called on UPDATE vn / producers / releases when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden)
+-- called on UPDATE vn / producers / releases / chars when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden)
-- this trigger is very similar to notify_dbdel()
CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$
BEGIN
INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
SELECT DISTINCT 'dbedit'::notification_ntype,
- (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype,
- c.requester, NEW.id, c2.rev, x.title, c2.requester
+ (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype,
+ h.requester, NEW.id, h2.rev, x.title, h2.requester
-- look for changes of the edited entry
- FROM changes c
+ FROM changes h
JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr
JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest
WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id
@@ -881,13 +884,16 @@ BEGIN
UNION SELECT pr.id, pr2.name FROM producers_rev pr
JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest
WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id
- ) x(id, title) ON c.id = x.id
+ UNION SELECT cr.id, cr2.name FROM chars_rev cr
+ JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest
+ WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id
+ ) x(id, title) ON h.id = x.id
-- join info about the deletion itself
- JOIN changes c2 ON c2.id = NEW.latest
+ JOIN changes h2 ON h2.id = NEW.latest
-- exclude the user who edited the entry
- WHERE c.requester <> c2.requester
+ WHERE h.requester <> h2.requester
-- exclude users who don't want this notify
- AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = c.requester AND key = 'notify_nodbedit');
+ AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit');
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
diff --git a/util/updates/update_2.20.sql b/util/updates/update_2.20.sql
new file mode 100644
index 00000000..236161d2
--- /dev/null
+++ b/util/updates/update_2.20.sql
@@ -0,0 +1,11 @@
+
+ALTER TYPE notification_ltype RENAME TO tmp;
+CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't');
+ALTER TABLE notifications ALTER COLUMN ltype TYPE notification_ltype USING ltype::text::notification_ltype;
+DROP TYPE tmp;
+
+\i util/sql/func.sql
+
+CREATE TRIGGER notify_dbdel AFTER UPDATE ON chars FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel();
+CREATE TRIGGER notify_dbedit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit();
+