Automated DB Updater Release First Release

Initial version of Automated DB Updater Release ADU

With this blog I'm releasing the intial version of a python utility to provide automated db updates across various environments for different components.

The code for this utility is hosted on github
https://github.com/sandy724/ADU

You can clone the read only copy of this codebase by url given below
https://github.com/sandy724/ADU.git

To understand the basic idea about this utility go thorugh this blog
http://sandy4blogs.blogspot.in/2013/07/automated-db-updater.html

How to use this utility
    Checkout the code at some directory, add the path of this directory in PYTHONPATH environment variable
    Create a database with a script's metadata table with given below ddl
CREATE TABLE `script_metadata` (
  `name` varchar(100) DEFAULT NOT NULL,
  `version` int(11) DEFAULT NOT NULL,
  `executed` tinyint(1) NOT NULL DEFAULT '0',
  `env` varchar(30) DEFAULT NOT NULL,
  `releas` varchar(30) DEFAULT NOT NULL,
  `component` varchar(30) DEFAULT NOT NULL
)
    Create a database.properties, containing connection properties of each environment database

[common_db]

dbHost=localhost

dbPort=3306

dbUser=root

dbPwd=root

db=test

[env1]

dbHost=localhost

dbPort=3306

dbUser=root

dbPwd=root

db=test

Here common_db represents connection to database which will contain metadata of scripts for monitoring

    Now execute the pythong utility
        Copy the client(updateDB.py) to directory of your choice, make sure that property configration file should also be at this directory
        python updateDB.py -f -r --env

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 :)