Handling SQL requests in SQLbusRT

While implementing the first test version of SQLbusRT, I faced a problem on how to communicate SQL requests and query results using ORTE as a medium.

Usually when doing an SQL request, a connection is set up between a client and an SQL server. The client sends its SQL request over this connection, and as an answer it receives the query results over the same channel. Unfortunately, in the bus architecture I have in mind things aren't that simple...

Remind the fact that SQLbusRT is built on top of a publish/subscribe model. Ideally speaking clients should be subscribers, and the database server should be a publisher. However, this introduces a problem for both instances:

• How does the publisher know what to publish?
• What should the subscriber subscribe to?

I already thought of a solution before I started implementing. This solution was even visible in my diagrams (see my previous posts) . The idea was to make a client act as a publisher to publish its request.

This idea however was not complete. While struggling with the question on how to actually implement this using publish/subscribe, I came up with the following:

• The request handler has a subscriptions for SQL requests
• The client publishes an SQL request
• The client creates a subscription for information from the request handler
  
• The request handler sends information on which data source to subscribe to
• The request handler sets up a publication for the new request
  
• The client subscribes to the correct data source

The following activity diagram shows how this communication is set up. It shows what data is present in the different messages. (Actual data, 'topic' and 'request', as required in RTPS.)
It also shows at what time subscriptions are being created in handlers and clients.

(Click to enlarge)

Note: The SQLrequest as you see it in the diagram is an object, containing the clientID, requestID and the actual SQL query. The response is also an object, containing the publicationID and requestID.

Working this way has a great benefit when multiple clients are making the same periodic or triggered request. The data only has to be fetched from the database once and will be distributed automatically, by letting the requesting clients subscribe to the same publication.