Class::DBI::Lite::Tutorial - How To Use Class::DBI::Lite
The following examples are intended to work with MySQL version 5.1.x
create table artists ( artist_id integer unsigned not null primary key auto_increment, name varchar(100) not null, ) engine=innodb charset=utf8; create table albums ( album_id integer unsigned not null primary key auto_increment, arist_id integer unsigned not null, name varchar(100) not null, unique(artist_id, name), foreign key fk_albums_artists (artist_id) references artists( artist_id ) on delete restrict ) engine=innodb charset=utf8;
First you must subclass Class::DBI::Lite::* and define your database connection:
Class::DBI::Lite::*
File lib/App/db/model.pm
lib/App/db/model.pm
package App::db::model; use strict; use warnings 'all'; use base 'Class::DBI::Lite::mysql'; __PACKAGE__->connection( 'DBI:mysql:dbname:hostname', 'username', 'password' ); 1;# return true:
File lib/App/db/artist.pm
lib/App/db/artist.pm
package App::db::artist; use strict; use warnings 'all'; use base 'App::db::model'; __PACKAGE__->set_up_table('artists'); # Artists have many Albums, referenced by the field 'artist_id': __PACKAGE__->has_many( albums => 'App::db::album' => 'artist_id' ); 1;# return true:
File lib/App/db/album.pm
lib/App/db/album.pm
package App::db::album; use strict; use warnings 'all'; use base 'App::db::model'; __PACKAGE__->set_up_table('albums'); # Albums have an artist, referenced by the field 'artist_id' __PACKAGE__->belongs_to( artist => 'App::db::artist' => 'artist_id' ); 1;# return true:
Using Class::DBI::Lite is simple.
Class::DBI::Lite
#!/usr/bin/perl -w use strict; use warnings 'all'; use App::db::artist; use App::db::album; # Now you're all set!
If you have relationships between your classes, you can express them like this:
__PACKAGE__->has_many( <methodname> => <classname> => <their_fieldname> );
__PACKAGE__->has_one( <methodname> => <classname> => <my_fieldname> );
...or...
__PACKAGE__->belongs_to( <methodname> => <classname> => <my_fieldname> );
So in our example we say:
# Artists have many Albums, referenced by the field 'artist_id': __PACKAGE__->has_many( albums => 'App::db::album' => 'artist_id' );
Which means that given an instance of My::Artist you can do this:
My::Artist
# Fetch the artist: my $artist = App::db::artist->retrieve( 1 ); # Fetch the artist's albums: my @albums = $artist->albums; # Print the artist's name for each of these albums: foreach my $album ( @albums ) { print $album->artist->name; } # As of version 1.005 You can also do the following: my @best_of = $artist->albums({name => { LIKE => '%Best of%'} }); my @sorted = $artist->albums(undef, { order_by => 'name DESC' } ); my @sorted_best = $artist->albums({ name => { LIKE => '%Best of%' } }, { order_by => 'name DESC' }); my @top_five = $artist->albums({ name => { LIKE => '%Best of%' } }, { order_by => 'name DESC limit 0, 5' });
That example would look like this if we were doing it with hand-coded SQL statements:
# **** THE OLD WAY: **** use DBI; my $dbh = DBI->connect('DBI:mysql:dbname:hostname', 'username', 'password' ); # Fetch the artist: my $sth = $dbh->prepare("SELECT * FROM artists WHERE artist_id = ?"); $sth->execute( 1 ); my ($artist) = $sth->fetchrow_hashref; $sth->finish(); # Fetch the artist's albums: my @albums = ( ); $sth = $dbh->prepare("SELECT * FROM albums WHERE artist_id = ?"); $sth->execute( $artist->{artist_id} ); while( my $album = $sth->fetchrow_hashref ) { push @albums, $album; } $sth->finish(); # Print the artist's name for each of these albums: $sth = $dbh->prepare("SELECT * FROM artists WHERE artist_id = ?"); foreach my $album ( @albums ) { $sth->execute( $album->{artist_id} ); my ($artist) = $sth->fetchrow_hashref; print $artist->{name}; } $sth->finish();
# Create an artist: my $artist = App::db::artist->create( name => 'Bob Marley' ); # These both do the same: print $artist->id; print $artist->artist_id;
If you execute a search method in list context, you get an array. Executing a search method in scalar context returns an iterator.
List Context:
my @albums = App::db::album->search( name => 'Legend' ); my @albums = $artist->albums;
Scalar Context:
my $albums = App::db::album->search( name => 'Legend' ); my $albums = $artist->albums;
Iterators can be worked through like this:
while( my $album = $albums->next ) { # Work with $album: print $album->name; } # How many items are in the iterator? print $albums->count;
**NOTE: Any has_many extension methods are also considered 'search' methods, so they will conform to this list/scalar context behavior as well.
has_many
Returns all results as objects of the correct type:
Basic Searching
my @albums = App::db::album->search( name => 'Legend' ); my @albums = App::db::album->search( artist_id => $artist->id, );
Advanced Searching
Advanced searching takes 1 or 2 parameters:
App::db::album->search_where( { <args> }, [<order_by and limits>] );
Examples:
my @albums = App::db::album->search_where({ artist_id => { IN => [ 1, 2, 3 ] } }); my @albums = App::db::album->search_where({ name => { LIKE => 'Lege%' } }, { order_by => 'name DESC LIMIT 0, 10' });
search_where uses SQL::Abstract to generate the SQL, so look there for more examples.
search_where
Sometimes you just need to know how many records match your query:
my $count = App::db::album->count_search( name => 'Bob Marley' );
Using count_where you can make more interesting queries:
count_where
my $count = App::db::album->count_search_where({ name => { LIKE => 'Legen%' }, artist_id => { IN => [ 1, 2, 3 ] } });
count_search_where uses SQL::Abstract to generate the SQL, so look there for more examples.
count_search_where
Example:
my $artist = App::db::artist->create( name => 'Bob Marley' ); # Change the name: $artist->name( 'Bob' ); print $artist->name; # Bob # Save the changes to the database: $artist->update;
If you don't call update after making changes to an object, you will get a warning that looks like this:
update
My::Artist #1 DESTROY'd without saving changes to name
To cause the object to forget about any unsaved changes you made to it, do this:
# Hit the reset button: $artist->discard_changes;
Removes the item from the database instantly:
$artist->delete;
It's the same as:
my $sth = $dbh->prepare("DELETE FROM artists WHERE artist_id = ?"); $sth->execute( 1 ); $sth->finish();
You can program triggers from within your application code. These can be useful but beware of mixing too much business logic in with your data logic.
__PACKAGE__->add_trigger( before_create => sub { my ($self) = @_; # Do something before we are created: });
__PACKAGE__->add_trigger( after_create => sub { my ($self) = @_; # Do something now that we've been created: });
__PACKAGE__->add_trigger( before_update => sub { my ($self) = @_; # Do something before we are updated: });
__PACKAGE__->add_trigger( after_update => sub { my ($self) = @_; # Do something now that we've been updated: });
__PACKAGE__->add_trigger( before_delete => sub { my ($self) = @_; # Do something before we are deleted: });
__PACKAGE__->add_trigger( after_delete => sub { my ($obj) = @_; # Obj only contains { artist_id => 1 } # Do something with $obj: });
Sometimes you just want to add a trigger to a specific field.
package App::db::artist; ... __PACKAGE__->add_trigger( before_update_name => sub { my ($self, $old_value, $new_value) = @_; warn "About to change this artist's name from '$old_value' to '$new_value'"; });
package App::db::artist; ... __PACKAGE__->add_trigger( after_update_name => sub { my ($self, $old_value, $new_value) = @_; warn "Finished changing this artist's name from '$old_value' to '$new_value'"; });
This is how transactions are done with Class::DBI::Lite:
# Safely update the name of every album: eval { App::db::artist->do_transaction( sub { # Your transaction code goes here: my $artist = App::db::artist->retrieve( 1 ); foreach my $album ( $artist->albums ) { $album->name( $artist->name . ': ' . $album->name ); $album->update; } }); }; if( $@ ) { # There was an error: die $@; } else { # Everything was OK: }
You can get the normal database handle by calling db_Main on any of your classes.
db_Main
my $dbh = App::db::artist->db_Main;
You can call the sth_to_objects method to convert a prepared statement into objects of a pre-defined type:
sth_to_objects
# Step 1: Prepare the statement: my $sth = App::db::artist->db_Main->prepare("SELECT * FROM artists WHERE name LIKE ?"); # Step 2: Execute the statement: $sth->execute( 'Bob%' ); # Step 3: Call sth_to_objects: my @artists = App::db::artist->sth_to_objects( $sth );
Class::DBI::Lite is fully-tested and works perfectly under mod_perl. Because it uses Ima::DBI::Contextual under the hood, you get all of its benefits.
mod_perl
To install Class::DBI::Lite, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Class::DBI::Lite
CPAN shell
perl -MCPAN -e shell install Class::DBI::Lite
For more information on module installation, please visit the detailed CPAN module installation guide.