News for hackers. Stuff that matters.
Re:SQLite Release 3.25.0 adds support for window functions by mamcx on Wednesday August 15, 2018 @10:15AM (#17766146)
Almost all my main apps have a sync history. Before was using batch processing and a version field:

customer: name, ...., version = 1

update customer: name, ...., version = 2

and sync manually. Now I use a log/event sourcing like setup, where every write to the BD is also stored in a log table and sync from there. Much better!

Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Wednesday August 15, 2018 @10:29AM (#17766232)
Make your logged events invertible and get an undo/redo function for free. That's how I kill three to four birds with one stone.
Re:SQLite Release 3.25.0 adds support for window functions by mamcx on Wednesday August 15, 2018 @10:51AM (#17766437)
invertible? What is that?
Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Wednesday August 15, 2018 @10:53AM (#17766454)
Say you record an event like "name of customer X has been changed to FooCo". Instead, record "name of customer X has been changed to FooCo from Foo & Sons Co". If you want to undo it, just swap from/to (=invert it).
Re:SQLite Release 3.25.0 adds support for window functions by zbentley on Wednesday August 15, 2018 @11:58AM (#17767055)
A common pattern to implement this is an audit table: https://dba.stackexchange.com/questions/15186/what-is-an-aud...

...which is itself a special case of an audit trail or log: https://en.wikipedia.org/wiki/Audit_trail

Re:SQLite Release 3.25.0 adds support for window functions by blattimwind on Friday August 17, 2018 @05:58AM (#17781570)
In my experience the usefulness of audit tables diminishes as the table becomes more complex (more columns; composite and document columns, like arrays, json, xml), because it becomes difficult to find what exactly changed. Another problem is related rows; audit tables aren't very good to capture the context of changes. You can work around this with more complex triggers, but it quickly becomes a lot easier to do this from the application and not in the database, capturing semantics and not just data changes.

The issue with any application-based approach is of course that it can't provide total access mediation.