[Dbix-class] find_or_create and unique constraints

[Bill Moseley]

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.

For example, say at some point a new column "three" was added as a column
and part of the unique constraint.

__PACKAGE->add_unique_constraint( name =3D> [qw/ one two three /] );


But there was old code that still only expected two columns in the unique
constraint:

$rs->find_or_create({
    one =3D> 1,
    two =3D> 2,
    other =3D> 123,
});


Since not all three columns are included this generates this code:

select * from foo where (( me.one =3D 1 AND me.two =3D 2 AND other =3D 123 =
));


which is not what you want.  This could return multiple rows (with a warning
"Query returned more than one row.  SQL that returns multiple rows is
DEPRECATED for ->find and ->single").  Which row is returned is undetermined
and can cause unpredictable results.


Then there is this situation where the three constraint columns are
included, plus additional columns:

$rs->find_or_create({
    one =3D> 1,
    two =3D> 2,
    three =3D> 3,
    other =3D> 123,
});


Then you get the correct unique query from DBIC:

select * from foo where (( me.one =3D 1 AND me.two =3D 2 AND three =3D 3 ));

But, that has the confusing problem that it may return the unique row based
on those three columns, but the "other" column is not set to 123 as one
might expect.



Another issue, which is much harder to catch, is if the database table has a
new row added that is now part of a unique constraint, but the
add_unique_constraint is not updated in the result class.

So, in this case the class had:

__PACKAGE->add_unique_constraint( name =3D> [qw/ one two  /] );


But, the usage was correct in the code according to the unique constraint in
the database:

$rs->find_or_create({
    one =3D> 1,
    two =3D> 2,
    three =3D> 3,
});


What DBIC does in this case is  select * from foo where ( ( me.one =3D 1 AND
me.two =3D 2 )) with the warning if more than one row comes back.

I don't know how to work around that issue other than throw an exception if
more than one row is returned.


Seems like ->single (and thus find_or_create) should throw an exception if
more than one row is returned, columns passed to find_or_create must satisfy
a defined unique constraint, and if a single row is found any non-constraint
columns specified should be updated.

Are there conditions where that would not work?





-- =

Bill Moseley
moseley at hank.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20101002/2fc=
8a740/attachment.htm