Tag: DB Automation

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 🙂

Build & Release Challenges : Manual DB Updates

The first problem that I’m gonna discuss is manual db updates. In our current application we do have automated DB updates execution in the production environment, but not in the rest of environments i.e dev, qa, stage, performance test … etc.

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

    Prev
    Next