protected override void PopulateTable(bool fresh)
{
using (var connection = GetConnection())
{
connection.Open();
SpannerCommand command = connection.CreateInsertOrUpdateCommand(TableName);
var keyParameter = command.Parameters.Add("K", SpannerDbType.String, IdGenerator.FromGuid());
var valueParameter = command.Parameters.Add("StringValue", SpannerDbType.String, IdGenerator.FromGuid());
// Create a lowest-bound timestamp which is definitely valid, but not
// associated with TestKey
RetryHelpers.RetryOnce(() =>
{
using (var tx = connection.BeginTransaction())
{
command.Transaction = tx;
command.ExecuteNonQuery();
tx.Commit(out var timestamp);
TimestampBeforeEntries = timestamp.Value;
}
});
// It's a pity we have to do this, but otherwise it's hard to get reliable tests.
Thread.Sleep(Staleness + TimeSpan.FromSeconds(2));
}
}
public void CommandTypeSupportsOnlyCommandText()
{
var command = new SpannerCommand();
Assert.Throws<NotSupportedException>(() => command.CommandType = CommandType.StoredProcedure);
Assert.Throws<NotSupportedException>(() => command.CommandType = CommandType.TableDirect);
command.CommandType = CommandType.Text;
Assert.Equal(CommandType.Text, command.CommandType);
}
public async Task <int> WriteWithTransactionUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
// This sample transfers 200,000 from the MarketingBudget
// field of the second Album to the first Album. Make sure to run
// the AddColumnAsyncSample and WriteDataToNewColumnAsyncSample first,
// in that order.
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
decimal transferAmount = 200000;
decimal secondBudget = 0;
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
// Create a readwrite transaction that we'll assign
// to each SpannerCommand.
using var transaction = await connection.BeginTransactionAsync();
// Create statement to select the second album's data.
var cmdLookup = connection.CreateSelectCommand("SELECT * FROM Albums WHERE SingerId = 2 AND AlbumId = 2");
cmdLookup.Transaction = transaction;
// Execute the select query.
using var reader1 = await cmdLookup.ExecuteReaderAsync();
while (await reader1.ReadAsync())
{
// Read the second album's budget.
secondBudget = reader1.GetFieldValue <decimal>("MarketingBudget");
// Confirm second Album's budget is sufficient and
// if not raise an exception. Raising an exception
// will automatically roll back the transaction.
if (secondBudget < transferAmount)
{
throw new Exception($"The second album's budget {secondBudget} is less than the amount to transfer.");
}
}
// Update second album to remove the transfer amount.
secondBudget -= transferAmount;
SpannerCommand cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = @MarketingBudget WHERE SingerId = 2 and AlbumId = 2");
cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, secondBudget);
cmd.Transaction = transaction;
var rowCount = await cmd.ExecuteNonQueryAsync();
// Update first album to add the transfer amount.
cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget + @MarketingBudgetIncrement WHERE SingerId = 1 and AlbumId = 1");
cmd.Parameters.Add("MarketingBudgetIncrement", SpannerDbType.Int64, transferAmount);
cmd.Transaction = transaction;
rowCount += await cmd.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine("Transaction complete.");
return(rowCount);
}
internal SpannerDataReaderWithChecksum(
SpannerRetriableTransaction transaction,
SpannerDataReader spannerDataReader,
SpannerCommand command)
{
Transaction = transaction;
_spannerDataReader = spannerDataReader;
_spannerCommand = (SpannerCommand)command.Clone();
}
public void UpdateRowSourceNotSupported()
{
var command = new SpannerCommand();
Assert.Throws<NotSupportedException>(() => command.UpdatedRowSource = UpdateRowSource.Both);
Assert.Throws<NotSupportedException>(() => command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord);
Assert.Throws<NotSupportedException>(() => command.UpdatedRowSource = UpdateRowSource.OutputParameters);
command.UpdatedRowSource = UpdateRowSource.None;
Assert.Equal(UpdateRowSource.None, command.UpdatedRowSource);
}
internal async Task <object> ExecuteScalarWithRetryAsync(SpannerCommand command, CancellationToken cancellationToken)
{
using var reader = await ExecuteDbDataReaderWithRetryAsync(command, cancellationToken);
if (await reader.ReadAsync(cancellationToken))
{
return(reader.GetValue(0));
}
return(null);
}
public async Task SingleUseSelect()
{
string connectionString = $"Data Source=projects/p1/instances/i1/databases/d1;Host={_fixture.Host};Port={_fixture.Port}";
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString, ChannelCredentials.Insecure);
SpannerCommand cmd = connection.CreateSelectCommand("SELECT 1");
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
Assert.Equal(1, reader.GetInt64(0));
}
}
private Task InsertRowAsync(SpannerCommand writeCommand)
{
string k = UniqueString();
_addedKeys.Add(k);
writeCommand.Parameters["K"].Value = k;
_stringValues[k] = (string)(writeCommand.Parameters["StringValue"].Value =
GetLargeString(MinDataSize, MaxDataSize));
_stringArrayValues[k] = (string[])(writeCommand.Parameters["StringArrayValue"].Value =
GetLargeStringArray());
_bytesValues[k] = (byte[])(writeCommand.Parameters["BytesValue"].Value =
GetLargeBytes(MinDataSize, MaxDataSize));
_bytesArrayValues[k] = (byte[][])(writeCommand.Parameters["BytesArrayValue"].Value = GetLargeBytesArray());
return(writeCommand.ExecuteNonQueryAsync());
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
numberOfPlayers = await cmd.ExecuteScalarAsync <long>();
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (int i = 0; i < 100; i++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "PlayerName", SpannerDbType.String }
});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
});
}
Console.WriteLine("Done inserting player records...");
}
public async Task <int> WriteUsingDmlCoreAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
await connection.OpenAsync();
SpannerCommand cmd = connection.CreateDmlCommand(
"INSERT Singers (SingerId, FirstName, LastName) VALUES "
+ "(12, 'Melissa', 'Garcia'), "
+ "(13, 'Russell', 'Morales'), "
+ "(14, 'Jacqueline', 'Long'), "
+ "(15, 'Dylan', 'Shaw')");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
return(rowCount);
}
public async Task WriteMutations()
{
string connectionString = $"Data Source=projects/p1/instances/i1/databases/d1;Host={_fixture.Host};Port={_fixture.Port}";
using (var connection = new SpannerConnection(connectionString, ChannelCredentials.Insecure))
{
SpannerCommand cmd = connection.CreateInsertOrUpdateCommand("Singers", new SpannerParameterCollection {
{ "SingerId", SpannerDbType.Int64, 1 },
{ "FirstName", SpannerDbType.String, "FirstName1" },
{ "LastName", SpannerDbType.String, "LastName1" },
});
await cmd.ExecuteNonQueryAsync();
}
IEnumerable <IMessage> requests = _fixture.SpannerMock.Requests;
CommitRequest commit = (CommitRequest)requests.Last();
Assert.Equal(Mutation.OperationOneofCase.InsertOrUpdate, commit.Mutations.First().OperationCase);
Assert.Equal("Singers", commit.Mutations.First().InsertOrUpdate.Table);
}
public async Task <int> UpdateDataAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
var rowCount = 0;
SpannerCommand cmd = connection.CreateDmlCommand(
"UPDATE Albums SET MarketingBudget = @MarketingBudget "
+ "WHERE SingerId = 1 and AlbumId = 1");
cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, 100000);
rowCount += await cmd.ExecuteNonQueryAsync();
cmd = connection.CreateDmlCommand(
"UPDATE Albums SET MarketingBudget = @MarketingBudget "
+ "WHERE SingerId = 2 and AlbumId = 2");
cmd.Parameters.Add("MarketingBudget", SpannerDbType.Int64, 500000);
rowCount += await cmd.ExecuteNonQueryAsync();
Console.WriteLine("Data Updated.");
return(rowCount);
}
protected internal override async Task <int> ExecuteNonQueryWithRetryAsync(SpannerCommand command, CancellationToken cancellationToken = default)
{
while (true)
{
command.Transaction = SpannerTransaction;
try
{
int res = await command.ExecuteNonQueryAsync(cancellationToken);
_retriableStatements.Add(new RetriableDmlStatement(command, res));
return(res);
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.Aborted)
{
await RetryAsync(e, cancellationToken);
}
catch (SpannerException e)
{
_retriableStatements.Add(new FailedDmlStatement(command, e));
throw;
}
}
}
/// <inheritdoc/>
protected internal override DbDataReader ExecuteDbDataReaderWithRetry(SpannerCommand command)
{
GaxPreconditions.CheckState(!Disposed, "This transaction has been disposed");
command.Transaction = SpannerTransaction;
return(command.ExecuteReader());
}
public void Add(SpannerCommand command) => _commands.Add(command);
/// <summary> /// Executes a <see cref="SpannerCommand"/> as a query and returns the first column of the /// first row. The entire transaction is retried if the command fails with an Aborted error. /// </summary> /// <param name="command">The command to execute. Must be a query.</param> /// <returns>The value of the first column of the first row in the query result</returns> protected internal abstract object ExecuteScalarWithRetry(SpannerCommand command);
/// <summary> /// Executes a <see cref="SpannerCommand"/> as an update statement and retries the entire /// transaction if the command fails with an Aborted error. /// </summary> /// <param name="command">The command to execute. Must be a DML or mutation command.</param> /// <param name="cancellationToken"></param> /// <returns>The number of affected rows.</returns> protected internal abstract Task <int> ExecuteNonQueryWithRetryAsync(SpannerCommand command, CancellationToken cancellationToken);
/// <summary> /// Executes a <see cref="SpannerCommand"/> as an update statement and retries the entire /// transaction if the command fails with an Aborted error. /// </summary> /// <param name="command">The command to execute. Must be a DML or mutation command.</param> /// <returns>The number of affected rows.</returns> protected internal abstract int ExecuteNonQueryWithRetry(SpannerCommand command);
/// <summary>
/// Read-only transactions cannot execute non-query statements. Calling this method will throw an <see cref="InvalidOperationException"/>.
/// </summary>
/// <exception cref="InvalidOperationException"></exception>
protected internal override Task <int> ExecuteNonQueryWithRetryAsync(SpannerCommand command, CancellationToken cancellationToken) =>
throw new InvalidOperationException("Non-query operations are not allowed on a read-only transaction");
public void CreatesSpannerParameter()
{
var command = new SpannerCommand();
Assert.IsType <SpannerParameter>(command.CreateParameter());
}
public static async Task InsertScoresAsync(
string projectId, string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
// Insert 4 score records into the Scores table for each player
// in the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
await connection.RunWithRetriableTransactionAsync(async (transaction) =>
{
Random r = new Random();
bool playerRecordsFound = false;
SpannerBatchCommand cmdBatch =
connection.CreateBatchDmlCommand();
var cmdLookup =
connection.CreateSelectCommand("SELECT * FROM Players");
using (var reader = await cmdLookup.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
playerRecordsFound = true;
for (int i = 0; i < 4; i++)
{
DateTime randomTimestamp = DateTime.Now
.AddYears(r.Next(-2, 1))
.AddMonths(r.Next(-12, 1))
.AddDays(r.Next(-10, 1))
.AddSeconds(r.Next(-60, 0))
.AddMilliseconds(r.Next(-100000, 0));
SpannerCommand cmdInsert =
connection.CreateDmlCommand(
"INSERT INTO Scores "
+ "(PlayerId, Score, Timestamp) "
+ "VALUES (@PlayerId, @Score, @Timestamp)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "Score", SpannerDbType.Int64 },
{ "Timestamp",
SpannerDbType.Timestamp }
});
cmdInsert.Parameters["PlayerId"].Value =
reader.GetFieldValue <int>("PlayerId");
cmdInsert.Parameters["Score"].Value =
r.Next(1000, 1000001);
cmdInsert.Parameters["Timestamp"].Value =
randomTimestamp.ToString("o");
cmdBatch.Add(cmdInsert);
}
}
if (!playerRecordsFound)
{
Console.WriteLine("Parameter 'scores' is invalid "
+ "since no player records currently exist. First "
+ "insert players then insert scores.");
Environment.Exit((int)ExitCode.InvalidParameter);
}
else
{
await cmdBatch.ExecuteNonQueryAsync();
Console.WriteLine(
"Done inserting score records..."
);
}
}
});
}
}
internal SpannerRetriableCommand(SpannerRetriableConnection connection, SpannerCommand spannerCommand)
{
_connection = connection;
_spannerCommand = (SpannerCommand)GaxPreconditions.CheckNotNull(spannerCommand, nameof(spannerCommand)).Clone();
}
internal async Task <SpannerDataReaderWithChecksum> ExecuteDbDataReaderWithRetryAsync(SpannerCommand command, CancellationToken cancellationToken)
{
// This method does not need a retry loop as it is not actually executing the query. Instead,
// that will be deferred until the first call to DbDataReader.Read().
command.Transaction = SpannerTransaction;
var spannerReader = await command.ExecuteReaderAsync(cancellationToken);
var checksumReader = new SpannerDataReaderWithChecksum(this, spannerReader, command);
_retriableStatements.Add(checksumReader);
return(checksumReader);
}
protected internal override DbDataReader ExecuteDbDataReaderWithRetry(SpannerCommand command) => Task.Run(() => ExecuteDbDataReaderWithRetryAsync(command, CancellationToken.None)).ResultWithUnwrappedExceptions();
internal RetriableDmlStatement(SpannerCommand command, long updateCount)
{
_command = (SpannerCommand)command.Clone();
_updateCount = updateCount;
}
public static async Task InsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
using (TransactionScope scope = new TransactionScope(
TransactionScopeAsyncFlowOption.Enabled))
{
Int64 numberOfPlayers = 0;
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
// Execute a SQL statement to get current number of records
// in the Players table to use as an incrementing value
// for each PlayerName to be inserted.
var cmd = connection.CreateSelectCommand(
@"SELECT Count(PlayerId) as PlayerCount FROM Players");
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
long parsedValue;
if (reader["PlayerCount"] != DBNull.Value)
{
bool result = Int64.TryParse(
reader.GetFieldValue <string>("PlayerCount"),
out parsedValue);
if (result)
{
numberOfPlayers = parsedValue;
}
}
}
}
// Insert 100 player records into the Players table.
SpannerBatchCommand cmdBatch = connection.CreateBatchDmlCommand();
for (var x = 1; x <= 100; x++)
{
numberOfPlayers++;
SpannerCommand cmdInsert = connection.CreateDmlCommand(
"INSERT INTO Players "
+ "(PlayerId, PlayerName) "
+ "VALUES (@PlayerId, @PlayerName)",
new SpannerParameterCollection {
{ "PlayerId", SpannerDbType.Int64 },
{ "PlayerName", SpannerDbType.String }
});
cmdInsert.Parameters["PlayerId"].Value =
Math.Abs(Guid.NewGuid().GetHashCode());
cmdInsert.Parameters["PlayerName"].Value =
$"Player {numberOfPlayers}";
cmdBatch.Add(cmdInsert);
}
await cmdBatch.ExecuteNonQueryAsync();
scope.Complete();
}
}
Console.WriteLine("Done inserting player records...");
}
public void PrepareNoop()
{
var command = new SpannerCommand();
command.Prepare();
}
/// <summary> /// Executes a <see cref="SpannerCommand"/> as a query and returns the result as a /// <see cref="DbDataReader"/> that will retry the entire transaction if the query or any /// of the results of the underlying stream of PartialResultSets returns an Aborted error. /// </summary> /// <param name="command">The command to execute. Must be a query.</param> /// <returns> /// The results of the query as a <see cref="DbDataReader"/> that will automatically retry /// the entire transaction if the result stream returns an Aborted error. /// </returns> protected internal abstract DbDataReader ExecuteDbDataReaderWithRetry(SpannerCommand command);
/// <summary> /// Executes a single command, retrying once if the first attempt is aborted. /// Only for use in "one-shot" commands (not in a transaction). public static Task <int> ExecuteNonQueryAsyncWithRetry(this SpannerCommand command) => ExecuteWithRetryAsyncImpl(() => command.ExecuteNonQueryAsync());
/// <summary>
/// Read-only transactions cannot execute non-query statements. Calling this method will throw an <see cref="InvalidOperationException"/>.
/// </summary>
/// <exception cref="InvalidOperationException"></exception>
protected internal override int ExecuteNonQueryWithRetry(SpannerCommand command) =>
throw new InvalidOperationException("Non-query operations are not allowed on a read-only transaction");