Database Replication with Clarion - How to develop applications supporting remote offices and data warehousing

Database Replication with Clarion - How to develop applications supporting remote offices and data warehousing - Part 2

by Jerry Ray

Published 1999-01-01 Printer-friendly version

How do I implement Database Replication?

There are two basic methods for implementing replication in your Clarion For Windows applications. The first method is to use a Client/Server database, such as SQL Anywhere from Sybase. Using ODBC drivers, you can allow your Clarion application to access these databases easily. The SQL Anywhere product includes Replication Server, which can automatically replicate the tables without intervention from your application program. You can couple this product with SQL Remote, also from Sybase, which can actually use e-mail to perform the transfer without requiring a live network. Other client/server databases that can perform replication include Microsoft SQL Server, Oracle, and Lotus Notes. All of these products create databases that can be accessed in your Clarion dictionary and application via ODBC or using accelerator drivers provided with Clarion 4 Professional or Clarion 5 Enterprise Edition. These products are automatic and highly configurable, although they can be quite expensive.

The second method is to "roll your own". You can write programs using the Clarion language to handle one-way and two-way replication. I have successfully written synchronization routines using Clarion For Windows and Windows95. I will show you a simple (and inexpensive) method of performing synchronization of replicas using just your Clarion environment, Windows95, and Microsoft PLUS for Windows 95.

There are three basic steps to writing code that is "replica-aware". First, we must prepare the dictionary. Next, we must embed code into our application. Third, we must write a synchronizer program.

Actually using our program will be easy. We will connect to the network by double clicking an icon, and then synchronize the replicas while connected. Then we will disconnect from the network.

First, what do we do to the dictionary?

Each table to be replicated requires some fields. The first field(s) necessary is what is known as a Globally Unique ID (GUID). This forms the primary key used to access the record by the synchronizer program. If you already have a primary key, you do not require one of these, but it never hurts. This is a unique identifier for the record that is guaranteed to remain unique throughout the world. It is needed to locate the copy of this particular record in each replica. Note that between replicas, record #3833 in record number order in the CUSTOMER table will NOT necessarily point to the same customer record. This is the reason we need the GUID. By making a key from this field, our code will easily be able to locate the correct record between all the records should a modification or deletion become necessary. You can use a combination of LOCATIONID and INVOICENO, for instance, to create the GUID. A better way, in my opinion, is to generate the GUID on the fly using a simple function call. You can have your program look in your PROGRAM.INI file to determine which location the program is running from. I like to write functions to make programming easier, so you can assign the GUID by calling a function as follows:

SampleFile.NodeId = GenerateGUID()

The above function call can easily be handled by placing the GenerateGUID() function call into the "Initial Value" entry box for the GUID field within the dictionary editor. This will cause the AppGen to prime the key when the record is created automatically. I also like to keep a copy of the GenerateGUID() function as either a .TXA file on disk, or even better, as a .DLL file that can be included into the app. The code for GenerateGUID is as follows:

GenerateGUID   FUNCTION()

TestGUID  STRING(30)
          InstanceNo += 1
          If InstanceNo = 999
            InstanceNo = 1
          END
          TestGUID = '{{' & FORMAT(GlobalSiteCode,@P####P)&'-'&|
            FORMAT(TODAY(),@P######P) & '-' & |
            FORMAT(CLOCK(),@P#######P) & '-' & |
            FORMAT(InstanceNo,@P###P) & '}'
            RETURN(CLIP(TestGUID))

In addition to the GUID set up, you must also indicate who will receive updates from your replica. This is accomplished by settting up a "Subscriber" file. Your replica "publishes" file changes to all "subscribers" defined in this file (either directly through the SubscriberID field or indirectly through the HandoffTo field). The structure of this file is as follows:

SUBSCRIBE
FILE,DRIVER('TOPSPEED'),NAME('C:.\Subscrib.TPS'),CREATE,PRE(SUBSCRIBE)
BySubscriberID        KEY(+SUBSCRIBE:SubscriberID),DUP,OPT,NOCASE
ByTableName
KEY(+SUBSCRIBE:TableName,+SUBSCRIBE:SubscriberID),DUP,OPT,NOCASE
RECORD                RECORD
SubscriberID            STRING(10)
TableName               STRING(40)
HourOffset              BYTE
HandoffTo               STRING(10)

. .

This file is built by the administrator and configured once for each participating replica. You will nominate those remote systems that require updates from your replica in this file and may maintain it from time to time. The Subscriber ID is a string such as "Miami", "Tampa", etc. This matches the "Publisher ID" of the remote system. The TableName field identifies the name of the database that is flagged for replication. The HourOffset is a number that represents, positive or negative, the number of hours apart from each other the two systems are (time zones).

The HandoffTo field is used when you do not want to cause the local system to be responsible for replicating the data, but instead you wish to nominate the remote computer to have the responsibility for continuing the update. For example, suppose you have a traveling salesman that works for a company that maintains one home office and three shipping warehouses. At night, when the salesman reaches his hotel room, he will have 4 different subscribers for any orders he has taken during the course of the day. The last thing the salesman will want to do is place 4 phone calls. Ideally, he should only have to dial the home office, and the Subscriber file be set up to nominate the home office to continue the process of replication to the other 3 subscribers. This is what the HandoffTo field takes care of during the synchronization process. In effect, the salesman will only be required to place one phone call and, during that call, all information is sent to the master replica at the home office, and at some later time those changes will be propagated by the home office to the shipping warehouses. This basically results in an "indirect" transfer to the participating subscriber. It still originates from the remote salesman, but transfers the responsibility of further replication to the nominated HandoffTo system.

Now, after creating a subscriber file, there must be a transaction log that indicates if a record has been added, modified, or deleted by the user or by a process. I create a transaction log file named "Replica" and include the following field set in this file:

REPLICA
FILE,DRIVER('TOPSPEED'),NAME('C:.\Replica.TPS'),CREATE,PRE(REPLICA)
GUIDKey               KEY(+REPLICA:GUID),OPT,NOCASE,PRIMARY
BySubscriber
KEY(+REPLICA:SubscriberID,+REPLICA:DateStamp,+REPLICA:TimeStamp),DUP,OPT,NOCASE
RECORD                RECORD
GUID                    STRING(40)
SubscriberID            STRING(10)
TableName               STRING(40)
TableGUID               STRING(40)
UpdateAction            BYTE
DateStamp               LONG
TimeStamp               REAL
ExpectedGeneration      SHORT
ConflictFlag            BYTE
HandoffTo               STRING(10)
. .

This file will be updated for each action occurring to the file to be replicated. Note that this transaction log will record each insertion, modification, or deletion of a record in the source table. Every time a record in the replica is added, modified, or deleted, several records are added to this replica transaction log, i.e. one record for each subscriber for each affected table. During later synchronization, the sync program will select only those records from the log to which the synchronizer program is currently connected. In other words, if the synchronizer is replicating to subscriber "Miami" from publisher "Jim Q. Salesman", only those transaction log entries that have SubscriberID "Miami" will be sent across the connection during that remote session. The records that still remain in the log after synchronization will be sent when the system makes a connection to other subscribers.

The final set of fields needed in the table to be replicated is DateStamp (LONG), and TimeStamp (REAL). These two fields are set by the application program when the records are written. As the synchronizer reads the tables, it looks for records that have been modified since the last synchronization (stored in the transaction log). A comparison will be made between the DateStamp/TimeStamp in the replicated table and the DateStamp/TimeStamp field in the replica transaction log to determine if a transfer is necessary or desired. This is also needed to handle conflict resolution where two or more replicas made changes to the same record, but in different ways. For example, perhaps one user changed the phone number and saved the record. Another user may have changed the zip code and saved the record. Only one record "wins". Which one wins is determined by looking at the dates and times of modification. An exception report could be printed by the synchronizer that will advise an administrator of a conflict. It is up to the administrator to resolve this type of conflict and making corrections to the record if necessary.

Now finally, in the dictionary editor, copy the definitions for the "Replica" transaction log file, the "Subscriber" file, and any replicated tables to another identical set of files in the dictionary. This second set of files represents the "remote" copy of them. Set the file properties so that the "Full Pathname" uses something like the following:

S:\Replica\Replica.Tps (Table name would be ReplicaRemote)

S:\Replica\Subscribe.Tps (Table name would be SubscribeRemote)

S:\Replica\Customer.Tps (Table name would be CustomerRemote)

Note the use of S: as part of the path name. When dial up networking is employed to make the connection, we will map drive S: to the remote server hard disk. By placing these pointers into the dictionary we can painlessly access the remote copy of these files from within our synchronizing program as easily as we are already accessing the local copy. Drive C: will represent the local replica, and drive S: will represent the remote replica, while dial up networking is active and the sync program running. Within the source code for our synchronizer, when we reference the file label "Replica", we will be accessing the local copy of the transaction log. When we reference the file label "ReplicaRemote", we will be accessing the remote copy of the transaction log. Thus, our synchronizer will be able to handle record copy in two different directions without any reliance on anything other than dial-up networking and a remote share. The same goes for "Subscribe" vs. "SubscribeRemote", and "Customer" vs. "CustomerRemote". Get it?

Now, how do we do the networking?

The process of synchronization happens in two end-user steps. First, the end-user clicks an icon on the desktop (a dial-up networking icon), to connect to the main network. This will also cause a remote drive letter S: to be mapped. Second, the synchronizer program is run. This program accesses drive C: (the local drive) and drive S: to perform the synchronization between the two copies of the database. After synchronization, the program terminates, and the end-user disconnects from the dial-up server.

There is an easy way to handle simple networking using Microsoft PLUS for Windows 95, and simple dial-up connections. Using this type of networking, synchronization will take anywhere from a couple of minutes to a couple of hours, depending upon the quality of the connection and how many records are modified through the day. You can read the instructions that come with the Plus software (in the help screens), or study the Windows 95 or NT Resource Kits, or just follow the instructions below.

Note that the computer on the main network is termed the dial-up server. The remote client computer will "dial in" to the dial-up server. The remote client will then exchange its information with the main network using the dial-up server as the bridge to the database.

First, a computer on the main network must be designated as a dial-up server. It is possible, though not necessary, to make this the same computer as the one that holds your main database on its hard drive. If you have a local Ethernet network, then you can make any Windows 95 computer the dial-up server, and the system can go across the LAN to access the files. If you are using Windows NT for this computer, then simply configure RAS (Remote Access Service) to handle the dial-ups. If you are using a Windows 95 computer, you need to load a copy of Microsoft PLUS. This includes a free dial-up server program. After installation of the program, you set it up as follows:

Go to the Dial-Up Networking icon under Startà Programsà Accessoriesà Communications.
Select the Connections menu from the Dial-Up Networking window.
Select Dial-Up Server from the Connections menu. Note that this option is only present after installing Microsoft PLUS. Click the option for "Allow Caller Access". Click OK to save your changes.
Now install the following network components into your Networks icon of Control Panel: a) The NetBEUI protocol is bound to the Dial-Up Adapter. b) Client for Microsoft Networks c) File and Printer Sharing for Microsoft Networks.
On the Network icon of Control Panel under the Identification tab, select a unique name for this server, and assign a name to the entire workgroup of computers. Note that all computers connected must have the same workgroup name but a different computer name.
Make sure that the computer that has the database files on its hard disk is a member of the workgroup. Then right-click drive C from the My Computer icon of the computer that has the files and select the Sharing option. Give it a share name such as DATA_C.

At the client end, set up the Dial-Up Networking icon to dial the phone number of the dial-up server. This is done as follows:

Go to the Dial-Up Networking icon under Startà Programsà Accessories.
Select the "Make New Connection" icon.
Type a name for the dial-up server you are calling and click Next.
Type the area code and phone number to the dial-up server and click Next.
Click Finish to create the connection icon.
Right-click the newly created icon and select Properties.
From the "Server Types" tab, check "Log on to Network" and "Enable Software Compression". Also check the "NetBEUI" protocol and leave everything else unchecked.
Click OK to save your changes.
Now install the following network components into your Networks icon of Control Panel: a) The NetBEUI protocol is bound to the Dial-Up Adapter. b) Client for Microsoft Networks.
Under Network icon of Control Panel, on the Identification tab, give this computer a unique name, but use the same workgroup name you used on the dial-up server.

Now the client computer will be able to call the dial-up server computer. Now you must
"map" a drive letter that points to the shared resource on the main network. To do this, perform the dial up by double-clicking the icon you created under Dial-Up Networking and connect. After connection, select Start and Run. Type \\ComputerName at the Run prompt. Now a version of Network Neighborhood will appear that shows that computer’s resources. Right-click the share name (DATA_C) of the hard drive that contains your database files, and map a drive letter (such as S:) to this drive. We will refer to drive S: later in the synchronization program. When you reconnect, the dial-up should automatically re-establish the S: connection.

At this point, any DOS or Windows application can access drive S: to copy files back and forth. We can also point our application (the synchronizer in this case) to read the data file records from the remote drive S: just as easily as it reads from drive C:.

I would also drag and drop a shortcut to this Dial-Up Networking Connection icon to the desktop. This will make it easier for your client user to get online prior to synchronization by giving them an icon they can just double-click to get online.

This completes the network step.

What do I do to the application?

First, your application should check an INI file to obtain the site code for the local replica. Next, have it read the table containing the subscriber information into a queue, so it will be processed faster. Embed the following code into your Main procedure WindowEventsà OpenWindow embed:

GlobalSiteCode = GETINI('Replication','SiteCode',1)
ReadSubscriberQueue

This will read the Site Code value from the Win.Ini file, defaulting it to 1 if it is not present. Remember that each site must have a different site code value in its INI file. For example, New York could be 1, Miami could be 2, etc.

The subscriber queue is defined as:

SubscriberQueue          QUEUE
SubscriberID         STRING(10)
TableName            STRING(40)
HandoffTo            STRING(10)
                   END

The ReadSubscriberQueue procedure is a "Process" that uses the following code embedded at the LocalObjectsà ThisProcessà TakeRecord embed.

      SubscriberQueue.SubscriberID = SUBSCRIBE:SubscriberID
      SubscriberQueue.TableName = SUBSCRIBE:TableName
      SubscriberQueue.HandoffTo = SUBSCRIBE:HandoffTo
      ADD(SubscriberQueue)

After you have set this up, you are ready to cause the form the flag modifications. To do this, embed the following code into the Control Eventsà ?Okà Accepted embed of each data entry form (or any other file update) that needs to support replication:

      UpdateReplicaLog(CONTACTS:GUID,'Contacts',SELF.Request)
      CONTACTS:DateStamp = TODAY()
      CONTACTS:TimeStamp = CLOCK()

Substitute the name of the GUID field for "CONTACTS:GUID", the name of the table for "Contacts", and set the correct datestamp and timestamp fields for your file.

The definition for the UpdateReplicaLog procedure is as follows:

UpdateReplicaLog PROCEDURE (AssignedGUID,FileName,Action)

  CODE
     Access:Replica.Open
     LOOP I# = 1 to RECORDS(SubscriberQueue)
      GET(SubscriberQueue,I#)
      IF SubscriberQueue.TableName <> FileName Then CYCLE.
      REPLICA:GUID = GenerateGUID()
      REPLICA:SubscriberID = SubscriberQueue.SubscriberID
      REPLICA:TableName = FileName
      REPLICA:TableGUID = AssignedGUID
      REPLICA:UpdateAction = Action
      REPLICA:DateStamp = TODAY()
      REPLICA:TimeStamp = CLOCK()
      REPLICA:ExpectedGeneration = 0
      REPLICA:ConflictFlag = FALSE
      REPLICA:HandoffTo = SubscriberQueue.HandoffTo
      IF Access:Replica.Insert() <> LEVEL:Benign
        MESSAGE('Could not write to replica log.')
      END
    END
    Access:Replica.Close

Don't forget to include the definition of the GenerateGuid function we discussed earlier somewhere in the app file. That's all that you need to do to your application.

Now we are ready to synchronize the tables

The actual synchronization process is handled by a simple Clarion for Windows program. The source code for a basic synchronizer is shown below. The program will first open both sets of databases, presenting an error if it is unable to. It then performs synchronization in two phases. In the first phase, records from the local tables are sent to the remote. In the second phase, records from the remote tables are downloaded to the local. In each phase, we check for conflicts as well as processing the "hand-offs". The code is fairly easy to understand.

You may download this code from www.cdcaug.com/clarion, and I would recommend that you do so. Contained in the download is a sample dictionary and an application that has been set up to support replicas, along with the following synchronizer source code.

Main                 PROCEDURE                        ! Declare Procedure
AttachedSubscriber   STRING(20)
PhaseString          STRING(40)
Progress1            BYTE
Counter              LONG
Recs                 LONG
LocalPublisher       STRING(20)

window WINDOW('Replica Synchronizer'),AT(,,216,78),GRAY
       STRING('Synchronizing Replicas ... Please wait ...'),AT(40,14),USE(?String1)
       STRING(@s40),AT(38,27),USE(PhaseString),CENTER
       PROGRESS,USE(Progress1),AT(38,44,140,8),RANGE(0,100)
     END
CODE

OPEN(Window)                        ! Open status window
DISPLAY

AttachedSubscriber = COMMAND('SUBSCRIBER')
LocalPublisher = COMMAND('PUBLISHER')

SHARE(Replica)                      ! Open files
Do CheckLocalError
SHARE(Contacts)
Do CheckLocalError
STREAM(Replica)
STREAM(Contacts)

SHARE(ReplicaRemote)
Do CheckRemoteError
SHARE(ContactsRemote)
Do CheckRemoteError

STREAM(ReplicaRemote)
STREAM(ContactsRemote)
!---------------------------------------------------------------------------
! Now we have confirmed that we have a live connection to the remote, and
! that all necessary files on the local and remote systems are ready to
! be synchronized.
!---------------------------------------------------------------------------
Do PhaseOne                         ! Process Phase one of Replication
Do PhaseTwo                         ! Process Phase two of Replication

FLUSH(Replica)                      ! End of processing
FLUSH(ReplicaRemote)
FLUSH(Contacts)
FLUSH(ContactsRemote)

CLOSE(Replica)
CLOSE(ReplicaRemote)
CLOSE(Contacts)
CLOSE(ContactsRemote)

CLOSE(Window)

RETURN

PhaseOne    ROUTINE
!---------------------------------------------------------------------------
! PHASE ONE:
!
! In phase one, we will send entries from the database to be
! synchronized to the remote system.
!
! We may also send copies of the log entry to the remote, if it has
! been determined that the remote computer is a "pass-thru", i.e. it
! will perform synchronization for other subscribers from the local
! publisher.
!
! Send All Log Entries that are destined for the remote system to
! the remote. As the entries are sent, determine if the log entry
! should be "handed off" to the remote (so that the remote can
! replicate the entry to other systems on its own.) If the log
! entry is not to be "handed off" it is simply erased from the
! replica log file after the primary database record is copied.
! If it is to be handed off, the local system will copy both the
! primary database record pointed to by the log, as well as the log
! entry itself to the remote replica.
!---------------------------------------------------------------------------

PhaseString = 'Phase 1 of 2 ...'
DISPLAY
Counter = 0
Recs = RECORDS(Replica)

SET(Replica)                        ! Read the replica log file
LOOP                                ! Start reading records destined for this remote
  NEXT(Replica)
  IF ERROR() Then Break.            ! Stop reading upon end of file
                                    ! Should we synchronize the main database?

  Counter += 1
  Progress1 = (Counter / Recs) * 100
  DISPLAY                           ! Display progress indicator

  IF REPLICA:SubscriberID = AttachedSubscriber  ! If so, then
    Do SynchronizeRemote                        ! synchronize the main database
  END
                                    ! Do we "hand off" some of the replica log entries
                                    ! to be processed by this remote?
  IF REPLICA:HandoffTo = AttachedSubscriber     ! If so, then
    Do SendLocalReplicaLog                      ! send the log entries
    Do SynchronizeRemote                        ! and synchronize the main database
  END
END

PhaseTwo ROUTINE
!---------------------------------------------------------------------------
! PHASE TWO:
!
! In phase two, we will receive entries from the database to be
! synchronized to the local system.
!
! We may also receive copies of the log entry from the remote, if it has
! been determined that the local computer is a "pass-thru", i.e. it
! will perform synchronization for other subscribers from the local
! publisher.
!
! Send All Log Entries that are destined for the local system to
! the local. As the entries are sent, determine if the log entry
! should be "handed off" to the local (so that the local can
! replicate the entry to other systems on its own.) If the log
! entry is not to be "handed off" it is simply erased from the
! remote replica log file after the primary database record is copied.
! If it is to be handed off, the local system will copy both the
! primary database record pointed to by the log, as well as the log
! entry itself from the remote replica.
!---------------------------------------------------------------------------

PhaseString = 'Phase 2 of 2 ...'
DISPLAY

Counter = 0
Recs = RECORDS(ReplicaRemote)

SET(ReplicaRemote)                   ! Read the replica log file
LOOP                                 ! Start reading records destined for this remote
  NEXT(ReplicaRemote)
  IF ERROR() Then Break.             ! Stop reading upon end of file
                                     ! Should we synchronize the main database?

  Counter += 1
  Progress1 = (Counter / Recs) * 100
  DISPLAY                            ! Display progress indicator

  IF REPLICAREM:SubscriberID = LocalPublisher   ! If so, then
    Do SynchronizeLocal                         ! synchronize the main database
  END

                                     ! Do we "hand off" some of the replica log entries
                                     ! to be processed by this remote?

  IF REPLICAREM:HandoffTo = LocalPublisher      ! If so, then
    Do GetRemoteReplicaLog                      ! send the log entries
    Do SynchronizeLocal                         ! and synchronize the main database
  END
END

CheckLocalError     ROUTINE

                    IF ERROR()
                      MESSAGE('A file on this local system could not be opened.|
Terminating Synchronizing Process.','Fatal Error',ICON:Exclamation,BUTTON:OK)
                      RETURN
                    END

CheckRemoteError    ROUTINE

                    IF ERROR()
                      MESSAGE('A file on the remote system could not be opened.|
Terminating Synchronizing Process.','Fatal Error',ICON:Exclamation,BUTTON:OK)
                      RETURN
                    END

SynchronizeRemote   ROUTINE

!---------------------------------------------------------------
! This routine will write the data record into the main database
! file that is pointed to by the current entry in the local
! replica log. Perform replication on the file named by the
! replica log entry. The log entry is removed from the local
! replica log when completed. Also, test for any conflicts
! found while synchronizing.
!---------------------------------------------------------------
IF REPLICA:TableName = 'Contacts'
  IF REPLICA:UpdateAction = 3               ! If record is being deleted
    CONTACTSREM:GUID = REPLICA:TableGUID    ! retrieve the remote copy
    GET(ContactsRemote,CONTACTSREM:GUIDKey) ! by the GUID key
    IF NOT ERROR()                          ! If no problem retrieving
      DELETE(ContactsRemote)                ! Then erase the record from the remote database
      DELETE(Replica)                       ! and remove the replica log entry
    ELSE                                    ! otherwise
      REPLICA:ConflictFlag = TRUE           ! set the conflict flag to true
      PUT(Replica)                          ! and don't sync the deletion
    END
  ELSIF REPLICA:UpdateAction = 2            ! If record is being changed
                                            ! has another user updated record newer?
                                            ! If so, set conflict flag to true
                                            ! and don't sync the change
    CONTACTSREM:GUID = REPLICA:TableGUID    ! retrieve the remote copy
    GET(ContactsRemote,CONTACTSREM:GUIDKey)     ! by the GUID key
    IF NOT ERROR()                              ! If the record was over there
                                                ! check for update conflict
      IF (CONTACTSREM:DateStamp > REPLICA:DateStamp)|
      OR ((CONTACTSREM:DateStamp = REPLICA:DateStamp)|
      AND (CONTACTSREM:TimeStamp > REPLICA:TimeStamp))
        REPLICA:ConflictFlag = TRUE
        PUT(Replica)
      ELSE
        CONTACTSREM:GUID = REPLICA:TableGUID    ! Make sure they don't already have it
        GET(ContactsRemote,CONTACTSREM:GUIDKey) ! by looking it up with the GUID Key
        IF NOT ERROR()                          ! If the record was already there
          CONTACTSREM:Record = CONTACTS:Record  ! If no conflict, update the remote copy
          PUT(ContactsRemote)
          DELETE(Replica)                       ! and remove the replica log entry
        ELSE
          REPLICA:ConflictFlag = TRUE
          PUT(Replica)
        END
      END
   ELSE
     REPLICA:ConflictFlag = TRUE                 ! set the conflict flag to true
     PUT(Replica)                                ! and don't sync the change
   END
 ELSIF REPLICA:UpdateAction = 1                  ! If record is being added
   CONTACTSREM:GUID = REPLICA:TableGUID          ! Make sure they don't already have it
   GET(ContactsRemote,CONTACTSREM:GUIDKey)       ! by looking it up with the GUID Key
   IF NOT ERROR()                                ! If the record was already there
     REPLICA:ConflictFlag = TRUE                 ! set the conflict flag to true
     PUT(Replica)                                ! and don't sync the addition
    ELSE
      CONTACTS:GUID = REPLICA:TableGUID          ! Retrieve local copy
      GET(Contacts,CONTACTS:GUIDKey)
      IF NOT ERROR()
        CONTACTSREM:Record = CONTACTS:Record     ! If no conflict, update the remote copy
        ADD(ContactsRemote)
        DELETE(Replica)                          ! and remove the replica log entry
      ELSE
        REPLICA:ConflictFlag = TRUE              ! set the conflict flag to true
        PUT(Replica)                             ! and don't sync the addition
      END
    END
  END                                            ! End of testing for Update Action
END                                              ! End of testing for table name

SynchronizeLocal ROUTINE
!---------------------------------------------------------------
! This routine will write the data record into the main database
! file that is pointed to by the current entry in the remote
! replica log. Perform replication on the file named by the
! replica remote log entry. The log entry is removed from the
! remote replica log when completed. Also, test for any conflicts
! found while synchronizing.
!---------------------------------------------------------------
IF REPLICAREM:TableName = 'Contacts'
  IF REPLICAREM:UpdateAction = 3                 ! If record is being deleted
    CONTACTS:GUID = REPLICAREM:TableGUID         ! retrieve the local copy
    GET(Contacts,CONTACTS:GUIDKey)               ! by the GUID key
    IF NOT ERROR()                               ! If no problem retrieving
      DELETE(Contacts)                           ! Then erase the record from the local database
      DELETE(ReplicaRemote)                      ! and remove the replica log entry
    ELSE ! otherwise
      REPLICAREM:ConflictFlag = TRUE             ! set the conflict flag to true
      PUT(ReplicaRemote)                         ! and don't sync the deletion
    END
  ELSIF REPLICAREM:UpdateAction = 2              ! If record is being changed
                                                 ! has another user updated record newer?
                                                 ! If so, set conflict flag to true
                                                 ! and don't sync the change
    CONTACTS:GUID = REPLICAREM:TableGUID         ! retrieve the remote copy
    GET(Contacts,CONTACTS:GUIDKey)               ! by the GUID key
    IF NOT ERROR()                               ! If the record was over there
                                                 ! check for update conflict
      IF (CONTACTS:DateStamp > REPLICAREM:DateStamp)|
      OR ((CONTACTS:DateStamp = REPLICAREM:DateStamp)
      AND (CONTACTS:TimeStamp > REPLICAREM:TimeStamp))
        REPLICAREM:ConflictFlag = TRUE
        PUT(ReplicaRemote)
      ELSE
        CONTACTSREM:GUID = REPLICAREM:TableGUID  ! Retrieve remote copy
        GET(ContactsRemote,CONTACTSREM:GUIDKey)
        IF NOT ERROR()
          CONTACTS:Record = CONTACTSREM:Record   ! If no conflict, update the local copy
          PUT(Contacts)
          DELETE(ReplicaRemote)                  ! and remove the replica log entry
        ELSE
          REPLICAREM:ConflictFlag = TRUE         ! set the conflict flag to true
          PUT(ReplicaRemote)                     ! and don't sync the change
        END
      END
    ELSE
      REPLICAREM:ConflictFlag = TRUE             ! set the conflict flag to true
      PUT(ReplicaRemote)                         ! and don't sync the change
    END
  ELSIF REPLICAREM:UpdateAction = 1              ! If record is being added
    CONTACTS:GUID = REPLICAREM:TableGUID         ! Make sure they don't already have it
    GET(Contacts,CONTACTS:GUIDKey)               ! by looking it up with the GUID Key
    IF NOT ERROR()                               ! If the record was already there
      REPLICAREM:ConflictFlag = TRUE             ! set the conflict flag to true
      PUT(ReplicaRemote)                         ! and don't sync the addition
    ELSE
      CONTACTSREM:GUID = REPLICAREM:TableGUID    ! Retrieve remote copy
      GET(ContactsRemote,CONTACTSREM:GUIDKey)
      IF NOT ERROR()
        CONTACTS:Record = CONTACTSREM:Record     ! If no conflict, update the remote copy
        ADD(Contacts)
        DELETE(ReplicaRemote)                    ! and remove the replica log entry
      ELSE
        REPLICAREM:ConflictFlag = TRUE           ! set the conflict flag
        PUT(ReplicaRemote)                       ! and don't sync the addition
      END
    END
  END                                            ! End of testing for Update Action
END                                              ! End of testing for table name

SendLocalReplicaLog ROUTINE

!-----------------------------------------------------------
! This routine will send the local replica log entry to the
! remote replica log. It will move the handoff information
! into the subscriber data field, so that the remote system
! appears to originate the transaction. This allows the
! remote to propagate the updates to other machines.
!-----------------------------------------------------------

REPLICAREM:Record = REPLICA:Record
REPLICAREM:SubscriberID = REPLICAREM:HandoffTo
REPLICAREM:HandoffTo = ''
  ADD(ReplicaRemote)
  GetRemoteReplicaLog ROUTINE
REPLICA:Record = REPLICAREM:Record
REPLICA:SubscriberID = REPLICA:HandoffTo
REPLICA:HandoffTo = ''
  ADD(Replica)

Take notice of the IF statements that are used to perform conflict detection. They are simply checking the timestamps of the records to see which copy is newer. Here is where you may modify the code to perform other types of conflict detection and resolution.

The source code above is straightforward and easy to implement. Please use it to your heart's content, and send me your modifications, bug fixes (if you find any <g>), and comments to me at jerry@cdcaug.com.

See you next month!

Downloads

Editor's note: The download page mentioned in the article is no longer online, but thanks to Dinko Bacun we now have most of the files. If you have any of the remaining files, or know who might, please contact the editor.

DevCon 98 Presentation Files:

Dial-Up Networking Automation for use with Replication:

Other Data Replication Strategies:

Printer-friendly version

Copyright © 1999-2008 by CoveComm Inc. All Rights Reserved. Reproduction in any form without the express written consent of CoveComm Inc., except as described in the subscription agreement, is prohibited.

Clarion Magazine ISSN 1718-9942