#title A train ride through DBIx::Class and its ecosystem
#author Stefan Hornburg (Racke)
#SORTtopics DBIx::Class; Deployment Handler; dbicdump
#lang en
#teaser Best practices for DBIx::Class. Explains how the ResultSet works. Based on a Dancer training at the Perl Dancer conference.
** Searching
The search parameters are in the first parameter of the *search* method.
*** NOT NULL
$rs->search({
vendor => { '!=', undef }
});
Or with helpers:
$rs->not_null('vendor');
*** Distinct
If you want to know whether the value for a column is *distinct* and retrieve the value at the *same time*, look at the following example:
{{{
my $distinct_status = $rs->search({}, {
columns => [ qw/status/ ],
group_by => [ qw/status/ ],
});
if ( $distinct_status->count == 1 ) {
my $ol_status = $distinct_status->first->status;
}
}}}
*** Comparing columns
We want to find all records where the columns
=username= and =replaced_by= contain the same
value.
Here is the corresponding SQL query:
select * from userdb where username = replaced_by;
We can't use a bareword on the right hand side
in our search parameters, so we have to resort
to use the =-ident= operator:
my $user_rs = $schema->resultset('User');
my $circular = $user_rs->search({
username => { -ident => 'replaced_by' },
});
** Sorting
my $sorted_rs = $schema->resultset('Company')->search(
{},
{ order_by => { -asc => 'name' } },
);
** Creating schema from existing database
You can use =dbicdump= to create a "boilerplate" schema from an
existing database.
Install first [[https://metacpan.org/pod/DBIx::Class::Schema::Loader][DBIx::Class::Schema::Loader]], e.g. with =cpanm=:
cpanm DBIx::Class::Schema::Loader
Now you run =dbicdump=:
dbicdump -o dump_directory=/home/dance/TravelDance/lib \
TravelDance::Schema \
dbi:Pg:dbname=perldance
Add database credentials (username and password):
dbicdump -o dump_directory=/home/dance/TravelDance/lib \
TravelDance::Schema \
dbi:Pg:dbname=sympa sympa "nevairbe"
You can also include useful components:
dbicdump -o dump_directory=/home/dance/TravelDance/lib
-o components='["InflateColumn::DateTime"]'
TravelDance::Schema
dbi:Pg:dbname=perldance
*** Remove comments generated by DBIx::Class::Schema::Loader.
perl -i -ne 'print if $_ !~ /(Created by DBIx::)|(DO NOT MODIFY)|(You can replace)/;' `find lib -name '*.pm'`
** Debugging
{{{
export DBIC_TRACE=1
}}}
** Result and ResultSet classes
*** Set-based DBIx::Class
Excellent article from Arthur Axel "fREW" Schmidt explaining the features of the
ResultSet class:
[[http://www.perladvent.org/2012/2012-12-21.html]]
*** Reaching out to another ResultSet class
If you want to reach out from a method in a result or resultset class to another
resultset class, you can do the following:
{{{
my $rset_message = $self->result_source->schema->resultset("Message");
}}}
*** Result classes
**** Updating result objects
#discardchanges
Sometimes you want to update your result object from the database record, e.g. after
creating a database record to get the values of columns with are automatically updated such as timestamps:
$user->create({username => 'nevairbe@linuxia.de'});
$user->discard_changes;
print $user->last_modified, "\n";
*** Flatten a object
This turns the object into a hash:
{{{
my %user_hash = $user->get_inflated_columns;
}}}
*** Predefined searches
Predefined searches encapsulate business logic inside the schema. If you use
them in the consumers of the schema, you can change the business logic without
changing the code of the consumes.
Example:
sub not_amazon {
my ($self) = @_;
my $user_rs = $self->search({
username => { -not_like => '%@marketplace.amazon%' },
});
return $user_rs;
}
*** Relationships
**** CASCADE actions
*** Indices
Add indices to your result classes like that:
sub sqlt_deploy_hook {
my ($self, $sqlt_table) = @_;
$sqlt_table->add_index(name => 'idx_product_canonical_sku',
fields => ['canonical_sku']);
}
sub sqlt_deploy_hook {
my ($self, $sqlt_table) = @_;
$sqlt_table->add_index(name => 'idx_product_canonical_sku',
fields => ['canonical_sku']
options => { where => q{canonical_sku <> ''},
);
}
** Components
#components
DBIx::Class uses [[https://metacpan.org/pod/Class::C3::Componentised][Class::C3::Componentised]]
to allow mix-ins (components) to be added to the
various classes that together comprise the schema definition.
There are three different types of components:
- *Schema* class components
- *Result* class components
- *ResultSet* class components
Many components exist for all of these three kinds.
*** Schema Components
You can add components like helpers into your main schema class with the method *load_components*:
{{{
package Interchange6::Schema;
use strict;
use warnings;
use base 'DBIx::Class::Schema::Config';
__PACKAGE__->load_components(
'Helper::Schema::DateTime',
'Helper::Schema::QuoteNames'
);
}}}
*** Using Result Class Components
In a single result class:
{{{
package TravelDance::Schema::Result::User;
use TravelDance::Schema::Candy -components => [qw(
InflateColumn::DateTime
PassphraseColumn
TimeStamp
)];
}}}
In order to add a result class component to *all* result classes you
can add these to the list
in =TravelDance::Schema::Candy='s =parse_arguments=.
*** ResultSet Components
In order to be able to add a component to all ResultSet classes in the schema *Interchange6::Schema* we need to do:
1. create parent class *Interchange6::Schema::ResultSet*
2. set as default resultset class in *Interchange6::Schema*
3. use for custom resultset classes *Interchange6::Schema::ResultSet::User*
**** Create parent class
package Interchange6::Schema::ResultSet;
use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
__PACKAGE__->load_components(
'Helper::ResultSet::CorrelateRelationship',
'Helper::ResultSet::Me',
'Helper::ResultSet::Random',
'Helper::ResultSet::SetOperations',
'Helper::ResultSet::Shortcut'
);
**** Set as default ResultSet class
package Interchange6::Schema;
__PACKAGE__->load_namespaces(
default_resultset_class => 'ResultSet'
);
** Exceptions
Throw an exception in your result class:
{{{
unless ( $self->valid_to > $self->valid_from ) {
$schema->throw_exception("valid_to is not later than valid_from");
}
}}}
** Helpers
[[s-h-stefan-hornburg-racke-a-train-ride-through-dbi-1.png][Arthur Axel "fREW" Schmidt]]
** Adding accessors to the schema
#addschemaaccessors
Vital information utilized in the schema and its methods isn't always available in the database, especially if you use it within web applications.
For example, we need to know which user is currently logged in to determine
- product prices
- whether to restrict searches
So we add accessors to the =Schema.pm= module:
__PACKAGE__->mk_group_ro_accessors(
inherited => (
[ 'current_user' => '_ic6_current_user' ],
)
);
__PACKAGE__->mk_group_wo_accessors(
inherited => (
[ 'set_current_user' => '_ic6_current_user' ],
)
);
In our web application, we are using the accessor in the following cases:
- start of processing a HTTP request
- user logs in
- user logs out
** Deployment Handler
In order to start with deployment handler, add a version number to your main
schema module:
package TravelDance::Schema;
=head1 NAME
TravelDance::Schema - Database schema class loader for TravelDance
=cut
use warnings;
use strict;
use base 'DBIx::Class::Schema';
use TravelDance::Schema::Populate::CountryLocale;
our $VERSION = 1;
To avoid unnecessary headaches, please use consecutive natural numbers.
*** Scripts
A number of useful scripts:
dh-prepare-version-storage :: prepares table for DH
dh-install-version-storage :: install and populates table for DH
dh-prepare-upgrade :: prepares upgrade
dh-upgrade :: performs upgrade
*** Adding relationships
Please make sure that the definition of the foreign key matches the primary key in the other table in terms of data type, length and default value.
DBI Exception: DBD::mysql::db do failed: Can't create table 'linuxia.#sql-6f14_e'
(errno: 150) [for Statement "ALTER TABLE orderline CHANGE COLUMN username username
varchar(255) NOT NULL, ADD INDEX orderline_idx_username (username), ADD CONSTRAINT
orderline_fk_username FOREIGN KEY (username) REFERENCES userdb (username) ON DELETE SET NULL"]
An other reason for such an error are rows that violate the foreign key, e.g. a value for *username* in *orderline*, which doesn't appear in *orderline*.