view release on metacpan or  search on metacpan

lib/sqitchtutorial-exasol.pod  view on Meta::CPAN

(If you see an error resolving the TLS certificate, you can disable
certificate verification by adding C<&SSLCERTIFICATE=SSL_VERIFY_NONE> to the
end of the URL. Only do this for testing!)

First Sqitch created registry tables used to track database changes. The
structure and name of the registry varies between databases (Exasol uses a
schema to namespace its registry, while SQLite and MySQL use separate
databases). Next, Sqitch deploys changes. We only have one so far; the C<+>
reinforces the idea that the change is being C<added> to the database.

With this change deployed, if you connect to the database, you'll be able to
see the schema:

  > exaplus -q -u sys -p exasol -c localhost:8563 -sql "select schema_name from exa_schemas;"

  SCHEMA_NAME
  --------------------------------------------------------------------------------------------------------------------------------
  SQITCH
  FLIPR

=head2 Trust, But Verify

But that's too much work. Do you really want to do something like that after
every deploy?

Here's where the C<verify> script comes in. Its job is to test that the deploy
did was it was supposed to. It should do so without regard to any data that
might be in the database, and should throw an error if the deploy was not
successful. In Exasol, the simplest way to do so for schema is probably to
simply create an object in the schema. Put this SQL into
F<verify/appschema.sql>:

  CREATE TABLE flipr.verify__ (id int);
  DROP   TABLE flipr.verify__;

In truth, you can use I<any> query that generates an SQL error if the schema
doesn't exist. Another handy way to do that is to divide by zero if an object
doesn't exist. For example, to throw an error when the C<flipr> schema does
not exist, you could do something like this:

  SELECT 1/COUNT(*) FROM exa_schemas WHERE schema_name = 'FLIPR';

Either way, run the C<verify> script with the L<C<verify>|sqitch-verify>
command:

  > sqitch verify 'db:exasol://sys:exasol@localhost:8563/?Driver=Exasol'
  Verifying db:exasol://sys:@localhost:8563/?Driver=Exasol
    * appschema .. ok
  Verify successful

Looks good! If you want to make sure that the verify script correctly dies if
the schema doesn't exist, temporarily change the schema name in the script to
something that doesn't exist, something like:

  CREATE TABLE nonesuch.verify__ (id int);

Then L<C<verify>|sqitch-verify> again:

  > sqitch verify 'db:exasol://sys:exasol@localhost:8563/?Driver=Exasol'
  Verifying db:exasol://sys:@localhost:8563/?Driver=Exasol
  * appschema .. Error: [42000] schema NONESUCH not found [line 1, column 40] (Session: 1582884049218108749)

  # Verify script "verify/appschema.sql" failed.
  not ok

  Verify Summary Report
  ---------------------
  Changes: 1
  Errors:  1
  Verify failed

It's even nice enough to tell us what the problem is. Or, for the
divide-by-zero example, change the schema name:

  SELECT 1/COUNT(*) FROM exa_schemas WHERE schema_name = 'nonesuch';

Then the verify will look something like:

  > sqitch verify 'db:exasol://sys:exasol@localhost:8563/?Driver=Exasol'
  Verifying db:exasol://sys:@localhost:8563/?Driver=Exasol
  * appschema .. Error: [22012] data exception - division by zero (Session: 1582884446489810101)

  # Verify script "verify/appschema.sql" failed.
  not ok

  Verify Summary Report
  ---------------------
  Changes: 1
  Errors:  1
  Verify failed

Less useful error output, but enough to alert us that something has gone
wrong.

Don't forget to change the schema name back before continuing!

=head2 Status, Revert, Log, Repeat

For purely informational purposes, we can always see how a deployment was
recorded via the L<C<status>|sqitch-status> command, which reads the registry
tables from the database:

  > sqitch status 'db:exasol://sys:exasol@localhost:8563/?Driver=Exasol'
  # On database db:exasol://sys:@localhost:8563/?Driver=Exasol
  # Project:  flipr
  # Change:   f9759f0ed77964b6a3b6c7aa3b6058b4bb7db764
  # Name:     appschema
  # Deployed: 2014-09-04 15:26:28 -0700
  # By:       Marge N. O’Vera <marge@example.com>
  #
  Nothing to deploy (up-to-date)

Let's make sure that we can revert the change:

  > sqitch revert 'db:exasol://sys:exasol@localhost:8563/?Driver=Exasol'
  Revert all changes from db:exasol://sys:@localhost:8563/?Driver=Exasol? [Yes]
    - appschema .. ok

The L<C<revert>|sqitch-revert> command first prompts to make sure that we
really do want to revert. This is to prevent unnecessary accidents. You can
pass the C<-y> option to disable the prompt. Also, notice the C<-> before the