Android Question any idea about working on the offline database and then synchronizing it when connected to the internet through the application ؟

AlfaizDev · Aug 23, 2024

any idea about working on the offline database and then synchronizing it when connected to the internet through the application ؟
If we assume that the rule is on the mysql server and a local rule is made sqlite for offline mode, how can they be fully synchronized in a moment, is there any idea about this?

Alex_197 · Aug 23, 2024

alfaiz678 said:
any idea about working on the offline database and then synchronizing it when connected to the internet through the application ؟
If we assume that the rule is on the mysql server and a local rule is made sqlite for offline mode, how can they be fully synchronized in a moment, is there any idea about this?

That's how my app works. I don't need to be connected to the Internet 24x7. I have sqlite database and when I need I click a button sync in my app and sync the data.

I created the API that does all the job. It's just a website written in ASP.Net. Local database tables have fields Sent and Received. When I click a button all the tables that needs to synced update Sent field with a timestamp. I don't know if I have the Internet when I click the button. If not - it will be done later but I already know all the data that has to be synced. Another words - send all the data where Sent is not null and Received is null. Received timestamp we're getting from the server response.

amorosik · Aug 23, 2024

Alex_197 said:
That's how my app works. I don't need to be connected to the Internet 24x7. I have sqlite database and when I need I click a button sync in my app and sync the data.

I created the API that does all the job. It's just a website written in ASP.Net. Local database tables have fields Sent and Received. When I click a button all the tables that needs to synced update Sent field with a timestamp. I don't know if I have the Internet when I click the button. If not - it will be done later but I already know all the data that has to be synced. Another words - send all the data where Sent is not null and Received is null. Received timestamp we're getting from the server response.

You say the app works even when disconnected from the network
Where do you get the timestamp?

AlfaizDev · Aug 23, 2024

Alex_197 said:
That's how my app works. I don't need to be connected to the Internet 24x7. I have sqlite database and when I need I click a button sync in my app and sync the data.

I created the API that does all the job. It's just a website written in ASP.Net. Local database tables have fields Sent and Received. When I click a button all the tables that needs to synced update Sent field with a timestamp. I don't know if I have the Internet when I click the button. If not - it will be done later but I already know all the data that has to be synced. Another words - send all the data where Sent is not null and Received is null. Received timestamp we're getting from the server response.

But I don't know an elaborate synchronization mechanism, as the rule is worked on by many users, if we add new data from sqlite to mysql, this will be easy, but the problem is in old data that has been updated, how will this synchronize

walt61 · Aug 23, 2024

Have a look here: https://www.b4x.com/android/forum/t...tabases-class-source-included-b4a-b4j.161218/

Alex_197 · Aug 23, 2024

amorosik said:
You say the app works even when disconnected from the network
Where do you get the timestamp?

from the app itself, You can put whatever you want - you can put True / False for instead of the timestamp.

Timestamp in the app

B4X:

public Sub Now As String
    
    Dim strNow As String
    
    strNow=DateTime.Time(DateTime.Now)
    
    Return strNow
    
End Sub

amorosik · Aug 23, 2024

Alex_197 said:
from the app itself, You can put whatever you want - you can put True / False for instead of the timestamp.

Timestamp in the app

B4X:

public Sub Now As String Dim strNow As String strNow=DateTime.Time(DateTime.Now) Return strNow End Sub

I don't think it would work correctly
If you use a timestamp as a reference to understand how to order the records during synchronization, it MUST be read by a common system between all the stations, for this reason I was asking where you get the timestamp from if the stations are disconnected from the network
Otherwise you risk that a single station with the clock set incorrectly makes correct synchronization process impossible

Alex_197 · Aug 23, 2024

alfaiz678 said:
But I don't know an elaborate synchronization mechanism, as the rule is worked on by many users, if we add new data from sqlite to mysql, this will be easy, but the problem is in old data that has been updated, how will this synchronize

amorosik said:
I don't think it would work correctly
If you use a timestamp as a reference to understand how to order the records during synchronization, it MUST be read by a common system between all the stations, for this reason I was asking where you get the timestamp from if the stations are disconnected from the network
Otherwise you risk that a single station with the clock set incorrectly makes correct synchronization process impossible

I'm using timestamp to mark the records that has to be send to the server. Instead of the timestamp you can put True / False

amorosik · Aug 23, 2024

Alex_197 said:
I'm using timestamp to mark the records that has to be send to the server. Instead of the timestamp you can put True / False

Sorry but i don't understand
If station1 and station2 modify the same record, for example client "code=100 name=MARIO surname=ROSSI", each on their own db, how do you understand which of the two )station1 or station2) is the correct record to use on the 'real' database and therefore to distribute to the other stations?

Alex_197 · Aug 23, 2024

alfaiz678 said:
But I don't know an elaborate synchronization mechanism, as the rule is worked on by many users, if we add new data from sqlite to mysql, this will be easy, but the problem is in old data that has been updated, how will this synchronize

you can keep UserID of each user in your local database. Also you can implement a Ticket which is an encrypted Device ID. You can sent a URL to your server that will look like this

B4X:

https://www.yourdomain.com?Ticket=strTicketEnctrypted&UserID=1234&List=123456_7890&whattodo=dosomething

where strTicketEnctrypted is encrypted Device ID.
whattodo - what exactly to do (let's say you want to sync all the orders created in your app.

List - here you can pass orders details such as order id, price and so on

From this URL we know who created the order, device id of this user and the orders details

Alex_197 · Aug 23, 2024

amorosik said:
Sorry but i don't understand
If station1 and station2 modify the same record, for example client "code=100 name=MARIO surname=ROSSI", each on their own db, how do you understand which of the two )station1 or station2) is the correct record to use on the 'real' database and therefore to distribute to the other stations?

What would you do if they open the same client on the website and start to edit his details? Of course they will overwrite each other.

AnandGupta · Aug 23, 2024

amorosik said:
Sorry but i don't understand
If station1 and station2 modify the same record, for example client "code=100 name=MARIO surname=ROSSI", each on their own db, how do you understand which of the two )station1 or station2) is the correct record to use on the 'real' database and therefore to distribute to the other stations?

This is problem case for any network database management, even multiple programmers on GIT project.
The solution is:

Master can be updated by main, say server, only. Or whichever you decide.
Transaction can be synced on internal number. The external displayed number can have node/app prefix.

Better to allow only view if network not available and add in network server only, controlling the data.

amorosik · Aug 23, 2024

Alex_197 said:
What would you do if they open the same client on the website and start to edit his details? Of course they will overwrite each other.

No, if two workstations tried to modify the same data within a db, one of the two would arrive 'before' and the other 'after'
And the db server can know exactly this 'before' and 'after' because it is it (the db server) that received the modification requests
But we were discussing the synchronization of data between workstations that are off the network
And therefore do not have the possibility of having a unique 'time reference'
It is a very different situation, it is not possible to define a 'before' and an 'after' if the workstations are isolated

Alex_197 · Aug 23, 2024

Alex_197 said:
What would you do if they open the same client on the website and start to edit his details? Of course they will overwrite each other.

Employee A comes to the client B to do some work. That means that we create a temporary TempJobID in the local database in the table tblJobs.
Then in the table tblJobsDetails we will use this TempJobID as a foreign key.

Employee C comes to the client B to do some work. So we will create another record in tblJobs and will create another TempJobID and so on.

That means they will never overwrite each other.

Then Employee A sync his app with the server. We will create a record in the tblJobs in the main database, will get a JobID. Then will create a record in the main database in the table tblJobsDetails with JobID as a foreign key. But the main idea is that we will return a JSON string back to the app that will have a timestamp created on the server, TempJobID and whatever you want to return back to the app, let say a table name and the userID

To finish the sync process you will open a tblJobs in your app local database and update the following files in tblJobs table:

JobID - that will have a JobID created on the server so you later match this record with the main database
Received - that will have a timestamp created on the server so you won't send this record back to the server again.

Alex_197 · Aug 23, 2024

amorosik said:
No, if two workstations tried to modify the same data within a db, one of the two would arrive 'before' and the other 'after'
And the db server can know exactly this 'before' and 'after' because it is it (the db server) that received the modification requests
But we were discussing the synchronization of data between workstations that are off the network
And therefore do not have the possibility of having a unique 'time reference'
It is a very different situation, it is not possible to define a 'before' and an 'after' if the workstations are isolated

to generate the local timestamp you don't need to be connected to the Internet.

If both employees tried to edit the same record - it will be the same before and after. I don't think so that it will be within the same milliseconds.

amorosik · Aug 24, 2024

Alex_197 said:
to generate the local timestamp you don't need to be connected to the Internet.

If both employees tried to edit the same record - it will be the same before and after. I don't think so that it will be within the same milliseconds.

Sure, you can generate a timestamp locally on station1, station2, station3
But those values are useless if you then have to synchronize the data coming from different stations
Just think that station1 and station3 modify the data (on local database) of the same record, for example the customer code=100
If you don't have a timestamp based on the same source (and you can't have it because you said that the stations are off-network) how do you know who entered/modified the data of that record last?

If station3 had the system clock 10 minutes ahead, it might seem that station3 was the first to modify, then it modified station1
And instead the sequence was opposite in reality

Alex_197 · Aug 24, 2024

amorosik said:
Sure, you can generate a timestamp locally on station1, station2, station3
But those values are useless if you then have to synchronize the data coming from different stations
Just think that station1 and station3 modify the data (on local database) of the same record, for example the customer code=100
If you don't have a timestamp based on the same source (and you can't have it because you said that the stations are off-network) how do you know who entered/modified the data of that record last?

If station3 had the system clock 10 minutes ahead, it might seem that station3 was the first to modify, then it modified station1
And instead the sequence was opposite in reality

You can set a insert or update trigger to your table in the server database that will create a log. Or you can create a stored procedure that will do the same - first it will add a record to the log and then update the record.

amorosik · Aug 25, 2024

If the various stations are off the network, as described in the initial post, you cannot perform an insert or modify a record on the 'database server'
Since the stations are disconnected from the network, you cannot reach the 'database server'

Erel · Aug 25, 2024

Check this: https://www.b4x.com/android/forum/threads/b4x-cloudkvs-synchronized-key-value-store.63536/

MrKim · Aug 26, 2024

There is no clean way to do it short of keeping a log of all changes "AND WHAT WAS DONE". Then combining the logs and processing them in sequential order and somehow having the log know what was done. What if someone updates information based on old data. Example: customer calls the sales guy and says "Add 10 more to my order" so the sales guy updates the order from 10 to 20. Later he wants to add another 10 but he can't reach the salesman so he calls the office and says, "Add another 10 to my order." So the office updates the order from 10 to 20 because they don't know about the 10 the salesman added. At this point the order should be 30 but the last edit was 20. Dates can have a similar problem. Even text fields if edited by two people would only result in the last edit if working solely by time. And that is only TWO users. What happens when a THIRD user updates the record. And user 3 updates the record between user 1 and user 2 but user 3 gets back to the office and updates his data first?

The only reliable solution is to log ALL RECORDS when changed when an edit is made then update the record. Then, when syncing, if the LOGGED record doesn't match the record you are updating a human being has to look at ALL LOGGED changes since to both records AND look at the original record and decide what to do.

How do I know this? I wrote a "simple" app for myself to store information across my multiple devices. No other users involved. What a mess!

Android Question any idea about working on the offline database and then synchronizing it when connected to the internet through the application ؟

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Active Member

Well-Known Member

Expert

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Expert

B4X founder

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency