GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  DBIX::CLASS::MANUAL::FEATURES (3)

.ds Aq ’

NAME

DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation

CONTENTS

META

    Large Community

There are hundres of DBIC contributors listed in AUTHORS. That ranges from documentation help, to test help, to added features, to entire database support.

    Active Community

Currently (June 9, 2010) 6 active branches (committed to in the last two weeks) in git. Last release (0.08122) had 14 new features, and 16 bug fixes. Of course that ebbs and flows <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=blob;f=Changes>.)

    Responsive Community

I needed MSSQL order-by support; the community helped me add support
generally very welcoming of people willing to help

General ORM

These are things that are in most other ORMs, but are still reasons to use DBIC over raw SQL.

    Cross DB

The vast majority of code should run on all databases without needing tweaking

    Basic CRUD

C - Create
R - Retrieve
U - Update
D - Delete

    SQL: Create



 my $sth = $dbh->prepare(
    INSERT INTO books
    (title, author_id)
    values (?,?)
 );

 $sth->execute( A book title, $author_id );



    DBIC: Create



 my $book = $book_rs->create({
    title     => A book title,
    author_id => $author_id,
 });



See create in DBIx::Class::ResultSet
No need to pair placeholders and values
Automatically gets autoincremented id for you
Transparently uses INSERT ... RETURNING for databases that support it

    SQL: Read



 my $sth = $dbh->prepare(
    SELECT title,
    authors.name as author_name
    FROM books, authors
    WHERE books.author = authors.id
 );

 while ( my $book = $sth->fetchrow_hashref ) {
   say "Author of $book->{title} is $book->{author_name}";
 }



    DBIC: Read



 my $book = $book_rs->find($book_id);



or



 my $book = $book_rs->search({ title => A book title }, { rows => 1 })->next;



or



 my @books = $book_rs->search({ author => $author_id })->all;



or



 while( my $book = $books_rs->next ) {
   printf "Author of %s is %s\n", $book->title, $book->author->name;
 }



See find in DBIx::Class::ResultSet, search in DBIx::Class::ResultSet, next in DBIx::Class::ResultSet, and all in DBIx::Class::ResultSet

<B>TMTOWTDI!B>

    SQL: Update



 my $update = $dbh->prepare(
    UPDATE books
    SET title = ?
    WHERE id = ?
 );

 $update->execute( New title, $book_id );



    DBIC: Update



 $book->update({ title => New title });



See update in DBIx::Class::Row

Will not update unless value changes

    SQL: Delete



 my $delete = $dbh->prepare(DELETE FROM books WHERE id = ?);

 $delete->execute($book_id);



    DBIC: Delete



 $book->delete



See delete in DBIx::Class::Row

    SQL: Search



 my $sth = $dbh->prepare(
   SELECT title,
   authors.name as author_name
   FROM books
   WHERE books.name LIKE "%monte cristo%" AND
   books.topic = "jailbreak"
 );



    DBIC: Search



 my $book = $book_rs->search({
    me.name  => { -like => %monte cristo% },
    me.topic => jailbreak,
 })->next;



See SQL::Abstract, ‘‘next’’ in DBIx::Class::ResultSet, and ‘‘search’’ in DBIx::Class::ResultSet
(kinda) introspectible
Prettier than SQL

    OO Overridability

Override new if you want to do validation
Override delete if you want to disable deletion
and on and on

    Convenience Methods

‘‘find_or_create’’ in DBIx::Class::ResultSet
‘‘update_or_create’’ in DBIx::Class::ResultSet

    Non-column methods

Need a method to get a user’s gravatar URL? Add a gravatar_url method to the Result class

    RELATIONSHIPS

‘‘belongs_to’’ in DBIx::Class::Relationship
‘‘has_many’’ in DBIx::Class::Relationship
‘‘might_have’’ in DBIx::Class::Relationship
‘‘has_one’’ in DBIx::Class::Relationship
‘‘many_to_many’’ in DBIx::Class::Relationship
SET AND FORGET

DBIx::Class Specific Features

These things may be in other ORM’s, but they are very specific, so doubtful

    ->deploy

Create a database from your DBIx::Class schema.



 my $schema = Frew::Schema->connect( $dsn, $user, $pass );

 $schema->deploy



See deploy in DBIx::Class::Schema.

See also: DBIx::Class::DeploymentHandler

    Schema::Loader

Create a DBIx::Class schema from your database.



 package Frew::Schema;

 use strict;
 use warnings;

 use base DBIx::Class::Schema::Loader;

 __PACKAGE__->loader_options({
    naming => v7,
    debug  => $ENV{DBIC_TRACE},
 });

 1;

 # elsewhere...

 my $schema = Frew::Schema->connect( $dsn, $user, $pass );



See DBIx::Class::Schema::Loader and CONSTRUCTOR OPTIONS in DBIx::Class::Schema::Loader::Base.

    Populate

Made for inserting lots of rows very quickly into database



 $schema->populate([ Users =>
    [qw( username password )],
    [qw( frew     >=4char$ )],
    [qw(      ...          )],
    [qw(      ...          )],
 );



See populate in DBIx::Class::Schema

I use populate here <http://blog.afoolishmanifesto.com/archives/1255> to export our whole (200M~) db to SQLite

    Multicreate

Create an object and its related objects all at once



 $schema->resultset(Author)->create({
    name => Stephen King,
    books => [{ title => The Dark Tower }],
    address => {
       street => 123 Turtle Back Lane,
       state  => { abbreviation => ME },
       city   => { name => Lowell     },
    },
 });



See create in DBIx::Class::ResultSet
books is a has_many
address is a belongs_to which in turn belongs to state and city each
for this to work right state and city must mark abbreviation and name as unique

    Extensible

DBIx::Class helped pioneer fast MI in Perl 5 with Class::C3, so it is made to allow extensions to nearly every part of it.

    Extensibility example: DBIx::Class::Helpers

DBIx::Class::Helper::ResultSet::IgnoreWantarray
DBIx::Class::Helper::ResultSet::Random
DBIx::Class::Helper::ResultSet::SetOperations
DBIx::Class::Helper::Row::JoinTable
DBIx::Class::Helper::Row::NumifyGet
DBIx::Class::Helper::Row::SubClass
DBIx::Class::Helper::Row::ToJSON
DBIx::Class::Helper::Row::StorageValues
DBIx::Class::Helper::Row::OnColumnChange

    Extensibility example: DBIx::Class::TimeStamp

See DBIx::Class::TimeStamp
Cross DB
set_on_create
set_on_update

    Extensibility example: Kioku

See DBIx::Class::Schema::KiokuDB
Kioku is the new hotness
Mix RDBMS with Object DB

    Result vs ResultSet

Result == Row
ResultSet == Query Plan
Internal Join Optimizer for all DB’s (!!!)
(less important but...)
ResultSource == Queryable collection of rows (Table, View, etc)
Storage == Database
Schema == associates a set of ResultSources with a Storage

    ResultSet methods



 package MyApp::Schema::ResultSet::Book;

 use strict;
 use warnings;

 use base DBIx::Class::ResultSet;

 sub good {
    my $self = shift;
    $self->search({
       $self->current_source_alias . .rating => { >= => 4 }
    })
 };

 sub cheap {
    my $self = shift;
    $self->search({
       $self->current_source_alias . .price => { <= => 5}
    })
 };

 # ...

 1;



See Predefined searches in DBIx::Class::Manual::Cookbook
All searches should be ResultSet methods
Name has obvious meaning
‘‘current_source_alias’’ in DBIx::Class::ResultSet helps things to work no matter what

    ResultSet method in Action



 $schema->resultset(Book)->good



    ResultSet Chaining



 $schema->resultset(Book)
    ->good
    ->cheap
    ->recent



    search_related



 my $score = $schema->resultset(User)
    ->search({me.userid => frew})
    ->related_resultset(access)
    ->related_resultset(mgmt)
    ->related_resultset(orders)
    ->telephone
    ->search_related( shops => {
       shops.datecompleted => {
          -between => [2009-10-01,2009-10-08]
       }
    })->completed
    ->related_resultset(rpt_score)
    ->search(undef, { rows => 1})
    ->get_column(raw_scores)
    ->next;



The SQL that this produces (with placeholders filled in for clarity’s sake) on our system (Microsoft SQL) is:



 SELECT raw_scores
   FROM (
     SELECT raw_scores, ROW_NUMBER() OVER (
         ORDER BY (
             SELECT (1)
           )
       ) AS rno__row__index
       FROM (
         SELECT rpt_score.raw_scores
           FROM users me
           JOIN access access
             ON access.userid = me.userid
           JOIN mgmt mgmt
             ON mgmt.mgmtid = access.mgmtid
           JOIN [order] orders
             ON orders.mgmtid = mgmt.mgmtid
           JOIN shop shops
             ON shops.orderno = orders.orderno
           JOIN rpt_scores rpt_score
             ON rpt_score.shopno = shops.shopno
         WHERE (
           datecompleted IS NOT NULL AND
           (
             (shops.datecompleted BETWEEN 2009-10-01 AND 2009-10-08)  AND
             (type = 1 AND me.userid = frew)
           )
         )
       ) rpt_score
   ) rpt_score
 WHERE rno__row__index BETWEEN 1 AND 1



See: related_resultset in DBIx::Class::ResultSet, search_related in DBIx::Class::ResultSet, and get_column in DBIx::Class::ResultSet.

    bonus rel methods



 my $book = $author->create_related(
    books => {
       title => Another Discworld book,
    }
 );

 my $book2 = $pratchett->add_to_books({
    title => MOAR Discworld book,
 });



See create_related in DBIx::Class::Relationship::Base and add_to_$rel in DBIx::Class::Relationship::Base

Note that it automatically fills in foreign key for you

    Excellent Transaction Support



 $schema->txn_do(sub {
    ...
 });

 $schema->txn_begin; # <-- low level
 # ...
 $schema->txn_commit;



See txn_do in DBIx::Class::Schema, txn_begin in DBIx::Class::Schema, and txn_commit in DBIx::Class::Schema.

    InflateColumn



 package Frew::Schema::Result::Book;

 use strict;
 use warnings;

 use base DBIx::Class::Core;

 use DateTime::Format::MySQL;

 # Result code here

 __PACKAGE__->load_components(InflateColumn);

 __PACKAGE__->inflate_column(
    date_published => {
       inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
       deflate => sub { shift->ymd },
    },
 );



See DBIx::Class::InflateColumn, inflate_column in DBIx::Class::InflateColumn, and DBIx::Class::InflateColumn::DateTime.

    InflateColumn: deflation



 $book->date_published(DateTime->now);
 $book->update;



    InflateColumn: inflation



 say $book->date_published->month_abbr; # Nov



    FilterColumn



 package Frew::Schema::Result::Book;

 use strict;
 use warnings;

 use base DBIx::Class::Core;

 # Result code here

 __PACKAGE__->load_components(FilterColumn);

 __PACKAGE__->filter_column(
    length => {
       to_storage   => to_metric,
       from_storage => to_imperial,
    },
 );

 sub to_metric   { $_[1] * .305 }
 sub to_imperial { $_[1] * 3.28 }



See DBIx::Class::FilterColumn and filter_column in DBIx::Class::FilterColumn

    ResultSetColumn



 my $rsc = $schema->resultset(Book)->get_column(price);
 $rsc->first;
 $rsc->all;
 $rsc->min;
 $rsc->max;
 $rsc->sum;



See DBIx::Class::ResultSetColumn

    Aggregates



 my @res = $rs->search(undef, {
    select   => [
       price,
       genre,
       { max => price },
       { avg => price },
    ],
    as       => [
       qw(price genre max_price avg_price)
    ],
    group_by => [qw(price genre)],
 });
 for (@res) {
    say $_->price .   . $_->genre;
    say $_->get_column(max_price);
    say $_->get_column(avg_price);
 }



See select in DBIx::Class::ResultSet, as in DBIx::Class::ResultSet, and group_by in DBIx::Class::ResultSet
Careful, get_column can basically mean <B>threeB> things
private in which case you should use an accessor
public for what there is no accessor for
public for get resultset column (prev example)

    HRI



 $rs->search(undef, {
   result_class => DBIx::Class::ResultClass::HashRefInflator,
 });



See result_class in DBIx::Class::ResultSet and DBIx::Class::ResultClass::HashRefInflator.
Easy on memory
Mega fast
Great for quick debugging
Great for performance tuning (we went from 2m to < 3s)

    Subquery Support



 my $inner_query = $schema->resultset(Artist)
    ->search({
     name => [ Billy Joel, Brittany Spears ],
 })->get_column(id)->as_query;

 my $rs = $schema->resultset(CD)->search({
     artist_id => { -in => $inner_query },
 });



See Subqueries in DBIx::Class::Manual::Cookbook

    Bare SQL w/ Placeholders



 $rs->update({
    # !!! SQL INJECTION VECTOR
    price => \"price + $inc", # DONT DO THIS
 });



Better:



 $rs->update({
    price => \[price + ?, [inc => $inc]],
 });



See Literal SQL with placeholders and bind values (subqueries) in SQL::Abstract

FURTHER QUESTIONS?

Check the list of additional DBIC resources.

COPYRIGHT AND LICENSE

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.
Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 DBIX::CLASS::MANUAL::FEATURES (3) 2016-02-09

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.