From 055f7e12029e85248d4b9ba60d377c1816bce265 Mon Sep 17 00:00:00 2001 From: Michael Andreen Date: Sun, 2 Mar 2008 11:14:17 +0100 Subject: [PATCH] Basic forum search --- NDWeb/Pages/Forum.pm | 9 +++--- NDWeb/Pages/Forum/Search.pm | 59 +++++++++++++++++++++++++++++++++++++ database/search.sql | 40 +++++++++++++++++++++++++ startup.pl | 1 + templates/forum/search.tmpl | 26 ++++++++++++++++ templates/skel.tmpl | 1 + 6 files changed, 132 insertions(+), 4 deletions(-) create mode 100644 NDWeb/Pages/Forum/Search.pm create mode 100644 database/search.sql create mode 100644 templates/forum/search.tmpl diff --git a/NDWeb/Pages/Forum.pm b/NDWeb/Pages/Forum.pm index 76ac472..5951be7 100644 --- a/NDWeb/Pages/Forum.pm +++ b/NDWeb/Pages/Forum.pm @@ -32,7 +32,10 @@ $NDWeb::Page::PAGES{forum} = __PACKAGE__; sub parse { my $self = shift; if ($self->{URI} =~ m{^/.*/allUnread}){ - param('allUnread',1); + $self->{allUnread} = 1; + }elsif ($self->{URI} =~ m{^/.*/search(?:/(.*))?}){ + bless $self, 'NDWeb::Pages::Forum::Search'; + $self->{PAGE} = 'forum/search'; } } @@ -155,7 +158,7 @@ sub render_body { ,undef,$thread->{fcid}) or $ND::ERROR .= p($DBH->errstr); $BODY->param(Category => $category); - }elsif(defined param('allUnread')){ #List threads in this board + }elsif(defined $self->{allUnread}){ #List threads in this board $BODY->param(AllUnread => 1); $BODY->param(Id => $board->{id}); my ($time) = $DBH->selectrow_array('SELECT now()::timestamp',undef); @@ -220,8 +223,6 @@ sub render_body { my ($category) = $DBH->selectrow_array(q{SELECT category FROM forum_categories WHERE fcid = $1} ,undef,$board->{fcid}) or $ND::ERROR .= p($DBH->errstr); $BODY->param(Category => $category); - - }elsif($self->{URI} =~ m{^/.*/search/(.*)}){ #List threads in this board }else{ #List boards $BODY->param(Overview => 1); $categories->execute or $ND::ERROR .= p($DBH->errstr); diff --git a/NDWeb/Pages/Forum/Search.pm b/NDWeb/Pages/Forum/Search.pm new file mode 100644 index 0000000..a0f28ac --- /dev/null +++ b/NDWeb/Pages/Forum/Search.pm @@ -0,0 +1,59 @@ +#************************************************************************** +# 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'; + + if (param('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,param('search')) or warn $DBH->errstr; + my @posts; + while (my $post = $posts->fetchrow_hashref){ + push @posts,$post; + } + $BODY->param(SearchResult => \@posts); + } + return $BODY; +} + +1; diff --git a/database/search.sql b/database/search.sql new file mode 100644 index 0000000..6801ca0 --- /dev/null +++ b/database/search.sql @@ -0,0 +1,40 @@ +ALTER TABLE forum_posts ADD textsearch tsvector; + +UPDATE forum_posts fp SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A') || setweight(to_tsvector(coalesce(u.username,'')), 'B') || setweight(to_tsvector(coalesce(fp.message,'')), 'D') FROM forum_threads ft, users u WHERE fp.ftid = ft.ftid AND u.uid = fp.uid; + +CREATE INDEX forum_posts_textsearch_index ON forum_posts USING gin(textsearch); + +/*CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger" +AS $_X$ + my $query = spi_prepare(q{UPDATE forum_posts fp + SET textsearch = setweight(to_tsvector(coalesce(ft.subject,'')), 'A') + || setweight(to_tsvector(coalesce(u.username,'')), 'B') + || setweight(to_tsvector(coalesce(fp.message,'')), 'D') + FROM forum_threads ft, users u + WHERE fp.ftid = ft.ftid AND u.uid = fp.uid AND fp.fpid = $1},'int4'); + spi_exec_prepared($query,$_TD->{new}{fpid}); + spi_freeplan($query); +$_X$ + LANGUAGE plperl; +*/ +CREATE OR REPLACE FUNCTION update_forum_post() RETURNS "trigger" +AS $_X$ +DECLARE + rec RECORD; +BEGIN + SELECT setweight(to_tsvector(coalesce(ft.subject,'')), 'A') + || setweight(to_tsvector(coalesce(u.username,'')), 'B') AS ts + INTO STRICT rec + FROM forum_threads ft, users u + WHERE NEW.ftid = ft.ftid AND u.uid = NEW.uid; + NEW.textsearch := rec.ts + || setweight(to_tsvector(coalesce(NEW.message,'')), 'D'); + return NEW; +END; +$_X$ + LANGUAGE plpgsql; + +CREATE TRIGGER update_forum_post + BEFORE INSERT OR UPDATE ON forum_posts + FOR EACH ROW + EXECUTE PROCEDURE update_forum_post(); diff --git a/startup.pl b/startup.pl index 89cddb0..45fa8e6 100644 --- a/startup.pl +++ b/startup.pl @@ -48,6 +48,7 @@ use NDWeb::Pages::Resources; use NDWeb::Pages::PlanetNaps; use NDWeb::Pages::Motd; use NDWeb::Pages::Forum; +use NDWeb::Pages::Forum::Search; use NDWeb::Pages::Settings; use NDWeb::Pages::Graph; use NDWeb::Pages::Mail; diff --git a/templates/forum/search.tmpl b/templates/forum/search.tmpl new file mode 100644 index 0000000..f631724 --- /dev/null +++ b/templates/forum/search.tmpl @@ -0,0 +1,26 @@ +
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. +

Search query:

+ +
+ + + + + + + + + + + + + + + + + +
ThreadAuthorMessageRank
  
+ diff --git a/templates/skel.tmpl b/templates/skel.tmpl index fed216c..e2af5c7 100644 --- a/templates/skel.tmpl +++ b/templates/skel.tmpl @@ -19,6 +19,7 @@
  • Settings
  • Forum
  • New posts ()
    • +
  • Forum search

    • Tick:

    Member menu

    -- 2.39.2