Advantage Developer Zone

 
 
 

Resolving Replication Conflicts

Friday, July 14, 2006

Resolving Replication Conflicts

Replication was introduced in Advantage 8.0 to provide the ability to push data changes from one Advantage Database Server to another Advantage Database Server. However, if changes are made to the same record at each of the server sites, only the last replicated change is received. By adding a conflict trigger to your table, Advantage will check all records that are replicated to the table for conflicts. When a conflict is detected, the conflict trigger will be invoked and can be used to resolve the conflict using business logic you write in your trigger.

A Brief Overview of Replication

Replication is only supported between Advantage Database Servers; Advantage Local Server (ALS) does not support replication.

 

Replication is configured in a data dictionary by creating publications and subscriptions.

A publication defines which tables are to be replicated from the current data dictionary (database). A subscription defines the destination data dictionary (database). A username and password must be defined for this connection with sufficient rights to read and write to all of the tables contained in the publication. The subscriber (destination) must have the same table structure as the publisher (source).

 

For more information, refer to the Replication Tech Tip , Replication Online Seminar, or the Advantage Help files.

Enabling Conflict Detection

By default, replication follows a “Last Change Wins” approach. This is to say that the last change that is replicated will overwrite any changes that were made to a record.

Replication is performed at the record level, therefore, if any field in the record changes, then the entire content of the record is replicated to all subscribers.

 

Replication can use either a primary key or some subset of the searchable fields in the table to uniquely identify records at the subscriber. When conflicts are expected, the primary key should be used to identify the replicated records. Otherwise, if one or more of the searchable fields being used to identify the record have changed at the target, the record will not be found. This will cause an error which will need to be resolved before replication can continue.

 

When a conflict trigger is defined for a table, Advantage computes a checksum of the destination record. If this checksum matches the original checksum of the source record, then replication occurs (NOTE: ModTime and RowID fields are not included in the checksum). If the checksum is different, the conflict trigger is invoked.

 

The conflict trigger behaves like an INSTEAD OF trigger, meaning the specified operation is performed rather than the normal update operation. Updates are the only events that raise a conflict trigger. Inserts and deletes occur regardless of whether the subscriber has changed the specified record.

The following conflict trigger writes the new information to another table if a conflict is detected. This preserves the changes that were replicated to the subscriber. A developer could present this information to the user allowing them to determine if the changes should be accepted or rejected.

  

   INSERT INTO CUST_REP SELECT n.*, NOW() FROM __new n;
  

A conflict trigger could also compare the records field by field and update only the fields that have changed instead of updating all fields. Additional logic would be required to handle a case where the same field was changed at both locations.

Providing Feedback to the User

Once a trigger has been created to capture conflicts, a user interface can be developed. The application can read the contents of the table where the conflicts are stored, in this example CUST_REP. The example below is a simple user interface that displays the current record and the update that was written to the table when the conflict occurred. By creating this form in an application, users can choose which changes to apply.

 

The following screen shot, which demonstrates this concept, is from an example application available on the Advantage Developer Zone.

 

 

In this example, the application displays the differences between the current data and the data that was sent from a publisher. The application reads the CustRepl table and locates the matching record in the Customer table in order to show the conflicting data to the user. The user can now choose which changes to apply. When the Update button is clicked, the current record will be updated with only the changes the user accepts.

 

This example application was written in Visual Studio and is available for download in the code samples section of the Advantage Developer Zone.

Summary

Advantage Replication is a powerful server feature that allows applications to be distributed across many sites while preserving changes to common tables. Conflicts can be handled programmatically through the use of triggers. Conflict triggers are the only type of trigger that will be fired when a record is replicated. A conflict trigger must be defined on a table for the conflict check to occur. By default, records are replicated regardless of changes at the subscriber table.