The SQLite session extension provides a mechanism for recording changes to some or all of the tables in an SQLite database, and packaging those changes into a binary “changeset” or “patchset” file that you can later use to apply to another database with the same schema and compatible starting data.

Patchset is a compact version of a changeset (for UPDATE and DELETE statements, it stores only values of the PRIMARY KEY of the affected rows), however, it is possible to invert a changeset and the use it to undo a session.

Patchsets and changesets are stored in a binary format, and therefore they are more compact than files with SQL statements.

Note: to use the session extension, your SQLite needs to be compiled with -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK flags.

To create a changeset or patchset,

  1. Open the “new” database.
  2. ATTACH the “old” database.
  3. Create a session with sqlite3session_create() on the “new” database.
  4. Attach all needed tables to the session with sqlite3session_attach().
  5. For every needed table, call sqlite3session_diff() on the “old” database.
  6. Capture the changeset (sqlite3session_changeset()) or patchset (sqlite3session_patchset()) and save it.
  7. Free the buffer (sqlite3_free()), delete the session (sqlite3session_delete()), close the database.

Applying a changeset or patchset to a database is easier: usually all you need is to read it from a file and call sqlite3changeset_apply():

Changesets or patchsets are a convenient way to distribute updates to databases, as they are usually much smaller than the database itself.

There is one caveat, though: if you are using sqleet and encrypt your database, changesets and patchsets are not encrypted. I did not have a chance to test the official SQLite Encryption Extension but I think it does not encrypt changesets either.

SQL Diff Using SQLite Session API
Tagged on:         

Leave a Reply

Your email address will not be published. Required fields are marked *