Now about your questions:

1. User has_many user_roles, user_role has a role. You can add more if you want to, but that's enough to ask a user what it's roles are.

2. I don't know what you're talking about, but retrieve_all is a built-in method.

3. You don't retrieve users "together with" roles. You retrieve users, and if you want the roles for a user you ask the user for them.

4. Did you read the article on perl.com?

5. No.

To see the SQL queries, turn on DBI_TRACE (see the DBI docs).

#!/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]

#!/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]

#!/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]

#!/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]

the naming is very important. this means your packages have to have the same name as the database tables.

Actually, this is not true. When you call has_a, you just have to give it the name of an accessor in the current class that will return the primary key for the other class. You give your accessors any name you like. For example, I could change your example like this:

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

__PACKAGE__->has_a(wild_and_crazy_role => 'MyPackage::Role');

sub accessor_name {
    my ($class, $column) = @_;
    $column =~ s/role_id/wild_and_crazy_role/;
    return $column;
}
[download]

thanks again perrin for your help on the Class-Dbi-Talk.
i solved all the problems i had with Class::DBI, see more on this mail posting.
for additional Class::DBI information here on perlmonks, i can also recommend the Class::DBI Intro node by trs80

Hey thanx, this ok somehow i got it after so much trying arround.... so the references are working now,
i can select and display stuff... but this brings me to another problem now -> what about the updating of records?

if i have one three tables and they are referenced between, how do i put and assign
values to each of them?

1. i should make ->create() for each table and pass the right values?
2. there is some way to send values to one ->create() (and all referenced tables are updated)?
3. or there is some magical thing which does everything by itself (in the middle_ref table to save the last
inserted ids from other two tables?)
4. and if this magical thing is real, is there a difference in syntax for creating
records in the database if tables are referenced on diferent way (has_a or has_many)?

and parasew thanx for the mysql /log thing ! this is very handy for developement,
you can see exactly what queries are going inside and traceroute some mistakes :)
very handy this should be placed as troubleshooting trick in the
manual :)

thanx
ile