Re: Class::DBI global questions and references (partly explained, waiting for more ;)

[parasew]

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));

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)";

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));

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";}

--
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 ;)

[perrin]

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;
}

[parasew]

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

[ile]

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