The process that we use for automated scripts execution in production environment is that we create a release folder, this release folder contains all the sql scripts for the release along with a meta file. The release meta file contains the list of all the scripts that needs to be executed, the current system reads this meta file & executes all the scripts of release. This process is fair enough for production system since the release is deployed only once on production system. In production systems we don’t have to track whether a script got executed or not i.e all the scripts execution is treated as atomic that is either all the scripts are executed or none is executed.
The drawback of atomic execution is the reason because of which this approach can not be applied to rest of the environments, since the db updates will always be incremental in rest of environments. In case of all other environments apart from production environment the release will be deployed multiple number of times, with each release new db scripts can be added to the system and only those new scripts needs to be executed.
The new system that I’m trying to develop will have incremental db update capability. The system that I’m planning to deveplop will have following characterstics :
- It should be able to keep track of script name for later reference.
- It should store the release mapping to which this script belongs.
- The sequence of the script to enforce the order of execution.
- The system should also maintain whether the script is already or not.
- The system should be able to handle error scenario i.e if a script execution fails a corrective action should be taken by the system
- It should be extensible enough so that various kind of reports can be generated from it
In the next blog I’ll be talking about the actual system how it is built
