Long time no... Oracle talk!

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

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?

First up, come up to 0.88. Some of this logic was wrong and got cleaned
up, which might make your problem go away. Otherwise, we may need to
change how the “extra” WHERE (main.id = distinctquery.id) gets
populated in the first place.

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.

You may want to switch to Apache::Session::File. or have a look at the
docs for Apache::Session::Oracle.

But all in all, this is great news. can you send me a diff relative to
3.0.4?

-Brook


rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel

http://www.bestpractical.com/rt – Trouble Ticketing. Free.