1.Dd January 24, 2024 2.Dt SQLITE3CHANGESET_APPLY 3 3.Os 4.Sh NAME 5.Nm sqlite3changeset_apply , 6.Nm sqlite3changeset_apply_v2 7.Nd apply a changeset to a database 8.Sh SYNOPSIS 9.In sqlite3.h 10.Ft int 11.Fo sqlite3changeset_apply 12.Fa "sqlite3 *db" 13.Fa "int nChangeset" 14.Fa "void *pChangeset" 15.Fa "int(*xFilter)( void *pCtx,const char *zTab)" 16.Fa "int(*xConflict)( void *pCtx,int eConflict,sqlite3_changeset_iter *p)" 17.Fa "void *pCtx" 18.Fc 19.Ft int 20.Fo sqlite3changeset_apply_v2 21.Fa "sqlite3 *db" 22.Fa "int nChangeset" 23.Fa "void *pChangeset" 24.Fa "int(*xFilter)( void *pCtx,const char *zTab)" 25.Fa "int(*xConflict)( void *pCtx,int eConflict,sqlite3_changeset_iter *p)" 26.Fa "void *pCtx" 27.Fa "void **ppRebase" 28.Fa "int *pnRebase" 29.Fa "int flags" 30.Fc 31.Sh DESCRIPTION 32Apply a changeset or patchset to a database. 33These functions attempt to update the "main" database attached to handle 34db with the changes found in the changeset passed via the second and 35third arguments. 36.Pp 37The fourth argument (xFilter) passed to these functions is the "filter 38callback". 39If it is not NULL, then for each table affected by at least one change 40in the changeset, the filter callback is invoked with the table name 41as the second argument, and a copy of the context pointer passed as 42the sixth argument as the first. 43If the "filter callback" returns zero, then no attempt is made to apply 44any changes to the table. 45Otherwise, if the return value is non-zero or the xFilter argument 46to is NULL, all changes related to the table are attempted. 47.Pp 48For each table that is not excluded by the filter callback, this function 49tests that the target database contains a compatible table. 50A table is considered compatible if all of the following are true: 51.Bl -bullet 52.It 53The table has the same name as the name recorded in the changeset, 54and 55.It 56The table has at least as many columns as recorded in the changeset, 57and 58.It 59The table has primary key columns in the same position as recorded 60in the changeset. 61.El 62.Pp 63If there is no compatible table, it is not an error, but none of the 64changes associated with the table are applied. 65A warning message is issued via the sqlite3_log() mechanism with the 66error code SQLITE_SCHEMA. 67At most one such warning is issued for each table in the changeset. 68.Pp 69For each change for which there is a compatible table, an attempt is 70made to modify the table contents according to the UPDATE, INSERT or 71DELETE change. 72If a change cannot be applied cleanly, the conflict handler function 73passed as the fifth argument to sqlite3changeset_apply() may be invoked. 74A description of exactly when the conflict handler is invoked for each 75type of change is below. 76.Pp 77Unlike the xFilter argument, xConflict may not be passed NULL. 78The results of passing anything other than a valid function pointer 79as the xConflict argument are undefined. 80.Pp 81Each time the conflict handler function is invoked, it must return 82one of SQLITE_CHANGESET_OMIT, SQLITE_CHANGESET_ABORT 83or SQLITE_CHANGESET_REPLACE. 84SQLITE_CHANGESET_REPLACE may only be returned if the second argument 85passed to the conflict handler is either SQLITE_CHANGESET_DATA or SQLITE_CHANGESET_CONFLICT. 86If the conflict-handler returns an illegal value, any changes already 87made are rolled back and the call to sqlite3changeset_apply() returns 88SQLITE_MISUSE. 89Different actions are taken by sqlite3changeset_apply() depending on 90the value returned by each invocation of the conflict-handler function. 91Refer to the documentation for the three available return values 92for details. 93.Bl -tag -width Ds 94.It DELETE Changes 95For each DELETE change, the function checks if the target database 96contains a row with the same primary key value (or values) as the original 97row values stored in the changeset. 98If it does, and the values stored in all non-primary key columns also 99match the values stored in the changeset the row is deleted from the 100target database. 101.Pp 102If a row with matching primary key values is found, but one or more 103of the non-primary key fields contains a value different from the original 104row value stored in the changeset, the conflict-handler function is 105invoked with SQLITE_CHANGESET_DATA as the second 106argument. 107If the database table has more columns than are recorded in the changeset, 108only the values of those non-primary key fields are compared against 109the current database contents - any trailing database table columns 110are ignored. 111.Pp 112If no row with matching primary key values is found in the database, 113the conflict-handler function is invoked with SQLITE_CHANGESET_NOTFOUND 114passed as the second argument. 115.Pp 116If the DELETE operation is attempted, but SQLite returns SQLITE_CONSTRAINT 117(which can only happen if a foreign key constraint is violated), the 118conflict-handler function is invoked with SQLITE_CHANGESET_CONSTRAINT 119passed as the second argument. 120This includes the case where the DELETE operation is attempted because 121an earlier call to the conflict handler function returned SQLITE_CHANGESET_REPLACE. 122.It INSERT Changes 123For each INSERT change, an attempt is made to insert the new row into 124the database. 125If the changeset row contains fewer fields than the database table, 126the trailing fields are populated with their default values. 127.Pp 128If the attempt to insert the row fails because the database already 129contains a row with the same primary key values, the conflict handler 130function is invoked with the second argument set to SQLITE_CHANGESET_CONFLICT. 131.Pp 132If the attempt to insert the row fails because of some other constraint 133violation (e.g. NOT NULL or UNIQUE), the conflict handler function 134is invoked with the second argument set to SQLITE_CHANGESET_CONSTRAINT. 135This includes the case where the INSERT operation is re-attempted because 136an earlier call to the conflict handler function returned SQLITE_CHANGESET_REPLACE. 137.It UPDATE Changes 138For each UPDATE change, the function checks if the target database 139contains a row with the same primary key value (or values) as the original 140row values stored in the changeset. 141If it does, and the values stored in all modified non-primary key columns 142also match the values stored in the changeset the row is updated within 143the target database. 144.Pp 145If a row with matching primary key values is found, but one or more 146of the modified non-primary key fields contains a value different from 147an original row value stored in the changeset, the conflict-handler 148function is invoked with SQLITE_CHANGESET_DATA 149as the second argument. 150Since UPDATE changes only contain values for non-primary key fields 151that are to be modified, only those fields need to match the original 152values to avoid the SQLITE_CHANGESET_DATA conflict-handler callback. 153.Pp 154If no row with matching primary key values is found in the database, 155the conflict-handler function is invoked with SQLITE_CHANGESET_NOTFOUND 156passed as the second argument. 157.Pp 158If the UPDATE operation is attempted, but SQLite returns SQLITE_CONSTRAINT, 159the conflict-handler function is invoked with SQLITE_CHANGESET_CONSTRAINT 160passed as the second argument. 161This includes the case where the UPDATE operation is attempted after 162an earlier call to the conflict handler function returned SQLITE_CHANGESET_REPLACE. 163.El 164.Pp 165It is safe to execute SQL statements, including those that write to 166the table that the callback related to, from within the xConflict callback. 167This can be used to further customize the application's conflict resolution 168strategy. 169.Pp 170All changes made by these functions are enclosed in a savepoint transaction. 171If any other error (aside from a constraint failure when attempting 172to write to the target database) occurs, then the savepoint transaction 173is rolled back, restoring the target database to its original state, 174and an SQLite error code returned. 175.Pp 176If the output parameters (ppRebase) and (pnRebase) are non-NULL and 177the input is a changeset (not a patchset), then sqlite3changeset_apply_v2() 178may set (*ppRebase) to point to a "rebase" that may be used with the 179sqlite3_rebaser APIs buffer before returning. 180In this case (*pnRebase) is set to the size of the buffer in bytes. 181It is the responsibility of the caller to eventually free any such 182buffer using sqlite3_free(). 183The buffer is only allocated and populated if one or more conflicts 184were encountered while applying the patchset. 185See comments surrounding the sqlite3_rebaser APIs for further details. 186.Pp 187The behavior of sqlite3changeset_apply_v2() and its streaming equivalent 188may be modified by passing a combination of supported flags 189as the 9th parameter. 190.Pp 191Note that the sqlite3changeset_apply_v2() API is still \fBexperimental\fP 192and therefore subject to change. 193.Sh IMPLEMENTATION NOTES 194These declarations were extracted from the 195interface documentation at line 12020. 196.Bd -literal 197SQLITE_API int sqlite3changeset_apply( 198 sqlite3 *db, /* Apply change to "main" db of this handle */ 199 int nChangeset, /* Size of changeset in bytes */ 200 void *pChangeset, /* Changeset blob */ 201 int(*xFilter)( 202 void *pCtx, /* Copy of sixth arg to _apply() */ 203 const char *zTab /* Table name */ 204 ), 205 int(*xConflict)( 206 void *pCtx, /* Copy of sixth arg to _apply() */ 207 int eConflict, /* DATA, MISSING, CONFLICT, CONSTRAINT */ 208 sqlite3_changeset_iter *p /* Handle describing change and conflict */ 209 ), 210 void *pCtx /* First argument passed to xConflict */ 211); 212SQLITE_API int sqlite3changeset_apply_v2( 213 sqlite3 *db, /* Apply change to "main" db of this handle */ 214 int nChangeset, /* Size of changeset in bytes */ 215 void *pChangeset, /* Changeset blob */ 216 int(*xFilter)( 217 void *pCtx, /* Copy of sixth arg to _apply() */ 218 const char *zTab /* Table name */ 219 ), 220 int(*xConflict)( 221 void *pCtx, /* Copy of sixth arg to _apply() */ 222 int eConflict, /* DATA, MISSING, CONFLICT, CONSTRAINT */ 223 sqlite3_changeset_iter *p /* Handle describing change and conflict */ 224 ), 225 void *pCtx, /* First argument passed to xConflict */ 226 void **ppRebase, int *pnRebase, /* OUT: Rebase data */ 227 int flags /* SESSION_CHANGESETAPPLY_* flags */ 228); 229.Ed 230.Sh SEE ALSO 231.Xr SQLITE_CHANGESET_DATA 3 , 232.Xr SQLITE_CHANGESET_OMIT 3 , 233.Xr SQLITE_CHANGESETAPPLY_NOSAVEPOINT 3 234