DBIx::C...Features(3)

📦
version 0.082844 · 7 versions
, in opensuse-leap 16.0

✨ DBIx::Class::Manual::Features ✨

okay so bestie, you're looking at the DBIx::Class::Manual::Features page from the User Contributed Perl Documentation 💅 — and it is literally a boatload of DBIx::Class features with links to the respective documentation. This is part 1, more is coming, so don't go anywhere. 🫶

🌸 NAME 🌸

DBIx::Class::Manual::Features — A boatload of DBIx::Class features with links to respective documentation. 💖

🌈 META 🌈

👑 Large Community 👑

There are literally hundres of DBIC contributors listed in AUTHORS (yes, the spelling stays, bestie 😭). That ranges from documentation help, to test help, to added features, to entire database support. iconic.

🦋 Active Community 🦋

Currently (June 9, 2010) there are 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 (https://metacpan.org/changes/distribution/DBIx-Class).) 😭

🫶 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. no cap.

🦋 Cross DB 🦋

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

🎀 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 💅

TMTOWTDI! 🔥

🎀 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.

        my $sth = $dbh->prepare('
          SELECT title,
          authors.name as author_name
          FROM books
          WHERE books.name LIKE "%monte cristo%" AND
          books.topic = "jailbreak"
        ');
        my $book = $book_rs->search({
           'me.name'  => { -like => '%monte cristo%' },
           'me.topic' => 'jailbreak',
        })->next;

See SQL::Abstract::Classic, "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 🌈

Okay so these are the extra-spicy DBIC-specific bits — other ORMs might have some of this, but it's very specific, so honestly? doubtful. 💅

🚀 ->deploy 🚀

Create a whole database from your DBIx::Class schema. Iconic.

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

$schema->deploy

See "deploy" in DBIx::Class::Schema.

See also: DBIx::Class::DeploymentHandler.

🌸 Schema::Loader 🌸

Now the reverse, bestie: 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 into the database super quickly.

$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. Slay.

🎀 Multicreate 🎀

Create an object and its related objects all at once. It's giving main character energy.

$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. Obsessed.

💜 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
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. Cute.

🫶 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 three 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", # DON'T DO THIS
});

Better:

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

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

Bestie, that first one is literally marked as a SQL injection vector in the code comment. Do not do the thing. 💀

💖 FURTHER QUESTIONS? 💖

Check the list of additional DBIC resources.

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.

And that's a wrap, bestie — source/version perl v5.42.0, dated 2025-01-22. Byeeee 💋✨

📦 where this page comes from