Async resultset and database schema APIs

[bgrainger]

API proposal at dotnet/runtime#38028.

Note that GetSchemaTableAsync and GetColumnSchemaAsync will always run synchronously in MySqlConnector; this should be called out in the documentation.

[bgrainger]

Updated API proposal at dotnet/runtime#38028 (comment).

[roji]

@bgrainger I've just merged dotnet/runtime#39098 for async schema APIs. If your implementation is sync in any case, there's no real reason to do anything on your side - the new System.Data APIs automatically fall back to their sync counterparts. Though I'm curious why these can only be synchronous on MySQL, is there no network involved?

[bgrainger]

@roji When a query is executed, MySQL Server sends the column metadata (for each column in the result set) as the initial response, so this information is always available by the time GetColumnSchemaAsync is invoked.

[roji]

@bgrainger what about DbConnection.GetSchemaTable, which is supposed to provide the list of tables/columns/views/etc?

For the DbDataReader, the standard seems to be to expose the minimal information that's provided by the server, but if CommandBehavior.KeyInfo is specified on the command, to possibly go back to the database and retrieve any extra information via another query. No idea whether that makes sense for MySQL or not...

[roji]

At least for Npgsql, PostgreSQL doesn't send a lot of information with standard resultsets, so I use KeyInfo is an indicator that the user wants me to query additional info from the database. I can provide more details (the relevant file is DbColumnSchemaGenerator).

[bgrainger]

@roji I was speaking specifically about (MySqlDataReader).GetSchemaTableAsync (which will be sync); MySqlConnection.GetSchemaAsync does (for many schemas) perform DB I/O and will execute asynchronously.

[roji]

Oh I see, thanks for clarifying. Does it make sense to open a separate issue to track implementing DbConnection.GetSchemaTable, or should this one be used (having "database schema" in the title)?

[bgrainger]

I conflated them into this issue. The (async) MySqlConnection.GetSchemaAsync implementation is here:

MySqlConnector/src/MySqlConnector/MySqlConnection.cs

Lines 537 to 562 in bb39cd8

	/// <summary>
	/// Asynchronously returns schema information for the data source of this <see cref="MySqlConnection"/>.
	/// </summary>
	/// <param name="cancellationToken">A token to cancel the asynchronous operation.</param>
	/// <returns>A <see cref="Task{DataTable}"/> containing schema information.</returns>
	/// <remarks>The proposed ADO.NET API that this is based on is not finalized; this API may change in the future.</remarks>
	public Task<DataTable> GetSchemaAsync(CancellationToken cancellationToken = default) => GetSchemaProvider().GetSchemaAsync(AsyncIOBehavior, cancellationToken).AsTask();
	/// <summary>
	/// Asynchronously returns schema information for the data source of this <see cref="MySqlConnection"/>.
	/// </summary>
	/// <param name="collectionName">The name of the schema to return.</param>
	/// <param name="cancellationToken">A token to cancel the asynchronous operation.</param>
	/// <returns>A <see cref="Task{DataTable}"/> containing schema information.</returns>
	/// <remarks>The proposed ADO.NET API that this is based on is not finalized; this API may change in the future.</remarks>
	public Task<DataTable> GetSchemaAsync(string collectionName, CancellationToken cancellationToken = default) => GetSchemaProvider().GetSchemaAsync(AsyncIOBehavior, collectionName, cancellationToken).AsTask();
	/// <summary>
	/// Asynchronously returns schema information for the data source of this <see cref="MySqlConnection"/>.
	/// </summary>
	/// <param name="collectionName">The name of the schema to return.</param>
	/// <param name="restrictions">The restrictions to apply to the schema; this parameter is currently ignored.</param>
	/// <param name="cancellationToken">A token to cancel the asynchronous operation.</param>
	/// <returns>A <see cref="Task{DataTable}"/> containing schema information.</returns>
	/// <remarks>The proposed ADO.NET API that this is based on is not finalized; this API may change in the future.</remarks>
	public Task<DataTable> GetSchemaAsync(string collectionName, string?[] restrictions, CancellationToken cancellationToken = default) => GetSchemaProvider().GetSchemaAsync(AsyncIOBehavior, collectionName, cancellationToken).AsTask();

I'll probably update that code with #if NET5 ... override when an updated SDK is available.

[roji]

Sounds good.