App-Sqitch
view release on metacpan or search on metacpan
lib/sqitchtutorial-oracle.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-oracle-intro/
appschema 2013-12-31T22:34:42Z Marge N. O’Vera <marge@example.com> # App user and schema for all flipr objects.
users [appschema] 2014-01-01T00:31:20Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [users appschema] 2014-01-01T00:35:21Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [users appschema] 2014-01-01T00:35:28Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
@v1.0.0-dev1 2014-01-01T00:39:35Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [appschema users] 2014-01-01T00:43:46Z Marge N. O’Vera <marge@example.com> # Adds table for storing lists.
insert_list [lists appschema] 2014-01-01T00:45:24Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
delete_list [lists appschema] 2014-01-01T00:45:43Z Marge N. O’Vera <marge@example.com> # Creates a function to delete a list.
flips [appschema users] 2014-01-01T00:51:15Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
insert_flip [flips appschema] 2014-01-01T00:53:00Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a flip.
delete_flip [flips appschema] 2014-01-01T00:53:16Z 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 ............... No errors.
ok
+ change_pass @v1.0.0-dev1 .. No errors.
ok
+ lists ..................... ok
+ insert_list ............... No errors.
ok
+ delete_list ............... No errors.
ok
+ flips ..................... ok
+ insert_flip ............... No errors.
ok
+ delete_flip ............... No errors.
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 383691f] Add `.gitattributes` with union merge for `sqitch.plan`.
1 file changed, 1 insertion(+)
lib/sqitchtutorial-oracle.pod view on Meta::CPAN
" | sqlplus -S scott/tiger@flipr_test
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
NICKNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
foo
cP?.eR!V[pf3d91ce9b7dcfe9260c6f4bb94ed0b22
bar
Z+l"_W_JiSefb62b789c0ff114cddcccc69c422e78
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 crypt from flipr_test
- change_pass .. No errors.
ok
- insert_user .. No errors.
ok
Did that work, are the MD5 passwords back?
> echo "
DELETE FROM flipr.users;
EXECUTE flipr.insert_user('foo', 's3cr3t');
EXECUTE flipr.insert_user('bar', 's3cr3t');
SELECT nickname, password FROM flipr.users;
" | sqlplus -S scott/tiger@flipr_test
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
NICKNAME
--------------------------------------------------------------------------------
PASSWORD
--------------------------------------------------------------------------------
foo
a4d80eac9ab26a4a2da04125bc2c096a
bar
a4d80eac9ab26a4a2da04125bc2c096a
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 by querying the
L<C<all_source>|https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2063.htm>
view. So the C<insert_user> verify script looks like this:
-- Verify flipr:insert_user on oracle
DESCRIBE flipr.insert_user;
SELECT 1/COUNT(*)
FROM all_source
WHERE type = 'PROCEDURE'
AND name = 'INSERT_USER'
AND text LIKE '%flipr.crypt(password, DBMS_RANDOM.STRING(''p'', 10))%';
And the C<change_pass> verify script looks like this:
-- Verify flipr:change_pass on oracle
DESCRIBE flipr.change_pass;
SELECT 1/COUNT(*)
FROM all_source
WHERE type = 'PROCEDURE'
AND name = 'CHANGE_PASS'
AND text LIKE '%password = flipr.crypt(newpass, DBMS_RANDOM.STRING(''p'', 10))%';
Make sure these pass by re-deploying:
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
ok
Excellent. Let's go ahead and commit these changes:
> git add .
> git commit -m 'Use crypt to encrypt passwords.'
[main be46175] Use crypt to encrypt passwords.
13 files changed, 181 insertions(+), 15 deletions(-)
create mode 100644 deploy/change_pass@v1.0.0-dev2.sql
create mode 100644 deploy/insert_user@v1.0.0-dev2.sql
rewrite revert/change_pass.sql (98%)
rename revert/{change_pass.sql => change_pass@v1.0.0-dev2.sql} (100%)
rewrite revert/insert_user.sql (98%)
rename revert/{insert_user.sql => insert_user@v1.0.0-dev2.sql} (100%)
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: 8367dc3bff7a563ec27f145421a1ffdf724cb6de
# Name: change_pass
# Deployed: 2013-12-31 17:18:28 -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
( run in 0.669 second using v1.01-cache-2.11-cpan-39bf76dae61 )