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..."); }