App-Sqitch
view release on metacpan or search on metacpan
lib/sqitchtutorial-mysql.pod view on Meta::CPAN
on our computer (don't bother if you're using the
L<Docker image|https://hub.docker.com/r/sqitch/sqitch/> because it uses the
client inside the container, not on your host machine):
> sqitch config --user engine.mysql.client /usr/local/mysql/bin/mysql
And let's also tell it who we are, since this data will be used in all
of our projects:
> sqitch config --user user.name 'Marge N. O’Vera'
> sqitch config --user user.email 'marge@example.com'
Have a look at F<~/.sqitch/sqitch.conf> and you'll see this:
> cat ~/.sqitch/sqitch.conf
[engine "mysql"]
client = /usr/local/mysql/bin/mysql
[user]
name = Marge N. O’Vera
email = marge@example.com
Which means that Sqitch should be able to find C<mysql> for any project, and
that it will always properly identify us when planning and committing changes.
Back to the repository. Have a look at the plan file, F<sqitch.plan>:
> cat sqitch.plan
%syntax-version=1.0.0
%project=flipr
%uri=https://github.com/sqitchers/sqitch-mysql-intro/
Note that it has picked up on the name and URI of the app we're building.
Sqitch uses this data to manage cross-project dependencies. The
C<%syntax-version> pragma is always set by Sqitch, so that it always knows how
to parse the plan, even if the format changes in the future.
Let's commit these changes and start creating the database changes.
> git add .
> git commit -am 'Initialize Sqitch configuration.'
[main 79fe2cc] Initialize Sqitch configuration.
2 files changed, 19 insertions(+)
create mode 100644 sqitch.conf
create mode 100644 sqitch.plan
=head1 Our First Change
First, our app will need a database user, so let's create one. Run this
command:
> sqitch add appuser -n 'Creates a an application user.'
Created deploy/appuser.sql
Created revert/appuser.sql
Created verify/appuser.sql
Added "appuser" to sqitch.plan
The L<C<add>|sqitch-add> command adds a database change to the plan and writes
deploy, revert, and verify scripts that represent the change. Now we edit
these files. The C<deploy> script's job is to create the table. By default,
the F<deploy/appuser.sql> file looks like this:
-- Deploy flipr:appuser to mysql
BEGIN;
-- XXX Add DDLs here.
COMMIT;
What we want to do is to replace the C<XXX> comment with the C<CREATE USER>
statement, like so:
-- Deploy flipr:users to mysql
BEGIN;
CREATE USER flipr;
COMMIT;
The C<revert> script's job is to precisely revert the change to the deploy
script, so we edit this to F<revert/appuser.sql> to look like this:
-- Revert flipr:users from mysql
BEGIN;
DROP USER flipr;
COMMIT;
Now we can try deploying this change. First, we need to create a database
to deploy to:
> mysql -u root --execute 'CREATE DATABASE flipr_test'
Now we tell Sqitch where to send the change via a
L<database URI|https://github.com/libwww-perl/uri-db/>:
> sqitch deploy db:mysql://root@/flipr_test
Deploying changes to db:mysql://root@/flipr_test
+ appuser .. ok
First Sqitch created the registry database and tables used to track database
changes. The registry database is separate from the database to which the
C<appuser> change was deployed; by default, its name is C<sqitch>, and will be
used to manage I<all> projects on a single MySQL server. Ideally, only Sqitch
data will be stored in this database, so it probably makes the most sense to
create a superuser named C<sqitch> or something similar and use it to deploy
changes.
If you'd like it to use a different database as the registry database, use
C<sqitch engine add mysql $name> to configure it (or via the
L<C<target> command|sqitch-target>; more L<below|/On Target>). This will be
useful if you don't want to use the same registry database to manage multiple
databases on the same server.
Next, Sqitch deploys changes to the target database, which we specified on the
command-line. We only have one change so far; the C<+> reinforces the idea
that the change is being I<added> to the database.
lib/sqitchtutorial-mysql.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-mysql-intro/
appuser 2013-12-31T21:04:04Z Marge N. O’Vera <marge@example.com> # Creates a an application user.
users [appuser] 2013-12-31T21:32:48Z Marge N. O’Vera <marge@example.com> # Creates table to track our users.
insert_user [users appuser] 2013-12-31T21:37:29Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a user.
change_pass [users appuser] 2013-12-31T21:37:36Z Marge N. O’Vera <marge@example.com> # Creates a function to change a user password.
@v1.0.0-dev1 2013-12-31T21:41:08Z Marge N. O’Vera <marge@example.com> # Tag v1.0.0-dev1.
lists [appuser users] 2013-12-31T21:46:22Z Marge N. O’Vera <marge@example.com> # Adds table for storing lists.
insert_list [lists appuser] 2013-12-31T21:48:14Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
delete_list [lists appuser] 2013-12-31T21:49:41Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a list.
flips [appuser users] 2013-12-31T21:53:03Z Marge N. O’Vera <marge@example.com> # Adds table for storing flips.
insert_flip [flips appuser] 2013-12-31T21:56:12Z Marge N. O’Vera <marge@example.com> # Creates a function to insert a flip.
delete_flip [flips appuser] 2013-12-31T21:56:22Z 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
- appuser ................... ok
Deploying changes to flipr_test
+ appuser ................... 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 d813f7c] 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-mysql.pod view on Meta::CPAN
DELIMITER //
+DROP FUNCTION change_pass;
CREATE FUNCTION change_pass(
nickname VARCHAR(512),
oldpass VARCHAR(512),
And now we're ready to try a deployment:
> sqitch deploy
Deploying changes to flipr_test
+ insert_user .. ok
+ change_pass .. ok
So, are the changes deployed?
> mysql -u root -D flipr_test --execute "
DELETE FROM users;
CALL insert_user('foo', 'secr3t');
CALL insert_user('bar', 'secr3t');
SELECT * FROM users;
"
+----------+---------------+----------------------------+
| nickname | password | timestamp |
+----------+---------------+----------------------------+
| bar | 0aasvM1.AzY0Y | 2013-12-31 22:14:45.554942 |
| foo | 80v1DpnRrqbwo | 2013-12-31 22:14:45.554457 |
+----------+---------------+----------------------------+
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 delete_flip @v1.0.0-dev2 from flipr_test
- change_pass .. ok
- insert_user .. ok
Did that work, are the C<MD5()> passwords back?
> mysql -u root -D flipr_test --execute "
DELETE FROM users;
CALL insert_user('foo', 'secr3t');
CALL insert_user('bar', 'secr3t');
SELECT * FROM users;
"
+----------+----------------------------------+----------------------------+
| nickname | password | timestamp |
+----------+----------------------------------+----------------------------+
| bar | 9695da4dd567a19f9b92065f240c6725 | 2013-12-31 22:15:29.843140 |
| foo | 9695da4dd567a19f9b92065f240c6725 | 2013-12-31 22:15:29.842700 |
+----------+----------------------------------+----------------------------+
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<ENCRYPT()>? I think the simplest thing to do is to
examine the body of the function as returned by
L<C<INFORMATION_SCHEMA.ROUTINES>|https://dev.mysql.com/doc/refman/5.7/en/information-schema-routines-table.html>
So the C<insert_user> verify script looks like this:
-- Verify flipr:insert_user on mysql
BEGIN;
SELECT sqitch.checkit(COUNT(*), 'Procedure "insert_user" does not exist or is not up-to-date')
FROM information_schema.routines
WHERE routine_schema = database()
AND routine_name = 'insert_user'
AND routine_definition LIKE '%ENCRYPT(md5(password), md5(FLOOR(RAND() * 0xFFFFFFFF))%';
ROLLBACK;
And the C<change_pass> verify script looks like this:
-- Verify flipr:change_pass on mysql
BEGIN;
SELECT sqitch.checkit(COUNT(*), 'Procedure "change_pass" does not exist or is not up-to-date')
FROM information_schema.routines
WHERE routine_schema = database()
AND routine_name = 'change_pass'
AND routine_definition LIKE '%ENCRYPT(md5(oldpass), password)%';
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 encrypt() to encrypt passwords.'
[main abcce73] Use encrypt() to encrypt passwords.
13 files changed, 137 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: 6f2e1cd4b1c031a66930811328cfcdb0389d8320
# Name: change_pass
# Deployed: 2013-12-31 14:16:45 -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
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
( run in 0.524 second using v1.01-cache-2.11-cpan-39bf76dae61 )