summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
Diffstat (limited to 'util')
-rw-r--r--util/sql/all.sql8
-rw-r--r--util/sql/func.sql8
-rw-r--r--util/sql/schema.sql4
-rw-r--r--util/updates/update_2.16.sql24
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;
+