Implement Flyway actions

[srcshelton]

Flyway 4.x supports migrate, clean, info, validate, baseline, and repair.

myway supports migrate (explicitly or by default), --clear-metadata (to drop the myway metadata *) and includes dropschema.sh (to drop the specified databases), whilst --check is accepted as an argument but does nothing.

* --clear-metadata used to re-create blank metadata tables after the existing ones were removed, but this didn't appear to be a good fit for the intent of the action. Flyway clean leaves the databases themselves in-place, and so is more equivalent to running dropschema.sh and then running myway.pl --init.

info looks similar to what would be provided by Issue #14, and baseline can be achieved by providing a parameter to --init or in a more involved but more concrete fashion be using an environment-specific placeholder/migration-schema.

validate is an interesting concept - rather than checking the validity of the schema-files themselves, it instead compares the available schema-files against what has already been applied to the database. We could use myway_schema_version contents to confirm that schema-files checksums have not changed with a simple check.

repair appears to be a cleanup option which removes metadata entries denoting failures (for databases not supporting DDL transactions, such as MySQL) and recalculates the Flyway-specific checksums... both of which sound as if they could do more harm than good.

[srcshelton]

A cleanup function could be very useful though, especially for objects which can grow very large such as the myway_stored_procedures table, which will currently gain an entry per stored-procedure per invocation.

This is likely preferable to repeatable files overwriting entries for previous runs of the same data, affecting the ability to reconstruct previous actions.

[srcshelton]

Flyway also supports target, which broadly matches our version.max/target-limit, except that Flyway will ignore higher versions and also supports current as a value to specify 'the current version of the schema' - which could mean the current highest version on-disk (e.g. equivalent to not specifying the option) or could mean the highest version in the database (e.g. equivalent to doing nothing, unless other options re-write history)... the documentation doesn't clarify.

Currently, validateschema.sh will see having on-disk versions which are higher than the target limit as an error (because if you don't want to deploy them, why are they there?) but myway.pl will happily migrate up to the limit specified and then exit appropriately, regardless of whether any further files remain (as it has performed its task correctly, and migrated to the point specified).

There have been conversations as to whether myway.pl should adopt the validateschema.sh convention in this respect, but I see validation and application as two separate domains, and that this transpires to match the Flyway approach strengthens this argument in my view.

Other Flyway options:

• outOfOrder (apply previously-missing interim versions rather than skipping them - myway validates entire version-chains, and so would have stopped at the first gap. This is similar to our handling of hotfix versions, which are always applied if they aren't already present);
• validateOnMigrate (automatically re-write checksums during migration)
• cleanOnValidationError (erase the entire database on error!)
• ignoreFutureMigrations (don't fail if any versions recorded in the schema_version table are ahead of the highest version on-disk)
• cleanDisabled (don't allow databases to be erased)
• baselineOnMigrate (initialise a database with content but no metadata to ${baselineVersion:-1} before applying migrations)

Not all of these would be suitable for myway's more rigorous approach to validating versions and maintaining history...

[srcshelton]

Flyway info displays:

• 'Version' (version)
• 'Description' (description)
• 'Installed on' (installed_on)
• 'State' (???)

... with the documentation showing a 'State' of Pending. This is interesting, because it shows this state for three versions concurrently, and because the Flyway metadata doesn't really have a way to record this: there is the success field, but this is a BOOLEAN NOT NULL type, and therefore cannot be used as a tri-state. Potentially this could be used for entires with an execution_time of zero or some magic value (negative? This field isn't UNSIGNED), or it could be - as is much more likely - denoting schema which exist on disk but not in the database.

What would be applied in myway terms is a --dry-run action, and any reporting of database state would appear better suited if it limits itself to only that state recorded in the database, regardless of what may or may not be available on-disk.