diff options
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Edit.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Lib.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/Discussions/Thread.pm | 15 | ||||
-rw-r--r-- | lib/VNWeb/Misc/Reports.pm | 4 | ||||
-rw-r--r-- | sql/schema.sql | 12 | ||||
-rw-r--r-- | sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | sql/triggers.sql | 18 | ||||
-rw-r--r-- | util/updates/2020-08-07-threads.sql | 13 |
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 |