FAQ
Hello,

I discovered an interesting bug/feature, in which a template seems to be executing an SQL insert without that being intended or even visible in the code! I think it's worth explaining exactly what's going on so please pardon the length.

In my Apache/FastCGI/Catalyst/MySQL/TT/DBIx::Class application (called CatMgr) I have Customer and Transaction objects. An admin portal shows a list of recent transactions, which are created when an Instant Payment Notification from PayPal says a payment was made.

Anyway, the transaction table has a field called customer storing the ID of a record in the customer table. I specify the relation ORMwise in CatMgrDB/Transaction.pm with:
__PACKAGE__->belongs_to( customer => CatMgrDB::Customer );

I decided to be clever (boom) and get the template, which is only handed a resultset with rows from the transaction table, to look up the company name of the associated Customer (r.customer.b_company, where r is a row in the resultset) and not have to make a more complicated search statement in the controller.

The thing is, then I decided to let admins add new transactions without an associated customer, to record in the database transactions that were made offline. Instead of assigning r.customer to the id of the company running the system I just say 0 (not using nulls). What happens is, somehow the template forces Catalyst (DBIx::Class) to create a NEW CUSTOMER if there wasn't one already. This is bad, bad, bad. Every time an admin views the list of transactions, it seems I get 4 blank customer records silently added simultaneously, or maybe even more, depending on what kind of records are shown on the current page it seems. I discovered this by matching the FastCGI error log (which is where the -Debug output goes.. all 300MB of it...) against the modification date of the new customer records.

It seems like I need a cascading_inserts => 0 style attribute here, though I can hack around it but I would like to know what the right way to deal with this. If I can leave the code as is and toggle a single attribute (maybe I should remove that belongs_to or convert it to a might_have?) that would be nice. It seems to be an easy pitfall. I can't find any documentation about a relation forcing objects to autovivify, it seems like a bug. Can anyone comment on this?

Thanks,

Matt



____________________________________________________________________________________
Need a vacation? Get great deals
to amazing places on Yahoo! Travel.
http://travel.yahoo.com/

Search Discussions

  • Matt Rosin at Jun 13, 2007 at 1:36 pm
    P.S. The TT code that causes Customer records to be created in the database by autovivification is the statement "r.customer.b_company" below:

    [% FOREACH r = Catalyst.stash.transactions %]
    ...
    [% TRY %][% r.customer.b_company %][% CATCH %]Not defined[% END %]
    ...
    [% END %]

    I can confirm that the words "Not defined" show up for 4 records on page 1 of results, which matches the 4 blank customer records. Putting a comment ("#") just before the letter r stops the autovivification.

    Unfortunately I cannot seem to solve it in TT though. "r.customer.defined" or "r.customer.b_company.defined" still autovivify I think, r.customer tests as > 0 even if not (since it is a structure I guess), and checking r.customer.id also is useless. Also the description of might_have does not sound like it will help, and could hurt my app. How to solve this, aside from using get_column in the perl code (which I have done to solve other problems).

    Thanks,
    Matt



    ____________________________________________________________________________________
    Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
    http://sims.yahoo.com/
  • Jason Kohles at Jun 13, 2007 at 3:55 pm

    On Jun 13, 2007, at 8:17 AM, Matt Rosin wrote:

    The thing is, then I decided to let admins add new transactions
    without an associated customer, to record in the database
    transactions that were made offline. Instead of assigning
    r.customer to the id of the company running the system I just say 0
    (not using nulls). What happens is, somehow the template forces
    Catalyst (DBIx::Class) to create a NEW CUSTOMER if there wasn't one
    already. This is bad, bad, bad. Every time an admin views the list
    of transactions, it seems I get 4 blank customer records silently
    added simultaneously, or maybe even more, depending on what kind of
    records are shown on the current page it seems. I discovered this
    by matching the FastCGI error log (which is where the -Debug output
    goes.. all 300MB of it...) against the modification date of the new
    customer records.
    The relationship docs imply that belongs_to will do the right thing
    if the relationship is optional, but also indicates that it
    determines if the relationship is optional by whether the foreign key
    can be NULL or not, if you have defined that column as NOT NULL, you
    may be running into problems....

    If the relationship is optional -- i.e. the column containing
    the
    foreign key can be NULL -- then the belongs_to relationship
    does the
    right thing. Thus, in the example above "$obj->author" would
    return
    "undef". However in this case you would probably want to set
    the
    "join_type" attribute so that a "LEFT JOIN" is done, which makes
    complex resultsets involving "join" or "prefetch" operations
    work
    correctly. The modified declaration is shown below:

    # in a Book class (where Author has_many Books)
    __PACKAGE__->belongs_to(author => 'My::DBIC::Schema::Author',
    'author', {join_type => 'left'});

    --
    Jason Kohles
    email@jasonkohles.com
    http://www.jasonkohles.com/
    "A witty saying proves nothing." -- Voltaire
  • Matt Rosin at Jun 13, 2007 at 4:44 pm
    Hi, thanks for your comment. But,

    1. I use NOT_NULL so my program has less opportunity to ever crash :) no really I saw DBIxC crash in the past I'm pretty sure though cannot document now, perhaps the calling a method on a nonexistent object error... I would prefer it to say "0" than to crash. Perhaps other people have smarter ways to improve robustness...

    2. At any rate, I do not think it is doing the right thing, regardless. It is a bug if you cannot test for whether the related object exists, and when an SQL insert is generated on another table. In particular I think this is happening mainly during interpretation of TT2's dot notation. I would expect $mytransaction->customer->b_company not to create a Customer object in the db too. Am I wrong in this? So should I either allow NULL in transaction.customer or else use might_have? The might_have docs suggest cascading updates too...

    Matt


    ____________________________________________________________________________________
    Park yourself in front of a world of choices in alternative vehicles. Visit the Yahoo! Auto Green Center.
    http://autos.yahoo.com/green_center/
  • Wade Stuart at Jun 13, 2007 at 5:39 pm

    Matt Rosin wrote on 06/13/2007 10:44:36 AM:

    Hi, thanks for your comment. But,

    1. I use NOT_NULL so my program has less opportunity to ever crash
    :) no really I saw DBIxC crash in the past I'm pretty sure though
    cannot document now, perhaps the calling a method on a nonexistent
    object error... I would prefer it to say "0" than to crash. Perhaps
    other people have smarter ways to improve robustness...
    To again paraphrase Jason, he is saying the way dbic handles belongs_to in
    this situation is that it will create a new row if the relationship is not
    optional and by setting the FK as not null you are in effect saying that
    "this relationship is not optional". In other words by making the fk
    enforce not null you are asking for this behavior.
    2. At any rate, I do not think it is doing the right thing,
    regardless. It is a bug if you cannot test for whether the related
    object exists, and when an SQL insert is generated on another table.
    No, it is enforcing the "not optional" relationship by "doing the right
    thing".
    In particular I think this is happening mainly during interpretation
    of TT2's dot notation. I would expect
    $mytransaction->customer->b_company not to create a Customer object
    in the db too. Am I wrong in this?
    Yes, you have specified by setting the fk as not null and using belongs_to
    that it should do just this.
    So should I either allow NULL in
    transaction.customer or else use might_have? The might_have docs
    suggest cascading updates too...
    Your business and application logic should determine the relationships and
    how they are enforced. Should your "transactions" always be linked to a
    customer? Should you have a dummy customer that can link to transactions
    added to the system in this code path? Does ...? Maybe you would get
    better advice if you removed the "How" you currently have implemented it
    and describe the business/app logic that you want to implement in English.
    Something such as:

    A user may create a transaction which should be linked to a customer
    record. Users may also create transactions which are not linked to
    customer records. Transactions may be linked to multiple customer records.
    Customer records may or may not have any associated transactions.

    The more specific your requirements the better the advice you will receive.


    -Wade

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcatalyst @
categoriescatalyst, perl
postedJun 13, '07 at 1:17p
activeJun 13, '07 at 5:39p
posts5
users3
websitecatalystframework.org
irc#catalyst

People

Translate

site design / logo © 2022 Grokbase