[Dbix-class] RFC: Component for Lookup tables

[John Napiorkowski]

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,
	});