Generate A Changeset From A Session Object

int sqlite3session_changeset(
  sqlite3_session *pSession,      /* Session object */
  int *pnChangeset,               /* OUT: Size of buffer at *ppChangeset */
  void **ppChangeset              /* OUT: Buffer containing changeset */
);

Obtain a changeset containing changes to the tables attached to the session object passed as the first argument. If successful, set *ppChangeset to point to a buffer containing the changeset and *pnChangeset to the size of the changeset in bytes before returning SQLITE_OK. If an error occurs, set both *ppChangeset and *pnChangeset to zero and return an SQLite error code.

A changeset consists of zero or more INSERT, UPDATE and/or DELETE changes, each representing a change to a single row of an attached table. An INSERT change contains the values of each field of a new database row. A DELETE contains the original values of each field of a deleted database row. An UPDATE change contains the original values of each field of an updated database row along with the updated values for each updated non-primary-key column. It is not possible for an UPDATE change to represent a change that modifies the values of primary key columns. If such a change is made, it is represented in a changeset as a DELETE followed by an INSERT.

Changes are not recorded for rows that have NULL values stored in one or more of their PRIMARY KEY columns. If such a row is inserted or deleted, no corresponding change is present in the changesets returned by this function. If an existing row with one or more NULL values stored in PRIMARY KEY columns is updated so that all PRIMARY KEY columns are non-NULL, only an INSERT is appears in the changeset. Similarly, if an existing row with non-NULL PRIMARY KEY values is updated so that one or more of its PRIMARY KEY columns are set to NULL, the resulting changeset contains a DELETE change only.

The contents of a changeset may be traversed using an iterator created using the sqlite3changeset_start() API. A changeset may be applied to a database with a compatible schema using the sqlite3changeset_apply() API.

Within a changeset generated by this function, all changes related to a single table are grouped together. In other words, when iterating through a changeset or when applying a changeset to a database, all changes related to a single table are processed before moving on to the next table. Tables are sorted in the same order in which they were attached (or auto-attached) to the sqlite3_session object. The order in which the changes related to a single table are stored is undefined.

Following a successful call to this function, it is the responsibility of the caller to eventually free the buffer that *ppChangeset points to using sqlite3_free().

Changeset Generation

Once a table has been attached to a session object, the session object records the primary key values of all new rows inserted into the table. It also records the original primary key and other column values of any deleted or updated rows. For each unique primary key value, data is only recorded once - the first time a row with said primary key is inserted, updated or deleted in the lifetime of the session.

There is one exception to the previous paragraph: when a row is inserted, updated or deleted, if one or more of its primary key columns contain a NULL value, no record of the change is made.

The session object therefore accumulates two types of records - those that consist of primary key values only (created when the user inserts a new record) and those that consist of the primary key values and the original values of other table columns (created when the users deletes or updates a record).

When this function is called, the requested changeset is created using both the accumulated records and the current contents of the database file. Specifically:

• For each record generated by an insert, the database is queried for a row with a matching primary key. If one is found, an INSERT change is added to the changeset. If no such row is found, no change is added to the changeset.

• For each record generated by an update or delete, the database is queried for a row with a matching primary key. If such a row is found and one or more of the non-primary key fields have been modified from their original values, an UPDATE change is added to the changeset. Or, if no such row is found in the table, a DELETE change is added to the changeset. If there is a row with a matching primary key in the database, but all fields contain their original values, no change is added to the changeset.

This means, amongst other things, that if a row is inserted and then later deleted while a session object is active, neither the insert nor the delete will be present in the changeset. Or if a row is deleted and then later a row with the same primary key values inserted while a session object is active, the resulting changeset will contain an UPDATE change instead of a DELETE and an INSERT.

When a session object is disabled (see the sqlite3session_enable() API), it does not accumulate records when rows are inserted, updated or deleted. This may appear to have some counter-intuitive effects if a single row is written to more than once during a session. For example, if a row is inserted while a session object is enabled, then later deleted while the same session object is disabled, no INSERT record will appear in the changeset, even though the delete took place while the session was disabled. Or, if one field of a row is updated while a session is disabled, and another field of the same row is updated while the session is enabled, the resulting changeset will contain an UPDATE change that updates both fields.

See also lists of Objects, Constants, and Functions.