Isaac Raway wrote:
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]
-----------------------------------------------------------------------------
I can elaborate a little on our implementation. We use a concept of
having a master central DB, which makes the sync'ing logic simpler.
Each slave sends its transactions to the master in XML and each time the
master changes it updates a serial number. When a slave detects it is
stale it requests the transactions necessary to bring it into sync.
The ability exists in the design to lift the performance by giving each
remote client its own serial to eliminate unecessary sync'ing, but that
is not currently a problem.
We use XML instead of SQL as the transmission medium for generality
since either DB could be any structure, including a simple case of an
XML file. The application is a common one, an ASP system run from a
global server but with clients distributed internationally and prey to
network failures and slowness.
In our case each DB does not have many users or a very high transactions
rate, but it does require accurate synchronization. Each client
maintains a queue of transactions pending on the master and that can
grow if there are network hitches or breakdowns. The inaccuracy in our
system occurs when a client requires global data which has been modified
by another user remote from that client. The system design is such that
it would take a very extended breakage for that to become an issue.
Currently we use Sqlite embedded in a custom application server as the
master, but that may change one day to PostgreSQL should increased
central multi-usage make it necessary.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
