ile,
for having a good usage of Class::DBI, as far as i understood it you have to reference the classes the way you want to use them.
this means that the has_a and has_many does not need to be the same as your references in the database. they are much more a tool to get the data in a way you want (Class::DBI is creating SQL queries, joins out of the has_a and has_many methods you define)
if you need to get the entries from a user and vice versa, the best thing to do is to make has_many on both classes that need it.

Class::DBI is very poor documented, at least the main parts are not clear since the underlying SQL-structure for the examples is not visible to the user, therefore issues like correct naming of Classes and database-tables become a problem (if you don't know how Class::DBI deals with that)

i still didn't get the clue about how to use the Mapping described in the Class::DBI pod, and also still no clue about might_have. if anyone could describe me the both, i would be very happy :)

at least i know how to use has_a and has_many correctly (at least i think so, any hints and better ways very welcome ;)



has_a (1:1 - "one to one")
for that, the naming is very important. this means your packages have to have the same name as the database tables.

here is an example if every user could just have one role (i dropped the lookup table since it is not required for this example)

#!/usr/bin/perl
use Class::DBI;

package MyPackage;
use base ('Class::DBI');
FsDBI->set_db('Main', 'DBI:mysql:databasename', 'user', 'password');


package MyPackage::user;
use base ('MyPackage');
MyPackage::User->table('user');
MyPackage::User->columns(Essential => qw(id role_id name lastname));

MyPackage::User ->has_a (role_id => 'FsDBI::role');

package MyPackage::role;
use base ('MyPackage');
MyPackage::role->table('role');
MyPackage::role->columns(Essential => qw(id role_title));
[download]

from the calling script you would go like:
# test.pl

#!/usr/bin/perl
use MyPackage;

my $testuser=MyPackage::user->retrieve(1);
print "user with id 1 is $testuser \";
print "\n";
print "user with id 1 has role $testuser->role_id(1)";
[download]

has_many (1:m - "one to many")
for this, naming is not so important. that means for the same example as above, with a one-to-many relation, this would look like this

MyPackageO2M.pm

#!/usr/bin/perl
use Class::DBI;

package MyPackageO2M;
use base ('Class::DBI');
FsDBI->set_db('Main', 'DBI:mysql:databasename', 'user', 'password');

package MyPackage::Tester;
use base ('MyPackageO2M');
MyPackage::Tester->table('user');
MyPackage::Tester->columns(Essential => qw(id role_id name lastname));

# see the difference: the method name (ihavesomuchroles) can be comple
+tely different to the database-table.
# which means in a 1:m situation, even the Classes can have different 
+names than the originating database-tables.
# (MyPackage::user VS MyPackage::Tester)

MyPackage::Tester ->has_many (ihavesomuchroles => 'MyPackage::role','i
+d');
#  the 'id' argument to 'ihavesomuchroles' is the destination field fo
+r MyPackage::role

package MyPackage::role;
use base ('MyPackage');
MyPackage::role->table('role');
MyPackage::role->columns(Essential => qw(id role_title));
[download]

from the calling script you would here go like:
test_onetomany.pl

#!/usr/bin/perl
use MyPackageO2M;

my $testuser=MyPackage::user->retrieve(1);
print "user with id 1 is $testuser \n";
print "and has roles:\n";
@roles=$testuser->ihavesomuchroles;
foreach my $temp (@roles)
{ print "role: $temp->id."and".$temp->role_title."\n";}
[download]

--
and for your question about SQL-query logging:
i had the same problem and helped myself by enabling the log functionality from mysqld. the nice thing is that you can see all the queries sent to the server. i use it on a terminal-window with 'tail -f logfilename' which is a very comfortable solution. (works with the apache error.log also and is very nice to use for cgi/mod_perl development ;)