[rt-devel] Behaviour correction for SB-0.88 (improves Oracle support)

[Brook Schofield]

Jesse (and interested Oracle parties),

The _isJoined function is supposed to "Returns true if this 
Searchbuilder requires joins between tables"  which is more like - will 
it or has it performed a join between tables. It seems all too eager to 
return true. This causes problems for the Oracle implementation with 
SQL being generated:

SELECT main.* FROM ( SELECT DISTINCT main.id FROM Queues main  ) 
distinctquery, Queues main WHERE (main.id = distinctquery.id)  AND 
((main.Disabled = '0'))

rather than the more simpler:

SELECT main.* FROM Queues main WHERE (main.Disabled = '0')

so I suggest the following patch to SB.pm - which could reduce the 
overhead for other databases as well.

sub _isJoined {
     my $self = shift;
     if (keys(%{$self->{'left_joins'}})) {
         return(1);
     } else {
         return(@{$self->{'aliases'}});
     }
}

Which return 'true' if there is more than 2 aliases or left_join keys. 
This should be the correct behaviour for all databases - but testing is 
obviously required.

I've also changed the order of the build of the SQL statements within 
_DoSearch (no changes required for _DoCount). Potentially the ORDER BY 
clause could be moved ahead of the DISTINCT component of the query - 
and this move would be required if a query was ever ordered by a column 
not in the primary table. I don't know whether that is possible within 
SearchBuilder - but useful for reference. I don't know whether the next 
join preserves the order of the DISTINCT sub select - I'll ask some 
DBAs. If it does - then I'll suggest the movement of the _OrderClass 
call.

FYI the SQL being generated was nesting the DISTINCT without the WHERE 
clauses which meant that the identifiers for the query weren't 
available in the outer select - causing an error.

I'm still having trouble with the Apache::Session stuff - but looking 
good otherwise. RT3 (3.0.3pre3 because I haven't updated yet) operates 
as expected - other than having to continually login - but I'm getting 
there.

Also, checkout http://rt3.fsck.com/Ticket/Display.html?id=2432 if you 
need any support files to start playing with Oracle/RT - not much 
should have changed since then. I'll roll new patches against 3.0.4 
if/when I get Apache::Session support working.

-Brook

-------------- next part --------------
A non-text attachment was scrubbed...
Name: sb_88_oracle.patch
Type: application/octet-stream
Size: 3202 bytes
Desc: not available
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030717/34e738c4/sb_88_oracle.obj
-------------- next part --------------