Recent

Author Topic: 1 way sync local to master db sqlite  (Read 1969 times)

lawman

  • New Member
  • *
  • Posts: 43
1 way sync local to master db sqlite
« on: January 25, 2020, 12:29:39 pm »
hi,

is there an easy framework or routine to easily 1 way sync many local db with one central master db using sqlite?

the master will only hold a centralised copy of the many local dbs, for viewing by manager.   manager wouldn't change anything in master.

however, if local db amends/deletes rows, then this would also need to be updated to master.

i know sqlite local apps can read concurrently from the centralised master db. 

only 1 local app can write to central master db at a time, but if locked, would just want this to retry after a minute.

the syncing should happen in background maybe with idle timer, so shouldn't affect user.   syncing doesn't have to be to the second.   every 5 min should be fine and before closing.

I chose sqlite as I don't need server yet, only local embedded db app.    was considering firebird, but that doesn't allow concurrent access by many local apps to one centralised db, in embedded mode - have to use server.

later, i may want 2 way syncing but not yet.
« Last Edit: January 25, 2020, 12:36:28 pm by lawman »

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: 1 way sync local to master db sqlite
« Reply #1 on: January 25, 2020, 03:54:02 pm »
For information, the integration of several databases into a centralized database is sometimes called the " briefcase model " database or application.
Otherwise, it's " replication ", when it's just bi-directional.
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

lawman

  • New Member
  • *
  • Posts: 43
Re: 1 way sync local to master db sqlite
« Reply #2 on: January 25, 2020, 07:11:54 pm »
How to do this with sqlite or Firebird?

Thx

lainz

  • Hero Member
  • *****
  • Posts: 4449
    • https://lainz.github.io/
Re: 1 way sync local to master db sqlite
« Reply #3 on: January 25, 2020, 08:59:56 pm »
You need to bring more information.

All these sqlite DB are used by only one application? If yes, you want that application as well write to the master DB?

Or these are different DB from different applications? You want any of these applications or another to update to the master BD?

That is for you to think what and where you will be coding.

The method we use in the company I work is to have fields like date of InsertedOn, UpdatedOn and DeletedOn.
There is as well a model table wich stores the latest time each table was synced.

The fields that are updated doesn't have UpdatedOn, is cleared and these are uploaded the next time.

InsertedOn is registered when you write the value the first time in the master DB.

DeletedOn must be added when you delete locally a value, instead of really deleting it, insert this field. Then when updating all the DeletedOn are deleted from the master DB, when the syncrhonization finishes you must delete it from the database, I mean any of them, not the master DB.

lawman

  • New Member
  • *
  • Posts: 43
Re: 1 way sync local to master db sqlite
« Reply #4 on: January 25, 2020, 09:05:57 pm »
all local dbs will have same app running on each users local computer.

each local app will upload their local db to central master db, which has same schema but an accumulation of all local db tables etc. combined in one central location.

that way managers can search the master db to check information per user local db data.

lainz

  • Hero Member
  • *****
  • Posts: 4449
    • https://lainz.github.io/
Re: 1 way sync local to master db sqlite
« Reply #5 on: January 25, 2020, 09:11:05 pm »
You need to code an application that gets the data and inserts into the DB.

So you will have 2 applications, one sending the data from multiple machines and one writing the data into the DB in a single machine.

lawman

  • New Member
  • *
  • Posts: 43
Re: 1 way sync local to master db sqlite
« Reply #6 on: January 25, 2020, 10:06:15 pm »
couldn't it be one app, on the local pc?

1.  local app gets data from local db
2.  local app writes data to master db sitting on eg. google drive.

Only think is,

3.  Is it possible for lazarus pascal to read/write to a file sitting on google drive?
4.  Is it possible to do this for a sqlite db


lainz

  • Hero Member
  • *****
  • Posts: 4449
    • https://lainz.github.io/
Re: 1 way sync local to master db sqlite
« Reply #7 on: January 25, 2020, 11:46:41 pm »
1 yes
2 no, Google drive is not for storing db
3 yes with an api but not for example inserting a row in a sqlite database, just whole files
4 no

devEric69

  • Hero Member
  • *****
  • Posts: 648
Re: 1 way sync local to master db sqlite
« Reply #8 on: January 26, 2020, 11:29:03 am »
couldn't it be one app, on the local pc?
1.  local app gets data from local db
2.  local app writes data to master db sitting on eg. google drive.

You need to have (already said, but rephrased):
1°) a software layer that "emulates" only the local update: a "false commit", which is only local then, not on the central master db.
2°) and you need another software layer, that solves whether the local "false commit" can be definitively transformed into a "final commit" on the central master db (only if your update is the oldest one, i.e. nobody will have updated your record on the central master db *before the timestamp of your local modification*).
So, in short, whether these 2 layers are in the same application, or are separated in 2 applications, it's a kind of "2-step commit", i.e. 2 logics must be present.


Google drive is a big inert file storage space.
« Last Edit: January 26, 2020, 11:30:44 am by devEric69 »
use: Linux 64 bits (Ubuntu 20.04 LTS).
Lazarus version: 2.0.4 (svn revision: 62502M) compiled with fpc 3.0.4 - fpDebug \ Dwarf3.

 

TinyPortal © 2005-2018