DBIx::Class::Manual::Features(3)			User Contributed Perl Documentation			  DBIx::Class::Manual::Features(3)

NAME

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

META

   Large Community
       Currently there are 88 people listed as contributors to DBIC.  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
", $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

   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::ClassResultSet, 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 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

perl v5.18.2							    2014-01-05					  DBIx::Class::Manual::Features(3)