return $rs->all(); # all records for page 1
-The C<page> attribute does not have to be specified in your search:
-
- my $rs = $schema->resultset('Artist')->search(
- undef,
- {
- rows => 10,
- }
- );
-
- return $rs->page(1); # DBIx::Class::ResultSet containing first 10 records
-
-In either of the above cases, you can get a L<Data::Page> object for the
-resultset (suitable for use in e.g. a template) using the C<pager> method:
+You can get a L<Data::Page> object for the resultset (suitable for use
+in e.g. a template) using the C<pager> method:
return $rs->pager();
Say you want to run a complex custom query on your user data, here's what
you have to add to your User class:
- package My::Schema::User;
+ package My::Schema::Result::User;
use base qw/DBIx::Class/;
achieve the same with subclassing the resultset class and defining the
ResultSource there:
- package My::Schema::UserFriendsComplex;
+ package My::Schema::Result::UserFriendsComplex;
- use My::Schema::User;
- use base qw/My::Schema::User/;
+ use My::Schema::Result::User;
+ use base qw/My::Schema::Result::User/;
__PACKAGE__->table('dummy'); # currently must be called before anything else
=head2 SELECT DISTINCT with multiple columns
- my $rs = $schema->resultset('Foo')->search(
+ my $rs = $schema->resultset('Artist')->search(
{},
{
- select => [
- { distinct => [ $source->columns ] }
- ],
- as => [ $source->columns ] # remember 'as' is not the same as SQL AS :-)
+ columns => [ qw/artistid name rank/ ],
+ distinct => 1
+ }
+ );
+
+ my $rs = $schema->resultset('Artist')->search(
+ {},
+ {
+ columns => [ qw/artistid name rank/ ],
+ group_by => [ qw/artistid name rank/ ],
}
);
+ # Equivalent SQL:
+ # SELECT me.artistid, me.name, me.rank
+ # FROM artist me
+ # GROUP BY artistid, name, rank
+
=head2 SELECT COUNT(DISTINCT colname)
- my $rs = $schema->resultset('Foo')->search(
+ my $rs = $schema->resultset('Artist')->search(
{},
{
- select => [
- { count => { distinct => 'colname' } }
- ],
- as => [ 'count' ]
+ columns => [ qw/name/ ],
+ distinct => 1
+ }
+ );
+
+ my $rs = $schema->resultset('Artist')->search(
+ {},
+ {
+ columns => [ qw/name/ ],
+ group_by => [ qw/name/ ],
}
);
- my $count = $rs->next->get_column('count');
+ my $count = $rs->count;
+
+ # Equivalent SQL:
+ # SELECT COUNT( * ) FROM (SELECT me.name FROM artist me GROUP BY me.name) count_subq:
=head2 Grouping results
are in any way unsure about the use of the attributes above (C< join
>, C< select >, C< as > and C< group_by >).
-=head2 Subqueries
+=head2 Subqueries (EXPERIMENTAL)
You can write subqueries relatively easily in DBIC.
artist_id => $inside_rs->get_column('id')->as_query,
});
+=head3 Support
+
+Subqueries are supported in the where clause (first hashref), and in the
+from, select, and +select attributes.
+
=head3 Correlated subqueries
my $cdrs = $schema->resultset('CD');
WHERE artistid = me.artistid
)
-=head2 Where subqueries will work
+=head3 EXPERIMENTAL
-Currently, subqueries will B<only> work in the where-clause of a search. In
-other words, in the first hashref of a search() method. Work is being done
-to make them work as part of the second hashref (from, select, +select, etc).
+Please note that subqueries are considered an experimental feature.
=head2 Predefined searches
so no additional SQL statements are executed. You now have a much more
efficient query.
-Note that as of L<DBIx::Class> 0.05999_01, C<prefetch> I<can> be used with
-C<has_many> relationships.
-
Also note that C<prefetch> should only be used when you know you will
definitely use data from a related table. Pre-fetching related tables when you
only need columns from the main table will make performance worse!
=head2 Multi-step prefetch
-From 0.04999_05 onwards, C<prefetch> can be nested more than one relationship
+C<prefetch> can be nested more than one relationship
deep using the same syntax as a multi-step join:
my $rs = $schema->resultset('Tag')->search(
AKA getting last_insert_id
-If you are using PK::Auto (which is a core component as of 0.07), this is
-straightforward:
+Thanks to the core component PK::Auto, this is straightforward:
my $foo = $rs->create(\%blah);
# do more stuff
module.
To make an object stringify itself as a single column, use something
-like this (replace C<foo> with the column/method of your choice):
+like this (replace C<name> with the column/method of your choice):
use overload '""' => sub { shift->name}, fallback => 1;
# do whatever else you wanted if it was a new row
}
+=head2 Static sub-classing DBIx::Class result classes
+
+AKA adding additional relationships/methods/etc. to a model for a
+specific usage of the (shared) model.
+
+B<Schema definition>
+
+ package My::App::Schema;
+
+ use base DBIx::Class::Schema;
+
+ # load subclassed classes from My::App::Schema::Result/ResultSet
+ __PACKAGE__->load_namespaces;
+
+ # load classes from shared model
+ load_classes({
+ 'My::Shared::Model::Result' => [qw/
+ Foo
+ Bar
+ /]});
+
+ 1;
+
+B<Result-Subclass definition>
+
+ package My::App::Schema::Result::Baz;
+
+ use strict;
+ use warnings;
+ use base My::Shared::Model::Result::Baz;
+
+ # WARNING: Make sure you call table() again in your subclass,
+ # otherwise DBIx::Class::ResultSourceProxy::Table will not be called
+ # and the class name is not correctly registered as a source
+ __PACKAGE__->table('baz');
+
+ sub additional_method {
+ return "I'm an additional method only needed by this app";
+ }
+
+ 1;
+
=head2 Dynamic Sub-classing DBIx::Class proxy classes
AKA multi-class object inflation from one table
B<Schema Definition>
- package DB::Schema;
+ package My::Schema;
use base qw/DBIx::Class::Schema/;
- __PACKAGE__->load_classes(qw/User/);
+ __PACKAGE__->load_namespaces;
+
+ 1;
B<Proxy-Class definitions>
- package DB::Schema::User;
+ package My::Schema::Result::User;
use strict;
use warnings;
print "I am a regular user.\n";
return ;
}
+
+ 1;
+
-
- package DB::Schema::User::Admin;
+ package My::Schema::Result::User::Admin;
use strict;
use warnings;
- use base qw/DB::Schema::User/;
+ use base qw/My::Schema::Result::User/;
sub hello
{
{
print "I am doing admin stuff\n";
return ;
- }
+ }
+
+ 1;
B<Test File> test.pl
use warnings;
use strict;
- use DB::Schema;
+ use My::Schema;
my $user_data = { email => 'someguy@place.com',
password => 'pass1',
password => 'pass2',
admin => 1 };
- my $schema = DB::Schema->connection('dbi:Pg:dbname=test');
+ my $schema = My::Schema->connection('dbi:Pg:dbname=test');
$schema->resultset('User')->create( $user_data );
$schema->resultset('User')->create( $admin_data );
Wasn't that easy?
+Beware, changing the Result class using
+L<DBIx::Class::ResultSet/result_class> will replace any existing class
+completely including any special components loaded using
+load_components, eg L<DBIx::Class::InflateColumn::DateTime>.
+
=head2 Get raw data for blindingly fast results
If the L<HashRefInflator|DBIx::Class::ResultClass::HashRefInflator> solution
above is not fast enough for you, you can use a DBIx::Class to return values
-exactly as they come out of the data base with none of the convenience methods
+exactly as they come out of the database with none of the convenience methods
wrapped round them.
This is used like so:
}
You will need to map the array offsets to particular columns (you can
-use the I<select> attribute of C<search()> to force ordering).
+use the L<DBIx::Class::ResultSet/select> attribute of L<DBIx::Class::ResultSet/search> to force ordering).
=head1 RESULTSET OPERATIONS
=head2 Getting Schema from a ResultSet
-To get the schema object from a result set, do the following:
+To get the L<DBIx::Class::Schema> object from a ResultSet, do the following:
$rs->result_source->schema
$rs = $user->addresses(); # get all addresses for a user
$rs = $address->users(); # get all users for an address
+=head2 Relationships across DB schemas
+
+Mapping relationships across L<DB schemas|DBIx::Class::Manual::Glossary/DB schema>
+is easy as long as the schemas themselves are all accessible via the same DBI
+connection. In most cases, this means that they are on the same database host
+as each other and your connecting database user has the proper permissions to them.
+
+To accomplish this one only needs to specify the DB schema name in the table
+declaration, like so...
+
+ package MyDatabase::Main::Artist;
+ use base qw/DBIx::Class/;
+ __PACKAGE__->load_components(qw/PK::Auto Core/);
+
+ __PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
+
+ __PACKAGE__->add_columns(qw/ artistid name /);
+ __PACKAGE__->set_primary_key('artistid');
+ __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
+
+ 1;
+
+Whatever string you specify there will be used to build the "FROM" clause in SQL
+queries.
+
+The big drawback to this is you now have DB schema names hardcoded in your
+class files. This becomes especially troublesome if you have multiple instances
+of your application to support a change lifecycle (e.g. DEV, TEST, PROD) and
+the DB schemas are named based on the environment (e.g. database1_dev).
+
+However, one can dynamically "map" to the proper DB schema by overriding the
+L<connection|DBIx::Class::Schama/connection> method in your Schema class and
+building a renaming facility, like so:
+
+ package MyDatabase::Schema;
+ use Moose;
+
+ extends 'DBIx::Class::Schema';
+
+ around connection => sub {
+ my ( $inner, $self, $dsn, $username, $pass, $attr ) = ( shift, @_ );
+
+ my $postfix = delete $attr->{schema_name_postfix};
+
+ $inner->(@_);
+
+ if ( $postfix ) {
+ $self->append_db_name($postfix);
+ }
+ };
+
+ sub append_db_name {
+ my ( $self, $postfix ) = @_;
+
+ my @sources_with_db
+ = grep
+ { $_->name =~ /^\w+\./mx }
+ map
+ { $self->source($_) }
+ $self->sources;
+
+ foreach my $source (@sources_with_db) {
+ my $name = $source->name;
+ $name =~ s{^(\w+)\.}{${1}${postfix}\.}mx;
+
+ $source->name($name);
+ }
+ }
+
+ 1;
+
+By overridding the L<connection|DBIx::Class::Schama/connection>
+method and extracting a custom option from the provided \%attr hashref one can
+then simply iterate over all the Schema's ResultSources, renaming them as
+needed.
+
+To use this facility, simply add or modify the \%attr hashref that is passed to
+L<connection|DBIx::Class::Schama/connect>, as follows:
+
+ my $schema
+ = MyDatabase::Schema->connect(
+ $dsn,
+ $user,
+ $pass,
+ {
+ schema_name_postfix => '_dev'
+ # ... Other options as desired ...
+ })
+
+Obviously, one could accomplish even more advanced mapping via a hash map or a
+callback routine.
+
=head1 TRANSACTIONS
As of version 0.04001, there is improved transaction support in
while (my @vals = $cursor->next) {
print $vals[0]."\n";
}
+
+In case you're going to use this "trick" together with L<DBIx::Class::Schema/deploy> or
+L<DBIx::Class::Schema/create_ddl_dir> a table called "dual" will be created in your
+current schema. This would overlap "sys.dual" and you could not fetch "sysdate" or
+"sequence.nextval" anymore from dual. To avoid this problem, just tell
+L<SQL::Translator> to not create table dual:
+
+ my $sqlt_args = {
+ add_drop_table => 1,
+ parser_args => { sources => [ grep $_ ne 'Dual', schema->sources ] },
+ };
+ $schema->create_ddl_dir( [qw/Oracle/], undef, './sql', undef, $sqlt_args );
Or use L<DBIx::Class::ResultClass::HashRefInflator>
L<callback system|DBIx::Class::ResultSource/sqlt_deploy_callback> if you wish
to share a hook between multiple sources):
- package My::Schema::Artist;
+ package My::Schema::Result::Artist;
__PACKAGE__->table('artist');
__PACKAGE__->add_columns(id => { ... }, name => { ... })
=head2 Working with PostgreSQL array types
-If your SQL::Abstract version (>= 1.50) supports it, you can assign to
-PostgreSQL array values by passing array references in the C<\%columns>
-(C<\%vals>) hashref of the L<DBIx::Class::ResultSet/create> and
-L<DBIx::Class::Row/update> family of methods:
+You can also assign values to PostgreSQL array columns by passing array
+references in the C<\%columns> (C<\%vals>) hashref of the
+L<DBIx::Class::ResultSet/create> and L<DBIx::Class::Row/update> family of
+methods:
$resultset->create({
numbers => [1, 2, 3]
$resultset->search(
{
- numbers => \[ '= ?', [1, 2, 3] ]
+ numbers => \[ '= ?', [numbers => [1, 2, 3]] ]
}
);
See L<SQL::Abstract/array_datatypes> and L<SQL::Abstract/Literal SQL with
-placeholders and bind values (subqueries)> for more explanation.
+placeholders and bind values (subqueries)> for more explanation. Note that
+L<DBIx::Class> sets L<SQL::Abstract/bindtype> to C<columns>, so you must pass
+the bind values (the C<[1, 2, 3]> arrayref in the above example) wrapped in
+arrayrefs together with the column name, like this: C<< [column_name => value]
+>>.
=head1 BOOTSTRAPPING/MIGRATING
syntax to load the appropriate classes there is not a direct alternative
avoiding L<Module::Find|Module::Find>.
+=head1 MEMORY USAGE
+
+=head2 Cached statements
+
+L<DBIx::Class> normally caches all statements with L<< prepare_cached()|DBI/prepare_cached >>.
+This is normally a good idea, but if too many statements are cached, the database may use too much
+memory and may eventually run out and fail entirely. If you suspect this may be the case, you may want
+to examine DBI's L<< CachedKids|DBI/CachedKidsCachedKids_(hash_ref) >> hash:
+
+ # print all currently cached prepared statements
+ print for keys %{$schema->storage->dbh->{CachedKids}};
+ # get a count of currently cached prepared statements
+ my $count = scalar keys %{$schema->storage->dbh->{CachedKids}};
+
+If it's appropriate, you can simply clear these statements, automatically deallocating them in the
+database:
+
+ my $kids = $schema->storage->dbh->{CachedKids};
+ delete @{$kids}{keys %$kids} if scalar keys %$kids > 100;
+
+But what you probably want is to expire unused statements and not those that are used frequently.
+You can accomplish this with L<Tie::Cache> or L<Tie::Cache::LRU>:
+
+ use Tie::Cache;
+ use DB::Main;
+ my $schema = DB::Main->connect($dbi_dsn, $user, $pass, {
+ on_connect_do => sub { tie %{shift->_dbh->{CachedKids}}, 'Tie::Cache', 100 },
+ });
+
=cut