Introduction

In some cases you might be interested to know what changes were made on some database.

The regular example is when you want to test whether an upgrade of your Application Under Test did not cause any unwanted changes on the database.

Make sure there is no change on the database

The following example makes a snapshot of two databases. An instance of TestBox is used to provide connection parameters to the databases.

import com.axway.ats.harness.config.TestBox;
import com.axway.ats.action.dbaccess.snapshot.DatabaseSnapshot;

// create a test box instance
TestBox testBox1 = new TestBox();
// and here use the different setter method in order to provide DB connection parameters
testBox1.set...

// make a snapshot from the database pointed in the test box
DatabaseSnapshot snapshot1 = new DatabaseSnapshot( "snap1", testBox1 );

/*
 Sometimes a table index name can contain random auto-generated String(s)
 If you do not have a way to handle them properly, you can specify a REGEX or a custom implementation of the Interface IndexNameMatcher to check if two indexes are the same
*/
// via Java REGEX
snapshot1.setIndexMatcher(SOME_REGEX);

// Note that if one if the IndexMatcher.isSame() method returns true, the other one will not be used at all.
// The order is: first the isSame(String,String,String). If that returns false, then use the result from the other one

/*snapshot1.setIndexNameMatcher(new IndexMatcher() {
    @Override
    public boolean isSame( String table, String firstName, String secondName ) {
        // some custom name to compare the names of the two indexes.
        // firstName -> the name of the first table index (from the first snapshot), secondName -> the name of the second table index (from the second snapshot)
        return false;
    }
    
@Override
    public boolean isSame( String table, Properties firstProperties, Properties secondProperties ) {
        // compare all of the index properties. The properties' keys can be found in XYZDbProvider.IndexProperties class
        return false;
    }
});*/

// skip information about this index for the specified table
Properties indexProperties = new Properties();
indexProperties.put(MysqlDbProvider.IndexProperties.COLUMN_NAME, "lname"); // for other DB types, change MysqlDbProvider to the appropriate type (PostgreSQL, Oracle, etc)
snapshot1.skipTableIndex(TABLE_NAME, indexProperties); 

// take first snapshot
snapshot1.takeSnapshot();

// create another test box instance
TestBox testBox2 = new TestBox();
// and here use the different setter method in order to provide DB connection parameters
testBox2.set...

// make a second snapshot from the database pointed in the test box
DatabaseSnapshot snapshot2 = new DatabaseSnapshot( "snap2", testBox2 );
// take second snapshot
snapshot2.takeSnapshot();

// compare both snapshots
snapshot2.compare( snapshot1 );

// or specify some additional comparison options
CompareOptions compareOptions = new CompareOptions();
// here it is expected that TABLE_TWO will have 1 unique row. In other words either snapshot1 or shapshot2 will have this table with 1 more or less rows
compareOptions.setExpectedTableMissingRowsCount(TABLE_TWO, 1, 1);
// here the unique rows between shapshots is in the range 2-5, (including both values)
compareOptions.setExpectedTableMissingRowsCount(TABLE_THREE, 2, 5);
snapshot2.compare(snapshot1, compareOptions);

If the databases are identical a message will be logged. Otherwise the compare method will throw an error describing the found differences. For example:

Comparing [snap2] and [snap1] produced the following unexpected differences:
Tables present in [snap2] only:
	Accounts
	Users
Different primary keys:
	table 'Transactions', primary key column in [snap2] is 'id', while in [snap1] is 'identification'
Table indexes for 'Operations' table:
	[snap2]:
		name=PRIMARY, column=id, index catalog=, type=BIT, asc/desc=A, non-unique=false, filter condition=null, sequence number=1
	[snap1]:
		name=PRIMARY, column=id, index catalog=, type=BINARY, asc/desc=A, non-unique=false, filter condition=null, sequence number=1

In the above description we see that:

there are table that are present in just one of the snapshots
there is table with a different primary key column
there is a table which index has a different type

When is the data loaded?

It is not clever to load all database data in the java memory as this might cause memory issues.

So when you call takeSnapshot method, we load only the list of tables and different meta information about each table like primary key, columns, indexes.

The real loading of the tables content is done when needed and this is:

when called compare method, we load the content of one table at a time for both databases. After comparing a pair of tables, we load data about the next pair and so on.
when called saveToFile method. We load one table at a time, save it into a file and then proceed to the next table

Do not check some table

By default all found tables in the pointed databases are checked. If you want to exclude a table from the check, you can do:

// skip one table
snapshot1.skipTables( "SomeTable" );

// skip some tables
snapshot1.skipTables( "SomeTable1", "SomeTable2", "SomeTable3" );
// or
snapshot1.skipTables( new String[]{ "SomeTable1", "SomeTable2", "SomeTable3"} );

Do not check some column(s) from some table

// skip one column
snapshot1.skipTableColumn( "SomeTable1", "column1" );

// skip a list of column
snapshot1.skipTableColumns( "SomeTable1", "column1", "column2", "column3" );

Do not check some index(es) from some table

// skip one or more indexes
Properties indexProperties = new Properties();
indexProperties.put(MysqlDbProvider.IndexProperties.COLUMN_NAME, "lname"); // for other DB types, change MysqlDbProvider to the appropriate type (PostgreSQL, Oracle, etc)
snapshot1.skipTableIndex(TABLE_NAME, indexProperties);

Save the snapshot into a file

// after snapshot is taken, you can call:
snapshot1.saveToFile( "C:\\db_snapshot_backup.xml" );

Database backup files are in XML format, so are human readable.

Load snapshot from file

// load snapshot info from a backup file
snapshot1.loadFromFile( "snapshot_from_backup_file", "C:\\db_snapshot_backup.xml" );

Check same database at two different moments

Sometimes you do not want to compare two databases, but one and same database at different moments.

If you just go ahead, make one snapshot, then take the another snapshot in some time, you will be unpleasantly surprised both snapshots are reported identical even if there is changed content. The reason is, as we described above, in the fact we do not load the table contents prior to calling the compare method. So in this case when we compare each table pairs, we will actually load the tables data at same moment for both snapshots from the same database and everything will be the same.

The way to go here is to make one snapshot and save it into a file, this guarantees you get the current tables content. When the seconds snapshot is made and compare is called, we will compare the second snapshot tables from the database versus the first snapshot tables from the backup file.

Make your own report about the found differences

If you do not like the way we present the found differences, you can make your own:

import com.axway.ats.common.dbaccess.snapshot.DatabaseSnapshotException;
import com.axway.ats.common.dbaccess.snapshot.equality.EqualityState;

// make both snapshots as described in the previous sections of this page

try {
    // compare both snapshots
    snapshot2.compare( snapshot1 );
} catch ( DatabaseSnapshotException e ) {
    // if not expected differences are found, you will get this exception
    // you catch it here so can extract the differences out of the exception
    EqualityState equalityState = e.getEqualityState();

    // now use the available method to get the list of differences and construct your own report style
    // some of the available methods are:
    equalityState.getTablesPresentInOneSnapshotOnly( "snap1" );
    equalityState.getDifferentPrimaryKeys();
    equalityState.getIndexesPresentInOneSnapshotOnly( "snap1" );
}

Back to parent page

Go to Table of Contents

ATS : Database Snapshots