[Dbix-class] Re: Relations and inflation

[Matt S Trout]

On Sun, Jun 24, 2007 at 06:27:29AM -0700, Matt Rosin wrote:
> Hello,
> 
> Thanks for your reply, Matt. I've boiled down code from my Catalyst project as follows.
> A bit disconcerting that the problem remains even though I've refactored it into a command line program. 

Not really, the root of the problem is in the database design, see below.

> In CatMgrDB::Listing below, I have added a number from 1 to 4 after the comment, so that 4 different relationships can be tried.
> 
> When the relation in Listing.pm is "belongs_to" (i.e. case 4) inflation works and the output is:
> # ./reltest.pl
> Name: Telebody Inc.
> Offer: Free website estimate
> 
> However when it is changed to "might_have" (case 1) the output is:
> # ./reltest.pl
> Name: Telebody Inc.
> Coupon is undefined
> 
> It also fails for case 2 (has_one) but (why??) it works for case 3 (wierd misuse of has_one, I think).

No, actually, case 3 is the closest you've come so far to something sane :)

> Listings follow.
> 
> Any light you can shed on this would be greatly appreciated. 
> 
> Meanwhile I have discovered something interesting, if I delete a coupon without zeroing the listing.coupon field, a new coupon with the same id is autovivified but with zeroes in all other fields. Which makes it impossible to delete with the existing code that looks at coupon.listing ...
>
> I really would like a way to disable autovivification. I don't believe it is "doing the right thing" if it keeps surprising me. Well I suppose I should be using might_have (if inflation would work with it) or allow nulls in the table.

It keeps surprising you because the defaults work for a normalised schema.

Yours isn't.

> root at host1 [/app/reltest]# cat CatMgrDB/Listing.pm
> package CatMgrDB::Listing;
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/PK::Auto Core/);
> __PACKAGE__->table('listing');
> __PACKAGE__->add_columns(qw/id category customer banner whitepages type flagged disabled name address address2 city state zip phone offertext site directions locations menu template usermemo adminmemo codememo package coupon modified/);

Drop the coupon field, it's duplicate information.

> __PACKAGE__->set_primary_key('id');
> #__PACKAGE__->might_have(coupon => CatMgrDB::Coupon); #1 inflation fails with this

This is right, and will work perfectly once you've made the other changes
outlined below.

> 1;
> root at host1 [/app/reltest]# cat CatMgrDB/Coupon.pm
> package CatMgrDB::Coupon;
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/PK::Auto Core/);
> __PACKAGE__->table('coupon');
> __PACKAGE__->add_columns(qw/id listing available flagged offer terms usermemo/); # and other columns

Drop the id field, it's unecessary since you only have at most one coupon per
listing.

> __PACKAGE__->set_primary_key('id');

Set listing as the primary key (and consider renaming to listing_id and
altering the third arg to the belongs_to rel appropriately).

> __PACKAGE__->belongs_to( listing  => 'CatMgrDB::Listing', 'listing', {join_type => 'left'});
> 1;

Drop the join_type, it's wrong unless you can have coupons without a listing,
which from what you've said so far you can't.

And with the above done, your might_have and belongs_to relations will work
fine, there'll be no unexpected autovivification -and- your database will
be in something approaching normal form.

Plus, you'll be able to set up a foreign key to match the belongs_to from
listing to coupon so your database is enforcing relational integrity - your
current design with circular relationships strongly suggests to me you
don't have foreign keys at all, which is a little on the dangerous side.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director    Want a managed development or deployment platform?
 Shadowcat Systems Ltd.  Contact mst (at) shadowcatsystems.co.uk for a quote
http://chainsawblues.vox.com/             http://www.shadowcatsystems.co.uk/