App-Sqitch
23 results

 view release on metacpan or  search on metacpan

lib/sqitchtutorial.pod  view on Meta::CPAN 

it on C<main>:

  > git checkout flips
  Switched to branch 'flips'
  > git rebase main
  First, rewinding head to replay your work on top of it...
  Applying: Add flips table.
  Using index info to reconstruct a base tree...
  M	sqitch.plan
  Falling back to patching base and 3-way merge...
  Auto-merging sqitch.plan
  CONFLICT (content): Merge conflict in sqitch.plan
  Failed to merge in the changes.
  Patch failed at 0001 Add flips table.
  The copy of the patch that failed is found in:
     .git/rebase-apply/patch

  When you have resolved this problem, run "git rebase --continue".
  If you prefer to skip this patch, run "git rebase --skip" instead.
  To check out the original branch and stop rebasing, run "git rebase --abort".

Oy, that's kind of a pain. It seems like no matter what we do, we'll need to
resolve conflicts in that file. Except in Git. Fortunately for us, we can tell
Git to resolve conflicts in F<sqitch.plan> differently. Because we only ever
append lines to the file, we can have it use the "union" merge driver, which,
according to L<its
docs|https://git-scm.com/docs/gitattributes#_built-in_merge_drivers>:

=over

Run 3-way file level merge for text files, but take lines from both versions,
instead of leaving conflict markers. This tends to leave the added lines in
the resulting file in random order and the user should verify the result. Do
not use this if you do not understand the implications.

=back

This has the effect of appending lines from all the merging files, which is
exactly what we need. So let's give it a try. First, back out the botched
rebase:

  > git rebase --abort

Now add the union merge driver to F<.gitattributes> for F<sqitch.plan>
and rebase again:

  > echo sqitch.plan merge=union > .gitattributes
  > git rebase main                            
  First, rewinding head to replay your work on top of it...
  Applying: Add flips table.
  Using index info to reconstruct a base tree...
  M	sqitch.plan
  Falling back to patching base and 3-way merge...
  Auto-merging sqitch.plan
  Applying: Add functions to insert and delete flips.
  Using index info to reconstruct a base tree...
  M	sqitch.plan
  Falling back to patching base and 3-way merge...
  Auto-merging sqitch.plan

Ah, that looks a bit better. Let's have a look at the plan:

  > 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.

Note that it has appended the changes from the merged "lists" branch, and then
merged the changes from our "flips" branch. Test it to make sure it works as
expected:

  > sqitch rebase -y
  Reverting all changes from flipr_test
    - delete_flip ............... ok
    - insert_flip ............... ok
    - flips ..................... ok
    - change_pass @v1.0.0-dev1 .. ok
    - insert_user ............... ok
    - users ..................... ok
    - appschema ................. ok
  Deploying changes to flipr_test
    + appschema ................. ok
    + users ..................... ok
    + insert_user ............... ok
    + change_pass @v1.0.0-dev1 .. ok
    + lists ..................... ok
    + insert_list ............... ok
    + delete_list ............... ok
    + flips ..................... ok
    + insert_flip ............... ok
    + delete_flip ............... ok

Note the use of L<C<rebase>|sqitch-rebase>, which combines a
L<C<revert>|sqitch-revert> and a L<C<deploy>|sqitch-deploy> into a single
command. Handy, right? It correctly reverted our changes, and then deployed
them all again in the proper order. So let's commit F<.gitattributes>; seems
worthwhile to keep that change:

  > git add .
  > git commit -m 'Add `.gitattributes` with union merge for `sqitch.plan`.'
  [flips f5ad242] Add `.gitattributes` with union merge for `sqitch.plan`.
   1 file changed, 1 insertion(+)
   create mode 100644 .gitattributes

=head2 Merges Mastered

And now, finally, we can merge into C<main>:

lib/sqitchtutorial.pod  view on Meta::CPAN 

   BEGIN;
 
  @@ -11,9 +12,9 @@ CREATE OR REPLACE FUNCTION flipr.change_pass(
   ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
   BEGIN
       UPDATE flipr.users
  -       SET password = md5($3)
  +       SET password = crypt($3, gen_salt('md5'))
        WHERE nickname = $1
  -       AND password = md5($2);
  +       AND password = crypt($2, password);
       RETURN FOUND;
   END;
   $$;

And then try a deployment:

  > sqitch deploy
  Deploying changes to flipr_test
    + insert_user .. ok
    + change_pass .. ok

So, are the changes deployed?

  > psql -d flipr_test -c "
      DELETE FROM flipr.users;
      SELECT flipr.insert_user('foo', 'secr3t'), flipr.insert_user('bar', 'secr3t');
      SELECT * FROM flipr.users;
  "
   nickname |              password              |           timestamp           
  ----------+------------------------------------+-------------------------------
   foo      | $1$pRNfJjI9$CdcEXJ9xCoJPD.R5Z/7.R1 | 2013-12-31 01:03:15.398572+00
   bar      | $1$Nf1LcU.p$B9sKzdu8vMgu5oxbimo5P1 | 2013-12-31 01:03:15.398572+00

Awesome, the stored passwords are different now. But can we revert, even
though we haven't written any reversion scripts?

  > sqitch revert --to @HEAD^^ -y
  Reverting changes to pgcrypto from flipr_test
    - change_pass .. ok
    - insert_user .. ok

Did that work, are the C<MD5()> passwords back?

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

Yes, it works! Sqitch properly finds the original instances of these changes
in the new script files that include tags.

But what about the verify script? How can we verify that the functions have
been modified to use C<crypt()>? I think the simplest thing to do is to
examine the body of the function, using
L<C<pg_get_functiondef()>|https://www.postgresql.org/docs/9.2/static/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE>. So the C<insert_user> verify script looks like this:

  -- Verify flipr:insert_user on pg

  BEGIN;

  SELECT has_function_privilege('flipr.insert_user(text, text)', 'execute');

  SELECT 1/COUNT(*)
    FROM pg_catalog.pg_proc
   WHERE proname = 'insert_user'
     AND pg_get_functiondef(oid) LIKE $$%crypt($2, gen_salt('md5'))%$$;

  ROLLBACK;

And the C<change_pass> verify script looks like this:

  -- Verify flipr:change_pass on pg

  BEGIN;

  SELECT has_function_privilege('flipr.change_pass(text, text, text)', 'execute');

  SELECT 1/COUNT(*)
    FROM pg_catalog.pg_proc
   WHERE proname = 'change_pass'
     AND pg_get_functiondef(oid) LIKE $$%crypt($3, gen_salt('md5'))%$$;

  ROLLBACK;

Make sure these pass by re-deploying:

  > sqitch deploy
  Deploying changes to flipr_test
    + insert_user .. ok
    + change_pass .. ok

Excellent. Let's go ahead and commit these changes:

  > git add .
  > git commit -m 'Use pgcrypto to encrypt passwords.'
  [main 4257ae6] Use pgcrypto to encrypt passwords.
   13 files changed, 107 insertions(+), 9 deletions(-)
   create mode 100644 deploy/change_pass@v1.0.0-dev2.sql
   create mode 100644 deploy/insert_user@v1.0.0-dev2.sql
   create mode 100644 revert/change_pass@v1.0.0-dev2.sql
   create mode 100644 revert/insert_user@v1.0.0-dev2.sql
   create mode 100644 verify/change_pass@v1.0.0-dev2.sql
   create mode 100644 verify/insert_user@v1.0.0-dev2.sql

  > sqitch status
  # On database flipr_test
  # Project:  flipr
  # Change:   d3ffa30b72abaf9619ae1f0e726026667612f2b1
  # Name:     change_pass
  # Deployed: 2013-12-30 17:05:08 -0800
  # By:       Marge N. O’Vera <marge@example.com>
  # 
  Nothing to deploy (up-to-date)

=head1 More to Come

Sqitch is a work in progress. Better integration with version control systems
is planned to make managing idempotent reworkings even easier. Stay tuned.

=head1 Author

David E. Wheeler <david@justatheory.com>

=head1 License

Copyright (c) 2012-2026 David E. Wheeler, 2012-2021 iovation Inc.

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights



( run in 0.642 second using v1.01-cache-2.11-cpan-39bf76dae61 )