SearchBuilder/Oracle Insert 'id' Patch

RT Developers
1

That’s great, but I still see some “[crit]:”-lines.
I do not know what they do, but they do not appear in the mysql version
:frowning:

I’ve just had a play on my oracle instance - and I am actually having
some problems. So I need to export out the mysql dataset and the Oracle
dataset and see what the similarities/differences are and then track
down why I’m getting these problems.

For instance: I can’t seem to create a ticket via the web interface and
when I login a root I only get the /SelfService/ interface - rather
than the standard root interface.

This is obviously a problem with the lack of group modifications etc…
This ISN’T due to me changing my local version of the schema back to
not allowing NULLs within the GroupMembership table - I get the errors
either way. I should probably move away from 3.0.1pre2 to 3.0.2pre5
just to I catch up on the other bug which have already been caught.

-Brook

2

That’s great, but I still see some “[crit]:”-lines.
I do not know what they do, but they do not appear in the mysql
version
:frowning:

Well - I now know why the [crit] lines appear in the Oracle
initialize-database output. This is because the SQL SELECT to load
columns from the Groups table doesn’t work.

MySQL SQL:
SimpleQuery: SELECT * FROM Groups WHERE Type = ? AND Instance = ? AND
Domain = ? AND Name = ?^M
Everyone^M
^M
SystemInternal^M
^M

which is equivalent to:

mysql> SELECT * FROM Groups WHERE Type = ‘Everyone’ AND Instance = ‘’
AND Domain = ‘SystemInternal’ AND Name = ‘’;
| id | Name | Description | Domain | Type
| Instance |
| 3 | | Pseudogroup for internal use | SystemInternal | Everyone
| |

Oracle SQL:
SimpleQuery: SELECT * FROM Groups WHERE lower(Domain) = ? AND
lower(Name) = ? AND lower(Type) = ? AND lower(Instance) = ?^M
systeminternal^M
^M
everyone^M
^M

if the query was modified to:

SimpleQuery: SELECT * FROM Groups WHERE lower(Domain) =
‘systeminternal’ AND lower(Type) = ‘everyone’ AND Instance IS NULL AND
Name IS NULL;

then the correct results would be returned, or was simplified to:

SimpleQuery: SELECT * FROM Groups WHERE lower(Domain) = ? AND
lower(Type) = ?^M
systeminternal^M
everyone^M

then it would actually work for Oracle. I don’t know to what extent
this should be based in SB simplifying a query to remove these
NULL/undef values - or whether it should be something in the
RT/Group.pm code.

The offending code from RT/Group_Overlay.pm:

sub LoadSystemInternalGroup {
my $self = shift;
my $identifier = shift;

     $self->LoadByCols( "Domain" => 'SystemInternal',
                        "Instance" => '',
                        "Name" => '',
                        "Type" => $identifier );

}

And the offending code in DBIx::SearchBuilder::Record.pm:

sub LoadByCols {
my $self = shift;
my %hash = (@_);
my (@bind, @phrases);
foreach my $key (keys %hash) {
if (defined $hash{$key}) {
push @phrases, “$key = ?”;
push @bind, $hash{$key};
}
else {
push @phrases, “$key is null”;
}
}

 my $QueryString = "SELECT  * FROM ".$self->Table." WHERE ".
 join(' AND ', @phrases) ;
 return ($self->_LoadFromSQL($QueryString, @bind));

}

basically the values should be undefined and thus “$key is null” would
be used - but the value in Oracle is actually NULL rather than nothing.
So what process should we follow:

a) get Oracle to insert NOTHING rather than a NULL (I don’t know how to
do this)
b) get LoadByCols to do an Oracle special thingy and force the “$key is
null” syntax

The easier of the two is forcing “IS NULL” - but what does anyone else
think?

This seems to be the only thing holding up the Oracle support for RT.

Ideas?

-Brook

3

Brook Schofield wrote on 14/05/2003 12:38:39 AM:

Well - I now know why the [crit] lines appear in the Oracle
initialize-database output. This is because the SQL SELECT to load
columns from the Groups table doesn’t work.

Oracle SQL:

SimpleQuery: SELECT * FROM Groups WHERE lower(Domain) = ? AND
lower(Name) = ? AND lower(Type) = ? AND lower(Instance) = ?^M
systeminternal^M
^M
everyone^M
^M

I modified the Oracle SQL query to force either = ‘’ or IS NULL - but I
still ran into strange problems. The issue appears to be with the
difference between VARCHAR2 and CLOB types in Oracle 9i. From the Oracle
9i manual:

IS NULL Semantic Discrepancy
In the SQL 92 standard, a character string of length zero is distinct from
a null string.
For an initialized LOB of length 0, you should expect IS NULL' to return zero (FALSE), since it is the correct and standard compliant behavior. In contrast, a VARCHAR2 of length 0 returns TRUE on 'IS NULL.
In addition, for the LENGTH() function:
If the input is a character string of zero length, LENGTH() returns NULL.
For a CLOB of zero length, an EMPTY_CLOB(), zero is returned by LENGTH and
DBMS_LOB.GETLENGTH() in SQL and PL/SQL.
This can be misleading. Note this semantic discrepancy.
This last line is actually from the Oracle 9i manual - not my own addition
:wink:
So I’ll be left with changing the query from:
SimpleQuery: SELECT * FROM Groups WHERE lower(Domain) = ? AND lower(Name)
= ? AND lower(Type) = ? AND lower(Instance) = ?^M
systeminternal^M
^M
everyone^M
^M
to:

SELECT * FROM Groups WHERE lower(Domain) = ? AND (Name = ‘’ OR Name IS
NULL) AND lower(Type) = ? AND (Instance = ‘’ OR Instance IS NULL)
systeminternal^M
everyone^M

to satisfy the issue with VARCHAR2 and CLOB. My DBAs tell me that Oracle
recommends the use of VARCHAR2 over VARCHAR because the behaviour of
VARCHAR may change over time - hopefully they’ll change VARCHAR to have
the same behaviour as their CLOB type in some future edition - which will
allow SearchBuilder to be simplified.

Now the only issue is whether the change should be within LoadByCols or
whether it should be abstracted out to an Oracle specific change?

sub LoadByCols {
my $self = shift;
my %hash = (@_);
my (@bind, @phrases);
foreach my $key (keys %hash) {

  •      if (defined $hash{$key}) {

  •      if (defined $hash{$key} && $hash{$key} ne '') {

             push @phrases, "$key = ?";
             push @bind, $hash{$key};
     }
     else {

  •              push @phrases, "$key is null";

  •              push @phrases, "($key IS NULL or $key = '')";

     }
 }

 my $QueryString = "SELECT  * FROM ".$self->Table." WHERE ".
 join(' AND ', @phrases) ;
 return ($self->_LoadFromSQL($QueryString, @bind));

}

NB:- The change for adding/removing the lower() tag should be made in
SearchBuilder.pm.

I know I always say this, but “Oracle support for RT is one step closer”
:wink:

-Brook