view release on metacpan or  search on metacpan

lib/DBIx/Class/Manual/Cookbook.pod  view on Meta::CPAN

          # 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 happened, 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;
      }
    });
  } catch {
    $exception = $_;
  };

  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
    # $thing entry in the database.

    # SQL: ROLLBACK;

    print "ERROR: $exception\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|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.

=head2 Simple Transactions with DBIx::Class::Storage::TxnScopeGuard

An easy way to use transactions is with
L<DBIx::Class::Storage::TxnScopeGuard>. See L</Automatically creating
related objects> for an example.

Note that unlike txn_do, TxnScopeGuard will only make sure the connection is
alive when issuing the C<BEGIN> statement. It will not (and really can not)
retry if the server goes away mid-operations, unlike C<txn_do>.

=head1 SQL

=head2 Creating Schemas From An Existing Database

L<DBIx::Class::Schema::Loader> will connect to a database and create a
L<DBIx::Class::Schema> and associated sources by examining the database.

The recommend way of achieving this is to use the L<dbicdump> utility or the
L<Catalyst> helper, as described in
L<Manual::Intro|DBIx::Class::Manual::Intro/Using DBIx::Class::Schema::Loader>.

Alternatively, use the
L<make_schema_at|DBIx::Class::Schema::Loader/make_schema_at> method:

  perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib \
    -e 'make_schema_at("My::Schema", \
    { db_schema => 'myschema', components => ["InflateColumn::DateTime"] }, \
    [ "dbi:Pg:dbname=foo", "username", "password" ])'

This will create a tree of files rooted at C<./lib/My/Schema/> containing source
definitions for all the tables found in the C<myschema> schema in the C<foo>
database.

=head2 Creating DDL SQL

The following functionality requires you to have L<SQL::Translator>
(also known as "SQL Fairy") installed.

To create a set of database-specific .sql files for the above schema:

 my $schema = My::Schema->connect($dsn);
 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
                        '0.1',
                        './dbscriptdir/'
                        );

By default this will create schema files in the current directory, for
MySQL, SQLite and PostgreSQL, using the $VERSION from your Schema.pm.

To create a new database using the schema:

 my $schema = My::Schema->connect($dsn);
 $schema->deploy({ add_drop_table => 1});

To import created .sql files using the mysql client:

  mysql -h "host" -D "database" -u "user" -p < My_Schema_1.0_MySQL.sql

To create C<ALTER TABLE> conversion scripts to update a database to a
newer version of your schema at a later point, first set a new
C<$VERSION> in your Schema file, then:

 my $schema = My::Schema->connect($dsn);
 $schema->create_ddl_dir(['MySQL', 'SQLite', 'PostgreSQL'],
                         '0.2',
                         '/dbscriptdir/',
                         '0.1'
                         );