Investigate using Sqitch for database change management

[Bill Erickson]

Opening this bug to discuss replacing our current home-built database change management process with a 3rd-party application called Sqitch (http://sqitch.org). I have been using this product to manage SQL changes locally for several months and it has worked well thus far.

The main benefits I see are these:

1. No more upgrade file numbering. Upgrades are dependency based.
2. No duplicating SQL in upgrade scripts and baseline schema files. Every change is a deployment (upgrade) script.
3. Encourages the creation of revert and verify scripts. Revert scripts are particularly useful for collaborative development.
4. Well documented and (I think) relatively easy for newcomers to understand.

Concerns:

1. It's fairly new. There are packages for Debian Jessie and Ubuntu Wily. Others install via CPAN.
2. It's a new dependency for packagers and potentially all EG users, depending on whether we replace upgrade scripts entirely.

Postgres Tutorial:

https://github.com/theory/sqitch/blob/master/lib/sqitchtutorial.pod

I have not been using a lot of the features (rework, bundle, rebase, etc.), because I have not needed them. My plan is to create a wiki page documenting how I've been using it.

I also have a master-tracking branch that ports all of our SQL into Sqitch as a proof of concept. I'll document this as well.

[Bill Erickson (berick)]

Proof of concept / migration branch, squashed and rebased to master as of today:

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/berick/lp1521693-sqitch

It's not yet integrated into the standard build process. After installing Sqitch, to build a new DB, do this:

$ cd Open-ILS/src/sql/schema/
$ sqitch deploy

To install the optional concerto data set:

$ cd ../test-data # Open-ILS/src/sql/test-data
$ sqitch deploy

The plan is to track master and migrate each new SQL upgrade script into the Sqitch instance in this branch. (That will, of course, not be in real time).

[Bill Erickson (berick)]

Force-pushed to same branch. eg_db_config now builds the DB from the Sqitch instance, so standard install process/documentation now works.

[Bill Erickson (berick)]

I cross-ported the SQL upgrade from bug #1319998 to bring the code up to date with mater. In this case, I also included revert and verify scripts so I could use the commit as a case study for documentation.

Documentation is here: http://wiki.evergreen-ils.org/doku.php?id=dev:sqitch. Review and comments appreciated.

[Bill Erickson (berick)]

Note to self: document in the wiki the importance of "--log-only" and discuss the implications of the pending "--merge" option. (See also https://github.com/theory/sqitch/issues/200)

[Bill Erickson (berick)]

Rebased to master and cross-ported changes for upgrade scripts 0951 through 0975. Still using user/berick/lp1521693-sqitch.

Adding pullrequest for 2.next for eyes.

[Bill Erickson (berick)]

Added a section to the docs about handling conflicting plan files, e.g. when using a single database to track changes from multiple Git branches. In summary: --log-only is your friend.

http://wiki.evergreen-ils.org/doku.php?id=dev:sqitch#resolving_conflicts_deploying_multiple_plan_files

[Bill Erickson (berick)]

Note to self: add pullrequest after the changes from bug #1564079 and are deployed and cross-ported.

[Bill Erickson (berick)]

Some requests from the EG hackfest discussion:

1. Regular database reification. We want to create a fresh DB export to use as the first step in all new installs at regular intervals (e.g. w/ each major release) so that it's not necessary to install every upgrade script that precedes a given version -- clean up the cruft. This also helps with keeping the install files up to date w/ Postgres (e.g. avoiding deprecated operators).

2. Continue using version upgrade scripts / avoid Sqitch for production.

3. Given #2 above, remove revert scripts when Sqitch commits are merged to master. The idea here is that they should never be used in the mainline code once a change is committed. Faulty SQL changes are replaced with follow-up commits, not reverts. Reverts are still used for development.

4. Explore other Sqitch commands / options. Start with the 'rework' command as a potential for solving the problem of a single function being modified by multiple branches simultaneously.

[Bill Erickson (berick)]

Experiment with database reification (as of SQL change 0980)

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/berick/lp1521693-sqitch-reify-experiment

This branch also avoids adding verify and revert scripts, since they will not be merged to master (and in this case, they were no-ops anyway).

If we continue down this path, it will be imperative that schema and data updates are segregated into their own files (data.* and schema.*) for the reification process to work w/o having to manually extract data updates from combined files.

I have also confirmed there is a usable Sqitch package in Ubuntu 16.04. Yay.

[Bill Erickson (berick)]

I'm putting this on the back burner. It's starting to look like Sqitch may not be the best tool for our needs.

The desire for regular database reification does not integrate easily with Sqitch, which works best with linear series of deployments. Reification in effect squashes and rewrites the deployment history. This could be made to work in Sqitch, but it's not really what it excels at. We'd be fighting the tool as much as using it.

Also the desire to avoid using Sqitch for production deployments suggests maybe it's not the best option. The goal is to make things cleaner/simpler/more cohesive, but as it stands I think we'd be replacing one thing that sort of works with another thing that sort of works.