Saturday, May 30, 2009

Managing Incremental Database Changes

Managing incremental database changes on different environments is quite tricky especially if the projects have been running for years.

I have seen in lot of teams or projects struggling to manage the incremental changes to their database schemas or the reference data once the system is deployed on different environments.

Few teams do versioning (manage in source control) of all the incremental scripts, but still the problems they tend to have are

* It makes it difficult to identify the sequence of scripts to be run unless a proper naming convention is managed.

* Figuring out which script has run against which environment becomes a bit difficult to manage. Though we know how the database schema has changed over a period of time, to identify the state of the schema in different environments there is no easy way.

* With some kind of convention also over time the number of scripts become very large, which ties back to the same problem of not able to identify the sequence of the scripts.

Well these are just few of the issues.. there are numerous issues when there is no proper mechanism to manage the incremental database schema/data changes.

In of the recent projects I have worked on, the team has come up with a nice way of managing these scripts which is version controlled, has absolutely no problems no matter how many environments the system is deployed on, solves the post live development issues etc.

Am taking a simple database as an example to demonstrate, but the same idea can be extended to any database.

1. A table which can maintain the version of scripts ran on this particular database instance (or environment).



CREATE TABLE ALTER_VERSION (
VERSION NUMBER(10) UNIQUE
);

It has just one column VERSION as the name implies holds the version of the script (schema change / DML)

2. Some mechanism to query the ALTER_VERSION table from PL/SQL block, in database a function can be created which takes in the VERSION (NUMBER data type) as parameter and the function checks if that version is exists in ALTER_VERSION table or not. Sample script goes here.



CREATE OR REPLACE FUNCTION has_alter_version (ver IN NUMBER)
RETURN NUMBER
IS
existing_ver NUMBER := 0;
BEGIN
SELECT count(*) INTO existing_ver FROM alterversion WHERE VERSION = ver;
RETURN existing_ver ;
END;

This function given a version number, checks if it exists in the ALTER_VERSION table, if exists returns 1, else returns 0.

Note: If version exists it returns 1 though we are using count(*) because of the unique constraint in the table ALTER_VERSION.

3. And finally one single sql file which contains all the schema changes and data changes, this file is version controlled and has all the schema/data changes SQLs in the format as given below.



BEGIN
IF has_alter_version(1) = 0 THEN
-- Schema changes or data changes script BEGINS here

CREATE TABLE DUMMY (
COL1 NUMBER(1),
COL2 VARCHAR(10)
);

-- Schema changes or data changes script ENDS here
INSERT INTO ALTER_VERSION VALUES (1);
END IF;
END;
/

BEGIN
IF has_alter_version(2) = 0 THEN
-- Schema changes or data changes script BEGINS here

INSERT INTO DUMMY VALUES (1, 'TEST1');
INSERT INTO DUMMY VALUES (2, 'TEST2');

-- Schema changes or data changes script ENDS here
INSERT INTO ALTER_VERSION VALUES (2);
END IF;
END;
/
As you can see what this script does is checks if a particular alter version exists in the database, if it does not exist, it runs the enclosing scripts. In the first block, it creates a table named DUMMY if an alter version 1 doesn't exist in the database. Similarly the rest of the blocks does the same.

And this script can be automated with the build process before deploying the application on to any environment. Based on the last version created in the environment the build just executes the scripts whose version doesn't exist in the database.

I know I know what you are thinking.. it sounds similar to db-debploy, but its quite simpler than using db deploy, no dependency on any other libraries, customize however you want :)



No comments: