Database sync between application and run rev sever

[andyh1234]

I'm wondering if anyone can suggest the best strategies for keeping a database in sync between desktop apps using an online rev server.

To explain, i have an existing rev app that uses sqllite for the database. I currently use drop box to keep the database in sync across a few computers, but i would like to improve this using the on rev service, and in the long run then add a web interface so the uss could also access the database usi online forms.

To do this, the database really needs to be in 3 places (or more), the two desktop computers and the on rev server, and be kept in sync so if the desktop computer was offline when a change was made, when the app is started with the commuter online it resynced.

Any ideas as to a strategy to do this. I'm happy enough coding in rev, on rev and SQL, but seem to be having a few problems getting my head around the complexity of it, so any suggestions as to the best methods or practices of keeping databases in sync would be appreciated!

Thanks

Andy

[RRobert]

1) You could setup a MySQL server and migrate your database. And communicate directly with the MySQL server.
2) You could put the SQLite database on a server and do the operations by CGI scripts or scripts in general which will be called from your Destkop apps.

Robert

[bangkok]

Indeed, to set up a MySQL server, would be the best solution :

-1 you use OnRev
-2 you use any other web hosting services (usually they all propose a MySQL DB)

It's true that OnRev would help you to go further. It's really a great system.

[you could have a look by the way on Runrev Server, the "OnRev" you can install by yourself on your own Apache webserver]

However, I think your "asynchronous" sync scheme might be tricky.

What will you do if, on local SQLlite, Desktop 1 and Desktop 2 have modified the same record ? How you will handle the sync later on the remote MySQL DB ?

[andyh1234]

Thanks for the replies.

Im going to go with On-rev and sql, see how it works. I have the on-rev package (on odin) for the development, although the server load on that server is currently hovering around 3 so I guess its already quite busy so if all this works its goo to know revserver will be available to put on a separate dedicated server.

Ive been looking at the way Google gears and Mobile Me seem to handle syncing, and think the best way is for the server side db to contain a 'last modified' counter, so for speed and minimum data transfer if the local computer checks its counter against the server and they match it can then know there is nothing to do.

If the counter is different, then it will know it has to sync.

If the computer has been offline and changes have been made, but when it goes back online and checks and the counter is different (so the data was also changed on another computer), then Im going for the way MobileMe seems to handle the issue and just prompt the user to say the data was changed elsewhere while this pc was offline and which version do they want to retain, local or remote, that seems the easiest solution at the moment.

As all my data is also date based, I figure I can reduce possible conflicts my making each separate days data sync separately, say if the overall counter is different, the app can then ask the server for a checksum on each days data, locate the days that have changed and just download the data for those days. That way it keeps data transfer down and if a local user has modified one day while offline, and then modified a different day on a different computer, the sync should be able to handle it without a conflict.

Finally, this leaves the door open for a small iphone or android app as it could just request the data for an individual day at a time, really keeping the data transfers down for the mobile devices. I figure I can write a small 'shell' app for the iphone that consists of a browser wrapped in a app, then the rev code on the server can serve it just what it needs so I can code it all in rev and updates may also be possible on the move.

Does that sound feasible, or have I missed something important?

Andy

[RRobert]

Does that sound feasible, or have I missed something important?

You should use SSL (HTTPS).

Robert

[andyh1234]

Thanks, I hadnt thought of that.