And would you allow a transaction that had > 1 named select and no modification statements, but commit if 1=1 ?
> On Jun 4, 2022, at 2:45 PM, Jeff Jirsa <jji...@gmail.com> wrote: > > > >> On Jun 3, 2022, at 8:39 AM, Blake Eggleston <beggles...@apple.com> wrote: >> >> Hi dev@, > > First, I’m ridiculously excited to see this. > >> >> 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. > > Is there a new keyword for “partition (not) exists” or is it inferred by the > select? > >> >> 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). >> > > The returned result set is after the updates are applied? > > >> Let me know what you think! >> >> Blake
