I found that mostly easy when you have the complete changelog available, and hard otherwise.

If your database structure writes a log or a change history for its rows, you can retrieve that history and replay it to the client. If you want to look at a real database, consider parsing the write ahead logs or replication logs, as these usually replay all the actions on rows.

There are specialized databases that basically store these logs together with the row data. I believe "Event Sourcing" is the buzzword for that. They seem to be separate from actual SQL databases though.

If you need to create the diff from thin air, it's usually not hard to generate the diff if you can find something close enough to the client state (daily backup) and the current state.

In short, I'm unaware of a premade solution to this.

If your database structure writes a log or a change history for its rows, you can retrieve that history and replay it to the client.

Thanks for this post. There will almost certainly be some sort of database on the server for keeping the logs of the changes (it may or may not be an RDBMS). At the moment I don't have a need for a DB on the client side. The whole thing does lend itself to being described as "replication" so that's also worth searching the archives for.

In short, I'm unaware of a premade solution to this.

Even a negative is a useful answer - it's shows I'm not missing the blindingly obvious. Thank you.

Even a negative is a useful answer - it's shows I'm not missing the blindingly obvious. Thank you.

Careful. Even Corion might be missing the "blindigly obvious". Non-existence of something cannot be proved ;-)

Of course this remark is far from being as helpful as I would like it to be. Meh.

perl -le'print map{pack c,($-++?1:13)+ord}split//,ESEL'

I don't do much web programming at all, but when I have, I really like creating REST interfaces with Dancer2. It's exceptionally easy to get started with their documentation, and build from there.

You create one app that crunches your back-end data, and serves it up within normal subroutines, but those calls are available as HTTP URLs.

Then, you can use pretty well any language to access the data over the web. Most all languages have a REST client API. In Perl and Python it's trivial, as is with C#, C++ etc. You can even use plain HTML/jQuery if you so chose.

The learning curve is a bit high, but once you are familiar with the basics and start playing around with some of the more popular plugins, you'll be glad you went down the road because now your application/data is available to any and pretty well all consumer types.

An application which I built (which still isn't completely finished) that may serve as an initial example is App::RPi::EnvUI. It's a REST API for a grow room environment control. All client code is in the public/js files. The main module is the front end, which uses a separate internal API module that actually manages data and does the real work.

I'm perfectly happy with REST and have several APIs for other things already in production using REST::Application and REST::Client. So, if there isn't already something on CPAN to do this that is very likely to be the way I would go.

The question was more intended to be that the general problem (irrespective of JSON or REST or whatever) of requesting a list of records since some timestamp, ordered by timestamp, has probably already been solved. So let's just use that instead of re-inventing the wheel.

Of course, if it turns out that there isn't an implementation on CPAN I might as well publish the one I write.

While typing this, one thought has occurred which is that there might be an RSS implementation which could be made to do this. I will have another dig.

• https://www.viget.com/articles/backup-your-database-in-git
• https://ongardie.net/blog/sqlite-in-git/

Disclaimer: I didn't use any of these, just have them on my "explore later" list.

I had considered the possibility of using git (or at least some VCS) fetching the changes, extracting the diff, processing that and then merging. It's a possibility for sure but the work involved might be even more than rolling our own RESTful updater. Thanks for the thought.

We looked at sqitch for another project a while back. I think it can do data changes OK but it is seriously overkill for this particular task (and the dependency tree is on the heavy side too).

I seem to remember that we used rsync for this some time in the Jurassic. Worked well with MySQL ISAM tables IIRC.



The way forward always starts with a minimal test.