How can I receive database table updates periodically?

[literal]

I am developing a multiplayer chess application with Unigui. However, in the application I will make, I need to receive the changes on the database table periodically. But should I get it for this by sending a SQL query to the database every second using a UniTimer. Is this a healthy method or couldy you suggest a different way? Thanks.

[Sherzod]

2 minutes ago, literal said:

by sending a SQL query to the database every second using a UniTimer.

This is a bad method.

By the way. Can you please specify which edition and build of UniGUI are you using?

[literal]

I am using v1.90 Professional Trial Edition 

[AntonioCuomo]

I have an application to manage assistance. Customers write on a table and on the server side I can manage tickets.
I used a unitimer and every 5 seconds I update the server page.
It is certainly not elegant also because the browser deletes the page and rewrites it with consequent flicker.

[Jean-Marc Kiener]

Some databases can inform you when per example data in a table changes. You also need dB components who can handle that. Devart sdac components web page should be a good starter.

[literal]

How can I get the changes in the database instantly every second?

[literal]

Just now, Jean-Marc Kiener said:

Bazı veritabanları, bir tablodaki örnek veriler değiştiğinde sizi bilgilendirebilir. Ayrıca bunu başarabilecek dB bileşenlerine de ihtiyacınız vardır. Devart sdac bileşenleri web sayfası iyi bir başlangıç olmalıdır.

I am using Devart Unidac and database is MySQL

[Mehmet Emin]

I think its acceptable to query the database every second or millisecond as long as you obey the rules:

You have the correct index on your table and you utilize this index in your query.
Your sql only ask changes from previous query.
Don't re-create and destroy at every call. Instead make sure you create it in the beginning and call it with changed parameters.
Run this query on a thread.
So you will run only one query for second/millisecond for the all sessions. Do not run it per UniSession on the UniMainModule.

Let's say 100 people playing online. you call the query and find that there are 10 moves performed since your previous call.
On the table you query you store the sessionid of the user who makes the move. 
You calculate the new state of the game per session id or game id.
At this stage you query the UniServerModule module for the UniSession of the user(s).

Your thread.execute

...
  if UniServerModule <> nil then
  begin
    if UniServerModule.SessionManager <> nil then
    begin
      if UniServerModule.SessionManager.Sessions.QuerySession(thesessionidfromdb) then
      begin
        LSession := UniServerModule.GetSession(thesessionidfromdb);
        UsersMainModule := TUniMainModule(LSession.UniApplication.UniMainModule);
        
        UsersMainModule.YourGameStateUpdated := True;
        
      end;
    end;
..
So this way you query the database every second but not per session, only once per server.

From here you have two options:
You can have a tunitimer on the form to check UsersMainModule.YourGameStateUpdated
Or better you have some websocket to update the user only if there is update.

**Note that you may need some locking mecanism (lock.enter .leave) during access to YourGameStateUpdated

[MOGSY]

Hi Mehmet

How can one achieve 'Your sql only ask changes from previous query' ?

regards

[Mehmet Emin]

Just keep the last id of the fetched records in a field on your thread and have the sql query like this:

select xyz from player_move where id>:id so that you get only the new records.

if you are interested in changed records since your previous query, have a timestamp column on the table to be updated at each update (also create an index on the timestamp column) so:

select xyz from some_update_table where timestamp>:timestamp so that you get only the updated records.

[Mehmet Emin]

in order not miss those multiple records with same timestamp as result of some concurrent updates. I prefer timestamp>=: instead of >. but anyway I hope this simple trick solves your need.

[MOGSY]

Thanks you

[Abaksoft]

@Literal,

In Chess Play, generally the most changes are only insert mode  on a table, and what sugest you Mehmet is enought to achieve what you are looking for.

But in global situations, where chages are : update, delete, insert

Major DBaward Company like Devart have their DBAlert component. if you have a VCL edition, don't  waste your time, this will not work in an asynchronous environment like unigui (see many topics here from Farshad). And if you do'nt want to buy the WEB edition (sugested by Jean-Marc)

here is an other way, wich solve my life :

A.  DB SIDE :

1. On your RDBM, Create one sequence (Generator ID) name it for example IDChange (and keep it alone, no assigned to a table).

2. Create Tree Trigers on your table :

• Triger for inset       (New IDChange = Old IDChange +1)

• Triger for  update  (New IDChange = Old IDChange +1) 

• Triger for Delete    (New IDChange = Old IDChange +1)

 

B.  Unigui Side :

• On serverModule, put an UnithreadTimer.   (Edited :  Or UniTimer on your Form)

• Every 5 secondes will run a query geting IDchange value.

if this value change, then refresh your UnidbGrid.

NB : Why using sequence ?Because retreiving its value is very fast (no need index on a table).

Regards