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 - Rebaseryou 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 inputor- block outputcallback.
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 - Sessionwith the relevant database. You can have multiple on the same database.
- Use - Session.attach()to determine which tables to record
- You can use - Session.enabledto 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:
To manipulate changesets:
- Use - ChangesetBuilder
- You can add multiple changesets together 
- You can add - individual changesfrom- Changeset.iter()or from your conflict handler in- Changeset.apply()
- Use - Rebaserto 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.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 - 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.
- Session.changeset_stream(output: SessionStreamOutput) None
- Produces a changeset of the session so far in a stream 
- 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 - tablein the attached database- from_schemato 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 
TableChange class
- class apsw.TableChange
- Represents a changed row. They come from - changeset iterationand 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 - InvalidContextErrorif 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.conflict: tuple[SQLiteValue, ...] | None
- Noneif 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 - Noneif not in a conflict handler.
- TableChange.indirect: bool
- Trueif this is an indirect change - for example made by triggers or foreign keys.
- TableChange.new: tuple[SQLiteValue | no_change, ...] | None
- Noneif not applicable (like a DELETE). Otherwise a tuple of the new values for the row, with- apsw.no_changeif no value was provided for that column.- Calls: sqlite3changeset_new 
- TableChange.old: tuple[SQLiteValue | no_change, ...] | None
- Noneif not applicable (like an INSERT). Otherwise a tuple of the old values for the row before this change, with- apsw.no_changeif no value was provided for that column,- Calls: sqlite3changeset_old 
- TableChange.op: str
- The operation code as a string - INSERT,- DELETE, or- UPDATE. See- opcodefor this as a number.
- TableChange.opcode: int
- The operation code - - apsw.SQLITE_INSERT,- apsw.SQLITE_DELETE, or- apsw.SQLITE_UPDATE. See- opfor 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- SessionStreamInputwhich 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 - Truethen return- rebaseinformation, else- None.
 
 - Filter- Callback called with a table name, once per table that has a change. It should return - Trueif changes to that table should be applied, or- Falseto 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 - TableChangeas the second. Possible conflicts are described here.- It should return the action to take. - If not supplied or on error, - SQLITE_CHANGESET_ABORTis returned.- See the example. 
- 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 - v2API is used (marked as experimental)
ChangesetBuilder class
- class apsw.ChangesetBuilder
- This object wraps a sqlite3_changegroup letting you concatenate changesets and individual - TableChangeinto 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 
- ChangesetBuilder.add_change(change: TableChange) None
- Parameters:
- change – An individual change to add. 
 - You can obtain - TableChangefrom- Changeset.iter()or from the conflict callback of- Changeset.apply().
- 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 
- 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 - MisuseErrorif 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