[Dbix-class] find_or_create and unique constraints

[Bill Moseley]

On Sun, Oct 3, 2010 at 8:19 AM, Daniel Westermann-Clark <dwc at pobox.com>wrot=
e:

> On 2010-10-02 17:16:20 -0700, Bill Moseley wrote:
> > I'm wondering if find_or_create should throw an exception if a unique
> > constraint does not exist or if the values passed to find_or_create do
> not
> > include ALL the columns in the constraint.
>
> I wrote some code long ago that attempted to do this (see
> _is_unique_query in ResultSet.pm), but it breaks down on any sort of
> complex query.  Anything with a join, for example, is hard to analyze
> against unique constraints.
>

I looked at that code, but what I decided to try first is override
'_unique_queries' which creates a list of queries that are unique.  I throw
an exception if none can be found:

around '_unique_queries' =3D> sub {
    my ( $orig, $self, $criteria, $attrs ) =3D @_;

    my @queries =3D $self->$orig( $criteria, $attrs);

    return @queries if @queries;

    my $cols =3D join ', ', keys %{$criteria};

    $self->throw_exception(
        "find must provide search criteria that includes primary keys(s) or
an unique constraint.  Columns [$cols] did not satisfy this requirement"
    );

};


Then I overrode find_or_create with the following.  I first attempt to
create if not inside a transaction and catch duplicate key violations.  Then
I try to find and promote warnings to an error (in case multiple rows are
returned.  I create if no row is returned, otherwise I update with full
criteria (to update any additional columns passed in).

Frankly, I'm not clear on all the code in find() and in single() so looking
for feedback on what might break here:

 sub find_or_create {
    my ( $self, $criteria, $attrs ) =3D @_;

    # Must provide something to work with here...
    croak 'Must provide search criteria as non empty hash to find_or_create'
        unless ref $criteria eq 'HASH' && keys %{$criteria};


    # First, if not in a transaction simply try to create.
    unless ( $self->result_source->storage->transaction_depth ) {

        my $new_obj;

        try {
            $new_obj =3D $self->create( $criteria, $attrs );
        }
        catch {
            my $error =3D $_;

            $self->throw_exception( $error )
                unless $self->is_duplicate_key_violation( $error );
        };

        # If we succeeded in creating the object then return.
        return $new_obj if $new_obj;
    }


    # Now attempt to find and enable fatal warnings
    # in the event that a query returns more than one row.
    my $row =3D do {
        local $SIG{__WARN__} =3D sub { $self->throw_exception(
"find_or_create: @_" ) };

        $self->find( $criteria, $attrs );
    };

    # Simply create if not found
    return $self->create( $criteria, $attrs ) unless $row;



    # If found then make sure row is updated.
    # We don't know which columns were used to find the row so update all.
    $row->$_( $criteria->{$_} ) for keys %{$criteria};

    $row->update;

    return $row;
}


-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101003/8b9=
66dea/attachment-0001.htm