Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Class::DBI and - possibly - complex data structures

by Evil Attraction (Novice)
on Sep 05, 2003 at 19:28 UTC ( #289332=perlquestion: print w/ replies, xml ) Need Help??
Evil Attraction has asked for the wisdom of the Perl Monks concerning the following question:

I've been recommended Class::DBI by so many now, that I fell down on my kneed and tried it out properly. I've been fiddling with it previously too, but didn't like the outcome performance-wise. Anyway - I'm ready to give a new try.

My little test project deserves some help, though, as I'm having problems dealing with "complex" data structures. They aren't that complex, really, but putting them into a Class::DBI context seems more than trivial.
I have these tables:

person
person_id mediumint unsigned auto_increment primary key, sex char(1) default '?' not null, modified date not null

name
name_id mediumint unsigned auto_increment primary key, name varchar(255) not null

person_name
person mediumint unsigned not null, /* references 'person' */ firstname mediumint unsigned not null, /* references 'name' */ lastname mediumint unsigned not null /* references 'name' */

As you can see, one Person can have more than one name. One extra question; it seems like Class::DBI forces you to name references without any ending '_id' if you want some nifty named methods in your classes?

Anyways - here are my classes;

Person.pm
package Person; use strict; use warnings; use base 'My::DBI'; Person->table( 'person' ); Person->columns( All => qw(person_id sex modified) ); Person->has_many( names => 'PersonName' ); 1;

Name.pm
package Name; use strict; use warnings; use base 'My::DBI'; Name->table( 'name' ); Name->columns( All => qw(name_id name) ); 1;

PersonName
package PersonName; use strict; use warnings; use base 'My::DBI'; PersonName->table( 'person_name' ); PersonName->columns( All => qw(person firstname name) ); PersonName->has_a( firstname => 'Name' ); PersonName->has_a( lastname => 'Name' );

My script might look something like this:
#!/usr/bin/perl # use strict; use warnings; use Person; my $Person = Person->retrieve( 1 ); my $names = $Person->names(); while ( my $Name = $names->next() ) { print $Name->lastname()->name() . ', ' . $Name->firstname()->name( +) . "\n"; }

It works almost, except that even though the person has 2 different names, only one of them gets listed (twice).

Any ideas?

Comment on Class::DBI and - possibly - complex data structures
Select or Download Code
Re: Class::DBI and - possibly - complex data structures
by Ovid (Cardinal) on Sep 05, 2003 at 19:58 UTC

    Your PersonName class has a bug:

    PersonName->columns( All => qw(person firstname name) );

    Since you don't have a 'name' field defined, that should be 'lastname'.

    Why does each person have more than one name, though? If it's the case that one person can be "John Smith" and "Jane Doe", it's appropriate to have names in a separate table. If, however, you mean that one person has a first and a last name, then you probably want to move those into your person table. If that's the case, you can collapse this down to one table.

    package Person; use strict; use warnings; use base 'My::DBI'; __PACKAGE__->table( 'person' ); __PACKAGE__->columns( All => qw(person_id sex modified first_name last +_name) );

    If, however, a person can really have more than one name (aliases), then two tables can suffice.

    package Person; use strict; use warnings; use base 'My::DBI'; Person->table( 'person' ); Person->columns( All => qw(person_id sex modified) ); Person->has_many( names => 'PersonName' ); package PersonName; use strict; use warnings; use base 'My::DBI'; PersonName->table( 'person_name' ); PersonName->columns( All => qw(person_name_id person_id first_name las +t_name) );

    And in your actual code:

    my $person = Person->retrieve( $person_id ); while ( my $name = $person->names ) { printf "%s, %s\n", $name->last_name, $name->first_name; }

    It also looks like you could use a brush-up on database design. Microsoft has a decent article on Database normalization basics. It's fairly concise and easy to understand -- as much as the topic is easy to understand, that is.

    Cheers,
    Ovid

    New address of my CGI Course.

      The reason I've modelled the database like this, is to save space. I've created a utility which converts genealogical data from the Gedcom format to a MySQL-driven database. When dealing with genealogy it's fairly common to have individuals with more than one name; either you're not sure about the person's name, and want to refer to more than one names, or the person have a marriage name etc.

      That name vs. lastname "bug" was a typo, and it doesn't affect the real problem here.

      For simplicity, I could have had a name table looking like this:

      person_id mediumint unsigned not null, /* references 'person' */ firstname varchar(255) not null, lastname varchar(255) not null

      ...but that's not really what I want. As I said, I want to save disk space, and by not duplicating data in the database I save a lot of space in this example.

      However, after some reading I discovered Class::DBI::Join which seems to do what I'm after. The only problem is: I still can't get it to work. Any examples on using that class would have been great, as the example doesn't do it for me.

      Thanks again!
Re: Class::DBI and - possibly - complex data structures
by perrin (Chancellor) on Sep 05, 2003 at 20:29 UTC
    I agree with Ovid -- this is a very strange schema design. However, assuming you did it this way just to see if Class::DBI can handle something strange, there is a much more elegant way to deal with this. You are trying to use a linking table here, which is supported by Class::DBI using has_many (see the style refs example in the docs) but it's confusing. Another approach is to write the query directly in the names class, like this:
    package Name; ... __PACKAGE__->set_sql(by_person => q/ SELECT n.name FROM name n, person_name pn WHERE (n.name_id = pn.firstname OR n.name_id = pn.lastname) AND pn.person = ? /); package Person; ... sub names { my $self = shift; Name->search_by_person($self); }
    it seems like Class::DBI forces you to name references without any ending '_id' if you want some nifty named methods in your classes?

    No, you can name things whatever you want. Look at the section on changing accessor names in the docs.

(OT)Re: Class::DBI and - possibly - complex data structures
by LameNerd (Hermit) on Sep 05, 2003 at 20:39 UTC
    Just curious about the data modeling of your tables ...

    Why not define table person_name like so ...
    person mediumint unsigned not null, /* references 'person' */ name mediumint unsigned not null, /* references 'name' */ seq mediumint unsigned not null /* determines firstname middlenam +e lastname */

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://289332]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (14)
As of 2014-07-10 13:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (211 votes), past polls