> 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
