summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2020-08-06 13:22:55 +0200
committerYorhel <git@yorhel.nl>2020-08-06 13:30:20 +0200
commit14fa9431498e7102ba772549570851839df93f4e (patch)
treeb14e3545c23daa716b6c4a5a0c41e8945f4e0fae /util
parent55fd685c182397616be8c5af1d8db996b1d2d9b1 (diff)
Discussions: Allow discontinuous post numbers + ignore hidden posts in counts/lastpost
This solves a few problems: - 'hidden' posts will no longer cause the thread to be bumped to the front page. - Deleting posts will no longer cause other posts to be renumbered (and hence will not break existing links to posts) - Numbers of deleted posts will no longer be re-used (except when they were the last post in the thread - fixing this would require an additional column in 'threads', but it didn't seem worth the trouble)
Diffstat (limited to 'util')
-rw-r--r--util/updates/2020-08-07-threads.sql13
1 files changed, 13 insertions, 0 deletions
diff --git a/util/updates/2020-08-07-threads.sql b/util/updates/2020-08-07-threads.sql
index 612ecaf0..d036ee81 100644
--- a/util/updates/2020-08-07-threads.sql
+++ b/util/updates/2020-08-07-threads.sql
@@ -1,6 +1,7 @@
-- * Convert thread identifiers to vndbids
-- * Remove threads_poll_votes.tid
-- * Add two ON DELETE CASCADE's
+-- * Replace threads.count with threads.c_(count,lastnum)
ALTER TABLE threads_poll_votes DROP COLUMN tid;
ALTER TABLE threads_poll_votes ADD PRIMARY KEY (optid,uid);
@@ -27,4 +28,16 @@ ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fke
ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
+ALTER TABLE threads DROP COLUMN count;
+ALTER TABLE threads ADD COLUMN c_count smallint NOT NULL DEFAULT 0; -- Number of non-hidden posts
+ALTER TABLE threads ADD COLUMN c_lastnum smallint NOT NULL DEFAULT 1; -- 'num' of the most recent non-hidden post
+
+ALTER TABLE threads_posts ALTER COLUMN num DROP DEFAULT;
+ALTER TABLE threads_posts ALTER COLUMN uid DROP DEFAULT;
+ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR NOT hidden);
+
+UPDATE threads
+ SET c_count = (SELECT COUNT(*) FROM threads_posts WHERE NOT hidden AND tid = threads.id)
+ , c_lastnum = (SELECT MAX(num) FROM threads_posts WHERE NOT hidden AND tid = threads.id);
+
\i sql/triggers.sql