com.microdeveloper.db.rectifier
Class Rectify

java.lang.Object
  com.microdeveloper.db.rectifier.Rectify

public class Rectify
extends Object

Resolves the data differences between two tables in one or two Oracle databases. To use the class, you can minimally specify the owner and table name or you can specify the particular columns to compare with. Despite the comparison columns all columns are updated when reconciling differences. To compare the tables without changing the data use the Differences class. The source can be either a table or a view but the target must be a table. Unlike differences, the two objects must structurally agree or an SQLException will be thrown. It is possible to use a View as the source and a table as the destination but care must be taken to ensure that the table columns have sufficient size to accomodate the View data. Whe updating the target, the matching row is first deleted and a new row is inserted. An SQL update is not performed when reconciling differences.

Atomic Transactions
If desired, the synchronization can be perfromed as an atomic transaction (the default). An atomic transaction only commits the work when all actions have been completed. For example, should an error occur part way through the sync, all changes are rolled back when atomic is true. Conversely, when atomic is false, all updates performed up to the point of error are committed. For large updates, the atomic transaction should perform better as all DML is executed in batch whereas non-atomic transactions are executed and committed as needed.

HashMap Result Statistics
Like the Differences class, the deltas found and changed are returned in a HashMap structure that contains statistics related to the comparison as well as lists of the actual differences that were affected. Each Differences HashMap contains the following:

• Source Orphan Count (integer)
• Source Orphan List (HashMap)
  • KeyColumn (String)
  • Delimited record values (String)
• Source Record Count (integer)
• Target Orphan Count (integer)
• Target Orphan List (HashMap)
• KeyColumn (String)
• Delimited record values (String)
• Target Record Count (integer)
• Difference Count (integer)
• Difference List (HashMap)
  • KeyColumn [NEW] (String)
  • Delimited record values (String)
  • KeyColumn [OLD] (String)
  • Delimited record values (String)
• Warnings Count (integer)
• List of problem (skipped) key values (HashMap)
  • KeyColumn (String)
  • Description of the problem (String)

Usage:
The simplest rectify usage would be where the two tables exist in the same database and all of the columns and rows are used in the comparison. For example, to synchronize the tables 'emp' and 'emp2', both of which are in the 'SCOTT' schema, we would use the short form of resolveDifferences as in:

// Assume the database connection has already been defined and opened in the object 'conn'
 HashMap m = new Rectify.resolveDifferences(conn, "scott", "emp", "scott", "emp2", "emp_id");

To limit the comparison, you can specify a list of columns to compare by supplying a singular array of column names. If any of these columns differ, the entire row is updated in the target. For example:

String[] cols = new String[3];
 cols[0] = "first_name";
 cols[1] = "last_name";
 cols[2] = "city";
 HashMap m = new Rectify.resolveDifferences(conn, "scott", "emp", "scott", "emp2", "emp_id", cols);

You can also limit the rows compared by specifying the where clause. The where clause can be specified for either the source, the target, or both. For example:

String whereClause = "WHERE mgr_id = '123'";
 HashMap m = Rectify.resolveDifferences(conn, "scott", "emp", whereClause, "scott", "emp2", whereClause, "emp_id", cols);

You can compare across databases as well by specifyng more than one connection object. For example:

Connection conn1 = new Connector("orcl_1").getConnection();
 Connection conn2 = new Connector("orcl_2").getConnection();
 HashMap m = new Rectify.resolveDifferences(conn1, conn2, "scott", "emp", whereClause, "scott", "emp2", whereClause, "emp_id", cols);

In general, if the tables are large, it is better to truncate and replace the target than to synchronize. If, however, the target contains orphans that you wish to retain, synchronization is the only option.

Since:

1.6

Version:

1.0

Author:

MicroDeveloper, Inc.

See Also:

Differences

Field Summary

static String	COLUMN_ERR Error when attempting to fetch the column data
static String	DELETE_MAP_KEY Key to the HashMap that contains the delete (target orphan) elements.
static String	DIFF_KEY Key for the count of differing records
static String	DML_ERR Error when exception is thrown during batch update
static String	ERROR_KEY Key for the count of recoverable errors during the synchronization
static String	ERROR_MAP_KEY Key to the HashMap that contains the data key elements that caused soft errors.
static String	FIELD_SEP_CHAR Character sequence used to separate field values during comparison
static String	INSERT_MAP_KEY Key to the HashMap that contains the insert (source orphan) elements.
static String	OBJECT_EXISTS_ERR Error when a given object requested for comparison is not found for the connection given
static String	PROBLEM_MAP_KEY Key to the HashMap that contains the data key elements with problems.
static String	SELF_SYNC_ERR Error for when the source and the target are the same (self comparison)
static String	SOFT_ERROR_HALT Error message used when a soft error is encountered and haltOnError is set to true
static String	SRC_CLOSED_ERR Error when source database connection is not open
static String	SRC_COUNT_KEY Key for the count of records compared from the source
static String	SRC_ORPHAN_KEY Key for the count of orphan records in the source
static String	TGT_CLOSED_ERR Error when target database connection is not open
static String	TGT_COUNT_KEY Key for the count of records compared from the target
static String	TGT_ORPHAN_KEY Key for the count of orphan records in the target
static String	TOO_FEW_DELETED_ERR Error when too few rows are deleted during an update (should be 1 to 1)
static String	TOO_FEW_INSERTED_ERR Error when too few rows are inserted during an update (should be 1 to 1)
static String	TOO_MANY_DELETED_ERR Error when too many rows are deleted during an update (should be 1 to 1)
static String	TOO_MANY_INSERTED_ERR Error when too many rows are inserted during an update (should be 1 to 1)
static String	UPDATE_MAP_KEY Key to the HashMap that contains the updated (differing) elements.
static String	WARNING_KEY Key for the count of warnings during the comparison

Constructor Summary

Rectify()
Creates a new instance of Rectify

Method Summary

HashMap	resolveDifferences(Connection srcConn, Connection tgtConn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String whereClause, String keyColumn, String[] comparisonColumns, boolean syncDeletes, boolean haltOnError, boolean atomic) Synchronizes objects between two data sources using a filtered rowset and specific comparison columns with discreet control over the execution parameters.
HashMap	resolveDifferences(Connection conn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String keyColumn) Synchronizes objects between a single data source with default execution parameters.
HashMap	resolveDifferences(Connection conn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String keyColumn, boolean syncDeletes, boolean haltOnError, boolean atomic) Synchronizes objects between a single data source with discreet control over the execution parameters.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

SRC_ORPHAN_KEY

public static final String SRC_ORPHAN_KEY

Key for the count of orphan records in the source

See Also:

Constant Field Values

TGT_ORPHAN_KEY

public static final String TGT_ORPHAN_KEY

Key for the count of orphan records in the target

See Also:

Constant Field Values

SRC_COUNT_KEY

public static final String SRC_COUNT_KEY

Key for the count of records compared from the source

See Also:

Constant Field Values

TGT_COUNT_KEY

public static final String TGT_COUNT_KEY

Key for the count of records compared from the target

See Also:

Constant Field Values

DIFF_KEY

public static final String DIFF_KEY

Key for the count of differing records

See Also:

Constant Field Values

WARNING_KEY

public static final String WARNING_KEY

Key for the count of warnings during the comparison

See Also:

Constant Field Values

ERROR_KEY

public static final String ERROR_KEY

Key for the count of recoverable errors during the synchronization

See Also:

Constant Field Values

ERROR_MAP_KEY

public static final String ERROR_MAP_KEY

Key to the HashMap that contains the data key elements that caused soft errors. The structure of the internal HashMap is Key=keyColumn and value = problem text

See Also:

Constant Field Values

PROBLEM_MAP_KEY

public static final String PROBLEM_MAP_KEY

Key to the HashMap that contains the data key elements with problems. The structure of the internal HashMap is Key=keyColumn and value = problem text

See Also:

Constant Field Values

INSERT_MAP_KEY

public static final String INSERT_MAP_KEY

Key to the HashMap that contains the insert (source orphan) elements. The structure of the internal HashMap is Key=keyColumn and value = column values separated by FIELD_SEP_CHAR characters

See Also:

Constant Field Values

UPDATE_MAP_KEY

public static final String UPDATE_MAP_KEY

Key to the HashMap that contains the updated (differing) elements. The structure of the internal HashMap is Key=keyColumn [NEW/OLD] and value = column values separated by FIELD_SEP_CHAR characters. There are always two records per element; one for the new (source) elemenet and one for the old (target) element.

See Also:

Constant Field Values

DELETE_MAP_KEY

public static final String DELETE_MAP_KEY

Key to the HashMap that contains the delete (target orphan) elements. The structure of the internal HashMap is Key=keyColumn and value = column values separated by FIELD_SEP_CHAR characters

See Also:

Constant Field Values

FIELD_SEP_CHAR

public static final String FIELD_SEP_CHAR

Character sequence used to separate field values during comparison

See Also:

Constant Field Values

SRC_CLOSED_ERR

public static final String SRC_CLOSED_ERR

Error when source database connection is not open

See Also:

Constant Field Values

TGT_CLOSED_ERR

public static final String TGT_CLOSED_ERR

Error when target database connection is not open

See Also:

Constant Field Values

OBJECT_EXISTS_ERR

public static final String OBJECT_EXISTS_ERR

Error when a given object requested for comparison is not found for the connection given

See Also:

Constant Field Values

COLUMN_ERR

public static final String COLUMN_ERR

Error when attempting to fetch the column data

See Also:

Constant Field Values

SELF_SYNC_ERR

public static final String SELF_SYNC_ERR

Error for when the source and the target are the same (self comparison)

See Also:

Constant Field Values

TOO_MANY_DELETED_ERR

public static final String TOO_MANY_DELETED_ERR

Error when too many rows are deleted during an update (should be 1 to 1)

See Also:

Constant Field Values

TOO_MANY_INSERTED_ERR

public static final String TOO_MANY_INSERTED_ERR

Error when too many rows are inserted during an update (should be 1 to 1)

See Also:

Constant Field Values

DML_ERR

public static final String DML_ERR

Error when exception is thrown during batch update

See Also:

Constant Field Values

TOO_FEW_DELETED_ERR

public static final String TOO_FEW_DELETED_ERR

Error when too few rows are deleted during an update (should be 1 to 1)

See Also:

Constant Field Values

TOO_FEW_INSERTED_ERR

public static final String TOO_FEW_INSERTED_ERR

Error when too few rows are inserted during an update (should be 1 to 1)

See Also:

Constant Field Values

SOFT_ERROR_HALT

public static final String SOFT_ERROR_HALT

Error message used when a soft error is encountered and haltOnError is set to true

See Also:

Constant Field Values

Constructor Detail

Rectify

public Rectify()

Creates a new instance of Rectify

Method Detail

resolveDifferences

public HashMap resolveDifferences(Connection srcConn,
                                  Connection tgtConn,
                                  String srcOwner,
                                  String srcTableName,
                                  String tgtOwner,
                                  String tgtTableName,
                                  String whereClause,
                                  String keyColumn,
                                  String[] comparisonColumns,
                                  boolean syncDeletes,
                                  boolean haltOnError,
                                  boolean atomic)
                           throws SQLException

Synchronizes objects between two data sources using a filtered rowset and specific comparison columns with discreet control over the execution parameters. With this variant you can control whether or not the target orphans are removed or not, whether to halt when soft errors (recoverable errors) occur, and whether you want the task to be atomic or not. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

Parameters:

srcConn - connection to the source database

tgtConn - connection to the target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

whereClause - where clause to limit rowset compared

keyColumn - field used as the key column

comparisonColumns - array of fields used for the comparison

syncDeletes - true if target orphans should be removed otherwise false to ignore target orphans

haltOnError - set to true if you want the synchronization to stop immediately when a soft error occurs.

atomic - set to true if you want the synchronization to be atomic (all or nothing).

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException

resolveDifferences

public HashMap resolveDifferences(Connection conn,
                                  String srcOwner,
                                  String srcTableName,
                                  String tgtOwner,
                                  String tgtTableName,
                                  String keyColumn,
                                  boolean syncDeletes,
                                  boolean haltOnError,
                                  boolean atomic)
                           throws SQLException

Synchronizes objects between a single data source with discreet control over the execution parameters. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

This variant assumes that all columns except the key column are to be compared for equality.

Parameters:

conn - connection to the source and target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

keyColumn - field used as the key column

syncDeletes - true if target orphans should be removed otherwise false to ignore target orphans

haltOnError - set to true if you want the synchronization to stop immediately when a soft error occurs.

atomic - set to true if you want the synchronization to be atomic (all or nothing).

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException

resolveDifferences

public HashMap resolveDifferences(Connection conn,
                                  String srcOwner,
                                  String srcTableName,
                                  String tgtOwner,
                                  String tgtTableName,
                                  String keyColumn)
                           throws SQLException

Synchronizes objects between a single data source with default execution parameters. The default execution parameters are: do not synchronize deletes, do not halt on soft errors, perform an atomic transaction, compare all columns, compare all rows. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

This variant assumes that all columns except the key column are to be compared for equality, all rows are to be compared, and that both the source and target exist within the same database.

Parameters:

conn - connection to the source and target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

keyColumn - field used as the key column

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException