Replication via SQL

4D provides a mechanism that allows data to be replicated or synchronized between two or more 4D databases via SQL. This specific functionality can be used to set up one or more mirror databases, guaranteeing permanent availability of data. 

The principle is as follows: a target database replicates the data of a remote source database locally. Updates are carried out periodically by the local database which retrieves the data from the remote database. Replication is carried out at the table level: you replicate the data of a remote database table into a table in the local database.
This is made possible by the use of stamps and specific SQL commands.

In the Structure editor, the replication mechanism is enabled via a table property in both the remote and local database. On the local side, the SQL REPLICATE command lets you retrieve data from a table in the remote database and then integrate this data into a table of the local database. As for the SQL SYNCHRONIZE command, it is used to carry out the synchronization of two tables.

Each table of the 4D database can be assigned three "virtual" fields: __ROW_ID, __ROW_STAMP and __ROW_ACTION. These fields are called "virtual" to differentiate them from "standard" fields because they have specific properties: they are automatically filled in, can be read but not modified by the users, and do not appear in the system tables of the database. The following table describes these fields as well as their mode of use:

When the replication mechanisms are enabled, as soon as a record is created, modified or deleted, the corresponding information is automatically updated in the virtual fields of this record.

By default the mechanisms that allow replication are not enabled. You must explicitly enable them in both the remote and local database for each table to be used in the replication or synchronization.

Please note that enabling the mechanism does not trigger the replication itself; in order for the data to actually be replicated in a local or synchronized database, you must use the REPLICATE or SYNCHRONIZE commands. 

To enable the internal replication mechanism, for each table (on the remote and local database) you must use the Enable Replication table property that is found in the table Inspector:

Note: In order for the replication mechanism to be able to function, you must specify a primary key for the tables implicated in the remote and local databases. You can create this key via the structure editor or using SQL commands. If no primary key has been specified, the option is grayed out.

When this option is checked, 4D generates the information necessary for replicating the records of the table (based more particularly on the primary key of the table). This information is stored in the virtual __ROW_STAMP and __ROW_ACTION fields.

Note: It is possible to enable and disable the generation of replication information via the SQL CREATE TABLE and ALTER TABLE commands, using the ENABLE REPLICATE and DISABLE REPLICATE keywords. For more information, please refer to the description of these commands.

WARNING: Checking this option causes information needed for replication mechanisms to be published. For security reasons, you must protect access to this information -- just as you protect access to your data when it is published. As a result, when you implement a replication system using this option, you must make sure that:
•    if the SQL server is launched, access is protected using 4D passwords and/or SQL schemas (see Configuration of 4D SQL Server),
•    if the HTTP server is launched, access is protected using 4D passwords and/or SQL schemas (see Configuration of 4D SQL Server) and/or the On Web Authentication Database Method and/or defining a virtual structure using the SET TABLE TITLES and SET FIELD TITLES commands. For more information, refer to the "URL 4DSYNC/" paragraph in the MissingRef section.

Once the replication mechanism is enabled in the each table of each database, you can use it from the local database via the SQL REPLICATE command. For more information, please refer to the description of this command.