[Dbix-class] Re: Relations and inflation

[Matt Rosin]

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. 

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).

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.

Incidentally the person asking about a design tool might be interested in dbdesigner which will import/export from uml diagram to mysql and postgresql. Though I think the arrows need to be smarter, the diagram it made from my db was clean but had too many arrows to read easily. http://fabforce.net/dbdesigner4/

Thank you very much, and regards from Tokyo where the rainy season seems to be starting..

Matt Rosin

root at host1 [/app/reltest]# ls -R
.:
./  ../  CatMgrDB/  CatMgrDB.pm*  reltest.pl*
./CatMgrDB:
./  ../  Coupon.pm  Listing.pm*

root at host1 [/app/reltest]# cat reltest.pl
#!/usr/local/bin/perl -w
# by Matt Rosin
use strict;
use warnings;
use CatMgrDB;
# this was a catalyst connection using config(schema_class => 'CatMgrDB_Model', ...)
my ($DBUSER, $DBPASS) = ('lalala','lalala');
my $schema = CatMgrDB->
  connect('dbi:mysql:TABLENAME',$DBUSER,$DBPASS,{'AutoCommit' => '1'});

# would be CatMgrDB::Listing->find(4499);
my $listing = $schema->resultset('CatMgrDB::Listing')->find(4499);
print "Name: " . $listing->name . "\n";
my $coupon = $listing->coupon;
if (defined $coupon) { print "Offer: " . $coupon->offer . "\n"; }
else { print "Coupon is undefined\n"; }

root at host1 [/app/reltest]# cat CatMgrDB.pm
package CatMgrDB;
# pulled code out of Catalyst modules
use base qw/DBIx::Class::Schema/;
__PACKAGE__->load_classes({ CatMgrDB => [qw/Listing Coupon/] });
1;

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/);
__PACKAGE__->set_primary_key('id');
#__PACKAGE__->might_have(coupon => CatMgrDB::Coupon); #1 inflation fails with this
#__PACKAGE__->has_one( coupon => CatMgrDB::Coupon ); #2 fails with this too 
#__PACKAGE__->has_one( coupon => CatMgrDB::Coupon, 'listing'); #3 inflation works but I don't know why...? Is it interpreting that last 'listing' as a join condition?
__PACKAGE__->belongs_to( coupon => CatMgrDB::Coupon, 'coupon', {'cascade_delete' => 1} ); #4 inflation works
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
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to( listing  => 'CatMgrDB::Listing', 'listing', {join_type => 'left'});
1;

Here are the relevant bits of database:

desc listing (db is mysql)
| Field      | Type         | Null | Key | Default           | Extra          |
| coupon     | int(11)      |      |     | 0                 |                |

select id,name,coupon from listing where id=4499
id      name    coupon
4499    Telebody Inc.   36

select id,listing,offer from coupon where id=36
id      listing offer
36      4499    Free website estimate


       
____________________________________________________________________________________
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/