DBIx::Class

Forget about writing sql

Oriol Soriano / @ureesoriano

Hi, my name is 'uri'!

Developer @ CAPSiDE Team
Young in perl: ~3 years old
Enjoy driving myself up walls

Its my first time up here!

(LITERALLY)

DBIX::CLASS Authors

Matt S. Trout (MST) - Project Founder

Peter Rabbitson (RIBASUSHI) - Maintainer

Jess Robinson (CASTAWAY) - Documentation

Mailing List

IRC: irc.perl.org#dbix-class

OBJEcTIVeS

INTUITION: what, how & utility provided
Establish a knowledge foundation
Sorry, not and advanced course. BUT...

Motivation: why dbix::CLASS?

DBs: hold info we want to access...
... from our applications/scripts/etc

... We need an interface / abstraction layer

motivation: why dbix::CLASS?

Motivation: why dbix::CLASS?

SQL is GREAT...
... For what its meant to be
... (NOT being an explicit part of a DB interface) (IMHO)
Change in language ==> Context/mindset
SQL is kind of rough & inexpressible (IMHO)
... What if the DB backend is changed? :DD
Perf. improvement (queries exec when needed!)
Lastly, plain UGLY

...alternatives?

Not using DBs
... Not really an option
What's been people doing?

Enter ORMs

ORM:

Relational <····> OO

oo world

Classes
Objects
Attributes

relational world

Column

Row

Table

Relational <····> oo

Table <==>

Row

Column <==>

<==>

Object

Class

Attribute

The DBix::Class way

Table <==> Result Class

<==>

Row

Result

Column <==> Result Class 'column'

The DBix::Class way

Result of a query <==> DBIx::Class::ResultSet

DB definition <==> DBIx::Class::Schema

The DBix::Class way

Result Class: Defines our Results & ResultSets
ResultSet: Set of Rows/Results
Result: Object representing a row of a ResultSet
Schema: DB definition

crud a la dbix::CLASS

Create, Retrieve, Update & Delete operations

create

$schema->resultset('Artists')->create({
    id   => 1234,
    name => 'The Rolling Stones',
});

In this scenario: id ~ DB 'implementation details'
DBIx::Class takes care of auto-increment PKs

create

$schema->resultset('Albums')->create({
    id     => 123,
    name   => 'Beggars Banquet',
    artist => 1234,  # artist id
    year   => 1968,
});

Again, feels weird having to specify artist id
Again, DBIx::Class can handle it ;)

retrieve: search

Called on a DBIx::Class::ResultSet
Returns a DBIx::Class::ResultSet

my $albums_rs = $schema->resultset('Albums')->search({
    year => '2010',
});

We can chain searches!

$albums_rs = $albums_rs->search({
    name => { '!=' => 'Greatest Hits' },
});

working with rsets

# Returns ResultSet in scalar context
my $albums = $schema->resultset('Albums')->search({
    name => { '!=' => 'Greatest Hits' },
});
# Returns a list of rows in list context
my @albums = $schema->resultset('Albums')->search({
    name => { '!=' => 'Greatest Hits' },
});
my @albums = $albums->all;

my $first_album = $albums->first;
my $count      = $albums->count;

working with rsets

while (my $album = $schema->resultset('Albums')->next) {
    print "title: ".$album->title."(" . $album->year.")\n";
}

Watch out !

# Iterating over a ResultSet
while (my $album = $albums->next) {
    print "title: ".$album->title."(" . $album->year.")\n";
}

==> Infinite loop

retrieve: find

my $row = $schema->resultset('Albums')->find({
    name => 'Greatest Hits',
});

Expects a selective enough condition to return ONE row

my $row = $schema->resultset('People')->find({
    name => 'Juan',
    dni  => '11223344A',
});
# returns MyApp::Schema::Result::People

Returns ONE result

DBIx::Class::Storage::DBI::select_single(): 
Query returned more than one row

Oooops!

update

my $result = $schema->resultset('People')->find({
    dni  => '11223344A',
});

$result->update({ age => 15 });

$result->email('juan@juan.com');
$result->age(15);
$result->update;

Changes wont persist until 'update' is called!

Row level

update

my $rs = $schema->resultset('People')->search({
    name => 'juan',
});

$rs->update({ age => 15 });

ResultSet level

delete

my $row = $schema->resultset('People')->find({
    dni=> '12341234A',
});

$row->delete;

Row level

ResultSet level

my $rs = $schema->resultset('People')->search({
    name => 'juan',
});

$rs->delete;

how does that work?

that's cool. but wait...

...Remember 'result classes'?

Defining your DBIx::Class Schema

define schema

package MyMusic::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

# Loads all 'Result' & 'ResultSet' classes
# ...if well namespaced!
__PACKAGE__->load_namespaces;

1;

a word on namespaces

# Schema class:
<app_name>::Schema

# Result classes:
<app_name>::Schema::Result::<table_name>

# ResultSet classes:
<app_name>::Schema::ResultSet::<table_name>

# Examples:
MyMusic::Schema; # schema
MyMusic::Schema::Result::Albums;
MyMusic::Schema::ResultSet::Albums;

Good practice. Good for your mental health!

schema: connection

use MyMusic::Schema;

my $schema = MyMusic::Schema->connect(
    'dbi:mysql:dbname=my_music;host=localhost','uree','1234'
);

my $artists = $schema->resultset('Artist')->search();

How to make a DB connection?

That's a lot of writing...

package MyMusic::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

# Loads all 'Result' & 'ResultSet' classes
__PACKAGE__->load_namespaces;

# Convenience connection method
sub connect_from_config {
    return __PACKAGE__->connect(
        "dbi:mysql:database=my_music;host=localhost",
        "uree",
        "1234",
    );
}

1;

schema: connection

Define custom connection method @ Schema class:

schema: connection

package MyMusic::Schema;

use strict;
use warnings;

use base 'DBIx::Class::Schema';

# Loads all 'Result' & 'ResultSet' classes
__PACKAGE__->load_namespaces;

# Convenience connection method
sub connect_from_config {
    return __PACKAGE__->connect(
        "dbi:mysql:database=my_music;host=localhost;"
        ."mysql_read_default_file=/home/uree/.my.cnf;"
        ."mysql_read_default_group=application;",
        undef,
        undef,
    );
}

1;

A better approach:

schema: connection

use MyMusic::Schema;

my $schema = MyMusic::Schema->connect(
    'dbi:mysql:dbname=my_music;host=localhost','uree','1234'
);

use MyMusic::Schema;

my $schema = MyMusic::Schema->connect_from_config();

Result classes: artist

package MyMusic::Schema::Result::Artist;

use base 'DBIx::Class::Core';

__PACKAGE__->table("artists");
__PACKAGE__->add_columns(
    "id", {
        data_type => "mediumint",
        extra => { unsigned => 1 },
        is_auto_increment => 1,
        is_nullable => 0,
    },
    "name", { 
        data_type => "varchar",
        is_nullable => 0, 
        size => 50 
    },
});

__PACKAGE__->set_primary_key("id");
__PACKAGE__->add_unique_constraint("name", ["name"]); # name, [cols]

Define Result Classes

relationships

... because wait; wasnt all this about relational world?

Artists

Albums

1 - N

1 - 1

People

couple

N - M

Artists

Festivals

Important: Relationships in DBIx::Class are defined from the perspective of each one of the tables.

relationships

.... Exactly, they are defined @ the Result Class :D

defining relationships: 1-N

Artists

Albums

1 - N

package MyMusic::Schema::Result::Album;

__PACKAGE__->belongs_to(
    "artist",
    "MyMusic::Schema::Result::Artist",
    { "foreign.id" => "self.artist" },
);

package MyMusic::Schema::Result::Artist;

__PACKAGE__->has_many(
    "albums",
    "MyMusic::Schema::Result::Album",
    { "foreign.artist" => "self.id" },
);

defining relationships: 1-1

People

1 - 1

couple

package MyApp::Schema::Result::People;

__PACKAGE__->has_one(
    "couple",
    "MyApp::Schema::Result::People",
    { "foreign.couple" => "self.id" },
);

package MyApp::Schema::Result::People;

__PACKAGE__->belongs_to(
    "couple",
    "MyApp::Schema::Result::People",
    { "foreign.id" => "self.couple" },
);

defining relationships: N-M

Artists

Festivals

N - M

package MyMusic::Schema::Result::ArtistsFestivals;

__PACKAGE__->belongs_to(
    "festival",
    "MyApp::Schema::Result::Festival",
    { "foreign.id" => "self.festival" },
);
__PACKAGE__->belongs_to(
    "artist",
    "MyApp::Schema::Result::Artist",
    { "foreign.id" => "self.artist" },
);

defining relationships: N-M

package MyMusic::Schema::Result::Artist;

__PACKAGE__->has_many(
    "artist_festivals",
    "MyApp::Schema::Result::ArtistsFestivals",
    { "foreign.artist" => "self.id" },
);

package MyMusic::Schema::Result::Festivals;

__PACKAGE__->has_many(
    "artist_festivals",
    "MyApp::Schema::Result::ArtistsFestivals",
    { "foreign.festival" => "self.id" },
);

defining relationships: N-M

Lastly, in DBIx::Class, N-M differ a bit from other relationships.
Relationship = direct connection between 2 tables.
N-M are called 'relationship bridges'

N-M are special...

Result classes: album

package MyMusic::Schema::Albums;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("albums");

__PACKAGE__->add_columns(...)

__PACKAGE__->set_primary_key("id");

__PACKAGE__->belongs_to(
    "artist", 
    "MyMusic::Schema::Albums", 
    {id => "artist" }
);

Result classes: festival

package MyMusic::Schema::Festivals;

use strict;
use warnings;

use base 'DBIx::Class::Core';

__PACKAGE__->table("festivals");

__PACKAGE__->add_columns(...)

__PACKAGE__->set_primary_key("id");

__PACKAGE__->has_many(
    "artists_festivals", 
    "MyMusic::Schema::ArtistsFestivals", 
    {"foreign.festival" => "self.id"},
);
__PACKAGE__->many_to_many("artists", "artists_festivals", "artist");

Result classes: artistsfestivals

package MyMusic::Schema::Result::ArtistsFestivals;

use base 'DBIx::Class::Core';

__PACKAGE__->table("artists_festivals");

__PACKAGE__->add_columns(...)

__PACKAGE__->set_primary_key("artist","festival");

__PACKAGE__->belongs_to(
    "festival",
    "MyApp::Schema::Result::Festival",
    { "foreign.id" => "self.festival" },
);
__PACKAGE__->belongs_to(
    "artist",
    "MyApp::Schema::Result::Artist",
    { "foreign.id" => "self.artist" },
);

... That's a lot of work

"well, my DB's got 93 tables..."

dbix::Class::schema::loader

enter...

"let me handle this for ya"

Dagfinn Ilmari Mannsåker

(ILMARI)

Author

(Separate distribution from DBIx::Class)

issues

Waaay to many lines of code!

We are keeping the state in 2 separate places! (DRY!)

dbix::CLASS::schema::loader

AUTOMATES the definition of a DBIx::Class::Schema

EVERYTHING :DD

Column definitions
PKs
Unique constraints
... Relationships too!

dbix::class::schema::loader

... HOW DOES THAT WORK?!

#!/usr/bin/env perl
use strict;
use warnings;

use DBIx::Class::Schema::Loader qw/make_schema_at/;


make_schema_at(
    'MyMusic::Schema',
    {
        debug => 1,
        dump_directory => './lib'
    },
    [
        'dbi:mysql:dbname=my_music;host=localhost','uree','1234',
    ],
);

FAITH in dbix::CLASS

restored

"complex" queries

joins & co.

... quick note about relationships

Relationships provide accessor methods

Enable ResultSet 'navigation'

my $albums = $artist->albums;  # Rset containing all of the artist's albums
my @albums = $artist->albums; # in list ctxt: returns a list of rows

# VS
my $albums = $schema->resultset('Albums')->search({
    artist => $artist->id,
});

Its 'sugar': the query is performed anyway

Almost everytime ;)

... quick note about relationships

More convenience methods provided

$artist->create_related('albums', {
        name => 'Black Sands',
        year => '2010',
});

$artist->add_to_albums({
        name => 'With Teeth',
        year => '2005',
});

my $album = $schema->resultset('Albums')->find({ id => 123 });

$artist->add_to_albums($album);

template toolkit & relationships

[% artist.albums.count %]

[% artist.albums_rs.count %]

TT: List context!!!

artist.albums is returning a LIST ==> doesnt work

*_rs methods force return of a ResultSet

complex queries: joins

my $artists_albums = $schema->resultset('Artists')->search({
    name => {'like' => 'the%'}
},{
    join => ['albums']  # relationship name
});

while (my $a = $artists_albums->next) {
    print $a->name ." - " . $a->album->title."\n";
}

Wont work as expected

SELECT ar.id, ar.name FROM artists ar
JOIN albums al on ar.id=al.artist;
WHERE ar.name LIKE 'the%';

We need to specify desired columns to retrieve

complex queries: joins

my $artists_albums = $schema->resultset('Artists')->search({
    name => {'like' => 'the%'}
},{
    join => ['albums']  # relationship name
    '+select' => ['albums.title'],
    '+as' => ['albums_title'],  # NOT A SQL 'AS' !
});

while (my $a = $artists_albums->next) {
    print $a->name ." - " . $a->get_column('albums_title')."\n";
}

SELECT ar.id, ar.name, al.title FROM artists ar
JOIN albums al on ar.id=al.artist;
WHERE ar.name LIKE 'the%';

Works! YAY :)

joins & N-M relationships

my $artists_festivals = $schema->resultset('Artists')->search({
    name => {'like' => 'the%'}
},{
    join => ['festivals']  # many_to_many
    '+select' => ['festivals.name'],
    '+as' => ['festivals_name'],  
});

Wont work:
Relationship = connection between 2 tables
N-M is 'special': a Relationship Bridge

joins & N-M relationships

my $artists_festivals = $schema->resultset('Artists')->search({
    name => {'like' => 'the%'}
},{
    join => [{ 'artists_festivals' => 'festival'}],
    '+select' => ['festival.name'],
    '+as' => ['festival_name'],  
});

Works! Multi-step join

multi-step joins

my $artists_festivals = $schema->resultset('Artists')->search({
    name => {'like' => 'the%'}
},{
    join => [{ 'artists_festivals' => 'festival'}, 'albums'],
    '+select' => ['festival.name'],
    '+as' => ['festival_name'],  
});

join types :(

Join types are defined per relationship

Cant be changed on 'the go'

Workaround: define additional relationship per desired join type

package MyMusic::Schema::Result::Album;

# We could search for albums with NO artist..
__PACKAGE__->belongs_to(
    'artist_left',
    'MyMusic::Schema::Result::Artist',
    {'foreign.id' => 'self.artist'},
    {'join_type' => 'left'},
);

They are working on it!

order, complex conditions...

my $albums = $schema->resultset('Albums')->search({
    -or => [
        year => '1968',
        year => '2005',
    ],
},{
    order_by => [{-desc => 'year'}],  
});

grouping

my $album_cnt = $schema->resultset('Albums')->search({},{
    group_by => ['artist'],
    '+select' => [{count => 'id', -as => 'amount_of_albums'}], #SQL 'AS'
    '+as' => ['num_albums'],
    order_by => [{-desc => 'amount_of_albums'}],  
});

while (my $a = $album_cnt->next) {
    print $a->artist->name . " - " . $a->get_column('num_albums')."\n";
}
# $a is an 'album'. Has a relationship 'belongs_to' to artist
# we can navigate $a->artist (returns a result)
# Not the case with a has many (would return a resultset)

prefetch

my $albums = $schema->resultset('Albums')->search();

while (my $a = $albums->next) {
    print $a->artist->name."\n";  # One extra query per iteration!
}

my $albums = $schema->resultset('Albums')->search({},{
    prefetch => 'artist',  # performs 1 query to retrieve all data
});

while (my $a = $albums->next) {
    print $a->artist->name."\n";  # No more extra queries
}

How many queries?

ONE single query

extending

result clases

& resultset clases

Extending result class

package MyMusic::Schema::Result::Album;
# ...
__PACKAGE__->belongs_to( 
  "artist",               
  "MyMusic::Schema::Result::Artist",   
  { id => "artist" },     
);           
                      
# Created by DBIx::Class::Schema::Loader v0.07042 @ 2014-11-08 04:11:58
# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:7c+0vxCsC6BigZr2ws9igw
 
sub title_year {
    my($self) = @_;
    return $self->title . '-' . $self->year; 
}            
        
# You can replace this text with custom code or comments, 
# and it will be preserved on regeneration

1;

Extending resultset class

package MyMusic::Schema::ResultSet::Album;
 
sub seventies {
    my($self) = @_;
    return $self->search({
        year => {
            -between => [1970, 1979],
        }
    }); 
}            

1;

the BEST DEBUG method

DBIC_TRACE=1

dbix::Class::deploymenthandler

... Or versioning your DB schema

Get involved!

DBIx:Class Hackathons

Next one on: April 2015

the end

... For real!