package My::Schema::Result::UserFriendsComplex;
use strict;
use warnings;
- use base qw/DBIx::Class/;
+ use base qw/DBIx::Class::Core/;
- __PACKAGE__->load_components('Core');
__PACKAGE__->table_class('DBIx::Class::ResultSource::View');
# ->table, ->add_columns, etc.
The following will B<not> work:
my $rs = $schema->resultset('CD')->search({
- artist_id => $inside_rs->get_column('id')->as_query,
+ artist_id => $inside_rs->get_column('id')->as_query, # does NOT work
});
=head3 Support
for you, in your My::DBIC::Schema::CD class:
# class definition as normal
- __PACKAGE__->load_components(qw/ Core /);
+ use base 'DBIx::Class::Core';
__PACKAGE__->table('cd');
# tell DBIC to use the custom ResultSet class
$rs->search({ 'YEAR(date_of_birth)' => 1979 });
-With quoting on, or for a more portable solution, use the C<where>
-attribute:
+With quoting on, or for a more portable solution, use literal SQL values with
+placeholders:
- $rs->search({}, { where => \'YEAR(date_of_birth) = 1979' });
+ $rs->search(\[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ]);
-=begin hidden
+ # Equivalent SQL:
+ # SELECT * FROM employee WHERE YEAR(date_of_birth) = ?
-(When the bind args ordering bug is fixed, this technique will be better
-and can replace the one above.)
+ $rs->search({
+ name => 'Bob',
+ -nest => \[ 'YEAR(date_of_birth) = ?', [ plain_value => 1979 ] ],
+ });
-With quoting on, or for a more portable solution, use the C<where> and
-C<bind> attributes:
+ # Equivalent SQL:
+ # SELECT * FROM employee WHERE name = ? AND YEAR(date_of_birth) = ?
- $rs->search({}, {
- where => \'YEAR(date_of_birth) = ?',
- bind => [ 1979 ]
- });
+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
+otherwise it's essentially a dummy string currently (use C<plain_value> as a
+habit). It is used by L<DBIx::Class> to handle special column types.
-=end hidden
+See also L<SQL::Abstract/Literal SQL with placeholders and bind values
+(subqueries)>.
=head1 JOINS AND PREFETCHING
use strict;
use warnings;
- use base qw/DBIx::Class/;
+ use base qw/DBIx::Class::Core/;
### Define what our admin class is, for ensure_class_loaded()
my $admin_class = __PACKAGE__ . '::Admin';
- __PACKAGE__->load_components(qw/Core/);
-
__PACKAGE__->table('users');
__PACKAGE__->add_columns(qw/user_id email password
### The statement below will print
print "I can do admin stuff\n" if $admin->can('do_admin_stuff');
+Alternatively you can use L<DBIx::Class::DynamicSubclass> that implements
+exactly the above functionality.
+
=head2 Skip row object creation for faster results
DBIx::Class is not built for speed, it's built for convenience and
To order C<< $book->pages >> by descending page_number, create the relation
as follows:
- __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => \'page_number DESC'} );
+ __PACKAGE__->has_many('pages' => 'Page', 'book', { order_by => { -desc => 'page_number'} } );
=head2 Filtering a relationship result set
This is straightforward using L<ManyToMany|DBIx::Class::Relationship/many_to_many>:
package My::User;
- use base 'DBIx::Class';
- __PACKAGE__->load_components('Core');
+ use base 'DBIx::Class::Core';
__PACKAGE__->table('user');
__PACKAGE__->add_columns(qw/id name/);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->many_to_many('addresses' => 'user_address', 'address');
package My::UserAddress;
- use base 'DBIx::Class';
- __PACKAGE__->load_components('Core');
+ use base 'DBIx::Class::Core';
__PACKAGE__->table('user_address');
__PACKAGE__->add_columns(qw/user address/);
__PACKAGE__->set_primary_key(qw/user address/);
__PACKAGE__->belongs_to('address' => 'My::Address');
package My::Address;
- use base 'DBIx::Class';
- __PACKAGE__->load_components('Core');
+ use base 'DBIx::Class::Core';
__PACKAGE__->table('address');
__PACKAGE__->add_columns(qw/id street town area_code country/);
__PACKAGE__->set_primary_key('id');
$rs = $user->addresses(); # get all addresses for a user
$rs = $address->users(); # get all users for an address
+ my $address = $user->add_to_addresses( # returns a My::Address instance,
+ # NOT a My::UserAddress instance!
+ {
+ country => 'United Kingdom',
+ area_code => 'XYZ',
+ town => 'London',
+ street => 'Sesame',
+ }
+ );
+
=head2 Relationships across DB schemas
Mapping relationships across L<DB schemas|DBIx::Class::Manual::Glossary/DB schema>
declaration, like so...
package MyDatabase::Main::Artist;
- use base qw/DBIx::Class/;
- __PACKAGE__->load_components(qw/PK::Auto Core/);
+ use base qw/DBIx::Class::Core/;
__PACKAGE__->table('database1.artist'); # will use "database1.artist" in FROM clause
Nested transactions will work as expected. That is, only the outermost
transaction will actually issue a commit to the $dbh, and a rollback
at any level of any transaction will cause the entire nested
-transaction to fail. Support for savepoints and for true nested
-transactions (for databases that support them) will hopefully be added
-in the future.
+transaction to fail.
+
+=head2 Nested transactions and auto-savepoints
+
+If savepoints are supported by your RDBMS, it is possible to achieve true
+nested transactions with minimal effort. To enable auto-savepoints via nested
+transactions, supply the C<< auto_savepoint = 1 >> connection attribute.
+
+Here is an example of true nested transactions. In the example, we start a big
+task which will create several rows. Generation of data for each row is a
+fragile operation and might fail. If we fail creating something, depending on
+the type of failure, we want to abort the whole task, or only skip the failed
+row.
+
+ my $schema = MySchema->connect("dbi:Pg:dbname=my_db");
+
+ # Start a transaction. Every database change from here on will only be
+ # commited into the database if the eval block succeeds.
+ eval {
+ $schema->txn_do(sub {
+ # SQL: BEGIN WORK;
+
+ my $job = $schema->resultset('Job')->create({ name=> 'big job' });
+ # SQL: INSERT INTO job ( name) VALUES ( 'big job' );
+
+ for (1..10) {
+
+ # Start a nested transaction, which in fact sets a savepoint.
+ eval {
+ $schema->txn_do(sub {
+ # SQL: SAVEPOINT savepoint_0;
+
+ my $thing = $schema->resultset('Thing')->create({ job=>$job->id });
+ # SQL: INSERT INTO thing ( job) VALUES ( 1 );
+
+ if (rand > 0.8) {
+ # This will generate an error, thus setting $@
+
+ $thing->update({force_fail=>'foo'});
+ # SQL: UPDATE thing SET force_fail = 'foo'
+ # WHERE ( id = 42 );
+ }
+ });
+ };
+ if ($@) {
+ # SQL: ROLLBACK TO SAVEPOINT savepoint_0;
+
+ # There was an error while creating a $thing. Depending on the error
+ # we want to abort the whole transaction, or only rollback the
+ # changes related to the creation of this $thing
+
+ # Abort the whole job
+ if ($@ =~ /horrible_problem/) {
+ print "something horrible happend, aborting job!";
+ die $@; # rethrow error
+ }
+
+ # Ignore this $thing, report the error, and continue with the
+ # next $thing
+ print "Cannot create thing: $@";
+ }
+ # There was no error, so save all changes since the last
+ # savepoint.
+
+ # SQL: RELEASE SAVEPOINT savepoint_0;
+ }
+ });
+ };
+ if ($@) {
+ # There was an error while handling the $job. Rollback all changes
+ # since the transaction started, including the already commited
+ # ('released') savepoints. There will be neither a new $job nor any
+ # $thing entry in the database.
+
+ # SQL: ROLLBACK;
+
+ print "ERROR: $@\n";
+ }
+ else {
+ # There was no error while handling the $job. Commit all changes.
+ # Only now other connections can see the newly created $job and
+ # @things.
+
+ # SQL: COMMIT;
+
+ print "Ok\n";
+ }
+
+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<eval>-block around C<txn_do> fails, a rollback is issued. If the C<eval>
+succeeds, the transaction is committed (or the savepoint released).
+
+While you can get more fine-grained controll using C<svp_begin>, C<svp_release>
+and C<svp_rollback>, it is strongly recommended to use C<txn_do> with coderefs.
=head1 SQL
package MyAppDB::Dual;
use strict;
use warnings;
- use base 'DBIx::Class';
- __PACKAGE__->load_components("Core");
+ use base 'DBIx::Class::Core';
__PACKAGE__->table("Dual");
__PACKAGE__->add_columns(
"dummy",
Typically L<DBIx::Class> result classes start off with
- use base qw/DBIx::Class/;
- __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
+ use base qw/DBIx::Class::Core/;
+ __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
If this preamble is moved into a common base class:-
package MyDBICbase;
- use base qw/DBIx::Class/;
- __PACKAGE__->load_components(qw/InflateColumn::DateTime Core/);
+ use base qw/DBIx::Class::Core/;
+ __PACKAGE__->load_components(qw/InflateColumn::DateTime/);
1;
and each result class then uses this as a base:-
projects / dbsrgits/DBIx-Class.git / blobdiff