Make `transaction` error handling more flexible

[anqit]

It would be nice if context.transaction didn't require the passed in ZIO to have a Throwable as the error type, and could allow and rollback on any error type. Not sure if there are technical limitations preventing this, but the flexibility would be nice and allow clearer code in cases where we would like to rollback a transaction due to a logical error rather than solely exceptional behavior. One case that has come up for me is when expecting exactly one row to be affected by some query.

Expected behavior

inline def deleteByIdQuery(id: Id): Quoted[Delete[SomeEntity]] = // ... query that returns count of affected rows, but only 1 or 0 are logically valid values

// attempting to delete some entity by Id, and ensure at most one record is deleted
def deleteById(id: Id): ZIO[DataSource, CustomError, Boolean] =
    // transaction {     <== would like to be able to pass this to transaction as is, and have the rollback occur if the ZIO is any type of failure
        run(deleteByIdQuery(id)).flatMap:
            case 1 => ZIO.succeed(true)
            case 0 => ZIO.succeed(false)
            case _ => ZIO.fail(CustomError("bad delete")) // <== this is not a throwable, but still represents a condition we would like to rollback the transaction on
    // }

Actual behavior

inline def deleteByIdQuery(id: Id): Quoted[Delete[SomeEntity]] = // ... query that returns count of affected rows, but only 1 or 0 are logically valid values

// attempting to delete some entity by Id, and ensure at most one record is deleted
def deleteById(id: Id): ZIO[DataSource, CustomError, Boolean] =
    transaction {
        run(deleteByIdQuery(id)).map:
            case 1 => true
            case 0 => false
            case _ => throw SomeException("bad delete") // <= need to throw some intermediate exception here
    }.mapError(mapSomeExceptionToCustomError) // <= and then map the exception to the desired error type

@getquill/maintainers