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?
[ Parent
| on HN ]
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.