summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Discussions.pm4
-rw-r--r--lib/VNDB/Handler/Misc.pm2
-rw-r--r--lib/VNWeb/Discussions/Edit.pm4
-rw-r--r--lib/VNWeb/Discussions/Lib.pm8
-rw-r--r--lib/VNWeb/Discussions/Thread.pm15
-rw-r--r--lib/VNWeb/Misc/Reports.pm4
-rw-r--r--sql/schema.sql12
-rw-r--r--sql/tableattrs.sql1
-rw-r--r--sql/triggers.sql18
-rw-r--r--util/updates/2020-08-07-threads.sql13
10 files changed, 55 insertions, 26 deletions
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 442f8032..0cc05303 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -40,14 +40,14 @@ sub dbThreadGet {
}
my @select = (
- qw|t.id t.title t.count t.locked t.hidden t.private|, 't.poll_question IS NOT NULL AS haspoll',
+ qw|t.id t.c_lastnum t.title t.locked t.hidden t.private|, 't.poll_question IS NOT NULL AS haspoll',
$o{what} =~ /lastpost/ ? (q|EXTRACT('epoch' from tpl.date) AS lastpost_date|, VNWeb::DB::sql_user('ul', 'lastpost_')) : (),
$o{what} =~ /poll/ ? (qw|t.poll_question t.poll_max_options t.poll_preview t.poll_recast|) : (),
);
my @join = (
$o{what} =~ /lastpost/ ? (
- 'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.count',
+ 'JOIN threads_posts tpl ON tpl.tid = t.id AND tpl.num = t.c_lastnum',
'JOIN users ul ON ul.id = tpl.uid'
) : (),
$o{type} && $o{iid} ?
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 70588d45..c316999d 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -122,7 +122,7 @@ sub homepage {
my $boards = join ', ', map $BOARD_TYPE{$_->{type}}{txt}.($_->{iid}?' > '.$_->{title}:''), @{$_->{boards}};
li;
txt fmtage($_->{lastpost_date}).' ';
- a href => "/$_->{id}.$_->{count}#last", title => "Posted in $boards", shorten $_->{title}, 25;
+ a href => "/$_->{id}.$_->{c_lastnum}#last", title => "Posted in $boards", shorten $_->{title}, 25;
lit ' by ';
VNWeb::HTML::user_($_, 'lastpost_');
end;
diff --git a/lib/VNWeb/Discussions/Edit.pm b/lib/VNWeb/Discussions/Edit.pm
index 3f1d495a..dda16d7e 100644
--- a/lib/VNWeb/Discussions/Edit.pm
+++ b/lib/VNWeb/Discussions/Edit.pm
@@ -58,10 +58,7 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub {
return elm_Redirect '/t';
} else {
tuwf->dbExeci('DELETE FROM threads_posts WHERE tid =', \$tid, 'AND num =', \$num);
- tuwf->dbExeci('UPDATE threads_posts SET num = num - 1 WHERE tid =', \$tid, 'AND num >', \$num);
- tuwf->dbExeci('UPDATE threads SET count = count - 1 WHERE id =', \$tid);
tuwf->dbExeci(q{DELETE FROM notifications WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ') AND subid =', \$num);
- tuwf->dbExeci(q{UPDATE notifications SET subid = subid - 1 WHERE ltype = 't' AND iid = vndbid_num(}, \$tid, ') AND subid >', \$num);
return elm_Redirect "/$tid";
}
}
@@ -89,7 +86,6 @@ elm_api DiscussionsEdit => $FORM_OUT, $FORM_IN, sub {
title => $data->{title},
poll_question => $data->{poll} ? $data->{poll}{question} : undef,
poll_max_options => $data->{poll} ? $data->{poll}{max_options} : 1,
- $tid ? () : (count => 1),
auth->permBoardmod ? (
hidden => $data->{hidden},
locked => $data->{locked},
diff --git a/lib/VNWeb/Discussions/Lib.pm b/lib/VNWeb/Discussions/Lib.pm
index d16708c4..d3f3acd6 100644
--- a/lib/VNWeb/Discussions/Lib.pm
+++ b/lib/VNWeb/Discussions/Lib.pm
@@ -58,12 +58,12 @@ sub threadlist_ {
return 0 if $opt{paginate} && !$count;
my $lst = tuwf->dbPagei(\%opt, q{
- SELECT t.id, t.title, t.count, t.locked, t.private, t.hidden, t.poll_question IS NOT NULL AS haspoll
+ SELECT t.id, t.title, t.c_count, t.c_lastnum, t.locked, t.private, t.hidden, t.poll_question IS NOT NULL AS haspoll
, }, sql_user('tfu', 'firstpost_'), ',', sql_totime('tf.date'), q{ as firstpost_date
, }, sql_user('tlu', 'lastpost_'), ',', sql_totime('tl.date'), q{ as lastpost_date
FROM threads t
JOIN threads_posts tf ON tf.tid = t.id AND tf.num = 1
- JOIN threads_posts tl ON tl.tid = t.id AND tl.num = t.count
+ JOIN threads_posts tl ON tl.tid = t.id AND tl.num = t.c_lastnum
JOIN users tfu ON tfu.id = tf.uid
JOIN users tlu ON tlu.id = tl.uid
WHERE }, $where, q{
@@ -100,12 +100,12 @@ sub threadlist_ {
txt_ ', ...' if $l->{boards}->@* > 4;
};
};
- td_ class => 'tc2', $l->{count}-1;
+ td_ class => 'tc2', $l->{c_count}-1;
td_ class => 'tc3', sub { user_ $l, 'firstpost_' };
td_ class => 'tc4', sub {
user_ $l, 'lastpost_';
txt_ ' @ ';
- a_ href => "/$l->{id}.$l->{count}#last", fmtdate $l->{lastpost_date}, 'full';
+ a_ href => "/$l->{id}.$l->{c_lastnum}#last", fmtdate $l->{lastpost_date}, 'full';
};
} for @$lst;
}
diff --git a/lib/VNWeb/Discussions/Thread.pm b/lib/VNWeb/Discussions/Thread.pm
index 87756b50..2b3df320 100644
--- a/lib/VNWeb/Discussions/Thread.pm
+++ b/lib/VNWeb/Discussions/Thread.pm
@@ -54,14 +54,13 @@ my $REPLY_OUT = form_compile out => $REPLY;
elm_api DiscussionsReply => $REPLY_OUT, $REPLY_IN, sub {
my($data) = @_;
- my $t = tuwf->dbRowi('SELECT id, locked, count FROM threads t WHERE id =', \$data->{tid}, 'AND', sql_visible_threads());
+ my $t = tuwf->dbRowi('SELECT id, locked FROM threads t WHERE id =', \$data->{tid}, 'AND', sql_visible_threads());
return tuwf->resNotFound if !$t->{id};
return elm_Unauth if !can_edit t => $t;
- my $num = $t->{count}+1;
+ my $num = sql '(SELECT MAX(num)+1 FROM threads_posts WHERE tid =', \$data->{tid}, ')';
my $msg = bb_subst_links $data->{msg};
- tuwf->dbExeci('INSERT INTO threads_posts', { tid => $t->{id}, num => $num, uid => auth->uid, msg => $msg });
- tuwf->dbExeci('UPDATE threads SET count =', \$num, 'WHERE id =', \$t->{id});
+ $num = tuwf->dbVali('INSERT INTO threads_posts', { tid => $t->{id}, num => $num, uid => auth->uid, msg => $msg }, 'RETURNING num');
elm_Redirect "/$t->{id}.$num#last";
};
@@ -103,7 +102,7 @@ sub posts_ {
div_ class => 'mainbox thread', sub {
table_ class => 'stripe', sub {
tr_ mkclass(deleted => $_->{hidden}), id => $_->{num}, sub {
- td_ class => 'tc1', $t->{count} == $_->{num} ? (id => 'last') : (), sub {
+ td_ class => 'tc1', $_ == $posts->[$#$posts] ? (id => 'last') : (), sub {
a_ href => "/$t->{id}.$_->{num}", "#$_->{num}";
if(!$_->{hidden} || auth->permBoard) {
txt_ ' by ';
@@ -156,8 +155,9 @@ TUWF::get qr{/$RE{tid}(?:(?<sep>[\./])$RE{num})?}, sub {
my($id, $sep, $num) = (tuwf->capture('id'), tuwf->capture('sep')||'', tuwf->capture('num'));
my $t = tuwf->dbRowi(
- 'SELECT id, title, count, hidden, locked, private
+ 'SELECT id, title, hidden, locked, private
, poll_question, poll_max_options
+ , (SELECT COUNT(*) FROM threads_posts WHERE tid = id) AS count
FROM threads t
WHERE', sql_visible_threads(), 'AND id =', \$id
);
@@ -165,7 +165,8 @@ TUWF::get qr{/$RE{tid}(?:(?<sep>[\./])$RE{num})?}, sub {
enrich_boards '', $t;
- my $page = $sep eq '/' ? $num||1 : $sep eq '.' ? ceil($num/25) : 1;
+ my $page = $sep eq '/' ? $num||1 : $sep ne '.' ? 1
+ : ceil((tuwf->dbVali('SELECT COUNT(*) FROM threads_posts WHERE num <=', \$num, 'AND tid =', \$id)||9999)/25);
$num = 0 if $sep ne '.';
my $posts = tuwf->dbPagei({ results => 25, page => $page },
diff --git a/lib/VNWeb/Misc/Reports.pm b/lib/VNWeb/Misc/Reports.pm
index de677c3d..1d610076 100644
--- a/lib/VNWeb/Misc/Reports.pm
+++ b/lib/VNWeb/Misc/Reports.pm
@@ -180,8 +180,8 @@ TUWF::get qr{/report/list}, sub {
Changing the status and/or adding a comment will add an entry to the log, so other mods can see what is going on. Everything on this page is only visible to moderators.
};
p_ q{
- BUG: Deleting a post from a thread (not "hiding", but actually deleting it) will cause reports
- on that thread to refer to innocent posts that happen to get the same post number as the deleted post.
+ BUG: Deleting the last post from a thread (not "hiding", but actually deleting it) will cause the report
+ to refer to an innocent post when someone adds a new post to that thread, as the reply will get the same number as the deleted post.
Not a huge problem, but something to be aware of when browsing through handled reports.
};
br_;
diff --git a/sql/schema.sql b/sql/schema.sql
index 8fd52943..531a4805 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -678,12 +678,13 @@ CREATE TABLE threads (
title varchar(50) NOT NULL DEFAULT '',
locked boolean NOT NULL DEFAULT FALSE,
hidden boolean NOT NULL DEFAULT FALSE,
- count smallint NOT NULL DEFAULT 0,
poll_question varchar(100),
poll_max_options smallint NOT NULL DEFAULT 1,
poll_preview boolean NOT NULL DEFAULT FALSE, -- deprecated
poll_recast boolean NOT NULL DEFAULT FALSE, -- deprecated
- private boolean NOT NULL DEFAULT FALSE
+ private boolean NOT NULL DEFAULT FALSE,
+ c_count smallint NOT NULL DEFAULT 0, -- Number of non-hidden posts
+ c_lastnum smallint NOT NULL DEFAULT 1 -- 'num' of the most recent non-hidden post
);
-- threads_poll_options
@@ -704,13 +705,14 @@ CREATE TABLE threads_poll_votes (
-- threads_posts
CREATE TABLE threads_posts (
tid vndbid NOT NULL,
- num smallint NOT NULL DEFAULT 0,
- uid integer NOT NULL DEFAULT 0,
+ num smallint NOT NULL,
+ uid integer NOT NULL,
date timestamptz NOT NULL DEFAULT NOW(),
edited timestamptz,
msg text NOT NULL DEFAULT '',
hidden boolean NOT NULL DEFAULT FALSE,
- PRIMARY KEY(tid, num)
+ PRIMARY KEY(tid, num),
+ CONSTRAINT threads_posts_first_nonhidden CHECK(num > 1 OR NOT hidden)
);
-- threads_boards
diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql
index a0ff9737..ce77b118 100644
--- a/sql/tableattrs.sql
+++ b/sql/tableattrs.sql
@@ -58,7 +58,6 @@ ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey
ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fkey FOREIGN KEY (optid) REFERENCES threads_poll_options (id) ON DELETE CASCADE;
diff --git a/sql/triggers.sql b/sql/triggers.sql
index 28b882a0..5e83adef 100644
--- a/sql/triggers.sql
+++ b/sql/triggers.sql
@@ -275,6 +275,24 @@ CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (
+-- Update threads.c_count and c_lastnum
+
+CREATE OR REPLACE FUNCTION update_threads_cache() RETURNS trigger AS $$
+BEGIN
+ 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)
+ WHERE id IN(OLD.tid,NEW.tid);
+ RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER update_threads_cache AFTER INSERT OR UPDATE OR DELETE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_threads_cache();
+
+
+
+
+
-- Call update_images_cache() for every change on image_votes
CREATE OR REPLACE FUNCTION update_images_cache() RETURNS trigger AS $$
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