Any tricks on how to quickly define if content needs a Sync?

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Any tricks on how to quickly define if content needs a Sync?

Post by liveme » Sat Feb 20, 2021 5:36 am

Hi,

I'm looking for anyone using content syncro between 2 DBs.

I ve never wonder how this could be done with LC, there is probably something smarter than comparing all records against the other DB, nop ?
_ IF DOABLE...Planning on using...

- 1 PG - Postgres DB one 1 side...
- several Sqlite DB on the other side...


*though each Sqlite should also allow to upload its new records to the hosted PG DB.

How would one only sync lines of records modified since any last access ?

Thanks !
:idea: :idea:

SparkOut
Posts: 2834
Joined: Sun Sep 23, 2007 4:58 pm

Re: Any tricks on how to quickly define if content needs a Sync?

Post by SparkOut » Sat Feb 20, 2021 11:43 am

This is a huge task to contemplate.
There are companies and projects with paid and free products that synchronise between disparare databases, such as cData Connect from www.cdata.com and https://ampliapps.com/sqlite-sync/ which is dual-licensed, I believe. I found these through online searching, so I can't tell you how successful they are, but if I were you, I would leave the heavy lifting to another.

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Any tricks on how to quickly define if content needs a Sync?

Post by AxWald » Sat Feb 20, 2021 1:20 pm

Hi,
liveme wrote:
Sat Feb 20, 2021 5:36 am
How would one only sync lines of records modified since any last access ?
I have a system with local SQLites & a mySQL/MariaDB servers. In my case the scope of data that may change locally & may need synchronizing is quite limited, actually it's just 1 table ("t_orders"). When writing to this table I set a flag ("cached") for each record that I clear after synching. So far, so simple.


I have another system in place that may meet your requirements - I monitor the last updates of tables that need it. For this I use a special table in my local SQLite:

Code: Select all

CREATE TABLE 't_tablestats' ('TName' CHAR PRIMARY KEY  NOT NULL  UNIQUE , 'LUPdate' DATETIME)
This table is kept up to date using a trigger for each table I want to monitor:

Code: Select all

CREATE TRIGGER r_t_orders AFTER INSERT ON t_orders 
BEGIN 
REPLACE INTO t_tablestats (TName, LUPdate) VALUES('t_orders', STRFTIME('%s','NOW'));
END
The trigger inserts a value matching "the seconds" into "LUPdate" each time a table is modified.

(I use "AFTER INSERT" in the trigger definition because I want to know how old my cached data are. Using "AFTER UPDATE" would reflect when they got modified last time. Additionally, I just see, my definition of this field is incorrect - should be UNSIGNED. But SQLite is quite forgiving here.)

So a quick look at "t_tablestats" tells me when I last changed what tables. From here I can decide what to do.

Keeping a "modified" flag in the SQLite records & REPLACEing matching records in the "real DB" shouldn't be this big a problem. I'd do it this way:
  1. Create a temporary table in the "real DB" & insert the changed data.
  2. Using a transaction to REPLACE 'em into the target table.
  3. When done successfully, drop the temp table & clear the "modified" flag in the SQLite.

Just see, PostGreSQL doesn't have explicit REPLACE/ UPSERT. No big deal:

Code: Select all

INSERT INTO t_table ...
ON CONFLICT ([where_ever]) 
DO UPDATE SET ...
should be the equivalent.


This is a simple way to handle the problem. Just make sure you flag anything that gets modified, so you'll know what to write back. For sure, never really DELETE any records - just flag 'em as "deleted" & let the "real DB" do the cleanup.
If this isn't enough, search for "SQLite replication" - there's quite some tools for such.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Any tricks on how to quickly define if content needs a Sync?

Post by liveme » Sun Feb 21, 2021 12:13 am

Thanks axwald, I think I got the idea about using a tempory table, etc...

so one does have to run a search operation to look up this whole table to find out what records will need to be updated or deleted,
i guess if you do not have that many record to updat... this would not take very long anyway...plus it would also depends on how many time in a day one expect to sync all its users..

Q : What does "flag" means, and how do you do it, do you use of bolean column for a true/false status mark ?

Great Tks for all the time and samples you shared, very helpfull !
:idea:

stam
Posts: 2599
Joined: Sun Jun 04, 2006 9:39 pm
Location: London, UK

Re: Any tricks on how to quickly define if content needs a Sync?

Post by stam » Sun Feb 21, 2021 12:36 pm

If SQL isn't of paramount importance, give LiveCloud a look.

It a noSQL database but developed specifically for LiveCode; the database is cloud-hosted but you can easily have a local database that syncs with this if you so choose.

You can define table structures but the queries are basically defined by LiveCode arrays rather SQL statements. Or you can go full noSQL (i didn't).

They have a free tier so you can test... and the paid tiers are very reasonably priced.

My prior database experience had been with SQL and FileMaker Pro only, so it took a little getting used to, but i've decided to go ahead with this for a few projects of mine now that i'm more comfortable with it...

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 755
Joined: Fri Jun 27, 2008 9:00 pm

Re: Any tricks on how to quickly define if content needs a Sync?

Post by Mikey » Sun Feb 21, 2021 2:23 pm

You would not believe how many solutions exist for this particular problem.
You might be surprised that trying to optimize it does not save enough time to make it worth your time.
"Option 0", in many cases, is good enough - when it's time to sync, delete all records in the destination and copy everything from the source. With one of our mobile apps, we stopped trying to sync and instead just did this, instead, because it only took a few seconds. We did not save enough time trying to be optimal (only pushing updates) to make it worth while.
Then there are all the other ways to try to avoid doing that. You can use timestamps on records, you can compare values, you can use a log file, logical mirroring, hashes of records, etc.
If that was not good enough, then consider mirroring the databases.
There are certainly cases where you don't have any other options, like where you have a C/S setup. In that case, perhaps timestamps would be good enough.

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Any tricks on how to quickly define if content needs a Sync?

Post by AxWald » Sun Feb 21, 2021 3:48 pm

Hi,
liveme wrote:
Sun Feb 21, 2021 12:13 am
so one does have to run a search operation to look up this whole table to find out what records will need to be updated or deleted,
I'd do a query on my "t_tablestats" first. This shows me what tables have changed at all.
Thus I don't need to bother about tables that haven't changed. So:

Code: Select all

put "SELECT TName FROM `t_tablestats` WHERE LUPdate > (" & gPrefArr["lastDBUpdate"] & ");" into StrSQL
This gives me a list of all tables that need updating.
(gPrefArr["lastDBUpdate"] => "the seconds" of the last update).
Now I loop through this list:

Code: Select all

   repeat for each line L in myTableList
      put L into x_Table
      put gPrefArr[L].["fields2update_lite"] into x_Flds                        -- fieldList for x_table
      /* id,val1,val2  */
      put gPrefArr["SQL_Select4update"] into strSQL                      --  generic sql to get the data
      /* SELECT [[x_Flds]] FROM `[[x_Table]]` WHERE isModified = 1;  */
      put merge(StrSQL) into StrSQL
      /* SELECT id,val1,val2 FROM `myTable` WHERE isModified = 1;  */
      put doSQL("cache",StrSQL) into myRecords
      /* Generic SQL routine that queries in the cache DB & returns a result  */
      --  [error handling ...]
      
      /* Now all data from our table (that need updating) are in myRecords  */
      
      put gPrefArr[L].["tempTableDef"] into myTabDef                            --  tableDef for x_table
      /* `id` INT(8) NOT NULL,`val1` CHAR(64) NULL DEFAULT NULL,`val2` SIGNED NULL DEFAULT '0'  */
      put makeTempTable("myDB", myRecords, myTabDef) into myTemp
      /* This creates a temp table, fills it with myRecords, and returns "[connID],[tmpTblName]"  */
      --  [error handling ...]
      
      /* We have now a temp table with the data. So we call a stored procedure to process it:  */
      
      put gPrefArr[L].["updateProc"] into myProc                              --  storedProc for x_table
      put "Call `" & myProc & "` ('" & item 2 of myTemp & "');" into strSQL
      /* Example for the SQL in the proc (returns 1 if success, else 0):
      .   INSERT INTO `t_table` (`id`,`fld1`,`fld2`) (SELECT `id`,`val1`,`val2` FROM `t_temp`) 
      .   ON DUPLICATE KEY UPDATE `fld1` = VALUES(`val1`), `fld2` = VALUES(`val2`);  */
      put doSQL_M("myDB",StrSQL,item 1 of myTemp,true) into myResult
      /* Same SQL routine as above, only it uses a valid connection in Param3, and, if Param4
      is true, closes it  */
      --  [error handling ...]
      
      /* Assuming all went well we can now "unflag" our cached records:  */
      
      put gPrefArr["SQL_RemoveModFlags"] into strSQL           --  generic sql to reset "modified" flags
      /* UPDATE `[[x_Table]]` SET isModified = 0 WHERE isModified = 1;  */
      put merge(StrSQL) into StrSQL
      /* UPDATE `myTable` SET isModified = 0 WHERE isModified = 1;  */
      put doSQL("cache",StrSQL) into myRecords
      --  [error handling ...]
   end repeat
(Pasting this into a button "mouseUp" looks a lot better ...)

This is more or less copied from the original program. You may notice a few strange things:
  1. I'm using a "gPrefArr" (= global preferences array) a lot. This comes handy because I load it (from my FTP) at each startUp - so I can change a lot of stuff w/o even firing up an IDE.
    .
  2. All my database functions are encapsulated & in a library stack.
    - "DoSQL(myDB, StrSQL)" opens a connection to myDB (fetching the credentials from gPrefArr ...), determines what to use for SQL (revdb_execute or revdb_queryList), sends the query to myDB, checks the result, closes the connection and returns the data (or an error description).
    - "DoSQL_M(myDB, StrSQL, aConnID, mustCloseConn)" is quite similar - only it doesn't open/close the connection automagically, it checks in aConnID for a connection ID to recycle, and closes only if mustCloseConn is true.
    - "makeTempTable(myDB, myRecords, myTableDef)" is more convolute: It connects to myDB, creates a temporary table using myTableDef (dropping it first if it exists yet), inserts the data in myRecords & returns the connection ID & comma & the tempTableName. It doesn't close the connection because that would destroy the temp table. This is done then by a consecutive DoSQL_M().
    .
  3. The real job (updating/ inserting the data on the remote DB) here is done by a stored procedure on the "big iron". This way the whole stuff is quite fast - I only transmit a minimum of data and as few code as possible.
    Further, I keep the data cached as small as possible. For instance, a salesman only has its own 200 customer addresses stored instead of the whole 27,475 records from `addresses`. And when updating, only the absolute necessary fields are touched.
    As much as possible is left to the database engine (using stored views, functions, procedures, triggers). This way I can utilize the caching mechanisms & the "horsepower" of the remote DB.

liveme wrote:
Sun Feb 21, 2021 12:13 am
i guess if you do not have that many record to updat... this would not take very long anyway...plus it would also depends on how many time in a day one expect to sync all its users..
I have "worst case" times for "full sync/ renew" of about 10 - 15 seconds (a salesman with very many small customers, very many saved documents, nasty habits and an exceedingly sluggish ancient tablet). Usually it's done in ~5 - 7 seconds. Used by humanoids with IQ > shoe size (!= sales droids) it's a ~5 seconds renew at startup & occaisonal 3 - 5 seconds syncs/ updates during work.

After trying many ways to do it effectively I'm now at this solution:
My main module (the standAlone, acting as "menu") shows if there's need to sync, and nags occasionally (if there's need). The user is responsible to initiate the syncing itself (preferably when there's time & a good connection).
If the user consequently ignores this (for a quite long time) the program will cease working until a "full sync/ renew" is done.

liveme wrote:
Sun Feb 21, 2021 12:13 am
Q : What does "flag" means, and how do you do it, do you use of bolean column for a true/false status mark ?
Yup. 1 = true, 0 = false.

Hope this helps, have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

liveme
Posts: 240
Joined: Thu Aug 27, 2015 5:22 pm
Location: down under

Re: Any tricks on how to quickly define if content needs a Sync?

Post by liveme » Sun Feb 21, 2021 11:04 pm

So valuable and real experience, very helpfull indeed, big thanks !

Post Reply

Return to “Databases”