On 2014/02/10 23:20, C M wrote:
On Mon, Feb 10, 2014 at 2:54 PM, RSmith <rsm...@rsweb.co.za> wrote:
How to go from the error codes to the diagnosis? I think the logic is as
follows:
[lots of snipping....]
Thanks for this insight.
I purposefully put the SQlite database file in the Dropbox folder because
it was my intention, with this app, to allow a user to use the app on more
than one computer and "sync" the database via Dropbox. E.g., s/he could
make changes to the db at home and then also from his/her office computer,
get home, and the database would be synced. I tried this out on two
computers at home and it seemed to be sort of working, but I occasionally
got conflicted copies and yet never pursued the right way to do it.
But this must be a fairly commonly sought need. The solution you propose
where I occasionally export a copy of the db to Dropbox is great *for
backup purposes* but seems to exclude the possibility of syncing across
multiple computers. So what would you recommend?
This is something all the systems we make do all the time, and I am sure is a common thing with most people on this list, so we
definitely understand the need to sync. Problem is Dropbox (or any of the other ones) is a file-syncing solution and not suitable
for direct DB syncs for reasons we covered already.
With most database syncing we just use actual cloud servers and cloud syncs with an actual database, i.e we have several cloud
servers running typically Apache, PHP, MySQL, SQLite, etc. to which all systems connect and either upload or download additions,
changes and the like. Simple versioning within the tables keep track of who updated most recently on a per-record basis, sometimes
per-field for really critical apps, but this is uncommon.
What you essentially trying to do here is allow another system to take care of the syncing for you with the very real benefit that
you don't need to support a cloud server. The downside is that you have to play by the rules of whatever service is used.
I will be honest here, for every suggestion I can come up with, I can find a scenario where it would break in a cloud-sync
environment. We actually have one system that uses cloud syncing through external service (Dropbox, Skydrive, Google drive, whatever
the user uses), but the we use custom data files that play nice with the services, the SQLite (or any other RDBMS) files simply
won't work. This means some extra work. Basically we create a computer-based key (hash), write all changes from the DB to a file
with that key as part of the name in the sync folder (MyDocs/Dropbox/ or whatever the user sets up). Any other system with which it
is shared sees that file appear in its syncing folder, and as soon as it becomes available (checking every few seconds or so), opens
it, adds the updates contained within it if those updates are newer than it's own last changes for the specified records/keys. In
turn, it will write a file containing its own newest changes etc.
SO any system connected to the cloud sync, sees all files created by all other computers, there may be many, but they are typically
very small. Every system incorporates updates from every other system's file if it is newer, but only ever creates its own update
file for its own changes and only really does that on a timely basis when the file is not locked. Every time updates are exported
only the newest updates are included and most important, what actually makes it all work, is that every such file has only 1 writer,
nobody else changes it - so you won't ever get the file trying to sync-back to your own folder or other related problems.
A headache we have is that one of the participant systems might go down for several days, user on holiday, whatever, and then it is
so far out of date that the update files it sees no longer contains some of the stuff it missed, in which case the user is prompted
to get a full DB backup from another user, and they can email it or dropbox it as another file or whatever.
ALL of these shenanigans simply because of the way the cloud syncs work - a rather cumbersome work-around, but hey, the
cloud-syncing is not on us, so a win.
I tried to be scant with details and give basic ideas because different approaches might be better depending on the exact nature of
the data and frequency of changes and importance of updates etc. etc. but feel free to ask off-list if needing more detail lest we
bore the others.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
