Hello. I recently began trying out Rose::DB::Object
and have run into some problems with foreign
keys/relationships. I'm working with an existing
postgresql database, so the naming conventions are my
own, not RDBO's.
My current problem revolves around a many-to-many
relation that is defined as follows.
lib/School/CourseSection.pm:
===========================>
package School::CourseSection;
use strict;
use base 'School::DB::Object';
use School::Course;
use School::Room;
use School::ClassInstructor;
use School::ClassStudent;
__PACKAGE__->meta->table( 'course_section' );
__PACKAGE__->meta->columns
( clid => { type => 'serial',
not_null => 1,
},
cid => { type => 'int',
not_null => 1,
},
sect_num => { type => 'smallint',
not_null => 1,
},
rmid => { type => 'int',
not_null => 1,
},
);
__PACKAGE__->meta->primary_key_columns( 'clid' );
__PACKAGE__->meta->add_unique_keys( 'cid',
'sect_num',
);
__PACKAGE__->meta->foreign_keys
( course => { class => 'School::Course',
key_columns=> { cid => 'cid' },
},
room => { class => 'School::Room',
key_columns=> { rmid => 'rmid' },
}
);
__PACKAGE__->meta->relationships
( instructors => { type => 'many to many',
map_class=>
'School::ClassInstructor',
map_from => 'the_class',
map_to => 'instructor',
},
students => { type => 'many to many',
map_class=>
'School::ClassStudent',
map_from => 'the_class',
map_to => 'student',
}
);
__PACKAGE__->meta->initialize;
package School::CourseSection::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::CourseSection' }
__PACKAGE__->make_manager_methods( 'classes' );
1;
<==========================
lib/School/ClassInstructor.pm:
=============================>
package School::ClassInstructor;
use strict;
use base 'School::DB::Object';
use School::CourseSection;
use School::Person;
__PACKAGE__->meta->table( 'class_instructor' );
__PACKAGE__->meta->columns
( clid => { type => 'int',
not_null => 1,
},
pid => { type => 'int',
not_null => 1,
},
);
__PACKAGE__->meta->primary_key_columns( 'clid',
'pid'
);
__PACKAGE__->meta->foreign_keys
( the_class => { class =>
'School::CourseSection',
key_columns=> { clid => 'clid'
},
},
instructor => { class => 'School::Person',
key_columns=> { pid => 'pid' },
}
);
__PACKAGE__->meta->initialize;
package School::ClassInstructor::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::ClassInstructor' }
__PACKAGE__->make_manager_methods( 'class_instructors'
);
1;
<=============================
lib/School/ClassStudent.pm:
==========================>
package School::ClassStudent;
use strict;
use base 'School::DB::Object';
use School::CourseSection;
use School::Person;
__PACKAGE__->meta->table( 'class_student' );
__PACKAGE__->meta->columns
( clid => { type => 'int',
not_null => 1,
},
pid => { type => 'int',
not_null => 1,
},
);
__PACKAGE__->meta->primary_key_columns( 'clid',
'pid',
);
__PACKAGE__->meta->foreign_keys
( the_class => { class =>
'School::CourseSection',
key_columns=> { clid => 'clid' },
},
student => { class => 'School::Person',
key_columns=> { pid => 'pid' },
},
);
__PACKAGE__->meta->initialize;
package School::ClassStudent::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::ClassStudent' }
__PACKAGE__->make_manager_methods( 'class_students' );
1;
<==========================
lib/School/Course.pm:
====================>
package School::Course;
use strict;
use base 'School::DB::Object';
__PACKAGE__->meta->table( 'course' );
__PACKAGE__->meta->columns
( cid => { type => 'serial',
not_null => 1,
},
cnum => { type => 'smallint',
not_null => 1,
},
short_cname=> { type => 'varchar',
length => 10,
not_null => 1,
},
long_cname=> { type => 'varchar',
length => 128,
},
);
__PACKAGE__->meta->primary_key_columns( 'cid' );
__PACKAGE__->meta->initialize;
package School::Course::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::Course' }
__PACKAGE__->make_manager_methods( 'courses' );
1;
<====================
lib/School/Room.pm:
==================>
package School::Room;
use strict;
use base 'School::DB::Object';
__PACKAGE__->meta->table( 'room' );
__PACKAGE__->meta->columns
( rmid => { type => 'serial',
not_null => 1,
},
short_rmname=> { type => 'varchar',
length => 8,
not_null => 1,
},
long_rmname => { type => 'varchar',
length => 32,
},
);
__PACKAGE__->meta->primary_key_columns( 'rmid' );
__PACKAGE__->meta->initialize;
package School::Room::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::Room' }
__PACKAGE__->make_manager_methods( 'rooms' );
1;
<==================
lib/School/Person.pm:
====================>
package School::Person;
use strict;
use base 'School::DB::Object';
__PACKAGE__->meta->table( 'person' );
__PACKAGE__->meta->columns
( pid => { type => 'serial',
not_null => 1,
},
lname => { type => 'varchar',
length => 32,
not_null => 1,
},
fname => { type => 'varchar',
length => 32,
not_null => 1,
},
miname => { type => 'varchar',
length => 32,
},
sex => { type => 'char',
length => 1,
},
dob => { type => 'date',
},
# picture => { type => 'oid',
# },
);
__PACKAGE__->meta->primary_key_columns( 'pid' );
__PACKAGE__->meta->initialize;
package School::Person::Manager;
use strict;
use base 'Rose::DB::Object::Manager';
sub object_class { 'School::Person' }
__PACKAGE__->make_manager_methods( 'people' );
1;
<====================
test-rdbo.pl:
============>
#!/usr/bin/perl
use strict;
use lib "/home/scott/projects/da-db/lib";
use School::CourseSection;
use School::Course;
use School::Person;
use School::Room;
use School::ClassInstructor;
local $Rose::DB::Object::Debug = 1;
local $Rose::DB::Object::Manager::Debug = 1;
my $class_iter =
School::CourseSection::Manager->get_classes_iterator
( require_objects => [ qw(instructors.instructor
course
room)
],
sort_by => 'lname, fname',
multi_many_ok => 1,
);
while (my $class = $class_iter->next) {
print $class->instructors->instructor->fname, " ",
$class->instructors->instructor->lname, "
teaches ",
$class->course->short_cname, ", section ",
$class->sect_num,
" in ", $class->room->short_rmname, ".\n";
}
<============
The problem occurs when I run the simple test program
above. I get the error:
School::CourseSection - no foreign key or relationship
named 'instructor' at
/usr/lib/perl5/vendor_perl/5.8.7/Rose/DB/Object/Manager.pm
line 656
Rose::DB::Object::Manager::get_objects('School::CourseSection::Manager',
'require_objects', 'ARRAY(0x804bd14)', 'sort_by',
'lname, fname', 'multi_many_ok', 1, 'return_iterator',
1, ...) called at
/usr/lib/perl5/vendor_perl/5.8.7/Rose/DB/Object/Manager.pm
line 240
Rose::DB::Object::Manager::__ANON__('School::CourseSection::Manager',
'require_objects', 'ARRAY(0x804bd14)', 'sort_by',
'lname, fname', 'multi_many_ok', 1) called at
test-rdbo.pl line 15
So, my questions:
1) Have I correctly defined the many-to-many relation?
2) Am I making an error in the way I have chained the
relations
in my query?
My CourseSection class was originally named Class, but
I suspected that was asking for trouble by way of
method name collisions, so I renamed it to
CourseSection. I've been able to get simple one-to-one
relations to behave as expected, but I'm stuck here.
Also, if it matters, Rose::DB is version 0.59 and
Rose::DB::Object is version 0.62.
Any help or insight anyone might offer would be much
appreciated.
Scott Karns
__________________________________________
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://ads.osdn.com/?ad_id=7637&alloc_id=16865&op=click
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
