view release on metacpan or  search on metacpan

lib/sqitchtutorial-sqlite.pod  view on Meta::CPAN

=back

But you can have Sqitch do most of the work for you. The only requirement is
that a tag appear between the two instances of a change we want to modify. In
general, you're going to make a change like this after a release, which you've
tagged anyway, right? Well we have, with C<@v1.0.0-dev2> added in the previous
section. With that, we can let Sqitch do most of the hard work for us, thanks
to the L<C<rework>|sqitch-rework> command, which is similar to
L<C<add>|sqitch-add>:

  > sqitch rework userflips -n 'Adds userflips.twitter.'
  Added "userflips [userflips@v1.0.0-dev2]" to sqitch.plan.
  Modify these files as appropriate:
	* deploy/userflips.sql
	* revert/userflips.sql
	* verify/userflips.sql

Oh, so we can edit those files in place. Nice! How does Sqitch do it? Well, in
point of fact, it has copied the files to stand in for the previous instance
of the C<userflips> change, which we can see via C<git status>:

  > git status
  # On branch main
  # Your branch is ahead of 'origin/main' by 4 commits.
  #   (use "git push" to publish your local commits)
  #
  # Changes not staged for commit:
  #   (use "git add <file>..." to update what will be committed)
  #   (use "git checkout -- <file>..." to discard changes in working directory)
  #
  #	modified:   revert/userflips.sql
  #	modified:   sqitch.plan
  #
  # Untracked files:
  #   (use "git add <file>..." to include in what will be committed)
  #
  #	deploy/userflips@v1.0.0-dev2.sql
  #	revert/userflips@v1.0.0-dev2.sql
  #	verify/userflips@v1.0.0-dev2.sql
  no changes added to commit (use "git add" and/or "git commit -a")

The "untracked files" part of the output is the first thing to notice. They
are all named C<userflips@v1.0.0-dev2.sql>. What that means is: "the
C<userflips> change as it was implemented as of the C<@v1.0.0-dev2> tag."
These are copies of the original scripts, and thereafter Sqitch will find them
when it needs to run scripts for the first instance of the C<userflips>
change. As such, it's important not to change them again. But hey, if you're
reworking the change, you shouldn't need to.

The other thing to notice is that F<revert/userflips.sql> has changed. Sqitch
replaced it with the original deploy script. As of now,
F<deploy/userflips.sql> and F<revert/userflips.sql> are identical. This is on
the assumption that the deploy script will be changed (we're reworking it,
remember?), and that the revert script should actually change things back to
how they were before. Of course, the original deploy script won't be
L<idempotent|https://en.wikipedia.org/wiki/Idempotence> -- that is, able to be
applied multiple times without changing the result beyond the initial
application. It could be if SQLite supported C<CREATE OR REPLACE VIEW>, but
since it doesn't, we will have to edit the script to drop the view before
creating it. Or, more simply, it needs to be updated to revert changes back to
how they were as-of the deployment of F<deploy/userflips@v1.0.0-dev2.sql>.

Modify F<deploy/userflips.sql> to add the C<twitter> column; in fact, let's
also add a C<DROP VIEW IF EXISTS> statement, in case we need to rework this
change again in the future:

  @@ -4,8 +4,9 @@
 
   BEGIN;
 
  +DROP VIEW IF EXISTS userflips;
   CREATE VIEW userflips AS
  -SELECT f.id, u.nickname, u.fullname, f.body, f.timestamp
  +SELECT f.id, u.nickname, u.fullname, u.twitter, f.body, f.timestamp
     FROM users u
     JOIN flips f ON u.nickname = f.nickname;
 

Next, modify F<verify/userflips.sql> to check for the C<twitter> column.
Here's the diff:

  @@ -2,7 +2,7 @@
 
   BEGIN;
 
  -SELECT id, nickname, fullname, body, timestamp
  +SELECT id, nickname, fullname, twitter, body, timestamp
     FROM userflips
    WHERE 0;
 
And finally, modify F<revert/userflips.sql> to drop the view
before creating it:

  @@ -4,6 +4,7 @@
 
   BEGIN;
 
  +DROP VIEW IF EXISTS userflips;
   CREATE VIEW userflips AS
   SELECT f.id, u.nickname, u.fullname, f.body, f.timestamp
     FROM users u

Note that if we had included that statement when we originally created the
C<userflips> change, we wouldn't have to change this file at all.

Now try a deployment:

  > sqitch deploy
  Deploying changes to flipr_test
	+ userflips .. ok

So, are the changes deployed?

  > sqlite3 flipr_test.db '.schema userflips'
  CREATE VIEW userflips AS
  SELECT f.id, u.nickname, u.fullname, u.twitter, f.body, f.timestamp
    FROM users u
    JOIN flips f ON u.nickname = f.nickname;

Awesome, the view now includes the C<twitter> column. But can we revert?