This translation is older than the original page and might be outdated. See what has changed.

Data Replica


Introduction


Unlike other existing replica systems, the XOne replica is able to deal with different Database Manager Systems.

A configuration of example may be the following one:

  • DB of the server MSSQL SERVER
  • SQLITE Clients, MSSQLSERVER CE, MYSQL, ORACLE…


The machinery even is able to apply selectivity so that not all the clients have to receive a full version of the main DB.

All the previous optimized to be able to convert any client ERP easily into a mobilized environment, in which the different methods of data transmission and variety of terminals are not a problem and may be easily configured.

The basic operating is based on the replication of the operations: INSERT, DELETE and UPDATE of SQL between the clients through a replica server that is in charge of maintaining the integrity of the main DB.

Aclient may be an Android, PC, Windows Mobile, :BlackBerry, iPhone, Symbian or Windows Phone, with different DBMS, they send its operations to the replica server and this one in turn will replicate these operations to the other clients.

Necessary Tables


The replica (Server) needs the following tables in Database for its correct functioning:

REPLICA SERVER REMARKS
MANDATORIES master_replica_queue
master_replica_iqueue
master_replica_slave
master_replica_cmdlog
master_replica_errorlog
master_replica_excluded
rl 		- Only with this tables we have SIMPLE replica, that is, ALL the operations are replicated.
SELECTIVE master_replica_squeue
master_replica_selected 		- With the selective replica we can configure NOT to replicate all the operations to all the clients. The CONDITIONAL=1 field will be configured in the master_replica_slave table and the master_replica_selected table must be configured.
- We can make the selectivity be grouped (intelligent) at the server, by adding a TBL field in the tabla master_replica_queue table, for grouping those operations madeover the same table in a unique consult (The DBMS uded at the server must allow subqueries).
FILES master_replica_fields
master_replica_files 		- It is necessary to configure a field in the master_replica_slave table with the path where the files are saved in the server (Just remember giving permissions to the folder).


Description of the Tables


Mandatory Tables


Table Description
master_replica_queue

- At the server there is a history of the operations properly executed and at the client there are the operations waiting to replicate toward the server.
- This table is filled by the replica server, NOTHING HAS TO BE CONFIGURED, it is just a history of the operations made.
- This table must be both in the replica server and in the clients.

master_replica_iqueue

- Table where the operations coming from the different replica client devices are inserted.
- The records of this table will be analyzed by the replica service according the selectivity specified at the master_replica_selected table and it will fill the master_replica_squeue table with the records that are addressed to each device.

master_replica_slave

- It has information about the replica clients, until which operation have they replicated, date format of the client´s DB, if it has conditional replica, if batches are used…
- It is automatically updated according the clients are replicating.
- This table is configured almost entirely with xnetsetup2, the utility of CGSOFT to sign the replica clients.

master_replica_cmdlog

- Empty, internally used by the system, nothing to configure.

master_replica_errorlog

- Log with the huge mistakes that are occurring, for a more detailed log see the log file that can be configured at the replicator.ini.

master_replica_excluded

- Information about the DB tables and its ID field (primary key) for the replica does not insert the value of that field from the server toward the client, since most of the DBMS do not accept a value insertion for an autonumeric field.

Example: The server sends an UPDATE of a product and this record does not exist at the replica client DB, for any reason: error, accidental deletion… In this case the server sends a backup of the record of that product in order the client inserts it into its DB, but such INSERT cannot have value for the ID field (primary key), since being numerical in most of DBMS would, the insertion of value in this field would cause an error..


rl

- The replica core has the relations betweem the tables of the mappings.xml files so that the replica server be able to maintain the referential integrity of the replicated operations. CGSOFT provides a tool to analyse the mappings.xml file and generates that table.


Tables for the Selective Replica


master_replica_squeue Queue of operations that have to replicate the clients that have configured selective replica, it has the ID of the master_replica_queue table that each selective replica client has to execute.
master_replica_selected Here are configured, table by table, the conditions that must be met for the operation to run on the clients of the selective replica.

Tables for Files Replica


master_replica_fiels Information about the tables and the fields where the file name to replicate is stored in.
master_replica_files Nothing is configured, it is the history or queue of the files replica.

master_replica_queue


The main fields of the table are the following ones:


FIELD TYPE MANDATORY REMARKS
ID INT (11) Yes (index) Autonumeric
ROWID VARCHAR (50) Yes (index) Unique identifier of the record in the whole DB (in Oracle reserved word, using CGSROWID instead)
OPERID VARCHAR (65) Yes (index) Unique identifier of the operation over the previous record
TIMESTAMP VARCHAR (25) Yes Date and time to which the operation was generated in the client
OPER INT (11) Yes Type of operation to replicate 1-Insert 2-Delete and 3-Update
MID INT (11) Yes (index) Identifier of the Client who makes the operation
SQL TEXT Yes Executed SQL sentence (At MYSQL 5.0 reserved word, using CGSSQL instead)
DMID INT (11) Yes (index) Operation generated by the server addressed for a specific client
CONDITIONAL INT (11) Yes To establish simple conditionality
TBL VARCHAR (50) Only for grouped selectivity It is used for grouped selectivity (NOT for MYSQL 4.0, only DBMS that admit subqueries)
DISABLED INT (11) Only for Batches It is put to 1 if we do not want to replicate a certain instruction
DMIDPR INT (11) Only for Batches It must exist, internally used


master_replica_slave


In this word there is a record for each replica client.

The main fields of the table are the following ones:

FIELD TYPE MANDATORY REMARKS
ID INT(11) Yes (index) Autonumeric
DIRIP VARCHAR(50) Yes Alphanumeric identifier of the license. E.g.: “PDA from Pedro Rodriguez”.
PROTOCOL VARCHAR(50) Yes The character of this field is merely informative, in the old clients only had access via HTTP, and in the new ones only have direct access. Therefore, this field will be able to take two values:“HTTP“ Indicates that the access is via proxy. “DIRECT” Indicates the access is direct.
LASTID INT(11) Yes (index) Last ID of the master_replica_queue that has successfully executed the client.
LASTDMID INT(11) Yes (index) Last ID of the master_replica_queue direct for a client(DMID) that has successfully executed.
SYNCHRONISM VARCHAR(20) Yes It is used to indicate the date and time in which a client has synchronized totally for the last time with the server.
MID INT(11) Yes (index) Unique identifier of the current client.
PENDING INT(11) Yes (index) It is worth 0 or NULL when the client has full power to be able to operate. A client having this field to 1 cannot replicate.
PRIORITY INT(11) Yes This field has the value of this client priority at the time to solve a conflict. The larger this number is the more priority the customer is.
ACTION VARCHAR(20) Yes This field is used to indicate the client to execute any action sent from theserver.
ULDATE VARCHAR(20) Yes This field has the date mask for those applications that access directly to the database. It is interesting to create a client with MID = -1 and put here the date mask of the direct applications, in such way that it is no necessary to configure as replica clients all the machines that directly work with the server database.
DLDATE VARCHAR(20) Yes This field has the date mask with which the replica operations must be sent to this client. E.g: dym for PDA with MSSQL SERVER CE
RPDATE VARCHAR(20) Yes This field is the opposite of the previous one, that is, it indicates in which format the dates sent for this client via replica are coming.
SERIAL VARCHAR(128) Yes This field has to contain the full number of license of this client. If it is empty, the client will be unabled and cannot replicate.
CONDITIONAL INT(11) Yes (index) This field is a bitmap that has flags of the selective status of a client. In general a null or zero value in this field indicates that the client is normal. In case the last bit be 1, the clitn will have selective replica.
LASTCID INT(11) Yes This field only is used in those databases that have at least a client with conditional or selective replica.
FILEPATH VARCHAR(128) Files replica only It will have the value of the path in which the client stores the files at the server. (The folder must have permissions)
BATCHSIZE INT(11) Operations Batch If we want to enable the operations sending in blocks toward the client. If the value of this field is NULL or zero, the server will use as client batch size the value by default(100), read from the configuration file replicator.ini, at the DefaultBatchRecords key.
The size will depend on the type of connection and device in question, being some values normally established the following ones: PDA ≅ 20, Blackberry ≅ 40-60, ADSL ≅ 100, in some cases in which the GPRS was very bad it was necessary to go down the batch size to 5 in some PDAs.
PUSH VARCHAR(35) (Optional) For PUSH only If the device doesn´t support PUSH, will be empty.
If the device supports PUSH, it will indicate the type of connector used.


rl


As it was already discussed, this table is the replica core, it contains the relations between the tables, so that, when replicating operations to clients it is done with the values .

The table main fields are the following ones:


FIELD TYPE MANDATORY REMARKS
ID INT(11) Yes (index) Autonumeric
T1 VARCHAR(50) Yes Name of the source table of the relation.
F1 VARCHAR(50) Yes ID field to relate to the destination table.
T2 VARCHAR(50) Yes Name of the destination table of the relation.
F2 VARCHAR(50) Yes ID field of the destination table.
T VARCHAR(50) Yes System field
CND VARCHAR(50) Yes System field


Common Problems


If any change occurs in any of the master_replica_xxxxx tables, at the rl table or at the replicator.ini, it is necessary to restart the replica service.