Slides for the talk catalyst-dbic-pg at pgwest-2008
Catalyst
DBIx::Class
PostgreSQL
-
Matt S Trout
-
Catalyst
DBIx::Class
Moose
-
Shadowcat Systems Limited
http://shadowcat.co.uk/
-
NW England
-
NW England
Web Development
Consultancy
-
Thanks to:
Shadowcat
SPI
-
Thanks to:
Airspace
Software
-
-
Technologies
Product
Implementation
-
-
Catalyst
-
MVC
framework
-
mod_perl
FastCGI
prefork
-
NOT the rails
of perl
-
MyISAM
framework
-
profanity
-
Model
agnostic
-
DBIx::Class
Rose::DB::Object
Catalyst::Model::DBI
-
LDAP
SVN
filesystem
...
-
View
agnostic
-
Template Toolkit
HTML::Mason
XML::LibXSLT
...
-
Self-contained
controllers
-
package MyApp::Controller::Admin;
use parent qw(Catalyst::Controller);
sub login :Local { # /admin/login
my ($self, $c) = @_;
...
-
Re-usable
controllers
-
package MyApp::ControllerBase::Login;
sub login :Local {
...
package MyApp::Controller::Admin;
use parent (MyApp::ControllerBase::Login);
-
Lightweight
core
-
Well understood
extension APIs
-
347 Catalyst::*
distributions
on CPAN
-
New extension
author CPAN
id signups
every week
-
Designed to
scale to
large apps
-
vox.com
-
vox.com
BBC iPlayer
-
vox.com
BBC iPlayer
youporn.com
-
-
DBIx::Class
-
ORM?
-
ROM?
-
Stays close
to the
database
-
Exposes the
full power of
the database
-
David Fetter
still hates it,
of course
-
Resultset
oriented
-
my $rs = $schema->resultset('User');
# SELECT * FROM users
-
my $admins = $rs->search(
{ 'role.name' => 'admin', join => 'role' });
# SELECT users.* FROM users JOIN roles
# ON users.role_id = roles.id
# WHERE roles.name = 'admin'
-
my $admin_logins = $rs->search_related('logins');
# SELECT logins.* FROM logins
# JOIN users ON ... JOIN roles ON ...
# WHERE roles.name = 'admin'
-
Designed for
extensiblity
-
package My::ResultSet::User;
use parent qw(DBIx::Class::ResultSet);
sub with_role {
my ($self, $role) = @_;
return $self->search(
{ 'role.name' => $role, join => 'role' });
}
-
my $admin_logins
= $schema->resultset('Users')
->with_role('admin')
->search_related('logins');
-
while (my $row = $rs->next) {
...
-
$user->approved_at(DateTime->now);
$user->update;
-
$photo->update({ views => \'views +1' });
-
Composite
key support
-
__PACKAGE__->has_many(
'mail_rules',
'My::Schema::MailRule',
{ 'foreign.domain' => 'self.domain',
'foreign.user' => 'self.user' }
);
-
$rs->search({}, {
rows => 100,
page => $page
});
-
(Somebody PLEASE
make DBD::Pg
support cursors)
-
Memcached
Query slaves
DDL generation
Fixtures
-
sprocs
GROUP BY
HAVING
FROM (SELECT ...
-
-
PostgreSQL
-
You already
know this
one, right?
-
-
Reaction
-
Application
meta-model
-
Component
UI system
-
Heavy use
of the Moose
metaprotocol
-
Still just
Catalyst
underneath
-
-
questions?
-
-
Allocate
-
Hotel
Booking
System
-
Admin
Interface
-
Admin UI:
ExtJS
JSON
Catalyst
-
Booking
Interface
-
Booking UI:
Catalyst
Reaction
(some Ext)
-
-
Availability
model
-
Rack
rate
-
Special
offers
-
Seasonal
rates
-
Per-day
rates
-
By:
product
date
occupancy
room type
stay type
-
Limited
allocation
-
By:
product
date
occupancy
room type
-
What dates can
I arrive?
-
What's the
cheapest offer
for my stay?
-
Constraint
problem
-
Many degress
of freedom
-
You're using
MySQL?!
-
NO YOU'RE
FUCKING NOT
-
-
Core data
model
-
Consortium
Hotel Group
Hotel
-
Old system:
one big
tree table
-
Performance:
-
Performance:
what performance?
-
Site is
white
labeled
-
By Hotel Group
By Hotel
-
Hotel Group
isa TradingEntity
Hotel
isa TradingEntity
-
Tables:
hotel_group_data
hotel_data
trading_entity_data
-
hotel_group_data.id
-> FK(trading_entity.id)
-
Views:
hotel_group
hotel
trading_entity
-
CREATE VIEW hotel_group AS
SELECT hg.id, hg.parent_id, ...,
te.theme_id, te.name, ...
FROM hotel_group_data hg JOIN trading_entity te
ON (hg.id = te.id);
-
DBIx::Class
implementation
-
package Allocate::Database::Schema::ViewData::HotelGroupData;
__PACKAGE__->add_columns(
'id' => {
'data_type' => 'int',
'sequence' => 'trading_entity_data_id_seq'
},
...
);
-
__PACKAGE__->belongs_to(
"consortium_id",
"Allocate::Database::Schema::ViewData::ConsortiumData",
{ id => "consortium_id" },
);
-
__PACKAGE__->belongs_to(
"parent_id",
"Allocate::Database::Schema::ViewData::HotelGroupData",
{ id => "parent_id" }, { join_type => 'left' }
);
-
CREATE TABLE
statements
generated
from classes
-
package Allocate::Database::Schema::HotelGroup;
use parent qw(Allocate::Database::Schema::TradingEntity);
__PACKAGE__->add_columns_from(
qw(Allocate::Database::Schema::ViewData::HotelGroupData));
-
CREATE VIEW
statements
maintained
by hand
(currently :)
-
CREATE RULE hotel_group_update AS ON UPDATE TO hotel_group
DO INSTEAD(
UPDATE trading_entity_data SET
theme_id = NEW.theme_id, ...
WHERE id = OLD.id;
UPDATE hotel_group_data SET
parent_id = NEW.parent_id, ...
WHERE id = OLD.id;
);
-
-not- reliable
for multiple
records at once
-
(I blame
David
Wheeler)
-
next refactor
will use triggers
-
Workaround:
$rs->update(...)
->
$rs->update_all(...)
-
Retrieving
just a
TradingEntity
-
my $domain = $hotel_domains_rs->find(
$url,
{ prefetch => { trading_entity } }
);
my $te = $domain->trading_entity;
$te->auto_cast;
-
sub auto_cast {
my ($self) = @_;
if ( my $cast = $self->hotel || $self->consortium || $self->hotel_group ) {
$_[0] = $cast;
}
}
-
relationships
defined as
join_type => 'left'
-
relationships
prefetched
-
denormalised
'type' field?
-
no real
advantage
-
HotelGroup
is a tree
-
UPDATE hotel_group_data
SET matpath_cache
= COALESCE(
(SELECT matpath_cache FROM hotel_group_data
WHERE id = NEW.parent_id),
'')
|| currval('trading_entity_data_id_seq') || '.'
WHERE id = currval('trading_entity_data_id_seq')
-
my $root
= $obj->matpath_cache;
$rs->search({
matpath_cache => {
like => "${root}.%"
}
});
-
my $leaf =
$obj->matpath_cache;
my @parts = split(/\./, $leaf)[0..-1];
$rs->search({
id => { -in => \@parts }
});
-
-
Public site
application
design
-
Domain Model
Interface Model
ViewPort
Widget
Layout
-
Domain Model - ORM
Interface Model - API
ViewPort - UI state
Widget - UI structure
Layout - templating
-
Allocate::InterfaceModel::Booking::Payment
-
Allocate::InterfaceModel::Booking::Payment
::Payment::Protx
::Payment::Iridium
...
-
Form
Handling
-
ViewPort
introspects
InterfaceModel
-
has 'email' => (
is => 'rw',
isa => Email,
required => 1
);
-
Email: [________]
-
Widget
-
implements fragment message {
my $message = $_{viewport}->error_message;
if ( $message ) {
arg 'as_string' => $message;
render 'message_layout';
}
};
-
=for layout widget
...
[% message %]
...
=for layout message_layout
<span class="message">
[% as_string %]
</span>
-
Skinning
-
package Allocate::View::Hotel;
...
with 'Catalyst::Component::InstancePerContext';
sub build_per_context_instance {
my ( $self, $c ) = @_;
return $self->new(
app => $c,
skin_name => $c->stash->{hotel}->theme_path
);
}
-
=extends NEXT
=for layout container
[% call_next %] <br />
=for layout message_layout
[% call_next %] <br />
=cut
-
-
Quick
availability
-
Take:
1+ hotels
1+ occupancies
two months
-
Find all possible
combinations of
-
Product/offer
Room
Stay type
-
Rate
cards
-
hotel_product_ratecard
Column | Type
**************************************
id | integer
product_id | integer
date | date
ends | date
description | character varying(150)
-
JOIN ON
BETWEEN
doth not
perform
-
rate_card_pivot
Column | Type
************************
rate_card_id | integer
product_id | integer
date | date
-
Indexes:
...
"rate_card_pivot_multi" btree
(rate_card_id, product_id,
date_part('dow'::text, date))
-
JOIN rate_card_pivot rcp ON
prod_hprc.day
= EXTRACT(DOW FROM rcp.date)
-
hotel_product_rates_card
Column | Type
**************************
rate_card_id | integer
stay_id | integer
occupancy_id | integer
hotel_room_id | integer
day | smallint
value | numeric
-
hotel_product_rates_override"
Column | Type
*************************
product_id | integer
stay_id | integer
occupancy_id | integer
hotel_room_id | integer
date | date
value | numeric
-
LEFT JOIN
+
COALESCE
-
Per-day
allocation
-
LEFT JOIN
hotel_product_room_allocation
all_rooms
ON
(all_rooms.date = p.date
AND all_rooms.product_id = so.id)
-
SELECT
...
day.alloaction
- SUM(all_rooms.booked)
...
GROUP BY
<everything else>
-
Build up
in layers
-
CREATE VIEW product_day_info AS ...
CREATE VIEW rackrate_day_info AS
SELECT ...
FROM product_day_info rr_pdi
JOIN ...
CREATE VIEW combined_avail_search2 AS
SELECT ...
FROM rackrate_day_info p
...
-
End result:
1.5 seconds
-
(and I'm an
index checking
pass short)
-
PostgreSQL's
optimiser
-rocks-
-
DBIx::Class
implementation
-
Generating
classes from
the database
-
my $sth = $dbh->table_info(undef,'public','%','VIEW');
while (my $data = $sth->fetchrow_hashref) {
push(@views, $data->{TABLE_NAME});
}
-
foreach my $view (@views) {
my $sth = $dbh->column_info(undef,'public',$view,'%');
my @cols;
while (my $data = $sth->fetchrow_hashref) {
push(@cols, $data->{COLUMN_NAME});
}
-
package [% package %];
...
__PACKAGE__->add_columns(qw(
[% columns.join("\n") | indent(2) %]
));
__PACKAGE__->set_primary_key(__PACKAGE__->columns);
-
Data
retrieval
-
my $rs = $source_rs->search(
{
occupancy_id => { -in => [ keys %occ_map ] },
hotel_id => { -in => [@hotel_ids] },
-and => [ { date => { '>=', $from } }, { date => { '<=', $to } }, ],
($self->has_only_offer
? ( offer_product_id => $self->only_offer )
: $self->rackrate_only ? () : ( offer_display_standard_avail => [ 1, undef ] ),
),
},
-
{
select => \@select,
as => \@as,
result_class => HashRefInflator,
order_by => [qw(date hotel_id occupancy_id room_id)],
},
);
-
HashRefInflator
just returns a
data structure
-
If objects
don't make
sense, don't
use them ...
-
Constraint
search
-
# iterate rooms in increasing id
# order for min-id short-circuit
foreach my $maybe_room (
sort { $a <=> $b } keys %{$occ_data->{rooms}}
) {
-
Accumulating
allocation
usage
-
# dynamically scoped
local $used->{room}{$maybe_room}
= $used->{room}{$maybe_room} + 1;
-
# short circuiting
ROOM: foreach my $maybe_room (
...
next ROOM unless
$room_data->{room_avail}
> $used->{room}{$maybe_room};
-
Multiple
occupancies
via recursion
-
if (@next_occs) {
my $inner_accum = ($accum
? $accum->{$maybe_room}{$maybe_product}[1]
: undef);
my $sub_choices = occs(
$idata, \@next_occs, 0, $used, $inner_accum, $alt_stay_length
);
next unless $sub_choices;
-
Multiple
nights
via iteration
-
if ($top_level && keys %$choices && ($stay_length > 1)) {
# if first night and more than one night
my @rdata = @{$data}[1..$stay_length-1];
foreach my $rdata (@rdata) { # rest of nights' data
my $is_last = $rdata eq $rdata[-1];
$choices = occs(
$rdata, $occ_list, $keep_min_max, undef, $choices, undef, $is_last
);
last unless $choices; # short-circuit if all eliminated
}
}
-
End result:
$search->allowed_arrival_dates
$search->possible_choices
-
-
Summary
-
Tables all
generated by
DBIx::Class
-
OODB views
written in SQL,
used as
normal objects
via DBIx::Class
-
Availability views
written in SQL,
queried via DBIx::Class
without object creation
-
TMTOWTDI
-
Pick which
works best
-
Catalyst/Reaction
are model agnostic
-
Catalyst/Reaction
are model agnostic
- so they don't
even notice ...
-
Key take away:
Frameworks and
ORMs do -not-
restrict good
database design
-
END
-
http://airspace.co.uk/
http://catalystframework.org/
http://lists.scsys.co.uk/
irc.perl.org#catalyst
these slides will be on
http://shadowcat.co.uk/