[Dbix-class] For a reliable update_or_create we need to make create( { primary_key => undef, ... } ) work in Pg

[Zbigniew Lukasiak]

On Jan 22, 2008 12:02 AM, Daniel Westermann-Clark <dwc at pobox.com> wrote:
> On 2008-01-21 14:14:10 +0100, Zbigniew Lukasiak wrote:
> > The reason for that is that finding without a unique condition is a
> > special case - it's not really obvious what it should return - it
> > could even be a run time error.  Find is based on search, normally
> > when you remove a condition from search it should return more rows -
> > but here when you remove a condition from find, to cooperate with
> > the find_or_* methods, it should return no rows at all.  We can
> > force it to do that - but this is quite complex, because you can
> > have the conditions not only in the query but also in the resultset
> > itself.  This needs to be done very carefully.
> >
> > But I am OK with that solution - in fact I have sent a patch that
> > worked for most of the cases that I imagined, but I am sure it would
> > benefit from your review.  I am attaching it here once again.
>
> There is an ~80% finished solution in ::ResultSet to resolve the query
> and resultset conditions and check them for uniqueness.  mst made me
> disable the warning at one point during the 0.07 RC cycle because
> those 20% cases happen more often than you'd think.  :)

The problem is that for a big part of that 20% the current fallback
solution leads to errors (like when udate_or_create and updates a
wrong row or when the generated SQL query fails with an error, both
cases I have documented in the other thread).

>
> If we can bring the existing _is_unique_query up to date, adding an
> option to turn off fallback sounds reasonable.  I agree that some
> applications could use the explicit switch.
>
> Since we're talking about changing the behavior of find, this most
> likely belongs on the 0.09 cycle.  To that end, I've opened a branch
> to start fixing _is_unique_query:
>
> http://dev.catalystframework.org/svnweb/bast/browse/DBIx-Class/0.09/branches/find_unique_fallback/
>
> I've started reworking the check a little, but there are still
> problems inferring uniqueness across a join.  See, for example, the
> final set of tests in t/79aliasing.t:
>
> # Don't pass column names with related alias to new_result
> {
>   my $cd_rs = $schema->resultset('CD')->search({ 'artist.name' =>
>   'Caterwauler McCrae' }, { join => 'artist' });
>
>   my $cd = $cd_rs->find_or_new({ title => 'Huh?', year => 2006 });
>   ok(! $cd->in_storage, 'new CD not in storage yet');
>   is($cd->title, 'Huh?', 'new CD title is correct');
>   is($cd->year, 2006, 'new CD year is correct');
> }
>
> Ideally this query should not warn: the search identifies a unique
> artist row and the find identifies a unique CD row.

Is it ever possible that it works 100%?  I mean - the Relationship POD
says that "The condition needs to be an SQL::Abstract-style
representation of the join between the tables" and from follows there,
it looks that it really can be nearly any SQL::Abstract structure.  Is
it ever feasible that we parse the SQL::Abstract query and determine
if it chooses a unique row?  And if not then where you suppose to
stop?


-- 
Zbigniew Lukasiak
http://perlalchemy.blogspot.com/