view release on metacpan or  search on metacpan

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

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. The easiest way to do that with a table is to simply C<SELECT>
from it. Put this query into F<verify/users.sql>:

  SELECT nickname, password, fullname, twitter
    FROM users
   WHERE 1=2;

Now you can run the C<verify> script with the L<C<verify>|sqitch-verify>
command:

  > sqitch verify db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  Verifying db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
    * users .. ok
  Verify successful

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

  SELECT nickname, password, fullname, twitter, created_at
    FROM users_nonesuch
   WHERE 1=2;

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

  > sqitch verify db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  Verifying db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
    * users .. Statement failed, SQLSTATE = 42S02
  Dynamic SQL Error
  -SQL error code = -204
  -Table unknown
  -USERS_NONESUCH
  -At line 3, column 2
  At line 3 in file verify/users.sql
  # Verify script "verify/users.sql" failed.
  not ok

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

Firebird is kind enough to tell us what the problem is. Don't forget to change
the table 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 tables
from the registry database:

  > sqitch status db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  # On database db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
  # Project:  flipr
  # Change:   2cde9cc8c19161e9837de57741502243b2ad380e
  # Name:     users
  # Deployed: 2014-01-05 14:05:22 -0800
  # 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:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  Revert all changes from db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb? [Yes] 
    - users .. 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
change name in the output, which reinforces that the change is being
I<removed> from the database. And now the C<users> table should be gone:

  > echo "CONNECT 'localhost:/tmp/flipr_test/flipr.fdb'; SHOW TABLES; quit;" \
    | isql-fb -q -u SYSDBA -p masterkey
  There are no tables in this database

And the status message should reflect as much:

  > sqitch status db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  # On database db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
  No changes deployed

Of course, since nothing is deployed, the L<C<verify>|sqitch-verify> command
has nothing to verify:

  > sqitch verify db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  Verifying db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
  No changes deployed

However, we still have a record that the change happened, visible via the
L<C<log>|sqitch-log> command:

  > sqitch log db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  On database db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
  Revert 2cde9cc8c19161e9837de57741502243b2ad380e
  Name:      users
  Committer: Marge N. O’Vera <marge@example.com>
  Date:      2014-01-05 14:06:59 -0800

      Creates table to track our users.

  Deploy 2cde9cc8c19161e9837de57741502243b2ad380e
  Name:      users
  Committer: Marge N. O’Vera <marge@example.com>
  Date:      2014-01-05 14:05:22 -0800

      Creates table to track our users.


Note that the actions we took are shown in reverse chronological order, with
the revert first and then the deploy.

Cool. Now let's commit it.

  > git add .
  > git commit -m 'Add users table.'
  [main ec72105] Add users table.
   4 files changed, 24 insertions(+), 0 deletions(-)
   create mode 100644 deploy/users.sql
   create mode 100644 revert/users.sql
   create mode 100644 verify/users.sql

And then deploy again. This time, let's use the C<--verify> option, so that
the C<verify> script is applied when the change is deployed:
 
  > sqitch deploy db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb --verify
  Deploying changes to db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
    + users .. ok

And now the C<users> table should be back:

  > echo "CONNECT 'localhost:/tmp/flipr_test/flipr.fdb'; SHOW TABLES; quit;" \
    | isql-fb -q -u SYSDBA -p masterkey
         USERS                           

When we look at the status, the deployment will be there:

  > sqitch status db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb
  # On database db:firebird://sysdba:@localhost//tmp/flipr_test/flipr.fdb
  # Project:  flipr
  # Change:   2cde9cc8c19161e9837de57741502243b2ad380e
  # Name:     users
  # Deployed: 2014-01-05 14:19:32 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

=head1 On Target

I'm getting a little tired of always having to type
C<db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb>, aren't
you? This L<database connection URI|https://github.com/libwww-perl/uri-db/> tells
Sqitch how to connect to the deployment target, but we don't have to keep
using the URI. We can name the target:

  > sqitch target add flipr_test db:firebird://sysdba:masterkey@localhost//tmp/flipr_test/flipr.fdb

The L<C<target>|sqitch-target> command, inspired by
L<C<git-remote>|https://git-scm.com/docs/git-remote>, allows management of one
or more named deployment targets. We've just added a target named
C<flipr_test>, which means we can use the string C<flipr_test> for the target,
rather than the URI. But since we're doing so much testing, we can also tell
Sqitch to deploy to the C<flipr_test> target by default:

  > sqitch engine add firebird target flipr_test

Now we can omit the target argument altogether, unless we need to deploy to
another database. Which we will, eventually, but at least our examples will be
simpler from here on in, e.g.:

  > sqitch status
  # On database flipr_test
  # Project:  flipr
  # Change:   2cde9cc8c19161e9837de57741502243b2ad380e
  # Name:     users
  # Deployed: 2014-01-05 14:19:32 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

Yay, that allows things to be a little more concise. Let's also make sure that
changes are verified after deploying them:

  > sqitch config --bool deploy.verify true
  > sqitch config --bool rebase.verify true

We'll see the L<C<rebase>|sqitch-rebase> command a bit later. In the meantime,
let's commit the new configuration and and make some more changes!

  > git commit -am 'Set default target and always verify.'
  [main cfc9fea] Set default target and always verify.
   1 files changed, 8 insertions(+), 0 deletions(-)

=head1 Deploy with Dependency

Let's add another change. Our app will need to store status messages from
users. Let's call them -- and the table to store them -- "flips". First, add
the new change:

  > sqitch add flips --requires users -n 'Adds table for storing flips.'
  Created deploy/flips.sql
  Created revert/flips.sql
  Created verify/flips.sql
  Added "flips [users]" to sqitch.plan

Note that we're requiring the C<users> change as a dependency of the new
C<flips> change. Although that change has already been added to the plan and
therefore should always be applied before the C<flips> change, it's a good
idea to be explicit about dependencies.

Now edit the scripts. When you're done, F<deploy/flips.sql> should look like
this:

  -- Deploy flipr:flips to firebird
  -- requires: users

  CREATE TABLE flips (
      id         INTEGER       NOT NULL PRIMARY KEY,
      nickname   VARCHAR(50)   NOT NULL REFERENCES users(nickname),
      body       VARCHAR(512)  DEFAULT '' NOT NULL CHECK ( char_length(body) <= 180 ),
      created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL
  );

  COMMIT;

A couple things to notice here. On the second line, the dependence on the
C<users> change has been listed. This doesn't do anything, but the default
C<deploy> template lists it here for your reference while editing the file.
Useful, right?

The C<users.nickname> column references the C<users> table. This is why we
need to require the C<users> change.

Now for the verify script. Again, all we need to do is C<SELECT> from the

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

  > sqitch deploy
  Deploying changes to flipr_test
    + flips .. ok

We know, since verification is enabled, that the table must have been created.
But for the purposes of visibility, let's have a quick look:

  > echo "CONNECT 'localhost:/tmp/flipr_test/flipr.fdb'; SHOW TABLES; quit;" \
    | isql-fb -q -u SYSDBA -p masterkey
         FLIPS                                  USERS

We can also verify all currently deployed changes with the
L<C<verify>|sqitch-verify> command:

  > sqitch verify
  Verifying flipr_test.db
    * users .. ok
    * flips .. ok
  Verify successful

Now have a look at the status:

  > sqitch status
  # On database flipr_test
  # Project:  flipr
  # Change:   dfe72351c686bd36017a2b586042b5336301e809
  # Name:     flips
  # Deployed: 2014-01-05 14:22:33 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

Success! Let's make sure we can revert the change, as well:

  > sqitch revert --to @HEAD^ -y
  Reverting changes to users from flipr_test
    - flips .. ok

Note that we've used the C<--to> option to specify the change to revert to.
And what do we revert to? The symbolic tag C<@HEAD>, when passed to
L<C<revert>|sqitch-revert>, always refers to the last change deployed to the
database. (For other commands, it refers to the last change in the plan.)
Appending the caret (C<^>) tells Sqitch to select the change I<prior> to the
last deployed change. So we revert to C<users>, the penultimate change. The
other potentially useful symbolic tag is C<@ROOT>, which refers to the first
change deployed to the database (or in the plan, depending on the command).

Back to the database. The C<flips> table should be gone but the
C<users> table should still be around:

  > echo "CONNECT 'localhost:/tmp/flipr_test/flipr.fdb'; SHOW TABLES; quit;" \
    | isql-fb -q -u SYSDBA -p masterkey
       USERS                        

The L<C<status>|sqitch-status> command politely informs us that we have
undeployed changes:

  > sqitch status
  # On database flipr_test
  # Project:  flipr
  # Change:   2cde9cc8c19161e9837de57741502243b2ad380e
  # Name:     users
  # Deployed: 2014-01-05 14:19:32 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Undeployed change:
    * flips

As does the L<C<verify>|sqitch-verify> command:

  > sqitch verify
  Verifying flipr_test
    * users .. ok
  Undeployed change:
    * flips
  Verify successful

Note that the verify is successful, because all currently-deployed changes are
verified. The list of undeployed changes (just "flips" here) reminds us about
the current state.

Okay, let's commit and deploy again:

  > git add .
  > git commit -am 'Add flips table.'
  [main 09c636c] Add flips table.
   4 files changed, 24 insertions(+), 0 deletions(-)
   create mode 100644 deploy/flips.sql
   create mode 100644 revert/flips.sql
   create mode 100644 verify/flips.sql
  > sqitch deploy
  Deploying changes to flipr_test
    + flips .. ok

Looks good. Check the status:

  > sqitch status
  # On database flipr_test
  # Project:  flipr
  # Change:   dfe72351c686bd36017a2b586042b5336301e809
  # Name:     flips
  # Deployed: 2014-01-05 14:24:06 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

=head1 View to a Thrill

One more thing to add before we are ready to ship a first beta release. Let's
create a view that lists user names with their flips.

  > sqitch add userflips --requires users --requires flips \
    -n 'Creates the userflips view.'
  Created deploy/userflips.sql
  Created revert/userflips.sql
  Created verify/userflips.sql
  Added "userflips [users flips]" to sqitch.plan

Now add this SQL to F<deploy/userflips.sql>:

  CREATE OR ALTER VIEW userflips AS