sqlite/man/sqlite3session_changeset.3

.Dd December 19, 2018
.Dt SQLITE3SESSION_CHANGESET 3
.Os
.Sh NAME
.Nm sqlite3session_changeset
.Nd Generate A Changeset From A Session Object
.Sh SYNOPSIS
.Ft int
.Fo sqlite3session_changeset
.Fa "sqlite3_session *pSession"
.Fa "int *pnChangeset"
.Fa "void **ppChangeset              "
.Fc
.Sh DESCRIPTION
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.
.Pp
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.
.Pp
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.
.Pp
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.
.Pp
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.
.Pp
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().
.Ss 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.
.Pp
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.
.Pp
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).
.Pp
When this function is called, the requested changeset is created using
both the accumulated records and the current contents of the database
file.
Specifically:
.Bl -bullet
.It
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.
.It
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.
.El
.Pp
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.
.Pp
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.
.Sh SEE ALSO
.Xr sqlite3_malloc 3 ,
.Xr sqlite3changeset_apply 3 ,
.Xr sqlite3changeset_start 3 ,
.Xr sqlite3session_enable 3