Automated DB Updater

In continuation with my blog series I'm finally introducing a automated db updater tool. You can read about the idea in previous blogs by going to below links

Manual DB Updates challenges
Manual DB Updates challenges-2

The short form of my tool is ADU(Automated DB Updater). Now some details about this tool

Each application will have database_script folder at the root level, this folder will contain folders corresponding to each release i.e release1, release2, release3...

A database release folder will contain

• Meta file :sql_sequence.txt, this file will contain the sequence in which sql files will be executed, only files mentioned in this file will be entertained
• SQL Files : A sql file must have a naming convention like this __.sql/__.sql

Process of automatic execution of scripts on an environment

• Input
• release_name : to figure out the folder from where scripts will be executed
• environment : Environment on which scripts will be executed
• Execution
• sql_sequence.txt file will be read line by line having one sql file name in each line
• The sql file will be verified whether it has been already executed or not
• If the sql file is already executed then two conditions are verified
• A new version of sql should be available
• Undo version of last executed sql should be present
• After execution of undo file the latest version of the sql file will be executed and the info is stored accordingly that it has been executed so that it will not be picked again
• Validations & Boundary Conditions
• All the files mentioned in sql_sequence.txt should exist.
• Undo script should be present for all the versions of a sql file barring the latest version of sql file.
• Undo script will only be executed if next version of script is available.

Very soon I'll share the github url of this project keep waiting :)