Telephone +44(0)1524 64544
Email: info(at)shadowcat.co.uk

Sat Dec 22 00:30:00 2012

Slides for the talk ow at lpw-2013

-

Decadon

-

10
years

-

A long
time
ago ...

-

... at a
Shadowcat
far far
away ...

-

ORM
WARS

-

-

Episode I

-

Episode I
The CPAN
menace

-

In house
framework

-

Maypole
+ code
generator

-

Catalyst

-

Catalyst
(thank
fuck)

-

Common
factor

-

LCD

-

Class::DBI

-

Ooooold

-

Single
column
PKs

-

Basic
hash
search

-

WHERE x = ?
AND y = ?

-

Joins?

-

What's a
join?

-

Well,
actually

-

Introducing
Ima::DBI

-

(playing
Jar Jar
Binks)

-

set_sql

-

  ->set_sql(name => 

-

  SELECT __ESSENTIAL__
  FROM   __TABLE__
  WHERE  rating = ?

-

Yes, it
does an
s{}{}

-

But wait,
there's more

-

  SELECT __ESSENTIAL__
  FROM   __TABLE__
  WHERE  %s

-

Yes, that's
sprintf

-

Really.

-

Let's just
move on

-

-

Episode Ia

-

Episode Ia
The Chansen
Edit

-

Christian
Hansen

-

Class::DBI::Sweet

-

Caching
SQL::Abstract

-

  search(
    x => $x,
    [
      y => $y,
      z => $z
    ]
  )

-

  WHERE x = ?
  AND (
    y = ?
    OR z = ?
  )

-

OMG

-

Then ... a
challenge

-

"ActiveRecord
style :include
is impossible"

-

ORLY?

-

join
prefetch

-

Internals
still
ridonkulous

-

Bugwards
compatibility
required

-

Curtains

-

-

Episode II

-

Episode II
Attack of
the clone

-

  cp -a ../Class-DBI/t
    t/cdbicompat

-

*hack*

-

DBIx::Class

-

Class::DBI
development
ended

-

SURPRISE!
USERS!

-

You're using
WHAT in
production?

-

Stable
release
cycle

-

I accidentally
the whole
resultset API

-

Development
continued

-

Architecture
starts to
groan at
the seams

-

  ->search

-

  Comment has creator
  Comment has post
  Post has creator

-

DBIx::Class
aliases by
relationship
name

-

  ->search(
    ...,
    { join => {
        post => 'creator',
        creator => {}
      } }
  )

-

  FROM comments me
  JOIN posts post ON ...
  JOIN creators creator ON ...
  JOIN creators creator_2 ON ...

-

Which
creator
is which?

-

Can't easily
introspect
SQL::Abstract

-

s{easily}{}

-

Started thinking
about how to
fix that

-

Meanwhile ...

-

-

Episode III

-

Episode III
Revenge of
the ribasith

-

ribasushi

-

99 problems

-

99 problems
... and they're
all SQL
dialects

-

DBIHacks.pm

-

DBIHacks.pm
the dark side
of DBIx::Class

-

  $sql_maker->{quote_char}
    = ["\x00", "\xFF"];

-

  my $al_re = qr/
    $lquote $alias $rquote $sep
    (?: $lquote ([^$rquote]+) $rquote )?
      |
    \b $alias \. ([^\s\)\($rquote]+)?
  /x;

-

  my @matches
    = $piece =~ /$al_re/g

-

Yes, he's
regexping
the SQL

-

RIIIIBAAAAAAAA

-

... wait,
wrong
franchise

-

Sadly
necessary

-

Sensible
databases

-

  SELECT cd.name, artist.name
  FROM cds cd
  JOIN artists artist
    ON cd.artistid = artist.id
  WHERE artist.age > ?NSI
standard
SQL way?

-

  SELECT cd.name, artist__name
  FROM (
    SELECT cd.name, artist__name,
      ROW_NUMBER()
        OVER( ORDER BY ORDER__BY__001 )
        AS rno__row__index
    FROM (
      SELECT cd.name, artist.name AS artist__name,
        artist.age AS ORDER__BY__001
      FROM cds cd
      JOIN artists artist
        ON cd.artistid = artist.id WHERE artist.age > ?
    ) cd
  ) cd
  WHERE (
    rno__row__index >= ? AND rno__row__index <= ?
  )

-

Really.

-

I HATE
DATABASES

-

... but
it works

-

-

Episode IV

-

Episode IV
A new hope

-

Data::Query

-

Query as
data structure

-

Query as
explicit
introspectable
data structure

-

  WHERE year > 2000

-

  {
    args => [
      {
        elements => [ "year" ],
        type => "Identifier"
      },
      {
        subtype => { Perl => "Scalar" },
        type => "Value",
        value => 2000
      }
    ],
    operator => { Perl => ">" },
    type => "Operator"
  }

-

No, you are
not expected
to write that

-

SQL::Abstract::Converter

-

  WHERE x = ?
  OR y = ?

-

  [
    x => $x,
    y => $y,
  ]

-

  [
    { x => $x },
    { y => $y },
  ]

-

  { -or => {
    x => $x,
    y => $y
  } }

-

  { -or => [
    x => $x,
    y => $y
  ] }

-

...

-

BUT they
all produce
the same
DQ tree

-

2011
SQL::Abstract
ported

-

DBIx::Class
exploded

-

DBIx::Class
violates
SQL::Abstract
encapsulation

-

DBIx::Class
violates
SQL::Abstract

-

Rewrote those
parts of the
DBIx::Class
internals

-

Limit
Dialects

-

I HATE
DATABASES

-

2012
Limit Dialects
(mostly) ported

-

Time to start
adding features?

-

Hahahahahahaha.

-

Hahahahahahaha.
Hahahaha.

-

Hahahahahahaha.
Hahahaha.
Ha.

-

*CRY*

-

-

As an
aside ...

-

I have some
reluctance
about shipping
DQ itself

-

Lots of API
bits to fine
tune

-

lib/DBIx/Class/_TempExtlib.pm

-

  our ($HERE) = File::Spec->rel2abs(
    File::Spec->catdir(
      (File::Spec->splitpath(__FILE__))[1],
      '_TempExtlib'
    )
  ) =~ /^(.*)$/; # screw you, taint mode

  unshift @INC, $HERE;

-

Adds DBIx/Class/_TempExtlib
to the front of @INC

-

Bundled versions
of Data::Query and
SQL::Abstract

-

Look.

-

Last time I said
"this is a
research project"

-

... you put my
svn trunk into
production

-

No, I do
NOT trust
you anymore.

-

-

Episode V

-

Episode V
The Russian
Strikes Back

-

Tried to
merge
master

-

SURPRISE!
DBIHacks
had been
rewritten!

-

RIIIIBAAAAAAAA

-

Rebase?

-

Not a chance

-

New plan?

-

  cp -a ../DBIx-Class-dq/lib/DBIx/Class/SQLMaker*
    lib/DBIx/Class/

-

  git add lib/

-

  git commit -m
    'RIIIIBAAAAAAAA'

-

Ported
DBIHacks

-

Ported
DBIHacks
... again

-

Minimised
changes
this time

-

Data::Query
*and* regexps

-

... no
really

-

Got to
(mostly)
green

-

Tried to
freeze
riba in
carbonite

-

Didn't
work

-

Distracted
him by
hiring him
instead

-

-

Episode VI

-

Episode VI
Return of
the JFDI

-

So ... DBIC
runs on
Data::Query

-

Now
what?

-

Alternative
expression
syntax

-

  ->search(
    { 'cd.title' => $title }
  )

-

  use Data::Query::ExprDeclare;
  ...
  ->search(expr {
      $_->cd->title eq $title
  })

-

  ->search({
     x => $x,
     y => $y
  });

-

  ->search(expr {
    $_->x eq $x
    & $_->y eq $y
  })

-

& ?

-

& can be
overloaded

-

& can be
overloaded,
&& can't

-

-

Easier
semi-complex
relationships

-

  ->has_many(
    cds_21c => 'My::Schema::CD',
    sub {
      my $args = shift;
      return (
        { "$args->{foreign_alias}.artist" =>
            { '=' => { -ident => "$args->{self_alias}.artistid"} },
          "$args->{foreign_alias}.year"   => { '>=' => 2000 },
        },
        $args->{self_rowobj} && {
          "$args->{foreign_alias}.artist" => $args->{self_rowobj}->artistid,
          "$args->{foreign_alias}.year"   => { '>=' => 2000 },
        }
      );
    },
  );

-

  ->has_many(
    cds_21c => 'My::Schema::CD',
    expr {
      $_->foreign->artist == $_->self->artistid
      & $_->foreign->year < 2000
    }
  );

-

-

grep_cache

-

  my $rs = ...->search(..., { cache => 1 });
  ...
  my $subset = $rs->search({ 'me.name' => $name });

-

Two
queries

-

  my $rs = ...->search(
    ..., { cache => 1, grep_cache => 1 }
  );
  ...
  my $subset = $rs->search({ 'me.name' => $name });

-

  grep { $_->get_column('name') eq $name }

-

I've been
waiting
YEARS to
do that.

-

-

Semantic
query

-

  $rs->where

-

where?

-

New style,
new method
name

-

  $rs->where(expr {
    $_->name eq $name
  });

-

Auto prepends
the current
source alias

-

  WHERE me.name = ?

-

  $cds->search_related('artist')
      ->where(expr {
          $_->name eq $name
        });

-

  WHERE artist.name = ?

-

It
knows
...

-

-

Column
mapping

-

  ->add_columns(
    DB001_nomen =>

-

Don't you
just love
DBAs?

-

  ->add_columns(
    DB001_nomen => {
      ...
      rename_for_dq => 'name',

-

  $rs->where(expr {
    $_->name eq $name
  });

-

  WHERE me.DB001_nomen = ?

-

-

Relationship
mapping

-

  $rs->where(expr {
    $_->post->owner->name eq $name
  });

-

  $rs->search(
    { 'owner.name' => $name },
    { join => { 'post' => 'owner' } }
  );

-

  $rs->where(expr {
    $_->post->owner->name eq $name
    & $_->owner->name eq $other_name
  });

-

  $rs->search(
    { 'owner.name' => $name,
      'owner_2.name' => $other_name },
    { join => [
        { 'post' => 'owner' },
        'owner'
    ] }
  );

-

  $rs->where(expr {
    $_->post->owner->name eq $name
    & $_->owner->name eq $other_name
  });

-

  WHERE owner.name = ?
  AND owner_2.name = ?

-

You can
combine
both too

-

  $rs->search(
    { 'owner.name' => $name },
    { join => { 'post' => 'owner' } }
  )->where(expr {
    $_->owner->name eq $other_name
  });

-

Resultset
chaining
just got
way more
fun ...

-

This is all
experimental

-

Or: Yes, I am
allowed to
change the API

-

This is all
incomplete

-

Or: Most things
don't yet
accept expr{}s

-

But ...
it works.

-

HALF A
GODSDAMNED
DECADE

-

... but
it works.

-

Want to
play?

-

  git://git.shadowcat.co.uk/dbsrgits/Data-Query.git
  git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git
    (dq branch)
  git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git
    (dq2eb branch)

-

No.
Wait.

-

The empire
demands more.

-

Even Episode VI
has to ship
some day ...

-

  cpanm MSTROUT/DBIx-Class-0.08901-TRIAL.tar.gz

-

  # WARNING: Battle station may
  # not yet be fully operational
  cpanm MSTROUT/DBIx-Class-0.08901-TRIAL.tar.gz

-

  # WARNING: Battle station may
  # not yet be fully operational

  cpanm MSTROUT/DBIx-Class-0.08901-TRIAL.tar.gz

  # irc.perl.org #dbix-class has many
  # Bothans to bring you information

-

  # (thank you)
  #
  # WARNING: Battle station may
  # not yet be fully operational

  cpanm MSTROUT/DBIx-Class-0.08901-TRIAL.tar.gz

  # irc.perl.org #dbix-class has many
  # Bothans to bring you information