[Dbix-class] Problem with scalar ref when inserting complex objects

[Jose Fonseca]

Hi, this is my first message here, so first of all let me thank you all for
creating DBIx::Class and for the support you all give here. I've been
lurking for a while on the web transcriptions and learn tons from it. (And
excuse me for the large message!).

SYNOPSIS
The SELECT COUNT(*) queries which precede some complex object inserts is
generated incorrectly when a scalar ref is passed as literal to be passed
directly to the DB. This is a documented feature of SQL::Abstract but does
not produce the expected behavior on DBIx::Class(or does it?).

In my case inserting a single CustomerLogin works fine, with the scalar ref
and all.

BUT when inserting that exactly same CustomerLogin within a Customer, for
example, with the following hashref

my $create_hash =3D {
customer_name =3D> "Obi Wan",
customer_address =3D> "I live here",
logins =3D> [
    {
       login =3D> 'Obi',
       membersince =3D> \'NOW()'
    }
]
}

DBIx::Class does a count(*) from logins first with broken SQL as described
below.

DESCRIPTION
When you pass a ref scalar to a ResultSet()->create(), it correctly passes
the value directly to the DB.

For instance when inserting a \'my string' it places 'my string' within the
values() list, without a placeholder.  That is all fine, it's the documented
SQL::Abstract behavior.

The problem happens when you give DBIx::Class a scalar ref for INSERTion but
it does a count(*) before the INSERT. It then inserts the literal into the
count(*) query's WHERE string without an operator. You inteded the literal
only for an INSERT but DBIx::Class used it for a SELECT too.

EXAMPLE
$SCHEMA->resultset('Base::Customer')->create({[.....SNIP...], mycolumn =3D>
\'my string' });

DBIx::Class for some reason does something similar to a

$SCHEMA->resultset('Base::Customer')->search({ [.....SNIP...],  mycolumn =
=3D>
\'my string' },{})->count();

which produces

SELECT COUNT( * ) FROM customers me WHERE ( [...sNIP...] *AND mycolumn 'my
string'*  )

It is the correct behavior from SQL::Abstract(so you could do something
complex with that literal), but seems to become an unwanted collateral
behavior when used through DBIx::Class.

I can overcome this changing my code, I just thought I'd share this
bug(feature?) with you, in case it is unintended.

Thanks once again and best wishes to all.

Regards,
Ze
http://zefonseca.com/

PS >> I was originally trying to use the database's NOW() function for
example passing in \'NOW()' to ResultSet->create(), because the other
solutions(passing DateTime->now at ResultSet->create() time) will tie the
application time to the database time, tightly coupling then - which has
caused me some troubles in the past. It works fine inserting single objects
with colums which need NOW() as their initial value. But if you take  that
exact same object, and try to insert it within the insertion of another
object, it breaks down because of the select count(*) which the ORM does.

NOW() is just one example of valid use of this feature of DBIx::Class at
insert time that breaks down due to the above issue, because MySQL as you
know does not support "default FUNCTION()" columns to be defined and this is
how I had solved it.

It works fine for single table inserts, but when you create() complex
objects DBIx::Class does some SELECT COUNT(*) queries, and that is where it
breaks down.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080213/cdc=
40845/attachment-0001.htm