Kenneth Marshall wrote:
Joop,
I saw a reference to your Oracle full-text add-in, but not the actual
patch. I know that it can be done. I was hoping for more uniform support
within RT when you use any DB that supports it. If you could send or post
the patch for Oracle again, that would be great. I am working on an
update to 3.6 with a PostgreSQL backend, and I would like to cobble in
some full-text support for the new system.
OK, found the diff, find it attached. Its against Searchbuilder-1.40 and
within you'll find an additional index that is needed to get this to
work, together with the fact that placing %'s around the search term is
not done anymore. I think the person behind the screen/keyboard should
decide to use wildcards and not the application behind its back. In this
case it also kills performance since you'll effectively disable any
index with search terms like these '%search%'.
Basically what this patch does is scan for searches on main.content and
rewrite the query to use the Oracle specific way of doing full-text
searches. I'm not sure this will capture all constructs, what comes to
mind is RTFM which probably needs its own patch.
Its probably rather straightforward to implement by prepping a RTFM
search and then clearing out the Oracle SGA and doing the search and
next find the generated SQL and modify SearchBuilder todo the right thing.
Hope this will help you Kenneth and maybe a couple of other people as well,
Joop
--- SearchBuilder.pm.orig Fri Jul 29 16:49:52 2005
+++ SearchBuilder.pm Mon Aug 22 13:40:38 2005
@@ -688,9 +688,15 @@
if ( $args{'FIELD'} ) {
- #If it's a like, we supply the %s around the search term
+ #If it's a like, we supply the %s around the search term only if its
not Oracle
+ #because for Oracle we'll use where contains(content,'text')>1
if ( $args{'OPERATOR'} =~ /LIKE/i ) {
- $args{'VALUE'} = "%" . $args{'VALUE'} . "%";
+ if ( $RT::DatabaseType == 'Oracle') {
+ $args{'VALUE'} = $args{'VALUE'}
+ }
+ else {
+ $args{'VALUE'} = "%" . $args{'VALUE'} . "%";
+ }
}
elsif ( $args{'OPERATOR'} =~ /STARTSWITH/i ) {
$args{'VALUE'} = $args{'VALUE'} . "%";
@@ -864,6 +870,20 @@
}
my $clause = "($QualifiedField $args{'OPERATOR'} $args{'VALUE'})";
+
+# Keep the original clause and modify if dbtype is Oracle
+# This patch makes searching for ticket content real fast when you have the
appropriate
+# index on attachments.content
+# CREATE INDEX CNT ON ATTACHMENTS (CONTENT) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('memory 4M');
+# parameters is tunable, see Oracle docs ;-)
+ if ( $RT::DatabaseType == 'Oracle' ) {
+ if ($QualifiedField eq 'lower(Attachments_2.Content)' and
$args{'OPERATOR'} eq 'LIKE') {
+ $clause = '(contains(Attachments_2.Content,' . $args{'VALUE'} . ')
>0)';
+ }
+ elsif ($QualifiedField eq 'lower(Attachments_2.Content)' and
$args{'OPERATOR'} eq 'NOT LIKE') {
+ $clause = '(not contains(Attachments_2.Content,' . $args{'VALUE'}
. ') >0)';
+ }
+ }
# Juju because this should come _AFTER_ the EA
my $prefix = "";
_______________________________________________
http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
Community help: http://wiki.bestpractical.com
Commercial support: [EMAIL PROTECTED]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
