Managing updates to static data
adamknight84
Posts: 2
We are using the DLM automation tools for CI and deployments. We use Jenkins for CI and Octopus Deploy for deployments. We are having a problem when it comes to static data. We store static data in SQL Source Control and we have problems when it comes to managing this data through the CI process. The deployment scripts generated by the CI handle the static data as the last step and outside of the flow of commits therefore you cannot have any dependency on static data in migration scripts.
Below is a simplified example highlighting our problem.
We have a “Permissions†table as static data which contains a list of user permissions for our application.
Suppose we have a permission called “Update User†which allows a user to perform any update on a user whether it be modifying it, deleting it etc.
Suppose we now decide that we want to split this permission into 2 permissions “Modify User†and “Delete Userâ€. We want to add these 2 new permissions, update all users that have the “Update User†to now have these 2 new permissions and then remove the “Update User†permission.
We cannot figure out how to do this kind of change. If we modify the static data to add the 2 new permissions and remove the old one and write a migration script to remap the permissions it will fail because at the time the migration script is run the 2 new permissions do not exist. If we add the 2 new permissions in the migration script it also fails because it then tries to insert the 2 new permissions after the migration script runs but can’t because they already exist.
I could see how to do this if static data was handled in commit order. I could add the 2 new permissions and commit. I could add a migration script to remap the permissions and commit. Finally I could remove the old permission and commit. But because static data is handled entirely separately how are we suppose to handle this situation?
Perhaps permissions is not the best example but it highlights the general problem. How do you manage changes to static data that require other data to be modified accordingly?
Any advice on this would be much appreciated.
Below is a simplified example highlighting our problem.
We have a “Permissions†table as static data which contains a list of user permissions for our application.
Suppose we have a permission called “Update User†which allows a user to perform any update on a user whether it be modifying it, deleting it etc.
Suppose we now decide that we want to split this permission into 2 permissions “Modify User†and “Delete Userâ€. We want to add these 2 new permissions, update all users that have the “Update User†to now have these 2 new permissions and then remove the “Update User†permission.
We cannot figure out how to do this kind of change. If we modify the static data to add the 2 new permissions and remove the old one and write a migration script to remap the permissions it will fail because at the time the migration script is run the 2 new permissions do not exist. If we add the 2 new permissions in the migration script it also fails because it then tries to insert the 2 new permissions after the migration script runs but can’t because they already exist.
I could see how to do this if static data was handled in commit order. I could add the 2 new permissions and commit. I could add a migration script to remap the permissions and commit. Finally I could remove the old permission and commit. But because static data is handled entirely separately how are we suppose to handle this situation?
Perhaps permissions is not the best example but it highlights the general problem. How do you manage changes to static data that require other data to be modified accordingly?
Any advice on this would be much appreciated.
