Manikandan Thiagarajan
asked on
How to make offline data synchronize to online(when connected to network
How to make mobile offline data synchronize to online when connected to network
ASKER
Yes we are developing some sort of application data will be captured in offline local database it will be synchronize when connected to network
The simple way to do this is set your row data as having a primary key of instance id + timestamp + sequence number.
Instance ID - machine or install, might be many on one machine.
Timestamp - when each row created.
Sequence - This is an auto-increment number used as a tie breaker, in the odd even you have two exactly the same timestamps on the same instance. You can get rid of this if you wrap your INSERT statement in a try/catch mechanism + just retry repeatedly, until you get a unique timestamp. When I'm coding this type of system, I use a try/catch loop, so I can avoid creating the Sequence column.
Then whenever any offline instance connects, you do the following.
1) Lookup the latest instance + timestamp.
2) Create a SELECT to pull all data > (last instance + time stamp).
3) INSERT all this data into your online/common database, which will always produce only new records being added from all your offline instances.
Pretty standard code.
You might even find an entire online/offline library on GitHub which implements this entire system in your target language.
Instance ID - machine or install, might be many on one machine.
Timestamp - when each row created.
Sequence - This is an auto-increment number used as a tie breaker, in the odd even you have two exactly the same timestamps on the same instance. You can get rid of this if you wrap your INSERT statement in a try/catch mechanism + just retry repeatedly, until you get a unique timestamp. When I'm coding this type of system, I use a try/catch loop, so I can avoid creating the Sequence column.
Then whenever any offline instance connects, you do the following.
1) Lookup the latest instance + timestamp.
2) Create a SELECT to pull all data > (last instance + time stamp).
3) INSERT all this data into your online/common database, which will always produce only new records being added from all your offline instances.
Pretty standard code.
You might even find an entire online/offline library on GitHub which implements this entire system in your target language.
ASKER
How to get the instance Id please explain offline. To online once again
Each time your application is installed anywhere or reinstalled anywhere, the installation process will do a callback to some central server + ask for an instance ID. This is just a sequential number assigned, to ensure each application install or reinstall has it's own instance ID.
Likely you'll maintain a correlation of license key to instance, so you know the user of your instance too.
Likely you'll maintain a correlation of license key to instance, so you know the user of your instance too.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Suppose I have huge amount of data how could I perform online to offline and offline to online
Data is data, so make no real difference of the size... to a point...
If you're trying to transfer Gigabytes of data each connection, then you do have a problem.
Speed of your client side connections, which may be running on a slow 3G network.
The normal way to handle large transfers is via logic similar to rsync.... You compress data + transfer data in chunks, so if you've been transferring data for an hour + your connection breaks, you don't start over syncing all data, you pick up where you left.
If you use the above database design, this all occurs as a side effect of the design, as each time you pull one row of data (one chuck), the database gets updated + if you connection breaks, then you start over again.
Now if the case of massive data, you'd likely batch a group of rows/records together to transfer, to avoid having a connection latency penalty for every data transfer.
Again, if this is your first time designing a system like this, hire someone proficient in your language of choice (server + client site) who understands database design + designs of systems like this.
If you're trying to transfer Gigabytes of data each connection, then you do have a problem.
Speed of your client side connections, which may be running on a slow 3G network.
The normal way to handle large transfers is via logic similar to rsync.... You compress data + transfer data in chunks, so if you've been transferring data for an hour + your connection breaks, you don't start over syncing all data, you pick up where you left.
If you use the above database design, this all occurs as a side effect of the design, as each time you pull one row of data (one chuck), the database gets updated + if you connection breaks, then you start over again.
Now if the case of massive data, you'd likely batch a group of rows/records together to transfer, to avoid having a connection latency penalty for every data transfer.
Again, if this is your first time designing a system like this, hire someone proficient in your language of choice (server + client site) who understands database design + designs of systems like this.
ASKER
I want to only design database that is java language how could I proceed with database deign
ASKER
Could you please Explain with simple words for below sentence sequence - This is an auto-increment number used as a tie breaker, in the odd even you have two exactly the same timestamps on the same instance. You can get rid of this if you wrap your INSERT statement in a try/catch mechanism + just retry repeatedly, until you get a unique timestamp. When I'm coding this type of system, I use a try/catch loop, so I can avoid creating the Sequence column.
Auto Incrementation is basic SQL.
https://mariadb.com/kb/en/ library/au to_increme nt provides a good overview.
In summary, if rows are setup with an auto increment (sequence) field + time stamp field both, then multiple rows can contain duplicate time stamps. Otherwise you'll end up with a problem ordering your data on the server side, if data ordering is required.
I'd recommend you hire a database designer for your project, as they'll understand why a tie breaker (auto increment) field is required, if data ordering is required.
They will also understand about how to implement a looped try/catch if you'd like to skip the auto increment field, to reduce database row length.
The way this works is you try an INSERT on a row with a primary field of type TIMESTAMP, then you'll have a failure if the exact TIMESTAMP exists, then you just loop + keep trying. Each time you loop, the TIMESTAMP will change by a few microseconds, so likely any loop will only run once or maybe twice before you have a new + unique TIMESTAMP, which will allow your INSERT to succeed.
This is all basic SQL. Likely almost any database designer can implement this type code.
https://mariadb.com/kb/en/
In summary, if rows are setup with an auto increment (sequence) field + time stamp field both, then multiple rows can contain duplicate time stamps. Otherwise you'll end up with a problem ordering your data on the server side, if data ordering is required.
I'd recommend you hire a database designer for your project, as they'll understand why a tie breaker (auto increment) field is required, if data ordering is required.
They will also understand about how to implement a looped try/catch if you'd like to skip the auto increment field, to reduce database row length.
The way this works is you try an INSERT on a row with a primary field of type TIMESTAMP, then you'll have a failure if the exact TIMESTAMP exists, then you just loop + keep trying. Each time you loop, the TIMESTAMP will change by a few microseconds, so likely any loop will only run once or maybe twice before you have a new + unique TIMESTAMP, which will allow your INSERT to succeed.
This is all basic SQL. Likely almost any database designer can implement this type code.
ASKER
Rsynch and transfer data in chunk means
This is my last question after that I will close the question
This is my last question after that I will close the question
I think you're asking about the commonly available rsync program, which is one of the unsung hero workhorses of the entire Internet.
The approach of rsync + similar utilities, is to transfer data in a way as to be able to resume broken connections/transfers at the point the transfer breaks. So usually blocks of information or file stamp/checksum logic figures out how to arrange this.
So a chunk might be a chunk of data or simply a diff between a remote file + local file, then only transfer the diff (rather than the whole file).
Where diff means only the few bytes difference between two files.
The approach of rsync + similar utilities, is to transfer data in a way as to be able to resume broken connections/transfers at the point the transfer breaks. So usually blocks of information or file stamp/checksum logic figures out how to arrange this.
So a chunk might be a chunk of data or simply a diff between a remote file + local file, then only transfer the diff (rather than the whole file).
Where diff means only the few bytes difference between two files.
I really think you'd be served working with a good application + database designer as a first step for your project.
This will give you a much higher chance of good success, with your code.
This will give you a much higher chance of good success, with your code.
Different applications implement this differently.
Maybe clarify if you're asking about a specific application you already use or you're developing some sort of application, where you'd like to bake in the ability to run online/offline + sync up online, after you've been running your app offline for a while.