> On Dec 5, 2019, at 8:05 AM, George <gxg_...@yahoo.com> wrote: > > Changes -> bidirectional. All terminals can save to db. Amount of collected > data it's not big and frequency between readings will be minimum 2sec. When > we push more readings. Data itself it's very small. Like reading temperature > from a probe.
The product I work on — Couchbase Mobile* — does this, but it may not be exactly what you want because it uses SQLite internally on the client side, and not at all on the server. So it's not something you can plug your existing SQLite-based code into. (Our data model is not relational but JSON-based.) Data sync is hard. I've been working in this area since 2011 so I think I have some expertise here :) Problems you're likely to run into: 1. You can't use regular integer primary keys, because different clients will end up creating rows with the same keys and cause collisions when they sync. You either have to use UUIDs as keys, or else somehow partition the key space in a deterministic way, like prefixing a fixed client ID to a key. 2. If multiple entities can update the db, there will likely be conflicts. Conflicts are hard to manage, and how you do it is entirely dependent on your high-level schema. In the worst case, conflicts require human intervention. 3. You need a message-oriented protocol. It's best to keep a continuous bidirectional connection open. WebSockets is a good protocol for this. You'll have to deal with errors establishing the connection, and unexpected disconnects, by periodic retries. 4. Schema upgrades in a distributed system are a mess. If your system is centralized enough you can take it down and upgrade every peer's database, then bring it back up, but of course that doesn't work in a decentralized system. (One of the main reasons Couchbase is schema-less.) 5. Keep in mind there is no central source of truth. Data takes finite time to propagate, and transient errors greatly increase that time. Even if you have a central server, it will be behind the clients that create the data, so it doesn't have the latest info. No one does. —Jens * https://www.couchbase.com/products/mobile _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
