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 -r1.4.4.1 -r1.4.4.2 --- openacs-4/packages/search/sql/oracle/search-packages-create.sql 16 Apr 2008 12:41:13 -0000 1.4.4.1 +++ openacs-4/packages/search/sql/oracle/search-packages-create.sql 16 Apr 2008 13:01:20 -0000 1.4.4.2 @@ -76,166 +76,3 @@ / show errors - -create or replace package site_wide_search -as - procedure register_event (p_object_id search_observer_queue.object_id%TYPE, - p_event search_observer_queue.event%TYPE); - - procedure logger (p_logmessage varchar); - - function im_convert( - query in varchar2 default null - ) return varchar2; - -end site_wide_search; -/ -show errors - -create or replace package body site_wide_search -as - procedure register_event (p_object_id search_observer_queue.object_id%TYPE, - p_event search_observer_queue.event%TYPE) is - begin - insert into search_observer_queue - (object_id, event) - values - (p_object_id, p_event); - end register_event; - - procedure logger (p_logmessage varchar) is - begin - insert into sws_log_messages (logmessage) values (p_logmessage); - end logger; - - -- Query to take free text user entered query and frob it into something - -- that will make interMedia happy. Provided by Oracle. - 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; - -end site_wide_search; -/ -show errors - 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 -r1.7.4.1 -r1.7.4.2 --- openacs-4/packages/search/sql/oracle/search-tables-create.sql 16 Apr 2008 12:41:13 -0000 1.7.4.1 +++ openacs-4/packages/search/sql/oracle/search-tables-create.sql 16 Apr 2008 13:01:20 -0000 1.7.4.2 @@ -31,13 +31,3 @@ constraint search_observer_queue_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); -