Knowledgebase
Trading Software > Help Desk > Knowledgebase

Search help:


TSL Distributed Network

Solution

The TSL distributed network provides for the maintenance of a disconnected SATELLITE COPY or COPIES of the HUB BMS data-set, and the structured merging of data between the HUB and SATELLITE(S).

 

Problem

In ideal conditions, it would be possible to maintain a Wide Area Network to allow remote client access to a centrally maintained BMS data-set, There are circumstances however where it is not possible to maintain a reliable network connection between the BMS server and its clients. An unreliable network connection presents risks to business continuity by impacting an ability to perform business processes on a disconnected client.

 

Requirement

Allow multiple distinct installation of the BMS to share updates between themselves.

For instance, you might run 2 shops, where a reliable connection between the two sites cannot be guaranteed at a sufficient level to make a shared server possible, but where it is necessary to centralise book-keeping activity, to to share various other data components of the BMS data-set. Both sites will maintain their own business processes including sales order processing, receive stock and purchase invoice processing tasks.

Considerations

Database updates: From time to time TSL will issue updates to the underlying BMS data-set. It is possible that a database transaction event may be rendered unusable if it is created on a different database update version than the HUB

 

Solution

The BMS maintain a log of transactions applied to its data-set. We can leverage the transaction log to 'replicate' updates between distinct BMS data-sets. To make this possible it is necessary to maintain some data centrally to describe the state of any satellite BMS installations and the progress made in processing updates from one or more satellites to the hub and then from the hub to the various satellites.
 
We can use the built-in MySQL FEDERATION storage engine to maintain links between the hub and satellite data-sets. The MySQL FEDERATION storage engine does not require a permanent connection between the server and client; that is to say, a connection may be interrupted but when it is re-established a client will re-connect invisibly.
 
We can post satellite transactions to the hub by updating a federated client transaction table.
We can read transactions from the hub by federating the hub's transaction table.
We can maintain universally available satellite state information in a federated table containing a list of satellites.
 
The diagram below shows which tables we will share between the HUB and Satellites.

Method

 
Enable the FEDERATED storage engine in MySQL
Edit .ini / .cnf file to include 'federated'
Windows:C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
Linux: /etc/my.cnf
 
 

1) Create a SATELLITE

  • On the HUB add an entry into the SATELLITES LIST [x_db_transactions_satellite_list]
  • Process the contents of [x_db_transactions_satellite_list] to generate relevant HUB-SIDE SATELLITE TRANSACTION LOGS.
    • For instance if the new SATELLITE network is to be known as 1 - HighStreetShop, then a new table will be created called 'x_db_transactions_satellites_1', ready to receive transactions from the HighStreetShop
  • There should be one of these table for each SATELLITE
  • HUB-SIDE SATELLITE TRANSACTION LOG tables will be federated to their appropriate SATELLITE server as [x_db_transactions_satellite]
  • HUB-SIDE SATELLITE TRANSACTION LOG tables will receive SATELLITE transaction data while a connection is available.
  • If no connection is possible, updates will fail on the SATELLITE side, but resume when a connection becomes available.

 

2) Copy HUB data to the New SATELLITE server

  • Stop processing data on the HUB
  • Update the new SATELLITE row  in the x_db_transactions_satellite_list with the latest x_db_transactions.tid value
  • Copy the HUB data-set to the new SATELLITE server

 

3) Configure the SATELLITE BMS

On the SATELLITE open then BMS

  • Post the local Network_ID into the local variable set [my_settings.settings.LOCAL_NetworK_ID]
  • Remove local tables [x_db_transactions_satellites_XX]
  • Create a federated copy of the HUB [x_db_transactions_satellites_(network_id)] table called [x_db_transactions_satellite]
  • Create a federated copy of the HUB [x_db_transactions] table called [x_db_transactions_hub]
  • Replace [x_db_transactions_satellite_list] with a federated copy from the HUB called [x_db_transactions_satellite_list].
  • YOU MUST identify one machine on your SATELLITE network as a BACKUP MACHINE. This machine will manage all HUB-SATELLITE data processing

 

SATELLITE-SIDE processing

Performed by the nominated BACKUP MACHINE

  • TO THE HUB
    • A threaded process attempts to copy data from the local [x_db_transactions] table into the federated [x_db_transactions_satellite] table
      • INSERT INTO x_db_transactions_satellite ( remote_tid, network_id ) Select TID, [_Local_instance_network_id]
      • FROM  x_db_transactions WHERE PROCESSED = 0;
    • The process marks local transactions as processed when they have been transferred (processed = 1)
  • FROM THE HUB
    • Collects (locally) un-processed transactions from the HUB - [HUB Update Packet]
      • Ignore transactions that originated from the local network
      • Select * from  [x_db_transactions_hub] where NOT network_id = [_Local_instance_network_id] AND TID > (Select last_transaction_id from x_db_transactions_satellites where network_id = [_Local_instance_network_id] ) LIMIT 1000;
    • Process results above in TID Order
    • Update the federated SATELLITE LIST table with the highest [HUB Update Packet].TID that has been collected, plus the current date time
      • update x_db_transactions_satellites set `last_contact`= NOW(), last_transaction_id = [HUB Update Packet].TID 
      • If [HUB Update Packet].schema_change > 0 then  `version_db`= [HUB Update Packet].schema_change
        • where network_id = [_Local_instance_network_id] ;

 

HUB-SIDE processing

Part A

Process SATELLITE data: Processes individual satellite [Update Packets] and posts them into the local HUB x_db_transactions table so they can be processed by the individual satellites as necessary.

  • Work though events one-at-a-time in the various HUB-SIDE SATELLITE TRANSACTION LOG tables
    • Select * from [x_db_transactions_satellites_XX] where version_db = (Select version_db from [x_db_transactions_satellite_List] where network_id = 0) and processed = 0;
  • Process the event statement except where
    • the SATELLITE version_db is different from the HUB version_db
  • Add successfully processed events into the local (HUB) [x_db_transactions]. These will become available to all SATELLITES.
    • UPDATE x_db_transactions_satellites_XX Set processed = 1 where TID =  Event_TID;
    • INSERT INTO [x_db_transactions] (EVERYTHING EXCEPT TID) SELECT (EVERYTHING EXCEPT TID) FROM x_db_transactions_satellites_XX where TID = Event_TID;
  • Mark failed updates in the federated [x_db_transactions_satellites_XX] table as CAUSED_ERROR = 1
  • Update the SATELLITE List with a count of unprocessed SATELLITE transactions.
    • update x_db_transactions_satellites S
    • set `remaining_events`= ( SELECT Count(*)  FROM  x_db_transactions A where NOT A.`network_id` = S.`network_id` AND TID > S.last_transaction_id)
    • , `remaining_satellite_events` = ( SELECT Count(*)  FROM  x_db_transactions_satellites_XX A where Processed = 0)
    • where network_id > 0;

SATELLITE.version_db <> HUB.version_db

  • These events have been created on SATELLITE at a different version_db state
  • We can process these events when the HUB version_db value catches up with the SATELLITE - See Part B
  • Do nothing. Do not mark the event as [processed = 1]

Part B

Handle Database Updates provided by TSL Licensing Server

When all entries in the federated table [x_db_transactions_satellite_List].remaining_events + remaining_satellite_events  = 0

  • On the TSL Licensing Server
  • collect any outstanding Change Events based on the prevailing application version and process them against the local data-set schema.
  • Update [x_db_transactions_satellite_List].version_db to the latest Change Event ID provided by the TSL Licensing Server where network_id =0
  •  
  • Write Change Statements into [x_db_transactions] and mark the row as 
    • schema_change = [Change Event ID provided by the TSL Licensing Server]
    • script =  [script flag provided by the TSL Licensing Server]

 Hub <-> Satellite data transposition


 

Structure and objects

Overall structure = HUB and SATELLITE

HUB = central 'controlling' data-set. Passive, Receives updates from SATELLITES and maintains an overall log of data-base transactions.

SATELLITE = 'distributed' copy of the HUB data-set. Performs local updates, 'PUSHES' local updates to the HUB, and 'PULLS' updates from the HUB via FEDERATED tables

_Local_instance_network_id = Local variable containing a unique network id that identifies a given SATELLITE. Also stored on the HUB in [Satellite Table]

Log Table = [x_db_transactions] table of logged database transactions held locally in both HUB and SATELLITE data-sets

Satellite List = HUB-SIDE table [x_db_transactions_satellite_list] maintains a list of SATELLITE data-sets. Federated as [x_db_transactions_satellite_list] to all SATELLITES.

Hub Log Table = HUB-SIDE Table [x_db_transactions] federated as [x_db_transactions_hub] on all SATELLITES

Satellite Log Table = HUB-SIDE Table [x_db_transactions_satellites_XX] federated as [x_db_transactions_satellite] on all SATELLITES

Update Packet = a single log entry from a log table

 

Log table

DROP TABLE IF EXISTS `x_db_transactions`;
CREATE TABLE `x_db_transactions` (
  `TID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Row ID. Unique Primary Idx',
  `TID_remote` BIGINT(20) NOT NULL DEFAULT '0',
  `network_id` INT(3) NOT NULL DEFAULT '0',
  `version_db` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'DB version Update row id',
  `local_instance_id` INT(3) NOT NULL DEFAULT '0',
  `machine_name` VARCHAR(25) NOT NULL,
  `Trans_datetime` DECIMAL(18,6) UNSIGNED NOT NULL DEFAULT '0.000000' COMMENT 'datetime + millisecs',
  `script` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Process as a script',
  `schema_change` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'identifies this event as a database update',
  `sql` TEXT,
  `parameters` TEXT COMMENT 'serialised parameters array',
  `caused_error` TINYINT(1) DEFAULT '0',
  `processed` TINYINT(1) DEFAULT '0',
  PRIMARY KEY (`TID`),
  KEY `processed` (`processed`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
 
Satellite List Table
 
DROP TABLE IF EXISTS `x_db_transactions_satellite_list`;
CREATE TABLE `x_db_transactions_satellite_list` (
  `network_id` INT(3) UNSIGNED NOT NULL,
  `name` VARCHAR(50) NOT NULL DEFAULT 'Network Name',
  `version_db` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'Updated by the LOCAL application on database change event',
  `last_contact` VARCHAR(20) NOT NULL DEFAULT '' COMMENT 'Updated by the SATELLITE when performing PART A',
  `last_transaction_id` BIGINT(20) NOT NULL DEFAULT '0' COMMENT 'Updated by the SATELLITE when performing PART A',
  `remaining_hub_events`  INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Updated By HUB. Events in hub log not yet processed by a given satellite ',
  `remaining_satellite_events`  INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Updated By HUB. Unprocessed events in federated satellite log ',
  PRIMARY KEY (`network_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

 

 
Create a federated table
 
DROP TABLE IF EXISTS T1;
CREATE TABLE `T1` (
  `TID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `C1` VARCHAR(50) NOT NULL DEFAULT '-',
  `C2` VARCHAR(100) NOT NULL DEFAULT '-',
  PRIMARY KEY (`TID`)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://[USERNAME]:[PASSWORD]@[HOST]:[PORT]/[DATABASE NAME]/[TABLE NAME]';
 
 
 
Was this article helpful? yes / no
Related articles WordPress: TSL Objects: t_images
WordPress: TSL Objects: t_images_p
WordPress: TSL Objects: t_attributes
product to attribute table
Unique Record Numbering
Article details
Article ID: 92
Category: Trading Software BMS
Rating (Votes): Article rated 3.8/5.0 (32)

 
« Go back

 
Powered by Help Desk Software HESK, in partnership with SysAid Technologies