Well, putting this together I think I may have settled on a solution for the
first version of this project:
1) Only a single user will have access to each DB, therefore taking the most
recent record from any table will always be the right thing to do. This
avoids the complexity of conflict resolution, deltas, etc. but of course
reduces complexity. I'm going to try to write it in such a way that conflict
resolution will be "easy" to add -- as in, not made more complex by my
design.
2) Rsync is interesting. Very interesting, but I'm afraid it probably won't
serve my purposes. Some records will come down, some will go up which as I
understand it isn't compatible with rsync (it is meant to keep a mirror copy
in sync with a master). So, I'm going to design a web service that will
allow me to get a list of modified records and then sync their data between
the client and server. A nice side effect of the web service is that object
will eventually be shareable by their URL since the application will already
understand the format returned by the service.
3) To solve the problem of unique IDs, I've come up with a single scheme:
each record created locally will have a normal numeric ID. Records created
on the server by some user action there will have a prefix or suffic ("r456"
etc). I'm a bit worried about indexing these two types of ID values in the
same table though. Any thoughts on this? I know it will "work" if I put
something with a character in it into an "integer" field in SQLite, but is
it the best idea? Right now I haven't done a ton of indexing, it hasn't been
required for performance, but I'll have to start doing that pretty soon. I
may also be able to use some sort of numeric convention, perhaps all odd
values are local, all even ones remote, etc. Any thoughts on this?
On 11/19/06, John Stanton <[EMAIL PROTECTED]> wrote:
Isaac Raway wrote:
> I am looking at a design that will require syncing a disconnected SQLite
DB
> file on client's machines to a central server. The version of the DB on
the
> server will also be modified periodically, so there is a chance that new
> records will be created in either and also updated. Conflicts therefore
are
> an issue. I am looking at generic methods for handling the deltas
between
> BLOBs I have in my DB, and I think that's within reach.
>
> What I'm worried about is just the logistics of either 1) importing all
> user's data to a single DB somehow or 2) managing several DB files from
> clients automatically. Has anyone does this kind of syncing? I realize
I'm
> somewhat light on details, but I'm not really even sure exactly what
this
> system will need to do: it's more of a framework really.
>
> At any rate, anyone have experience syncing SQLite DB files?
>
One method is to use rsync to synchronize the files. That takes care of
deltas etc.
We keep Sqlite DBs sync'd by maintaining a "stale" counter using
triggers and having the remote DB sync itself when it decides that it is
stale. We use an XML based transaction transmission to perform the
synchronization. The theory of our method is that the sync'd DB could
be any RDBMS, not necessarily Sqlite or even an SQL driven DB.
This last method involves some extra overhead. A DB access includes a
communication with the central DB to get the current status. The rsync
method is less overhead, but does not maintain real time synschronization.
Our real time method is used to maintain a distributed database with
good local performance and which is tolerant of network disruptions. A
network failure does not stop production, it merely degrades the quality
temporarily.
A word of caution. Keeping the DB's sync'd in real time involves a lot
of logic and is not a trivial exercise.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
--
Isaac Raway
Entia non sunt multiplicanda praeter necessitatem.
http://blueapples.org - blog
http://stonenotes.com - personal knowledge management
