DBIx::Class
Building a basic app

Travis Holton

Instructive demo...need data

  • Cocktail recipe data

    • title: 'Zombie #1' - ingredients: - 1 1/4 oz Lemon juice - 2 dashes Grenadine - 3/4 oz blood Orange juice - 3/4 oz Cherry Heering - 3/4 oz White rum - 2 oz Dark rum - 3/4 oz dark 151 proof rum instructions: 'Shake over ice in a shaker, and strain into a large highball glass over crushed ice.'

  • Special thanks Spidering Hacks; O'Reilly, 2003

    • 1. Modeling A Cocktail Recipe Database

    Database Schema

  • recipe combination of beverage, ingredient, measurement and quantity

    • +--------------+ +-----------------+ | beverages | | recipes | +---------------+ |--------------| |-----------------| | ingredients | | beverage_id |--------<| beverage_id | |---------------| | name | | ingredient_id |>-----| ingredient_id | | title | +---<| measurement_id | | name | | instruction | | | recipe_id |--+ | description | +--------------+ | +-----------------+ | +---------------+ +----------------+ | | | measurements | | +------------+ | |----------------| | | quantities | | | measurement_id |-+ |------------| | | name | | recipe_id |1----+ | unit | | quantity | +----------------+ +------------+

    2. Setting up DBIx::Class

    Setup the model

  • example class structure

    • CocktailFu | |-- Schema.pm |-- Schema | |-- Result | | |-- Beverage.pm | | |-- Ingredient.pm | | |-- Measurement.pm | | |-- Quantity.pm | | |-- Recipe.pm | |-- ResultSet | +-- Beverage.pm

  • Define a class to represent the model

    • CocktailFu | |-- Schema.pm |-- Schema | |-- Result | | |-- Beverage.pm | | |-- Ingredient.pm | | |-- Measurement.pm | | |-- Quantity.pm | | |-- Recipe.pm | |-- ResultSet | +-- Beverage.pm

  • Define a class to represent the model

    • package CocktailFu::Schema; use Moose; use namespace::autoclean; BEGIN { extends 'DBIx::Class::Schema' } __PACKAGE__->load_namespaces(); __PACKAGE__->meta->make_immutable( inline_constructor => 0 ); 1;

  • Instance represents database

    • my $model = CocktailFu::Schema->connect($dsn, $user, $password);

    Defining table relations

  • Create modules that correspond to tables

    • CocktailFu | |-- Schema.pm |-- Schema | |-- Result | | |-- Beverage.pm | | |-- Ingredient.pm | | |-- Measurement.pm | | |-- Quantity.pm | | |-- Recipe.pm | |-- ResultSet | +-- Beverage.pm

  • Model for beverages

    • package CocktailFu::Schema::Result::Beverage; use Moose; use namespace::autoclean; BEGIN { extends 'DBIx::Class::Core' } __PACKAGE__->table('beverages'); __PACKAGE__->add_columns( qw/beverage_id name title instruction/); __PACKAGE__->set_primary_key('beverage_id'); __PACKAGE__->add_unique_constraint([qw/name/]);

  • Model for recipes

    • package CocktailFu::Schema::Result::Recipe; use Moose; use namespace::autoclean; BEGIN { extends 'DBIx::Class::Core' } __PACKAGE__->table('recipes'); __PACKAGE__->add_columns( qw/recipe_id beverage_id ingredient_id measurement_id/); __PACKAGE__->add_unique_constraint( [qw/beverage_id ingredient_id measurement_id/]); __PACKAGE__->set_primary_key(qw/recipe_id/);

  • Model for ingredients

    • package CocktailFu::Schema::Result::Ingredient; use Moose; use namespace::autoclean; BEGIN { extends 'DBIx::Class::Core' } __PACKAGE__->table('ingredients'); __PACKAGE__->add_columns(qw/ingredient_id name description/); __PACKAGE__->set_primary_key('ingredient_id'); __PACKAGE__->add_unique_constraint([qw/name/]);

  • quantities and measurements similar

    • 3. Using the DBIC model

    Usual CRUD

  • Create a row

    • my $beverage = $model->resultset('Beverage')->create( { name => 'mojito', title => 'Mojito' instruction => qq{Place mint leaves and lime...} } );

  • Update a row

    • $beverage->update({name => 'b-52', description => 'B-52'});

  • Delete a row

    • $beverage->delete;

    4. Searching the db: ResultSets vs Rows

    DBIx::Class::ResultSet objects

  • DBIx::Class::ResultSet: Set of 0 or more rows from a table or search.
  • A table

    • my $beverage_rs = $model->resultset('Beverage');

  • Basic search

    • my $rs = $model->resultset('Ingredient')->search( { description => 'Orange Juice' } );

  • Can iterate over using next

    • while (my $ingredient = $rs->next) { .. }

  • Can get rows as array using all

    • my @ingredients = $rs->all; foreach my $ingredient (@ingredients){..}

  • Can count results

    • my $count = $rs->count;

  • Get reference to a ResultSet (i.e. table)

    • my $beverages_rs = $model->resultset('Beverage');

  • Search table

    • my $subset_rs = $beverage_rs->search( {title => {ilike => '%Martini%'}});

  • ResultSet of a search can also be searched

    • my $vespers_martini = $subset_rs->find({name => 'vespers-martini'}); say $vespers_martini->instruction; # e.g. "Shaken, not stirred...."

    DBIx::Class::Row objects

  • DBIx::Class::Row object is instance of classes created earlier.
  • Operations on ResultSets, e.g. find, next, first return Row objects

    • my $ingredient = $ingredient_rs->find(27); # pk search my $other_ingredient = $ingredient_rs ->find({name => 'rum-and-coke'}); # unique constraint

  • Use with primary key and unique constraint columns to return row objects

    • say $ingredient->description; # 'Rum and Coke'

    5. Modeling relationships between tables

    Main types of relationships to model

  • has_one
  • belongs_to
  • has_many
  • many_to_many
  • Represent foreign key relations in db
  • Make it possible to traverse relations in data

    • The has_one relationship

  • Other table has primary key of this table in one of its columns

    • has_one +--------------+ +----------------+ | quantities | | recipes | |--------------| |----------------| | quantity_id | | recipe_id |----------1| recipe_id | | beverage_id | | quantity | | measurement_id | +--------------+ | ingredient_id | +----------------+

  • Use has_one when there is 1:1 relation between two tables.

    • package CocktailFu::Schema::Result::Recipes; #.. __PACKAGE__->has_one( quantity => Quantity => { 'foreign.recipe_id' => 'self.recipe_id' } );

    The belongs_to relationship

  • Use belongs_to to reference foreign key table.
  • recipes belongs_to beverages, ingredients, measurements

    • belongs_to +--------------+ +-----------------+ | beverages | | recipes | +---------------+ |--------------| |-----------------| | ingredients | | beverage_id |--------<| beverage_id | |---------------| | name | | ingredient_id |>-----| ingredient_id | | title | | measurement_id | | name | | instruction | | recipe_id | | description | +--------------+ +-----------------+ +---------------+ package CocktailFu::Schema::Result::Recipe; #.. __PACKAGE__->belongs_to( beverage => Beverage => { 'foreign.beverage_id' => 'self.beverage_id' } ); __PACKAGE__->belongs_to( ingredient => 'CocktailFu::Schema::Result::Ingredient' => { 'foreign.ingredient_id' => 'self.ingredient_id' } ); __PACKAGE__->belongs_to( measurement => 'CocktailFu::Schema::Result::Measurement' => { 'foreign.measurement_id' => 'self.measurement_id' } );

    Using has_one and belongs_to

  • Return object that represents a row in the db.

    • my $ingredient = $recipe->ingredient; say $ingredient->description;

    The has_many relationship

  • Use has_many when foreign table has current table's primary key.

    • has_many +--------------+ +-----------------+ has_many | beverages | | recipes | +---------------+ |--------------| |-----------------| | ingredients | | beverage_id |--------<| beverage_id | |---------------| | name | | ingredient_id |>-----| ingredient_id | | title | | measurement_id | | name | | instruction | | recipe_id | | description | +--------------+ +-----------------+ +---------------+

  • beverages has_many recipes
  • ingredients has_many recipes
  • measurements has_many recipes

    • package CocktailFu::Schema::Result::Beverage; ... __PACKAGE__->has_many( recipes => 'Recipe' => { 'foreign.beverage_id' => 'self.beverage_id' } ); package CocktailFu::Schema::Result::Ingredient; ... __PACKAGE__->has_many( recipes => Recipe => { 'foreign.ingredient_id' => 'self.ingredient_id' } ); package CocktailFu::Schema::Result::Measurement; ... __PACKAGE__->has_many( recipes => Recipe => { 'foreign.measurement_id' => 'self.measurement_id' } ); my $recipes = $beverage->recipes; while (my $recipe = $recipes->next){ say $recipe->ingredient->description; say $recipe->measurement->unit; }

    The many_to_many relationship

  • combines has_many and belongs_to

    • _________________many_to_many_______________ / \ / --has_many-> --belongs_to-> \ +--------------+ +-----------------+ \ | beverages | | recipes | +---------------+ |--------------| |-----------------| | ingredients | | beverage_id |--------<| beverage_id | |---------------| | name | | ingredient_id |>-----| ingredient_id | | title | | measurement_id | | name | | instruction | | recipe_id | | description | +--------------+ +-----------------+ +---------------+ package CocktailFu::Schema::Result::Beverage; #.. __PACKAGE__->many_to_many( ingredients => recipes => 'ingredient' );

  • In code somewhere...

    • my $ingredients = $beverage->ingredients; while (my $ingredient = $ingredients->next) { say $ingredient->description; }

  • Both return ResultSet objects representing sets of rows

    • 6. More Data Manipulation

    Creating a new cocktail

  • Sample data:

    • title: 'Vesper Martini' - ingredients: - 3 cl Gordon's Gin - 1 cl Vodka - 1/2 cl Kina Lillet - 1 Lemon Peel instructions: 'Shaken, not stirred'

  • Insert beverage

    • my $beverage = $model->resultset('Beverage')->create( { name => 'vesper-martini', title => 'Vesper Martini', instruction => q{Shaken, not stirred} } );

  • Insert ingredient

    • my $ingredient = $model->resultset('Ingredient')->create( { name => 'gordons-gin', description => q{Gordon's Gin} } );

  • Insert measurement

    • my $measurement = $model->resultset('Measurement') ->create( { unit => 'cl' } );

  • Insert recipe using beverage, ingredient & measurement

    • my $recipe = $model->resultset('Recipe')->create( { beverage => $beverage->beverage_id, ingredient => $ingredient->ingredient_id, measurement => $measurement->measurement_id, quantity => '1' });

  • has_many relation creates implicit add_to_relationship
  • has_one between recipes and quantities
  • Insert everything at once

    • my $recipe = $beverage->add_to_recipes( { ingredient => { name => 'gordons-gin', description => q{Gordon's Gin} }, measurement => { unit => 'cl' }, quantity => { quantity => 3 } }, { join => { recipes => [qw/quantity ingredient measurement/] } } );

    7. Retrieving data

    The stupid ORM makes too many queries! problem

  • What happens when code does this?

    • my $beverage_rs = $model->resultset('Beverage'); my $beverage = $beverage_rs->find({name => 'agent-orange'}); my $recipes = $beverage->recipes; while (my $recipe = $recipes->next) { my $ingredient = $recipes->ingredient->description; my $measurement = $recipes->measurement->unit; }

    Executing: SELECT me.id, me.name, me.title, me.instruction FROM beverages me WHERE ( me.name = ? ) Parameters: 'agent-orange' Execution took 0.0004 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.beverage, me.ingredient, me.measurement FROM recipes me WHERE ( me.beverage = ? ) Parameters: '96' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '10' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '322' Execution took 0.0030 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '11' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0002 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '321' Execution took 0.0025 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '13' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '319' Execution took 0.0022 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '17' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '320' Execution took 0.0023 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '20' Execution took 0.0002 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0002 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '318' Execution took 0.0019 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '138' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '315' Execution took 0.0019 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '139' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0002 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '316' Execution took 0.0019 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.description FROM ingredients me WHERE ( me.id = ? ) Parameters: '140' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.name, me.unit FROM measurements me WHERE ( me.id = ? ) Parameters: '3' Execution took 0.0003 seconds. ------------------------------------------------------------------------------ Executing: SELECT me.id, me.recipe, me.quantity FROM quantities me WHERE ( me.recipe = ? ) Parameters: '317' Execution took 0.0018 seconds. ------------------------------------------------------------------------------

  • 26 queries total: 1 for beverage, 1 for recipe, 8 for ingredients, 8 for measurement, 8 for quantity
  • Naïve execution--not ideal!
  • Same thing using prefetch

    • my $beverage_rs = $model->resultset('Beverage')->search( {}, { prefetch => { recipes => [qw/quantity ingredient measurement/] } } ); my $beverage = $beverage_rs->find({name => 'vespers-martini'}); my $recipes = $beverage->recipes; while (my $recipe = $recipes->next) { my $ingredient = $recipes->ingredient->description; my $measurement = $recipes->measurement->unit; }

    Executing: SELECT me.id, me.name, me.title, me.instruction, recipes.id, recipes.beverage, recipes.ingredient, recipes.measurement, quantity.id, quantity.recipe, quantity.quantity, ingredient.id, ingredient.name, ingredient.description, measurement.id, measurement.name, measurement.unit FROM beverages me LEFT JOIN recipes recipes ON recipes.beverage = me.id LEFT JOIN quantities quantity ON quantity.recipe = recipes.id LEFT JOIN ingredients ingredient ON ingredient.id = recipes.ingredient LEFT JOIN measurements measurement ON measurement.id = recipes.measurement WHERE ( me.name = ? ) ORDER BY me.id Parameters: 'agent-orange' Execution took 0.0048 seconds. ------------------------------------------------------------------------------

  • 1 query gets everything at once
  • a little better
  • So, what is prefetch?

    • my $beverage_rs = $model->resultset('Beverage')->search( {}, { join => ['instruction',{recipes => 'ingredient'}] } );

  • prefetch basically join but keeps columns from joined tables

    • Executing: SELECT me.id, me.name, me.title, me.instruction FROM beverages me LEFT JOIN recipes recipes ON recipes.beverage = me.id WHERE ( me.name = ? ) Parameters: 'agent-orange' Execution took 0.0005 seconds.

    8. Other stuff

    Search clauses

  • DBIC query

    • my $filtered_beverages = $beverage_rs->search( { 'ingredient.name' => 'vodka' }, { distinct => 1, join => { recipes => 'ingredient' }, order_by => { -asc => [qw/ingredient.name/] } } );

    Aggregation functions

  • Add a price column to beverages and Beverage.pm

    • my $beverage_rs = $model->resultset('Beverage'); my $sum = $beverage_rs->get_column('price')->sum; my $sum = $beverage_rs->get_column('price')->max

    Pagination

  • Automatically jump to page offset of a ResultSet

    • my $rs = $model->resultset('Beverage')->search( {}, { rows => 10, page => 6} );

  • Get a Data::Page object for page navigation

    • my $pager = $rs->pager; my $current_page = $pager->current_page; my $first_page = $pager->first_page; my $next_page = $pager->next_page;

    Profiling SQL Queries

  • Automatically log all sql queries
  • Turn on DBIC logging

    • export DBIC_TRACE=1

  • Turn on logging and write to a file by default

    • export DBIC_TRACE="1=/path/to/logfile.log"

  • Override built-in DBIC profiler for own needs (i.e. pretty format SQL output).

    • package CocktailFu::SqlProfiler; use strict; use warnings; use parent qw(DBIx::Class::Storage::Statistics); use Time::HiRes qw/time/; my $start; sub query_start { my $self = shift; my $sql = shift; my @params = @_; my $params = join ', ' => @params; $sql =~ s/( (?:(?: LEFT|INNER )\s+)?JOIN)/\n $1/xgi; $sql =~ s/( SELECT | WHERE | FROM | (?:INSERT INTO) | (?: GROUP | ORDER )\s+BY )\s+ /$1\n /xgi; $sql =~ s/( FROM | WHERE | ORDER | LIMIT | OFFSET | GROUP | HAVING )/\n$1/xgi; $sql =~ s/(,|AND)\s/$1\n /xgi; $self->print( "\nExecuting:\n$sql\nParameters:\t" . $params . "\n" ); $start = time; } sub query_end { my $self = shift; my $sql = shift; my @params = @_; my $elapsed = sprintf "%0.4f", ( time - $start ); $self->print("Execution took $elapsed seconds.\n"); my $line = "-" x 78; $self->print( $line . "\n" ); $start = undef; } 1; __END__