How do I customize the DBIx :: Class result classes in this simple case?

Question

Suppose I have the following simplified sample database consisting of three tables:

CREATE TABLE people ( person_id INTEGER PRIMARY KEY, person_name VARCHAR(100) ); CREATE TABLE events ( event_id INTEGER PRIMARY KEY, event_name VARCHAR(100), event_creator INTEGER CONSTRAINT fk_event_creator REFERENCES people(person_id) ); CREATE TABLE event_attendees ( event_id INTEGER NOT NULL CONSTRAINT fk_event_attendee_event REFERENCES events(event_id), person_id INTEGER NOT NULL CONSTRAINT fk_event_attendee_person REFERENCES people(person_id), role CHAR(1), -- O: organizer, P: performer, S: speaker, G: guest CONSTRAINT pk_event_attendees PRIMARY KEY (event_id, person_id) ); 

Given event_id , I can request the names of all the organizers, given person_id , I may want to find the names of all events in which this person is a guest or creator of the event, and so on. d.

I know how to do this using plain SQL. Could you tell me which result classes I need to configure and what relationships I need to specify when using DBIx :: Class ?

Answer 1

Are you familiar with DBIx :: Class :: Schema :: Loader ? Although it can be used in one-time scripts to dynamically create a DBIC schema in memory, it also has the ability to run in a single shot mode, where it writes schema definitions to disk so you can edit and build, and this is more advanced than you think .

First, you want the schema to actually be present in the database so that the loader can scan it. Then you do something like:

 perl -MDBIx::Class::Schema::Loader=make_schema_at \ -e 'make_schema_at("MyApp::Schema", {dump_directory=>"schema_out"},' \ -e '["dbi:DBType:connstring", "user", "pass"]);' 

(where "MyApp :: Schema" is the name of the package in which you want the generated schema classes to be available, and "schema_out" is the directory where you want them to be generated).

After that, you can edit the generated circuit classes or, if you find that the loader does a good enough job (or at least a good enough job, in which you don’t need to edit anything on "DON, T EDIT ABOVE THIN LINE", you can decide that the schema in the DB is your main source, and save the Schema :: Loader script, which will be run again to automatically generate classes automatically if the DB changes.

Update

Parts of the above schema are not processed correctly with DBIx :: Class :: Schema :: Loader v0.05002, because Sinan managed to find an error! Foreign key constraints were not analyzed correctly if the part of the “link” and the column name were not on the same line.

The bug was fixed in DBICSL git, but since the fix has not yet been released, this is how the relations look (I leave the column definitions to save space, they should be the same as the loader currently generates them).

EventAttendee.pm

 __PACKAGE__->set_primary_key(qw(event_id person_id)); __PACKAGE__->belongs_to( "event" => "MyApp::Schema::Result::Event", { event_id => "event_id" }, {} ); __PACKAGE__->belongs_to( "person" => "MyApp::Schema::Result::Person", { person_id => "person_id" }, {} ); 

Event.pm

 __PACKAGE__->set_primary_key("event_id"); __PACKAGE__->belongs_to( "event_creator" => "MyApp::Schema::Result::Person", { person_id => "event_creator" }, { join_type => "LEFT" }, ); __PACKAGE__->has_many( "event_attendees" => "MyApp::Schema::Result::EventAttendee", { "foreign.event_id" => "self.event_id" }, ); # Not auto-generated, but you probably want to add it :) __PACKAGE__->many_to_many( "people_attending" => "event_attendees" => "person" ); 

People.pm

 __PACKAGE__->has_many( # It might be wise to change this to "events_created" "events" => "MyApp::Schema::Result::Event", { "foreign.event_creator" => "self.person_id" }, ); __PACKAGE__->has_many( "event_attendees" => "MyApp::Schema::Result::EventAttendee", { "foreign.person_id" => "self.person_id" }, ); # Not auto-generated, but you probably want to add it :) __PACKAGE__->many_to_many( "events_attending" => "event_attendees" => "event" );