App-Sqitch
view release on metacpan or search on metacpan
lib/sqitchtutorial-oracle.pod view on Meta::CPAN
Couldn't be much simpler, right? Let's deploy this bad boy:
> sqitch deploy
Deploying changes to flipr_test
+ users .. 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 "DESCRIBE flipr.users;" | sqlplus -S scott/tiger@flipr_test
Name Null? Type
----------------------------------------- -------- ----------------------------
NICKNAME NOT NULL VARCHAR2(512 CHAR)
PASSWORD NOT NULL VARCHAR2(512 CHAR)
TIMESTAMP NOT NULL TIMESTAMP(6) WITH TIME ZONE
We can also verify all currently deployed changes with the
L<C<verify>|sqitch-verify> command:
> sqitch verify
Verifying flipr_test
* appschema .. ok
* users ...... ok
Verify successful
Now have a look at the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 6840dc13beb0cd716b8bd3979b03a259c1e94405
# Name: users
# Deployed: 2013-12-31 16:32:31 -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 appschema from flipr_test
- users .. 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<appschema>, 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<users> table should be gone but the C<flipr> schema
should still be around:
> echo "DESCRIBE flipr.users;" | sqlplus -S scott/tiger@flipr_test
ERROR:
ORA-04043: object flipr.users does not exist
The L<C<status>|sqitch-status> command politely informs us that we have
undeployed changes:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: c59e700589fc03568e8f35f592c0d9b7c638cbdd
# Name: appschema
# Deployed: 2013-12-31 16:22:01 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Undeployed change:
* users
As does the L<C<verify>|sqitch-verify> command:
> sqitch verify
Verifying flipr_test
* appschema .. ok
Undeployed change:
* users
Verify successful
Note that the verify is successful, because all currently-deployed changes are
verified. The list of undeployed changes (just "users" here) reminds us about
the current state.
Okay, let's commit and deploy again:
> git add .
> git commit -am 'Add users table.'
[main 2506312] Add users table.
4 files changed, 17 insertions(+)
create mode 100644 deploy/users.sql
create mode 100644 revert/users.sql
create mode 100644 verify/users.sql
> sqitch deploy
Deploying changes to flipr_test
+ users .. ok
Looks good. Check the status:
> sqitch status
# On database flipr_test
# Project: flipr
# Change: 6840dc13beb0cd716b8bd3979b03a259c1e94405
# Name: users
# Deployed: 2013-12-31 16:34:28 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)
Excellent. Let's do some more!
=head1 Add Two at Once
Let's add a couple more changes to add functions for managing users.
> sqitch add insert_user --requires users --requires appschema \
lib/sqitchtutorial-oracle.pod view on Meta::CPAN
And of course, its C<revert> script, F<revert/change_pass.sql>, should look
something like:
-- Revert flipr:change_pass from oracle
DROP PROCEDURE flipr.change_pass;
Try em out!
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
ok
Looks good. The "No errors" notices come from the C<SHOW ERRORS> SQL*Plus
command. It's not very useful here, but very useful if there are compilation
errors. If it bothers you, you can drop the C<SHOW ERRORS> line and select the
error for display in the C<DECLARE> block, instead.
Now, do we have the functions? Of course we do, they were verified. Still,
have a look:
> echo "DESCRIBE flipr.insert_user;\nDESCRIBE flipr.change_pass;" \
| sqlplus -S scott/tiger@flipr_test
PROCEDURE flipr.insert_user
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NICKNAME VARCHAR2 IN
PASSWORD VARCHAR2 IN
PROCEDURE flipr.change_pass
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NICK VARCHAR2 IN
OLDPASS VARCHAR2 IN
NEWPASS VARCHAR2 IN
And what's the status?
> sqitch status
# On database flipr_test
# Project: flipr
# Change: e1c9df6a95da835769eb560790588c16174f78df
# Name: change_pass
# Deployed: 2013-12-31 16:37:22 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)
Looks good. Let's make sure revert works:
> sqitch revert -y --to @HEAD^^
Reverting changes to users from flipr_test
- change_pass .. ok
- insert_user .. ok
> echo "DESCRIBE flipr.insert_user;\nDESCRIBE flipr.change_pass;" \
| sqlplus -S dwheeler/dwheeler@flipr_test
ERROR:
ORA-04043: object flipr.insert_user does not exist
ERROR:
ORA-04043: object flipr.change_pass does not exist
Note the use of C<@HEAD^^> to specify that the revert be to two changes prior
the last deployed change. Looks good. Let's do the commit and re-deploy dance:
> git add .
> git commit -m 'Add `insert_user()` and `change_pass()`.'
[main 6b6797e] Add `insert_user()` and `change_pass()`.
7 files changed, 92 insertions(+)
create mode 100644 deploy/change_pass.sql
create mode 100644 deploy/insert_user.sql
create mode 100644 revert/change_pass.sql
create mode 100644 revert/insert_user.sql
create mode 100644 verify/change_pass.sql
create mode 100644 verify/insert_user.sql
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. No errors.
ok
+ change_pass .. No errors.
ok
> sqitch status
# On database flipr_test
# Project: flipr
# Change: e1c9df6a95da835769eb560790588c16174f78df
# Name: change_pass
# Deployed: 2013-12-31 16:38:46 -0800
# By: Marge N. O’Vera <marge@example.com>
#
Nothing to deploy (up-to-date)
> sqitch verify
Verifying flipr_test
* appschema .... ok
* users ........ ok
* insert_user .. ok
* change_pass .. ok
Verify successful
Great, we're fully up-to-date!
=head1 Ship It!
Let's do a first release of our app. Let's call it C<1.0.0-dev1> Since we want
to have it go out with deployments tied to the release, let's tag it:
> sqitch tag v1.0.0-dev1 -n 'Tag v1.0.0-dev1.'
Tagged "change_pass" with @v1.0.0-dev1
> git commit -am 'Tag the database with v1.0.0-dev1.'
[main eae5f71] Tag the database with v1.0.0-dev1.
1 file changed, 1 insertion(+)
> git tag v1.0.0-dev1 -am 'Tag v1.0.0-dev1'
We can try deploying to make sure the tag gets picked up by deploying to a new
database, like so (assuming you have an Oracle SID named C<flipr_dev> that
points to a different database):
> sqitch deploy db:oracle://scott:tiger@/flipr_dev
Adding registry tables to db:oracle://scott:@/flipr_dev
( run in 0.475 second using v1.01-cache-2.11-cpan-39bf76dae61 )