SQL Diff Using SQLite Session API

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.

sqlite3* db;
sqlite3_session* session;
int rc;
int size;
void* buf;
FILE* f;

rc = sqlite3_open("new.sqlite", &db);
assert(!rc);

rc = sqlite3_exec(db, "ATTACH 'old.sqlite' AS old", NULL, 0, NULL);
assert(!rc);

rc = sqlite3session_create(db, "main", &session);
assert(!rc);

rc = sqlite3session_attach(session, "some_table");
assert(!rc);

rc = sqlite3session_diff(session, "old", "some_table", NULL);
assert(!rc);

/* Create a patchset */
rc = sqlite3session_patchset(session, &size, &buf);
assert(!rc);
f = fopen("patchset.bin", "w");
assert(f);
fwrite(buf, size, 1, f);
fclose(f);
sqlite3_free(buf);

/* Create a changeset */
rc = sqlite3session_changeset(session, &size, &buf);
assert(!rc);
f = fopen("changeset.bin", "w");
assert(f);
fwrite(buf, size, 1, f);
fclose(f);
sqlite3_free(buf);

sqlite3session_delete(session);
sqlite3_close(db);

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():

static int conflict_callback(void* ctx, int conflict, sqlite3_changset_iter* iterator)
{
    return SQLITE_CHANGESET_REPLACE;
}

sqlite3_db* db;
void* buf; /* Patchset or changeset */
int size;  /* And its size */

int rc = sqlite3changeset_apply(db, size, buf, NULL, conflict_callback, NULL);

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.