More convenient transaction support

[alecthomas]

Hi,

The transaction support is not that convenient IMO. It forces pre-generation of statements with no opportunity for logic inside the transaction.

That is, AFAICT, you can not perform a select inside the transaction then conditionally issue other statements based on the result. Or in fact, issue any kind of conditional statements.

It also makes decomposition of many statements into separate functions more difficult, forcing all functions to return Statements.

eg. I can not do this:

db.transaction({
   if let user: In = users.filter(age >= 18).first {
    if user.location == location {
      // update some other table
    }
  } else {
    // do some other update
  }
})

Is there any reason a more convenient form of transaction couldn't be supported?

[stephencelis]

I spent a long time vetting out transaction convenience methods, and it boils down to how Swift works and what I wanted in the balance of complexity. I wanted to leave as little room possible in the convenience methods to make a mistake. Sometimes this requires more upfront design (and can be inconvenient), but I figured there are 2 paths to take:

1. Distill statements into functions that can be invoked one at a time (and ensure that all of them run and are checked, which is currently more-or-less enforced)
2. Use the raw run/prepare API and invoke db.run("BEGIN TRANSACTION") manually.

The example you give is less common from what I've used transactions for, so I'd like to better understand the use-case and how to support it (I've generally used transactions for 1., two+-step foreign key application, and 2., cascading CHECK validation failures). I could add convenience/helper functions, but it would generally be the same as this:

db.run("BEGIN DEFERRED TRANSACTION")
if /* condition */ {
    /* run statement */
} else {
    /* run other statement */
}
db.run("COMMIT TRANSACTION") // or ROLLBACK, depending on the logic

What kind of code would be ideal for you?

[alecthomas]

The use case is synchronising data from a remote API. The returned JSON is quite complex, with some values conditionally inserting extra rows, or checking if an object already exists locally, and so on.

Using db.run() manually works though, and I can just write my own transaction wrapper function.

[stephencelis]

Everything is up for review and change. I just want to understand use cases before supporting them too early. I should write a post on how I designed the early API, but it boils down to this:

1. Swift doesn't support exceptions, so you can't use them for flow control (which Ruby/Python and many others use to implicitly COMMIT/ROLLBACK transactions).
2. The best you get with a block/closure method is enforcing that it returns true/false or .Commit/.Rollback. This is probably a good secondary API to support, and you definitely have me thinking more about it.

For me, having implicit error handling, flow control, and simple boolean logic makes code a lot more readable and maintainable, so I tend to err on the side of naïve legibility.

[stephencelis]

Let me sleep on this, but so far I'm inclined to add a simple, alternative block-based API that requires you to return true/false or .Commit/.Rollback. Preference? true/false is simple but the enum would be explicit.

This isn't advertised, but statements are already Bool-savvy. If they are successful, they are true, otherwise false. (Another thing to add to the documentation bucket list.)

[alecthomas]

I personally prefer the explicit .Commit/.Rollback.

[stephencelis]

Closed by c5881a8