Re: Problems with set_sql and iterators

[prev] [thread] [next] [Date index for 2005/06/20]

Randal L. Schwartz wrote:

> Jacinta> 	# Declare our primary key field(s)
> __PACKAGE__-> columns(Primary => 'StaffID', 'ProjectName');
> __PACKAGE__-> columns(All => 'StaffID', 'ProjectName', 'Allocation');
> __PACKAGE__-> columns(TEMP => 'Total');


> Then what you really need is a unique column for each allocation, and
> a constraint to ensure that there are no duplicates.  Especially if
> you think there might be NULLs in your "primary key", which means it's
> no longer a real primary key in a real database.

G'day Randal,

I don't think that there might be NULLs in my primary key (I know there are 
not).  I am very aware of what a primary key is and I understand all this about 
duplicates.

As I said before, the above spec is a *simplification* of a real problem I have 
encountered.  I made a mistake in this class because I only created it for the 
purposes of demonstrating the error I am discussing.  I corrected myself in 
order to save anyone else from having to do so.

> Jacinta> The fact that I *know* the search returns data, can access that data,
> Jacinta> but can't iterate over seems to be a problem.  It seems to me that
> Jacinta> either object creation without defined primary keys should fail (so
> Jacinta> failing at the search stage) or that both iteration and array contexts
> Jacinta> should work.
> 
> I'd say it's a bug that CDBI lets you define a broken database (NULL
> in a primary key), but CDBI has to trust that you have some
> responsibility in the matter to reflect a valid reality.

The problem is that I'm *not* defining a broken database.  I'm asking CDBI to 
return the results of a search on a table for which I have CDBI class defined. 
As an *aggregation* the search results don't fill in all values of the primary 
key I've set.  This should make sense.

Just in case you missed my earlier mail, the aggregation I'm doing is:

	SELECT ProjectName, sum(Allocation) as Total
         FROM Projects
         GROUP BY ProjectName
         ORDER BY ProjectName

which returns ProjectName and Total.  The primary key consists of the StaffID 
and ProjectName.

The error may be in the fact that CDBI tries to build objects for these results 
even though it does not have all the information it requires for the primary 
key.  It succeeds by leaving the appropriate essential fields undefined where 
necessary.  It is not until the objects are treated in a boolean context that 
problems occur.

> Jacinta> Upon more thought I guess I'm challenging the idea that an object
> Jacinta> whose primary key is undefined but otherwise contains data should be
> Jacinta> considered false.  I'm struggling to find any situation that *I* would
> Jacinta> expect that result.  I'm sure that there situations, so if you could
> Jacinta> expand on one or two that would be appreciated.
> 
> Because primary keys never contain NULL.

It seems to me that either CDBI should throw an exception because the search 
results don't fill in all values of the primary keys for this class, or CDBI 
should behave differently (from how it does) in a boolean context.

Since CDBI *can* be used to perform aggregate searches on the tables the classes 
are defined for, and since aggregate searches can't keep the primary key 
information intact, it seems to me that this overload to bool which marks the 
objects as false is strange.  There is obviously data there, I wish to use it, 
why say the object is false?

For example, this breaks:

     my $project_it = Staff::Projects->search_total_times();

     while( my $project = $project_it->next) {
             print $project->projectname, "      ", $project->total(), "\n";
     }

although this works:

     my @projects = Staff::Projects->search_total_times();

     foreach my $project (@projects) {
             print $project->projectname, "      ", $project->total(), "\n";
     }


If it's not considered a good idea to perform this sort of aggregate search 
through CDBI like this then the documentation should say so.


All the best,

     Jacinta

        -- 
            ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
   _..`--'_..-_/  /--'_.' ,'           | contact@xxxxxxxxxxxx.xxx.xx |
  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |