| DBIx::Class::Manual::Features(3) | User Contributed Perl Documentation | DBIx::Class::Manual::Features(3) |
DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation
There are hundres of DBIC contributors listed in AUTHORS. That ranges from documentation help, to test help, to added features, to entire database support.
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 <https://metacpan.org/changes/distribution/DBIx-Class>.)
These are things that are in most other ORMs, but are still reasons to use DBIC over raw SQL.
The vast majority of code should run on all databases without needing tweaking
my $sth = $dbh->prepare('
INSERT INTO books
(title, author_id)
values (?,?)
');
$sth->execute( 'A book title', $author_id );
my $book = $book_rs->create({
title => 'A book title',
author_id => $author_id,
});
See "create" in DBIx::Class::ResultSet
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}";
}
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!
my $update = $dbh->prepare('
UPDATE books
SET title = ?
WHERE id = ?
');
$update->execute( 'New title', $book_id );
$book->update({ title => 'New title' });
See "update" in DBIx::Class::Row
Will not update unless value changes
my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');
$delete->execute($book_id);
$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;
Need a method to get a user's gravatar URL? Add a "gravatar_url" method to the Result class
These things may be in other ORM's, but they are very specific, so doubtful
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
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.
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
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
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.
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
$schema->resultset('Book')->good
$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.
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
$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.
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.
$book->date_published(DateTime->now); $book->update;
say $book->date_published->month_abbr; # Nov
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
my $rsc = $schema->resultset('Book')->get_column('price');
$rsc->first;
$rsc->all;
$rsc->min;
$rsc->max;
$rsc->sum;
See DBIx::Class::ResultSetColumn
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
$rs->search(undef, {
result_class => 'DBIx::Class::ResultClass::HashRefInflator',
});
See "result_class" in DBIx::Class::ResultSet and DBIx::Class::ResultClass::HashRefInflator.
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
$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
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.
| 2018-01-29 | perl v5.34.0 |