Maven, SQL and ordered execution

[tweetmeme source=”anismiles” only_single=false http://www.URL.com%5D

I have been re-architecting this new project. This is huge. And broken. And I was called upon to clean, refactor and re-architect it. Phew!

I thought a good strategy would be to deal with it in outside-in style, which is, fixing the build part first then moving down to various parts. One of the interesting things that they had done was using Maven to populate PostgreSQL DB Schemas and then load data into the DB.

Steps of execution

1. Drop everything
2. Create users
3. Create schemas
4. Load data
5. Load upgrade data

They used sql-maven-plugin underwhich they had multiple execution tasks. The whole thing was very untidy and I didn’t like it.

Summary

1. They used <fileSets> to import sql files. FileSets are an elegant choice if you want to import files with ANT like wildcard patterns. But the files are alphabetically sorted. So, unless you don’t care about the order in which the files will be executed, you should avoid using FileSets. In their case, they wanted to run ‘drop_users_databases.sql’ before ‘create_users_databases.sql’. With FileSets, it was impossible to do, they would be re-ordered just the opposite. Solution? They should have used <srcFiles>, but instead they created 2 separate <execution> tasks to run in order. Too much verbosity. Eh?
2. Another issue was with loading upgrade data. Now, they follow agile methodologies and keep updating their DB schemas, and so after few iterations, before they merge the changes in the main script, they end up with a number of files like upgrade-schema-2.0.4.sql, upgrade-schema-2.0.5.sql, upgrade-schema-2.0.5.1.sql and so on. Pain is, they don’t have any automated mechanism to execute these files in order, so the developers would be forced to do it manually. If the number of these files were low, that wouldn’t have been a problem… but how about thirty or forty such files? Pissed off.

Strategy

1. Kick <fileSets> out of the window
2. Use <srcFiles> instead (because I cared more about order)
3. Logically partition the operations
4. Create a new profile to help run these DB executions at will

Code

<profiles>  
<profile>  
	<!-- profile id -->
	<id>init-db</id> 
	<build>
		<plugins>
			<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>sql-maven-plugin</artifactId>
				
				<!-- postgresql dependencies -->	
				<dependencies>
				  <dependency>
					<groupId>postgresql</groupId>
					<artifactId>postgresql</artifactId>
					<version>8.3-603.jdbc4</version>
					<scope>clean</scope>
				  </dependency>
				</dependencies>
				
				<!-- DB Configuration -->
				<configuration>
					<url>jdbc:postgresql:my_db</url>
					<driver>org.postgresql.Driver</driver>
					<username>user</username>
					<password>pass</password>
					<autocommit>true</autocommit>							
				</configuration>
				
				<executions>
					<!-- 1. drop everything, create users and load data -->
					<execution>
						<id>drop-and-create-database</id>
						<phase>clean</phase>
						<goals><goal>execute</goal></goals>
						<configuration>
							<srcFiles>
								<srcFile>src/main/sql/drop_users_databases.sql</srcFile>						
								<srcFile>src/main/sql/create_users_databases.sql</srcFile>						
								<srcFile>src/main/sql/create_schema.sql</srcFile>						
								<srcFile>src/main/sql/load_data.sql</srcFile>						
								<srcFile>src/main/sql/test_data.sql</srcFile>						
							</srcFiles>    															
						</configuration>
					</execution>			
					<!-- 2. run all upgrade scripts -->
					<execution>
						<id>upgrade-schema</id>
						<phase>clean</phase>
						<goals><goal>execute</goal></goals>
						<configuration>
							<srcFiles>
								<srcFile>src/main/sql/upgrade-schema-2.0.6.sql</srcFile>						
								<srcFile>src/main/sql/upgrade-schema-2.1.5.sql</srcFile>						
								<srcFile>src/main/sql/upgrade-schema-2.1.7.sql</srcFile>						
								<srcFile>src/main/sql/upgrade-schema-2.1.8.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-2.2.1.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.0.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.1.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.2.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.3.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.4.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.5.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.6.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.6.1.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.8.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.9.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.10.sql</srcFile>
								<srcFile>src/main/sql/upgrade-schema-3.0.11.sql</srcFile>
							</srcFiles>    						
						</configuration>
					</execution>
				</executions> 
			</plugin>
		</plugins>  
	</build>  
</profile>  
</profiles>  	  

How to run?

Note that I have bound the executions with phase ‘clean’ within a profile with id ‘init-db’. So, I just need to let maven know about the phase and profile, like this:

mvn -Pinit-db clean

I hope this will help someone in need. I will post more adventurous code cleaning stuffs as I will encounter them. Cheers!

Postgres Enum with Hibernate

[tweetmeme source=”anismiles” only_single=false http://www.URL.com%5D

In a running project, we had to map Postgres Enum onto Java Enum using Hibernate. It turned out to be more tricky than we had assumed initially. So, I thought a little tutorial here might save your time if you are stuck in the similar situation.

I am taking an example of Person-Gender mapping where Gender is of enum type.

1. First, create proper Enums and Tables in Postgres database. Here is the script.

   create type genderType as enum (
   	'MALE', 'FEMALE'
   );
   
   create table person (
   	personid serial NOT NULL,
   	gender genderType not null
   );
   

2. Then, you need to define you Enum class.

   package com.impetus.ilabs.entity;
   
   public enum GenderType {
       MALE, FEMALE;
   }
   

3. Now, define your Entity class like this,

   package com.impetus.ilabs.entity;
   
   import java.io.Serializable;
   
   public class Person implements Serializable {
   
   	private Integer personId;
   	
   	private GenderType gender;
   
   	public Integer getPersonId() {
   		return personId;
   	}
   
   	public void setPersonId(Integer personId) {
   		this.personId = personId;
   	}
   
   	public GenderType getGender() {
   		return gender;
   	}
   
   	public void setGender(GenderType gender) {
   		this.gender = gender;
   	}
   
   	@Override
   	public String toString() {
   		StringBuilder builder = new StringBuilder();
   		builder.append("Person [gender=");
   		builder.append(gender);
   		builder.append(", personId=");
   		builder.append(personId);
   		builder.append("]");
   		return builder.toString();
   	}
   }
   

4. This is the magical part. You need to implement a custom UserType that can understand Enum objects. Here is an implementation of PGEnumUserType.

   package com.impetus.ilabs;
   
   import java.io.Serializable;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Types;
   import java.util.Properties;
   
   import org.hibernate.HibernateException;
   import org.hibernate.usertype.EnhancedUserType;
   import org.hibernate.usertype.ParameterizedType;
   import org.postgresql.util.PGobject;
   
   // This implementation works only with Postgres
   public class PGEnumUserType implements EnhancedUserType, ParameterizedType {
   	// Enum  class under observation
   	private Class<Enum> enumClass;
   
   	public void setParameterValues(Properties parameters) {
   		String enumClassName = parameters.getProperty("enumClassName");
   		try {
   			enumClass = (Class<Enum>) Class.forName(enumClassName);
   		} catch (ClassNotFoundException cnfe) {
   			throw new HibernateException("Enum class not found", cnfe);
   		}
   	}
   
   	public Object assemble(Serializable cached, Object owner)
   			throws HibernateException {
   		return cached;
   	}
   
   	public Object deepCopy(Object value) throws HibernateException {
   		return value;
   	}
   
   	public Serializable disassemble(Object value) throws HibernateException {
   		return (Enum) value;
   	}
   
   	public boolean equals(Object x, Object y) throws HibernateException {
   		return x == y;
   	}
   
   	public int hashCode(Object x) throws HibernateException {
   		return x.hashCode();
   	}
   
   	public boolean isMutable() {
   		return false;
   	}
   
   	public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
   			throws HibernateException, SQLException {
   		Object object = rs.getObject(names[0]);
   		if (rs.wasNull()) {
   			return null;
   		}
   
   		// Notice how Object is mapped to PGobject. This makes this implementation Postgres specific
   		if (object instanceof PGobject) {
   			PGobject pg = (PGobject) object;
   			return Enum.valueOf(enumClass, pg.getValue());
   		}
   		return null;
   	}
   
   	public void nullSafeSet(PreparedStatement st, Object value, int index)
   			throws HibernateException, SQLException {
   		if (value == null) {
   			st.setNull(index, Types.VARCHAR); 
   			// UPDATE: To support NULL insertion, change to: st.setNull(index, 1111);
   		} else {
   			// Notice 1111 which java.sql.Type for Postgres Enum
   			st.setObject(index, ((Enum)value), 1111);
   		}
   	}
   
   	public Object replace(Object original, Object target, Object owner)
   			throws HibernateException {
   		return original;
   	}
   
   	public Class returnedClass() {
   		return enumClass;
   	}
   
   	public int[] sqlTypes() {
   		return new int[] { Types.VARCHAR }; 
   		// UPDATE: To support NULL insertion, change to: return new int[] { 1111 };
   	}
   
   	public Object fromXMLString(String xmlValue) {
   		return Enum.valueOf(enumClass, xmlValue);
   	}
   
   	public String objectToSQLString(Object value) {
   		return '\'' + ((Enum) value).name() + '\'';
   	}
   
   	public String toXMLString(Object value) {
   		return ((Enum) value).name();
   	}
   }
   

5. Okay, so the tricky part is over. Now, you need to write Hibernate Mapping xml for Person class.

   <?xml version="1.0"?>
   <!DOCTYPE hibernate-mapping PUBLIC
           "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
           "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
   
   <hibernate-mapping>
   	<class name="com.impetus.ilabs.entity.Person" table="person">
   		<id name="personId" type="java.lang.Integer"
   			column="personid">
   			<generator class="native"></generator>
   		</id>
   
   		<property name="gender">
   			<type name="com.impetus.ilabs.PGEnumUserType">
   				<param name="enumClassName">com.impetus.ilabs.entity.GenderType</param>
   			</type>
   		</property>
   	</class>
   </hibernate-mapping>
   

6. Done! I am including a sample usage class, but you don’t worry about this part, you are good with whatever DAO implementation you might have.

   package com.impetus.ilabs;
   
   import org.hibernate.Session;
   import org.hibernate.SessionFactory;
   import org.hibernate.cfg.Configuration;
   
   import com.impetus.ilabs.entity.GenderType;
   import com.impetus.ilabs.entity.Person;
   
   public class App {
   	
   	private static final SessionFactory sessionFactory = buildSessionFactory();
   
   	private static SessionFactory buildSessionFactory() {
   		try {
   			return new Configuration().configure().buildSessionFactory();
   		} catch (Throwable ex) {
   			throw new ExceptionInInitializerError(ex);
   		}
   	}
   	
   	private static void save (Person person) {
   		Session session = sessionFactory.openSession();
   		session.beginTransaction();
   			
   		// create object
   		session.save(person);
   		session.getTransaction().commit();
   	}
   
   	private static void load (int id) {
   		Session session = sessionFactory.openSession();
   		session.beginTransaction();
   			
   		System.out.println (session.load(Person.class, id));
   		session.getTransaction().commit();
   	}
   
   	public static void main(String[] args) {
   		// create object
   		Person person = new Person();
   		person.setGender(GenderType.MALE);
   		save(person);
   		
   		// load object
   		load(person.getPersonId());
   	}
   }
   

Enjoy! By the way, the whole thing that is described above is also available as maven project to download.