History tables and error logging

[turgayz]

Hi,
[I hope this is the right forum]
I need to track all updates to the database in my application. I mean, all versions of a record must be kept.
For example, in my DAO, if I have a method like

public User update_updateUser(User user) {
  getHibernateTemplate().saveOrUpdate(user);
}

I want to make this run as:

public User update_updateUser(User user) {
  BackupUser backupUser=new BackupUser(User);
  getHibernateTemplate().save(backupUser);
  getHibernateTemplate().saveOrUpdate(user);
}

For each entity, I will need to create another entity (Like BackupUser), which maps to another database table (Like BACKUP_USER).

I would like to learn if it is possible, by use of AOP, to have the functionality I mentioned above - without handcoding manually for each Hibernate call.

Another similar requirement is error logging. Whenever Spring catches an Exception, I would like to run this code:

ApplicationErrorLog appErrorLog = new(ApplicationErrorLog);
getHibernateTemplate().save(applicationErrorLog );

I know that I have to read a lot about AOP, but until then, your help, simple code examples and appcontext.xml will be will be very much appreciated.

Regards,
Turgay Zengin

[irbouho]

did you consider using database triggers?

[turgayz]

Right, the natural way to implement this is using an update trigger, but I do not want to go into database triggers, because I want the database to be portable.

Regards,
Turgay Zengin

[turgayz]

This is what I could do, after studying AOP:

SaveDatabaseInterceptor is for catching save operations, and then saving a backup object to the database. It works if the object implements Auditable --if(args0 instanceof Auditable)--, and if this is not a "insert into" but a "update" operation --if(oldId!=null)--.

public class SaveDatabaseInterceptor implements MethodInterceptor {
	private SystemDAO systemDAO = null;

	public void setSystemDAO(SystemDAO systemDao) {
        this.systemDAO = systemDao;
    }

	public Object invoke(MethodInvocation invocation) throws Throwable {
		Object[] args=invocation.getArguments();
		Object args0=args[0];
		if(args0 instanceof Auditable) {
			BasePersistentObject bpo=(BasePersistentObject)args[0];
			Long oldId=bpo.getId();
			if(oldId!=null){				
				BasePersistentObject backupBPO=bpo.getBackupObject();
				systemDAO.saveBPO(backupBPO);
				System.out.println("save advice worked!:" + invocation);
			}		
		}
		Object rval = invocation.proceed();	
		return rval;
	}
}

Each Auditable entity class defines a method to get a "Backup" object. For example, in User.java:

	public BasePersistentObject getBackupObject() {
		return new Backup_User(this);
	}

Backup_User is a seperate class, which also has a mapping file for hibernate.

For "delete" operations, I have the following.

public class RemoveDatabaseInterceptor implements MethodInterceptor {
	private SystemDAO systemDAO = null;

	public void setSystemDAO(SystemDAO systemDao) {
        this.systemDAO = systemDao;
    }
	
	public Object invoke(MethodInvocation invocation) throws Throwable {	
		Object[] args=invocation.getArguments();
		Object args0=args[0];
		if(args0 instanceof Auditable) {
			BasePersistentObject bpo=(BasePersistentObject)args[0];
			
			if(bpo!=null){				
				BasePersistentObject deletedBPO=bpo.getDeletedObject();
				systemDAO.saveBPO(deletedBPO);
				System.out.println("remove advice worked!:" + invocation);
			}		
		}

		Object rval = invocation.proceed();	
		return rval;
		
	}
	
}

Likewise, each Auditable class has a method to get a "Deleted" object:

	public BasePersistentObject getDeletedObject() {
		return new Deleted_User(this);
	}

Relevant parts from the applicationContext.xml:

	<bean id="DAOTemplate" lazy-init="true"
		class="org.springframework.aop.framework.ProxyFactoryBean">
		<property name="interceptorNames">
			<list>
				<value>saveDatabaseAdvisor</value>
				<value>removeDatabaseAdvisor</value>
			</list>
		</property>
	</bean>

    <bean id="userDAO" parent="DAOTemplate" >
		<property name="proxyInterfaces"><value>dao.UserDAO</value></property>
		<property name="target"><ref local="userDAOTarget"/></property>
    </bean>    

	<bean id="userDAOTarget" class="dao.hibernate.UserDAOHibernate"> 
        <property name="sessionFactory"><ref local="sessionFactory"/></property>		
	</bean>		

    <bean id="systemDAO" parent="DAOTemplate" >
		<property name="proxyInterfaces"><value>dao.SystemDAO</value></property>
		<property name="target"><ref local="systemDAOTarget"/></property>
    </bean>    

	<bean id="systemDAOTarget" class="dao.hibernate.SystemDAOHibernate"> 
        <property name="sessionFactory"><ref local="sessionFactory"/></property>		
	</bean>		

	<bean id="saveDatabaseInterceptor" class="util.SaveDatabaseInterceptor">
		<property name="systemDAO"><ref local="systemDAO"/></property>
	</bean>

	<bean id="saveDatabaseAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="saveDatabaseInterceptor"/></property>
		<property name="patterns">
			<list>
				<value>.*save.*</value>				
			</list>			
		</property>
	</bean>

	<bean id="removeDatabaseInterceptor" class="util.RemoveDatabaseInterceptor" >
		<property name="systemDAO"><ref local="systemDAO"/></property>
	</bean>
	
	<bean id="removeDatabaseAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="removeDatabaseInterceptor"/></property>
		<property name="patterns">
			<list>
				<value>.*remove.*</value>
			</list>			
		</property>
	</bean>

I now have only one problem with the remove operation. When the entity itself is deleted from the master table(User), a record will be inserted into the "Deleted_" table (Deleted_User). But I cannot put a foreign key to the "Deleted" table to point to the "deletedUser", because the record will be deleted from the master table...

I am thinking about not deleting from the master table actually, but marking it as deleted, using a field called DATABASE_STATUS, and setting it to "DELETED". ("ACTIVE" if not deleted). But this also introduces another problem: If I have unique constraints on the table (USERNAME UNIQUE), than I cannot insert a new record violating that unique constraint. Any ideas?

Regards,
Turgay Zengin

[turgayz]

Here is the design I came up with, hoping that it could be useful for others as well. Any comments, critiques, improvement suggestions are welcome.

SaveDatabaseInterceptor is for catching save operations, and then saving a backup object to the database. It works if the object implements Auditable --if(args0 instanceof Auditable)--, and if this is not a "insert into" but a "update" operation --if(oldId!=null)--.

public class SaveDatabaseInterceptor implements MethodInterceptor {
	private SystemDAO systemDAO = null;
	private static final Log log = LogFactory.getLog(SaveDatabaseInterceptor.class);
	
	public void setSystemDAO(SystemDAO systemDao) {
        this.systemDAO = systemDao;
    }

	public Object invoke(MethodInvocation invocation) throws Throwable {
		log.debug("save advice will work:" + invocation);
		BasePersistentObject bpo=null;
		Object[] args=invocation.getArguments();
		Object args0=args[0];
		if(args0 instanceof Auditable) {
			bpo=(BasePersistentObject)args0;
			Long oldId=bpo.getId();
			if(oldId!=null){												
				BasePersistentObject backupBPO=bpo.getBackupObject();
				backupBPO.setDatabaseStatus(BasePersistentObject.RECORD_UPDATED+"_"+bpo.getBaseRecord().getId()+"_"+(bpo.getDatabaseVersion()+1));
				systemDAO.saveBackupBPO(backupBPO);				
				log.debug("save advice worked:" + invocation);
			}
			else {
				//new record
				bpo.setBaseRecord(bpo);
			}
		}
		Object rval = invocation.proceed();
		return rval;
	}

Here is the BAsePersistentObject from which all my persistent classes inherit from:

public abstract class BasePersistentObject implements Serializable {
	public static String RECORD_ACTIVE="ACTIVE";
	public static String RECORD_UPDATED="UPDATED";
	public static String RECORD_DELETED="DELETED";
	
    protected Long id;
    protected BasePersistentObject baseRecord;
    protected int databaseVersion;
    protected String databaseStatus=RECORD_ACTIVE;
    protected User createdUser;
    protected Timestamp createdTime;
    protected User lastModifiedUser;
    protected Timestamp lastModifiedTime;
    protected Timestamp systemModifiedTime;
    protected User ownerUser;
        
// getters, setters omitted
    
    public boolean isActive() { return getDatabaseStatus().equals("ACTIVE"); }

    public boolean isDeleted() { return getDatabaseStatus().equals("DELETED"); }
    
	public  BasePersistentObject getBackupObject() {
		BasePersistentObject bpo=null;
		try{
			bpo=(BasePersistentObject)BeanUtils.cloneBean(this);
			bpo.setId(null);
		}catch(Exception e) {
			System.out.println(e);
		}
		return bpo;
	}

The interesting method here is getBAckupObject(), which returns a copy of this entity for auditing purposes. The classes to be audited will have to implement a marker interface called Auditable.
Remove advice will simply modify the database status as "DELETED".

public class RemoveDatabaseInterceptor implements MethodInterceptor {
	private static final Log log = LogFactory.getLog(RemoveDatabaseInterceptor.class);
	private SystemDAO systemDAO = null;

	public void setSystemDAO(SystemDAO systemDao) {
        this.systemDAO = systemDao;
    }
	
	public Object invoke(MethodInvocation invocation) throws Throwable {
		log.debug("remove advice will work:" + invocation);
		Object[] args=invocation.getArguments();
		Object args0=args[0];
		if(args0 instanceof Auditable) {
			BasePersistentObject bpo=(BasePersistentObject)args[0];			
			if(!bpo.getDatabaseStatus().equals(BasePersistentObject.RECORD_ACTIVE))
				throw new Exception("Status of the entity object was not ACTIVE");
			else {

				bpo.setDatabaseStatus(BasePersistentObject.RECORD_DELETED+"_"+bpo.getBaseRecord().getId());
				log.debug("remove advice worked:" + invocation);
			}
		}

		Object rval = invocation.proceed();	
		return rval;
		
	}

SystemDAO, which handles application wide DAO, looks like:

public interface SystemDAO extends DAO {
	/**
	 * When an exception occurs, we want to record it in the database,
	 * with use of AOP(ExceptionAdvice). Not to be used directly, the system calls when needed.
	 * @param ael the ApplicationErrorLog object to be logged in the database.
	 * @return the ApplicationErrorLog object that was logged in the database.
	 * @see ExceptionAdvice 
	 */
    public ApplicationErrorLog saveApplicationErrorLog(ApplicationErrorLog ael);
    
    /**
	 * All database create or update operations can use this interface.
	 * @param bpo the BasePersistentObject to be created or updated in the database.
	 * Subject to the AOP SaveDatabaseInterceptor if implements Auditable.
	 * @return the BasePersistentObject that was created or updated in the database.
	 * @see SaveDatabaseInterceptor
	 * @see Auditable
	 */
    public BasePersistentObject saveBPO(BasePersistentObject bpo);
    
    /**
	 * Backup records(auditing) are saved through this interface.
	 * Created to let SaveDatabaseInterceptor bypass this database update.
	 * @param bpo the BasePersistentObject to be created in the database for auditing purposes.
	 * @return the BasePersistentObject that was created in the database for auditing purposes..
	 * @see SaveDatabaseInterceptor
	 */
    public BasePersistentObject saveBackupBPO(BasePersistentObject bpo);
    
    /**
	 * All database "delete" operations use this interface.
	 * @param bpo the BasePersistentObject to be deleted in the database.
	 * Subject to the AOP RemoveDatabaseInterceptor if implements Auditable.
	 * @see RemoveDatabaseInterceptor
	 * @see Auditable
	 */
    public void removeBPO(BasePersistentObject bpo);
    
    /**
	 * All database "select * from ATABLE where id=?" operations use this interface.
	 * @param theClass The class name of the entity to be retrieved. 
	 * @param id The Long representation of the identifier field
	 * @return the BasePersistentObject that was retrieved from the database.
	 */
    public BasePersistentObject retrieveBPO(Class theClass,Long id);

And the Hibernate implementation:

public class SystemDAOHibernate extends HibernateDaoSupport implements SystemDAO {
    private static final Log log = LogFactory.getLog(SystemDAOHibernate.class);

	public ApplicationErrorLog saveApplicationErrorLog(ApplicationErrorLog ael) {
		getHibernateTemplate().saveOrUpdate(ael);
		return ael;
	}

    public BasePersistentObject saveBPO(BasePersistentObject bpo) {
        getHibernateTemplate().saveOrUpdate(bpo);
        return bpo;
    }

    public BasePersistentObject saveBackupBPO(BasePersistentObject bpo) {
        getHibernateTemplate().saveOrUpdate(bpo);
        return bpo;
    }
    
	public void removeBPO(BasePersistentObject bpo) {
		getHibernateTemplate().saveOrUpdate(bpo);		
	}

    public BasePersistentObject retrieveBPO(Class theClass,Long id){
    	return (BasePersistentObject)getHibernateTemplate().get(theClass,id);
    }

The ExceptionAdvice mentioned above looks like (inserts a record into the APPLICATIONERRORLOG table):

public class ExceptionAdvice implements ThrowsAdvice{
	private SystemDAO systemDAO = null;
	private UserDAO userDAO = null;
	private static final Log log = LogFactory.getLog(ExceptionAdvice.class);
	
	public void setUserDAO(UserDAO userDao) {
        this.userDAO = userDao;
    }
	public void setSystemDAO(SystemDAO systemDao) {
        this.systemDAO = systemDao;
    }

	public void afterThrowing(Method m,Object[] args,Object target,Exception ex){
		log.debug("Exception advice will work!:"+ex);
		ApplicationErrorLog ael=new ApplicationErrorLog();
		Timestamp now = new Timestamp(System.currentTimeMillis());
		ael.setCreatedTime(now);
		
		ael.setCreatedUser(userDAO.retrieveUserByUserName("admin"));
		ael.setDescription("Method:"+m+" Exception:"+ex);
		ael.setErrorNumber("111"); //TODO: To be implemented by looking at the actual error...		
		systemDAO.saveApplicationErrorLog(ael);
		log.debug("Exception advice worked!:"+ex);
	}

The database schema for an Auditable class looks like:

create table DEPARTMENT(
ID NUMERIC NOT NULL primary key,
DATABASEVERSION NUMERIC NOT NULL,
BASERECORDID NUMERIC NOT NULL,
DATABASESTATUS VARCHAR(15) DEFAULT 'ACTIVE' NOT NULL,
CREATEDUSERID NUMERIC NOT NULL,
CREATEDTIME TIMESTAMP NOT NULL,
LASTMODIFIEDUSERID NUMERIC NOT NULL,
LASTMODIFIEDTIME TIMESTAMP NOT NULL,
SYSTEMMODIFIEDTIME TIMESTAMP NOT NULL,
OWNERUSERID NUMERIC NOT NULL,
NAME VARCHAR(20) NOT NULL,
DESCRIPTION VARCHAR(100) NOT NULL);

And finally the applicationcontext.xml (I know this has been long already )

<beans>
<!-- BASICDATASOURCE -->
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <!-- datasource properties omitted -->
    </bean>

    <!-- Hibernate SessionFactory -->
    <bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
        <property name="dataSource"><ref local="dataSource"/></property>
        <!-- Hibernate definitions omitted -->
    </bean>

    <!-- Transaction manager for a single Hibernate SessionFactory &#40;alternative to JTA&#41; -->
    <bean id="transactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
        <property name="sessionFactory"><ref local="sessionFactory"/></property>
    </bean>

	<bean id="DAOTemplate" lazy-init="true"
		class="org.springframework.aop.framework.ProxyFactoryBean">
		<property name="interceptorNames">
			<list>
				<value>saveDatabaseAdvisor</value>
				<value>removeDatabaseAdvisor</value>
			</list>
		</property>
	</bean>

    <bean id="userDAO" parent="DAOTemplate" >
		<property name="proxyInterfaces"><value>dao.UserDAO</value></property>
		<property name="target">
			<bean id="userDAOTarget" class="dao.hibernate.UserDAOHibernate"> 
				<property name="sessionFactory"><ref local="sessionFactory"/></property>
			</bean> 
		</property>
    </bean>    

    <bean id="systemDAO" parent="DAOTemplate" >
		<property name="proxyInterfaces"><value>dao.SystemDAO</value></property>
		<property name="target">
			<bean id="systemDAOTarget" class="dao.hibernate.SystemDAOHibernate"> 
				<property name="sessionFactory"><ref local="sessionFactory"/></property>		
			</bean>	
		</property>
    </bean>    		

	<bean id="TransactionalFacadeTemplate" lazy-init="true"
		class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
		<property name="transactionManager"><ref local="transactionManager"/></property> 
		<property name="transactionAttributes"> 
			<props> 
				<prop key="update_*">PROPAGATION_REQUIRED</prop> 
				<prop key="*">PROPAGATION_SUPPORTS,readOnly</prop> 
			</props> 
		</property>	
	</bean>

 	<bean id="SecureFacadeTemplate" lazy-init="true" parent="TransactionalFacadeTemplate"
		class="org.springframework.aop.framework.ProxyFactoryBean">
		<property name="interceptorNames">
			<list>
				<value>securityAdvisor</value>
			</list>
		</property>
	</bean>
	
    <bean id="userManagerFacade" parent="SecureFacadeTemplate" >
		<property name="proxyInterfaces"><value>service.UserManagerFacade</value></property>
		<property name="target">
			<bean id="userManagerFacadeTarget" class="service.impl.UserManagerFacadeImpl"> 
				<property name="userDAO"><ref local="userDAO"/></property> 
				<property name="systemDAO"><ref local="systemDAO"/></property> 
			</bean> 
		</property>
    </bean>    

	<bean id="saveDatabaseInterceptor" class="util.SaveDatabaseInterceptor">
		<property name="systemDAO"><ref local="systemDAO"/></property>
	</bean>

	<bean id="saveDatabaseAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="saveDatabaseInterceptor"/></property>
		<property name="patterns">
			<list>
				<!--<value>.*save.*</value>-->
				<value>.*save&#40;?!Backup&#41;\w+</value>		
			</list>			
		</property>
	</bean>

	<bean id="removeDatabaseInterceptor" class="util.RemoveDatabaseInterceptor" >
		<property name="systemDAO"><ref local="systemDAO"/></property>
	</bean>
	
	<bean id="removeDatabaseAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="removeDatabaseInterceptor"/></property>
		<property name="patterns">
			<list>
				<value>.*remove.*</value>
			</list>			
		</property>
	</bean>

	<bean id="securityInterceptor" class="util.SecurityInterceptor" />

	<bean id="securityAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="securityInterceptor"/></property>
		<property name="patterns">
			<list>
				<value>.*retrieve.*</value>
				<value>.*update_.*</value>
			</list>			
		</property>
	</bean>

	<bean id="exceptionAdvice" class="util.ExceptionAdvice" >
		<property name="userDAO"><ref local="userDAO"/></property>
		<property name="systemDAO"><ref local="systemDAO"/></property>
	</bean>

	<bean id="exceptionAdvisor" class="org.springframework.aop.support.RegexpMethodPointcutAdvisor">
		<property name="advice"><ref local="exceptionAdvice"/></property>
		<property name="patterns">
			<list>
				<value>.*</value>
			</list>			
		</property>
	</bean>
	
	<bean id="autoProxyCreator" class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator">		
		<property name="advisorBeanNamePrefix">
				<value>exceptionAdv</value>			
		</property>
		<property name="usePrefix">
				<value>true</value>			
		</property>
	</bean>

</beans>

And (really finally) for the sake of completeness, here is the SecurityInterceptor:

public class SecurityInterceptor implements MethodInterceptor {
	private static final Log log = LogFactory.getLog(SecurityInterceptor.class);
	
	public Object invoke(MethodInvocation invocation) throws Throwable {
//	 Apply crosscutting code
	doSecurityCheck(invocation);
	
//	 Call next interceptor
	return invocation.proceed();
	}
	
	protected void doSecurityCheck(MethodInvocation invocation) throws UnAuthorizedException {
		log.debug("security check performed:"+invocation); //TODO: implement security check...
	}

You can follow the progress of this at http://sourceforge.net/projects/openhelpdesk if you want to.

Regards,
Turgay Zengin