DBIx::Class::Manual::Intro - Introduction to DBIx::Class
You're bored with SQL, and want a native Perl interface for your database? Or you've been doing this for a while with Class::DBI, and think there's a better way? You've come to the right place.
Here are a few simple tips that will help you get your bearings with DBIx::Class.
DBIx::Class needs to know what your Table structure looks like. You do that by defining DBIx::Class::ResultSources. Each table gets a ResultSource, which defines the Columns it has, along with any Relationships it has to other tables. (And oh, so much more besides) The important thing to understand:
A ResultSource == Table
(most of the time, but just bear with my simplification)
So, we've got some ResultSources defined. Now, we want to actually use those definitions to help us translate the queries we need into handy perl objects!
Let's say we defined a ResultSource for an "album" table with three columns: "albumid", "artist", and "title". Any time we want to query this table, we'll be creating a DBIx::Class::ResultSet from its ResultSource. For example, the results of:
SELECT albumid, artist, title FROM album;
Would be retrieved by creating a ResultSet object from the album table's ResultSource, likely by using the "search" method.
DBIx::Class doesn't limit you to creating only simple ResultSets -- if you wanted to do something like:
SELECT title FROM album GROUP BY title;
You could easily achieve it.
The important thing to understand:
Any time you would reach for a SQL query in DBI, you are creating a DBIx::Class::ResultSet.
DBIx::Class tends to wait until it absolutely must fetch information from the database. If you are returning a ResultSet, the query won't execute until you use a method that wants to access the data. (Such as "next", or "first")
Setting up a ResultSet does not execute the query; retrieving the data does.
Let's look at how you can set and use your first native DBIx::Class tree.
First we'll see how you can set up your classes yourself. If you want them to be auto-discovered, just skip to the next section, which shows you how to use DBIx::Class::Schema::Loader.
First, you should create your base schema class, which inherits from DBIx::Class::Schema:
package My::Schema; use base qw/DBIx::Class::Schema/;
In this class you load your result_source ("table", "model") classes, which we will define later, using the load_classes() method. You can specify which classes to load manually:
# load My::Schema::Album and My::Schema::Artist __PACKAGE__->load_classes(qw/ Album Artist /);
Or load classes by namespace:
# load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes __PACKAGE__->load_classes( { 'My::Schema' => [qw/ Album Artist /], 'My::OtherSchema' => [qw/ LinerNotes /] } );
Or let your schema class load all classes in its namespace automatically:
# load My::Schema::* __PACKAGE__->load_classes();
Next, create each of the classes you want to load as specified above:
package My::Schema::Album; use base qw/DBIx::Class/;
Load any components required by each class with the load_components() method. This should consist of "Core" plus any additional components you want to use. For example, if you want serial/auto-incrementing primary keys:
__PACKAGE__->load_components(qw/ PK::Auto Core /);
PK::Auto is supported for many databases; see DBIx::Class::Storage::DBI for more information.
PK::Auto
Set the table for your class:
__PACKAGE__->table('album');
Add columns to your class:
__PACKAGE__->add_columns(qw/ albumid artist title /);
Each column can also be set up with its own accessor, data_type and other pieces of information that it may be useful to have -- just pass add_columns a hash:
add_columns
__PACKAGE__->add_columns(albumid => { accessor => 'album', data_type => 'integer', size => 16, is_nullable => 0, is_auto_increment => 1, default_value => '', }, artist => { data_type => 'integer', size => 16, is_nullable => 0, is_auto_increment => 0, default_value => '', }, title => { data_type => 'varchar', size => 256, is_nullable => 0, is_auto_increment => 0, default_value => '', } );
DBIx::Class doesn't directly use most of this data yet, but various related modules such as DBIx::Class::WebForm make use of it. Also it allows you to create your database tables from your Schema, instead of the other way around. See SQL::Translator for details.
See DBIx::Class::ResultSource for more details of the possible column attributes.
Accessors are created for each column automatically, so My::Schema::Album will have albumid() (or album(), when using the accessor), artist() and title() methods.
Define a primary key for your class:
__PACKAGE__->set_primary_key('albumid');
If you have a multi-column primary key, just pass a list instead:
__PACKAGE__->set_primary_key( qw/ albumid artistid / );
Define this class' relationships with other classes using either belongs_to to describe a column which contains an ID of another Table, or has_many to make a predefined accessor for fetching objects that contain this Table's foreign key:
belongs_to
has_many
__PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id');
See DBIx::Class::Relationship for more information about the various types of available relationships and how you can design your own.
This is an external module, and not part of the DBIx::Class distribution. Like Class::DBI::Loader, it inspects your database, and automatically creates classes for all the tables in your database. Here's a simple setup:
package My::Schema; use base qw/DBIx::Class::Schema::Loader/; __PACKAGE__->loader_options( relationships => 1 ); 1;
The actual autoloading process will occur when you create a connected instance of your schema below.
See the DBIx::Class::Schema::Loader documentation for more information on its many options.
To connect to your Schema, you need to provide the connection details. The arguments are the same as for "connect" in DBI:
my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
You can create as many different schema instances as you need. So if you have a second database you want to access:
my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
Note that DBIx::Class::Schema does not cache connections for you. If you use multiple connections, you need to do this manually.
To execute some sql statements on every connect you can add them as an option in a special fifth argument to connect:
my $another_schema = My::Schema->connect( $dsn, $user, $password, $attrs, { on_connect_do => \@on_connect_sql_statments } );
See "connect_info" in DBIx::Class::Schema::Storage::DBI for more information about this and other special connect-time options.
connect
Once you've defined the basic classes, either manually or using DBIx::Class::Schema::Loader, you can start interacting with your database.
To access your database using your $schema object, you can fetch a "ResultSet" in DBIx::Class::Manual::Glossary representing each of your tables by calling the resultset method.
resultset
The simplest way to get a record is by primary key:
my $album = $schema->resultset('Album')->find(14);
This will run a SELECT with albumid = 14 in the WHERE clause, and return an instance of My::Schema::Album that represents this row. Once you have that row, you can access and update columns:
SELECT
albumid = 14
WHERE
My::Schema::Album
$album->title('Physical Graffiti'); my $title = $album->title; # $title holds 'Physical Graffiti'
If you prefer, you can use the set_column and get_column accessors instead:
set_column
get_column
$album->set_column('title', 'Presence'); $title = $album->get_column('title');
Just like with Class::DBI, you call update to commit your changes to the database:
update
$album->update;
If needed, you can throw away your local changes:
$album->discard_changes if $album->is_changed;
As you can see, is_changed allows you to check if there are local changes to your object.
is_changed
To create a new record in the database, you can use the create method. It returns an instance of My::Schema::Album that can be used to access the data in the new record:
create
my $new_album = $schema->resultset('Album')->create({ title => 'Wish You Were Here', artist => 'Pink Floyd' });
Now you can add data to the new record:
$new_album->label('Capitol'); $new_album->year('1975'); $new_album->update;
Likewise, you can remove it from the database:
$new_album->delete;
You can also remove records without retrieving them first, by calling delete directly on a ResultSet object.
# Delete all of Falco's albums $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
DBIx::Class provides a few different ways to retrieve data from your database. Here's one example:
# Find all of Santana's albums my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
In scalar context, as above, search returns a DBIx::Class::ResultSet object. It can be used to peek at the first album returned by the database:
search
my $album = $rs->first; print $album->title;
You can loop over the albums and update each one:
while (my $album = $rs->next) { print $album->artist . ' - ' . $album->title; $album->year(2001); $album->update; }
Or, you can update them all at once:
$rs->update({ year => 2001 });
In list context, the search method returns all of the matching rows:
# Fetch immediately all of Carlos Santana's albums my @albums = $schema->resultset('Album')->search( { artist => 'Carlos Santana' } ); foreach my $album (@albums) { print $album->artist . ' - ' . $album->title; }
We also provide a handy shortcut for doing a LIKE search:
LIKE
# Find albums whose artist starts with 'Jimi' my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
Or you can provide your own WHERE clause:
# Find Peter Frampton albums from the year 1986 my $where = 'artist = ? AND year = ?'; my @bind = ( 'Peter Frampton', 1986 ); my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
The preferred way to generate complex queries is to provide a SQL::Abstract construct to search:
my $rs = $schema->resultset('Album')->search({ artist => { '!=', 'Janis Joplin' }, year => { '<' => 1980 }, albumid => { '-in' => [ 1, 14, 15, 65, 43 ] } });
This results in something like the following WHERE clause:
WHERE artist != 'Janis Joplin' AND year < 1980 AND albumid IN (1, 14, 15, 65, 43)
For more examples of complex queries, see DBIx::Class::Manual::Cookbook.
The search can also be modified by passing another hash with attributes:
my @albums = My::Schema->resultset('Album')->search( { artist => 'Bob Marley' }, { rows => 2, order_by => 'year DESC' } );
@albums then holds the two most recent Bob Marley albums.
@albums
For more information on what you can do with a DBIx::Class::ResultSet, see "METHODS" in DBIx::Class::ResultSet.
For a complete overview of the available attributes, see "ATTRIBUTES" in DBIx::Class::ResultSet.
There is a problem with slow performance of certain DBIx::Class operations in perl-5.8.8-10 and later on RedHat and related systems, due to a bad backport of a "use overload" related bug. The problem is in the Perl binary itself, not in DBIx::Class. If your system has this problem, you will see a warning on startup, with some options as to what to do about it.
DBIx::Class::Manual::Cookbook
To install DBIx::Class, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class
CPAN shell
perl -MCPAN -e shell install DBIx::Class
For more information on module installation, please visit the detailed CPAN module installation guide.