I know we all must often run into schema designs like the following:
## Column details removed for clarity
package MyApp::Schema::Result::Gender; {
use base 'MyApp::Schema::Result';
__PACKAGE__->table('gender');
__PACKAGE__->add_columns(qw/gender_id label/);
}
package MyApp::Schema::Result::Person; {
use base 'MyApp::Schema::Result';
__PACKAGE__->table('person');
__PACKAGE__->add_columns(qw/person_id name fk_gender_id);
__PACKAGE__->belongs_to(
gender => 'MyApp::Schema::Gender',
{'foreign.fk_gender_id' => 'self.fk_gender_id'}
);
}
After all, this is type of normalization is a basic pattern. However, as far
as I know we don't have a good system for installing the necessary data at
populate time. What I mean here is that for the above database to be useful,
you need some rows inserted into the 'gender' table, ie 'female', 'male',
'transgendered', etc.
Things I've seen people do to work around this lack would include manually
created SQL files to run post deployment, manually editing the sql generated by
$storage->deploy, overloading deploy to run a set of ->create statements, or
trying to use the Fixtures module.
All the above solutions work, but have severe lacking. For example, all the
solutions involving editing sql files fail to achieve the degree of database
freedom DBIC offers out of the box. Additionally, by not having DBIC itself
aware of the lookup information, you lose out on the chance to apply roles to
your classes based on the lookup in a clear an easy manner. For example, I
often have a 'state' table as a lookup normalization on a persons address.
However, I also often have the need to apply special logic to people living in
the state of California, since that state has a lot of special rules. Right
now this usually manifests itself as a lot of ugly conditional logic. It would
be really nice if DBIC itself could have enough awareness to apply components
or roles at runtime when a row returned contain a particular lookup value.
Lastly, it would be great if this system could hook cleanly into the DBIC
framework for doing database diffs. That way if you had to alter something,
like a list of Countries, for example, you'd get a head start on the generated
sql between versions.
So here's my first go at attempting to create some syntax for this. Yeah,
there's a lot to do with it, but this is a start. It's possible we'd rather
have the lookup data encapsulated in a different class, just to keep the the
class files clean. Please your comments and suggestions very welcomed!
package MyApp::Schema::Result::Gender; {
use base 'MyApp::Schema::Result';
__PACKAGE__->components('Lookup');
__PACKAGE__->table('gender');
__PACKAGE__->add_columns(
gender_id => {
data_type => 'varchar',
size => '36',
is_nullable => 0,
},
label => {
data_type => 'varchar',
size => '12',
is_nullable => 0,
},
);
__PACKAGE__->set_primary_key('gender_id');
__PACKAGE__->uuid_columns('gender_id');
__PACKAGE__->lookup_unique_label('label');
__PACKAGE__->populate_lookup(
['lookup'],
['male'],
['female],
['transgendered'],
['unknown'],
);
}
package MyApp::Schema::Result::Person; {
use base 'MyApp::Schema::Result';
__PACKAGE__->table('person');
__PACKAGE__->add_columns(
person_id => {
data_type => 'varchar',
size => '36',
is_nullable => 0,
},
fk_gender_id => {
data_type => 'varchar',
size => '36',
is_nullable => 0,
},
name => {
data_type => 'varchar',
size => '24',
is_nullable => 0,
},
);
__PACKAGE__->set_primary_key('person_id');
__PACKAGE__->uuid_columns('person_id');
__PACKAGE__->belongs_to(
gender => 'MyApp::Schema::Gender',
{'foreign.fk_gender_id' => 'self.fk_gender_id'}
);
}
my $person = $schema->resultset('Person')->create({
name='Joe Somebody',
gender=>{label=>'male'},
});
my $female = $schema->resultset('Gender')->find_female;
my $person = $schema->resultset('Person')->create({
name='Mary Somebody',
gender=>$female,
});
_______________________________________________
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/[email protected]
