{"action":"created","issue":{"url":"https://api.github.com/repos/groue/GRDB.swift/issues/61","repository_url":"https://api.github.com/repos/groue/GRDB.swift","labels_url":"https://api.github.com/repos/groue/GRDB.swift/issues/61/labels{/name}","comments_url":"https://api.github.com/repos/groue/GRDB.swift/issues/61/comments","events_url":"https://api.github.com/repos/groue/GRDB.swift/issues/61/events","html_url":"https://github.com/groue/GRDB.swift/issues/61","id":157254168,"number":61,"title":"Savepoint support","user":{"login":"swiftlyfalling","id":18540905,"avatar_url":"https://avatars.githubusercontent.com/u/18540905?v=3","gravatar_id":"","url":"https://api.github.com/users/swiftlyfalling","html_url":"https://github.com/swiftlyfalling","followers_url":"https://api.github.com/users/swiftlyfalling/followers","following_url":"https://api.github.com/users/swiftlyfalling/following{/other_user}","gists_url":"https://api.github.com/users/swiftlyfalling/gists{/gist_id}","starred_url":"https://api.github.com/users/swiftlyfalling/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/swiftlyfalling/subscriptions","organizations_url":"https://api.github.com/users/swiftlyfalling/orgs","repos_url":"https://api.github.com/users/swiftlyfalling/repos","events_url":"https://api.github.com/users/swiftlyfalling/events{/privacy}","received_events_url":"https://api.github.com/users/swiftlyfalling/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/groue/GRDB.swift/labels/enhancement","name":"enhancement","color":"84b6eb"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2016-05-27T17:24:27Z","updated_at":"2016-05-28T08:26:01Z","closed_at":null,"body":"What are your thoughts on adding support for [SAVEPOINT](https://www.sqlite.org/lang_savepoint.html) within Transactions?\r\n\r\nPerhaps something like:\r\n\r\n```\r\ntry dbPool.writeInTransaction { db in\r\n    try db.inSavepoint(name: \"Savepoint1\") { \r\n        if let poi = PointOfInterest.fetchOne(db, key: 1) {\r\n            try poi.delete(db) //\r\n        }\r\n        let wine = Wine(color: .Red, name: \"Pomerol\")\r\n        try wine.insert(db)    // need to rollback the delete above if this fails\r\n        return .Commit         // (or .Release?)\r\n    }\r\n    // other SavePoints, etc...\r\n    return .Commit\r\n}\r\n```"},"comment":{"url":"https://api.github.com/repos/groue/GRDB.swift/issues/comments/222297002","html_url":"https://github.com/groue/GRDB.swift/issues/61#issuecomment-222297002","issue_url":"https://api.github.com/repos/groue/GRDB.swift/issues/61","id":222297002,"user":{"login":"groue","id":54219,"avatar_url":"https://avatars.githubusercontent.com/u/54219?v=3","gravatar_id":"","url":"https://api.github.com/users/groue","html_url":"https://github.com/groue","followers_url":"https://api.github.com/users/groue/followers","following_url":"https://api.github.com/users/groue/following{/other_user}","gists_url":"https://api.github.com/users/groue/gists{/gist_id}","starred_url":"https://api.github.com/users/groue/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/groue/subscriptions","organizations_url":"https://api.github.com/users/groue/orgs","repos_url":"https://api.github.com/users/groue/repos","events_url":"https://api.github.com/users/groue/events{/privacy}","received_events_url":"https://api.github.com/users/groue/received_events","type":"User","site_admin":false},"created_at":"2016-05-28T08:26:01Z","updated_at":"2016-05-28T08:26:01Z","body":"Hi again, @swiftlyfalling\r\n\r\nThanks for the suggestion. Consider it on the TODO list.\r\n\r\nI like your documented code snippet which makes things clear. And particularly the automatic rollback of savepoints on error: that's handy.\r\n\r\nI 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.\r\n\r\nUntil then, you can use savepoints with raw SQL:\r\n\r\n```swift\r\ntry db.execute(\"SAVEPOINT foo\")\r\n```\r\n\r\n> 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?\r\n\r\nWell spotted!\r\n\r\nNo real surprise here: transaction hooks are for transactions that write on disk, when savepoints can be seen as \"marks\" in transactions' timelines.\r\n\r\n> 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.)\r\n\r\nThat's so true.\r\n\r\nWe could:\r\n\r\n1. 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.\r\n\r\n    Plus side: transaction observers are kept simple.\r\n\r\n    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.\r\n    \r\n2. Allow transaction observers to keep their own stack of savepoints by notifying them of SAVEPOINT and ROLLBACK TO commands.\r\n\r\n    Plus side: transaction observers are empowered, and remain notified of events in \"real time\".\r\n\r\n    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.\r\n\r\n"}}
{"actor":{"display_login":"groue"}}