Savepoint support

[swiftlyfalling]

What are your thoughts on adding support for SAVEPOINT within Transactions?

Perhaps something like:

try dbPool.writeInTransaction { db in
    try db.inSavepoint(name: "Savepoint1") { 
        if let poi = PointOfInterest.fetchOne(db, key: 1) {
            try poi.delete(db) //
        }
        let wine = Wine(color: .Red, name: "Pomerol")
        try wine.insert(db)    // need to rollback the delete above if this fails
        return .Commit         // (or .Release?)
    }
    // other SavePoints, etc...
    return .Commit
}

[swiftlyfalling]

Side-note: It doesn't look like the sqlite3_rollback_hook gets called for ROLLBACK TO SAVEPOINT savepoint-name. Do you experience the same result?

If so, this impacts any TransactionObservers in the event that savepoints are used. (They will receive databaseDidChangeWithEvent for any changes in a savepoint, but will not receive databaseDidRollback if a rollback to savepoint is executed for any reason.)

[groue]

Hi again, @swiftlyfalling

Thanks for the suggestion. Consider it on the TODO list.

I like your documented code snippet which makes things clear. And particularly the automatic rollback of savepoints on error: that's handy.

I discover savepoints, actually: I haven't used them yet. I'll have to look at the savepoints API of a couple of other libraries, like http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html.

Until then, you can use savepoints with raw SQL:

try db.execute("SAVEPOINT foo")

Side-note: It doesn't look like the sqlite3_rollback_hook gets called for ROLLBACK TO SAVEPOINT savepoint-name. Do you experience the same result?

Well spotted!

No real surprise here: transaction hooks are for transactions that write on disk, when savepoints can be seen as "marks" in transactions' timelines.

If so, this impacts any TransactionObservers in the event that savepoints are used. (They will receive databaseDidChangeWithEvent for any changes in a savepoint, but will not receive databaseDidRollback if a rollback to savepoint is executed for any reason.)

That's so true.

We could:

1. Hide this funny stuff from transaction observers. We start buffering all database events as soon as a transaction passes its first savepoint, and we maintain our own stack of savepoints in order to track the list of applied events. These applied events are eventually dispatched to observers, just before the databaseWillCommit callback.

   Plus side: transaction observers are kept simple.

   Minus side: observers are potentially notified of events long after they were performed. This could be a problem, but I'm not so sure. Events don't contain much information (rowid, tablename, and operation: insert/update/delete). DatabaseDidChangeWithEvent is not allowed to use the database. It's hard to imagine a custom side effect that has to be performed synchronously with database changes.

2. Allow transaction observers to keep their own stack of savepoints by notifying them of SAVEPOINT and ROLLBACK TO commands.

   Plus side: transaction observers are empowered, and remain notified of events in "real time".

   Minus side: that's a lot of bug opportunities for observers. The Transaction Nesting Rules documented at https://www.sqlite.org/lang_savepoint.html are clear, but not quite trivial.

[groue]

Note to myself on how to provide robust support for savepoints even when user executes raw SQL statements and does not use high-level APIs: savepoints statements can be identified by StatementCompilationObserver:

• SAVEPOINT foo has code SQLITE_SAVEPOINT, first string BEGIN, and second string foo.
• ROLLBACK TO SAVEPOINT foo has code SQLITE_SAVEPOINT, first string ROLLBACK, and second string foo.
• RELEASE SAVEPOINT foo has code SQLITE_SAVEPOINT, first string RELEASE, and second string foo.

[groue]

@swiftlyfalling: until I add savepoint APIs to GRDB, I've fixed the current state of the lib: events that are rollbacked by a savepoint are never notified to observers. See d57107c

[groue]

Sane interaction of transaction observer and savepoints has shipped in v0.69.0.

[groue]

... and Database.inSavepoint { ... } has shipped in v0.70.0.