summaryrefslogtreecommitdiff
path: root/util/sql/func.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql/func.sql')
-rw-r--r--util/sql/func.sql56
1 files changed, 36 insertions, 20 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index 75e5ecad..3af04562 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -490,13 +490,16 @@ $$ LANGUAGE 'plpgsql';
-- the stats_cache table
CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
+DECLARE
+ unhidden boolean;
+ hidden boolean;
BEGIN
IF TG_OP = 'INSERT' THEN
IF TG_TABLE_NAME = 'users' THEN
UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
ELSE
IF TG_TABLE_NAME = 'threads_posts' THEN
- IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN
+ IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND threads.hidden = FALSE) THEN
UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
END IF;
ELSE
@@ -504,13 +507,20 @@ BEGIN
END IF;
END IF;
- ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
- IF OLD.hidden = TRUE THEN
+ ELSIF TG_OP = 'UPDATE' THEN
+ IF TG_TABLE_NAME IN('tags', 'traits') THEN
+ unhidden := OLD.state <> 2 AND NEW.state = 2;
+ hidden := OLD.state = 2 AND NEW.state <> 2;
+ ELSE
+ unhidden := OLD.hidden AND NOT NEW.hidden;
+ hidden := NOT unhidden;
+ END IF;
+ IF unhidden THEN
IF TG_TABLE_NAME = 'threads' THEN
UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
END IF;
UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSIF OLD.hidden = FALSE THEN
+ ELSIF hidden THEN
IF TG_TABLE_NAME = 'threads' THEN
UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
END IF;
@@ -802,16 +812,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 +831,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 +875,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 +894,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;