3 ms·

I've built out a journal layer on top of SQLite for a flash card app I'm working on. I wanted to be able to sync data across devices but I knew that I'd always

by allenu 6y ago

I've built out a journal layer on top of SQLite for a flash card app I'm working on. I wanted to be able to sync data across devices but I knew that I'd always just have to handle a single-user/multi-device scenario and never multi-user/multi-device. I was able to come up with a simple CRDT style system where I just log the modifications made per device and share them out with other devices via simple files (i.e. upload to central file store and download peer journals). Each device then has to reconstruct the state of the SQLite database entries using the shared journal files. Since it assumes single user, I can safely use "last write wins" as the conflict resolution strategy. It's working reasonably well for my small use case, but there are definitely limitations that you have to be aware of. The big one is the fact that it's journal-based means the journal can grow and grow. Since I use simple files as the protocol for sharing changes, this means uploading/downloading the same content more than I'd like. If I had a stream-based server that just handled the deltas, it would be better, but my goal with this project was to avoid having to roll my own service. (I wanted users to provide their own cloud storage, say iCloud or Dropbox, and the app would just use it as a central repo, meaning no fees for maintaining the service.) There are tricks around these limitations that I can work on if I had the time (my main goal is to build the app and not a sync system). For example, I could cap the journal files to a certain size and create a new one when the limit is reached. This avoids having to upload the same contents too frequently. I could also have a strategy where I merge all journals into a central file every so often so that the state of the world is shared there instead of the journals (which can then be discarded).

pdimitar 6y ago
Interesting, thanks for sharing. I'm curious as to why you didn't use sqlite's changeset / patchset API?
- allenu 6y ago
  Interesting! I wasn’t aware of that feature. I actually built out my idea first as its own toy database with journals and later added SQLite as the storage mechanism. Patchset could work for this, but I’d have to play with it a bit. Off the top of my head, I’d need to redesign things to consider time in terms of sessions as opposed to granular changes. i.e. today I can just append deltas and use a time stamp to know how it fits chronologically with other changes in peers. A new peer created could sort all deltas to play back the database from scratch. I suppose with patchsets it could sort them all by start time and apply them one after the other to recreate the database. It’s an interesting problem because you can’t assume to have synced recently, so you don’t know the baseline you are starting with, but for the application I’m building, that may not matter.
treekogreen 6y ago
This sounds like a cool project. Considering its a journalling app and I'm assuming the user just enters plain text, have you experimented with compression and decompression before and after the SQLite storage?
- allenu 6y ago
  It's actually a flash card app, not a journaling one, so the text entered is quite short. I haven't played around with the compression idea, but it sounds like it could be useful for large text entries.

by allenu 6y ago

pdimitar 6y ago

Interesting, thanks for sharing. I'm curious as to why you didn't use sqlite's changeset / patchset API?

allenu 6y ago
Interesting! I wasn’t aware of that feature. I actually built out my idea first as its own toy database with journals and later added SQLite as the storage mechanism. Patchset could work for this, but I’d have to play with it a bit. Off the top of my head, I’d need to redesign things to consider time in terms of sessions as opposed to granular changes. i.e. today I can just append deltas and use a time stamp to know how it fits chronologically with other changes in peers. A new peer created could sort all deltas to play back the database from scratch. I suppose with patchsets it could sort them all by start time and apply them one after the other to recreate the database. It’s an interesting problem because you can’t assume to have synced recently, so you don’t know the baseline you are starting with, but for the application I’m building, that may not matter.

treekogreen 6y ago

This sounds like a cool project. Considering its a journalling app and I'm assuming the user just enters plain text, have you experimented with compression and decompression before and after the SQLite storage?

allenu 6y ago
It's actually a flash card app, not a journaling one, so the text entered is quite short. I haven't played around with the compression idea, but it sounds like it could be useful for large text entries.