view release on metacpan or  search on metacpan

lib/sqitchtutorial.pod  view on Meta::CPAN

  > cat sqitch.plan
  %syntax-version=1.0.0
  %project=flipr
  %uri=https://github.com/sqitchers/sqitch-intro/

  appschema 2013-12-30T23:19:45Z Marge N. O’Vera <marge@example.com> # Add schema for all flipr objects.
  users [appschema] 2013-12-30T23:49:00Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
  insert_user [users appschema] 2013-12-30T23:57:36Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
  change_pass [users appschema] 2013-12-30T23:57:45Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
  @v1.0.0-dev1 2013-12-31T00:01:22Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.

  lists [appschema users] 2013-12-31T00:39:40Z Marge N. O’Vera <marge@example.com> # Adds table for storing lists.
  insert_list [lists appschema users] 2013-12-31T00:41:29Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
  delete_list [lists appschema users] 2013-12-31T00:41:37Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a list.
  flips [appschema users] 2013-12-31T00:32:39Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
  insert_flip [flips appschema users] 2013-12-31T00:35:59Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a flip.
  delete_flip [flips appschema users] 2013-12-31T00:36:34Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a flip.

Much much better, a nice clean main now. And because it is now identical to
the "flips" branch, we can just carry on. Go ahead and tag it, bundle, and
release:

  > sqitch tag v1.0.0-dev2 -n 'Tag v1.0.0-dev2.'
  Tagged "delete_flip" with @v1.0.0-dev2
  > git commit -am 'Tag the database with v1.0.0-dev2.'
  [main 230603b] Tag the database with v1.0.0-dev2.
   1 file changed, 1 insertion(+)
  > git tag v1.0.0-dev2 -am 'Tag v1.0.0-dev2'
  > sqitch bundle --dest-dir flipr-1.0.0-dev2
  Bundling into flipr-1.0.0-dev2
  Writing config
  Writing plan
  Writing scripts
    + appschema
    + users
    + insert_user
    + change_pass @v1.0.0-dev1
    + lists
    + insert_list
    + delete_list
    + flips
    + insert_flip
    + delete_flip @v1.0.0-dev2

Note the use of the C<--dest-dir> option to C<sqitch bundle>. Just a nicer way
to create the top-level directory name so we don't have to rename it from
F<bundle>.

=head1 In Place Changes

Uh-oh, someone just noticed that MD5 hashing is not particularly secure. Why?
Have a look at this:

  > psql -d flipr_test -c "
      SELECT flipr.insert_user('foo', 'secr3t'), flipr.insert_user('bar', 'secr3t');
      SELECT * FROM flipr.users;
  "
   nickname |             password             |           timestamp           
  ----------+----------------------------------+-------------------------------
   foo      | 9695da4dd567a19f9b92065f240c6725 | 2013-12-31 00:56:20.240481+00
   bar      | 9695da4dd567a19f9b92065f240c6725 | 2013-12-31 00:56:20.240481+00

If user "foo" ever got access to the database, she could quickly discover that
user "bar" has the same password and thus be able to exploit the account. Not
a great idea. So we need to modify the C<insert_user()> and C<change_pass()>
functions to fix that. How?

We'll use
L<C<pgcrypto>|https://www.postgresql.org/docs/current/static/pgcrypto.html>'s
C<crypt()> function to encrypt passwords with a salt, so that they're all
unique. We just add a change to add C<pgcrypto> to the database, and then we
can use it. The deploy script should be:

  CREATE EXTENSION pgcrypto;

And the revert script should be:

  DROP EXTENSION pgcrypto;

=over

If you're on PostgreSQL 9.0 or lower, you won't be able to deploy C<pgcrypto>
with a Sqitch change, alas. You'll have to install it manually, like so:

    psql -d flipr_test -f /path/to/pgsql/share/contrib/pgcrypto.sql

Don't forget to do this with your staging and production databases, too. Or
consider upgrading to PostgreSQL 9.1 or higher; the SQL-level extension
support is amazingly useful.

=back

We're going to use the C<crypt()> and C<gen_salt()> functions, so in the
C<verify> script, let's make sure that the extension exists I<and> that both
those functions exist:

  SELECT 1/count(*) FROM pg_extension WHERE extname = 'pgcrypto';
  SELECT has_function_privilege('crypt(text, text)', 'execute');
  SELECT has_function_privilege('gen_salt(text)', 'execute');

Now we can use C<pgcrypto>. But how to deploy the changes to C<insert_user()>
and C<change_pass()>?

Normally, modifying functions in database changes is a
L<PITA|https://www.urbandictionary.com/define.php?term=pita>. You have to make
changes like these:

=over

=item 1.

Copy F<deploy/insert_user.sql> to F<deploy/insert_user_crypt.sql>.

=item 2.

Edit F<deploy/insert_user_crypt.sql> to switch from C<MD5()> to C<crypt()>
and to add a dependency on the C<pgcrypto> change.

=item 3.

Copy F<deploy/insert_user.sql> to F<revert/insert_user_crypt.sql>.