Index: openacs-4/packages/search/sql/oracle/load-site-wide-search
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/load-site-wide-search,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/load-site-wide-search 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,48 @@
+#
+# Copyright (C) 2005 MIT
+#
+# 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.
+#
+
+#
+# This is the script to create the site-wide-search package for .LRN
+#
+# @author openacs@dirkgomez.de
+# @version $Id: load-site-wide-search,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+# @creation-date 13-May-2005
+#
+# Ported from ACES.
+#
+# prerequisites:
+
+# Oracle 8.1.7.4 (I have not tried .LRN search with Oracle 9i, but
+# it'll probably work, it won't work with Oracle 10g.)
+# Also consider this Oracle bug.:
+# http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=261880.1
+
+# Call with load-site-wide-search dbuser passwd ctxsyspasswd.
+
+USERNAME=$1
+PASSWORD=$2
+CTXSYS_PASSWORD=$3
+
+# For programming convenience just drop everything
+sqlplus ${USERNAME}/${PASSWORD} @search-tables-drop.sql
+sqlplus ${USERNAME}/${PASSWORD} @search-packages-drop.sql
+sqlplus ${USERNAME}/${PASSWORD} @search-index-drop.sql ${USERNAME} ${PASSWORD}
+
+sqlplus ${USERNAME}/${PASSWORD} @search-tables-create.sql
+sqlplus ${USERNAME}/${PASSWORD} @search-packages-create.sql
+sqlplus ctxsys/${CTXSYS_PASSWORD} @search-ctxsys.sql ${USERNAME} ${PASSWORD}
+sqlplus ${USERNAME}/${PASSWORD} @search-index-create.sql ${USERNAME} ${PASSWORD}
+sqlplus ${USERNAME}/${PASSWORD} @search-from-scratch.sql
Index: openacs-4/packages/search/sql/oracle/search-ctxsys.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/search-ctxsys.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-ctxsys.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,41 @@
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Create ctxsys schema objects for .LRN site-wide search
+--
+-- @author openacs@dirkgomez.de
+-- @version $Id: search-ctxsys.sql,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+-- @creation-date 13-May-2005
+--
+-- Partly ported from ACES.
+
+CREATE OR replace procedure sws_user_proc_&1 ( rid IN ROWID, tlob IN OUT nocopy clob )
+AS
+BEGIN
+ &1..sws_user_datastore_proc(rid, tlob);
+END;
+/
+show errors;
+
+grant execute on sws_user_proc_&1 to &1;
+
+grant ctxapp to &1;
+
+-- stuff to make interMedia faster
+exec ctx_adm.set_parameter('max_index_memory', '1G');
+
+exit;
Index: openacs-4/packages/search/sql/oracle/search-from-scratch.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/search-from-scratch.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-from-scratch.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,49 @@
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Populate .LRN's intermedia index
+--
+-- @author Dirk Gomez
+-- @version $Id: search-from-scratch.sql,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+-- @creation-date 13-May-2005
+--
+
+truncate table site_wide_index;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select message_id, subject, 'a' from forums_messages;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select event_id, name, 'a' from acs_events;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select entry_id, question, 'a' from faq_q_and_as;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select content_id, pretty_name, 'a' from static_portal_content;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select survey_id, name, 'a' from surveys;
+
+commit;
+
+alter index sws_ctx_index rebuild parameters ('sync') ;
+
+exit
+
+
+
Index: openacs-4/packages/search/sql/oracle/search-im-convert.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/search-im-convert.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-im-convert.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,152 @@
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Create database packages for .LRN site-wide search
+--
+-- @author openacs@dirkgomez.de
+-- @version $Id: search-im-convert.sql,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+-- @creation-date 13-May-2005
+
+-- Ported from ACES.
+
+-- Query to take free text user entered query and frob 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
+ plusString := plusString||','||token||'*10';
+ hasPlus :=1;
+ elsif(inMinus=1) then
+ minusString := minusString||'OR '||token||' ';
+ hasMinus :=1;
+ else
+ mainString := mainString||' NEAR '||token;
+ 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
+ plusString := plusString||','||token||'*10';
+ hasPlus :=1;
+ elsif(inMinus=1) then
+ minusString := minusString||'OR '||token||' ';
+ hasMinus :=1;
+ else
+ mainString := mainString||' NEAR '||token;
+ 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);
+
+ -- 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;
+/
+
Index: openacs-4/packages/search/sql/oracle/search-index-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/search-index-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-index-create.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,43 @@
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Create the intermedia index for .LRN site-wide search
+--
+-- @author openacs@dirkgomez.de
+-- @version $Id: search-index-create.sql,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+-- @creation-date 13-May-2005
+--
+-- Partly ported from ACES.
+
+-- create section groups for within clauses
+begin
+ ctx_ddl.create_section_group('swsgroup', 'basic_section_group');
+ ctx_ddl.add_field_section('swsgroup', 'oneline', 'oneline', TRUE);
+end;
+/
+
+-- create intermedia index for site wide index
+begin
+ ctx_ddl.create_preference('sws_user_datastore', 'user_datastore');
+ ctx_ddl.set_attribute('sws_user_datastore', 'procedure', 'sws_user_proc_&1');
+end;
+/
+
+create index sws_ctx_index on site_wide_index (datastore)
+indextype is ctxsys.context parameters ('datastore sws_user_datastore memory 250M section group swsgroup');
+
+exit;
\ No newline at end of file
Index: openacs-4/packages/search/sql/oracle/search-index-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/search-index-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-index-drop.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,32 @@
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Drop the intermedia index for .LRN site-wide search
+--
+-- @author Dirk Gomez
+-- @version $Id: search-index-drop.sql,v 1.1 2005/05/21 11:34:10 dirkg Exp $
+-- @creation-date 13-May-2005
+
+begin
+ ctx_ddl.drop_section_group('swsgroup');
+ ctx_ddl.drop_preference('sws_user_datastore');
+end;
+/
+
+drop index sws_ctx_index;
+
+exit;
Index: openacs-4/packages/search/sql/oracle/search-index-from-scratch.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/Attic/search-index-from-scratch.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/search/sql/oracle/search-index-from-scratch.sql 21 May 2005 11:34:10 -0000 1.1
@@ -0,0 +1,20 @@
+--------Fill the index from scratch
+
+truncate table site_wide_index;
+
+insert into site_wide_index (object_id, object_name, datastore)
+ select message_id, subject, 'a' from forums_messages;
+
+
+commit;
+
+@/web/dotlrn211/packages/search/sql/oracle/search-dirk-imconvert.sql
+
+alter index sws_ctx_index rebuild parameters ('sync') ;
+
+select * from sws_log_messages;
+quit
+exit
+
+
+
Index: openacs-4/packages/search/sql/oracle/search-packages-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/search-packages-create.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/search/sql/oracle/search-packages-create.sql 2 Jul 2002 01:52:38 -0000 1.1
+++ openacs-4/packages/search/sql/oracle/search-packages-create.sql 21 May 2005 11:34:10 -0000 1.2
@@ -1,59 +1,491 @@
--
--- Search Observer
+-- Copyright (C) 2005 MIT
--
+-- 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.
+--
-create or replace package search_observer
-as
+--
+-- Create database packages for .LRN site-wide search
+--
+-- @author Dirk Gomez
+-- @version $Id$
+-- @creation-date 13-May-2005
- procedure enqueue (
- object_id in search_observer_queue.object_id%TYPE,
- event in search_observer_queue.event%TYPE
- );
+-- Partly ported from ACES.
- procedure dequeue (
- object_id in search_observer_queue.object_id%TYPE,
- event_date in search_observer_queue.event_date%TYPE,
- event in search_observer_queue.event%TYPE
- );
+-- The site_wide_search packages holds generally useful
+-- PL/SQL procedures and functions.
-end search_observer;
+create or replace package site_wide_search
+as
+ procedure logger (p_logmessage varchar);
+end site_wide_search;
/
show errors
-create or replace package body search_observer
+create or replace package body site_wide_search
as
+ procedure logger (p_logmessage varchar) is
+ begin
+ insert into sws_log_messages (logmessage) values (p_logmessage);
+ end logger;
+end site_wide_search;
+/
+show errors
- procedure enqueue (
- object_id in search_observer_queue.object_id%TYPE,
- event in search_observer_queue.event%TYPE
- )
- is
- begin
+--------------------------------------------------------
+-- Forum triggers and procedures
+
+create or replace trigger forums_messages_sws_insert_tr
+ after insert on forums_messages for each row
+begin
+ insert into site_wide_index (object_id, object_name, datastore)
+ values (:new.message_id, :new.subject, 'a');
+end;
+/
+show errors
+
+create or replace trigger forums_messages_sws_update_tr
+ after update on forums_messages for each row
+begin
+ update site_wide_index
+ set object_name=:new.subject,
+ datastore='a'
+ where object_id = :new.message_id;
+end;
+/
+show errors
- insert
- into search_observer_queue
- (object_id, event)
- values
- (enqueue.object_id, enqueue.event);
+create or replace trigger forums_messages_sws_delete_tr
+ after delete on forums_messages for each row
+begin
+ delete from site_wide_index
+ where object_id = :old.message_id;
+end;
+/
+show errors
- end enqueue;
+create or replace procedure forums_messages_sws_helper (p_tlob in out nocopy clob, p_object_id in varchar)
+is
+ cursor forums_messages_cursor(v_object_id char) is
+ select subject, content, p.first_names || ' ' || p.last_name as
+ author_name, parties.email
+ from forums_messages fm, persons p, parties
+ where p.person_id = fm.user_id
+ and parties.party_id = p.person_id
+ and fm.message_id = v_object_id;
+begin
+ for forums_messages_record in forums_messages_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if forums_messages_record.subject is not null then
+ dbms_lob.writeappend(p_tlob, length(forums_messages_record.subject) + 1, forums_messages_record.subject || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ dbms_lob.writeappend(p_tlob, length(forums_messages_record.author_name) + 1, forums_messages_record.author_name || ' ');
+ if forums_messages_record.content is not null then
+ dbms_lob.append(p_tlob, forums_messages_record.content);
+ end if;
+ end loop;
+end;
+/
+show errors;
- procedure dequeue (
- object_id in search_observer_queue.object_id%TYPE,
- event_date in search_observer_queue.event_date%TYPE,
- event in search_observer_queue.event%TYPE
- )
- is
- begin
+--------------------------------------------------------
+-- static-portal triggers and procedures
+
+create or replace trigger static_portal_sws_insert_tr
+ after insert on static_portal_content for each row
+begin
+ insert into site_wide_index (object_id, object_name, datastore)
+ values (:new.content_id, :new.pretty_name, 'a');
+end;
+/
+show errors
+
+create or replace trigger static_portal_sws_update_tr
+ after update on static_portal_content for each row
+begin
+ update site_wide_index
+ set object_name=:new.pretty_name,
+ datastore='a'
+ where object_id = :new.content_id;
+end;
+/
+show errors
- delete
- from search_observer_queue
- where object_id = dequeue.object_id
- and event = dequeue.event
- and event_date = dequeue.event_date;
+create or replace trigger static_portal_sws_delete_tr
+ after delete on static_portal_content for each row
+begin
+ delete from site_wide_index
+ where object_id = :old.content_id;
+end;
+/
+show errors
+
+create or replace procedure static_portal_sws_helper (p_tlob in out nocopy clob, p_object_id in varchar)
+is
+ cursor static_portal_content_cursor(v_object_id char) is
+ select pretty_name, body, p.first_names || ' ' || p.last_name as
+ author_name, parties.email
+ from static_portal_content fm, persons p, parties, acs_objects ao
+ where fm.content_id = ao.object_id
+ and p.person_id = ao.creation_user
+ and parties.party_id = p.person_id
+ and fm.content_id = v_object_id;
+begin
+ for static_portal_content_record in static_portal_content_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if static_portal_content_record.pretty_name is not null then
+ dbms_lob.writeappend(p_tlob, length(static_portal_content_record.pretty_name) + 1, static_portal_content_record.pretty_name || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ dbms_lob.writeappend(p_tlob, length(static_portal_content_record.author_name) + 1, static_portal_content_record.author_name || ' ');
+ if static_portal_content_record.body is not null then
+ dbms_lob.append(p_tlob, static_portal_content_record.body);
+ end if;
+ end loop;
+end;
+/
+show errors;
+
+--------------------------------------------------------
+-- ACS-events triggers and procedures
+-- I think only calendar makes use of the acs-events tables.
+
+create or replace trigger acs_events_sws_insert_tr
+ after insert on acs_events for each row
+begin
+ insert into site_wide_index (object_id, object_name, datastore)
+ values (:new.event_id, :new.name, 'a');
+end;
+/
+show errors
+
+create or replace trigger acs_events_sws_update_tr
+ after update on acs_events for each row
+begin
+ update site_wide_index
+ set object_name=:new.name,
+ datastore='a'
+ where object_id = :new.event_id;
+end;
+/
+show errors
- end dequeue;
+create or replace trigger acs_events_sws_delete_tr
+ after delete on acs_events for each row
+begin
+ delete from site_wide_index
+ where object_id = :old.event_id;
+end;
+/
+show errors
-end search_observer;
+create or replace procedure acs_events_sws_helper (p_tlob in out nocopy clob, p_object_id in varchar)
+is
+ cursor acs_events_cursor(v_object_id char) is
+ select name, description, p.first_names || ' ' || p.last_name as
+ author_name, parties.email
+ from acs_events ae, acs_objects ao, persons p, parties
+ where p.person_id = ao.creation_user
+ and ao.object_id = v_object_id
+ and parties.party_id = p.person_id
+ and ae.event_id = v_object_id;
+begin
+ for acs_events_record in acs_events_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if acs_events_record.name is not null then
+ dbms_lob.writeappend(p_tlob, length(acs_events_record.name) + 1, acs_events_record.name || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ dbms_lob.writeappend(p_tlob, length(acs_events_record.author_name) + 1, acs_events_record.author_name || ' ');
+ if acs_events_record.description is not null then
+ dbms_lob.writeappend(p_tlob, length(acs_events_record.description) + 1, acs_events_record.description || ' ');
+ end if;
+ end loop;
+end;
/
+show errors;
+
+--------------------------------------------------------
+-- FAQ triggers and procedures
+
+create or replace trigger faq_q_and_as_sws_insert_tr
+ after insert on faq_q_and_as for each row
+begin
+ insert into site_wide_index (object_id, object_name, datastore)
+ values (:new.entry_id, :new.question, 'a');
+end;
+/
show errors
+
+create or replace trigger faq_q_and_as_sws_update_tr
+ after update on faq_q_and_as for each row
+begin
+ update site_wide_index
+ set object_name=:new.question,
+ datastore='a'
+ where object_id = :new.entry_id;
+end;
+/
+show errors
+
+create or replace trigger faq_q_and_as_sws_delete_tr
+ after delete on faq_q_and_as for each row
+begin
+ delete from site_wide_index
+ where object_id = :old.entry_id;
+end;
+/
+show errors
+
+create or replace procedure faq_q_and_as_sws_helper (p_tlob in out nocopy clob, p_object_id in varchar)
+is
+ cursor faq_q_and_as_cursor(v_object_id char) is
+ select question, answer, p.first_names || ' ' || p.last_name as
+ author_name, parties.email
+ from faq_q_and_as ae, acs_objects ao, persons p, parties
+ where p.person_id = ao.creation_user
+ and ao.object_id = v_object_id
+ and parties.party_id = p.person_id
+ and ae.entry_id = v_object_id;
+begin
+ for faq_q_and_as_record in faq_q_and_as_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if faq_q_and_as_record.question is not null then
+ dbms_lob.writeappend(p_tlob, length(faq_q_and_as_record.question) + 1, faq_q_and_as_record.question || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ dbms_lob.writeappend(p_tlob, length(faq_q_and_as_record.author_name) + 1, faq_q_and_as_record.author_name || ' ');
+ if faq_q_and_as_record.answer is not null then
+ dbms_lob.writeappend(p_tlob, length(faq_q_and_as_record.answer) + 1, faq_q_and_as_record.answer || ' ');
+ end if;
+ end loop;
+end;
+/
+show errors;
+
+--------------------------------------------------------
+-- Survey Procs
+
+create or replace trigger surveys_sws_insert_tr
+ after insert on surveys for each row
+begin
+ insert into site_wide_index (object_id, object_name, datastore)
+ values (:new.survey_id, :new.name, 'a');
+end;
+/
+show errors
+
+create or replace trigger surveys_sws_update_tr
+ after update on surveys for each row
+begin
+ update site_wide_index
+ set object_name=:new.name,
+ datastore='a'
+ where object_id = :new.survey_id;
+end;
+/
+show errors
+
+
+create or replace trigger surveys_sws_delete_tr
+ after delete on surveys for each row
+begin
+ delete from site_wide_index
+ where object_id = :old.survey_id;
+end;
+/
+show errors
+
+create or replace procedure surveys_sws_helper (p_tlob in out nocopy clob, p_object_id in varchar)
+is
+
+ cursor surveys_cursor(v_object_id char) is
+ select name, description, p.first_names || ' ' || p.last_name as
+ author_name, parties.email
+ from surveys sv, persons p, parties, acs_objects ao
+ where sv.survey_id = ao.object_id
+ and p.person_id = ao.creation_user
+ and parties.party_id = p.person_id
+ and sv.survey_id = v_object_id;
+
+ cursor survey_sections_cursor(v_survey_id char) is
+ select section_id, name, description
+ from survey_sections sv
+ where sv.survey_id = v_survey_id;
+
+ cursor survey_questions_cursor(v_section_id char) is
+ select question_text
+ from survey_questions
+ where section_id = v_section_id;
+
+begin
+ for surveys_record in surveys_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if surveys_record.name is not null then
+ dbms_lob.writeappend(p_tlob, length(surveys_record.name) + 1, surveys_record.name || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ dbms_lob.writeappend(p_tlob, length(surveys_record.author_name) + 1, surveys_record.author_name || ' ');
+ if surveys_record.description is not null then
+ dbms_lob.writeappend(p_tlob, length(surveys_record.description) + 1, surveys_record.description || ' ');
+ end if;
+
+ for survey_sections_record in survey_sections_cursor(p_object_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if survey_sections_record.name is not null then
+ dbms_lob.writeappend(p_tlob, length(survey_sections_record.name) + 1, survey_sections_record.name || ' ');
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if survey_sections_record.description is not null then
+ dbms_lob.append(p_tlob, survey_sections_record.description);
+ end if;
+
+ for survey_questions_record in survey_questions_cursor(survey_sections_record.section_id) loop
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ if survey_questions_record.question_text is not null then
+ dbms_lob.append(p_tlob, survey_questions_record.question_text);
+ end if;
+ dbms_lob.writeappend(p_tlob, length(''), '');
+ end loop;
+
+ end loop;
+
+ end loop;
+end;
+/
+show errors;
+
+create or replace trigger survey_sections_sws_insert_tr
+ after insert on survey_sections for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id = :new.survey_id;
+end;
+/
+show errors
+
+create or replace trigger survey_sections_sws_update_tr
+ after update on survey_sections for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id = :new.survey_id;
+end;
+/
+show errors
+
+
+create or replace trigger survey_sections_sws_delete_tr
+ after delete on survey_sections for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id = :old.survey_id;
+end;
+/
+show errors
+
+create or replace trigger survey_questions_sws_insert_tr
+ after insert on survey_questions for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id in (select survey_id
+ from survey_sections
+ where section_id = :new.section_id);
+end;
+/
+show errors
+
+create or replace trigger survey_questions_sws_update_tr
+ after update on survey_questions for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id in (select survey_id
+ from survey_sections
+ where section_id = :new.section_id);
+end;
+/
+show errors
+
+
+create or replace trigger survey_questions_sws_delete_tr
+ after delete on survey_questions for each row
+begin
+ update site_wide_index
+ set datastore='a'
+ where object_id in (select survey_id
+ from survey_sections
+ where section_id = :old.section_id);
+end;
+/
+show errors
+
+--------------------------------------------------------
+-- The user_datastore proc which is called on every change of the datastore.
+
+create or replace procedure sws_user_datastore_proc ( p_rid in rowid, p_tlob in out nocopy clob )
+is
+ v_object_id site_wide_index.object_id%type;
+ v_object_type acs_objects.object_type%type;
+begin
+ site_wide_search.logger ('entered sws_user_datastore_proc');
+ select swi.object_id, ao.object_type
+ into v_object_id, v_object_type
+ from site_wide_index swi, acs_objects ao
+ where swi.object_id = ao.object_id
+ and p_rid = swi.rowid;
+
+ -- clean out the clob we're going to stuff
+ dbms_lob.trim(p_tlob, 0);
+
+ site_wide_search.logger ('in sws_user_datastore_proc with type ' || v_object_type);
+ -- handle different sections
+ if v_object_type = 'forums_message' then
+ site_wide_search.logger ('calling forums_messages_sws_helper ');
+ forums_messages_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'cal_item' then
+ site_wide_search.logger ('calling acs_events_sws_helper with cal_item');
+ acs_events_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'faq_q_and_a' then
+ site_wide_search.logger ('calling faq_q_and_as_sws_helper with faq_q_and_a');
+ faq_q_and_as_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'static_portal_content' then
+ site_wide_search.logger ('calling static_portal_sws_helper');
+ static_portal_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'survey' then
+ site_wide_search.logger ('calling surveys_sws_helper');
+ surveys_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'survey_section' then
+ site_wide_search.logger ('calling survey_sections_sws_helper');
+ survey_sections_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'survey_question' then
+ site_wide_search.logger ('calling survey_questions_sws_helper');
+ survey_questions_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'survey_response' then
+ site_wide_search.logger ('calling survey_responses_sws_helper');
+ survey_responses_sws_helper(p_tlob, v_object_id);
+ elsif v_object_type = 'wp_slides' then
+ v_object_type := 'foobar';
+ end if;
+end;
+/
+show errors;
+
+
+exit;
+
Index: openacs-4/packages/search/sql/oracle/search-packages-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/search-packages-drop.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/search/sql/oracle/search-packages-drop.sql 2 Jul 2002 01:52:38 -0000 1.1
+++ openacs-4/packages/search/sql/oracle/search-packages-drop.sql 21 May 2005 11:34:10 -0000 1.2
@@ -1,2 +1,24 @@
-drop package body search_observer;
-drop package search_observer;
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Drop database packages for .LRN site-wide search
+--
+-- @author Dirk Gomez
+-- @version $Id$
+-- @creation-date 13-May-2005
+
+exit;
\ No newline at end of file
Index: openacs-4/packages/search/sql/oracle/search-tables-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/search-tables-create.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/search/sql/oracle/search-tables-create.sql 2 Jul 2002 01:52:38 -0000 1.1
+++ openacs-4/packages/search/sql/oracle/search-tables-create.sql 21 May 2005 11:34:10 -0000 1.2
@@ -1,8 +1,49 @@
-create table search_observer_queue (
- object_id integer,
- event_date date
- default sysdate,
- event varchar(6)
- constraint search_observer_queue_event_ck
- check (event in ('INSERT','DELETE','UPDATE'))
+--
+-- Copyright (C) 2005 MIT
+--
+-- 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.
+--
+
+--
+-- Create database tables for .LRN site-wide search
+--
+-- @author openacs@dirkgomez.de
+-- @version $Id$
+-- @creation-date 13-May-2005
+--
+-- Partly ported from ACES.
+
+-- Central table for site-wide search.
+create table site_wide_index (
+ object_id integer
+ constraint sws_index_pk primary key
+ constraint sws_index_fk references acs_objects(object_id) on delete cascade,
+ object_name varchar(4000),
+ datastore char(1) not null,
+ event_date date
+ default sysdate,
+ event varchar(6)
+ constraint site_wide_index_event_ck
+ check (event in ('INSERT','DELETE','UPDATE'))
);
+
+-- Intermedia sometimes is painful to debug, so I added a logging
+-- mechanism which relies on Oracle's autonomous transactions: DML
+-- statements are committed immediately so you can access this data
+-- from a different session right away.
+
+create table sws_log_messages (
+ logmessage varchar2(4000),
+ logtime date default sysdate);
+
+exit;
Index: openacs-4/packages/search/sql/oracle/search-tables-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/search/sql/oracle/search-tables-drop.sql,v
diff -u -N -r1.1 -r1.2
--- openacs-4/packages/search/sql/oracle/search-tables-drop.sql 2 Jul 2002 01:52:38 -0000 1.1
+++ openacs-4/packages/search/sql/oracle/search-tables-drop.sql 21 May 2005 11:34:10 -0000 1.2
@@ -1 +1,7 @@
+drop table site_wide_index;
drop table search_observer_queue;
+drop table sws_log_messages;
+
+exit;
+
+