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



Friday, April 17, 2009

Remote debugging using IntelliJ

I had few stand alone java processes running that are not started from the IDE which I had to debug from IntelliJ, it took quite some time for me to figure out how to do that, so I thought will blog it for people who might need it.

It’s quite simple all you have to do is, run the java process which ever you want to debug using the following flags for java command.

$ java -Xdebug -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=9995 RemoteProcess

Where:

9995 is the port on which the IntelliJ will hook on to your remote java process for debugging information. This can be any port as long is it is not being used by other process.

RemoteProcess is a stand alone java process which you want to debug.

Once the java process is up and running, here is what you need to do in IntelliJ to start debugging that process.

Make sure you have the code base of the java process you are running opened in the IDE, go to Run -> Edit Configurations which opens up the config window as shown below.


Click on the + symbol on the top left corner of the window, which prompts you to select one of the different available configurations, select Remote from the given list as shown in the picture below.


It adds a new remote configuration and the config window is defaulted with few values as shown in the picture below.


Fill in the window with the values as described below.

Name text field: Enter the remote process name (can be any string, its just an identifier for your remote process)

Following values are for the settings section.

Transport: Select "Socket" option.

Debugger Mode: Select "Attach" option.

Host: Enter the hostname or IP address of the host on which the remote java process is running, if the java process is running on the same machine as of IntelliJ, you can just fill in as "localhost" as the value for this field.

Port: Enter the same port which your have used in the debugging flags while starting the remote java process. In this particular example it is 9995.

Click on Apply button and save the configuration.

That's it you are ready to debug your remote java process.

To start the debugging session, go to Run -> Debug, from the left pane select the configuration you just savedin the previous step and hit the Debug button. IntelliJ starts the debugging session immediately.