Fail Upon Skipped Versioned Migration

[zanebclark]

The Problem

I use the epoch time as the version number when creating versioned scripts. This almost removes the version-number guesswork from the creation of a new script. Here's the exception:

Two developers are creating scripts around the same time. Or, one dev starts work on a script and has to come back to it after making some other small change:

• V1702512738__some_change_requiring_a_lengthy_review.sql
• V1702512750__a_small_change.sql

Because it is smaller or easier to review, the V1702512750 script is applied by Schemachange first. When the V1702512738 script is merged into the branch in question, Schemachange silently ignores it. I mean, it isn't silent. Somewhere in the thousands of lines of logging, there's a line about it. The dev could check the logs or review Snowflake to ensure that the changes "took", but I think we can do better than that.

The Ask

I'd like to support a flag that would cause schemachange to fail if both of the following are true:

• A versioned script isn't already applied and
• The script won't be applied because the max version number is already greater than the script version

In fetch_change_history, we're already fetching information from the change_history_table about versioned scripts. Instead of get_alphanum_key(script['script_version']) <= get_alphanum_key(max_published_version), we could retrieve the change_history_table record when iterating over the repository scripts. If there's no change history table record to speak of and the "fail on missing" flag is true, throw an exception. While we're at it, we could compare the has of the versioned script to what was recorded in the change history table. If that value differs, a warning would suffice.

If you're assigning arbitrary version numbers (e.g. 12345), this functionality is baked in. The process of provisioning a version number would likely involve reviewing existing script version numbers and selecting the next available number. If two developers use this process and arrive at the same number, Schemachange will complain about the duplicated version number and fail the second migration.

I can implement this in a fork, but I'd like to do so with feedback. Useful? Monstrous? Thanks for reading!