2

All s/w is Windows based, coded in Delphi.

Some guys submit some data, which I send by TCP to a database server running MySql.

Some other guys add a pass/fail to their data and update the database.

And a third group are just looking at reports.

Now, the first group can see a history of what they submitted. When the second group adds pass/fail, I would like to update their history. My options seem to be

  1. blindly refresh the history regularly (in Delphi, I display on a DB grid so I would close then open the query), but this seems inefficient.
  2. ask the database server regularly if anything changed in the last X minutes.
  3. never poll the database server, instead letting it inform the user's app when something changes.

1 seems inefficient. 2 seems better. 3 reduces TCP traffic, but that isn't much. Anyway, just a few bytes for each 2. However, it has the disadvantage that both sides are now both TCP client and server.

Similarly, if a member of the third group is viewing a report and a member of either of the first two groups updates data, I wish to reflect this in the report. What it the best way to do this?

I guess there are two things to consider. Most importantly, reduce network traffic and, less important, make my code simpler.

I am sure this is a very common pattern, but I am new to this kind of thing, so would welcome advice. Thanks in advance.

[Update] Close voters, I have googled & can't find an answer. I am hoping for the beneft of your experience. Can you help me reword this to be acceptable? or maybe give a UTL which will help me? Thanks

Mawg says reinstate Monica
  • 38,334
  • 103
  • 306
  • 551
  • 1
    For 2 and 3, you need triggers to track data changes. For 2 you will collect them in some table (perphaps organized per client session basis) and clients will periodically poll this table. Standard client-server connectivity is sufficient for this. For 3 you will need to emit notifications. AFAIK, MySQL does not support it, hence you have to implemented it from the scratch, like [here](http://stackoverflow.com/questions/3983992/implement-mysql-event-notification-back-to-a-delphi-application), But you will need extra connectivity between client-server. – pf1957 Apr 06 '13 at 06:16

2 Answers2

4

Short answer: use notifications (option 3).

Long answer: this is a use case for some middle layer which propagates changes using a message-oriented middleware. This decouples the messaging logic from database metadata (triggers / stored procedures), can use peer-to-peer and publish/subscribe communication patterns, and more.

I have blogged a two-part article about this at

The article is about Firebird but the suggested solutions can be applied to any application / database.

In your scenarios, clients can also use the middleware message broker send messages to the system even if the database or the Delphi part is down. The messages will be queued in the broker until the other parts of the system are back online. This is an advantage if there are many clients and update installations or maintenance windows are required.

Similarly, if a member of the third group is viewing a report and a member of either of the first two groups updates data, I wish to reflect this in the report. What it the best way to do this?

If this is a real requirement (reports are usually a immutable 'snapshot' of data, but maybe you mean a view which needs to be updated while beeing watched, similar to a stock ticker) but it is easy to implement - a client just needs to 'subscribe' to an information channel which announces relevant data changes. This can be solved very flexible and resource-saving with existing message broker features like message selectors and destination wildcards. (Note that I am the author of some Delphi and Free Pascal client libraries for open source message brokers.)

Related questions:

Community
  • 1
  • 1
mjn
  • 36,362
  • 28
  • 176
  • 378
1

Each of your proposed solutions are all viable in certain situations.

I've been writing software for a long time and comments below relate to personal experience which dates way back to 1981. I have no doubt others will have alternative opinions which will also answer your questions.

Please allow me to justify the positives and negatives of each approach, and the parameters around each comment.

"blindly refresh the history regularly (in Delphi, I display on a DB grid so I would close then open the query), but this seems inefficient."

  • Yes, this is inefficient
  • Is often the quickest and simplest thing to do.
  • Seems like the best short-term temporary solution which gives maximum value for minimal effort.
    • Good for "exploratory coding" helping derive a better software design.
    • Should be a good basis to refine / explore alternatives.
    • It's very important for programmers to strive to document and/or share with team members who could be affected by your changes their team when a tech debt-inducing fix has been checked-in.
    • If not intended as production quality code, this is acceptable.
    • If usability is poor, then consider more efficient solutions, like what you've described below.

"ask the database server regularly if anything changed in the last X minutes."

  • You are talking about a "pull" or "polling" model. Consider the following API options for this model:
    • What's changed since the last time I called you? (client to provide time to avoid service having to store and retrieve seesion state)
    • If nothing has changed, server can provide a time when the client should poll again. A system under excessive load is then able to back-off clients, i.e if a server application has an awareness of such conditions, then it is therefore better able to control the polling rate of compliant clients, by instructing them to wait for a longer period before retrying.
    • After considering that, ask "Is the API as simple as it can possibly be?"

"never poll the database server, instead letting it inform the user's app when something changes."

  • This is the "push" model you're talking about- publishing changes, ready for subscribers to act upon.
    • Consider what impact this has on clients waiting for a push - timeout scenarios, number of clients, etc, System resource consumption, etc.
    • Consider that the "pusher" has to become aware of all consuming applications. If using industry standard messaging queueing systems (RabbitMQ, MS MQ, MQ Series, etc, all naturally supporting Publish/Subscribe JMS topics or equivalent then this problem is abstracted away, but also added some complexity to your application)
    • consider the scenarios where clients suddenly become unavailable, hypothesize failure modes and test the robustness of you system so you have confidence that it is able to recover properly from failure and consistently remain stable.

So, what do you think the right approach is now?

KRK Owner
  • 762
  • 7
  • 16