Sebastian Kuligowski's Home Page

Oracle materialized view test automation with Hibernate

May 29th, 2009

Writing automated tests for Oracle materialized views is difficult task especially when long refresh period is applied for the view. This short article describes two approches for effective test automation of materialized view using Hibernate framework. The first one is based on refereshing materialized view on demand, second one uses SQL query attached to the materialized view.

Oracle materialized view is a database table that contains the results of executed query. It's commonly used to pregenerate data set for fast retrive. Materialized view could be created with long refresh period what means that pregenerated data set is not updated for a long time. It's a real impediment to creation of automatic tests because the client application is not able to change the results of materialized view during the test phase. Moreover, client application should roll back all changes made during every test to make the database consistent for other tests.

Working example

The best way to ilustrate how tests of materialized views could be automated using Hibernate is to present by working example. The first thing that should be done is to prepare database environment. 

Grant user privileges

Before you create materialized view you should grant CREATE MATERIALIZED VIEW and DROP ANY MATERIALIZED VIEW privileges to the user. You can do this by login as SYS or SYSTEM user and invoking following queries:

GRANT CREATE MATERIALIZED VIEW TO MVIEW_TEST;
GRANT DROP ANY MATERIALIZED VIEW TO MVIEW_TEST;

Creating database scheme

The next step is to create scheme for database that will be used by application. Scheme contains one table PERSON and one materialized view defined as grouping query from PERSON table. Materialized view is named PERSON_AGGREGATE and its refresh period is set to one day.

CREATE TABLE PERSON (
	ID NUMBER,
	NAME VARCHAR2(64),
	AGE NUMBER,
	PRIMARY KEY(ID)
);

CREATE SEQUENCE PERSON_SEQ;

CREATE MATERIALIZED VIEW PERSON_AGGREGATE 
USING INDEX REFRESH FORCE 
START WITH TRUNC(SYSDATE) + 4/24
NEXT SYSDATE + 1
AS 
	SELECT NAME, COUNT(*) PERSON_COUNT, AVG(AGE) AVG_AGE FROM PERSON GROUP BY NAME;

Creating persistent classes for PERSON and PERSON_AGGREGATE

After creation of the database environment we should preapre persistent classes for PERSON table and PERSON_AGGREGATE materialized view. Person and PersonAggregate classes use java persistence annotations to indicate appropriate mapping between database tables and java objects.

@Entity
@Table(name="PERSON")
public class Person {

	@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="PERSON_SEQ")
	@SequenceGenerator(name="PERSON_SEQ", sequenceName="PERSON_SEQ")
	@Column(name="ID")
	private long id;
	
	@Column(name="NAME")
	private String name;
	
	@Column(name="AGE")
	private long age;

	public Person() {}
	
	public Person(String name, long age) {
		setName(name);
		setAge(age);
	}

	//getters and setters
}

Note that primary key identifier of Person class is generated behind the scene - using database sequence PERSON_SEQ. The second persistent class is perpared for materialized view and contains aggregated data from PERSON table.

@Entity
@Table(name="PERSON_AGGREGATE")
public class PersonAggregate {
		
	@Id
	@Column(name="NAME")
	private String name;
	
	@Column(name="PERSON_COUNT")
	private int cnt;
	
	@Column(name="AVG_AGE")
	private double avg;

	public PersonAggregate() {}

	//getters and setters
}

Hibernate configuration file

The next step is to tell hibernate where persistent classes can be found. It means that two mapping elements should be added to hibernate.cfg.xml file. As long as it is used annotation based confiuration we don't have to care about individual xml mappings for every persistent class.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
		<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
		<property name="connection.url">jdbc:oracle:thin:@10.10.10.10:1521:orcl</property>
		<property name="connection.username">MVIEW_TEST</property>
		<property name="connection.password">MVIEW_TEST</property>
		<property name="connection.pool_size">10</property>
		<property name="show_sql">true</property>
		<property name="dialect">org.hibernate.dialect.OracleDialect</property> 
		<property name="current_session_context_class">thread</property>
		
		<mapping class="pl.kuligowski.mview.dto.Person"        />
		<mapping class="pl.kuligowski.mview.dto.PersonAggregate"        />		
    </session-factory>
</hibernate-configuration>

Testing materialized view using DBMS_MVIEW.REFRESH procedure

The first approach uses REFRESH procedure build in Oracle database DBMS_MVIEW package that is used for materialized view management. Refresh action could be performed on demand only if materialized view has been created in ON DEMAND mode (this mode is default by the way).

DBMS_MVIEW.REFRESH procedure commits current transaction at the end of refresh operation - so any data you've created before invoking REFRESH procedure is also committed. The result of that is after execution of test you are obligated to manually clean the database. Transaction rollback can not be performed because transaction is already commited.

public class MViewTestByRefresh {
	
	private static final SessionFactory sessionFactory = 
		 new AnnotationConfiguration().configure().buildSessionFactory();
	
	protected Session session;
	
	@Before
	public void setUp() {
		session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		// filling master table with records
		session.save(new Person("John", 40));
		session.save(new Person("John", 22));
		session.save(new Person("John", 28));
	}

	@After
	public void tearDown() {
		// cleaning master table from records
		session.createSQLQuery("DELETE FROM PERSON").executeUpdate();
		// refreshing materialized view to make it empty
		refreshMaterializedView();
	}
	
	@Test
	public void testByDBMSRefreshProcedure() {
		refreshMaterializedView();
		PersonAggregate john = (PersonAggregate) 
			session.createQuery("from PersonAggregate as pa where pa.name='John'")
				.uniqueResult();
		Assert.assertEquals(30d, john.getAvg());
	}

	private void refreshMaterializedView() {
		// by calling addSynchronizedEntityClass method we inform hibernate that any Person
		// persistent object should be synchronized with database before invoking query
		session.createSQLQuery("{call DBMS_MVIEW.REFRESH('PERSON_AGGREGATE')}")
			.addSynchronizedEntityClass(Person.class).executeUpdate();
	}
}

Note that tearDown method contains cleaning code which is performed at the end of the test. At first all data is removed from master table (PERSON), then materialized view is refreshed basing on empty PERSON table. Manual cleaning the data is the main inconvenience for this method.

Testing materialized view using query defined for the view

The second approach physically doesn't touch the materialized view. To generate actual data for the view we use SQL query defied for that materialized view. Query string can be retrieved from USER_SNAPSHOTS table.

public class MViewTestByQuery {
		
	private static final SessionFactory sessionFactory = 
		 new AnnotationConfiguration().configure().buildSessionFactory();
	
	protected Session session;
	
	@Before
	public void setUp() {
		session = sessionFactory.getCurrentSession();
		session.beginTransaction();
		session.save(new Person("John", 40));
		session.save(new Person("John", 22));
		session.save(new Person("John", 28));
	}

	@After
	public void tearDown() {
		// we have to rollback all changes after each test
		session.getTransaction().rollback();
	}
	
	@Test
	public void testByMVQuery() {
		// get current SQL query defined for materialized view
		String mViewSQL = (String) 
			session.createSQLQuery(
				"SELECT QUERY FROM USER_SNAPSHOTS WHERE NAME='PERSON_AGGREGATE'")
					.addScalar("QUERY", Hibernate.STRING).uniqueResult();
		
		// execute desired query with mViewSQL query as a subquery
		// you have to inform hibernate that before executing query 
		// Person persistent objects should be synchronized with database
		PersonAggregate john = (PersonAggregate) 
			session.createSQLQuery("select * from ("+mViewSQL+") pa where pa.name='John'")
				.addEntity(PersonAggregate.class)
				.addSynchronizedEntityClass(Person.class)
				.uniqueResult();
		Assert.assertEquals(30d, john.getAvg());
	}
}

The main advantage of this approach is that all operations are handled with single transaction and transaction rollback could be performed at the and of the tests - manual cleaning is not needed. 

Conclusions

Both methods allow you to test materialized views automatically and are indepented from query defined for materialized view. You don't have to care about changes of SQL query for materialized view. Both the first method and the second one need pointing out of depended classes that should be synchronized before querying materialized view (addSynchronizedEntityClass method).

Choose the first method if you prepare data for all tests only once. Then your view could be refreshed at the begining of the tests and cleared at the end. If your tests base on individual data sets and are indepentent from each other choose the second approach. Then you could roll back current transaction and perform other tests having database clean.

Sources (Eclipse-Maven-ready): hibernate-mview.zip

Page generated in 0.013s

Sebastian Kuligowski's Home Page

Copyright © Krak√≥w, 2008 All Rights Reserved