Hi dev@, I’ve been working on a draft syntax for Accord transactions and wanted to bring what I have to the dev list to solicit feedback and build consensus before moving forward with it. The proposed transaction syntax is intended to be an extended batch syntax. Basically batches with selects, and an optional condition at the end. To facilitate conditions against an arbitrary number of select statements, you can also name the statements, and reference columns in the results. To cut down on the number of operations needed, select values can also be used in updates, including some math operations. Parameterization of literals is supported the same as other statements.
Here's an example selecting a row from 2 tables, and issuing updates for each row if a condition is met: BEGIN TRANSACTION; SELECT * FROM users WHERE name='blake' AS user; SELECT * from cars WHERE model='pinto' AS car; UPDATE users SET miles_driven = user.miles_driven + 30 WHERE name='blake'; UPDATE cars SET miles_driven = car.miles_driven + 30 WHERE model='pinto'; COMMIT TRANSACTION IF car.is_running; This can be simplified by naming the updates with an AS <name> syntax. If updates are named, a corresponding read is generated behind the scenes and its values inform the update. Here's an example, the query is functionally identical to the previous query. In the case of the user update, a read is still performed behind the scenes to enable the calculation of miles_driven + 30, but doesn't need to be named since it's not referenced anywhere else. BEGIN TRANSACTION; UPDATE users SET miles_driven += 30 WHERE name='blake'; UPDATE cars SET miles_driven += 30 WHERE model='pinto' AS car; COMMIT TRANSACTION IF car.is_running; Here’s another example, performing the canonical bank transfer: BEGIN TRANSACTION; UPDATE accounts SET balance += 100 WHERE name='blake' AS blake; UPDATE accounts SET balance -= 100 WHERE name='benedict' AS benedict; COMMIT TRANSACTION IF blake EXISTS AND benedict.balance >= 100; As you can see from the examples, column values can be referenced via a dot syntax, ie: <select_name>.<column> -> select1.value. Since the read portion of the transaction is performed before evaluating conditions or applying updates, values read can be freely applied to non-primary key values in updates. Select statements used either in checking a condition or creating an update must be restricted to a single row, either by specifying the full primary key or a limit of 1. Multi-row selects are allowed, but only for returning data to the client (see below). For evaluating conditions, = & != are available for all types, <, <=, >, >= are available for numerical types, and EXISTS, NOT EXISTS can be used for partitions, rows, and values. If any column references cannot be satisfied by the result of the reads, the condition implicitly fails. This prevents having to include a bunch of exists statements. On completion, an operation would return a boolean value indicating the operation had been applied, and a result set for each named select (but not named update). We could also support an optional RETURN keyword, which would allow the user to only return specific named selects (ie: RETURN select1, select2). Let me know what you think! Blake
