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

Sat Dec 22 00:30:00 2012

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/