# 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:

- Use `Changeset.iter()`

To apply a changeset:

- Use `Changeset.apply()`

To manipulate changesets:

- Use `ChangesetBuilder`

- You can add multiple changesets together

- You can add `individual changes` from `Changeset.iter()` or from your conflict handler in `Changeset.apply()`

- Use `Rebaser` to incorporate conflict resolutions into a changeset

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:

    - op – One of the sqlite3session options

    - args – Zero or more arguments as appropriate for op

  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.aclose() → None

  Close

  Async close

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

  Sync / Async

  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

  Sync / Async

  Produces a changeset of the session so far.

  Calls: sqlite3session_changeset

Session.changeset_size: int

  Value

  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

  Sync / Async

  Produces a changeset of the session so far in a stream

  Calls: sqlite3session_changeset_strm

Session.close() → None

  Close

  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

  Value

  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

  Sync / Async

  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

  Sync / Async

  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

  Sync / Async

  Get or change if this session is in indirect mode

  Calls: sqlite3session_indirect

Session.is_empty: bool

  Sync / Async

  True if no changes have been recorded.

  Calls: sqlite3session_isempty

Session.memory_used: int

  Value

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

  Calls: sqlite3session_memory_used

Session.patchset() → bytes

  Sync / Async

  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

  Sync / Async

  Produces a patchset of the session so far in a stream

  Calls: sqlite3session_patchset_strm

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

  Value

  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 `filter_change and conflict handler in apply`.

  A TableChange is only valid when your filter or conflict handler is active, or has just been provided by a changeset iterator. It goes out of scope after your filter or 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.

  When a conflict handler is `SQLITE_CHANGESET_FOREIGN_KEY` then only the `fk_conflicts` field has information, and all the rest will be `None`.

TableChange.column_count: int

  > Number of columns in the affected table

TableChange.conflict: SQLiteValues | 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 | AsyncConnection, *, filter: Callable[[str], bool] | None = None, filter_change: Callable[[TableChange], bool] | None = None, conflict: Callable[[int, TableChange], int] | None = None, flags: int = 0, rebase: bool = False) → bytes | None

  Sync / Async

  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

    - filter_change – Callback to determine if a particular change is made

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

    - flags – 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.

  ### Filter Change

  Callback called with each `TableChange`. It should return `True` if the change should be applied, or `False` to ignore it. If not supplied then all changes are applied.

  Note You can only supply either `filter` or `filter_change` but not both.

  ### 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:

    - sqlite3changeset_apply_v2

    - sqlite3changeset_apply_v2_strm

    - sqlite3changeset_apply_v3

    - sqlite3changeset_apply_v3_strm

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:

    - sqlite3changeset_start

    - sqlite3changeset_start_v2

    - sqlite3changeset_start_strm

    - sqlite3changeset_start_v2_strm

## ChangesetBuilder class

class apsw.ChangesetBuilder

  > This object wraps a sqlite3_changegroup to build a changeset or patchset. The contents can come from:

  > - Existing changesets

  > - Individual `TableChange`

  > - `add_insert()`, `add_update()`, `add_delete()`

  > See the example

  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:

    - sqlite3changegroup_add

    - sqlite3changegroup_add_strm

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.add_delete(table: str, indirect: bool, row: SQLiteValues) → None

  > Adds an `delete` change row. You must have called `schema()` first so the table can be verified.

  See also

  - `add_insert()`

  - `add_update()`

  Calls: sqlite3changegroup_change_finish

ChangesetBuilder.add_insert(table: str, indirect: bool, row: SQLiteValues) → None

  Adds an `insert` change row. You must have called `schema()` first so the table can be verified.

  See also

  - `add_delete()`

  - `add_update()`

  Calls: sqlite3changegroup_change_finish

ChangesetBuilder.add_update(table: str, indirect: bool, old: SQLiteValues, new: SQLiteValues) → None

  Adds an `update` change giving old and new rows. You must have called `schema()` first so the table can be verified.

  See sqlite3changegroup_change_finish for details on which columns of old and new you must provide values for. Where no value should be provided, use `apsw.no_change`.

  See also

  - `add_insert()`

  - `add_delete()`

  Calls: sqlite3changegroup_change_finish

ChangesetBuilder.close() → None

  Releases the builder

  Calls: sqlite3changegroup_delete

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

  Configures the changegroup.

  Calls: sqlite3changegroup_config

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 | AsyncConnection, 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.close() → None

  Releases the rebaser.

  Calls: sqlite3rebaser_delete

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