Update of LookUp Tables

[janasoft]

I'm trying to make a simple framework to manage database applications in a multi-user environment. It is a learning exercise so I am trying to implement it without using the mechanisms offered by Lazarus: TTable, TQuery, TMemDataset, and others.
I have managed to complete a sufficiently functional prototype but I am facing a problem that I do not know how to address.
When I start the application, I load data from some tables that I use as LookUp tables into a series of TFPGObjectLists. Although it is not very common, the data of those tables can be modified and, what I want to know is what would be the best strategy to determine that some data has been modified in any of these tables to refresh the corresponding TFPGObjectList.
In a single-user environment it would be relatively simple, but I can't think of how to do it in multi-user mode
It is possible that as I have proposed it is not the most appropriate way to do it, so I am open to modifying my approach if someone offers me a better idea
Best regards

[cdbc]

Hi
One solution would be to implement an Observed/Subject on the LookupTable and then an Observer on each user, to be notified when the table changes, so they can update their internal objectlists via a new read from table...
Just my 2 cents worth.
Regards Benny

[janasoft]

Hi Benny. Thank you for your answer
The problem is that the table changed is stored in a SGDB so I think I can´t use that concept directly against de table.
I had thought of a similar solution so that when a user modified a table it would emit some kind of message to the rest, but I have no idea how to implement a kind of observer pattern in a multi-user environment

[cdbc]

Hi
Hmmm... Maybe a shared library?!?
IPC of some form or another, fpc/laz has some built-in...
A 'tainted' file somewhere they all can see...
Regards Benny

[tooldeveloper]

Are you familiar with how to implement something like an Update Sequence Number (USN) or similar?

I am not sure what an SGDB's capabilities are so the only example I can give is for a standard transactional database (eg SQLite, SQL Server, Postgres, etc.) with the ability to do atomic commits.

Assuming your database engine supports triggers you can use a standard INSERT/UPDATE/DELETE trigger. Inside the trigger(s) do an atomic commit to increment USN every time there is an important change. Clients only need to compare the last USN the client read from the database to the current USN in the database. If the two USNs differ then the client will know it needs to refresh.

[janasoft]

Hi tooldeveloper.

I am not sure what an SGDB's capabilities are ..

Sorry a SGDB is a DBMS in spanish. I forgot to do the translation 

Are you familiar with how to implement something like an Update Sequence Number (USN) or similar?

Yes, I know these mechanisms.
The problem is that, when using them, part of the management of the application has to be delegated to the Database and I would not want to be obliged to do so, because that would imply having to write specific code for each DBMS that could be used. 
On the other hand, if I use this system I am obliged to make a query to verify the USN every time it is necessary to use a lookup table. And I was thinking of something that was in the opposite direction, a kind of Observer pattern as Benny proposed.
I have no idea how other types of frameworks solve this situation and I have found practically no information about it.
In any case, your proposal is a valid solution that I could consider if I can't find a better solution.
Regards

[Zvoni]

hmm.........What about introducing a timestamp-field?
You start your program, you grab the timestamp of the last update, you fill your lookup-tables.
In the meantime, someone else updates your lookup-table, resulting in a "newer" timestamp.

Maybe a second thread (with its own connection to the DB), checking that timestamp in regular intervals, comparing it to the timestamp you had when you filled your lookup-Table?
Timestamp is newer --> "Re-Update your Lookup-table"

I'm just thinking aloud.....

EDIT: And it would depend on which DBMS is in use

[cdbc]

Hi
Just flew by something called MQTT and Mosquitto... Maybe that's something for you?!? You could have a look...
Regards Benny

[janasoft]

hmm.........What about introducing a timestamp-field?

Zvoni, this option is in the same line as the one proposed by tooldeveloper. Although it seems a bit simpler to implement, it requires including code in the database which is something I didn't want to do. In addition, constant queries are still needed.
In any case, I thank you for your collaboration and I take note of your idea..

Just flew by something called MQTT and Mosquitto...

Benny, I hadn't thought about this possibility. It sounds good because, even though it involves introducing a new technology, this mechanism could resemble more of an observer pattern which notifies when something changes. I will try to do some tests to see if it is possible to integrate it into a Lazarus application.

Many, many thanks to all

Regards Ricardo

[Sieben]

Did you check if your DBMS provides an update callback? SQLite3 for example does and the header file provided by Lazarus (sqlite3.inc) already has the necessary entries. Implementing it eg with a descendant of T*Connection is another matter though.

[janasoft]

Did you check if your DBMS provides an update callback

Thank you very much for your information Sieben.
I didn't know that SQLite implemented this system. I know that something similar exists in FB but I didn't know if it was a feature that existed in other DBM's so, at first, I hadn't contemplated it since I wanted to get a generic solution.
In any case, I will also investigate this possibility.

SQLite3 for example does and the header file provided

When I try to access the link you indicate I get this message: about:blank#blocked
Regards

[Sieben]

Don't know why the link didn't work but here it is:

https://www.sqlite.org/c3ref/update_hook.html

[Zvoni]

Don't know why the link didn't work but here it is:

https://www.sqlite.org/c3ref/update_hook.html

Be careful with that one.
Read the documentation carefully.
IIRC, you cannot use the connection for which that callback has been called

I know that because i have fiddled around with that.
Successfully i might add

[Sieben]

Could you elaborate on that a bit? And perhaps show some code on how and where you implemented it...?

[Zvoni]

Sorry, i‘m currently on vacation and using my iPad
Will be next week