Using SymmetricDS for Multi Variant Database Replication

Last time when i used MySQL 5.6 Database Replication from Master to Slave kind of things was pretty awesome, straight-forward, reliable and work as it is. I was using it for my first priority on backup database, and using mysqldump from the slave db as second backup priority, even though still doesn’t solve the issue of high available server application because it required a down time to switch between master to slave. I can leave it this issue for just for now, but I’m facing another issue when the application requires customization on the replication type of each tables.

For example we have to two databases need to be replicate, database A and B, database A has several tables to do the one way replication to database B, database  B also has several tables need to the the replication one way to database A, and both databases has the remaining tables that need to be replicate bi-directional way. Database A as central server that sit somewhere in the cloud and it has public ip and can be access from every where; database B sit in local shop some where, it has limited and unreliable internet connection, and it doesn’t have any public ip. Database A as a central database has a big capacity CPU and Memory, it handles most of the transaction data, but once there is an record update relate to table that belong to database B, it will push only for the particular record. In this case the replication require functionality to do the filtering record and also communication some how with http or tcp pulling type because the database can be inaccessible from outside.

One of the solution out there is by using the SymmetricDS (https://www.symmetricds.org) replication framework, is an open source software for database synchronization with support for multi master replication, filtered synchronization, and transformation. It runs on java with minimal version 7, support for multi variant databases like MySQL, MongoDB, Microsoft SQL Server, Oracle, SQLite, H2, etc. SymmetricDS is using JDBC to communicate with external database.

The symmetricDS treat our application as a node, each node has their own presentation of their database, each node has unique id called External Id and it group by how it will replicate the data via http pull or http push. And this software will create their system tables which defined by put prefix sym_ inside our database, is make our database became messy, one thing that i don’t like about SymmetricDS.

The way of SymmetricDS works is by allowing the user to select which user’s table need to be replicate, and define how the table replication works. The SymmetricDS will scan the selected tables and trigger the replication when there is a data changed inside, the replication data it self has a previous and new value of the particular record and store as CSV Format. Until now, I don’t fully understand the detail about how the SymmetricDS validate and scan each of the table, but I have found it put all the change history , create a hash value and store into their system table.

After SymmetricDS capture the changed record, it will define the destination node based on the route table that we (user) defined at the beginning, in this case from database A to B, so we can called route a_2_b with source node A and target node B. SymmetricDS extract the data into CSV format and transform the record data when is need, and create as a batch data file before it transfer into the destination node by http transport. SymmetricDS is using Restful framework to do the communication with other node, so we don’t worry about the security any more for the data transportation by putting the ssl on the top of the communication, and after the batch message  is received by destination node, it will load into their database.

The good thing about SymmetricDS because it run with Java, so it can run for any OS like Linux or Windows, it also has a small size of application sit in the memory. One of the example implementation by using this kind of technology is Point Of Sale / Cashier Machine that require to synchronize the data into the central database server.

 

 

Design for Lacak Platform

Lacak is a tracker platform to serve many variant GPS device vendors with many protocols supported like Gt06, Teltonika, G15c, and etc. Currently the platform is using the monolithic type of application in a single box server. There is a need to move to cluster-wide design of application because the GPS devices are growing from time to time. The cluster-wide idea is to group the GPS devices and each of the group will use the same tracker server, so it must be many tracker servers provided and it the same time all the transaction will go to the same location. The transaction itself store the location of GPS device within the period of time, it has Device Id, Device Time, Longitude, Longitude, Latitude, Speed, Course, etc.

The issue with cluster-wide design is we need to have centralized, cluster-wide, persistent, messaging, and streaming framework. So we came with Apache Kafka which is a Distributed Streaming Framework as a solution. We put the Kafka as a central application that run to deliver each of the  GPS transaction.

We are using Splunk for data analyzer and we build “Lacak-Pusher” to grep all the transaction from Kafka and deliver to the external third party applications include Splunk Server.

Please see the design below :

 

Concurrent Requests Inside HTTP API Broker

When we design the API for our application, we need to make it clean and easy to access. One of the option that we can provide is to make it centralize, by means it only have one point target as API Server. But this idea seems not easy to enough when we face with many internal applications that sit in many box servers.

For example like diagram above, client provided access to list all the user’s contact profiles via API Broker. The API Broker itself allow to access the users application to get detail information about the specific user details, and allow to access the contacts application that has two API: to get list of user’s contacts and to get user contact profile details.

What happening  is when Client create new request “Get List User Contact Profiles” to API Broker, the broker will create new request “Get User Profile” to Users Application and when it received the use profile details successfully, it will continue to create new request “Get List User Contacts” to Contacts Application. The API Broker will receive the list of contact reference identifiers based on the user’s identifier that already pass to the Contacts Application. And the list of contact reference identifiers will required to read all the contact details.

The problem that we are going to face is there will be a latency issue, because based on the list of customer reference identifiers we need to do the iteration and each of the iteration need to create new request “Get Contact Profile”.

We can get rid the sequential iteration by put a concurrent with queue and workers module inside, it means that there will be a several worker that run concurrently to process message that pass thru the queue. And there will be mechanism to split message became several parts and recompose the parts back became an original message with additional information.

 

 

Traccar Tracker Server As Proxy

Traccar Server is the best Tracker Server is open source and has solid performance to track every gps devices with many variant protocol supported. It works as location server, accept and track every location transaction from gps devices, it show the real time movement of the devices, with map layer that we can customized, and it has user management roles with customized device identifier.

Above is the sample of tracker server that we implemented for several Gps devices like FM1120, G15c, and Gt06. Each of the vendor has their own protocols , and Traccar support most of them, but for special device like G15c, we can build our own protocol on it and plug into the Traccar Server.

The question that we have right now is how to make this tracker server flexible enough as a proxy server, so that location request from gps devices can be forwarded to another tracker server (other than Traccar). Yes, it’s possible.

Each of the protocol inside Traccar is interpreted with a base socket decode/encode stacked modules came from JBoss Netty, and we can put new module called forwarder/proxy before it jump into decode/encode stacked module.

Inside the forward module will only read the packets data without any corruption, because the data will read by encode/decode stacked module in the end. The forward module will act as forwarder, to bring the packet data as it is to the other third party tracker server.

In this example we going to add proxy for Gt06 Protocol for all Gt06 Gps Devices, by adding new module called Gt06ProtocolForwarder, so that every packet inbound data will go thru will forward first into third party if is enabled.

To validate which protocol can be forward enable or not, we can use existing traccar configuration file like below

The full source code you can find is in here https://github.com/biskandar/lacak-tracker-server

 

Full time or freelance?

Full time or freelance? Permanent employee or contractor? Cubicle or home office?

I have tried all these employment options. For me, there’s a clear winner. After spending a couple decades at in-house software development in Singapore, I dared to convert my small time moonlighting into full time contract work as a freelance web application developer. When I went freelance for good. This meant I had an extra two months of paid vacation. I used the time to round up clients, and I have never wanted to go back. No more commute, no more TPS reports, and no more pro forma meetings. I now had complete control over my career and my success.

I am often asked if I miss the cubicle world. Absolutely not! Perhaps the greatest enjoyment of contract work is the appreciation I receive from my clients. In the corporate world, the overlords take for granted that you will do their bidding, and they rarely ask for your opinion. But to my contract clients, I am the expert engineer. My advice and experience are valued. After I fulfill the clients’ business needs on time, under budget, and with quality panache, they express gratitude!

Of course, nothing is perfect, and freelancing has a large downside (and I’m not talking about the lack of paid benefits!). A contractor must keep the work “pipeline” full. That requires a significant amount of time and effort. Establishing a consistent pay rate that matches your market value is difficult, because not all clients can afford a senior engineer. Sure, there are lots of job boards that act as dating services between contractors and clients, but they have about the same success rate as real dating services.

The job boards simply haven’t figured out the correct business model yet. The better way to connect clients with contractors is to have a pre-vetted pool of contractors and retain them with repeated project success. Vetting a new contractor must be technically thorough, and done by other engineers. It can’t be the slipshod qualifying that is done by the typical, technology-illiterate headhunter. That’s where Toptal comes in. Toptal is a broker for clients and contractors, like the job boards, but Toptal maintains a community of elite engineers and developers that have qualified themselves with multiple levels of skill tests. This formula is a win-win for clients and contractors. Clients have a higher probability of project success, and contractors have a steady supply of work at consistent rates.

To answer the question I posed at the beginning of this article: as a Toptal member, the clear winner in the choice of permanent employee or contractor is me!

Messaging Basic Module

Since i read messaging ebook like “Enterprise Integration Pattern”, it give new perspective about how to build good messaging platform. In the mean time i worked as Senior Java Developer for advertising messaging platform, it force me to build robust and yet flexible application.

There are two level applications to do with messaging platform, one is enterprise level like Java Messaging Service (JMS) with Enterprise Messaging Queue and the other is low level like DIY application.

Both has pro and cons, and Im doing at the DIY one, it require simple J2SE Runtime, low level and common libraries. It run with small heap memory, and it fast. It design to handle smaller amount of message traffic.

The more deep into the messaging platforn, i found several pattern than we can build as library module and it can use by other module.

Now I introduce to you the “Messaging Basic Module”, this a generic module, has light depencies, with some list following features below :
– It formed as jar library
– It shall has persistent feature
– It shall provide internal api
– It shall has load restrictions

This module can be use at enterprise level.

MyAdv Application

Since I have been developed the messaging for advertising from 2008, I need to build a new fresh advertising application, yet still related to messaging platform but more into specific concern.

I got the idea to make the application to reach their customers and manage the data easly for small company or personal used. The reached can be from short message with interaction or enhanced and bring the stuff to the next level, something more richer and details.

The main module like customer management, interaction message, shortener link, and landing page are remaining there, but the scope just to make it easy to access and stupid proof for anyone can use it.

Its like the sketch that i draw, im just using with my mobile device, i can manage the customer table, short text, shareable short link, and landing page. All these items i can share to all my customers.

Http Driver

A drive module for messaging gateway to support delivery data that came from http protocol.

It has driver terminology that came from drive acronym means that the module it self must comply with the legacy protocol provided by third party application, in this case is as an http web server.

It run only for http protocol as a base before it will represent as http get with query string data or http post with xml or json format, it depends how the legacy format that the web server provided.

As an module that built from java languange, it has internal API functions to provide service from other modules, the API is represent as Java Interface, and will came with the following 3 functions : sendMessage( Message message ) ; recvMessage( Message message ) ; recvStatus( Message message ) ; The sendMessage function is used for external module to send message , the recvMessage function is used to forward message from external module , and the recvStatus function is used to forward status of send message.

Please find the basic design of the driver module below

Inside the module there well be at least 3 workers : SendWorker, RespWorker, and RecvWorker. SendWorker is a submodule to handle all the send message that came from external module, accept it by store into the send queue or buffer, and send it all via http protocol with throughput mechanism. All the sent messages will forward to RespWorker, and this worker will accept it, store all the records into the transaction table. RecvWorker is a submodule to handle all the status outbound and inbound message, store into the queue or buffer, and forward to the external module.

All modules, including the http driver module, and all the submodule must have a legacy message data that can be understand thru all the functions, the legacy data message will found outside the module, because it doesn’t belong to specific module.

Please find the basic design of the SendWorker and RecvWorker

Simple Messaging Gateway

Before jump to the bigger and complex messaging platform, it would be nice if we have build a simple messaging gateway. The purpose of this application is to responsible the delivery message that came from http sender to http listener. Ok, now start with design, the gateway application required at least a driver/adapter http module to translate the message, a router module to do the messaging routing, and the message itself. The message data structure contains several main fields like message unique id, original node, destination node, original address, destination address, and message content. The node field can refer to the name of http module that we are going to build, this module can be instance into several objects, but the router object can only have one, and it will handle all the messages from/to object modules. Inside the router module shall have capability to restrict the message throughput, that’s why it needs a duo queue as inbound and outbound queue, several thread workers to process message from inbound queue to outbound queue. Inside the http module shall have capability to convert message into http message and the other way around, it requires the http client library to support and http embedded web server. For the support mechanism, we should put the log library as well, and write every messaging activities into the log, in this application we don’t provide the User Interface module, and all the initial parameters will held in the configuration file that stored into XML format. This application also run under Java Runtime, we can use version 7 the latest and stable one.

	<configuration>
	<simple-gateway>
	<drivers>
	<driver id="driverHttpSender">
	<properties>
	<property name="remoteUri" value="http://remoteuri/path"/&gt;
	<property name="scriptFile" value="./js/driverHttpSender.js"/>
	</properties>
	</driver>
	<driver id="driverHttpListener">
	<properties>
	<property name="host" value="serverip"/>
	<property name="port" value="portval"/>
	<property name="scriptFile" value="./js/driverHttpListener.js"/>
	</properties>
	</driver>
	</drivers>
	<nodes>
	<node id="nodeHttpSimple">
	<driver inbound="driverHttpListener" outbound="driverHttpSender"/>
	<router outbound="routerHttpSimple"/>
	</node>
	</nodes>
	<routers>
	<router id="routerHttpSimple">
	<queue inboundSize="100" outboundSize="100" />
	<worker loadPerSeconds="10" threadSize="5" />
	<processor id="processorHttpSimple"/>
	</router>
	</routers>
	<processors>
	<processor id="processorHttpSimple">
	<properties>
	<property name="map.nodeHttp" value="nodeHttp" />
	</properties>
	</processor>
	</processors>
	</simple-gateway>
	</configuration>

view raw 2015072701.xml hosted with ❤ by GitHub

Like the example xml file configuration above, explained that the platform has 4 modules, first the driver module, is that handle the low level protocol like http, smtp, etc. Second is node module, this module is just as representative of driver inbound and outbound , and it also forward all the inbound message to router module. The third is router module, this module will responsible to route each of the message based on the script inside to which destination node. The forth is processor module, this module is as route scripting module for router module. In this example, the processorHttpSimple will route the message based on the map properties that defined in the existing configuration xml file.

Introduction for the messaging application

I have done to build many applications related to messaging stuffs for the last 10 years, mostly an application that use for short message service ( SMS ) that delivered to the GSM Mobile phone. I think this is a time write all the experiments and share the snippet code that usually i have did mostly, and it became a pattern. Before jump into the code and practice stuffs, i would like to go the introduction concept about how to build application related to the messaging stuffs. The main key is a middle ware application as a main topic, because application it self need to have connectivity to transfer from and to the SMS Provider, and the application must serve the interaction to the user or third party application. And inside the application there shall be a router, queue, and worker module, this modules is a must requirement. For this explanation of the router, queue, and worker module, I would like to share about one book that explain deeply about them: Enterprise Integration Patterns ( EAIPatterns ) , you can find it in this link http://www.enterpriseintegrationpatterns.com/eaipatterns.html. This book will give you an explanation about what patterns that usually we faced to build messaging application. The messaging application is a module that have messaging oriented inside, the message can be inbound or outbound direction, and each message has its delivery status. The message content itself can be simple text, short message service (SMS), notification message, email message, etc. Process of the message can be asynchronous or synchronous mode, and all of them require queue and cache for the performance wise. And the delivery protocol that can be use such as simple http get post method, web service with SOAP, restful format, etc. Inside messaging application should has database to keep persistent the data traffic and/or data profile, queue and thread worker to perform delivery throughput, message converter, and support tools like log mechanism for debugging purpose. It also should have ui module to do the provision profile data and monitoring traffic including the report traffic. Mostly, there are 3 main modules inside the application: 1. Driver Module, this module will responsible to communicate with internal elements, each element has its own protocol but usually most of them were using standard / legacy protocol. 2. Router Module, this module is the core engine to pass the message that came from driver to adapter module or the opposite direction, this module also can handle the throughput of the running message, and intelligent to route the destination of the module. 3. Adapter Module, the module that communicate with external party app, and usually the third party app shall follow the legacy protocol that the adapter have.

Like in graph above, there are several drivers based on the legacy protocol that internal network elements provided, the message came from driver will pass to the router, and the router will decide to forward the messag into which adapter, and depend on the rules inside the router, each of the adapter will serve based on the protocol that required.