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

  1. INTUITION: what, how & utility provided
  2. Establish a knowledge foundation
  3. 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();

VS

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!

DBIx::Class intro

By ureesoriano

DBIx::Class intro

Barcelona Perl Workshop 2014

  • 2,431