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:
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.
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). |
| 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. |
| master_replica_iqueue |
- Table where the operations coming from the different replica client devices are inserted. |
| 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… |
| 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. |
|---|
| 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. |
| 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. |
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 |
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. |
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 |
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.