1600 - Creating a Join Between Two Tables for Replication

To configure a replication based on a join between two tables, you need to

1. Create replications from each source table to a single target table.
2. Define scripts for each replication to handle the join and to prevent truncation data from the target table.
3. Put both replications in a replication group.
4. Clean up the target table by removing all records.
5. Run the replication.

Use the following example to help you set up a replication which involves a join of two source tables.

Assume that you have the scenario below:

The STUDENTRESULT table is a de-normalized table obtained by a join between the STUDENT and RESULT tables. There is no need to replicate the STUDENT or the RESULT table separately because the STUDENTRESULT table contains the same information.

For Syniti DR/DBMoto to replicate effectively, all the tables must have a primary key. The target table must have the primary keys defined as constraints.

 - For the STUDENT table the primary key is SID

 - For the RESULT table the primary keys are SID, COURSE

 - For the STUDENTRESULT table the primary keys are SID, COURSE

Important: To enhance performance, create an index on the target table STUDENTRESULT.SID field. You do not need to do this if the field order in the primary key definition for table STUDENTRESULT is SID, COURSE.  You need to create another index for the SID field if the field order in primary key is COURSE, SID)

Create Two Replications

Create the following replications where the target table is the same in both cases. Be sure that the data replicator is not running, or disable the replications when you create them, to avoid starting a replication before the setup is complete.

STUDENT-> STUDENTRESULT

RESULT->STUDENTRESULT.

1. Configure the replication for STUDENTRESULT table.
2. The source table is RESULT, target table is STUDENTRESULT.
3. Map the fields RESULT.SID, RESULT.COURSE, RESULT.MARK to the fields STUDENTRESULT.SID, STUDENTRESULT.COURSE, STUDENTRESULT.MARK
4. Map the field STUDENTRESULT.NAME to vbNull (this field is not mapped but will be used in the script)
5. Define a script for the replication.
6. Add the following VB.NET script:

'Sample Code Behavior:
‘- Add
'  When a new record is added DBMoto searches the matching record on table STUDENT.
'  If Student is found, the field NAME is set to the correct value otherwise is set to Null'
‘- Update
'  If field SID has changed then
'    the matching record on the STUDENT table is searched.
'    If Student is found, the NAME field is set to the correct value, otherwise it is set to Null
'  Else
'    the NAME field remains unchanged
'- Delete
'  Record is simply deleted

Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript

Namespace DBRS

    Public Class ReplicationScript : Inherits IreplicationScript
        Public Overrides Sub Refresh_onBeforeTruncate(ByRef CancelTruncate As Boolean)
            CancelTruncate = True
        End Sub

        Public Overrides Sub Record_onAfterMapping(recSource As IRecord, recTarget As IRecord, ByRef AbortRecord As Boolean)
            Select Case recTarget.OperationType
              Case enmOperationType.Insert
                If recTarget.GetValueAfter("SID") = Nothing Then
                  recTarget.SetValueAfter("NAME", Nothing)
                Else
                  UpdateSTUDENTFields (recTarget)
                End If
              Case enmOperationType.Update
                If recTarget.GetValueAfter("SID") = Nothing Then
                  recTarget.SetValueAfter("NAME", Nothing)
                ElseIf recTarget.GetValueBefore("SID") = Nothing Then
                  'Field SID was null and now it has a value
                  UpdateSTUDENTFields (recTarget)
                ElseIf recTarget.GetValueBefore("SID") <> recTarget.GetValueAfter("SID") Then
                  'Field SID has changed
                  UpdateSTUDENTFields (recTarget)
                End If
            End Select
        End Sub

        Private Sub UpdateSTUDENTFields (recTarget As IRecord)
          Dim cmd As System.Data.IdbCommand
          Dim rs As System.Data.IdataReader
          Dim SQL As String

          SQL = "Select * From STUDENT Where SID = " & recTarget.GetValueAfter("SID")

          cmd = SourceConnection.CreateCommand
          cmd.CommandText = SQL

          rs = cmd.ExecuteReader

          While rs.Read
            recTarget.SetValueAfter("NAME", rs("NAME"))
          End While
          rs.Close
        End Sub

    End Class

End Namespace

7. Configure the replication for the STUDENT table.
8. The source table is STUDENT, target table is STUDENTRESULT.
9. Map the fields STUDENT.SID, STUDENT.NAME to the same fields on target table
10. Map the fields STUDENTRESULT.COURSE, STUDENTRESULT.MARK to vbNull (these fields are not mapped but will be used in the script)
11. Define a script for the replication.
12. Add the following VB.NET script:

'Sample Code Behavior:
'- Add
'  All records on STUDENTRESULT Table with SID = [inserted value] are updated (field NAME is set to the proper value)
'- Update
'  If primary key SID has changed then
'    Field NAME of Table STUDENTRESULT with SID = [OldSID] is Set to Null.
'    Field NAME of Table STUDENTRESULT with SID = [NewSID] is set to correct value
'  Else
'    Field NAME of Table STUDENTRESULT with SID = [SID] is set to correct value
'- Delete
' Field NAME of Table STUDENTRESULT with SID = [DeletedSID] is Set to Null.
' All operations are aborted because we don’t want to propagate operations on the STUDENT table over the STUDENTRESULT that contains just results of the different exams.

Imports Microsoft.VisualBasic
Imports DBMotoPublic
Imports DBMotoScript
Imports DBRS.GlobalScript

Namespace DBRS

    Public Class ReplicationScript : Inherits IReplicationScript

       Public Overrides Sub Record_onAfterMapping(recSource As IRecord, recTarget As IRecord, ByRef AbortRecord As Boolean)
            Select Case recTarget.OperationType
              Case enmOperationType.Insert
                UpdateSTUDENTRESULT(recTarget)
                AbortRecord = True
              Case enmOperationType.Update
                If recTarget.GetValueAfter("SID") <> recTarget.GetValueBefore("SID") Then
                  SetNullSTUDENTRESULT(recTarget)
                End If
                UpdateSTUDENTRESULT(recTarget)
                AbortRecord = True
              Case enmOperationType.Delete
                SetNullSTUDENTRESULT (recTarget)
                AbortRecord = True
            End Select
        End Sub

        Public Overrides Sub Refresh_onBeforeTruncate(ByRef CancelTruncate As Boolean)
            CancelTruncate = True
        End Sub

        Private Sub UpdateSTUDENTRESULT (recTarget As IRecord)
          Dim SQL As String

          SQL = "Update STUDENTRESULT Set "

          If recTarget.GetValueAfter("NAME") = Nothing Then
            SQL = SQL + " NAME = Null "
          Else
            SQL = SQL + " NAME = '" + Replace(recTarget.GetValueAfter("NAME"),"'","''") + "' "
          End If
          if recTarget.GetValueAfter("SID") = Nothing then
             SQL = SQL + " Where SID = '" & recTarget.GetValueBefore("SID")
          else
             SQL = SQL + " Where SID = '" & recTarget.GetValueAfter("SID")
          End if
          AddLog ("UpdateSTUDENTRESULT SQL: " + SQL, 0)
          Try
            Dim cmd As System.Data.IdbCommand
            cmd = TargetConnection.CreateCommand
            cmd.CommandText = SQL
            cmd.ExecuteNonQuery()
          Catch Ex As System.Exception
              AddLog ("STUDENT.*_onAfterFieldMapping (UpdateSTUDENTRESULT): Cannot update field NAME on Table STUDENTRESULT (" + Ex.Message + ")", 1)
          End Try
      End Sub

      Private Sub SetNullSTUDENTRESULT (recTarget As IRecord)
        Dim SQL As String

        SQL = "Update STUDENTRESULT Set NAME = Null Where SID = " & recTarget.GetValueBefore("SID")
        AddLog ("SetNullSTUDENTRESULT SQL: " + SQL, 0)
        Try
            Dim cmd As System.Data.IdbCommand
            cmd = TargetConnection.CreateCommand
            cmd.CommandText = SQL
            cmd.ExecuteNonQuery()
        Catch Ex As System.Exception
            AddLog ("STUDENT.*_onAfterFieldMapping (SetNullSTUDENTRESULT): Cannot update field NAME on Table STUDENTRESULT (" + Ex.Message + ")", 1)
        End Try
      End Sub

    End Class

End Namespace 

Create a Group for the Two Replications

Create a group and include the two replications in the group. This will allow Syniti DR/DBMoto to process the replications sequentially following the master-detail sequence of operations from the transaction log.

Clean up the Target Table

Because the two replications are defined in a group, and replicate to the same table, the Refresh_onBeforeTruncate event has been defined with the parameter CancelTruncate set to True. This code prevents the situation where one replication removes data that has been replicated by the other replication

However, when CancelTruncate is set to True, you need to clean up the target table manually by removing all records BEFORE running the replication.

Run the Replication

Start the Replication Agent/Data Replicator and/or enable the replications.