From ffd7bf407e56bf271981151b6845f26ff1b366bc Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Thu, 19 Jun 2008 23:11:38 +0200 Subject: [PATCH] Converted forum to catalyst. --- NDWeb/Forum.pm | 110 --------- NDWeb/Pages/Forum.pm | 288 ----------------------- NDWeb/Pages/Forum/Search.pm | 74 ------ lib/ND | 2 +- lib/NDWeb.pm | 4 + lib/NDWeb/Controller/Forum.pm | 431 ++++++++++++++++++++++++++++++++++ lib/NDWeb/Include.pm | 7 +- root/src/forum/allUnread.tt2 | 33 +++ root/src/forum/board.tt2 | 56 +++++ root/src/forum/index.tt2 | 19 ++ root/src/forum/search.tt2 | 53 +++++ root/src/forum/thread.tt2 | 28 +++ t/controller_Forum.t | 10 + templates/forum.tmpl | 116 --------- templates/forum/search.tmpl | 46 ---- templates/viewthread.tmpl | 20 -- 16 files changed, 641 insertions(+), 656 deletions(-) delete mode 100644 NDWeb/Forum.pm delete mode 100644 NDWeb/Pages/Forum.pm delete mode 100644 NDWeb/Pages/Forum/Search.pm create mode 100644 lib/NDWeb/Controller/Forum.pm create mode 100644 root/src/forum/allUnread.tt2 create mode 100644 root/src/forum/board.tt2 create mode 100644 root/src/forum/index.tt2 create mode 100644 root/src/forum/search.tt2 create mode 100644 root/src/forum/thread.tt2 create mode 100644 t/controller_Forum.t delete mode 100644 templates/forum.tmpl delete mode 100644 templates/forum/search.tmpl delete mode 100644 templates/viewthread.tmpl diff --git a/NDWeb/Forum.pm b/NDWeb/Forum.pm deleted file mode 100644 index 810f547..0000000 --- a/NDWeb/Forum.pm +++ /dev/null @@ -1,110 +0,0 @@ -#************************************************************************** -# Copyright (C) 2006 by Michael Andreen * -# * -# This program is free software; you can redistribute it and/or modify * -# it under the terms of the GNU General Public License as published by * -# the Free Software Foundation; either version 2 of the License, or * -# (at your option) any later version. * -# * -# This program is distributed in the hope that it will be useful, * -# but WITHOUT ANY WARRANTY; without even the implied warranty of * -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * -# GNU General Public License for more details. * -# * -# You should have received a copy of the GNU General Public License * -# along with this program; if not, write to the * -# Free Software Foundation, Inc., * -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * -#**************************************************************************/ - -package NDWeb::Forum; -use strict; -use warnings FATAL => 'all'; -use CGI qw{:standard}; -use HTML::Template; -use NDWeb::Include; -require Exporter; - -our @ISA = qw/Exporter/; -our @EXPORT = qw/viewForumThread addForumPost addForumThread markThreadAsRead/; - -sub viewForumThread { - my ($thread) = @_; - - my $template = HTML::Template->new(filename => "templates/viewthread.tmpl", global_vars => 1, cache => 1); - - $template->param(Id => $thread->{id}); - $template->param(Post => $thread->{post}); - - my $posts = $ND::DBH->prepare(q{SELECT u.username,date_trunc('seconds',fp.time::timestamp) AS time,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread -FROM forum_threads ft JOIN forum_posts fp USING (ftid) JOIN users u ON u.uid = fp.uid LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid -WHERE ft.ftid = $1 -ORDER BY fp.time ASC -}); - $posts->execute($thread->{id},$ND::UID) or $ND::ERROR .= p($ND::DBH->errstr); - my @posts; - my $old = 1; - while (my $post = $posts->fetchrow_hashref){ - if ($old && $post->{unread}){ - $old = 0; - $post->{NewPosts} = 1; - } - $post->{message} = parseMarkup($post->{message}); - push @posts,$post; - } - - if (defined param('cmd') && param('cmd') eq 'Preview'){ - my $text = parseMarkup(escapeHTML(param('message'))); - $text .= p b $@ if $@; - push @posts,{message => $text, unread => 1, username => 'PREVIEW', Time => 'Not submitted yet', NewPosts => $old ? 1 : 0}; - - $text = escapeHTML param('message'); - $text =~ s/\x{3}\d\d?//g; #mirc color TODO: possibly match until \x{0F} and change to [color] block - $text =~ s/[^\x{9}\x{A}\x{D}\x{20}-\x{D7FF}\x{E000}-\x{FFFD}\x{10000}-\x{10FFFF}]//g; - $template->param(Message => $text); - } - $template->param(Posts => \@posts); - - markThreadAsRead($thread->{id}); - - return $template->output; -} - -sub addForumPost { - my ($dbh,$thread,$uid,$message) = @_; - my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid) VALUES($1,$2,$3)}); - unless ($insert->execute($thread->{id},escapeHTML($message),$uid)){ - $ND::ERROR .= p($dbh->errstr); - return 0; - } - return 1; -} - -sub addForumThread { - my ($dbh,$board,$uid,$subject) = @_; - - my $insert = $dbh->prepare(q{INSERT INTO forum_threads (fbid,subject,uid) VALUES($1,$2,$3)}); - - if ($insert->execute($board->{id},escapeHTML($subject),$uid)){ - my $id = $dbh->last_insert_id(undef,undef,undef,undef,"forum_threads_ftid_seq"); - return $dbh->selectrow_hashref(q{SELECT ftid AS id, subject, $2::boolean AS post FROM forum_threads WHERE ftid = $1} - ,undef,$id,$board->{post}) - or $ND::ERROR .= p($dbh->errstr); - }else{ - $ND::ERROR .= p($dbh->errstr); - } -} - -sub markThreadAsRead { - my ($thread) = @_; - my $rows = $ND::DBH->do(q{UPDATE forum_thread_visits SET time = now() -WHERE uid = $1 AND ftid = $2},undef,$ND::UID,$thread); - if ($rows == 0){ - $ND::DBH->do(q{INSERT INTO forum_thread_visits (uid,ftid) VALUES ($1,$2)} - ,undef,$ND::UID,$thread) or $ND::ERROR .= p($ND::DBH->errstr); - }elsif(not defined $rows){ - $ND::ERROR .= p($ND::DBH->errstr); - } -} - -1; diff --git a/NDWeb/Pages/Forum.pm b/NDWeb/Pages/Forum.pm deleted file mode 100644 index 1bd5793..0000000 --- a/NDWeb/Pages/Forum.pm +++ /dev/null @@ -1,288 +0,0 @@ -#************************************************************************** -# Copyright (C) 2006 by Michael Andreen * -# * -# This program is free software; you can redistribute it and/or modify * -# it under the terms of the GNU General Public License as published by * -# the Free Software Foundation; either version 2 of the License, or * -# (at your option) any later version. * -# * -# This program is distributed in the hope that it will be useful, * -# but WITHOUT ANY WARRANTY; without even the implied warranty of * -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * -# GNU General Public License for more details. * -# * -# You should have received a copy of the GNU General Public License * -# along with this program; if not, write to the * -# Free Software Foundation, Inc., * -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * -#**************************************************************************/ - -package NDWeb::Pages::Forum; -use strict; -use warnings; -use NDWeb::Forum; -use CGI qw/:standard/; -use NDWeb::Include; -use ND::Include; - -use base qw/NDWeb::XMLPage/; - -$NDWeb::Page::PAGES{forum} = __PACKAGE__; - -sub parse { - my $self = shift; - if ($self->{URI} =~ m{^/.*/allUnread}){ - $self->{allUnread} = 1; - }elsif ($self->{URI} =~ m{^/.*/search(?:/(.*))?}){ - bless $self, 'NDWeb::Pages::Forum::Search'; - $self->{PAGE} = 'forum/search'; - } -} - -sub render_body { - my $self = shift; - my ($BODY) = @_; - $self->{TITLE} = 'Forum'; - my $DBH = $self->{DBH}; - - $DBH->do(q{UPDATE users SET last_forum_visit = NOW() WHERE uid = $1},undef,$ND::UID) or warn $DBH->errstr; - - my $board; - if(param('b')){ - my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post, bool_or(fa.moderate) AS moderate,fb.fcid - FROM forum_boards fb NATURAL JOIN forum_access fa - WHERE fb.fbid = $1 AND - gid IN (SELECT groups($2)) - GROUP BY fb.fbid,fb.board,fb.fcid - }); - $board = $DBH->selectrow_hashref($boards,undef,param('b'),$ND::UID) or warn $DBH->errstr; - } - if (param('markAsRead')){ - my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject - ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread - ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post - FROM forum_threads ft - JOIN forum_posts fp USING (ftid) - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid - WHERE ((ft.fbid IS NULL AND $1 IS NULL) OR ft.fbid = $1) AND fp.time <= $3 - GROUP BY ft.ftid, ft.subject - HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1 - }); - - $threads->bind_param('$1',$board->{id},{TYPE => DBI::SQL_INTEGER }) or warn $DBH->errstr; - $threads->bind_param('$2',$ND::UID,{TYPE => DBI::SQL_INTEGER }) or warn $DBH->errstr; - $threads->bind_param('$3',param('markAsRead')) or warn $DBH->errstr; - $threads->execute or warn $DBH->errstr; - while (my $thread = $threads->fetchrow_hashref){ - markThreadAsRead $thread->{id}; - } - } - - my $thread; - if(param('t')){ - my $findThread = $DBH->prepare(q{SELECT ft.ftid AS id,ft.subject, bool_or(fa.post) AS post - , bool_or(fa.moderate) AS moderate,ft.fbid,fb.board,fb.fcid,ft.sticky - FROM forum_boards fb NATURAL JOIN forum_access fa NATURAL JOIN forum_threads ft - WHERE ft.ftid = $1 AND - gid IN (SELECT groups($2)) - GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky - }); - $thread = $DBH->selectrow_hashref($findThread,undef,param('t'),$ND::UID) or warn $DBH->errstr; - } - - if (defined param('cmd')){ - if(param('cmd') eq 'Submit' or param('cmd') eq 'Preview'){ - $DBH->begin_work; - if ($board && $board->{post}){ - $thread = addForumThread $DBH,$board,$ND::UID,param('subject'); - } - if (param('cmd') eq 'Submit' and $thread && $thread->{post}){ - addForumPost($DBH,$thread,$ND::UID,param('message')); - $self->{RETURN} = 'REDIRECT'; - $self->{REDIR_LOCATION} = "/forum?t=$thread->{id}#NewPosts"; - } - $DBH->commit or warn $DBH->errstr; - return if $self->{RETURN}; - } - if(param('cmd') eq 'Move' && $board->{moderate}){ - $DBH->begin_work; - my $moveThread = $DBH->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3}); - for my $param (param()){ - if ($param =~ /t:(\d+)/){ - $moveThread->execute(param('board'),$1,$board->{id}) or warn $DBH->errstr; - if ($moveThread->rows > 0){ - log_message $ND::UID, qq{Moved thread: $1 to board: }.param('board'); - } - } - } - $DBH->commit or warn $DBH->errstr; - } - if($thread && param('cmd') eq 'Sticky' && $thread->{moderate}){ - if ($DBH->do(q{UPDATE forum_threads SET sticky = TRUE WHERE ftid = ?}, undef,$thread->{id})){ - $thread->{sticky} = 1; - }else{ - warn $DBH->errstr; - } - } - if($thread && param('cmd') eq 'Unsticky' && $thread->{moderate}){ - if ($DBH->do(q{UPDATE forum_threads SET sticky = FALSE WHERE ftid = ?}, undef,$thread->{id})){ - $thread->{sticky} = 0; - }else{ - warn $DBH->errstr; - } - } - } - - - if ($thread){ #Display the thread - $BODY->param(Title => $thread->{subject}); - $BODY->param(FBID => $thread->{fbid}); - $BODY->param(Board => $thread->{board}); - $BODY->param(FTID => $thread->{id}); - $BODY->param(Moderate => $thread->{moderate}); - $BODY->param(Sticky => $thread->{sticky} ? 'Unsticky' : 'Sticky'); - $BODY->param(Thread => viewForumThread $thread); - my ($category) = $DBH->selectrow_array(q{SELECT category FROM forum_categories WHERE fcid = $1} - ,undef,$thread->{fcid}) or warn $DBH->errstr; - $BODY->param(Category => $category); - - }elsif(defined $self->{allUnread}){ #List threads in this board - $BODY->param(AllUnread => 1); - - my $threads = $DBH->prepare(q{SELECT fcid,category,fbid,board,ft.ftid AS id,u.username,ft.subject, - count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts, - date_trunc('seconds',max(fp.time)::timestamp) as last_post, - min(fp.time)::date as posting_date, ft.sticky - FROM forum_categories fc - JOIN forum_boards fb USING (fcid) - JOIN forum_threads ft USING (fbid) - JOIN forum_posts fp USING (ftid) - JOIN users u ON u.uid = ft.uid - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid - WHERE fbid > 0 AND - fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) - GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username - HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1 - ORDER BY fcid,fbid,sticky DESC,last_post DESC}); - - my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef); - $BODY->param(Date => $time); - $threads->execute($ND::UID) or warn $DBH->errstr; - my @categories; - my $category = {fcid => 0}; - my $board = {id => 0}; - while (my $thread = $threads->fetchrow_hashref){ - if ($category->{fcid} != $thread->{fcid}){ - delete $category->{fcid}; - $category = {fcid => $thread->{fcid}, category => $thread->{category}}; - push @categories,$category; - } - if ($board->{id} != $thread->{fbid}){ - $board = {id => $thread->{fbid}, board => $thread->{board}}; - push @{$category->{Boards}},$board; - } - delete $thread->{fcid}; - delete $thread->{fbid}; - delete $thread->{category}; - delete $thread->{board}; - push @{$board->{Threads}},$thread; - } - delete $category->{fcid}; - $BODY->param(Categories => \@categories); - - }elsif($board){ #List threads in this board - $BODY->param(ViewBoard => 1); - $BODY->param(Title => $board->{board}); - $BODY->param(Post => $board->{post}); - $BODY->param(Moderate => $board->{moderate}); - $BODY->param(Id => $board->{id}); - $BODY->param(FBID => $board->{id}); - $BODY->param(Board => $board->{board}); - my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef); - $BODY->param(Date => $time); - - my $threads = $DBH->prepare(q{SELECT ft.ftid AS id,u.username,ft.subject - ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts - ,date_trunc('seconds',max(fp.time)::timestamp) as last_post - ,min(fp.time)::date as posting_date, ft.sticky - FROM forum_threads ft - JOIN forum_posts fp USING (ftid) - JOIN users u ON u.uid = ft.uid - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid - WHERE ft.fbid = $1 - GROUP BY ft.ftid, ft.subject,ft.sticky,u.username - ORDER BY sticky DESC,last_post DESC - }) or warn $DBH->errstr; - $threads->execute($board->{id},$ND::UID) or warn $DBH->errstr; - my @threads; - while (my $thread = $threads->fetchrow_hashref){ - push @threads,$thread; - } - $BODY->param(Threads => \@threads); - - if ($board->{moderate}){ - my $categories = $DBH->prepare(q{SELECT fcid AS id,category FROM forum_categories ORDER BY fcid}); - my $boards = $DBH->prepare(q{SELECT fb.fbid AS id,fb.board, bool_or(fa.post) AS post - FROM forum_boards fb NATURAL JOIN forum_access fa - WHERE fb.fcid = $1 AND - gid IN (SELECT groups($2)) - GROUP BY fb.fbid,fb.board - ORDER BY fb.fbid - }); - $categories->execute or warn $DBH->errstr; - my @categories; - while (my $category = $categories->fetchrow_hashref){ - $boards->execute($category->{id},$ND::UID) or warn $DBH->errstr; - - my @boards; - while (my $b = $boards->fetchrow_hashref){ - next if (not $b->{post} or $b->{id} == $board->{id}); - delete $b->{post}; - push @boards,$b; - } - $category->{Boards} = \@boards; - delete $category->{id}; - push @categories,$category if @boards; - } - $BODY->param(Categories => \@categories); - } - my ($category) = $DBH->selectrow_array(q{SELECT category FROM forum_categories WHERE fcid = $1} - ,undef,$board->{fcid}) or warn $DBH->errstr; - $BODY->param(Category => $category); - }else{ #List boards - $BODY->param(Overview => 1); - my $boards = $DBH->prepare(q{SELECT fcid,category,fb.fbid AS id,fb.board - ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE) - AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread - ,date_trunc('seconds',max(fp.time)::timestamp) as last_post - FROM forum_categories - JOIN forum_boards fb USING (fcid) - LEFT OUTER JOIN forum_threads ft USING (fbid) - LEFT OUTER JOIN forum_posts fp USING (ftid) - LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid) - WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1))) - GROUP BY fcid,category,fb.fbid, fb.board - ORDER BY fcid,fb.fbid - }); - $boards->execute($ND::UID) or warn $DBH->errstr; - my @categories; - my $category = {fcid => 0}; - while (my $board = $boards->fetchrow_hashref){ - if ($category->{fcid} != $board->{fcid}){ - delete $category->{fcid}; - $category = {fcid => $board->{fcid}, category => $board->{category}}; - push @categories,$category; - } - delete $board->{fcid}; - delete $board->{category}; - push @{$category->{Boards}},$board; - } - delete $category->{fcid}; - $BODY->param(Categories => \@categories); - - } - return $BODY; -} - -1; - diff --git a/NDWeb/Pages/Forum/Search.pm b/NDWeb/Pages/Forum/Search.pm deleted file mode 100644 index 1789bc7..0000000 --- a/NDWeb/Pages/Forum/Search.pm +++ /dev/null @@ -1,74 +0,0 @@ -#************************************************************************** -# Copyright (C) 2006 by Michael Andreen * -# * -# This program is free software; you can redistribute it and/or modify * -# it under the terms of the GNU General Public License as published by * -# the Free Software Foundation; either version 2 of the License, or * -# (at your option) any later version. * -# * -# This program is distributed in the hope that it will be useful, * -# but WITHOUT ANY WARRANTY; without even the implied warranty of * -# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * -# GNU General Public License for more details. * -# * -# You should have received a copy of the GNU General Public License * -# along with this program; if not, write to the * -# Free Software Foundation, Inc., * -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * -#**************************************************************************/ - -package NDWeb::Pages::Forum::Search; -use strict; -use warnings; -use NDWeb::Forum; -use CGI qw/:standard/; -use NDWeb::Include; -use ND::Include; - -use base qw/NDWeb::Pages::Forum/; - -sub render_body { - my $self = shift; - my ($BODY) = @_; - my $DBH = $self->{DBH}; - $self->{TITLE} = 'Forum'; - my @queries; - if (param('search')){ - push @queries,'('.param('search').')'; - } - my %cat = (body => 'D', topic => 'A', author => 'B'); - for ('body','topic','author'){ - if (param($_)){ - my @words = split /\W+/,param($_); - my $op = param('all'.$_) ? '&' : '|'; - my $cat = $cat{$_}; - my $query = join " $op ", map {"$_:$cat"} @words; - push @queries,"($query)"; - } - } - my $search = join ' & ', @queries; - - if ($search){ - my $posts = $DBH->prepare(q{SELECT fp.ftid,u.username,ft.subject - ,ts_headline(fp.message,to_tsquery($2)) AS headline - ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank - FROM forum_boards fb - JOIN forum_threads ft USING (fbid) - JOIN forum_posts fp USING (ftid) - JOIN users u ON fp.uid = u.uid - WHERE fb.fbid IN (SELECT fbid FROM forum_access - WHERE gid IN (SELECT groups($1))) - AND fp.textsearch @@@ to_tsquery($2) - ORDER BY rank DESC - }) or warn $DBH->errstr; - $posts->execute($ND::UID,$search) or warn escapeHTML $DBH->errstr; - my @posts; - while (my $post = $posts->fetchrow_hashref){ - push @posts,$post; - } - $BODY->param(SearchResult => \@posts); - } - return $BODY; -} - -1; diff --git a/lib/ND b/lib/ND index 245cb3b..30f7070 160000 --- a/lib/ND +++ b/lib/ND @@ -1 +1 @@ -Subproject commit 245cb3b058157d719b02be466e1b4deaa72fc986 +Subproject commit 30f707022d89930d31a3d6c83a788f182f65bbaf diff --git a/lib/NDWeb.pm b/lib/NDWeb.pm index 85bbf83..00c8f00 100644 --- a/lib/NDWeb.pm +++ b/lib/NDWeb.pm @@ -5,6 +5,10 @@ use warnings; use Catalyst::Runtime '5.70'; +#Need to preload, otherwise the first hit is slow +use CGI qw/:standard/; +escapeHTML(''); + # Set flags and add plugins for the application # # -Debug: activates the debug mode for very useful log messages diff --git a/lib/NDWeb/Controller/Forum.pm b/lib/NDWeb/Controller/Forum.pm new file mode 100644 index 0000000..d242ed9 --- /dev/null +++ b/lib/NDWeb/Controller/Forum.pm @@ -0,0 +1,431 @@ +package NDWeb::Controller::Forum; + +use strict; +use warnings; +use parent 'Catalyst::Controller'; + +use NDWeb::Include; + +=head1 NAME + +NDWeb::Controller::Forum - Catalyst Controller + +=head1 DESCRIPTION + +Catalyst Controller. + +=head1 METHODS + +=cut + +=head2 index + +=cut + +sub index :Path :Args(0) { + my ( $self, $c ) = @_; + my $dbh = $c->model; + + my $boards = $dbh->prepare(q{SELECT fcid,category,fb.fbid,fb.board + ,count(NULLIF(COALESCE(fp.fpid::BOOLEAN,FALSE) + AND COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread + ,date_trunc('seconds',max(fp.time)::timestamp) as last_post + FROM forum_categories + JOIN forum_boards fb USING (fcid) + LEFT OUTER JOIN forum_threads ft USING (fbid) + LEFT OUTER JOIN forum_posts fp USING (ftid) + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv USING (ftid) + WHERE EXISTS (SELECT fbid FROM forum_access WHERE fbid = fb.fbid AND gid IN (SELECT groups($1))) + GROUP BY fcid,category,fb.fbid, fb.board + ORDER BY fcid,fb.fbid + }); + $boards->execute($c->stash->{UID}); + + my @categories; + my $category = {fcid => 0}; + while (my $board = $boards->fetchrow_hashref){ + if ($category->{fcid} != $board->{fcid}){ + $category = {fcid => $board->{fcid}, category => $board->{category}}; + push @categories,$category; + } + push @{$category->{boards}},$board; + } + $c->stash(categories => \@categories); +} + +sub allUnread : Local { + my ( $self, $c ) = @_; + my $dbh = $c->model; + + my $threads = $dbh->prepare(q{SELECT fcid,category,fbid,board,ft.ftid,u.username,ft.subject, + count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts, + date_trunc('seconds',max(fp.time)::timestamp) as last_post, + min(fp.time)::date as posting_date, ft.sticky + FROM forum_categories fc + JOIN forum_boards fb USING (fcid) + JOIN forum_threads ft USING (fbid) + JOIN forum_posts fp USING (ftid) + JOIN users u ON u.uid = ft.uid + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $1) ftv ON ftv.ftid = ft.ftid + WHERE fbid > 0 AND + fb.fbid IN (SELECT fbid FROM forum_access WHERE gid IN (SELECT groups($1))) + GROUP BY fcid,category,fbid,board,ft.ftid, ft.subject,ft.sticky,u.username + HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1 + ORDER BY fcid,fbid,sticky DESC,last_post DESC + }); + + $threads->execute($c->stash->{UID}); + my @categories; + my $category = {fcid => 0}; + my $board = {fbid => 0}; + while (my $thread = $threads->fetchrow_hashref){ + if ($category->{fcid} != $thread->{fcid}){ + $category = {fcid => $thread->{fcid}, category => $thread->{category}}; + push @categories,$category; + } + if ($board->{fbid} != $thread->{fbid}){ + $board = {fbid => $thread->{fbid}, board => $thread->{board}}; + push @{$category->{boards}},$board; + } + delete $thread->{fcid}; + delete $thread->{fbid}; + delete $thread->{category}; + delete $thread->{board}; + push @{$board->{threads}},$thread; + } + $c->stash(categories => \@categories); + $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef)); +} + + +sub search : Local { + my ( $self, $c ) = @_; + + my $dbh = $c->model; + + my @queries; + if ($c->req->param('search')){ + push @queries,'('.$c->req->param('search').')'; + } + my %cat = (body => 'D', topic => 'A', author => 'B'); + for ('body','topic','author'){ + if ($c->req->param($_)){ + my @words = split /\W+/,$c->req->param($_); + my $op = $c->req->param('all'.$_) ? '&' : '|'; + my $cat = $cat{$_}; + my $query = join " $op ", map {"$_:$cat"} @words; + push @queries,"($query)"; + } + } + my $search = join ' & ', @queries; + + if ($search){ + my $posts = $dbh->prepare(q{SELECT fp.ftid,u.username,ft.subject + ,ts_headline(fp.message,to_tsquery($2)) AS headline + ,ts_rank_cd(fp.textsearch, to_tsquery($2),32) AS rank + FROM forum_boards fb + JOIN forum_threads ft USING (fbid) + JOIN forum_posts fp USING (ftid) + JOIN users u ON fp.uid = u.uid + WHERE fb.fbid IN (SELECT fbid FROM forum_access + WHERE gid IN (SELECT groups($1))) + AND fp.textsearch @@@ to_tsquery($2) + ORDER BY rank DESC + }); + eval { + $posts->execute($c->stash->{UID},$search); + my @posts; + while (my $post = $posts->fetchrow_hashref){ + push @posts,$post; + } + $c->stash(searchresults => \@posts); + }; + if ($@){ + $c->stash( searcherror => $dbh->errstr); + } + } + +} + + +sub board : Local { + my ( $self, $c, $board ) = @_; + my $dbh = $c->model; + + $c->stash(time => $dbh->selectrow_array('SELECT now()::timestamp',undef)); + + $c->forward('findBoard'); + $board = $c->stash->{board}; + + my $threads = $dbh->prepare(q{SELECT ft.ftid,u.username,ft.subject + ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread,count(fp.fpid) AS posts + ,date_trunc('seconds',max(fp.time)::timestamp) as last_post + ,min(fp.time)::date as posting_date, ft.sticky + FROM forum_threads ft + JOIN forum_posts fp USING (ftid) + JOIN users u ON u.uid = ft.uid + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid + WHERE ft.fbid = $1 + GROUP BY ft.ftid, ft.subject,ft.sticky,u.username + ORDER BY sticky DESC,last_post DESC + }); + $threads->execute($board->{fbid},$c->stash->{UID}); + my @threads; + while (my $thread = $threads->fetchrow_hashref){ + push @threads,$thread; + } + $c->stash(threads => \@threads); + + if ($board->{moderate}){ + my $categories = $dbh->prepare(q{SELECT fcid,category FROM forum_categories ORDER BY fcid}); + my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post + FROM forum_boards fb NATURAL JOIN forum_access fa + WHERE fb.fcid = $1 AND + gid IN (SELECT groups($2)) + GROUP BY fb.fbid,fb.board + ORDER BY fb.fbid + }); + $categories->execute; + my @categories; + while (my $category = $categories->fetchrow_hashref){ + $boards->execute($category->{fcid},$c->stash->{UID}); + + my @boards; + while (my $b = $boards->fetchrow_hashref){ + next if (not $b->{post} or $b->{fbid} == $board->{fbid}); + push @boards,$b; + } + $category->{boards} = \@boards; + push @categories,$category if @boards; + } + $c->stash(categories => \@categories); + } +} + + +sub thread : Local { + my ( $self, $c, $thread ) = @_; + my $dbh = $c->model; + + $c->forward('findThread'); + $thread = $c->stash->{thread}; + + my $posts = $dbh->prepare(q{ + SELECT u.username,date_trunc('seconds',fp.time::timestamp) AS time + ,fp.message,COALESCE(fp.time > ftv.time,TRUE) AS unread + FROM forum_threads ft + JOIN forum_posts fp USING (ftid) + JOIN users u ON u.uid = fp.uid + LEFT OUTER JOIN + (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid + WHERE ft.ftid = $1 + ORDER BY fp.time ASC + }); + $posts->execute($c->stash->{thread}->{ftid},$c->stash->{UID}); + + my @posts; + while (my $post = $posts->fetchrow_hashref){ + $post->{message} = parseMarkup($post->{message}); + push @posts,$post; + } + + $c->stash(posts => \@posts); + $c->forward('markThreadAsRead') if $c->user_exists; +} + + +sub markBoardAsRead : Local { + my ( $self, $c, $board, $time ) = @_; + my $dbh = $c->model; + + $c->forward('findBoard'); + $board = $c->stash->{board}; + + my $threads = $dbh->prepare(q{SELECT ft.ftid,ft.subject + ,count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) AS unread + ,count(fp.fpid) AS posts, max(fp.time)::timestamp as last_post + FROM forum_threads ft + JOIN forum_posts fp USING (ftid) + LEFT OUTER JOIN (SELECT * FROM forum_thread_visits WHERE uid = $2) ftv ON ftv.ftid = ft.ftid + WHERE ft.fbid = $1 AND fp.time <= $3 + GROUP BY ft.ftid, ft.subject + HAVING count(NULLIF(COALESCE(fp.time > ftv.time,TRUE),FALSE)) >= 1 + }); + $threads->execute($board->{fbid},$c->user->id,$time); + $dbh->begin_work; + while (my $thread = $threads->fetchrow_hashref){ + $c->forward('markThreadAsRead',[$thread->{ftid}]); + } + $dbh->commit; + $c->res->redirect($c->req->referer); +} + +sub markThreadAsRead : Private { + my ( $self, $c, $thread ) = @_; + my $dbh = $c->model; + + my $rows = $dbh->do(q{UPDATE forum_thread_visits SET time = now() + WHERE uid = $1 AND ftid = $2 + },undef,$c->user->id,$thread); + if ($rows == 0){ + $dbh->do(q{INSERT INTO forum_thread_visits (uid,ftid) + VALUES ($1,$2)} + ,undef,$c->user->id,$thread); + } +} + +sub moveThreads : Local { + my ( $self, $c, $board ) = @_; + my $dbh = $c->model; + + $c->forward('findBoard',[$c->req->param('board')]); + my $toboard = $c->stash->{board}; + unless ($toboard->{moderate}){ + $c->acl_access_denied('test',$c->action,'No moderator access for target board.') + } + + $c->forward('findBoard'); + $board = $c->stash->{board}; + unless ($board->{moderate}){ + $c->acl_access_denied('test',$c->action,'No moderator access for source board.') + } + + my $log = "Moved these threads:\n\n"; + $dbh->begin_work; + my $moveThread = $dbh->prepare(q{UPDATE forum_threads SET fbid = $1 WHERE ftid = $2 AND fbid = $3}); + for my $param ($c->req->param){ + if ($param =~ /t:(\d+)/){ + $moveThread->execute($toboard->{fbid},$1,$board->{fbid}); + if ($moveThread->rows > 0){ + $log .= "$1\n"; + } + } + } + + $log .= "\nFrom board: $board->{board} ($board->{fbid})"; + $log .= "\nTo board: $toboard->{board} ($toboard->{fbid})"; + $dbh->do(q{INSERT INTO forum_posts (ftid,uid,message) + VALUES((SELECT ftid FROM users WHERE uid = $1),$1,$2) + }, undef, $c->user->id, $log); + $dbh->commit; + + $c->res->redirect($c->uri_for('board',$board->{fbid})); +} + +sub newThread : Local { + my ( $self, $c, $board ) = @_; + my $dbh = $c->model; + + $c->forward('findBoard'); + $board = $c->stash->{board}; + + unless ($c->stash->{board}->{post}){ + $c->acl_access_denied('test',$c->action,'No post access to board.') + } + + my $insert = $dbh->prepare(q{INSERT INTO forum_threads (ftid,fbid,subject,uid) + VALUES(DEFAULT,$1,$2,$3) RETURNING (ftid); + }); + + $insert->execute($board->{fbid},html_escape($c->req->param('subject')),$c->stash->{UID}); + my $thread = $insert->fetchrow; + $insert->finish; + $c->forward('addPost',[$thread]); + +} + +sub addPost : Local { + my ( $self, $c, $thread ) = @_; + my $dbh = $c->model; + + if ($c->req->param('cmd') eq 'Submit'){ + $c->forward('findThread'); + unless ($c->stash->{thread}->{post}){ + $c->acl_access_denied('test',$c->action,'No post access to board.') + } + $c->forward('insertPost'); + $c->res->redirect($c->uri_for('thread',$thread)); + }elsif ($c->req->param('cmd') eq 'Preview'){ + $c->forward('thread'); + $c->forward('previewPost'); + $c->stash(template => 'forum/thread.tt2'); + } +} + +sub setSticky : Local { + my ( $self, $c, $thread, $sticky ) = @_; + my $dbh = $c->model; + + $c->forward('findThread'); + unless ($c->stash->{thread}->{moderate}){ + $c->acl_access_denied('test',$c->action,'No moderator access to board.') + } + + $dbh->do(q{UPDATE forum_threads SET sticky = $2 WHERE ftid = $1} + , undef,$thread, $sticky); + $c->res->redirect($c->uri_for('thread',$thread)); +} + +sub findThread : Private { + my ( $self, $c, $thread ) = @_; + my $dbh = $c->model; + my $findThread = $dbh->prepare(q{SELECT ft.ftid,ft.subject, bool_or(fa.post) AS post + , bool_or(fa.moderate) AS moderate,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category + FROM forum_boards fb + NATURAL JOIN forum_access fa + NATURAL JOIN forum_threads ft + NATURAL JOIN forum_categories fc + WHERE ft.ftid = $1 AND gid IN (SELECT groups($2)) + GROUP BY ft.ftid,ft.subject,ft.fbid,fb.board,fb.fcid,ft.sticky,fc.category + }); + $thread = $dbh->selectrow_hashref($findThread,undef,$thread,$c->stash->{UID}); + $c->stash(thread => $thread); +} + +sub findBoard : Private { + my ( $self, $c, $board ) = @_; + my $dbh = $c->model; + + my $boards = $dbh->prepare(q{SELECT fb.fbid,fb.board, bool_or(fa.post) AS post, bool_or(fa.moderate) AS moderate,fb.fcid, fc.category + FROM forum_boards fb + NATURAL JOIN forum_access fa + NATURAL JOIN forum_categories fc + WHERE fb.fbid = $1 AND + gid IN (SELECT groups($2)) + GROUP BY fb.fbid,fb.board,fb.fcid,fc.category + }); + $board = $dbh->selectrow_hashref($boards,undef,$board,$c->stash->{UID}); + + $c->stash(board => $board); +} + +sub previewPost : Private { + my ( $self, $c) = @_; + push @{$c->stash->{posts}}, { + unread => 1, + username => 'PREVIEW', + message => parseMarkup(html_escape $c->req->param('message')), + }; + $c->stash(previewMessage => html_escape $c->req->param('message')); +} + +sub insertPost : Private { + my ( $self, $c, $thread ) = @_; + my $dbh = $c->model; + + my $insert = $dbh->prepare(q{INSERT INTO forum_posts (ftid,message,uid) + VALUES($1,$2,$3)}); + $insert->execute($thread,html_escape($c->req->param('message')),$c->stash->{UID}); +} + +=head1 AUTHOR + +Michael Andreen (harv@ruin.nu) + +=head1 LICENSE + +GPL 2.0, or later. + +=cut + +1; diff --git a/lib/NDWeb/Include.pm b/lib/NDWeb/Include.pm index e35567a..ff5a882 100644 --- a/lib/NDWeb/Include.pm +++ b/lib/NDWeb/Include.pm @@ -22,11 +22,16 @@ use strict; use warnings; require Exporter; use BBCode::Parser; +use CGI qw/:standard/; our @ISA = qw/Exporter/; our @EXPORT = qw/parseMarkup min max - alliances intelquery /; + alliances intelquery html_escape/; + +sub html_escape($) { + return CGI::escapeHTML @_; +} sub parseMarkup ($) { my ($text) = @_; diff --git a/root/src/forum/allUnread.tt2 b/root/src/forum/allUnread.tt2 new file mode 100644 index 0000000..26f9724 --- /dev/null +++ b/root/src/forum/allUnread.tt2 @@ -0,0 +1,33 @@ +[% META title = 'Forum unread posts' %] + +

All new posts

+ + + + + + + + + +[% FOR category IN categories %] + + [% FOR board IN category.boards %] + + + + + + [% FOR thread IN board.threads %] + + + +  [% thread.subject %]  + + + + + [% END %] + [% END %] +[% END %] +
UnreadTotalSubjectPostedCreatorLast post
[% category.category %]
[% board.board %]Mark threads as read
[% thread.unread %][% thread.posts %][% thread.posting_date %][% thread.username %][% thread.last_post %]
diff --git a/root/src/forum/board.tt2 b/root/src/forum/board.tt2 new file mode 100644 index 0000000..f158e65 --- /dev/null +++ b/root/src/forum/board.tt2 @@ -0,0 +1,56 @@ +[% META title = 'Forum board' %] + +

[% board.category %] - [% board.board %]

+ +[% IF UID > 0 %]

Mark threads as read

[% END %] + +
+ + + + + + + + + +[% FOR thread IN threads %] + + + +  [% thread.subject %]  + + + + [% IF board.moderate %] + + [% END %] + +[% END %] +
UnreadTotalSubjectPostedCreatorLast post
[% thread.unread %][% thread.posts %][% thread.posting_date %][% thread.username %][% thread.last_post %]
+ +[% IF board.moderate %] +

Move checked threads to: + + +

+[% END %] +
+ +[% IF board.post %] +
New Thread +

Subject:

+ +
+ + +
+[% END %] diff --git a/root/src/forum/index.tt2 b/root/src/forum/index.tt2 new file mode 100644 index 0000000..51a941d --- /dev/null +++ b/root/src/forum/index.tt2 @@ -0,0 +1,19 @@ +[% META title = 'Forum overview' %] + + + + + + + +[% FOR category IN categories %] + + [% FOR board IN category.boards %] + + + + + + [% END %] +[% END %] +
UnreadBoardLast post
[% category.category %]
[% board.unread %][% board.board %][% board.last_post %]
diff --git a/root/src/forum/search.tt2 b/root/src/forum/search.tt2 new file mode 100644 index 0000000..fdb338f --- /dev/null +++ b/root/src/forum/search.tt2 @@ -0,0 +1,53 @@ +[% META title = 'Forum search' %] + +
+ +
Topic + Find posts with these words in the thread's topic
+
+ Require all words:
+
+ +
Body + Find posts with these words in the body
+
+ Require all words:
+
+ +
Usernames + Find posts written by one these users
+
+
+ +
Custom search + Use | (OR) or & (AND) to separate words. Word:A searches for Word in + topic and Word:B searches for Word as author. 'Two words' to search for a + longer string. Word:D limits the search to just the message body. +

+
+

+
+ +[% IF searcherror %] +

Could not search, because of:

+

[% searcherror | html %]

+[% END %] + +[% IF searchresults %] + + + + + + + + [% FOR post IN searchresults %] + + + + + + + [% END %] +
ThreadAuthorMessageRank
 [% post.subject %] [% post.username %][% post.headline %][% post.rank %]
+[% END %] diff --git a/root/src/forum/thread.tt2 b/root/src/forum/thread.tt2 new file mode 100644 index 0000000..48fff18 --- /dev/null +++ b/root/src/forum/thread.tt2 @@ -0,0 +1,28 @@ +[% META title = 'Forum thread' %] +

[% thread.subject %]

+

[% thread.category %] - [% thread.board %]

+[% IF thread.moderate %] +Make this thread [% thread.sticky ? 'unsticky' : 'sticky' %] +[% END %] + +[% createNewPostHr = 1 %] +[% FOR post IN posts %] + [% IF createNewPostHr AND post.unread%] + [% createNewPostHr = 0 %] +
+

New posts below:

+ [% END %] +
+[% post.username %] : [% post.time %] + [% post.message %] +
+[% END %] + +[% IF thread.post %] +
New Reply + +
+ + +
+[% END %] diff --git a/t/controller_Forum.t b/t/controller_Forum.t new file mode 100644 index 0000000..e737344 --- /dev/null +++ b/t/controller_Forum.t @@ -0,0 +1,10 @@ +use strict; +use warnings; +use Test::More tests => 3; + +BEGIN { use_ok 'Catalyst::Test', 'NDWeb' } +BEGIN { use_ok 'NDWeb::Controller::Forum' } + +ok( request('/forum')->is_success, 'Request should succeed' ); + + diff --git a/templates/forum.tmpl b/templates/forum.tmpl deleted file mode 100644 index 394264d..0000000 --- a/templates/forum.tmpl +++ /dev/null @@ -1,116 +0,0 @@ -

 - -

- - -- - -

- - - -Make this thread - - - -

Mark threads as read

-
- - - - - - - - - - - - - - - - - - - - - -
UnreadTotalSubjectPostedCreatorLast post
class="sticky">  
- -Move checked threads to: - - -
- - -
New Thread -

Subject:

- - - -
- - -
- - - - - - - - - - - - - - - - - - - -
UnreadBoardLast post
- - -

All new posts

- - - - - - - - - - - - - - - - - - - - - - class="sticky">   - - - - - - - -
UnreadTotalSubjectPostedCreatorLast post
Mark threads as read
- diff --git a/templates/forum/search.tmpl b/templates/forum/search.tmpl deleted file mode 100644 index 43890f4..0000000 --- a/templates/forum/search.tmpl +++ /dev/null @@ -1,46 +0,0 @@ -
- -
Topic - Find posts with these words in the thread's topic
-
- Require all words:
-
- -
Body - Find posts with these words in the body
-
- Require all words:
-
- -
Usernames - Find posts written by one these users
-
-
- -
Custom search - Use | (OR) or & (AND) to separate words. Word:A searches for Word in - topic and Word:B searches for Word as author. 'Two words' to search for a - longer string. Word:D limits the search to just the message body. -

-
- -
- - - - - - - - - - - - - - - - - -
ThreadAuthorMessageRank
  
- diff --git a/templates/viewthread.tmpl b/templates/viewthread.tmpl deleted file mode 100644 index 0bf6719..0000000 --- a/templates/viewthread.tmpl +++ /dev/null @@ -1,20 +0,0 @@ - - -
-

New posts below:

- -
- : - -
- - -
New Reply - - - -
- - -
- -- 2.39.2