diff options
author | Yorhel <git@yorhel.nl> | 2010-02-01 18:36:27 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-02-01 18:37:45 +0100 |
commit | 4d9254ff709e1925211e62cee480cf287d52a74a (patch) | |
tree | ef73e5eb98f69b4f4d45b54cd670026a71481ee3 | |
parent | c4afecd1c5135d10a36a3a663837f5b83083bdd2 (diff) |
Added notification for database deletions to users who contributed
Complex queries...
-rw-r--r-- | ChangeLog | 4 | ||||
-rw-r--r-- | data/lang.txt | 14 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 3 | ||||
-rw-r--r-- | util/sql/all.sql | 3 | ||||
-rw-r--r-- | util/sql/func.sql | 33 | ||||
-rw-r--r-- | util/updates/update_2.11.sql | 12 |
6 files changed, 62 insertions, 7 deletions
@@ -8,7 +8,9 @@ git - ? - Abstracted parsing skin config files into a SkinFile module - Automatically generate the skin credits on d7, by reading the skin files - Only tagmods can create top-level tags - - Notification system (still only used for PMs) + - Notification system for + - PMs + - Notifying users of a deletion of an entry they contributed to - Removed the ?l10n= paremeter - Remove sessions that haven't been used for more than a month - Properly copy over search string on switching with the searchtabs diff --git a/data/lang.txt b/data/lang.txt index 264d31ed..cd19e812 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -4673,7 +4673,13 @@ cs*: hu*: :_usern_type_pm -en : PM +en : Private Message +ru*: +cs*: +hu*: + +:_usern_type_dbdel +en : Entry you contributed to has been deleted ru*: cs*: hu*: @@ -4690,6 +4696,12 @@ ru*: cs*: hu*: +:_usern_n_item_edit +en : Edit of [_1] by [_2] +ru*: +cs*: +hu*: + :_usern_but_markread en : mark selected read ru*: diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 7ab21336..f25e74c1 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -593,8 +593,9 @@ sub notifies { td class => 'tc5', onclick => qq|javascript:location.href="/u$uid/notify/$l->{id}"|; lit mt '_usern_n_'.( $l->{ltype} eq 't' ? ($l->{subid} == 1 ? 't_new' : 't_reply') - : die("unknown notification type")), + : 'item_edit'), sprintf('<i>%s</i>', xml_escape $l->{c_title}), sprintf('<i>%s</i>', xml_escape $l->{username}); + sprintf('<i>%s</i>', xml_escape $l->{title}), sprintf('<i>%s</i>', xml_escape $l->{subtitle}); end; end; }, diff --git a/util/sql/all.sql b/util/sql/all.sql index 06c1ca7f..e4f66d91 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -63,6 +63,9 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update(); CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); -- Sequences used for ID generation of items not in the DB diff --git a/util/sql/func.sql b/util/sql/func.sql index 9675097e..1dcdd496 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -618,3 +618,36 @@ BEGIN END; $$ LANGUAGE plpgsql; + +-- called on UPDATE vn / producers / releases +CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ +BEGIN + -- item is deleted? + IF OLD.latest IS DISTINCT FROM NEW.latest AND NOT OLD.hidden AND NEW.hidden THEN + 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 + -- 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 + 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 + UNION SELECT rr.id, rr2.title FROM releases_rev rr + JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest + WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id + 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 + -- join info about the deletion itself + JOIN changes c2 ON c2.id = NEW.latest + WHERE c.requester <> 1 -- exclude Multi + -- exclude the user who deleted the entry + AND c.requester <> c2.requester; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql index 0a077bc1..27b90c8f 100644 --- a/util/updates/update_2.11.sql +++ b/util/updates/update_2.11.sql @@ -1,7 +1,7 @@ -CREATE TYPE notification_ntype AS ENUM ('pm'); -CREATE TYPE notification_ltype AS ENUM ('t'); +CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel'); +CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't'); CREATE TABLE notifications ( id serial PRIMARY KEY NOT NULL, @@ -39,8 +39,6 @@ CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); - CREATE OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ BEGIN @@ -90,3 +88,9 @@ ALTER TABLE sessions RENAME COLUMN expiration TO added; UPDATE sessions SET added = added - '1 year'::interval; +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); +-- make sure to add these triggers AFTER performing the batch edit above +CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); + |