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

[Jose Fonseca]

Hi Matt, I'm out of the office for  few days. I'll give you a better call
stack / debug trace once I return. What I did to be able to get the system
out the door is do 2 separate inserts, which is ok but I thought inserting a
complex hash was really elegant.

Question: Why does it select count(*) before the insert only when it's a
complex object(within a has_many relation)?   Shouldn't it count(*) every
time then?

Also: it'll never match any rows when there is temporal data that changes
with each insert, timestamps or auto_increment columns.  In my case it does
soemthing like:

SELECT COUNT(*) FROM table me WHERE me.col1 =3D val1 AND me.col2 =3D val2 A=
ND
me.inserttime NOW();

Even if the syntax was right at inserttime =3D NOW() it could very well be =
the
same val1 and val2 but NOW() would have skewed....see what I mean?

Regards,
Ze


On Thu, Feb 14, 2008 at 2:14 PM, Matt S Trout <dbix-class at trout.me.uk>
wrote:

> On Wed, Feb 13, 2008 at 03:52:56PM -0200, Jose Fonseca wrote:
> > 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'*  )
>
> Bugger.
>
> Can you backtrace it and find where it's making that query from? I'm
> betting
> it's a numification of the resultset that's triggering the overload that
> shouldn't be happening in the first place.
>
> If that's true we can simply make it not do that query at all :)
>
> --
>      Matt S Trout       Need help with your Catalyst or DBIx::Class
> project?
>   Technical Director
> http://www.shadowcat.co.uk/catalyst/
>  Shadowcat Systems Ltd.  Want a managed development or deployment
> platform?
> http://chainsawblues.vox.com/
> http://www.shadowcat.co.uk/servers/
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.rawmode.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080215/5e6=
ed59b/attachment-0001.htm