sqitchers / sqitch Goto Github PK

If a change is deployed to a database, but then later tagged, there needs to be a way to apply that tag to the existing named change in the database. Maybe with a retag command? Should be automatic for the most recently-applied tag.

Need a command to change dependencies. Maybe sqitch revise? Too close to rework? Maybe just sqitch require $change $dep and sqitch conflict $change $dep?

I've added App::Sqitch::X; we should use that for all exceptions in non-command code. Some commands might still fail or bail, but really only if they need to exit with no message (such as for a command with no results where results were expected). Otherwise, all code should use hurl with messages localized via Locale::TextDomain. And then go() should be modified in App:Sqitch to catch such exceptions and do the right thing with them (probably call fail($@->message)).

Currently, this is verboten:

sqitch add foo_

This is because the regular expression matching names uses the [:punct:] character class to match punctuation, and that class apparently includes underscores. This is…unfortunate. So update that regular expression to use a character class that excludes _.

Rather than relying on the most recently-applied change, consider storing the current change name (and ID) in the projects table. This will remove ambiguity. Also consider modifying the ID stored in the database to depend on parents as part of how it is created, so that if order is changed, we can detect that and refuse to deploy. Revert would still have to work, though.

It looks like every key in a deploy.variables section is converted to lowercase, thus unusable as a psql variable if spelled in all caps in the scripts.

This limitation does not exists with the overriding command line argument, --set, which preserves the case.

Currently during sqitch deploy, Sqitch checks the dependencies (requires and conflicts) for each change before it is deployed. The downside is that one could get halfway through a deploy before a deployment fails and triggers a reversion. Better would be to validate the dependencies of all changes to be deployed before deploying any of them. That way, it can fail early, letting the user know what's missing (or conflicting) before making any changes to the database. The same early detection should be applied on reversion (#36), as well.

Study the output of git help and mimic it with a short list of the most important commands. Then add an --all command that lists all commands. Yeah, maybe there won't be that many to start with, but it could add up!

I think that, for now, we should ignore the VCS integration and focus on the plan file. This is because I think it will be the mots important way to plan for most folks, as repositories are not usually that fastidious. I myself often make many, many changes to deployment scripts as I am doing development, and really I just want them to start from a certain point, once I've tagged an app to ship.

With this in mind, I think that add-step should be replaced with a plan command. It will take subcommands to do things with the plan file, such as:

• Add a tag
• Add a step
• Add a step and tag
• Generate a plan file from the dependency graph
• Generate a plan from the VCS history

The second-to-last item there assumes there is no plan file, or a new one needs to be created. The last item allows one to bootstrap a plan from VCS history. This is one of the ways in which a VCS can complement Sqitch.

Can probably get rid of the concept of untracked steps this way, too: if it's not in the plan, it is not known.

First, add a short version of --template-dir to the add command.

But more importantly, add a --template-file option to allow a file to be selected. It should have short options, too.

This command should copy the plan, config, and step scripts to a directory to be bundled for distribution. Probably not worth giving a whole lot of thought to until the plan and VCS integration stuff is all worked out (#10). At that point, we will know for sure what needs to be generated from the VCS history for distribution and what does not.

This would be like log, but instead of showing what has been committed to the database, it would show what's in the plan.

• Allow searching by dates. Would probably have to use DateTime::Format::Flexible or similar to parse user-specified DateTime formats.
• Allow searching of email addresses. Right now, the --committer and --planner options allow regex searches of user names. We should also support email addresses. Might be useful to follow git log and have --committer and --planner support the combination of user name and email address…
• Allow searching for a list of changes, e.g., sqitch log foo..bar or sqitch plan @HEAD^4.. or even sqitch log @HEAD.
• Allow searching by planner in log.

Will add more items as I think of them.

Will run all scripts in the verify directory that correspond to currently-deployed changes. For reworked changes, only the most recent instance of the script should be run.

Also add a --verify option to deploy and rebase that will verify each step after it is deployed, and optionally roll back if the verify fails.

The thing that runs the scripts will vary by engine, but should be a reasonable default that can be replaced by a config variables. A verify script will be considered to have failed it the command does not return success (exitval 0).

The new core.uri setting should only be on a per-project basis. And I think I am going to want a project name config, too. So add a new config section, "project", and change the config command to disallow setting any of its variables anywhere other than in the project ("local") configuration file. And maybe throw an error if Config.pm sees it coming from another file? Then, of course, add support for it to Sqitch.pm.

This command runs some sanity checks and outputs a report on what it found. Some of the checks I'm thinking it should have:

• Steps missing from the plan
• Dependency chain problems (cycles, missing dependencies?)
• Invalid plan file formatting
• Invalid config file formatting
• Missing step and tag deployments in the database
• Missing tags in the database
• Disagreements as to deployment order between the plan and what is deployed to the database

I'm sure we can think of more things as time goes on. Maybe not all will run by default, some turned on by --enable- options or -all. Or maybe we just always run everything.

The revert command does not validate dependencies. It should be updated to check that a change to be reverted is not required by some other change.

Discovered while building a plan with long lists of dependencies. I passed -r foo:bar twice, and when I deployed, I got an error:

Deploying changes to reporting_shard
  + this_and_that
ERROR:  duplicate key value violates unique constraint "dependencies_pkey"
DETAIL:  Key (change_id, dependency)=(034840a070e5323003cf0e20babd7cfc901a2fab, foo:bar) already exists.
  - this_and_that
Deploy failed

Easy enough to fix by editing the plan, but better would to just uniq all the dependencies (and, while at it, make sure that requires and conflicts don't have dupes between them, either).

Similar to how they work for the log command. Maybe. If anyone really cares.

Just wondering if you were fixed on Moose, as opposed to Moo? Just to get the usage message on my machine takes nearly a second:

$ time ./t/sqitch 
...
real    0m0.858s
user    0m0.764s
sys 0m0.028s

This is unfortunately not a great advertisement for a tool written in Perl. Command implementation taking as long as they need is fine, but a wrong option (or give me the list of commands etc) is not so great.

If Moo is not your flavour of objects, would you consider patches which separated out the command-line handling from the command implementation?

In register_project, we currently ignore duplicate violations. However, to protect from the case that two different projects have the same name, we should also check the URI. That is, when a project already exists with the specified name, but its URI is different (or NULL vs. not NULL), we should die then, too. This will minimize the chances of two different projects claiming the same project name.

In the add command, use Template Toolkit for template processing if it's installed. If it's not installed, still use Template::Tiny.

Sometimes a database is built from multiple projects, with their own plans and changes. Currently, one can keep then independent by having them each use a different schema or prefix for its metadata tables. But more convenient would be a way to have just one set of metadata tables for them all, and for them to be able to specify dependencies on each other.

The dependency specification would be the trickier part. Maybe each project should have a name, as well as a URI, and dependencies can be specified via $name:$changespec? The plan parser would know not to complain if such a dependency was missing from the plan itself, while deploy would be able to find them and complain. I think the simplest way for it to find them would be to include the project name in the info method (which is hashed for the change ID). I guess we could also still use URIs instead of names, using something like $uri:$changespec in the plan, but it would be annoyingly long and tedious to type on the command-line.

The _parse method’s use of (?{}) in regular expressions does not take backtracking into account.

Instead of push @req, $^N, you need local @req = (@req, $^N). And at the end of the regular expression, you need another code block to assign @req to a different array, so the undoing of the localisation that occurs when the regexp is exited does not clobber the results.

Believe me, I’ve done this before. See CSS::DOM::PropertyParser, which generates regexps and uses various hacks to work around the bugs. :-)

This might be a good task for someone familiar with Dist::Zilla. I would like to remove the Build.PL script and use Dist::Zilla for building the Sqitch distribution. Why? Well, besides the fact dzil rocks, I have another thing I want to do with it: I want to add a Dzil plugin that builds the necessary localization libraries in the distribution. We don't need them for development, but will want to build them to ship Sqitch. Basically, it will merely require dzil-ing this `Makefile. See this README for details.

Currently the template directory option seems to fall back to the default template is the template directory path is invalid.

It should throw an error to warn the user instead.

As discussed on IRC, my strawberry 5.14.2 (win xp) won't submit a test report b/c it says some deps are broken, even though cpan won't update those mods b/c it says they're up to date.
So anyway, there are a few test failures.
Let me know if you'd like something more verbose or whatever.
As I do this frequently for my own dists it's fairly easy for me to build a tarball from a repo and run a test there so if you've got a branch or something you'd like me try out just let me know.

CPAN: CPAN::SQLite loaded ok (v0.202)
Database was generated on Mon, 01 Oct 2012 13:22:52 GMT
CPAN: YAML loaded ok (v0.80)
CPAN: CPAN::Kwalify loaded ok (v5.50)
Running make for D/DW/DWHEELER/App-Sqitch-0.934.tar.gz
CPAN: Digest::SHA loaded ok (v5.62)
CPAN: Compress::Zlib loaded ok (v2.052)
Checksum for C:\strawberry\cpan\sources\authors\id\D\DW\DWHEELER\App-Sqitch-0.934.tar.gz ok
CPAN: Archive::Tar loaded ok (v1.88)
CPAN: File::Temp loaded ok (v0.22)
CPAN: Parse::CPAN::Meta loaded ok (v1.4404)
CPAN: CPAN::Meta loaded ok (v2.120921)
CPAN: Module::CoreList loaded ok (v2.60)
CPAN: Time::HiRes loaded ok (v1.9724)

  CPAN.pm: Building D/DW/DWHEELER/App-Sqitch-0.934.tar.gz

CPAN: CPAN::Reporter loaded ok (v1.2004)
Created MYMETA.yml and MYMETA.json
Creating new 'Build' script for 'App-Sqitch' version '0.934'
(C:\strawberry\perl\bin\perl.exe Build.PL  exited with 0)
CPAN::Reporter: Build.PL result is 'pass', No errors.
Building App-Sqitch
(C:\strawberry\perl\bin\perl.exe ./Build exited with 0)
CPAN::Reporter: Build result is 'pass', No errors.
  DWHEELER/App-Sqitch-0.934.tar.gz
  C:\strawberry\perl\bin\perl.exe ./Build -- OK
Running Build test
t/add.t ............ ok
'2' is not recognized as an internal or external command,
operable program or batch file.
Error closing pipe to C:\STRAWB~1\perl\bin\perl.exe: Inappropriate I/O control operation
Trace begun at C:\strawberry\cpan\build\App-Sqitch-0.934-az0BsH\blib\lib\App\Sqitch.pm line 449
App::Sqitch::spool('App::Sqitch=HASH(0x219ef7c)', 'GLOB(0x2193894)', 'C:\STRAWB~1\perl\bin\perl.exe', 'read.pl') called at t\base.t line 304
main::__ANON__ at C:\strawberry\perl\site\lib\Capture\Tiny.pm line 362
eval {...} at C:\strawberry\perl\site\lib\Capture\Tiny.pm line 362
Capture::Tiny::_capture_tee(1, 0, 0, 0, 'CODE(0x2198a2c)') called at t\base.t line 305
# Looks like you planned 95 tests but ran 90.
# Looks like your test exited with 1 just after 90.
t/base.t ........... 
Dubious, test returned 1 (wstat 256, 0x100)
Failed 5/95 subtests 
t/blank.t .......... ok

#   Failed test 'Should have no mkdir output'
#   at t/bundle.t line 171.
#     Structures begin differing at:
#          $got->[0] = ARRAY(0x21fff54)
#     $expected->[0] = Does not exist
# Looks like you failed 1 test of 125.
t/bundle.t ......... 
Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/125 subtests 
t/change.t ......... ok
t/changelist.t ..... ok
t/command.t ........ ok
t/config.t ......... ok
t/configuration.t .. ok
Invalid locale name or id: English_United States.1252
# Looks like you planned 33 tests but ran 4.
# Looks like your test exited with 255 just after 4.
t/datetime.t ....... 
Dubious, test returned 255 (wstat 65280, 0xff00)
Failed 29/33 subtests 
t/depend.t ......... ok
t/deploy.t ......... ok
t/engine.t ......... ok
t/help.t ........... ok
t/init.t ........... ok
t/linelist.t ....... ok
Invalid locale name or id: English_United States.1252
# Looks like your test exited with 255 just after 45.
t/log.t ............ 
Dubious, test returned 255 (wstat 65280, 0xff00)
All 45 subtests passed 
t/options.t ........ ok
t/pg.t ............. ok
t/plan.t ........... ok
t/pod-coverage.t ... skipped: Test::Pod::Coverage 1.08 required for testing POD coverage
t/pod.t ............ skipped: Test::Pod 1.41 required for testing POD
t/pragma.t ......... ok
t/revert.t ......... ok
t/rework.t ......... ok
t/sqlite.t ......... ok
t/status.t ......... ok
t/tag.t ............ ok
t/tag_cmd.t ........ ok
t/x.t .............. ok

Test Summary Report
-------------------
t/base.t         (Wstat: 256 Tests: 90 Failed: 0)
  Non-zero exit status: 1
  Parse errors: Bad plan.  You planned 95 tests but ran 90.
t/bundle.t       (Wstat: 256 Tests: 125 Failed: 1)
  Failed test:  42
  Non-zero exit status: 1
t/datetime.t     (Wstat: 65280 Tests: 4 Failed: 0)
  Non-zero exit status: 255
  Parse errors: Bad plan.  You planned 33 tests but ran 4.
t/log.t          (Wstat: 65280 Tests: 45 Failed: 0)
  Non-zero exit status: 255
Files=30, Tests=3026, 46 wallclock secs ( 0.29 usr +  0.08 sys =  0.37 CPU)
Result: FAIL
Failed 4/30 test programs. 1/3026 subtests failed.
(C:\strawberry\perl\bin\perl.exe ./Build test exited with 65280)

CPAN::Reporter: test results were not valid, Prerequisite version too low:
requires:

    Module                         Need    Have    
    ------------------------------ ------- --------
    Config                         0       0       
    Config::GitLike                1.09    1.09    
    constant                       0       1.21    
    DateTime                       0       0.74    
    DBI                            0       1.616   
    Digest::SHA1                   0       2.13    
    Encode                         0       2.44    
    File::Basename                 0       2.82    
    File::Copy                     0       2.21    
    File::HomeDir                  0       0.98    
    File::Path                     0       2.08_01 
    File::Temp                     0       0.22    
    Getopt::Long                   0       2.38    
    Hash::Merge                    0       0.12    
    IO::Pager                      0       0.3     
    IPC::System::Simple            1.17    1.21    
    List::Util                     0       1.23    
    Locale::TextDomain             1.20    1.20    
    Moose                          2.0300  2.0604  
  ! Moose::Meta::Attribute::Native 2.0300  broken  
  ! Moose::Util::TypeConstraints   2.0300  broken  
    MooseX::Types::Path::Class     0.05    0.06    
    namespace::autoclean           0.11    0.13    
    overload                       0       1.13    
    parent                         0       0.225   
    Path::Class                    0       0.26    
    perl                           v5.10.1 5.014002
    Pod::Find                      0       1.51    
    Pod::Usage                     0       1.51    
  ! POSIX                          0       broken  
    Role::HasMessage               0.005   0.005   
    Role::Identifiable::HasIdent   0.005   0.005   
  ! Role::Identifiable::HasTags    0.005   broken  
    StackTrace::Auto               0       0.102080
    strict                         0       1.04    
    String::Formatter              0       0.102082
    Sub::Exporter                  0       0.982   
    Sub::Exporter::Util            0       0.982   
    Sys::Hostname                  0       1.16    
    Template::Tiny                 0.11    1.12    
    Term::ANSIColor                2.02    3.01    
    Throwable                      0       0.102080
    Try::Tiny                      0       0.11    
    URI                            0       1.59    
    User::pwent                    0       1.00    
    utf8                           0       1.09    
    warnings                       0       1.12    
    Win32::Locale                  0       0.04    
    Win32::ShellQuote              0       0.001001

build_requires:

    Module                         Need    Have    
    ------------------------------ ------- --------
    Capture::Tiny                  0.12    0.20    
    File::Spec                     0       3.33    
    FindBin                        0       1.50    
    Module::Build                  0.35    0.3800  
    Test::Deep                     0       0.108   
    Test::Dir                      0       1.014   
    Test::Exception                0       0.31    
    Test::File                     0       1.34    
    Test::File::Contents           0.05    0.20    
    Test::MockModule               0.05    0.05    
    Test::More                     0.94    0.98    
    Test::NoWarnings               0.083   1.03    

configure_requires:

    Module                         Need    Have    
    ------------------------------ ------- --------
    Module::Build                  0.35    0.3800  

.

  DWHEELER/App-Sqitch-0.934.tar.gz
  C:\strawberry\perl\bin\perl.exe ./Build test -- NOT OK
//hint// to see the cpan-testers results for installing this module, try:
  reports DWHEELER/App-Sqitch-0.934.tar.gz
Running Build install
  make test had returned bad status, won't install without force

I want to add a Dzil plugin that builds the necessary localization libraries in the distribution. We don't need them for development, but will want to build them to ship Sqitch. Basically, it will merely require dzil-ifying this `Makefile. See this README for details.

This command should show the current deploy status of the database, as well as any deployments that have yet to be applied. It does this by comparing the plan to what's actually deployed in the database (found in the tags, tag_names, and steps tables in the database).

An example for an up-to-date database:

> sqitch -d flipr_test status
# On database flipr_test
# Tag:  beta
# Step: appuser
# Date: 2012-04-09 18:43:45
#
Nothing to deploy (up-to-date)

Use $sqitch->comment() for the # lines and $sqitch->info() for the bare line. Here's another example, for an out-of-date database:

> sqitch -d flipr_test status
# On database flipr_test
# Nothing deployed.
#
# Changes not deployed:
# * beta
#   + appuser
# * gamma
#   + users_table
#   + insert_user
#   + update_user
#   + delete_user
#
Use "sqitch deploy" to deploy these changes

And here is an example with the --show tags option, which shows all deployed tags:

> sqitch status --show tags
# On database flipr_test
# Tag:  fa650af
# Step: users
# Date: 2012-04-09 20:45:23
#
# Tags:
#    fa650af - 2012-04-09 20:45:23 - david
#    36acafd - 2012-04-09 19:04:50 - david
#
Nothing to deploy (up-to-date)

Maybe that second example should only show the underployed stuff with --show tags and --show steps.

The basic description of the command is here.

If a database’s list of changes and/or tags varies from the plan, we could perhaps use a command to allow the database metdata to be updated so that it is in sync with the plan. Missing tags would be added, missing changes would be deployed. Needs some thought, though.

It would be useful to have a remove command.

This command would take a change name, remove it from the plan and remove the associated deploy, revert and test script.

This should only work when no other change has a dependency on the named change.

So far, untracked steps can be found and deployed under the tag "HEAD+". But there are some things that still need figuring out:

• How to update the tag in the DB once the changes have a real tag.
• How to re-run the deploy for HEAD+, in case there are more untracked changes. Maybe a rebase-style command, where changes are reverted then re-deployed?

If I run sqitch --top-dir sql init, it will fail because sql does not exist. Make sure it exists before trying to open the plan file there.

If no target is specified in sqitch revert, prompt to make sure the user wants to go all the way back to the beginning. Also add a -y option to bypass it.

The plan needs subclasses (or roles?) to support VCS integration. This would included:

• The ability to find source code from the VCS history (change name and tag)
• The ability to read such source code files and pass them to a client. Might need to have them as real files
• The ability to read the VCS history and generate a plan from it, for bootstrapping (#24)
• The ability to add tags to the VCS history via sqitch tag and other automations.

And no doubt other stuff.

Or something similarly named. The idea is that the scripts are not run, but the changes are logged as if they were run. This would allow one to start using Sqitch on an existing project: Port your scripts to Sqitch, then deploy to production with sqitch deploy --no-run. That will bring the state of the database up-to-date with where it was prior to the Sqitchification of the project.

This would allow one to look at the info from a change or tag, or the contents of a script. Similar to git cat-file. Some examples that would show the output from info of a plan object:

sqitch show change df6a24dc05f00328e6c1dc3dca79a4e9e1627dd6
sqitch show change foo
sqitch show @beta1

And these would show the contents of files:

sqitch show deploy users_table
sqitch show revert users_table
sqitch show test users_table

Same as -e in git-cat-file:

-e
    Suppress all output; instead exit with zero status if <object> exists
    and is a valid object.

Not sure what to call it, but the idea is to be able to convert an existing project over to using Sqitch by writing a plan from the VCS history.

I'm trying to understand what purpose revert has, that is different to deploying to a previous step?

The error codes for git config are very useful. Some of them are duplicated in sqitch command, but not all. It could use a bit more work to get them all right.

IOW, allow something like --set in the deploy and revert commands, with add --set as an example. This would allow a run of a script to be passed key/value pairs to affect things, if the engine supports it. For PostgreSQL, these would be passed via psql --set, so that the instances of those variables in deployment scripts could be varied.

The revert command needs to be project-aware: it should only revert changes for the local project, not for any other project.

It's possible to create a plan with dependencies arranged such that changes without mutual dependencies can be ordered differently than they are in the plan. Change the change ordering code to strongly favor the order in which changes appear in the plan, and only re-order when dependencies make the original ordering impossible.

[I'm not sure where you are tracking issue or feature requests so for now here is as good as anywhere I guess]

It may be necessary to use scripts written in languages other than SQL (eg Perl) to perform certain steps when upgrading or downgrading a database. These scripts should also be part of the dependency graph, and of course indempotent. Database connection parameters could be passed in as environment variables or as yaml/json on stdin etc.

With sqitch @ tag 0.51:

ollie@deimos ~/Work/MusicBrainz/musicbrainz-server-db (master) % sqitch 
Synopsis:
      sqitch [<options>] <command> [<command-options>] [<args>]

Options:
         --plan-file  FILE    Path to a deployment plan file.
         --engine     ENGINE  Database engine.
         --client     PATH    Path to the engine command-line client.
      -d --db-name    NAME    Database name.
      -u --username   USER    Database user name.
         --host       HOST    Database server host name.
         --port       PORT    Database server port number.
         --uri        URI     Project unique identifier.
         --top-dir    DIR     Path to directory with plan and scripts.
         --deploy-dir DIR     Path to directory with deployment scripts.
         --revert-dir DIR     Path to directory with reversion scripts.
         --test-dir   DIR     Path to directory with test scripts.
         --extension  EXT     SQL script file name extension.
         --etc-path           Print the path to the etc directory and exit.
         --quiet              Quiet mode with non-error output suppressed.
         --verbose            Increment verbosity.
         --version            Print the version number and exit.
         --help               Print a usage statement and exit.
         --man                Print the complete documentation and exit.

ollie@deimos ~/Work/MusicBrainz/musicbrainz-server-db (master) % sqitch --help
ollie@deimos ~/Work/MusicBrainz/musicbrainz-server-db (master) %

I found this a little confusing. It turns out sqitch help is what I wanted.

There needs to be a way to add revert changes (which begin with -) to the plan, and to, you know, actually revert them from the database at that point. Can probably be ignored for a while, as it's not likely to be a common requirement, but since the plan grammar already supports it, we really ought to do the right thing with it.

The tag command should add a blank line to the plan lines after the tag. Not essential, but makes for nicer reading once additional changes are added.

Or better yet, the add command should add a blank line before the new change if the preceding line is a tag.

To complement deploy

The log command, documented here, has quite a few options to allow for all sorts of interesting ways to query the deployment history of a database. Note that this is different than what can be seen via the status command, which only shows information about the current deployment state. The log command shows everything that has ever been done, both deployments and reversions, without regard to the current state. Think of it like git log, while the status command is more like git status.

The log data can all be found in the events table in the database.