Let's step back a bit and look at what you're really trying to do.
Databases are about storing "things" in persistent storage. Those "things" are "rows", which loosely represent something.
But in OO programming, all "things" are Objects, so what we would like to do would be to store objects, not rows, in databases. There are a couple of approaches: use an OODBMS, or have some way of converting your objects into an RDBMS format.
For the latter approach, I strongly recommend the Tangram library in CPAN.
Here is a simple "movie database" in Tangram...
#!/usr/bin/perl use LazyObject; use warnings; # "LazyObject" just gives you simple "new", "get", "set" members package Movie; @ISA = (LazyObject); package Person; @ISA = (LazyObject); package Job; @ISA = (LazyObject); package Credit; @ISA = (LazyObject); package Location; @ISA = (LazyObject); package main; use strict; use Tangram; use Tangram::Schema; use Tangram::RawDateTime; use Tangram::IntrSet; use Tangram::mysql; my $schema = new Tangram::Schema { classes => [ 'Credit' => { fields => { # nothing, this is an association class with no data. }, }, 'Movie' => { fields => { string => [ qw(title) ], int => [ qw(release_year) ], # this means there is a set of 'Credit' objects # related to this 'Movie' object. iset => { credits => 'Credit', }, }, }, 'Person' => { fields => { string => [ qw(name) ], rawdatetime => [ qw(birthdate) ], ref => [ qw(birth_location) ], # This person also has a set of credits iset => { credits => 'Credit', }, }, }, 'Job' => { fields => { string => [ qw(job_title) ], # As does this job iset => { credits => 'Credit', }, }, }, 'Location' => { fields => { string => [ qw(location) ], ref => [ qw(parent_location) ], }, }, ], }; my ($dsn, $user, $pass) = ("dbi:mysql:database=movies", "root", ""); print "Connecting to the database\n"; my $dbh = DBI->connect($dsn, $user, $pass); print "Creating tables with SQL command:\n"; Tangram::mysql->deploy($schema); print "Now creating tables...\n"; Tangram::mysql->deploy($schema, $dbh); print "Disconnecting...\n"; $dbh->disconnect() or warn $DBI::errstr;; # now connect to it as if we were a normal program print "Connecting to Storage...\n"; my $storage = Tangram::Storage->connect($schema, $dsn, $user, $pass); # Insert some data do { print "Building data objects...\n"; my @locations = ( new Location( location => "Grappenhall", parent_location => new Location ( location => "Warrington", parent_location => new Location ( location => "Cheshire", parent_location => new Location ( location => "England", parent_location => new Location ( location => "United Kingdom" ) ) ) ) ), new Location( location => "Dallas", parent_location => new Location ( location => "Texas", parent_location => new Location ( location => "United States" ) ) ), ); my @credits = ( map { new Credit } (1..5) ); my @jobs = ( new Job( job_title => "Dr. Frank-N-Furter", credits => Set::Object->new( $credits[0] ) ), new Job( job_title => "Wadsworth", credits => Set::Object->new( $credits[1] ) ), new Job( job_title => "Prosecutor", credits => Set::Object->new( $credits[2] ) ), new Job( job_title => "Long John Silver", credits => Set::Object->new( $credits[3] ) ), new Job( job_title => "Dr. Scott", credits => Set::Object->new( $credits[4] ) ), ); my @movies = ( new Movie( title => "Rocky Horror Picture Show", release_year => 1975, credits => Set::Object->new( @credits[0, 4] ) ), new Movie( title => "Clue", release_year => 1985, credits => Set::Object->new( $credits[1] ) ), new Movie( title => "The Wall: Live in Berlin", release_year => 1990, credits => Set::Object->new( $credits[2] ) ), new Movie( title => "Muppet Treasure Island", release_year => 1996, credits => Set::Object->new( $credits[3] ) ), ); my @actors = ( new Person( name => "Tim Curry", birthdate => "1946-04-19 12:00:00", birth_location => $locations[0], credits => Set::Object->new( @credits[0..3] ) ), new Person( name => "Marvin Lee Aday", birthdate => "1947-09-27 12:00:00", birth_location => $locations[1], credits => Set::Object->new( $credits[4] ) ), ); $|=1; print "Inserting data objects into storage..."; print "movies"; $storage->insert(@movies); print ", jobs"; $storage->insert(@jobs); print ", credits"; $storage->insert(@credits); print ", actors"; $storage->insert(@actors); print ", done!\n"; }; # first get the person in question print "Getting Remote objects...\n"; my ($r_person, $r_movie, $r_job, $r_credit, $r_location) = $storage->remote( qw(Person Movie Job Credit Location) ); # turn on tracing of SQL $Tangram::TRACE = \*STDOUT; for my $name ("Tim Curry", "Marvin Lee Aday") { print "Selecting...\n"; my ($who) = $storage->select($r_person, $r_person->{name} eq $name +); # print the same header; but let's throw in the birth location too +, # because it's so easy. print "Printing...\n"; print ($who->{name}, ", born in ", $who->{birth_location}->{locati +on}, " was in the following films:\n\n"); # now iterate through the credits desired for my $credit ($who->{credits}->members) { my ($movie) = $storage->select($r_movie, $r_movie->{credits}->includes($credit) ); my ($job) = $storage->select($r_job, $r_job->{credits}->includes($credit) ); if ($movie) { print( $movie->title, " released in ", $movie->release_year, "\n"); } else { print "Um, an unknown movie :-}\n"; } if ($job) { print ' ', $job->job_title, "\n"; } else { print " Oh dear, no job\n"; } } } # Select all people from a suburb of Warrington, who were in the movie + "Clue" my ($clue) = $storage->select($r_movie, $r_movie->{title} eq "Clue"); my ($person) = $storage->select($r_person, ($r_person->{birth_location +}->{parent_location}->{location} eq "Warrington")); print $person->{name}, "\n";
As you can see, there is no SQL here and I'm still doing some quite complex queries.
A brief investigation of UML is probably worth the effort, too - so you can figure out how to think of concepts such as foreign keys in OO terms.
In reply to Re: Leashing DBI
by mugwumpjism
in thread Leashing DBI
by billyak
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |