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.
Settingitupmanually
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_namespaces() method:
# load My::Schema::Result::* and their resultset classes
__PACKAGE__->load_namespaces();
By default this loads all the Result (Row) classes in the My::Schema::Result:: namespace, and also any
resultset classes in the My::Schema::ResultSet:: namespace (if missing, the resultsets are defaulted to
be DBIx::Class::ResultSet objects). You can change the result and resultset namespaces by using options
to the "load_namespaces" in DBIx::Class::Schema call.
It is also possible to do the same things manually by calling "load_classes" for the Row classes and
defining in those classes any required resultset classes.
Next, create each of the classes you want to load as specified above:
package My::Schema::Result::Album;
use base qw/DBIx::Class::Core/;
Load any additional components you may need with the load_components() method, and provide component
configuration if required. For example, if you want automatic row ordering:
__PACKAGE__->load_components(qw/ Ordered /);
__PACKAGE__->position_column('rank');
Ordered will refer to a field called 'position' unless otherwise directed. Here you are defining the
ordering field to be named 'rank'. (NOTE: Insert errors may occur if you use the Ordered component, but
have not defined a position column or have a 'position' field in your row.)
Set the table for your class:
__PACKAGE__->table('album');
Add columns to your class:
__PACKAGE__->add_columns(qw/ albumid artist title rank /);
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:
__PACKAGE__->add_columns(albumid =>
{ accessor => 'album',
data_type => 'integer',
size => 16,
is_nullable => 0,
is_auto_increment => 1,
},
artist =>
{ data_type => 'integer',
size => 16,
is_nullable => 0,
},
title =>
{ data_type => 'varchar',
size => 256,
is_nullable => 0,
},
rank =>
{ data_type => 'integer',
size => 16,
is_nullable => 0,
default_value => 0,
}
);
DBIx::Class doesn't directly use most of this data yet, but various related modules such as
HTML::FormHandler::Model::DBIC make use of it. Also it allows you to create your database tables from
your Schema, instead of the other way around. See "deploy" in DBIx::Class::Schema for details.
See DBIx::Class::ResultSource for more details of the possible column attributes.
Accessors are created for each column automatically, so My::Schema::Result::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:
# in My::Schema::Result::Artist
__PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
See DBIx::Class::Relationship for more information about the various types of available relationships and
how you can design your own.
UsingDBIx::Class::Schema::Loader
This module (DBIx::Class::Schema::Loader) is an external module, and not part of the DBIx::Class
distribution. It inspects your database, and automatically creates classes for all the tables in your
schema.
The simplest way to use it is via the dbicdump script from the DBIx::Class::Schema::Loader distribution.
For example:
$ dbicdump -o dump_directory=./lib \
-o components='["InflateColumn::DateTime"]' \
MyApp::Schema dbi:mysql:mydb user pass
If you have a mixed-case database, use the "preserve_case" option, e.g.:
$ dbicdump -o dump_directory=./lib -o preserve_case=1 \
-o components='["InflateColumn::DateTime"]' \
MyApp::Schema dbi:mysql:mydb user pass
If you are using Catalyst, then you can use the helper that comes with Catalyst::Model::DBIC::Schema:
$ script/myapp_create.pl model MyModel DBIC::Schema MyApp::Schema \
create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'
See Catalyst::Helper::Model::DBIC::Schema for more information on this helper.
See the DBIx::Class::Schema::Loader and DBIx::Class::Schema::Loader::Base documentation for more
information on the many loader options.
Connecting
To connect to your Schema, you need to provide the connection details or a database handle.
Viaconnectiondetails
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::Storage::DBI for more information about this and other special
"connect"-time options.
Viaadatabasehandle
The supplied coderef is expected to return a single connected database handle (e.g. a DBI $dbh)
my $schema = My::Schema->connect (
sub { Some::DBH::Factory->connect },
\%extra_attrs,
);
Basicusage
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.
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::Result::Album" that represents this row. Once you have that row, you can access and update
columns:
$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:
$album->set_column('title', 'Presence');
$title = $album->get_column('title');
Just like with Class::DBI, you call "update" to save your changes to the database (by executing the
actual "UPDATE" statement):
$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.
Addingandremovingrows
To create a new record in the database, you can use the "create" method. It returns an instance of
"My::Schema::Result::Album" that can be used to access the data in the new record:
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;
Findingyourobjects
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:
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:
# 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::Classic-compatible 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 => { -desc => 'year' } }
);
@albums then holds the two most recent Bob Marley 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.
Install Now
PNG Icons
Emojis