Hello everyone, Does anyone know of any additional documentation or good examples of the session extension usage?
I am probably using it in an odd way, but I encountered something that makes perfect sense but was surprising originally. Basically if you use the session extension and apply a changeset to a table with a column created with ON CONFLICT ROLLBACK then even if the apply succeeds properly (OMIT/REPLACE) the call will always return an error if any conflicts occur as the ON CONFLICT ROLLBACK causes all pending SAVESTATEs to be rolled back including the changeset_apply savestate, as such the changeset_apply will no longer exist when the call to RELEASE it is made. I am using sqlite via C# and a slightly modified fork of sqlite-net ( https://github.com/praeclarum/sqlite-net) along with a pinvoke wrapper [time permitting to I will see if it is wanted upstream] for the session extension. I have a table in which I set the primary key to ON CONFLICT ROLLBACK and proceed to insert rows into the table. I have a replica of the database, using the session module I create a change set of the differences between the two databases and apply [in the wrong direction] the change set which will cause a conflict for each difference and allows my conflict handler to determine if the change should be replicated (which is performed via ignoring the change to keep current, or INSERT, DELETE, or UPDATE as appropriate). I have unit tests which create a simple database and replica then proceed to make changes and replicate, which works fine (uses default ON CONFLICT ABORT, i.e. not specified). However, if the database uses the ROLLBACK mode then in the session extension within sessionChangesetApply() the "SAVEPOINT changeset_apply" will be released on the first conflict (regardless of how it is handled by the user's conflict handler) and later in the sessionChangesetApply() function the following line "rc = sqlite3_exec(db, "RELEASE changeset_apply", 0, 0, 0);" will always fail as 'changeset_apply' no longer exists and so sqlite3changeset_apply will always fail if there are any conflicts. The reason this occurs is within sqlite3VdbeHalt() if the conflict action [p->errorAction] isn't one of SQLITE_OK, OE_Fail, or OE_Abort then all open save points are closed, including the 'changeset_apply' one. SQLITE_OK || p->errorAction==OE_Fail ){ eStatementOp = SAVEPOINT_RELEASE; }else if( p->errorAction==OE_Abort ){ eStatementOp = SAVEPOINT_ROLLBACK; }else{ sqlite3RollbackAll(db, SQLITE_ABORT_ROLLBACK); sqlite3CloseSavepoints(db); <<--- The document session/sqlite3changeset_apply.html states "All changes made by this function are enclosed in a savepoint transaction. If any other error (aside from a constraint failure when attempting to write to the target database) occurs, then the savepoint transaction is rolled back, restoring the target database to its original state, and an SQLite error code returned." isn't completely accurate, as an error is also returned on constraint failure and the constraint causes automatic ROLLBACK. On reflection (and re-reading that INSERT OR ROLLBACK is basically a per statement form of [not valid] INSERT ON CONFLICT ROLLBACK), when using session extension the table schema should probably not use ON CONFLICT ROLLBACK, but if needed for a particular scenario then I believe the INSERT/UPDATE OR ROLLBACK form can be used. Hopefully, as my searches did not turn up much outside of the official documentation on the SESSION extension (probably a bit of my part using bad search criteria and its hard to search for 'session'), someone here can direct me to either better search terms or some additional documentation/usage of the session extension and maybe my explanation will help someone in the future realize why the call always fails when nothing looks wrong. Thank you, Kenneth J. Davis perditi...@gmail.com _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
