Handle out of order changes to static data

DamianWise

We have a table that we use to store the current version of an application.  A simplified version of it can be thought of as a primary key (auto-incrementing) and a string with the application version number.
We want to treat this data as Flyway static data so that the version number is automatically updated as migration scripts are deployed.  However, we make hot-fixes to our application and this causes problems.
For example, image that we are going to update from version 1.0.0.0 to version 2.0.0.0 and this would be in the static data table with pk = 1 and 2 respectively.  However, before we deploy we make a hot-fix to version 1.0.0.1 and this has a pk = 2.  When we go to deploy version 2.0.0.0 we have a primary key conflict.
This must hold true of any static data that gets changed outside of the normal updates to static data.  Is there some simple or easily maintainable way to handle this in Flyway/Flyway Desktop?

Piers_Williams

Just to ask - what is the final state of the table you are hoping for - something like this?

pk	version
1	1.0.0.0
2	1.0.0.1
3	2.0.0.0

Are you using state based or migration based deployments?

DamianWise

That is what the table would look like after merging from the hot-fix and the feature.  We are using migrations based deployments.

Piers_Williams

Hi,

Did the hotfix get applied to development as well as production? Static data is typically the same across environments.

I wonder if you could instead calculate the versions table from the flyway schema history table instead of maintaining it - for example in SQL Server:


create view versions as select installed_rank as [pk], version from flyway_schema_history;<br>select * from versions;<br>
Creates a table very similar to your desired table given the following migrations were applied:

• V1.0.0.0__First.sql
• V1.0.0.1__Hotfix.sql
• V2.0.0.0__Second.sql