Copying remote Firebird table to local database

Question

I have a remote Firebird 3.0 server with a database. In this database, there is a big table. The client very often queries this table during their work. There are too many clients and bad internet connection, so the work with this table is terrible. I made a local copy of this table via IBExpert into a temporary database, which is distributed with client application.

But now there is a need in a change of some values in this table (add new values and edit some olds). So I need some kind of synchronization - copying of remote modified table to client's local database.

The client application was made by use of Delphi Berlin 10.1. So the synchronization should be done by Delphi code.

Can you give me an idea, how it will be correctly to synchronize such a big table, please?

i'd use some of quasi-replication tools available for firebird if You REALLY need that kind of setup : http://www.firebirdfaq.org/faq249/ — Vancalar, Nov 17 '18 at 17:54
Are you looking for [this](http://docwiki.embarcadero.com/RADStudio/Tokyo/en/Database_Alerts_(FireDAC))? — Ilyes, Nov 17 '18 at 18:37
How big (in Mb) is the table, and do the users of the remore db all access it via your app or another one? — MartynA, Nov 17 '18 at 20:19
@MartynA ~8 Mb, and yes, they access it via my app. Some of users have restrion to launch third-patry apps. — Tim Woo, Nov 18 '18 at 06:04
Please explain more about your setup? Is this data in a separate database, would it be ok to just delete it and create the database new, or does it also contain data that needs to be preserved? — Mark Rotteveel, Nov 18 '18 at 09:08
Well, you *might* be able to cobble something together in Delphi code, but if this problem table is the wrong side of a bad i'net connection, have you considered maybe replicating it to clients using a `bit torrent`? That way the clients could act as seeds for one another ... — MartynA, Nov 18 '18 at 10:18
@MarkRotteveel Yes, I have 2 separate DB: 1 - at Server, DB with lots of tables (including problematic one); 2 - at client, DB with only problematic table. Changes happen at server, and that changes should be made also at local copies. I think that it's not okay to delete client's DB and re-create it, because of large size and bad connection. At present I'm trying to make sync by means of Server Alerts and additional ChangeLog-Table as Sami suggested. — Tim Woo, Nov 18 '18 at 10:29
Zipping up a backup and restoring it locally could be simpler (and maybe quicker). — Mark Rotteveel, Nov 18 '18 at 10:30
`The client very often queries this table during their work` - but does the client need ALL that table for his work? maybe the client would be okay with some subset of it ? Not all columns, not all rows? — Arioch 'The, Nov 19 '18 at 08:32
I'm astonished you've just accepted that answer, when you've already said you're using server alerts. — MartynA, Nov 19 '18 at 09:46
@MartynA probably the point was how to react on that master alert, how exactly to implement replicating the changes into local server — Arioch 'The, Nov 20 '18 at 08:24
@MartynA QUOTE: "At present I'm trying to make sync by means of Server Alerts and additional ChangeLog-Table as Sami suggested." (Are you looking for this? – Sami Nov 17 at 18:37) So I tried to make sync that way, and it seems to be working well for a now. — Tim Woo, Nov 21 '18 at 15:16

score 0 · Accepted Answer · edited Feb 27 '21 at 08:34

You could fire POST_EVENT on master database (for insert, update, delete (triggers)) to notify client applications that there are changes.

Then your client would need to fire procedure (on local DB) to do a sync. This could be done by EXECUTE STATEMENT ON EXTERNAL

  FOR EXECUTE STATEMENT ('SELECT ... WHERE CURRENT_TIMESTAMP >= tablename.modifiedon')
  ON EXTERNAL 'SERVER/PORT:DBPATH'

You should include date of insert/modified/delete in master DB.

Copying remote Firebird table to local database

1 Answers1