Peer-to-peer replication of a sqlite database

Question

I am looking for a way to replicate a small and simple relational database (like SQLite) across peers. This should work in an environment with unstable network connections, hence the need for each peer to have a full copy of the database. This should allow a peer to continue working off-line in the event of network failure.

To keep things simple, replication should only have to support the replication of addition of data, i.e. only INSERTs, not DELETEs or UPDATEs.

Does anyone know of a good - and ideally cross-platform - technology or method of creating such a system? I am currently looking at JXTA and JXSE, but I am put off by its complexity and apparant lack of life in its community after the takeover of Sun by Oracle.

Thanks! Frans

Vision

Answer 1

rqlite uses the raft consensus algorithm, so it should be fairly resilient to unstable network connection.

Also, it seems to be possible to configure rqlite to accept reads even in the case of a network failure.

A similar project, dqlite, exists as a library, available in various languages, but it seems less explicit about the event of a network failure.

Larondalarosa

Answer 2

You may want to explore JGroups for the communication layer if you don't like JXTA. For the replication, I think you will have to implement your own code.

Freemanfreemartin

Thanks! I will take a look at Jgroups. I understand I need to code the replication myself. With JXTA that is probably the same. It seems a far less complicated problem than efficicient P2P communication. – Vision

Answer 3

I am working on something similar (though the code is far from ready). I'll describe a little about my intended approach, but whether that is suitable for you depends on some key design points you'd need to consider. I am not aware of any ready-built projects that will do this, unfortunately.

• In particular we'd need to know what language you wish to use, or which languages you'd rather avoid.
• Also, consider how you intend to do peer dicovery - can you set up trust between node pairs manually, or do you want them to auto-discover?
• Presumably all peers may insert data?

If you are able to use PHP, and are happy manually peering node pairs, then my approach may be of interest. Set up an ORM such as Doctrine, Propel or NotORM, and get each node to regularly sync with an internet time source. For each new row in a db, grab the data (either in an array or ORM object), serialise it, and push it out to all nodes that you have a trust relationship with. Where a push fails, keep a note of this and retry at periodic intervals (potentially giving up after a remote node fails to answer a large number of retries).

Pushes can either be kicked off by your application that creates the row, or can be called by whatever scheduler is available on each machine. A push message can be XML, or for simplicity can be just a POST message containing the new row and whatever metadata (e.g. timestamp of save, so as to resolve INSERT order from several nodes).

If your nodes do not have static IP addresses, they could be registered with a dynamic DNS addressing service so as to allow each node to stay in touch with peers even if their IP changes. You might also consider adding a message signing system, to ensure that messages between nodes are genuine.

Concoct

Thanks for the tips. As for language - any language would do. I have never coded PHP but the most important thing is that the job gets done. I do want auto-discovery. The replication process should not require user interaction. Also, I would like smart peer behaviour, like: * use all available means of transport and switch if necessary * peers with high connectivity should help peers with low connectivity * prioritization of messages – Vision

If you don't mind using an unstructured database, you could try Refuge, which is based on CouchDB (and I think written in Erlang). I chatted to the developers via IRC and their knowledge & approach might be a good resource for you - they want their system to have auto-discovery too, I believe. I think it is early days for them, however - afaik they don't have a fully working prototype yet. – Concoct

Incidentally my project, Meshing, is also at early stages, but you may find some of my writing on it of interest. However bear in mind I don't intend to implement peer-discovery - for what I am trying to achieve, I don't think I need it. – Concoct