I've seen this pattern a couple of times (e.g. for broadcasting changes using Postgres's LISTEN/NOTIFY.)<p>Each time, I wonder why people are recreating the database's journal, within the database, where this meta-journal will <i>itself</i> be getting journaled.<p>Why not just consume the actual DB journal?<p>I'm not sure how that'd work for SQLite—it'd probably require a hack of some sort, since SQLite's journal is an ephemeral part of the DB file. But for more full-fledged RDBMSes, this would just be a matter of hijacking a streaming-replication-based backup system to act as a store of record for events. E.g., in the case of Postgres, you'd just have an ETL pipeline pointed at your WAL-E bucket, parsing through your WAL files either to load into a separate OLAP store, or for stateless map-reduction whenever you need to answer a question.
I solved this problem by wrapping sqldiff (included w/ sqlite src) as a custom git diff driver.<p><a href="https://github.com/cannadayr/git-sqlite" rel="nofollow">https://github.com/cannadayr/git-sqlite</a>
The sqlite sessions extension seems to be a good foundation for getting changelogs out of a sqlite database. It is sadly not too well known: <a href="https://www.sqlite.org/sessionintro.html" rel="nofollow">https://www.sqlite.org/sessionintro.html</a>
Related article: "Automatic Undo/Redo Using SQLite" <a href="https://www.sqlite.org/undoredo.html" rel="nofollow">https://www.sqlite.org/undoredo.html</a>
Neat! If you wanted to extend this more (string diffs, non-json encodings), a custom function would be the next logical step. In Python it might be:<p><pre><code> import sqlite3
def delta(*args):
ret = {}
for name, old, new in zip(args[::3], args[1::3], args[2::3]):
if old != new:
ret[name] = old
if ret:
return ret
db = sqlite3.connect(':memory:')
db.create_function('delta1', delta, 3)
db.create_function('delta2', delta, 6)
db.create_function('delta3', delta, 9)
print db.execute('select delta2("b", "20", "20", "c", 3.4, 3.6)').fetchone()
>> (u'{"c": 3.4}',)</code></pre>
ArangoDB also has a first tool to listen to changes in the database and execute actions or queries. It is limited to a single node. Question is how scalable all of these tools are <a href="https://www.arangodb.com/2017/03/arangochair-tool-listening-changes-arangodb/" rel="nofollow">https://www.arangodb.com/2017/03/arangochair-tool-listening-...</a>