__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
- # ->table, ->add_columns, etc.
+ # For the time being this is necessary even for virtual views
+ __PACKAGE__->table($view_name);
+
+ #
+ # ->add_columns, etc.
+ #
# do not attempt to deploy() this view
__PACKAGE__->result_source_instance->is_virtual(1);
);
... and you'll get back a perfect L<DBIx::Class::ResultSet> (except, of course,
-that you cannot modify the rows it contains, e.g. cannot call L</update>,
-L</delete>, ... on it).
+that you cannot modify the rows it contains, e.g. cannot call
+L<update|DBIx::Class::ResultSet/update> or
+L<delete|DBIx::Class::ResultSet/delete> on it).
Note that you cannot have bind parameters unless is_virtual is set to true.
});
my $rs = $schema->resultset('CD')->search({
- artist_id => { 'IN' => $inside_rs->get_column('id')->as_query },
+ artist_id => { -in => $inside_rs->get_column('id')->as_query },
});
-The usual operators ( =, !=, IN, NOT IN, etc.) are supported.
+The usual operators ( '=', '!=', -in, -not_in, etc.) are supported.
B<NOTE>: You have to explicitly use '=' when doing an equality comparison.
The following will B<not> work:
my $rs = $cdrs->search({
year => {
'=' => $cdrs->search(
- { artist_id => { '=' => { -ident => 'me.artist_id' } } },
- { alias => 'inner' }
+ { artist_id => { -ident => 'me.artist_id' } },
+ { alias => 'sub_query' }
)->get_column('year')->max_rs->as_query,
},
});
SELECT me.cdid, me.artist, me.title, me.year, me.genreid, me.single_track
FROM cd me
- WHERE year = (
- SELECT MAX(inner.year)
- FROM cd inner
- WHERE artist_id = me.artist_id
- )
+ WHERE year = (
+ SELECT MAX(sub_query.year)
+ FROM cd sub_query
+ WHERE artist_id = me.artist_id
+ )
=head2 Predefined searches
it can be accomplished with C<DBIx::Class> when necessary by resorting to
literal SQL:
- $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
+ $rs->search(
+ \[ 'YEAR(date_of_birth) = ?', 1979 ]
+ );
# Equivalent SQL:
# SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
+To include the function as part of a larger search, use the '-and' keyword
+to collect the search conditions:
+
$rs->search({ -and => [
name => 'Bob',
- \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
+ \[ 'YEAR(date_of_birth) = ?', 1979 ]
]});
# Equivalent SQL:
# SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
-Note: the C<plain_value> string in the C<< [ plain_value => 1979 ] >> part
-should be either the same as the name of the column (do this if the type of the
-return value of the function is the same as the type of the column) or in the
-case of a function it's currently treated as a dummy string (it is a good idea
-to use C<plain_value> or something similar to convey intent). The value is
-currently only significant when handling special column types (BLOBs, arrays,
-etc.), but this may change in the future.
+Note: the syntax for specifying the bind value's datatype and value is
+explained in L<DBIx::Class::ResultSet/DBIC BIND VALUES>.
See also L<SQL::Abstract/Literal SQL with placeholders and bind values
(subqueries)>.
+=head2 Software Limits
+
+When your RDBMS does not have a working SQL limit mechanism (e.g. Sybase ASE)
+and L<GenericSubQ|DBIx::Class::SQLMaker::LimitDialects/GenericSubQ> is either
+too slow or does not work at all, you can try the
+L<software_limit|DBIx::Class::ResultSet/software_limit>
+L<DBIx::Class::ResultSet> attribute, which skips over records to simulate limits
+in the Perl layer.
+
+For example:
+
+ my $paged_rs = $rs->search({}, {
+ rows => 25,
+ page => 3,
+ order_by => [ 'me.last_name' ],
+ software_limit => 1,
+ });
+
+You can set it as a default for your schema by placing the following in your
+C<Schema.pm>:
+
+ __PACKAGE__->default_resultset_attributes({ software_limit => 1 });
+
+B<WARNING:> If you are dealing with large resultsets and your L<DBI> or
+ODBC/ADO driver does not have proper cursor support (i.e. it loads the whole
+resultset into memory) then this feature will be extremely slow and use huge
+amounts of memory at best, and may cause your process to run out of memory and
+cause instability on your server at worst, beware!
+
=head1 JOINS AND PREFETCHING
=head2 Using joins and prefetch
# SELECT cd.*, artist.*, liner_notes.* FROM cd
# JOIN artist ON cd.artist = artist.id
# JOIN liner_notes ON cd.id = liner_notes.cd
- # WHERE artist.name = 'Bob Marley'
+ # WHERE artist.name = 'Bob Marley' AND liner_notes.notes LIKE '%some text%'
# ORDER BY artist.name
=head2 Multi-step joins
=head1 ROW-LEVEL OPERATIONS
-=head2 Retrieving a row object's Schema
+=head2 Retrieving a result object's Schema
-It is possible to get a Schema object from a row object like so:
+It is possible to get a Schema object from a result object like so:
my $schema = $cd->result_source->schema;
# use the schema as normal:
L<DBIx::Class> classes are proxy classes, therefore some different
techniques need to be employed for more than basic subclassing. In
this example we have a single user table that carries a boolean bit
-for admin. We would like like to give the admin users
+for admin. We would like to give the admin users
objects (L<DBIx::Class::Row>) the same methods as a regular user but
also special admin only methods. It doesn't make sense to create two
separate proxy-class files for this. We would be copying all the user
Alternatively you can use L<DBIx::Class::DynamicSubclass> that implements
exactly the above functionality.
-=head2 Skip row object creation for faster results
+=head2 Skip result object creation for faster results
DBIx::Class is not built for speed, it's built for convenience and
ease of use, but sometimes you just need to get the data, and skip the
=head2 Creating a result set from a set of rows
-Sometimes you have a (set of) row objects that you want to put into a
+Sometimes you have a (set of) result objects that you want to put into a
resultset without the need to hit the DB again. You can do that by using the
-L<set_cache|DBIx::Class::Resultset/set_cache> method:
+L<set_cache|DBIx::Class::ResultSet/set_cache> method:
my @uploadable_groups;
while (my $group = $groups->next) {
=head2 Filtering a relationship result set
-If you want to get a filtered result set, you can just add add to $attr as follows:
+If you want to get a filtered result set, you can just add to $attr as follows:
__PACKAGE__->has_many('pages' => 'Page', 'book', { where => { scrap => 0 } } );
-=head2 Many-to-many relationships
+=head2 Many-to-many relationship bridges
This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
To accomplish this one only needs to specify the DB schema name in the table
declaration, like so...
- package MyDatabase::Main::Artist;
+ package MyApp::Schema::Result::Artist;
use base qw/DBIx::Class::Core/;
__PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
__PACKAGE__->add_columns(qw/ artist_id name /);
__PACKAGE__->set_primary_key('artist_id');
- __PACKAGE__->has_many('cds' => 'MyDatabase::Main::Cd');
+ __PACKAGE__->has_many('cds' => 'MyApp::Schema::Result::Cd');
1;
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
+L<connection|DBIx::Class::Schema/connection> method in your Schema class and
building a renaming facility, like so:
- package MyDatabase::Schema;
+ package MyApp::Schema;
use Moose;
extends 'DBIx::Class::Schema';
1;
-By overridding the L<connection|DBIx::Class::Schama/connection>
+By overriding the L<connection|DBIx::Class::Schema/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:
+L<connection|DBIx::Class::Schema/connect>, as follows:
my $schema
- = MyDatabase::Schema->connect(
+ = MyApp::Schema->connect(
$dsn,
$user,
$pass,
# Abort the whole job
if ($_ =~ /horrible_problem/) {
- print "something horrible happend, aborting job!";
+ print "something horrible happened, aborting job!";
die $_; # rethrow error
}
});
} catch {
$exception = $_;
- }
+ };
- if ($caught) {
+ if ($exception) {
# There was an error while handling the $job. Rollback all changes
# since the transaction started, including the already committed
# ('released') savepoints. There will be neither a new $job nor any
}
In this example it might be hard to see where the rollbacks, releases and
-commits are happening, but it works just the same as for plain L<<txn_do>>: If
-the C<try>-block around C<txn_do> fails, a rollback is issued. If the C<try>
-succeeds, the transaction is committed (or the savepoint released).
+commits are happening, but it works just the same as for plain
+L<txn_do|DBIx::Class::Storage/txn_do>: If the L<try|Try::Tiny/try>-block
+around L<txn_do|DBIx::Class::Storage/txn_do> fails, a rollback is issued.
+If the L<try|Try::Tiny/try> succeeds, the transaction is committed
+(or the savepoint released).
While you can get more fine-grained control using C<svp_begin>, C<svp_release>
and C<svp_rollback>, it is strongly recommended to use C<txn_do> with coderefs.
numbers => [1, 2, 3]
});
- $row->update(
+ $result->update(
{
numbers => [1, 2, 3]
},
=head2 Formatting DateTime objects in queries
To ensure C<WHERE> conditions containing L<DateTime> arguments are properly
-formatted to be understood by your RDBMS, you must use the C<DateTime>
+formatted to be understood by your RDBMS, you must use the L<DateTime>
formatter returned by L<DBIx::Class::Storage::DBI/datetime_parser> to format
any L<DateTime> objects you pass to L<search|DBIx::Class::ResultSet/search>
conditions. Any L<Storage|DBIx::Class::Storage> object attached to your
-L<Schema|DBIx::Class::Schema> provides a correct C<DateTime> formatter, so
+L<Schema|DBIx::Class::Schema> provides a correct L<DateTime> formatter, so
all you have to do is:
my $dtf = $schema->storage->datetime_parser;
C<DateTime> object, which almost never matches the RDBMS expectations.
This kludge is necessary only for conditions passed to
-L<DBIx::Class::ResultSet/search>, whereas
-L<create|DBIx::Class::ResultSet/create>,
-L<find|DBIx::Class::ResultSet/find>,
-L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are all
+L<search|DBIx::Class::ResultSet/search> and L<DBIx::Class::ResultSet/find>,
+whereas L<create|DBIx::Class::ResultSet/create> and
+L<DBIx::Class::Row/update> (but not L<DBIx::Class::ResultSet/update>) are
L<DBIx::Class::InflateColumn>-aware and will do the right thing when supplied
-an inflated C<DateTime> object.
+an inflated L<DateTime> object.
=head2 Using Unicode
=head3 Oracle
Information about Oracle support for unicode can be found in
-L<DBD::Oracle/Unicode>.
+L<DBD::Oracle/UNICODE>.
=head3 PostgreSQL
For example, say that you have three columns, C<id>, C<number>, and
C<squared>. You would like to make changes to C<number> and have
C<squared> be automagically set to the value of C<number> squared.
-You can accomplish this by wrapping the C<number> accessor with
-L<Class::Method::Modifiers>:
+You can accomplish this by wrapping the C<number> accessor with the C<around>
+method modifier, available through either L<Class::Method::Modifiers>,
+L<Moose|Moose::Manual::MethodModifiers> or L<Moose-like|Moo> modules):
around number => sub {
my ($orig, $self) = (shift, shift);
$self->squared( $value * $value );
}
- $self->next::method(@_);
- }
+ $self->$orig(@_);
+ };
-Note that the hard work is done by the call to C<next::method>, which
+Note that the hard work is done by the call to C<< $self->$orig >>, which
redispatches your call to store_column in the superclass(es).
Generally, if this is a calculation your database can easily do, try
=item *
Use L<populate|DBIx::Class::ResultSet/populate> in void context to insert data
-when you don't need the resulting L<DBIx::Class::Row> objects, if possible, but
-see the caveats.
+when you don't need the resulting L<result|DBIx::Class::Manual::ResultClass> objects,
+if possible, but see the caveats.
When inserting many rows, for best results, populate a large number of rows at a
time, but not so large that the table is locked for an unacceptably long time.
as the ORM loads all the relevant classes. This section examines
techniques for reducing the startup delay.
-These tips are are listed in order of decreasing effectiveness - so the
+These tips are listed in order of decreasing effectiveness - so the
first tip, if applicable, should have the greatest effect on your
application.
significant startup delay.
For production use a statically defined schema (which can be generated
-using L<DBIx::Class::Schema::Loader|DBIx::Class::Schema::Loader> to dump
-the database schema once - see
+using L<DBIx::Class::Schema::Loader> to dump the database schema once - see
L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> and
-L<dump_directory|DBIx::Class::Schema::Loader/dump_directory> for more
+L<dump_directory|DBIx::Class::Schema::Loader::Base/dump_directory> for more
details on creating static schemas from a database).
=head2 Move Common Startup into a Base Class
=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:
+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/CachedKids> hash:
# print all currently cached prepared statements
print for keys %{$schema->storage->dbh->{CachedKids}};
});
=cut
+
+=head1 FURTHER QUESTIONS?
+
+Check the list of L<additional DBIC resources|DBIx::Class/GETTING HELP/SUPPORT>.
+
+=head1 COPYRIGHT AND LICENSE
+
+This module is free software L<copyright|DBIx::Class/COPYRIGHT AND LICENSE>
+by the L<DBIx::Class (DBIC) authors|DBIx::Class/AUTHORS>. You can
+redistribute it and/or modify it under the same terms as the
+L<DBIx::Class library|DBIx::Class/COPYRIGHT AND LICENSE>.
projects / dbsrgits/DBIx-Class.git / blobdiff