Quantcast

RFC: Component for Lookup tables

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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


     

_______________________________________________
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/dbix-class@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: RFC: Component for Lookup tables

Alexander Hartmaier
That sound like a use case for DBIx::Class::DynamicSubclass.

Am Montag, den 24.08.2009, 17:14 +0200 schrieb 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,
>       });
>
>
>
>
> _______________________________________________
> 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/dbix-class@...
--
BR Alex


*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH   Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*

_______________________________________________
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/dbix-class@...
Loading...