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:

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.

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.

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.

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.

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.

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.

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.