Cheap Cloud Integration

So you have a situation at hand. You have succumbed to peer pressure and subscribed to a few Cloud SaaS solutions. You have dismantled the legacy system and essentially burnt the bridge.

After the initial rounds of congratulations from the top management for saving on capex and staff cost, now it is time to face the common users. He is used to the mammoth integrated, on-premise ERP system, where everything was linked to everything else. The finance man could stop the release of a production work order for some tax non-compliance at a factory thousands of miles away. Employees defined in the HR modules would seamlessly show up as shop-floor workers in the MES module.

Cloud paradoxically transported back them to 80s where island systems ruled. But then in those days one could run around with floppy disks to move dBase files from one PC to the other and get jobs done. Cloud software does not give such convenience. You cannot afford a modern cloud integration tool as there was no budget allocated for the same at the outset. Anyway, if you approach your management with the demand of half a million, you will be subjected to some gruesome medieval punishment, like, say, drawn and quartered.

You have a few mid-size servers - Dell T30 - left as part of legacy hardware. You also have a small army of database developers who have not been pink slipped yet. Your situation is probably worse than that of the retreating German Army after Operation Barbarossa.

But then you have access to open source, which Hitler did not have.

Here is what you will need:

1. A server with PostgreSQL installed

2. Python in the same server

3. A Python developer with intermediate skill.

The existing database programmers will, of course, learn PostgreSQL if they want to save their jobs. So that is a trivial challenge.

Now let me explain what I propose to do.

First, look at this simplistic model between data flow between a CRM and an SCM system. Both are Cloud based. Whatever tool you use the fundamental flow will be of the type described below:

1. New customer flow (virtual)

2. Request: Any new customers

4. Response: New customer list

3. Request: Insert new customers

5. Response: New customers inserted

One important point to be noted here is that the Cloud raises no request. A request is always raised by the integrator. It is always a "pull" scenario.

So it does not matter who is your Web Service client. It may be a rundown UNIX box. It may be a swanky Android phone. Cloud does not care.

In our case the intermediate client that I propose is this:

I have simplified the use cases a bit. The assumption is that all the Web Services are REST. There is no SOAP.

So the Python module also becomes simple. It just has to bother about HTTP GET, PUT or POST and JSON in HTTP body. The JSON that it receives as a response is to be dumped straightaway into the database.

The tricky part is how to make a JSON request. PostgreSQL is a hybrid database. It allows JSON and XML to be stored as native data types as number and date and text. So SQL scripts could handle JSON creation and transformation easily. Reading a JSON and creating one from scratch is pretty straightforward in PostgreSQL. Its SQL extensions do the job easily.

So, in a nutshell, SQL manipulates data, and Python carries it.