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