[rt-devel] SearchBuilder/Oracle inconsistencies (Was: SearchBuilder/Oracle Insert 'id' Patch)

[Brook Schofield]

On Tuesday, May 6, 2003, at 11:09  PM, Brook Schofield wrote:
> On Tuesday, May 6, 2003, at 10:39  PM, Steen Olesen - Tiscali A/S  
> wrote:
>> 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
>> :(

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

> Now creating a database for RT.
> Now populating database schema.
> Creating database schema.
> schema sucessfully inserted
> Now inserting database ACLs
> Now inserting RT core system objects
> unix passed to setlogsock, but path not available at
> /usr/local/lib/perl5/site_perl/5.8.0/Log/Dispatch/Syslog.pm
> line 66 Checking for existing system user...not found.  This
> appears to be a
> new installation.
> Creating system user...done.
> Now inserting RT data
> Creating Superuser  ACL...Creating
> groups...3.4.5.6.7.8.9.done. Creating users...[Tue May  6
> 12:18:44 2003] [crit]: Attempting to add a
> member to a group which wasn't loaded. 'oops'
> (/opt/rt3/lib/RT/Group_Overlay.pm:943)
> [Tue May  6 12:18:44 2003] [crit]: Attempting to add a member to a
> group which wasn't loaded. 'oops'
> (/opt/rt3/lib/RT/Group_Overlay.pm:943)
> 10.[Tue May  6 12:18:44 2003] [crit]: Attempting to add a member to a
> group which wasn't loaded. 'oops'
> (/opt/rt3/lib/RT/Group_Overlay.pm:943)
> [Tue May  6 12:18:44 2003] [crit]: Attempting to add a member to a
> group which wasn't loaded. 'oops'
> (/opt/rt3/lib/RT/Group_Overlay.pm:943)
> 12.done.
> Creating ACL...2.3.done.
> Creating queues...1.2.done.
> Creating ScripActions...1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.done.
> Creating ScripConditions...1.2.3.4.5.6.7.8.9.done.
> Creating templates...1.2.3.4.5.6.7.8.9.done.
> Creating scrips...1.2.3.4.5.6.7.8.9.10.11.12.13.done.