view release on metacpan or  search on metacpan

lib/DBIO/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|DBIO::Storage/txn_do>: If the L<try|Try::Tiny/try>-block
around L<txn_do|DBIO::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 DBIO::Storage::TxnScopeGuard

An easy way to use transactions is with
L<DBIO::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<DBIO::Generate> will connect to a database and create L<DBIO::Schema>
Result class files by examining the database.

The recommended way of achieving this is to use the L<dbiogen> utility or the
L<Catalyst> helper, as described in
L<Manual::Intro|DBIO::Manual::Intro/Using DBIO::Generate>:

  dbiogen -o dump_directory=./lib \
    -o db_schema=myschema \
    -o components='["InflateColumn::DateTime"]' \
    My::Schema 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

Each driver ships a native Deploy class; core has no SQL::Translator-based DDL
generation. The old C<create_ddl_dir> is deprecated and now throws - use the
storage's native Deploy class instead.

To create a new database from the schema:

 my $schema = My::Schema->connect($dsn);
 $schema->deploy;

C<deploy> routes through the C<dbio_deploy_class()> declared on the active
storage (e.g. L<DBIO::PostgreSQL::Deploy>, L<DBIO::MySQL::Deploy>,
L<DBIO::SQLite::Deploy>), which introspects both the live database and the
desired schema and applies the diff (test-and-compare). The native Deploy
classes expose C<install>, C<diff>, C<apply> and C<upgrade> directly for finer
control, including version-to-version C<ALTER TABLE> upgrades.

If you keep pre-generated C<.sql> files on disk,
C<< $schema->deployment_statements >> reads and returns the matching DDL file
(and throws if none is present); it performs no on-the-fly SQL generation.

=head2 Select from dual

Dummy tables are needed by some databases to allow calling functions
or expressions that aren't based on table content, for examples of how
this applies to various database types, see:
L<http://troels.arvin.dk/db/rdbms/#other-dummy_table>.

Note: If you're using Oracles dual table don't B<ever> do anything
other than a select, if you CRUD on your dual table you *will* break
your database.

Make a table class as you would for any other table

  package MyAppDB::Dual;
  use strict;
  use warnings;