diff options
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/all.sql | 8 | ||||
-rw-r--r-- | util/sql/func.sql | 8 | ||||
-rw-r--r-- | util/sql/schema.sql | 4 | ||||
-rw-r--r-- | util/updates/update_2.16.sql | 24 |
4 files changed, 25 insertions, 19 deletions
diff --git a/util/sql/all.sql b/util/sql/all.sql index 6ecb96f1..eb0d724d 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -10,7 +10,7 @@ CREATE TYPE language AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 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 prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_dbedit', 'notify_announce'); +CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', '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'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); @@ -106,8 +106,10 @@ CREATE SEQUENCE covers_seq; -- Rows that are assumed to be available -INSERT INTO users (id, username, mail, rank, notify_dbdel) VALUES (0, 'deleted', 'del@vndb.org', 0, false); -INSERT INTO users (username, mail, rank, notify_dbdel) VALUES ('multi', 'multi@vndb.org', 0, false); +INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); +INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0); +INSERT INTO users_prefs (uid, key, value) VALUES (0, 'notify_nodbedit', '1'); +INSERT INTO users_prefs (uid, key, value) VALUES (1, 'notify_nodbedit', '1'); INSERT INTO stats_cache (section, count) VALUES ('users', 1), diff --git a/util/sql/func.sql b/util/sql/func.sql index 3f1372b5..0248c69a 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -790,12 +790,10 @@ BEGIN ) x(id, title) ON c.id = x.id -- join info about the deletion itself JOIN changes c2 ON c2.id = NEW.latest - -- join info about the user who should get this notification - JOIN users u ON u.id = c.requester -- exclude the user who edited the entry WHERE c.requester <> c2.requester -- exclude users who don't want this notify - AND u.notify_dbedit; + AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = c.requester AND key = 'notify_nodbedit'); RETURN NULL; END; $$ LANGUAGE plpgsql; @@ -805,11 +803,11 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ BEGIN INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid + SELECT 'announce', 't', up.uid, t.id, 1, t.title, NEw.uid FROM threads t JOIN threads_boards tb ON tb.tid = t.id -- get the users who want this announcement - JOIN users u ON u.notify_announce + JOIN users_prefs up ON up.key = 'notify_announce' WHERE t.id = NEW.tid AND tb.type = 'an' -- announcement board AND NOT t.hidden; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 638191c3..ec5d8e58 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -271,9 +271,7 @@ CREATE TABLE users ( ip inet NOT NULL DEFAULT '0.0.0.0', c_tags integer NOT NULL DEFAULT 0, salt character(9) NOT NULL DEFAULT '', - ign_votes boolean NOT NULL DEFAULT FALSE, - notify_dbedit boolean NOT NULL DEFAULT TRUE, - notify_announce boolean NOT NULL DEFAULT FALSE + ign_votes boolean NOT NULL DEFAULT FALSE ); -- users_prefs diff --git a/util/updates/update_2.16.sql b/util/updates/update_2.16.sql index b401a7a4..c1b3358b 100644 --- a/util/updates/update_2.16.sql +++ b/util/updates/update_2.16.sql @@ -50,7 +50,7 @@ ALTER TABLE rlists RENAME COLUMN rstat TO status; -- add users_prefs table -CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_dbedit', 'notify_announce'); +CREATE TYPE prefs_key AS ENUM ('l10n', 'skin', 'customcss', 'show_nsfw', 'hide_list', 'notify_nodbedit', 'notify_announce'); CREATE TABLE users_prefs ( uid integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, key prefs_key NOT NULL, @@ -61,15 +61,23 @@ CREATE TABLE users_prefs ( -- convert from users.* to users_prefs INSERT INTO users_prefs (uid, key, value) SELECT id, 'skin'::prefs_key, skin FROM users WHERE skin <> '' - UNION - SELECT id, 'customcss', customcss FROM users WHERE customcss <> ''; - UNION - SELECT id, 'show_nsfw'::prefs_key, '1' FROM users WHERE show_nsfw; - UNION - SELECT id, 'hide_list'::prefs_key, '1' FROM users WHERE NOT show_list; - + UNION ALL + SELECT id, 'customcss', customcss FROM users WHERE customcss <> '' + UNION ALL + SELECT id, 'show_nsfw', '1' FROM users WHERE show_nsfw + UNION ALL + SELECT id, 'hide_list', '1' FROM users WHERE NOT show_list + UNION ALL + SELECT id, 'notify_nodbedit', '1' FROM users WHERE NOT notify_dbedit + UNION ALL + SELECT id, 'notify_announce', '1' FROM users WHERE notify_announce; + +-- remove unused columns from the user table ALTER TABLE users DROP COLUMN skin; ALTER TABLE users DROP COLUMN customcss; ALTER TABLE users DROP COLUMN show_nsfw; ALTER TABLE users DROP COLUMN show_list; +ALTER TABLE users DROP COLUMN notify_dbedit; +ALTER TABLE users DROP COLUMN notify_announce; + |