Session extension

APSW provides access to all session functionality (including experimental). See the Session Example/Tour.

The session extension allows recording changes to a database, and later replaying them on another database, or undoing them. This allows offline syncing, as well as collaboration. It is also useful for debugging, development, and testing. Note that it records the added, modified, and deleted row values - it does not record or replay the queries that resulted in those changes.

  • You can choose which tables have changes recorded (or all), and pause / resume recording at any time

  • The recorded change set includes the row values before and after a change. This allows comprehensive conflict detection, and inverting (undoing the change), Optionally you can use patch sets (a subset of change sets) which do not have the before values, consuming less space but have less ability to detect conflicts, or be inverted.

  • The recorded changes includes indirect changes made such as by triggers and foreign keys.

  • When applying changes you can supply a conflict handler to choose what happens on each conflicting row, including aborting, skipping, applying anyway, applying your own change, and can record the conflicting operation to another change set for later.

  • You are responsible for managing your schema - the extension will not create, update, or delete tables for you. When applying changesets, if a corresponding table does not already exist then those changes are ignored. This means that you do not need all tables present on all databases.

  • It is efficient only storing enough to make the semantic change. For example if multiple changes are made to the same row, then they can be accumulated into one change record, not many.

  • You can iterate over a change set to see what it contains

  • Changesets do not contain the changes in the order made

  • Using ChangesetBuilder, you can accumulate multiple change sets, and add changes from an iterator or conflict handler.

  • Using Rebaser you can merge conflict resolutions made when applying a changeset into a later changeset, so those conflict resolutions do not have to be redone on each database where they are applied.

  • Doing multi-way synchronization across multiple databases changed separately is hard. A common approach to conflicts is to use timestamps with the most recent change “winning”. Changesets do not include timestamps, and are not time ordered. You should carefully design your schema and synchronization to ensure the needed levels of data integrity, consistency, and meeting user goals up front. Adding it later is painful.

  • Most APIs produce and consume changesets as bytes (or bytes like). That limits the changeset size to 2GB - the limit is in the SQLite code and also the limit for blobs. To produce or consume larger changesets, or to not have an entire changeset in memory, there are streaming versions of most APIs where you need to provide to provide a block input or block output callback.

Important

By default Session can only record and replay changes that have an explicit primary key defined (ie PRIMARY KEY must be present in the table definition). It doesn’t matter what type or how many columns make up the primary key. This provides a stable way to identify rows for insertion, changes, and deletion.

You can use Session.config() with SQLITE_SESSION_OBJCONFIG_ROWID to enable recording of tables without an explicit primary key, but it is strongly advised to have deterministic primary keys so that changes made independently can be reconciled. The changesets will also contain wrong operations if the table has a column named _rowid_.

Availability

The session extension and APSW support for it have to be enabled at compile time for each. APSW builds from PyPI include session support.

Most platform provided SQLite are configured with session support, and APSW should end up with it too.

The methods and classes documented here are only present if session support was enabled.

Usage Overview

The session extension does not do table creation (or deletion). When applying a changeset, it will only do so if a same named table exists, with the same number of columns, and same primary key. If no such table exists, the change is silently ignored. (Tip for managing your schema)

To record changes:

  • Use a Session with the relevant database. You can have multiple on the same database.

  • Use Session.attach() to determine which tables to record

  • You can use Session.enabled to turn recording off or on (it is on by default)

  • Use Session.changeset() to get the changeset for later use.

  • If you have two databases, you can use Session.diff() to get the changes necessary to turn one into the other without having to record changes as they happen

To see what your changeset contains:

To apply a changeset:

To manipulate changesets:

Tip

The session extension rarely raises exceptions, instead just doing nothing. For example if tables don’t exist, don’t have a primary key, attached databases don’t exist, and similar scenarios where typos could happen, you won’t get an error, just no action.

Extension configuration

apsw.session_config(op: int, *args: Any) → Any
Parameters:

Calls: sqlite3session_config

Session class

class apsw.Session(db: Connection, schema: str)

This object wraps a sqlite3_session object.

Starts a new session.

Parameters:

  • connection – Which database to operate on

  • schema – main, temp, the name in ATTACH

Calls: sqlite3session_create

Session.attach(name: str | None = None) → None

Attach to a specific table, or all tables if no name is provided. The table does not need to exist at the time of the call. You can call this multiple times.

See also

table_filter()

Calls: sqlite3session_attach

Session.changeset() → bytes

Produces a changeset of the session so far.

Calls: sqlite3session_changeset

Session.changeset_size: int

Returns upper limit on changeset size, but only if Session.config() was used to enable it. Otherwise it will be zero.

Calls: sqlite3session_changeset_size

Session.changeset_stream(output: SessionStreamOutput) → None

Produces a changeset of the session so far in a stream

Calls: sqlite3session_changeset_strm

Session.close() → None

Ends the session object. APSW ensures that all Session objects are closed before the database is closed so there is no need to manually call this.

Calls: sqlite3session_delete

Session.config(op: int, *args: Any) → Any

Set or get configuration values

For example session.config(apsw.SQLITE_SESSION_OBJCONFIG_SIZE, -1) tells you if size information is enabled.

Calls: sqlite3session_object_config

Session.diff(from_schema: str, table: str) → None

Loads the changes necessary to update the named table in the attached database from_schema to match the same named table in the database this session is attached to.

See the example.

Note

You must use attach() (or use table_filter()) to attach to the table before running this method otherwise nothing is recorded.

Calls: sqlite3session_diff

Session.enabled: bool

Get or change if this session is recording changes. Disabling only stops recording rows not already part of the changeset.

Calls: sqlite3session_enable

Session.indirect: bool

Get or change if this session is in indirect mode

Calls: sqlite3session_indirect

Session.is_empty: bool

True if no changes have been recorded.

Calls: sqlite3session_isempty

Session.memory_used: int

How many bytes of memory have been used to record session changes.

Calls: sqlite3session_memory_used

Session.patchset() → bytes

Produces a patchset of the session so far. Patchsets do not include before values of changes, making them smaller, but also harder to detect conflicts.

Calls: sqlite3session_patchset

Session.patchset_stream(output: SessionStreamOutput) → None

Produces a patchset of the session so far in a stream

Calls: sqlite3session_patchset_strm

Session.table_filter(callback: Callable[[str], bool]) → None

Register a callback that says if changes to the named table should be recorded. If your callback has an exception then False is returned.

See also

attach()

Calls: sqlite3session_table_filter

TableChange class

class apsw.TableChange

Represents a changed row. They come from changeset iteration and from the conflict handler in apply.

A TableChange is only valid when your conflict handler is active, or has just been provided by a changeset iterator. It goes out of scope after your conflict handler returns, or the iterator moves to the next entry. You will get InvalidContextError if you try to access fields when out of scope. This means you can’t save TableChanges for later, and need to copy out any information you need.

TableChange.column_count: int

Number of columns in the affected table

TableChange.conflict: tuple[SQLiteValue, ...] | None

None if not applicable (not in a conflict). Otherwise a tuple of values for the conflicting row.

Calls: sqlite3changeset_conflict

TableChange.fk_conflicts: int | None

The number of known foreign key conflicts, or None if not in a conflict handler.

Calls: sqlite3changeset_fk_conflicts

TableChange.indirect: bool

True if this is an indirect change - for example made by triggers or foreign keys.

TableChange.name: str

Name of the affected table

TableChange.new: tuple[SQLiteValue | no_change, ...] | None

None if not applicable (like a DELETE). Otherwise a tuple of the new values for the row, with apsw.no_change if no value was provided for that column.

Calls: sqlite3changeset_new

TableChange.old: tuple[SQLiteValue | no_change, ...] | None

None if not applicable (like an INSERT). Otherwise a tuple of the old values for the row before this change, with apsw.no_change if no value was provided for that column,

Calls: sqlite3changeset_old

TableChange.op: str

The operation code as a string INSERT, DELETE, or UPDATE. See opcode for this as a number.

TableChange.opcode: int

The operation code - apsw.SQLITE_INSERT, apsw.SQLITE_DELETE, or apsw.SQLITE_UPDATE. See op for this as a string.

TableChange.pk_columns: set[int]

Which columns make up the primary key for this table

Calls: sqlite3changeset_pk

Changeset class

class apsw.Changeset

Provides changeset (including patchset) related methods. Note that all methods are static (belong to the class). There is no Changeset object. On input Changesets can be a collections.abc.Buffer (anything that resembles a sequence of bytes), or SessionStreamInput which provides the bytes in chunks from a callback.

Output is bytes, or SessionStreamOutput (chunks in a callback).

The streaming versions are useful when you are concerned about memory usage, or where changesets are larger than 2GB (the SQLite limit).

Changeset.apply(changeset: ChangesetInput, db: Connection, *, filter: Callable[[str], bool] | None = None, conflict: Callable[[int, TableChange], int] | None = None, flags: int = 0, rebase: bool = False) → bytes | None

Applies a changeset to a database.

Parameters:

  • source – The changeset either as the bytes, or a stream

  • db – The connection to make the change on

  • filter – Callback to determine if changes to a table are done

  • conflict – Callback to handle a change that cannot be applied

  • flags – v2 API flags.

  • rebase – If True then return rebase information, else None.

Filter

Callback called with a table name, once per table that has a change. It should return True if changes to that table should be applied, or False to ignore them. If not supplied then all tables have changes applied.

Conflict

When a change cannot be applied the conflict handler determines what to do. It is called with a conflict reason as the first parameter, and a TableChange as the second. Possible conflicts are described here.

It should return the action to take.

If not supplied or on error, SQLITE_CHANGESET_ABORT is returned.

See the example.

Calls:
Changeset.concat(A: Buffer, B: Buffer) → bytes

Returns combined changesets

Calls: sqlite3changeset_concat

Changeset.concat_stream(A: SessionStreamInput, B: SessionStreamInput, output: SessionStreamOutput) → None

Streaming concatenate two changesets

Calls: sqlite3changeset_concat_strm

Changeset.invert(changeset: Buffer) → bytes

Produces a changeset that reverses the effect of the supplied changeset.

Calls: sqlite3changeset_invert

Changeset.invert_stream(changeset: SessionStreamInput, output: SessionStreamOutput) → None

Streaming reverses the effect of the supplied changeset.

Calls: sqlite3changeset_invert_strm

Changeset.iter(changeset: ChangesetInput, *, flags: int = 0) → Iterator[TableChange]

Provides an iterator over a changeset. You can supply the changeset as the bytes, or streamed via a callable.

If flags is non-zero them the v2 API is used (marked as experimental)

Calls:

ChangesetBuilder class

class apsw.ChangesetBuilder

This object wraps a sqlite3_changegroup letting you concatenate changesets and individual TableChange into one larger changeset.

Creates a new empty builder.

Calls: sqlite3changegroup_new

ChangesetBuilder.add(changeset: ChangesetInput) → None
Parameters:

changeset – The changeset as the bytes, or a stream

Adds the changeset to the builder

Calls:
ChangesetBuilder.add_change(change: TableChange) → None
Parameters:

change – An individual change to add.

You can obtain TableChange from Changeset.iter() or from the conflict callback of Changeset.apply().

Calls: sqlite3changegroup_add_change

ChangesetBuilder.close() → None

Releases the builder

Calls: sqlite3changegroup_delete

ChangesetBuilder.output() → bytes

Produces a changeset of what was built so far

Calls: sqlite3changegroup_output

ChangesetBuilder.output_stream(output: SessionStreamOutput) → None

Produces a streaming changeset of what was built so far

Calls: sqlite3changegroup_output_strm

ChangesetBuilder.schema(db: Connection, schema: str) → None

Ensures the changesets comply with the tables in the database

Parameters:

  • db – Connection to consult

  • schema – main, temp, the name in ATTACH

You will get MisuseError if changes have already been added, or this method has already been called.

Calls: sqlite3changegroup_schema

Rebaser class

class apsw.Rebaser

This object wraps a sqlite3_rebaser object.

Starts a new rebaser.

Calls: sqlite3rebaser_create

Rebaser.configure(cr: Buffer) → None

Tells the rebaser about conflict resolutions made in an earlier Changeset.apply().

Calls: sqlite3rebaser_configure

Rebaser.rebase(changeset: Buffer) → bytes

Produces a new changeset rebased according to configure() calls made.

Calls: sqlite3rebaser_rebase

Rebaser.rebase_stream(changeset: SessionStreamInput, output: SessionStreamOutput) → None

Produces a new changeset rebased according to configure() calls made, using streaming input and output.

Calls: sqlite3rebaser_rebase_strm