Natively support multiple DML statements

[elefeint]

Cloud Spanner has several APIs for executing different, although somewhat overlapping, types of queries:

1. ExecuteStreamingSql: supports insert/update statements in addition to the select statements (for which it is most useful).
   However, does NOT support DDL, and requires that DML statements arrive in order specified by seqno.
2. ExecuteBatchDml: supports multiple insert/update statements.
3. (out of scope for this issue) Database Admin API UpdateDatabaseDdl. This poses additional complications, as it returns a long-running operation, which then would have to be checked for status separately.

In #87, using multiple ExecuteStreamingSql calls when executing parameterized DML queries was made possible by explicitly serializing the DML queries to ensure they arrive to Spanner in order. However, waiting for the network roundtrip for each statement is not ideal.
Since we will have to detect the nature of each query no matter what implementation we choose (because Spanner has separate APIs for different query types), we should use ExecuteBatchDml to execute multiple DML queries.

[ChengyuanZhao]

Some more detail from in-person discussions:

The place where we identified where we can use this is when for a single Statement the user has bound many sets of params. It is straightforward to use the Batch DML rpc method

However, within a transaction we may have multiple Statements, and it is unclear how to group things into batches or know that there are multiple statements that need grouping (or even ordering as we do now).

[dzou]

Another approach we could consider: If we could add a setting like .setConnectionProperties(...) to the Connection class, we can make several modes in which a connection can be active:

• Read-only
• read-write
• partitioned DML
• Batch DML
• DDL

If we can ask for this feature to solve the read-only/read-write distinction (r2dbc/r2dbc-spi#98), we might be able to use that to solve this as well. Make a connection only able to run queries depending on what mode is set.

[meltsufin]

@dzou Do you mind adding a comment to this effect on r2dbc/r2dbc-spi#98?

[ChengyuanZhao]

@dzou Your idea is also to avoid parsing the SQL right? So if the setting is DDL we will use UpdateDatabaseDdl to run every single statement?

And by "partitioned DML" do you mean batch DML ?

[dzou]

@dzou Do you mind adding a comment to this effect on r2dbc/r2dbc-spi#98?

Done.

@dzou Your idea is also to avoid parsing the SQL right? So if the setting is DDL we will use UpdateDatabaseDdl to run every single statement?

And by "partitioned DML" do you mean batch DML ?

That is correct, partitioned DML == batch DML. Yup, this would be an approach where we hopefully can avoid parsing SQL.

EDIT: I was incorrect in the previous statement; batch DML is not the same as partitioned DML, they are 2 separate things.

[ChengyuanZhao]

related r2dbc/r2dbc-spi#99