[rt-devel] Long time no... Oracle talk!

[Brook Schofield]

I came home and couldn't get to sleep when I wanted too. So I decided 
to have another crack at this RT/Oracle stuff.
It really helps if you look for error messages and turn on tracing in 
DBI.

The current issue seems to be around the distinct queries. The SQL 
generated is:

	SELECT main.* FROM (
		SELECT DISTINCT main.id FROM Queues main
	) distinctquery, Queues main
	WHERE (main.id = distinctquery.id)
	WHERE ((main.Disabled = '0'))
	ORDER BY main.Name ASC

when it should be:

	SELECT main.* FROM (
		SELECT DISTINCT main.id FROM Queues main
	) distinctquery, Queues main
	WHERE (main.id = distinctquery.id)
		AND ((main.Disabled = '0'))
          ^^^
	ORDER BY main.Name ASC

the double WHERE is inserted because to code in SearchBuilder doesn't 
know that another WHERE has already been inserted as part of the 
DISTINCT query code. Any tips on correcting this?

I've done the following in SearchBuilder.pm (working with version 0.86 
- haven't upgraded to v0.88 yet):

     # The initial SELECT or SELECT DISTINCT is decided later

     $QueryString = $self->_BuildJoins . " ";

     # DISTINCT query only required for multi-table selects
     if ($QueryString) {
         $self->_DistinctQuery(\$QueryString, $self->{'table'});
     } else {
         $QueryString = "SELECT main.* FROM $QueryString";
     }
       if ( $self->_isLimited > 0 ) {
                 my $tmp = $self->_WhereClause . " " . 
$self->{'table_links'} . "
  ";
                 $tmp =~ s/WHERE/AND/;
                 $QueryString .= $tmp;
         }


I still don't understand why the "if ($QueryString) {" isn't always 
going to be true - but that is a later issue to tackle. Does anyone 
know a clean way of doing this? Any tips! I looked at changing the 
nesting order so that the DISTINCT query could be a wrapper for the 
later query - but I think that is more likely to induce more problems.

With my rough hacks in place Oracle support appears to work. The only 
issue that I am having now is Apache::Session not working and I'm 
required to Re-Authenticate for each page. But I could have broken 
something in my tinkering.

-Brook