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

Sat Dec 22 00:30:00 2012

Slides for the talk data-query at ipw-2012

-

Data::Query

-

A little
history

-

Shadowcat

-

Maypole
Class::DBI

-

Catalyst
Class::DBI

-

Class::DBI

-

Argh?

-

No query
composition

-

No multi column
primary keys

-

SQL strings
everywhere

-

Estimates
too high

-

Hours a day
on #catalyst

-

Temper.
Lost.

-

DBIx::Class

-

Saner?

-

More
powerful

-

Different
design
mistakes

-

Class::DBI::Sweet

-

Class::DBI
SQL::Abstract

-

DBIx::Class
SQL::Abstract

-

SQL::Abstract

-

Argh?

-

DWIM
syntax

-

Single pass
conversion
to SQL

-

You can't
introspect
the query

-

Can't
rename
columns

-

Can't
rename
tables

-

Sigh

-

Needs an
explicit
layer

-

Started
thinking
in 2008

-

Explicit
queries

-

Query as
data
structure

-

Yes, this
has taken
me over
four years

-

Meanwhile ...

-

DBIx::Class
got more
features

-

Users'
expectations
increased

-

(perl6 has
the same
problem :)

-

Anyway

-

First attempt at
SQL::Abstract 2

-

MooseX::Declare

-

The perl
community's
latest
"too clever"
phase

-

Overengineered
Slow to start

-

Not acceptable
to CGI users

-

Started
again

-

2010

-

2010
Built basic
codebase

-

2011

-

2011
SQL::Abstract
ported

-

DBIx::Class
exploded

-

DBIx::Class
violates
SQL::Abstract
encapsulation

-

DBIx::Class
violates
SQL::Abstract

-

2012

-

2012
Converted
to Moo!

-

Converted
to Moo!
(ye gods but
that helped)

-

DBIx::Class
violates
SQL::Abstract

-

Rewrote those
parts of the
DBIx::Class
internals

-

Limit
Dialects

-

I HATE
DATABASES

-

Sensible
databases

-

  SELECT cd.name, artist.name
  FROM cds cd
  JOIN artists artist
    ON cd.artistid = artist.id
  WHERE artist.age > ?
  ORDER BY artist.age
  LIMIT ? OFFSET ?

-

The ANSI
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 <= ?
  )

-

-

How does
DBIx::Class
do this?

-

  # mangle the input sql as we will be replacing the selector entirely
  unless (
    $rs_attrs->{_selector_sql}
      and
    $proto_sql =~ s/^ \s* SELECT \s* \Q$rs_attrs->{_selector_sql}//ix
  ) {
    $self->throw_exception("Unrecognizable SELECT: $proto_sql");
  }

-

  # anything with a placeholder in it needs re-selection
  $in_sel_index->{$sql_sel}++
    unless $sql_sel =~ / (?: ^ | \W ) \? (?: \W | $ ) /x;

-

    if (
      ! $in_sel_index->{$node->{sql}}
        or
      $node->{as} =~ / (?<! ^ $re_alias ) \. /x
        or
      $node->{unquoted_sql} =~ / (?<! ^ $re_alias ) $re_sep /x
    ) {
      $node->{as} = $self->_unqualify_colname($node->{as});
      my $quoted_as = $self->_quote($node->{as});
      push @{$sel->{inner}},
        sprintf '%s AS %s', $node->{sql}, $quoted_as;
      push @{$sel->{outer}}, $quoted_as;
      $renamed->{$node->{sql}} = $quoted_as;
    }

-

  for my $chunk ($self->_order_by_chunks ($rs_attrs->{order_by})) {
    # order with bind
    $chunk = $chunk->[0] if (ref $chunk) eq 'ARRAY';
    $chunk =~ s/\s+ (?: ASC|DESC ) \s* $//ix;

    next if $in_sel_index->{$chunk};

-

Scared
yet?

-

This has been
working in
production
for YEARS

-

Welcome to
databases ...

-

-

Back to
Data::Query

-

Explicit
query tree

-

  {
    type => 'Select',
    select => [ ... ],
    from => { ... }
  }

-

  WHERE year > 2000

-

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

-

Simple
helpers

-

  Operator(
    { Perl => '>' },
    [
      Identifier('year'),
      perl_scalar_value(2000)
    ]
  )

-

SQL::Abstract ?

-

SQL::Abstract::Converter

-

Converts SQLA
to Data::Query

-

SQL::Abstract

-

  sub select {
    my ($self, @args) = @_;
    $self->_render_dq(
      $self->converter
           ->_select_to_dq(@args)
    );
  }

-

_render_dq

-

Data::Query::Renderer::SQL::Naive

-

Example:
_render_select

-

  my @select = intersperse(',',
    map +(is_Alias()
           ? $self->_render_alias($_, $self->_format_keyword('AS'))
           : $self->_render($_)), @{$dq->{select}}
  );

  return [
    $self->_format_keyword('SELECT'),
    \@select,
    # if present this may be a bare FROM, a FROM+WHERE, or a FROM+WHERE+GROUP
    # since we're the SELECT and therefore always come first, we don't care.
    ($dq->{from}
       ? ($self->_format_keyword('FROM'), @{$self->_render($dq->{from})})
       : ()
    ),
  ];

-

Eventual
result

-

  [ $sql, @values ]

-

WHERE age > 21

-

  [
    "WHERE age > ? ",
    {
      subtype => {
        Perl => "Scalar"
      },
      type => "Value",
      value => 21
    }
  ]

-

Value types
allow e.g.
DBI binding

-

_render_dq

-

  sub _render_dq {
    my ($self, $dq) = @_;
    if (!$dq) {
      return '';
    }
    my ($sql, @bind) = @{$self->renderer->render($dq)};
    wantarray ?
      ($self->{bindtype} eq 'normal'
        ? ($sql, map $_->{value}, @bind)
        : ($sql, map [ $_->{value_meta}, $_->{value} ], @bind)
      )
      : $sql;
  }

-

What about
outside of
SQL::Abstract?

-

Declarative
interface

-

  SELECT { $_->cd->name }
  FROM { $_->cds, AS('cd') }

-

  {
    from => {
      from => {
        elements => [
          "cds"
        ],
        type => "Identifier"
      },
      to => "cd",
      type => "Alias"
    },
    select => [
      {
        elements => [
          "cd",
          "name"
        ],
        type => "Identifier"
      }
    ],
    type => "Select"
  }

-

  SELECT { $_->cd->name }
  FROM { $_->cds, AS('cd') }

becomes

  SELECT cd.name FROM cds cd

-

  SELECT { $_->cd->name }
  FROM { $_->cds, AS('cd') }
  WHERE { $_->year > 2000 }

becomes

  SELECT cd.name FROM cds cd
  WHERE year > ?
    (binding: 2000)

-

This is
just perl

-

  my @cols = qw(name year genre);
  SELECT { my $root = $_; map $root->$_, @cols }
  FROM { $_->cds }

-

  my @cols = qw(name year genre);
  SELECT { my $root = $_; map $root->$_, @cols }
  FROM { $_->cds }

becomes

  SELECT name, year, genre
  FROM cds

-

Joins

-

  SELECT { $_->cd->name }
  FROM { $_->cds, AS 'cd' }
  JOIN { $_->artists, AS 'artist' }
    ON { $_->cd->artistid eq $_->artist->id }

-

  SELECT { $_->cd->name }
  FROM { $_->cds, AS 'cd' }
  JOIN { $_->artists, AS 'artist' }
    ON { $_->cd->artistid eq $_->artist->id }

becomes

  SELECT cd.name
  FROM cds cd
  JOIN artists artist
    ON cd.artistid = artist.id

-

More
interestingly
...

-

  my $basic =
    SELECT { $_->cd->name }
    FROM { $_->cds, AS 'cd' }
    JOIN { $_->artists, AS 'artist' }
      ON { $_->cd->artistid eq $_->artist->id }

-

  to_sql(FROM { $basic } WHERE { $_->cd->year > 2000 })

becomes


  SELECT cd.name
  FROM cds cd
  JOIN artists artist
    ON cd.artistid = artist.id
  WHERE cd.year > ?
    (binding: 2000)

-

And the
limit
dialects?

-

  SELECT { $_->cd->name, $_->artist->name }
  FROM { $_->cds, AS 'cd' }
  JOIN { $_->artists, AS 'artist' }
    ON { $_->cd->artistid eq $_->artist->id }
  WHERE { $_->artist->age > 25 }
  ORDER BY { $_->artist->age, $_->cd->id }
  LIMIT { 10 } OFFSET { 20 };

-

Sane
databases

-

  SELECT cd.name, artist.name
  FROM cds cd
  JOIN artists artist
    ON cd.artistid = artist.id WHERE artist.age > ?
    ORDER BY artist.age
  LIMIT ? OFFSET ?

-

Insane
databases?

-

Let's look
at some code

-

-

End
result?

-

  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 <= ?
  )

-

Repositories:
  git://git.shadowcat.co.uk/dbsrgits/Data-Query.git
  git://git.shadowcat.co.uk/dbsrgits/SQL-Abstract.git
  git://git.shadowcat.co.uk/dbsrgits/DBIx-Class.git

-

Questions?

-

Thank You
IRC:mst
mst@shadowcat.co.uk
@shadowcat_mst