public async Task ExecuteSqlAsync_RequestTransactionIsPopulatedWhenPresent()
{
var pool = new FakeSessionPool();
var transactionId = ByteString.CopyFromUtf8("transaction");
var mode = TransactionOptions.ModeOneofCase.ReadWrite;
var pooledSession = PooledSession.FromSessionName(pool, s_sampleSessionName);
var sessionWithTransaction = pooledSession.WithTransaction(transactionId, mode);
// Make a successful request
var request = new ExecuteSqlRequest();
pool.Mock.Setup(client => client.ExecuteSqlAsync(request, It.IsAny <CallSettings>()))
.ReturnsAsync(new ResultSet())
.Verifiable();
await sessionWithTransaction.ExecuteSqlAsync(request, null);
// The call modifies the request. (We can't easily check that it was modified before the RPC)
Assert.Equal(s_sampleSessionName, request.SessionAsSessionName);
Assert.Equal(transactionId, request.Transaction.Id);
pool.Mock.Verify();
}
public Task <long> ExecuteDmlAsync(ExecuteSqlRequest request, CancellationToken cancellationToken, int timeoutSeconds)
{
return(ExecuteHelper.WithErrorTranslationAndProfiling(Impl, "EphemeralTransaction.ExecuteDmlAsync", _connection.Logger));
async Task <long> Impl()
{
using (var transaction = await _connection.BeginTransactionImplAsync(_transactionOptions, TransactionMode.ReadWrite, cancellationToken).ConfigureAwait(false))
{
transaction.CommitTimeout = timeoutSeconds;
transaction.CommitPriority = _commitPriority;
while (true)
{
try
{
long count = await((ISpannerTransaction)transaction)
.ExecuteDmlAsync(request, cancellationToken, timeoutSeconds)
.ConfigureAwait(false);
// This is somewhat ugly. PDML commits as it goes, so we don't need to, whereas non-partitioned
// DML needs the commit afterwards to finish up.
if (_transactionOptions.ModeCase != TransactionOptions.ModeOneofCase.PartitionedDml)
{
await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
}
return(count);
}
catch (SpannerException e) when(
_transactionOptions.ModeCase == TransactionOptions.ModeOneofCase.PartitionedDml &&
e.ErrorCode == ErrorCode.Internal &&
e.Message.Contains("Received unexpected EOS on DATA frame from server"))
{
// Retry with the same transaction. Since this error happens in long-lived
// transactions (>= 30 mins), it's unnecessary to do exponential backoff.
continue;
}
}
}
}
}
public async Task ExecuteSqlAsync_RequestTransactionIsLeftAlonePopulatedWhenPresent()
{
var pool = new FakeSessionPool();
var pooledSession = PooledSession.FromSessionName(pool, s_sampleSessionName);
// Make a successful request
var request = new ExecuteSqlRequest {
Transaction = new TransactionSelector {
Begin = new TransactionOptions {
ReadOnly = new TransactionOptions.Types.ReadOnly()
}
}
};
pool.Mock.Setup(client => client.ExecuteSqlAsync(request, It.IsAny <CallSettings>())).ReturnsAsync(new ResultSet());
await pooledSession.ExecuteSqlAsync(request, 5, CancellationToken.None);
// The call modifies the request's session, but not transaction.
Assert.Equal(s_sampleSessionName, request.SessionAsSessionName);
Assert.Equal(TransactionSelector.SelectorOneofCase.Begin, request.Transaction.SelectorCase);
Assert.Equal(new TransactionOptions.Types.ReadOnly(), request.Transaction.Begin.ReadOnly);
}
public async Task <long> CustomTimeoutsAndRetriesAsync(string projectId, string instanceId, string databaseId)
{
// Create a SessionPool.
SpannerClient client = SpannerClient.Create();
SessionPool sessionPool = new SessionPool(client, new SessionPoolOptions());
// Acquire a session with a read-write transaction to run a query.
DatabaseName databaseName =
DatabaseName.FromProjectInstanceDatabase(projectId, instanceId, databaseId);
TransactionOptions transactionOptions = new TransactionOptions
{
ReadWrite = new ReadWrite()
};
using PooledSession session = await sessionPool.AcquireSessionAsync(
databaseName, transactionOptions, CancellationToken.None);
ExecuteSqlRequest request = new ExecuteSqlRequest
{
Sql = "INSERT Singers (SingerId, FirstName, LastName) VALUES (20, 'George', 'Washington')"
};
// Prepare the call settings with custom timeout and retry settings.
CallSettings settings = CallSettings
.FromExpiration(Expiration.FromTimeout(TimeSpan.FromSeconds(60)))
.WithRetry(RetrySettings.FromExponentialBackoff(
maxAttempts: 12,
initialBackoff: TimeSpan.FromMilliseconds(500),
maxBackoff: TimeSpan.FromMilliseconds(6400),
backoffMultiplier: 1.5,
retryFilter: RetrySettings.FilterForStatusCodes(
new StatusCode[] { StatusCode.Unavailable, StatusCode.DeadlineExceeded })));
ResultSet result = await session.ExecuteSqlAsync(request, settings);
await session.CommitAsync(new CommitRequest(), null);
return(result.Stats.RowCountExact);
}
public Task <long> ExecuteDmlAsync(ExecuteSqlRequest request, CancellationToken cancellationToken, int timeoutSeconds)
{
return(ExecuteHelper.WithErrorTranslationAndProfiling(Impl, "EphemeralTransaction.ExecuteDmlAsync", _connection.Logger));
async Task <long> Impl()
{
using (var transaction = await _connection.BeginTransactionImplAsync(_transactionOptions, TransactionMode.ReadWrite, cancellationToken).ConfigureAwait(false))
{
transaction.CommitTimeout = timeoutSeconds;
long count = await((ISpannerTransaction)transaction)
.ExecuteDmlAsync(request, cancellationToken, timeoutSeconds)
.ConfigureAwait(false);
// This is somewhat ugly. PDML commits as it goes, so we don't need to, whereas non-partitioned
// DML needs the commit afterwards to finish up.
if (_transactionOptions.ModeCase != TransactionOptions.ModeOneofCase.PartitionedDml)
{
await transaction.CommitAsync(cancellationToken).ConfigureAwait(false);
}
return(count);
}
}
}
public Task <ReliableStreamReader> ExecuteQueryAsync(
ExecuteSqlRequest request,
CancellationToken cancellationToken)
{
return(ExecuteHelper.WithErrorTranslationAndProfiling(
async() =>
{
GaxPreconditions.CheckNotNull(request, nameof(request));
Logger.Debug(() => "Executing a query through an ephemeral transaction.");
using (var holder = await SpannerConnection.SessionHolder
.Allocate(_connection, cancellationToken)
.ConfigureAwait(false))
{
var streamReader = _connection.SpannerClient.GetSqlStreamReader(request, holder.Session);
holder.TakeOwnership();
streamReader.StreamClosed += (o, e) => { _connection.ReleaseSession(streamReader.Session); };
return streamReader;
}
}, "EphemeralTransaction.ExecuteQuery"));
}
public void Equals_ExecuteSqlRequest()
{
var request = new ExecuteSqlRequest
{
Sql = "SELECT * FROM Foo",
Session = "sessionName",
Transaction = new TransactionSelector
{
Id = ByteString.CopyFrom("some_id", Encoding.UTF8)
}
};
var request2 = request.Clone();
var request3 = new ExecuteSqlRequest();
var partition1 = new CommandPartition(ReadOrQueryRequest.FromRequest(request));
var partition2 = new CommandPartition(ReadOrQueryRequest.FromRequest(request2));
var partition3 = new CommandPartition(ReadOrQueryRequest.FromRequest(request3));
var partition4 = new CommandPartition(ReadOrQueryRequest.FromRequest(new ReadRequest()));
Assert.Equal(partition1, partition2);
Assert.NotEqual(partition1, partition3);
Assert.NotEqual(partition1, partition4);
Assert.NotEqual(partition3, partition4);
Assert.Equal(partition1, partition1.Clone());
}
public Task <long> ExecuteDmlAsync(ExecuteSqlRequest request, CancellationToken cancellationToken, int timeoutSeconds) =>
throw new NotSupportedException("A single use transaction can only be used for read operations.");
public async Task <long> ExecuteDmlAsync(ExecuteSqlRequest request, CancellationToken cancellationToken, int timeoutSeconds)
{
ISpannerTransaction transaction = await GetTransactionAsync(cancellationToken, timeoutSeconds).ConfigureAwait(false);
return(await transaction.ExecuteDmlAsync(request, cancellationToken, timeoutSeconds).ConfigureAwait(false));
}
internal CommandPartition(ExecuteSqlRequest executeSqlRequest)
{
ExecuteSqlRequest = executeSqlRequest;
}
public async Task <ReliableStreamReader> ExecuteQueryAsync(ExecuteSqlRequest request, CancellationToken cancellationToken, int timeoutSeconds)
{
ISpannerTransaction transaction = await SpannerTransactionTask.ConfigureAwait(false);
return(await transaction.ExecuteQueryAsync(request, cancellationToken, timeoutSeconds).ConfigureAwait(false));
}
public IActionResult ExecuteResult([FromRoute] string id, [FromBody] ExecuteSqlRequest request)
{
var conn = ConnectionHelper.Connections[id];
var ret = new List <MySqlQueryResult>();
var splitedCommands = MySqlCommandSpliter.SplitCommand(request.Sql);
using (var command = new MySqlCommand())
{
command.Connection = conn;
command.CommandText = $"USE `{request.Database}`;";
try
{
command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Response.StatusCode = 400;
return(Json(new DBError
{
Code = ex.Number,
Message = ex.Message
}));
}
foreach (var x in splitedCommands)
{
var res = new MySqlQueryResult();
res.Command = x;
var analyze = MySqlCommandSpliter.AnalyzeCommand(x);
res.Table = analyze.Table;
command.CommandText = x;
var begin = DateTime.Now;
IEnumerable <MySqlTableColumn> tableColumns = null;
try
{
tableColumns = MySqlCommandSpliter.GetTableColumns(res.Table, conn).ToList();
res.Readonly = string.IsNullOrWhiteSpace(analyze.Table) || !(analyze.IsSimpleSelect && MySqlCommandSpliter.IsContainedKeys(analyze.Columns, tableColumns));
}
catch (MySqlException)
{
res.Readonly = true;
}
try
{
using (var reader = command.ExecuteReader())
{
if (res.Readonly)
{
res.Columns = GenerateColumnsFromReader(reader).ToList();
res.ColumnTypes = null;
res.Nullable = null;
res.Keys = null;
res.RowsAffected = reader.RecordsAffected;
}
else
{
res.Columns = analyze.Columns;
if (res.Columns.Count() == 1 && res.Columns.First() == "*")
{
res.Columns = tableColumns.Select(x => x.Field);
}
else
{
res.Columns = analyze.Columns;
}
res.ColumnTypes = res.Columns.Select(x => tableColumns.SingleOrDefault(y => y.Field == x)?.Type);
res.Nullable = res.Columns.Select(x => tableColumns.SingleOrDefault(y => y.Field == x)?.Null);
res.Keys = tableColumns.Where(x => x.Key == "PRI").Select(x => x.Field);
res.RowsAffected = reader.RecordsAffected;
}
if (reader.HasRows)
{
res.Rows = new List <List <string> >();
while (reader.Read())
{
var row = new List <string>();
for (var i = 0; i < reader.FieldCount; ++i)
{
if (reader.IsDBNull(i))
{
row.Add(null);
}
else if (reader.GetFieldType(i) == typeof(byte[]))
{
row.Add(Convert.ToBase64String((byte[])reader[i]));
}
else
{
row.Add(reader[i].ToString());
}
}
res.Rows.Add(row);
}
}
}
}
catch (MySqlException ex)
{
Response.StatusCode = 400;
return(Json(new DBError
{
Code = ex.Number,
Message = ex.Message
}));
}
var end = DateTime.Now;
res.TimeSpan = Convert.ToInt64((end - begin).TotalMilliseconds);
ret.Add(res);
}
}
return(Json(ret));
}
public Task <ReliableStreamReader> ExecuteQueryAsync(ExecuteSqlRequest request,
CancellationToken cancellationToken,
int timeoutSeconds) =>
throw new NotSupportedException("A partitioned update transaction can only be used for DML operations.");
