[Catalyst] Adding relationships to loaded CDBI models

[James Tolley]

Hello,

It's auspiciously coincidental that this discussion started just now,
because I've been dealing with trying to get mysql relationships loaded
automatically. I was frustrated with the options available and so I wrote
and just released Class::DBI::Loader::mysql::Grok to the CPAN.

The idea is to have the table names and column names (along with primary key
info) describe the table releationships. I would very much like to have some
feedback on it. I hope you'll forgive the long message, but here's the
perldoc (I just noticed that I failed to put might_have into the perldoc,
but that's supported as well - just use a primary key column to point to the
other table, with $table_name !~ /_ref$/i like the liner_notes table below)
and SQL:

NAME
    Class::DBI::Loader::mysql::Grok - Build Quality Table Relationships
    Automatically

SYNOPSIS
      use Class::DBI::Loader; # optional
      use Class::DBI::Loader::mysql::Grok;

      my $loader = Class::DBI::Loader->new(
        ...
        namespace     => "Music",
        relationships => 1,
      );

      my $class  = $loader->find_class('artist'); # $class => Music::Artist
      my $artist = $class->retrieve(1);

      for my $cd ($artist->cds) {
            print $cd->artist->name,"\n";
            print $cd->reldate->ymd,"\n"; # a Time::Piece object
      }

      # etc ...

DESCRIPTION
    If you name your tables and columns using some common sense rules,
    there's no need for you to do any work to have proper db abstraction.
    The following examples mostly follow the Class::DBI perldoc. To see
    where they differ (immaterially), see the test script and the
    accompanying SQL.

    The kinds of relationships which are created include:

    has_a
    In the example above, the cd table contains a column which matches the
    name of another table: artist. In this case, Music::Cd objects have a
    has_a relationship with Music::Artist. As a result, you can call
    $cd->artist->name, etc.

    has_many
    Similar to the has_a example above, the fact that the cd table contains
    a column which matches the name of another table means that
    Music::Artist objects have a has_many relationship with Music::CD. As a
    result, you can call $artist->cds->next->title, etc.

    has_many mapping
    When we're working with a mapping table like Music::StyleRef in the
    Class::DBI perldoc, which maps a many-to-many relationship, the mapping
    table name must =~ /_ref$/i, and the columns in that table must be named
    after the tables to which they refer.

    Time::Piece support
    While not a multi-table relationship, Time::Piece support is included
    for date, time, datetime, and timestamp types.

  EXPORT
    None by default, but it does redefine the _relationships routine in
    Class::DBI::Loader::mysql.

SEE ALSO
    Class::DBI Class::DBI::Loader, Class::DBI::Loader::mysql, Time::Piece

AUTHOR
    James Tolley, <james at bitperfect.com>

COPYRIGHT AND LICENSE
    Copyright (C) 2005 by James Tolley

    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself, either Perl version 5.8.6 or, at
    your option, any later version of Perl 5 you may have available.

__END__
DROP TABLE IF EXISTS artist;
CREATE TABLE artist (
 artistid int(10) unsigned not null primary key auto_increment,
 name  char(255)
);
INSERT INTO artist (name) VALUES ('Apocryphal');

DROP TABLE IF EXISTS cd;
CREATE TABLE cd (
 cdid int(10) unsigned not null primary key auto_increment,
 artist int(10) unsigned not null,
 title char(255) not null,
 reldate date not null
);
INSERT INTO cd (artist,title,reldate) VALUES (1,'First', '2001-01-01');

DROP TABLE IF EXISTS liner_notes;
CREATE TABLE liner_notes (
 cd  int(10) unsigned not null primary key,
 notes text,
 stamp timestamp
);
INSERT INTO liner_notes (cd,notes) VALUES (1, 'Liner Notes for First');

DROP TABLE IF EXISTS time_table;
CREATE TABLE time_table (
 id     int(10) unsigned not null primary key auto_increment,
 artist     int(10) unsigned not null,
 time_field time  default '12:12:12',
 date_field date  default '2005-01-01',
 datetime_field  datetime default '2005-01-01 12:12:12',
 timestamp_field  timestamp
);
INSERT INTO time_table (artist) VALUES (1);

DROP TABLE IF EXISTS style;
CREATE TABLE style (
 styleid int(10) unsigned not null primary key auto_increment,
 style char(30) not null
);
INSERT INTO style (style) VALUES ('Blues'),('Funk'),('Country');

DROP TABLE IF EXISTS style_ref;
CREATE TABLE style_ref (
 cd  int(10) unsigned not null,
 style int(10) unsigned not null,
 primary key (cd,style)
);
INSERT INTO style_ref (cd,style) VALUES (1,1),(1,2),(1,3);


Thanks!

James