public async Task AddColumnAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
string alterStatement = "ALTER TABLE Albums ADD COLUMN MarketingBudget INT64";
using var connection = new SpannerConnection(connectionString);
var updateCmd = connection.CreateDdlCommand(alterStatement);
await updateCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the MarketingBudget column.");
}
private void AssertReadLatestValue(SpannerConnection readConnection)
{
var cmd = CreateSelectAllCommandForKey(readConnection);
using (var reader = cmd.ExecuteReader())
{
Assert.True(reader.Read());
string expected = _newestEntry.Value;
string actual = reader.GetFieldValue <string>(reader.GetOrdinal("StringValue"));
Assert.Equal(expected, actual);
}
}
public async Task DeleteVenuesTable()
{
string connectionString = $"Data Source=projects/{ProjectId}/instances/{InstanceId}/" +
$"databases/{DatabaseId}";
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
// Drop the Venues table.
using var cmd = connection.CreateDdlCommand(@"DROP TABLE Venues");
await cmd.ExecuteNonQueryAsync();
}
public void CloneWithQueryOptions()
{
var connection = new SpannerConnection("Data Source=projects/p/instances/i/databases/d");
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.QueryOptions = QueryOptions.Empty.WithOptimizerVersion("1");
var command2 = (SpannerCommand)command.Clone();
Assert.Same(command.SpannerConnection, command2.SpannerConnection);
Assert.Equal(command.CommandText, command2.CommandText);
Assert.Equal(command.QueryOptions, command2.QueryOptions);
}
public void CloneWithTags()
{
var connection = new SpannerConnection("Data Source=projects/p/instances/i/databases/d");
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Tag = "tag-1";
var command2 = (SpannerCommand)command.Clone();
Assert.Same(command.SpannerConnection, command2.SpannerConnection);
Assert.Equal(command.CommandText, command2.CommandText);
Assert.Equal(command.Tag, command2.Tag);
}
public void CloneWithPriority()
{
var connection = new SpannerConnection("Data Source=projects/p/instances/i/databases/d");
var command = connection.CreateSelectCommand("SELECT * FROM FOO");
command.Priority = Priority.Low;
var command2 = (SpannerCommand)command.Clone();
Assert.Same(command.SpannerConnection, command2.SpannerConnection);
Assert.Equal(command.CommandText, command2.CommandText);
Assert.Equal(command.Priority, command2.Priority);
}
public async Task AddNumericColumnAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
string alterStatement = "ALTER TABLE Venues ADD COLUMN Revenue NUMERIC";
using var connection = new SpannerConnection(connectionString);
using var updateCmd = connection.CreateDdlCommand(alterStatement);
await updateCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the Revenue column.");
}
public async Task AddStoringIndexAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
string createStatement = "CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)";
using var connection = new SpannerConnection(connectionString);
var createCmd = connection.CreateDdlCommand(createStatement);
await createCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the AlbumsByAlbumTitle2 index.");
}
/// <summary>
/// </summary>
public ISpannerRelationalConnection CreateMasterConnection()
{
var builder = new SpannerConnectionStringBuilder(ConnectionString);
//Spanner actually has no master or admin db, so we just use a normal connection.
var masterConn =
new SpannerConnection($"Data Source=projects/{builder.Project}/instances/{builder.SpannerInstance}");
var optionsBuilder = new DbContextOptionsBuilder();
optionsBuilder.UseSpanner(masterConn);
return(new SpannerRelationalConnection(Dependencies.With(optionsBuilder.Options)));
}
public void TransactionTagCannotBeSetForReadOnlyTransaction()
{
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginReadOnlyTransaction();
Assert.Throws <InvalidOperationException>(() => transaction.Tag = "transaction-tag-1");
}
public void CommitPriorityDefaultsToUnspecified()
{
Mock <SpannerClient> spannerClientMock = SpannerClientHelpers
.CreateMockClient(Logger.DefaultLogger, MockBehavior.Strict);
spannerClientMock
.SetupBatchCreateSessionsAsync()
.SetupBeginTransactionAsync();
SpannerConnection connection = BuildSpannerConnection(spannerClientMock);
SpannerTransaction transaction = connection.BeginTransaction();
Assert.Equal(Priority.Unspecified, transaction.CommitPriority);
}
public async Task CanClearResources()
{
var client1 = CreateMockClient();
var session = await SessionPool.Default.CreateSessionFromPoolAsync(
client1.Object, s_defaultName.ProjectId,
s_defaultName.InstanceId, s_defaultName.DatabaseId, null, CancellationToken.None)
.ConfigureAwait(false);
SessionPool.Default.ReleaseToPool(client1.Object, session);
await SpannerConnection.ClearPooledResourcesAsync();
Assert.Equal(0, SessionPool.Default.CurrentPooledSessions);
}
public void OpenWithNoDatabase_InvalidCredentials()
{
var builder = new SpannerConnectionStringBuilder
{
DataSource = "projects/project_id/instances/instance_id",
CredentialFile = "this_will_not_exist.json"
};
using (var connection = new SpannerConnection(builder))
{
Assert.Throws <FileNotFoundException>(() => connection.Open());
}
}
public async Task <int> UpdateUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = MarketingBudget * 2 WHERE SingerId = 1 and AlbumId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return(rowCount);
}
public void AddNonDmlCommand_FromValid()
{
var connection = new SpannerConnection();
var command = new SpannerBatchCommand(connection);
command.Add(
"DELETE FROM myTable WHERE Key=@key",
new SpannerParameterCollection {
{ "key", SpannerDbType.Int64, 5 }
});
Assert.Throws <InvalidOperationException>(() => command.Add("drop database myDatabase"));
}
public async Task <int> InsertUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("INSERT Singers (SingerId, FirstName, LastName) VALUES (10, 'Virginia', 'Watson')");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) inserted...");
return(rowCount);
}
private void DeleteDatabase(string name)
{
if (!DatabaseExists(name))
{
return;
}
using (var master = new SpannerConnection(CreateAdminConnectionString()))
{
ExecuteNonQuery(master, GetDropDatabaseSql(name));
SpannerConnection.ClearPooledResourcesAsync().WaitWithUnwrappedExceptions();
}
}
private static IEnumerable <T> Query <T>(SpannerConnection connection, string sql, object[] parameters = null)
=> Execute(connection, command =>
{
using (var dataReader = command.ExecuteReader())
{
var results = Enumerable.Empty <T>();
while (dataReader.Read())
{
results = results.Concat(new[] { dataReader.GetFieldValue <T>(0) });
}
return(results);
}
}, sql, false, parameters);
public async Task <int> DeleteUsingDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE FirstName = 'Alice'");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return(rowCount);
}
protected override void Dispose(bool disposing)
{
if (_disposed)
{
return;
}
if (disposing)
{
SpannerConnection.Dispose();
}
_disposed = true;
base.Dispose(disposing);
}
public async Task <int> UpdateUsingDmlWithTimestampCoreAsync(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();
using var cmd = connection.CreateDmlCommand("UPDATE Albums SET LastUpdateTime = PENDING_COMMIT_TIMESTAMP() WHERE SingerId = 1");
int rowCount = await cmd.ExecuteNonQueryAsync();
Console.WriteLine($"{rowCount} row(s) updated...");
return(rowCount);
}
public async Task <long> DeleteUsingPartitionedDmlCoreAsync(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();
using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");
long rowCount = await cmd.ExecutePartitionedUpdateAsync();
Console.WriteLine($"{rowCount} row(s) deleted...");
return(rowCount);
}
public async Task <string> GetGamePath(string name)
{
using (var connection = new SpannerConnection(CONNECTION_STRING))
{
var cmd = connection.CreateSelectCommand(
@"SELECT path FROM game WHERE name = " + name);
using (var reader = await cmd.ExecuteReaderAsync())
{
//return await reader.ReadAsync();
// todo: fix
return("");
}
}
}
private async Task SelectAndReadFixtureData(string key, SpannerConnection connection, SpannerTransaction transaction)
{
using (var command = connection.CreateSelectCommand(_selectSql))
{
command.Transaction = transaction;
command.Parameters.Add("Key", SpannerDbType.String, key);
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
;
}
}
}
private async Task <int> DeleteFixtureData(string key, SpannerConnection connection, SpannerTransaction transaction)
{
var parameters = new SpannerParameterCollection
{
{ "Key", SpannerDbType.String, key },
{ "OriginalValue", SpannerDbType.Int64, 4 }
};
using (var command = connection.CreateDeleteCommand(_fixture.TableName, parameters))
{
command.Transaction = transaction;
return(await command.ExecuteNonQueryAsync());
}
}
private static async Task DeleteAsync(string projectId,
string instanceId, string databaseId)
{
string AdminConnectionString = $"Data Source=projects/{projectId}/"
+ $"instances/{instanceId}";
using (var connection = new SpannerConnection(AdminConnectionString))
using (var cmd = connection.CreateDdlCommand(
$@"DROP DATABASE {databaseId}"))
{
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
Console.WriteLine($"Done deleting database: {databaseId}");
}
public static async Task QueryAsync(
string projectId, string instanceId, string databaseId, int timespan)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/"
+ $"{instanceId}/databases/{databaseId}";
// Create connection to Cloud Spanner.
using (var connection = new SpannerConnection(connectionString))
{
string sqlCommand;
if (timespan == 0)
{
// No timespan specified. Query Top Ten scores of all time.
sqlCommand =
@"SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
ORDER BY s.Score DESC LIMIT 10";
}
else
{
// Query Top Ten scores filtered by the timepan specified.
sqlCommand =
$@"SELECT p.PlayerId, p.PlayerName, s.Score, s.Timestamp
FROM Players p
JOIN Scores s ON p.PlayerId = s.PlayerId
WHERE s.Timestamp >
TIMESTAMP_SUB(CURRENT_TIMESTAMP(),
INTERVAL {timespan.ToString()} HOUR)
ORDER BY s.Score DESC LIMIT 10";
}
var cmd = connection.CreateSelectCommand(sqlCommand);
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
Console.WriteLine("PlayerId : "
+ reader.GetFieldValue <string>("PlayerId")
+ " PlayerName : "
+ reader.GetFieldValue <string>("PlayerName")
+ " Score : "
+ string.Format("{0:n0}",
Int64.Parse(reader.GetFieldValue <string>("Score")))
+ " Timestamp : "
+ reader.GetFieldValue <string>("Timestamp").Substring(0, 10));
}
}
}
}
private async Task IncrementByOneAsync(SpannerConnection connection, bool orphanTransaction = false)
{
SpannerException spannerException;
do
{
spannerException = null;
try
{
//We'll do manually created transactions here so the tests run on .net core
using (var transaction = await connection.BeginTransactionAsync())
{
long current;
using (var cmd =
connection.CreateSelectCommand(
"SELECT Int64Value FROM TX WHERE K=@k",
new SpannerParameterCollection {
{ "k", SpannerDbType.String, _key }
}))
{
cmd.Transaction = transaction;
var fetched = await cmd.ExecuteScalarAsync().ConfigureAwait(false);
current = fetched is DBNull ? 0L : (long)fetched;
}
using (var cmd = connection.CreateUpdateCommand(
"TX",
new SpannerParameterCollection
{
{ "k", SpannerDbType.String, _key },
{ "Int64Value", SpannerDbType.Int64, current + 1 }
}))
{
cmd.Transaction = transaction;
await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!orphanTransaction)
{
await transaction.CommitAsync().ConfigureAwait(false);
}
}
}
}
catch (SpannerException ex)
{
spannerException = ex;
}
} while (spannerException?.IsRetryable ?? false);
}
public void TransactionConstructor()
{
var connection = new SpannerConnection();
var pool = new FakeSessionPool();
var session = PooledSession.FromSessionName(pool, new SessionName("project", "instance", "database", "session"));
var transaction = new SpannerTransaction(connection, TransactionMode.ReadWrite, session: session, timestampBound: null);
var command = new SpannerBatchCommand(transaction);
Assert.Empty(command.Commands);
Assert.Same(connection, command.Connection);
Assert.Same(transaction, command.Transaction);
Assert.Equal(SpannerBatchCommandType.None, command.CommandType);
}
private static async Task BatchInsertPlayersAsync(string projectId,
string instanceId, string databaseId)
{
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}"
+ $"/databases/{databaseId}";
long playerStartingPlanetDollars = 1000000;
// Batch insert 249,900 player records into the Players table.
using (var connection = new SpannerConnection(connectionString))
{
await connection.OpenAsync();
for (int i = 0; i < 100; i++)
{
// For details on transaction isolation, see the "Isolation" section in:
// https://cloud.google.com/spanner/docs/transactions#read-write_transactions
using (var tx = await connection.BeginTransactionAsync())
using (var cmd = connection.CreateInsertCommand("Players", new SpannerParameterCollection
{
{ "PlayerId", SpannerDbType.String },
{ "PlayerName", SpannerDbType.String },
{ "PlanetDollars", SpannerDbType.Int64 }
}))
{
cmd.Transaction = tx;
for (var x = 1; x < 2500; x++)
{
string nameSuffix = Guid.NewGuid().ToString().Substring(0, 8);
cmd.Parameters["PlayerId"].Value = Guid.NewGuid().ToString("N");
cmd.Parameters["PlayerName"].Value = $"Player-{nameSuffix}";
cmd.Parameters["PlanetDollars"].Value = playerStartingPlanetDollars;
try
{
cmd.ExecuteNonQuery();
}
catch (SpannerException ex) {
Console.WriteLine($"Spanner Exception: {ex.Message}");
// Decrement x and retry
x--;
continue;
}
}
await tx.CommitAsync();
}
}
}
Console.WriteLine("Done inserting sample records...");
}
