Index: openacs-4/packages/forums/forums.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/forums.info,v diff -u -r1.8 -r1.9 --- openacs-4/packages/forums/forums.info 25 Jun 2002 19:32:59 -0000 1.8 +++ openacs-4/packages/forums/forums.info 2 Jul 2002 02:00:01 -0000 1.9 @@ -20,7 +20,6 @@ - @@ -33,6 +32,8 @@ + + @@ -48,6 +49,8 @@ + + @@ -99,6 +102,10 @@ + + + + Index: openacs-4/packages/forums/sql/oracle/forums-search-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-search-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/oracle/forums-search-create.sql 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,261 @@ +-- +-- Copyright (C) 2001, 2002 OpenForce, Inc. +-- +-- This file is part of dotLRN. +-- +-- dotLRN 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. +-- +-- dotLRN 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. +-- + +-- +-- Support for searching of forum messages +-- +-- @author yon@openforce.net +-- @creation-date 2002-07-01 +-- @version $Id: forums-search-create.sql,v 1.1 2002/07/02 02:00:01 yon Exp $ +-- + +-- IMPORTANT: +-- replace all instances of the string "yon" below this line with your schema +-- user and schema password accordingly. also, replace the "connect +-- ctxsys/ctxsys" statement with the appropriate values for your system. need +-- to figure out how to do this in a better way. + +-- as normal user +create or replace procedure index_message ( + rid in rowid, + tlob in out NOCOPY clob +) +is + offset number := 1; +begin + + for row in (select forums_messages.subject, + forums_messages.content, + persons.first_names, + persons.last_name + from forums_messages, + persons + where forums_messages.rowid = rid + and forums_messages.user_id = persons.person_id) + loop + dbms_lob.writeappend(tlob, length(row.subject) + 1, ' ' || row.subject); + dbms_lob.append(tlob, row.content); + dbms_lob.writeappend(tlob, length(row.first_names) + 1, ' ' || row.first_names); + dbms_lob.writeappend(tlob, length(row.last_name) + 1, ' ' || row.last_name); + end loop; + +end; +/ +show errors + +-- as ctxsys +connect ctxsys/ctxsys; + +create or replace procedure s_index_message ( + rid in rowid, + tlob in out NOCOPY clob +) +is +begin + yon.index_message(rid, tlob); +end; +/ +show errors + +grant execute on s_index_message to yon; + +-- as normal user +connect yon/yon; + +execute ctx_ddl.create_preference('forums_user_datastore', 'user_datastore'); +execute ctx_ddl.set_attribute('forums_user_datastore', 'procedure', 's_index_message'); + +create index forums_content_idx + on forums_messages (content) + indextype is ctxsys.context + parameters ('datastore forums_user_datastore'); + +declare + v_job number; +begin + dbms_job.submit( + job => v_job, + what => 'ctx_ddl.sync_index(''forums_content_idx'');', + interval => 'sysdate + 1/24' + ); +end; +/ +show errors + +-- ripped off from site-wide-search + +-- if we pass in a very long string to im_convert, we will end up with internal +-- strings that are too long. Because this is relatively hard to debug, +-- we wrote im_convert_length_check to throw a more appropriate error message +-- Note that we raise an exception because passing such a long query to +-- interMedia is pretty slow. Alternative to the application error are to +-- 1. return the string as is +-- 2. increase the max length from 256 to 1024 +-- mbryzek@arsdigita.com, 7/6/2000 +create or replace procedure im_convert_length_check ( + p_string IN varchar2, + p_number_chars_to_append IN number, + p_max_length IN number, + p_variable_name IN varchar2 +) +is +begin + if nvl(length(p_string),0) + p_number_chars_to_append > p_max_length then + raise_application_error(-20000, 'Variable "' || p_variable_name || '" exceeds ' || p_max_length || ' character declaration'); + end if; +end; +/ +show errors; + +-- Query to take free text user entered query and from it into something +-- that will make interMedia happy. Provided by Oracle. +create or replace function im_convert ( + query in varchar2 default null +) return varchar2 +is + i number :=0; + len number :=0; + char varchar2(1); + minusString varchar2(256) := ''; + plusString varchar2(256) := ''; + mainString varchar2(256) := ''; + mainAboutString varchar2(500) := ''; + finalString varchar2(500) := ''; + hasMain number :=0; + hasPlus number :=0; + hasMinus number :=0; + token varchar2(256); + tokenStart number :=1; + tokenFinish number :=0; + inPhrase number :=0; + inPlus number :=0; + inWord number :=0; + inMinus number :=0; + completePhrase number :=0; + completeWord number :=0; + code number :=0; +begin + + len := length(query); + + -- we iterate over the string to find special web operators + for i in 1..len loop + char := substr(query,i,1); + if(char = '"') then + if(inPhrase = 0) then + inPhrase := 1; + tokenStart := i; + else + inPhrase := 0; + completePhrase := 1; + tokenFinish := i-1; + end if; + elsif(char = ' ') then + if(inPhrase = 0) then + completeWord := 1; + tokenFinish := i-1; + end if; + elsif(char = '+') then + inPlus := 1; + tokenStart := i+1; + elsif((char = '-') and (i = tokenStart)) then + inMinus :=1; + tokenStart := i+1; + end if; + + if(completeWord=1) then + token := '{ '||substr(query,tokenStart,tokenFinish-tokenStart+1)||' }'; + if(inPlus=1) then + im_convert_length_check(plusString, 4+length(token), 256, 'plusString'); + plusString := plusString||','||token||'*10'; + hasPlus :=1; + elsif(inMinus=1) then + im_convert_length_check(minusString, 4+length(token), 256, 'minusString'); + minusString := minusString||'OR '||token||' '; + hasMinus :=1; + else + im_convert_length_check(mainString, 6+length(token), 256, 'mainString'); + mainString := mainString||' NEAR '||token; + im_convert_length_check(mainAboutString, 1+length(token), 500, 'mainAboutString'); + mainAboutString := mainAboutString||' '||token; + hasMain :=1; + end if; + tokenStart :=i+1; + tokenFinish :=0; + inPlus := 0; + inMinus :=0; + end if; + completePhrase := 0; + completeWord :=0; + end loop; + + -- find the last token + token := '{ '||substr(query,tokenStart,len-tokenStart+1)||' }'; + if(inPlus=1) then + im_convert_length_check(plusString, 4+length(token), 256, 'plusString'); + plusString := plusString||','||token||'*10'; + hasPlus :=1; + elsif(inMinus=1) then + im_convert_length_check(minusString, 4+length(token), 256, 'minusString'); + minusString := minusString||'OR '||token||' '; + hasMinus :=1; + else + im_convert_length_check(mainString, 6+length(token), 256, 'mainString'); + mainString := mainString||' NEAR '||token; + im_convert_length_check(mainAboutString, 1+length(token), 500, 'mainAboutString'); + mainAboutString := mainAboutString||' '||token; + hasMain :=1; + end if; + + mainString := substr(mainString,6,length(mainString)-5); + mainAboutString := replace(mainAboutString,'{',' '); + mainAboutString := replace(mainAboutString,'}',' '); + mainAboutString := replace(mainAboutString,')',' '); + mainAboutString := replace(mainAboutString,'(',' '); + plusString := substr(plusString,2,length(plusString)-1); + minusString := substr(minusString,4,length(minusString)-4); + + -- let's just check once for the length of finalString... note this uses the + -- longest possible string that is created in the rest of this function + im_convert_length_check(finalString, nvl(length(mainString),0) + nvl(length(mainAboutString),0) + nvl(length(minusString),0) + nvl(length(plusString),0) + 30, 500, 'finalString'); + + -- we find the components present and then process them based on the specific combinations + code := hasMain*4+hasPlus*2+hasMinus; + if(code = 7) then + finalString := '('||plusString||','||mainString||'*2.0,about('||mainAboutString||')*0.5) NOT ('||minusString||')'; + elsif (code = 6) then + finalString := plusString||','||mainString||'*2.0'||',about('||mainAboutString||')*0.5'; + elsif (code = 5) then + finalString := '('||mainString||',about('||mainAboutString||')) NOT ('||minusString||')'; + elsif (code = 4) then + finalString := mainString; + finalString := replace(finalString,'*1,',NULL); + finalString := '('||finalString||')*2.0,about('||mainAboutString||')'; + elsif (code = 3) then + finalString := '('||plusString||') NOT ('||minusString||')'; + elsif (code = 2) then + finalString := plusString; + elsif (code = 1) then + -- not is a binary operator for intermedia text + finalString := 'totallyImpossibleString'||' NOT ('||minusString||')'; + elsif (code = 0) then + finalString := ''; + end if; + + return finalString; +end; +/ +show errors; Index: openacs-4/packages/forums/sql/oracle/forums-search-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/sql/oracle/forums-search-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/sql/oracle/forums-search-drop.sql 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,61 @@ +-- +-- Copyright (C) 2001, 2002 OpenForce, Inc. +-- +-- This file is part of dotLRN. +-- +-- dotLRN 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. +-- +-- dotLRN 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. +-- + +-- +-- Support for searching of forum messages +-- +-- @author yon@openforce.net +-- @creation-date 2002-07-01 +-- @version $Id: forums-search-drop.sql,v 1.1 2002/07/02 02:00:01 yon Exp $ +-- + +-- IMPORTANT: +-- replace all instances of the string "yon" below this line with your schema +-- user and schema password accordingly. also, replace the "connect +-- ctxsys/ctxsys" statement with the appropriate values for your system. need +-- to figure out how to do this in a better way. + +-- as normal user + +drop function im_convert; +drop procedure im_convert_length_check; + +declare +begin + for row in (select job + from user_jobs + where what like '%forums_content_idx%') + loop + dbms_job.remove(job => row.job); + end loop; +end; +/ +show errors + +drop index forums_content_idx; + +execute ctx_ddl.unset_attribute('forums_user_datastore', 'procedure'); +execute ctx_ddl.drop_preference('forums_user_datastore'); + +-- as ctxsys +connect ctxsys/ctxsys; + +drop procedure s_index_message; + +-- as normal user +connect yon/yon; + +drop procedure index_message; Index: openacs-4/packages/forums/tcl/forums-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/tcl/forums-procs.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/tcl/forums-procs.tcl 2 Jun 2002 04:56:45 -0000 1.3 +++ openacs-4/packages/forums/tcl/forums-procs.tcl 2 Jul 2002 02:00:01 -0000 1.4 @@ -98,4 +98,5 @@ } { db_dml update_forum_disabled_p {} } + } Index: openacs-4/packages/forums/www/forum-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/forum-view.adp,v diff -u -r1.9 -r1.10 --- openacs-4/packages/forums/www/forum-view.adp 25 Jun 2002 18:00:32 -0000 1.9 +++ openacs-4/packages/forums/www/forum-view.adp 2 Jul 2002 02:00:01 -0000 1.10 @@ -8,7 +8,7 @@ - + [ @@ -24,6 +24,12 @@ ]

+ + + + Search:  + + Index: openacs-4/packages/forums/www/forum-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/forum-view.tcl,v diff -u -r1.3 -r1.4 --- openacs-4/packages/forums/www/forum-view.tcl 5 Jun 2002 19:14:39 -0000 1.3 +++ openacs-4/packages/forums/www/forum-view.tcl 2 Jul 2002 02:00:01 -0000 1.4 @@ -28,6 +28,20 @@ set post_p 1 } +form create search -action search + +element create search search_text \ + -label Search \ + -datatype text \ + -widget text \ + -html {size 60} + +element create search forum_id \ + -label ForumID \ + -datatype text \ + -widget hidden \ + -value $forum_id + # Get forum data forum::get -forum_id $forum_id -array forum Index: openacs-4/packages/forums/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/index.adp,v diff -u -r1.8 -r1.9 --- openacs-4/packages/forums/www/index.adp 25 Jun 2002 15:38:17 -0000 1.8 +++ openacs-4/packages/forums/www/index.adp 2 Jul 2002 02:00:01 -0000 1.9 @@ -6,13 +6,21 @@ - - + + -
+ + [ New Forum ] + +    + + + Search:  + +
Index: openacs-4/packages/forums/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/index.tcl,v diff -u -r1.2 -r1.3 --- openacs-4/packages/forums/www/index.tcl 3 Jun 2002 04:39:05 -0000 1.2 +++ openacs-4/packages/forums/www/index.tcl 2 Jul 2002 02:00:01 -0000 1.3 @@ -12,6 +12,20 @@ set user_id [ad_verify_and_get_user_id] set admin_p [permission::permission_p -party_id $user_id -object_id $package_id -privilege admin] +form create search -action search + +element create search search_text \ + -label Search \ + -datatype text \ + -widget text \ + -html {size 60} + +element create search forum_id \ + -label ForumID \ + -datatype text \ + -widget hidden \ + -value "" + db_multirow forums select_forums {} set context_bar {} Index: openacs-4/packages/forums/www/message-view.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/message-view.adp,v diff -u -r1.5 -r1.6 --- openacs-4/packages/forums/www/message-view.adp 5 Jun 2002 19:25:37 -0000 1.5 +++ openacs-4/packages/forums/www/message-view.adp 2 Jul 2002 02:00:01 -0000 1.6 @@ -4,7 +4,7 @@
- +
+
@notification_chunk@ @@ -14,6 +14,12 @@ ] + + + Search:  + +
Index: openacs-4/packages/forums/www/message-view.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/message-view.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/forums/www/message-view.tcl 5 Jun 2002 19:25:37 -0000 1.5 +++ openacs-4/packages/forums/www/message-view.tcl 2 Jul 2002 02:00:01 -0000 1.6 @@ -23,6 +23,20 @@ # Load up the message information forum::message::get -message_id $message_id -array message +form create search -action search + +element create search search_text \ + -label Search \ + -datatype text \ + -widget text \ + -html {size 60} + +element create search forum_id \ + -label ForumID \ + -datatype text \ + -widget hidden \ + -value $message(forum_id) + # Check if the message is approved if {!${moderate_p} && ![string equal $message(state) approved]} { ad_returnredirect "forum-view?forum_id=$message(forum_id)" Index: openacs-4/packages/forums/www/search-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/Attic/search-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/www/search-oracle.xql 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,41 @@ + + + + oracle8.1.6 + + + + select forums_messages.*, + person.name(forums_messages.user_id) as user_name, + to_char(forums_messages.posting_date, 'Mon DD YYYY HH24:MI:SS') as posting_date, + score(1) as the_score + from forums_messages, + forums_forums + where forums_messages.forum_id = forums_forums.forum_id + and forums_forums.package_id = :package_id + and forums_messages.state = 'approved' + and contains(forums_messages.content, :search_text, 1) > 0 + order by the_score desc, + forums_messages.posting_date desc + + + + + + select forums_messages.*, + person.name(forums_messages.user_id) as user_name, + to_char(forums_messages.posting_date, 'Mon DD YYYY HH24:MI:SS') as posting_date, + score(1) as the_score + from forums_messages, + forums_forums + where forums_forums.forum_id = :forum_id + and forums_forums.package_id = :package_id + and forums_messages.forum_id = forums_forums.forum_id + and forums_messages.state = 'approved' + and contains(forums_messages.content, :search_text, 1) > 0 + order by the_score desc, + forums_messages.posting_date desc + + + + Index: openacs-4/packages/forums/www/search-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/Attic/search-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/www/search-postgresql.xql 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,47 @@ + + + + oracle8.1.6 + + + + select forums_messages.*, + person__name(forums_messages.user_id) as user_name, + to_char(forums_messages.posting_date, 'Mon DD YYYY HH24:MI:SS') as posting_date, + 100 as the_score + from forums_messages, + forums_forums + where forums_messages.forum_id = forums_forums.forum_id + and forums_forums.package_id = :package_id + and forums_messages.state = 'approved' + and ( forums_messages.subject like ('%' || :search_text || '%') + or forums_messages.content like ('%' || :search_text || '%') + or user_name like ('%' || :search_text || '%') + ) + order by the_score desc, + forums_messages.posting_date desc + + + + + + select forums_messages.*, + person__name(forums_messages.user_id) as user_name, + to_char(forums_messages.posting_date, 'Mon DD YYYY HH24:MI:SS') as posting_date, + 100 as the_score + from forums_messages, + forums_forums + where forums_forums.forum_id = :forum_id + and forums_forums.package_id = :package_id + and forums_messages.forum_id = forums_forums.forum_id + and forums_messages.state = 'approved' + and ( forums_messages.subject like ('%' || :search_text || '%') + or forums_messages.content like ('%' || :search_text || '%') + or user_name like ('%' || :search_text || '%') + ) + order by the_score desc, + forums_messages.posting_date desc + + + + Index: openacs-4/packages/forums/www/search.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/search.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/www/search.adp 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,58 @@ + +Search Forums +@context_bar@ + +
+ + + + + + + +
+ + Search:  + +
+ +
+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
SubjectAuthorPosting Date
+ @messages.subject@ + @messages.user_name@@messages.posting_date@
+ No Messages +
+ +
Index: openacs-4/packages/forums/www/search.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/forums/www/search.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/forums/www/search.tcl 2 Jul 2002 02:00:01 -0000 1.1 @@ -0,0 +1,44 @@ +ad_page_contract { + + @author yon@openforce.net + @creation-date 2002-07-01 + @version $Id: search.tcl,v 1.1 2002/07/02 02:00:01 yon Exp $ + +} -query { + {forum_id ""} +} + +set package_id [ad_conn package_id] + +form create search + +element create search search_text \ + -label Search \ + -datatype text \ + -widget text \ + -html {size 60} + +element create search forum_id \ + -label ForumID \ + -datatype text \ + -widget hidden \ + -value $forum_id \ + -optional + +if {[form is_valid search]} { + form get_values search search_text forum_id + + set query search_all_forums + if {![empty_string_p $forum_id]} { + set query search_one_forum + } + + db_multirow messages $query {} + +} else { + set messages:rowcount 0 +} + +set context_bar {Search} + +ad_return_template