Sync a local SQLite DB with a server SQLite DB

Question

Is it possible, and if so how can I effectively sync a local database with a global one such that when an action is taken on the local version it is also made on the global database. Simply using version control to subvert the global is unacceptable because there could be several local copies of the database according to the number of people making changes to the database. In other words is it possible to just have a mirror of the database so it can be accessed locally even though it is somewhere else on a network.

Answer 1

Synchronizing databases if a very complex topic. The simplest approach is to keep a record at the application level (or though a custom sqlite wrapper) of all queries run against each system and then run those again when synchronizing.

This becomes problematic when you have multiple sources or two way synchronization. Queries that ran fine on one database may fail on an other due to different order of operations (assuming referential integrity is enabled).

Another more complex but robust approach is to keep track of a full transaction history for each table. You'll also need to know which records were affected in each transaction, and the order within each transaction. When that's done, you'll probably need a way to detect when problems will occur and automatically work around them.