ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / WLanguage / Managing databases / HFSQL / Managing replications / Universal replication
  • Overview
  • Implementing universal replication
  • Operating mode of replication
  • Database structure
  • Step 1: Implementation on the master site
  • Step 2: Preparing the data of the subscriber site
  • Step 3: Implementation on the subscriber site
  • Step 4: Initial synchronization between the master and subscriber sites
  • Step 5: Daily synchronizations
  • Remarks
  • Limitations
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Overview
The purpose of universal replication is to keep several databases synchronized. These databases can have different types. A replication can be performed:
  • between an HFSQL database and an Oracle database.
  • between HFSQL Classic or HFSQL Client/Server databases.
  • ...
Universal replication uses a centralized model: all databases are synchronized with a master database. Then, the master database reflect the changes in the other databases.
To easily implement universal replication, it is recommended to use assisted universal replication.
Remark: Universal replication is available for the replication of mobile data (Android or iOS). For more details, see Replication of mobile data (Android or iOS).
Universal replication uses several types of files:
  • .RPM file: file used to describe a master database as well as its subscriber databases. Contains the name of the .SYN file for each subscriber. This file is a text file. It is created and stored on the computer that will be used to connect to the master database.
  • .RPL file: file describing a subscriber database. A RPL file is created for each subscriber database. This file is found on the subscriber computer. Contains the name of the .SYN file of the subscriber. This file is a text file. It is created on the master computer then transferred to the subscriber computer.
  • .RPA file: log file containing the replication information. This file is exchanged between the master database and the subscriber database. This file is created by HCreateMovableReplica, and it is read and deleted by HSynchronizeReplica. The content is incremented: if two HCreateMovableReplica are performed one after another in the same direction, the first file and the second file will have the same content but the second file will also contain the recent modifications. If a .RPA file is "lost", simply create it again: it will contain all the modifications. If there are multiple RPA files on the subscriber, simply run the last one. This file is in binary format.
  • .SYN file: file containing information about the situation of the remote database. This file is used to optimize the size of the synchronization files. This file is found on the master computer and on each subscriber computer. This file is in binary format.
Implementing universal replication

Operating mode of replication

Database structure

Implementing a universal replication does not require to modify the structure of the HFSQL databases (Classic or Client/Server).
On the contrary, to implement universal replication on external databases (Oracle, ...), a DateTime item must be created in each file taken into account by the replication. This item will have to be updated by the application when modifying or adding a record.
If the databases use different time zones, we recommend that you use a universal format (GMT date and time for example).
Several specific functions can also be used when implementing universal replication on non-HFSQL databases:
HRplDeclareLinkUsed to signal to the replication engine that a link was found between two files. The engine will follow the link to get the list of records that must be replicated in the second file.
HRplFilterProcedureUsed to specify a specific filter procedure when a given file is replicated.
HRplManageFileDefines the options used for the universal replication of a file.
HRplManageItemDefines the options used for the universal replication of an item.
For more details, see Replication functions.

Step 1: Implementation on the master site

This step is used to create the main database of the application. This step must be performed during the first execution of the program (or via a specific program dedicated to the administrator).
  1. To enable universal replication, simply use HSetReplication with the rplReplicationUniversal constant.
    HSetReplication(rplReplicationUniversal)

    This function is used to disable the standard replication mode (if it was enabled) and to enable universal replication.
  2. Creating the base directory of data (optional)
    You have the ability to create a directory that will receive the database files. This directory will be shared on the server of the master site in order for the users to access the data.
    // Create the data directory
    fMakeDir(gsMasterDirectory)
    // Connect to this location
    HOpenConnection(MasterConnection)
    HChangeConnection(CLIENT, MasterConnection)
  3. Creating the data files of the application (optional)
    If the data files do not exist, you have the ability to create empty data files (HCreation). This data will be handled by the users of the master site.
  4. Creating the master replica
    To declare the master database, all you have to do is use HCreateMasterReplica.
    HCreateMasterReplica(gsMasterDirectory)

    This line of code creates the MasterReplica.RPM file on disk. Then, all you have to do is write the subscribers into this file.

Step 2: Preparing the data of the subscriber site

This step is used to create a second database. This step is performed on the "master" site. This step must be performed via a specific option of the application (or via a specific program dedicated to the administrator).
  1. Creating a home directory for the data (optional)
    You have the ability to create a directory that will receive the database files.
  2. Creating the subscriber replica
    To create a new subscriber, use HCreateSubscriberReplica. This function creates a subscriber (RPL file) with the specified name. This functions also returns a subscriber number.
    As soon as it is created, the subscriber is ready to receive the data from the master replica that was used to initialize it. This first synchronization is used to make sure that the content of the subscriber identical to the content of the master.
    // Create the subscriber replica
    HCreateSubscriberReplica(gsMasterDirectory, gsSubscriberDirectory, ...
    "Subscriber1", 0, "Customer" + TAB + "DATETIMEID")

    In this example, the "DATETIMEID" item corresponds to a "DateTime" item that must be available to the database (for a database not in HFSQL Classic or Client/Server format). This item will have to be updated by the application when modifying or adding a record. If the databases use different time zones, we recommend that you use a universal format (GMT date and time for example).
    This item is not required for an HFSQL Classic or Client/Server database.
    Remark: A parameter of HCreateSubscriberReplica indicates whether the automatic data modification must be taken into account. For more details, see Remarks.
Example: Manipulating a specific item for the replication between an HFSQL Classic database and a MySQL database:
A trigger is implemented in order to automatically fill the "Itm_DateTime" item found in the MySQL database:
  • Code of the trigger:
    TriggerResult is boolean
    TriggerResult = HDescribeTrigger("*", ...
    "HADD,HMODIFY,HDELETE,HCROSS,HWRITE", ...
    "AddDateTime",hTriggerBefore)
    IF TriggerResult = False THEN Error(HErrorInfo())
  • Procedure called by the trigger:
    PROCÉDURE AjoutDateHeure()
    TestReplic.Rub_DateHeure = DateSys() + TimeSys()

Step 3: Implementation on the subscriber site

  1. When the data is "prepared" for the subscriber site (.rpl, .syn files), all you have to do is enable the replication mechanism on the subscriber site (HSetReplication).
    HSetReplication(rplReplicationUniversal)
  2. The subscriber site must create its blank database (HCreation) before the first synchronization:
    HCreation(CUSTOMER)

    This database will be initialized during the first synchronization.

Step 4: Initial synchronization between the master and subscriber sites

This step is required in order for universal replication to operate properly. Via the initial synchronization, the subscriber database must contain all or part of the master database (before the subscriber can be used).
  1. Creating a movable replica:
    A movable replica will be created from the master database. it will contain the data of the database from which the master replica was created. This operation is performed with HCreateMovableReplica. HCreateMovableReplica creates a log file (.RPA file).
    HCreateMovableReplica(sMasterReplica, "Master","")
  2. Including the master replica in the subscriber database:
    The data found in the master database is imported into the subscriber database by HSynchronizeReplica.
    sMovableReplica= gsMasterDirectory +RPL.File
     
    HSynchronizeReplica(sMovableReplica, ...
    gsSubscriberDirectory + "Subscriber_Replica1.RPL", ...
    rplToSubscriber)
    fDelete(sMovableReplica)
  3. The subscriber is now created. The subscriber files can now be moved to the subscriber site. This operation can be performed via any type of media (FTP, CD ROM, ...), the main purpose being to create an empty database and to perform a fist replication on the site.
Caution:
  • As soon as a subscriber replica is initialized, you must no longer replace/restore one of the master files (because they contain information regarding the ranges of identifiers for the subscriber replicas).
  • All the database files do not necessarily have to be replicated. Replicate the necessary data files only.

Step 5: Daily synchronizations

Once the initial synchronization was performed, the replication mechanism is ready to operate in "Standard" mode.
To synchronize a database, you must:
  1. Generate a movable replica (HCreateMovableReplica).
  2. Transmit the movable replica to the relevant site (master or subscriber).
  3. Synchronize the database via the movable replica via HSynchronizeReplica.
These operations can be initiated from the "Master" site or from the "Subscriber" sites.
The type of replication can be configured in order to define the operating mode of the replication. The different types of replications are as follows:
  • rplMasterFirst: this replication mode is the default mode. It is used to protect the database of the "master" site from the modifications performed in the "subscriber" site. The modifications made to the subscriber sites will not be carried over in this mode (except for the additions).
  • rplSubscriberFirst: in this replication mode, the subscriber sites have priority over the master site. In this mode, the master site is a copy of the different subscribers but the modifications are not carried over among the different subscribers.
  • rplMostRecentFirst: this mode is used to propagate all the modifications performed on the databases. This operating mode must be used when all the databases have the same priority (the modifications are carried over to all the databases).
You also have the ability to define a custom replication procedure: this allows you to define the priorities used during the synchronization mechanism.
Remarks
  • Check the replicated files. All the database files do not necessarily have to be replicated. Replicate the necessary data files only.
  • The exchanges of ".RPA" files are not necessarily symmetrical: you can create and run several logs in one direction without creating and running a single log in the other direction. However, to improve the performances, you should avoid performing exchanges always in the same direction.
  • The replication respects the filters applied to the tables or to the files (except for links, to respect the integrity).
  • A filter can be implemented on the master side: the subscribers will receive a sub-set of data from the master database. In this case, there is no need to implement a filter for the subscriber database.
    If, due to the filter, a record is not "visible" anymore on the master database, this record will be considered as being deleted. It will be automatically deleted from the subscriber computer.
  • HRecreateSubscriberReplica is used to re-create a subscriber replica from the master replica.
  • Take automatic data modification into account (HFSQL Classic or Client/Server databases):
    When creating the subscriber replica with HCreateSubscriberReplica, you can specify whether the automatic data modification must be performed during replication. In this case:
    • Changes in the structure of the master database will be carried over to the subscriber database. This will be done during replication.
      Caution: This can significantly increase the time required for replication.
    • The new items will be taken into account by the replication.
    Remarks:
    • This mechanism does not work if a unique key is added or deleted..
    • For an existing replication, it is necessary to recreate this replication (as well as the subscribers) to take into account this functionality.
Limitations
  • Each file or table must have at least one unique key.
  • You must have the ability to associate a date with a record. If the item used to associate a date with the record is automatically filled by a trigger, this trigger must be disabled when running HSynchronizeReplica (to avoid filling this item with the replication date).
    Remark: For the HFSQL databases (Classic or Client/Server), the item used to associate a date with the record is automatically managed.
  • The replication does not open the files or the tables. The files or the tables must have be opened by the program before starting the replication.
  • During the replication, the memory consumption may be very high. Therefore, only the necessary records should be replicated toward a subscriber computer.
  • Caution: special case: some of the linked tables are not replicated. In this case, the replication may replay some modifications as an {addition, deletion} couple. If a non-replicated table is linked with cascading modifications, the linked records may be wrongly deleted.
  • Replication will not be performed in the following case: one of the files to be replicated is secured, protected by a password and is used in a link with integrity rules.
Related Examples:
WD Universal Replication Training (WINDEV): WD Universal Replication
[ + ] This examples explains how to use the universal replication to synchronize data of different sites.
The universal replication is used to synchronize the data of a site (master site) with the same data of one or more replicated sites (subscriber sites). The data structures are identical on each site but they can be exploited via different data managers. HFSQL Classic and Access will be used in this example.
The example presents the processes that must be included in your applications to allow the user, via a simple action (menu, button, ...), to:
- create a master replica,
- create one or more subscriber replicas,
- see and modify the data of these replicas,
- export the created or modified data to a site (portable replica),
- import the created or modified data into another site...
Minimum version required
  • Version 10
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 06/23/2023

Send a report | Local help