(blah blah hiring blah)

Me

Deobfuscating the Basics 💡

Deobfuscating the Basics 💡

Schema

Bunch of Tables (or views, for the pedants)

Result

Row

ResultSet

Query !!!

ResultSource

Table (...)

less common, but: Storage

Your RDBMS (theoretically could be shared)

Questions?

Definition via ::Schema::Loader

Definition via ::Schema::Loader

1. Create DB with DDL or tool
2. Generate DBIC Schema with ::Schema::Loader

use DBIx::Class::Schema::Loader 'make_schema_at';
make_schema_at( 'My::Schema',
    { debug => 1, dump_directory => './lib' },
    [ 'dbi:Pg:dbname="foo"', 'myuser', 'mypassword' ],
)
         

Definition via ::SL - hints

1. Name tables and columns consistently
2. Create FK's
3. sl_table_moniker
4. (No more ::SL, I don't use it much)

Definition via Perl

Definition via Perl - Schema

• Represents DB
• Good for high level methods
• Almost no boilerplate

Definition via Perl - Schema

package My::Schema; use 5.20.0; use warnings;

our $VERSION = 1;
use base 'DBIx::Class::Schema';

__PACKAGE__->load_namespaces(
   default_resultset_class => 'ResultSet',
);

1;
      

Definition via Perl - Result

• Represents Row
• Normally Bulk of Configuration
• Definition Conflates with ResultSource
• (candy)

Definition via Perl - Result Base Class

Optional, but a good idea

package My::Schema::Result;

use parent 'DBIx::Class::Core';

__PACKAGE__->load_components(
   'Helper::Row::RelationshipDWIM', # shorter slides
);

1;
         

Definition via Perl - Candy Base Class

Definition via Perl - Candy Base Class

Super Optional, but good for slides

package My::Schema::Candy;

use 5.20.0;
use warnings;

use parent 'DBIx::Class::Candy';

sub base { 'My::Schema::Result' }
sub perl_version { 20 }
sub autotable { 1 }

1;
         

Definition via Perl - Result Class

package My::Schema::Result::Device;

use My::Schema::Candy;

primary_column id => {
   data_type => 'int',
   is_auto_increment => 1,
};

column type_id => { data_type => 'int' };

column name => {
   data_type => 'varchar',
   size      => 128,
};

column last_checkin => {
   data_type   => 'datetime',
   is_nullable => 1,
};

column is_enabled => { data_type => 'bit' };

has_many computer_device_links => '::Computer_Device', 'device_id';
belongs_to type => '::DeviceType', 'type_id';

unique_constraint name_and_type => [qw(name type_id)];

1;
         

Deobfuscating Relationships 💡

Deobfuscating Relationships 💡

belongs_to

the only real thing modeled in DDL

"Album"."artist_id" → "Artist"."id"

album belongs to artist

non-undef accessor*

*caveat because users are liars and databases are theives

has_many

other side of belongs to

"Artist"."id" → "Album"."artist_id"

artist has many albums

RS accessor

Questions?

Deobfuscating Relationships 💡

might_have

same as has_many but zero or one (rare)

"Artist"."id" → "PreviousName"."artist_id"

artist might have a previous name

Natalie Portman's Shaved Head → Brite Futures

undefable accessor

has_one

same as has_many but exactly one (more rare)

(first_album) "Artist"."id" → "Album"."artist_id"

artist has one first album

non-undef accessor*

*caveat because ...

Deobfuscating Relationships 💡

Deobfuscating Relationships 💡

many_to_many

not a rel, more than one layer of joins

only adds methods to result, very limiting

"Artist"."id" → "Artist_Label"."artist_id", "Artist_Label"."label_id" → "Label"."id"

artist many_to_many labels

minimal sugar

Definition via Perl - ResultSource

1. Represents table or view
2. Defined entirely as a side effect of the Result Class definition above
3. (Or the other way around)

Definition via Perl - ResultSet Base

package My::Schema::ResultSet; use 5.20.0; use warnings;
use parent 'DBIx::Class::ResultSet';
__PACKAGE__->load_components(
   'Helper::ResultSet::IgnoreWantarray',
   'Helper::ResultSet::Me', # slides
);
1;
      

Definition via Perl - ResultSet

package My::Schema::ResultSet::Device; use 5.20.0; use warnings;
use parent 'My::Schema::ResultSet';
sub search_by_name { $_[0]->search({ $_[0]->me . name => $_[1] }) }
sub enabled { $_[0]->search({ $_[0]->me . is_enabled => 1 }) }
sub find_by_name { $_[0]->search_by_name($_[1])->single }
1;
      

DBIC Usage - ResultSet

DBIC Usage - ResultSet

• "entry" API into DBIC
• Most powerful part of DBIC

Searching 💡

Searching 💡

say $_->name for
   $schema->resultset('Device')
   ->search({ 'me.name' => 'phone' })->all 

say $_->name for
   $schema->resultset('Device')
   ->search_by_name('phone')->all 

Searching 💡

say $_->name for
   $schema->resultset('Device')
   ->enabled
   ->search_by_name('phone')
   ->all 

Rel Traversal 💡

Rel Traversal 💡

• related_resultset (and search_related)
• (and join)
• my first lightbulb moment (notmuch show thread:0000000000000364)

Subqueries

Subqueries

• Mostly just ->as_query
• But you probably also need ->get_column('id')

$schema->resultset('Computer')->search({
   'me.id' => { -in => $location_rs->get_column('id')->as_query },
})
      

Prefetch vs Join 💡

Prefetch vs Join 💡

• join - for searching
• prefetch - for searching and retreiving data

Columns vs Select/As

Columns vs Select/As

• Not once in the 5 years I've used DBIC have I needed select/as
• srsly, YAGNI
• these are exactly equivalent

  select => ['foo'], as => 'bar'

  columns => { bar => 'foo' }

• columns tends to be harder to get wrong
• select/as is for when a single query results in many columns

Populate

What really hits the DB 💡

• $rs->all
• $rs->first
• $rs->single
• $rs->next
• $rs->find
• $rs->update
• $rs->delete
• $rs->populate
• $row->update
• $row->delete
• $row->$row_accessor (non-has_many)
• $row->discard_changes

Extensions You'll Need

Deployment

• DBIx::Class::DeploymentHandler
• DBIx::Class::Migration

Row Extensions

ResultSet Extensions

• DBIx::Class::Helper::ResultSet::IgnoreWantarray
• DBIx::Class::Helper::ResultSet::Me
• DBIx::Class::Helper::ResultSet::Shortcut
• DBIx::Class::Helper::ResultSet::ResultClassDWIM
• DBIx::Class::Helper::ResultSet::SetOperations
• DBIx::Class::Helper::ResultSet::CorrelateRelationship
• DBIx::Class::Helper::ResultSet::DateMethods1

Schema Extensions

• Slides: http://frioux.github.io/yapcna-2014-dbic
• DBIC: A Whirlwind Overview
• Deployment Handler
• Set Based DBIC

Correlated Subqueries

Augmented Inflation

Augmented Inflation

package My::Schema::ResultClass::ComputerWithMacros;

use Moo;
use warnings NONFATAL => 'all';

use Lynx::Macro;
use My::Schema::Result::Computer;

has schema => (
   is => 'ro',
   required => 1,
);

has wmp => (
   is => 'ro',
   lazy => 1,
   init_arg => undef,
   builder => '_build_wmp',
);

sub _build_wmp { Lynx::Macro->new(schema => $_[0]->schema) }

has _macros => (
   is => 'ro',
   lazy => 1,
   init_arg => undef,
   builder => '_build_macros',
);

sub _build_macros {
   return +{
      map { lc $_->{from} => $_ }
      $_[0]->schema->resultset('Macro')->search({
         'me.from' => { -not_like => "%[%]%" },
      }, {
         result_class => '::HRI',
      })->all
   }
}

sub inflate_result {
   my ($self, $src, $data, $prefetch) = @_;

   unless (defined $prefetch->{_macro}[0]{to}) {
      if (my $macro = $self->_macros->{lc $data->{name}}) {
         $prefetch->{_macro} = [$macro]
      } elsif (my $wc_macro = $self->wmp->choose_macro($data->{name})) {
         $prefetch->{_macro} = [$wc_macro]
      }
   }

   My::Schema::Result::Computer->inflate_result($src, $data, $prefetch)
};


1;

      

Oh man transactions!

Oh man transactions!