[CDBI] Re: Class::DBI internal column swapping wierdness ? - possible resolution! (UNION DISTINCT)?

Richard Foley rfoley at amadeus.com
Thu Oct 6 10:45:59 BST 2005


Hi

I think I may have found the reason for this wierd behaviour, it seems to 
come from a 'retrieve_from_sql' statement, when I use a 'UNION DISTINCT' 
in the clause, something like this:

        my $sql = qq|
            name IN (
                SELECT name FROM task where shared = 'NO' AND application 
= '$application'
            )
            UNION DISTINCT
            SELECT * FROM task where shared = 'YES' AND application IN (
                SELECT name FROM application WHERE container = 
'$container'
            )
        |; 
        @tasks = Att::Task->retrieve_from_sql($sql);

What seems to happen, is that the objects returned from the first part of 
the SQL statement are expanded (or expandable), but the ones which come 
back from the unioned (second) part of the statement, are munged in a bad 
way.  On later inspection, they appear to be a hash built from a list with 
an element missing or added, so knocking all the key=>value pairs off by 
one, and messing up the construction of the object, as you can see from 
the original bug report (below).

In the example below, it's not just that enabled=>YES and shared=>ENABLED 
is wrong, created_by=>1 should be created_by=>$adatestring, and so on.

If you follow me?

In summary, I think this is distinctly related to the UNION DISTINCT in 
the SQL, and retrieve_from_sql() not handling it correctly.

Richard.



From:   "Richard.Foley at t-online.de" <Richard.Foley  on 29-09-2005 16:29 
ZE2
Please respond to Richard.Foley at rfi.net

To:
classdbi at lists.digitalcraftsmen.net



cc:
rfoley at amadeus.net









Subject:
Class::DBI internal column swapping wierdness ?





I have some very wierd behaviour going on using Class::DBI.  Columns
appear to be being swapped internally, which is seriously screwing our
application up.  Now I may be missing something obvious here, of course,
but here is the basic scenario:

Here's the stripped down package:

 package Att::Task;
 use strict;
 use base qw(Att::Dbi);

 __PACKAGE__->table  ('task');
 __PACKAGE__->columns(Primary    => qw(name));
 __PACKAGE__->columns(Essential    => qw(
     created created_by modified modified_by 
     title application stage seq enabled shared freetext
 ));

 __PACKAGE__->has_a(application  =>     qw(Att::Application));
 __PACKAGE__->has_a(stage        =>     qw(Att::Stage));
 __PACKAGE__->has_a(enabled      =>     qw(Att::EnabledDisabled));
 __PACKAGE__->has_a(shared  =>  qw(Att::YesNo));

 1

The relevant lines are shared (YesNo) and enabled(EnabledDisabled).  We
can take a look in the database to see what's in the relevant tables:


 mysql> SELECT name FROM enableddisabled;
 +----------+
 | name     |
 +----------+
 | DISABLED |
 | ENABLED  |
 +----------+

 mysql> SELECT name FROM yesno;
 +------+
 | name |
 +------+
 | NO   |
 | YES  |
 +------+


Just to be pedantic here's the database constraint definitions for the
relevant columns:

  CONSTRAINT `task_ibfk_2` FOREIGN KEY (`ENABLED`) REFERENCES
`enableddisabled` (`NAME`),
  CONSTRAINT `task_ibfk_5` FOREIGN KEY (`SHARED`) REFERENCES `yesno`
(`NAME`)

Pretty simple stuff so far.  Now let's take a look at a record from the
database.


 mysql> SELECT name, enabled, shared FROM task WHERE name LIKE
'thenew%';
 +--------------------------+---------+--------+
 | name                     | enabled | shared |
 +--------------------------+---------+--------+
 | thenewdeliverychec_02397 | ENABLED | YES    |
 +--------------------------+---------+--------+

As expected, enabled=ENABLED and shared=YES.  Now, take a look at what
Class::DBI returns:

Att::Task thenewdeliverychec_02397         $VAR1 = bless( {
    'stage' => bless( {
                     'name' => '2005-09-13 14:08:13'
                   }, 'Att::Stage' ),
    'name' => 'thenewdeliverychec_02397',
    'application' => bless( {
                           'name' => 'att'
                         }, 'Att::Application' ),
    'freetext' => 'Check what was delivered changed to analysis - ppt
pptcomp',
    'modified' => '0000000000analysis_00504',
    'created' => 'The new delivery check',
    'shared' => bless( {
                      'name' => 'ENABLED'
                    }, 'Att::YesNo' ),
    'modified_by' => '1',
    'title' => '2005-09-13 14:08:13',
    'seq' => bless( {
                   'id' => 'att'
                 }, 'Att::Sequence' ),
    'created_by' => 'pptcomp_25143',
    'enabled' => bless( {
                       'name' => 'YES'
                     }, 'Att::EnabledDisabled' )
}, 'Att::Task' );

All of a sudden, enabled=YES and shared=ENABLED!!!

How the hell did that happen...?!  If anyone has any bright ideas here,
before I pull my remaining hair out, I'd appreciate it.  TIA.

-- 
Ciao
Richard Foley
Ciao - shorter than aufwiedersehen








-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digitalcraftsmen.net/mailman/private/classdbi/attachments/20051006/88f9a50b/attachment.htm


More information about the ClassDBI mailing list