Class::DBI::Cookbook
In summary, Class::DBI maps an instance of an object to a row in a relational database table. When you operate upon the object, you affect the underlying database. Class::DBI takes care of generating all the select, inserts, updates, deletes for you. It makes working with a relational databases almost trivial.
If your anything like me, you started interacting with your Relational Database Management System (RDBMS), such as: Oracle, Postgres, MySQL, Sybase, SQLServer... (in my order of preference :) using Perl via the vendor supplied command line tool. Shelling out in your script, launching the command line tool to execute some SQL, catching the output, parsing it, and using it in your Perl in some meaningful way.
Then I found out about DBI, and the world was beautiful. I could now connect and execute SQL within perl in an consise and intuitive way. But then I got lazy (a good thing). And I started writing all kinds of helper modules, to do all the things I was doing over and over.
Then, I though, ``dummy I bet someone has already done this'', so I searched around the web and CPAN, and found Class::DBI. Since then I've been doing my database interaction exclusively via Class::DBI.
While this cookbook overlaps much of the Class::DBI documentation (probably entirely), it is not, I repeat, it is not a substitution for the Class::DBI documentation.
This cookbook attempts to be many things:
Class::DBI is commonly called CDBI by the lazy. I will be calling it CDBI from now on.
CDBI Cookbook
Many to ManysSeeing your sql statements Returning Computed/Aggregated values Count(*)s Getting your DBI sth and dbh has_many
====================
| artistgroup |
|------------------|
| bandartistid |
| personartistid |
====================
\|/ \|/
| |
| |
====================
| artist |
|------------------|
| artistid |
| name |
| popularity |
====================
|
|
/|\
===================
| cd | ===================
|-----------------| | linernote |
| cdid | |-----------------|
| artistid |-----| cdid |
| title | | note |
| publishdate | ===================
===================
|
|
/|\
===================
| track |
|-----------------|
| cdid |
| songid |
| sequence |
===================
\|/
|
|
============
| song |
|----------|
| songid |
| name |
============
For those unfamiliar with ERDs, ERDs are a graphical representation showing tables, columns within those tables, and the relationships between tables.
The boxes are tables. So in this example we have 5 tables: ARTIST, CD, TRACK, SONG, and LINERNOTE. Within each table we list the columns in that table. For example, the ARTIST table contains 3 columns: ARTISTID, NAME, PARENTARTISTID. For consistency sake, the primary key (i.e. the column(s) that uniquely identify a row of data), are the first column listed. In the ARTIST table the ARTISTID is the primary key.
Furthermore, the ERD depicts the relationships between tables. The "crows feet" (i.e. the line between tables with the 3 pronged end), indicates the quantity, of the relationship. So, reading down, "an artist has many CDs" and reading up, "a CD has a artist". The orientation (i.e. up, down, left,right) of the relationship is unimportant, it's the "crows feet" that's important. That is, the crows feet are on the side of the relationship which contains "many".
The Data Definition Language (DDL) for creating the above tables and relationships is included in this document in the appendix.
It's usually wise to set up a ``top level'' class for your entire application to inherit from, rather than have each class inherit directly from Class::DBI. This gives you a convenient point to place system-wide overrides and enhancements to Class::DBI's behavior. For example, it's common to put your database connection into your base class.
package Music::MusicDB;
use strict;
use warnings;
use base 'Class::DBI::Oracle';
my ($dsn, $username, $password) = getConfig();
Music::CookBookDB->set_db('Main',
$dsn,
$username,
$password,
{AutoCommit=>1},
);
sub getConfig {
return ('dbi:Oracle:o901.mydomain.com','me', 'me');
}
1;
=head2 Regular Class
To start off slow let's implement the Artist class. CDBI maps, one to one, a class to a table. The artist class looks like:
package Music::Artist;
use strict;
use base 'Music::MusicDB';
Music::Artist->table('artist');
Music::Artist->columns(All => qw/artistid name popularity/);
1;
You indicate the table via the ->table method. And, you indicate the columns int the table via the ->columns method. When not otherwise specified, CDBI uses the first column ``artistid'' as the primary key of the table.
CDBI automatically creates methods with the same names as your columns. All the methods are in lower case (by default), regardless of the character case in your database.
So, if I have an Artist object ``$artist'' and I want to print out the ``name'' field, I do:
print $artist->name,"\n";
If I want to print a bunch of column values:
@column = qw/name artistid/;
@value = $artist->get(@column);
foreach (@value) {
print $_,"\n";
}
If I want to change the value of the ``name'' field:
$artist->name("New Name");
If I want to change a bunch of attributes:
%hash = (name=>"New Name", artistid=>17);
$artist->set(%hash);
CDBI, uses Class::Accessor internally to create the methods. So it you are interested, check out the Class::Accessor docs for how to use it (also a very helpful module).
Inserting (creating) data in CDBI, like everything else, is extremely easy. I want to create some artists. I create a script like:
#!/usr/bin/perl
use strict;
use Music::Artist;
my %hash = (artistid=>1, name=>'sublime');
my $obj1 = Music::Artist->create(\%hash);
%hash = (artistid=>2, name=>'beatles');
my $obj2 = Music::Artist->create(\%hash);
Simple, I've now created 2 rows in my database. I also have two (2) objects available in my code, which are the instantiation of those two rows, with which I can perform useful functionality upon.
There are a number of methods with which to instantiate objects in your perl code from the database.
Like the name indicates this method returns all of the rows in a table.
# Generic form
@obj = Class->retrieve_all;
# In your code
#!/usr/bin/perl
use strict;
use Music::Artist;
my @obj = Music::Artist->retrieve_all;
Which obviously returns an array of objects, one for each row in the database.
You use this method to ``retrieve'' a row by primary key. It returns a single row.
# Generic forms
$obj = Class->retrieve( $id );
$obj = Class->retrieve( %hash );
# In your code
my $artist = Music::Artist->retrieve(2);
print $artist->name,"\n";
If the table uses multiple columns to define the primary key, you use a hash to define the key values:
my %key = (CD=>100,songid=>1000);
my $track = Music::Track->retrieve(%key);
print $track->sequence,"\n";
Returns an array of objects matching the criteria you supply.
# Generic form
my @objs = Class->search(column1 => $value, column2 => $value ...);
The criteria are exact and additive, meaning that when you say:
my @cds = Music::CD->search(title => "Greatest Hits", year => 1990);
Means ``retrieve all the rows where title equals 'Greatest Hits' AND year equals 1990''.
This is a simple search for all objects where the columns specified are ``like'' the values specified.
# Generic form
@obj = Class->search_like(column1 => $like_pattern, ....);
# In your code
my @cd = Music::CD->search_like(title => 'October%');
my @cd = Music::CD->search_like(title => 'Hits%', artist => 'Various%');
$like_pattern is a pattern given in SQL LIKE predicate syntax. '%' means ``any one or more characters'', '_' means ``any single character''. The wildcards can be used in the front, middle, or end of the string.
my @obj = Music::Artist->search_like(name => '_eatles');
my @obj = Music::Artist->search_like(name => '%les');
my @obj = Music::Artist->search_like(name => 'Be%les');
While not actually a part of the CDBI distribution, it is a nearly indispensable plugin component.
Essentially it lets you create queries that are much more complex than the standard ``search'' method. Using AbstractSearch you can use ``AND''s, ``OR''s, <, >, !=, <=, >= and probably more in your search criteria.
To use ``search_where'' you must ``use'' Class::DBI::AbstractSearch within your class definition, for example:
package Music::CD
use Class::DBI::AbstractSearch;
# Then in a nearby piece of code
#!/usr/bin/perl
use strict
# select the rows where the (name equals 'Ozzy' or 'Kelly') AND
# (status is not 'outdated')
my @obj = Music::Artist->search_where(
name => [ 'Ozzy', 'Kelly' ],
status => { '!=', 'outdated' },
);
Class::DBI::AbstractSearch is built upon SQL::Abstract. Look at SQL::Abstract to learn the exactly how to form complicated where clauses.
With the inclusion of ``iterator'' CDBI is CRUDI (hardy har har)
All of the above retrieval methods can also return an ``iterator''.
#!/usr/bin/perl
use strict;
use Music::Artist;
my $iterator = Music::Artist->retrieve_all;
Notice the above returns a scalar as opposed to an array. This form of return is called an ``iterator''. CDBI determines which type of return you want based on the target's datatype (i.e. the datatype scalar or array of the field on the left of the equals ``='' sign).
You use an iterator like:
my $iterator = Music::Artist->retrieve_all;
while (my $artist = $iterator->next) {
print $artist->name,"\n";
}
You might ask, ``what's the big deal''. The big deal occurs during execution. The array method returns all the rows and all the columns contained in the ``essential'' column group (discussed later) from the database, and creates the associated CDBI objects at execution. Whereas, the iterator fetches only the primary keys of all the rows and holds off creating the object until it is asked for, via ->next.
There is another form, that is a combination of search and create. It first looks for the row with the values you specify, if it can't be found, CDBI creates it for you.
my $cd = Music::Artist->find_or_create({name=>'Elvis Presley'});
If CDBI can't find ``Elvis'' then it will create the King.
Update, edit, modify, whatever you want to call it, changes the data in the object and the database.
# first get the object
my $obj = Music::Artist->retrieve(1);
# Change the data one colummn at a time
$obj->name("New Name");
$obj->status("discontinued");
# Change them en masse
my $obj2 = Music::Artist->retrieve(2);
$obj2->set(name=>"something", status=>"top 40");
You can probably guess what this does.
#!/usr/bin/perl
use strict;
use Music::Artist;
my @obj = Music::Artist->retrieve_all;
foreach (@obj) {
$_->delete;
}
Answer: Makes your DBA hate you!
One of the fundamental points is the ``R'' of RDBMS, that is ``relational''. CDBI makes it really easy to navigate from one object to another related object.
Simply, you define which tables (classes) are children of the table you are workign upon. That is, which tables store the primary key of your table, in a column of their table.
This means that for every one of a specific object, there are many directly related objects. When you invoke the "has_many" method, it creates a method in your classes definition which you can then call from your code.
Class->has_many(method_to_create => "Child::Class");
In our model, each artist puts out one or more CDs (what's the point of being an artist if you aren't going to have a CD).
So let's add a bit to the Artist class
Music::Artist->has_many(cds => 'Music::CD');
that let's it relate to the CD class. More specifically, it says an Artist has_many CDs. The entire Artist class looks like:
package Music::Artist;
use strict;
use base 'Music::MusicDB';
Music::Artist->table('artist');
Music::Artist->columns(All => qw/artistid name popularity/);
Music::Artist->has_many(cds => 'Music::CD');
1;
Behind the scenes, it makes a method available called ``cds'', which you can call on an instance of an Artist.
Class->has_a(foreign_key_column => 'foreign_class');
Similarly, a CD has to relate to the Artist class. We haven't seen the CD class yet so let's build it now and put in the ``has_a'' code that relates it to the Artist class.
package Music::CD;
use strict;
use base 'Music::MusicDB';
Music::CD->set_up_table('cd');
Music::CD->has_a(artistid => 'Music::Artist');
Music::CD->has_many(tracks=>Music::Track=>'cdid');
1;
In database speak, you use has_a to obtain data via a foreign key. In other words, your object contains the value of another object's primary key.
The has_a code says ``CD'' has_a ``Artist''. It also says, use the artistid in the CD to look up the corresponding value in the primary key of the Artist.
Something you might not have noticed is that the Artist class didn't need to specify how to find the relating CDs. That is because CDBI is smart enough to examine the CD class and learn how it relates to the Artist class.
Now let's load up some sampe data (see Appendex 2), so we can do some examples. Whew, that was tough.
Let's read down the hierarch, via the has_many, from Artist to CD
#!/usr/bin/perl
use strict;
use lib '/home/jstrauss/Class/DBI';
use Music::Artist;
my ($artist) = Music::Artist->search_like(name=>'%Beatles%');
print $artist->name,"\n";
foreach ($artist->cds) {
print "\t",$_->title,"\n";
}
Here's the output:
The Beatles
Abbey Road
Rubber Soul
Sgt. Peppers Lonely Hearts Club Band
The Beatles (aka The White Album)
Revolver
Or if you want to read up the hierarchy, via the has_a, from CD to Artist:
#!/usr/bin/perl
use strict;
use lib '/home/jstrauss/Class/DBI';
use Music::CD;
my ($cd) = Music::CD->search(title=>'Abbey Road');
print "The CD: ",$cd->title," written by the: ",$cd->artistid->name,"\n";
Here's the output:
The CD: Abbey Road written by the: The Beatles
You can chain these together, you can read down one relationship, up another and so on. Here we'll print out all the songs on all the CDs written by the Beatles (at least for the data we have):
#!/usr/bin/perl
use strict;
use lib '/home/jstrauss/Class/DBI';
use Music::Artist;
my ($artist) = Music::Artist->search_like(name=>'%Beatles%');
print $artist->name,"\n";
foreach ($artist->cds) {
print "\t", $_->title, "\n";
foreach my $track ($_->tracks) {
print "\t\t", $track->songid->name,"\n";
}
}
Here's the output:
The Beatles
Abbey Road
Rubber Soul
Sgt. Peppers Lonely Hearts Club Band
The Beatles (aka The White Album)
Back in the U.S.S.R.
Dear Prudence
Glass Onion
Ob-La-Di, Ob-La-Da
Wild Honey Pie
Continuing Story of Bungalow Bill
While My Guitar Gently Weeps
Happiness Is a Warm Gun
Martha My Dear
I'm So Tired
Blackbird
Piggies
Rocky Raccoon
Don't Pass Me By
Why Don't We Do It in the Road?
I Will
Julia
Revolver
The following DDL is for Oracle, I'm sure you can convert it to your RDBMS of choice fairly simply.
alter table ARTIST
drop constraint FK_ARTIST_REFERENCE_ARTIST;
alter table CD
drop constraint FK_COMPACTD_REFERENCE_ARTIST;
alter table LINERNOTE
drop constraint FK_LINERNOT_REFERENCE_COMPACTD;
alter table TRACK
drop constraint FK_TRACK_REFERENCE_COMPACTD;
alter table TRACK
drop constraint FK_TRACK_REFERENCE_SONG;
drop table ARTIST cascade constraints;
drop table CD cascade constraints;
drop table LINERNOTE cascade constraints;
drop table SONG cascade constraints;
drop table TRACK cascade constraints;
/*==============================================================*/
/* Table: ARTIST */
/*==============================================================*/
create table ARTIST (
ARTISTID NUMBER not null,
PARENTARTISTID NUMBER,
NAME VARCHAR(50) not null,
constraint PK_ARTIST primary key (ARTISTID)
);
/*==============================================================*/
/* Table: CD */
/*==============================================================*/
create table CD (
CDID NUMBER not null,
ARTISTID NUMBER not null,
TITLE VARCHAR(50) not null,
YEAR DATE not null,
constraint PK_CD primary key (CDID)
);
/*==============================================================*/
/* Table: LINERNOTE */
/*==============================================================*/
create table LINERNOTE (
CDID NUMBER not null,
NOTE VARCHAR(50) not null,
constraint PK_LINERNOTE primary key (CDID)
);
/*==============================================================*/
/* Table: SONG */
/*==============================================================*/
create table SONG (
SONGID NUMBER not null,
NAME VARCHAR(50) not null,
constraint PK_SONG primary key (SONGID)
);
/*==============================================================*/
/* Table: TRACK */
/*==============================================================*/
create table TRACK (
TRACKID NUMBER not null,
CDID NUMBER not null,
SONGID NUMBER not null,
SEQUENCE NUMBER not null,
constraint PK_TRACK primary key (TRACKID)
);
alter table ARTIST
add constraint FK_ARTIST_REFERENCE_ARTIST foreign key (PARENTARTISTID)
references ARTIST (ARTISTID);
alter table CD
add constraint FK_COMPACTD_REFERENCE_ARTIST foreign key (ARTISTID)
references ARTIST (ARTISTID);
alter table LINERNOTE
add constraint FK_LINERNOT_REFERENCE_COMPACTD foreign key (CDID)
references CD (CDID);
alter table TRACK
add constraint FK_TRACK_REFERENCE_COMPACTD foreign key (CDID)
references CD (CDID);
alter table TRACK
add constraint FK_TRACK_REFERENCE_SONG foreign key (SONGID)
references SONG (SONGID);
=cut