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