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);
using var updateCmd = connection.CreateDdlCommand(alterStatement);
await updateCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the MarketingBudget column.");
}
public async Task AddCommitTimestampAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
string alterStatement = "ALTER TABLE Albums ADD COLUMN LastUpdateTime TIMESTAMP OPTIONS (allow_commit_timestamp=true)";
using var connection = new SpannerConnection(connectionString);
var updateCmd = connection.CreateDdlCommand(alterStatement);
await updateCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added LastUpdateTime as a commit timestamp column in Albums table.");
}
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);
using var createCmd = connection.CreateDdlCommand(createStatement);
await createCmd.ExecuteNonQueryAsync();
Console.WriteLine("Added the AlbumsByAlbumTitle2 index.");
}
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 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 async Task CreateDatabaseAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}";
using (var connection = new SpannerConnection(connectionString))
{
string createStatement = $"CREATE DATABASE `{databaseId}`";
var cmd = connection.CreateDdlCommand(createStatement);
await cmd.ExecuteNonQueryAsync();
}
// Update connection string with Database ID for table creation.
connectionString += $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
// Define create table statement for table #1.
string createTableStatement =
@"CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
ComposerInfo BYTES(MAX)
) PRIMARY KEY (SingerId)";
var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
// Define create table statement for table #2.
createTableStatement =
@"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX)
) PRIMARY KEY (SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE";
cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.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 async Task CreateWithExtrasDrop2()
{
string dbName = GenerateDatabaseName();
var builder = new SpannerConnectionStringBuilder(_fixture.Database.NoDbConnectionString);
using (var connection = new SpannerConnection(builder))
{
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}");
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection(builder.WithDatabase(dbName)))
{
const string tableCreate1 = @"CREATE TABLE TX1 (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
const string tableCreate2 = @"CREATE TABLE TX2 (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
var updateCmd = connection.CreateDdlCommand(tableCreate1, tableCreate2);
await updateCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
var cmd = connection.CreateInsertCommand("TX2", new SpannerParameterCollection
{
{ "K", SpannerDbType.String, "key" },
{ "StringValue", SpannerDbType.String, "value" }
});
await cmd.ExecuteNonQueryAsync();
cmd = connection.CreateSelectCommand("SELECT * FROM TX2");
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
Assert.Equal("key", reader.GetFieldValue <string>("K"));
Assert.Equal("value", reader.GetFieldValue <string>("StringValue"));
}
}
using (var connection = new SpannerConnection(builder))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public void DropDatabasePositive(string ddlString)
{
SpannerCommandTextBuilder builder = new SpannerCommandTextBuilder(ddlString);
Assert.True(builder.IsDropDatabaseCommand);
Assert.Equal("FOO", builder.DatabaseToDrop);
//via spannerconnection as well...
using (var connection = new SpannerConnection("Data Source=projects/p/instances/i"))
{
var command = connection.CreateDdlCommand(ddlString);
Assert.True(command.SpannerCommandTextBuilder.IsDropDatabaseCommand);
Assert.Equal("FOO", command.SpannerCommandTextBuilder.DatabaseToDrop);
}
}
public async Task CreateDrop()
{
string dbName = "t_" + Guid.NewGuid().ToString("N").Substring(0, 28);
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
var createCmd = connection.CreateDdlCommand($"CREATE DATABASE {dbName}");
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public async Task CreatePerformancesTableWithTimestampColumnAsync()
{
using var connection = new SpannerConnection(ConnectionString);
// Define create table statement for table with commit timestamp column.
string createTableStatement =
@"CREATE TABLE Performances (
SingerId INT64 NOT NULL,
VenueId INT64 NOT NULL,
EventDate Date,
Revenue INT64,
LastUpdateTime TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (SingerId, VenueId, EventDate),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE";
var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
}
public async Task CreateDrop()
{
string dbName = GenerateDatabaseName();
using (var connection = new SpannerConnection(_fixture.Database.NoDbConnectionString))
{
var createCmd = connection.CreateDdlCommand($"CREATE DATABASE {dbName}");
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection(_fixture.Database.NoDbConnectionString))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public static async Task CreateSampleDatabaseAsync(
string projectId, string instanceId, string databaseId)
{
// Initialize request connection string for database creation.
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}";
// Make the request.
using (var connection = new SpannerConnection(connectionString))
{
string createStatement = $"CREATE DATABASE `{databaseId}`";
var cmd = connection.CreateDdlCommand(createStatement);
try
{
await cmd.ExecuteNonQueryAsync();
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.AlreadyExists)
{
// OK.
}
}
// Update connection string with Database ID for table creation.
connectionString = connectionString + $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
// Define create table statement for table #1.
string createTableStatement =
@"CREATE TABLE Players(
PlayerId INT64 NOT NULL,
PlayerName STRING(2048) NOT NULL
) PRIMARY KEY(PlayerId)";
// Make the request.
var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
// Define create table statement for table #2.
createTableStatement =
@"CREATE TABLE Scores(
PlayerId INT64 NOT NULL,
Score INT64 NOT NULL,
Timestamp TIMESTAMP NOT NULL OPTIONS(allow_commit_timestamp=true)
) PRIMARY KEY(PlayerId, Timestamp), INTERLEAVE IN PARENT Players ON DELETE NO ACTION";
// Make the request.
cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
}
}
public async Task DdlCommandReturnsErrors()
{
string dbName = GenerateDatabaseName();
var builder = new SpannerConnectionStringBuilder(_fixture.Database.NoDbConnectionString);
using (var connection = new SpannerConnection(builder))
{
const string tableSingers = @"
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
ComposerInfo BYTES(MAX),
) PRIMARY KEY(SingerId)";
const string tableAlbums = @"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE";
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}",
tableSingers, tableAlbums);
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection(builder.WithDatabase(dbName)))
{
var dropSingersCmd = connection.CreateDdlCommand("DROP TABLE Singers");
var dropAlbumsCmd = connection.CreateDdlCommand("DROP TABLE Albums");
await Assert.ThrowsAsync <SpannerException>(() => dropSingersCmd.ExecuteNonQueryAsync());
await dropAlbumsCmd.ExecuteNonQueryAsync();
await dropSingersCmd.ExecuteNonQueryAsync();
}
using (var connection = new SpannerConnection(builder))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public async Task DdlCommandReturnsErrors()
{
string dbName = "t_" + Guid.NewGuid().ToString("N").Substring(0, 28);
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
const string tableSingers = @"
CREATE TABLE Singers (
SingerId INT64 NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
ComposerInfo BYTES(MAX),
) PRIMARY KEY(SingerId)";
const string tableAlbums = @"CREATE TABLE Albums (
SingerId INT64 NOT NULL,
AlbumId INT64 NOT NULL,
AlbumTitle STRING(MAX),
) PRIMARY KEY(SingerId, AlbumId),
INTERLEAVE IN PARENT Singers ON DELETE CASCADE";
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}",
tableSingers, tableAlbums);
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection($"{_testFixture.NoDbConnectionString}/databases/{dbName}"))
{
var dropSingersCmd = connection.CreateDdlCommand("DROP TABLE Singers");
var dropAlbumsCmd = connection.CreateDdlCommand("DROP TABLE Albums");
await Assert.ThrowsAsync <SpannerException>(() => dropSingersCmd.ExecuteNonQueryAsync());
await dropAlbumsCmd.ExecuteNonQueryAsync();
await dropSingersCmd.ExecuteNonQueryAsync();
}
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public async Task CreateVenuesTableAndInsertDataAsync()
{
string connectionString = $"Data Source=projects/{ProjectId}/instances/{InstanceId}/" +
$"databases/{DatabaseId}";
// Create connection to Cloud Spanner.
using var connection = new SpannerConnection(connectionString);
// Define create table statement for Venues.
string createTableStatement =
@"CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(1024),
) PRIMARY KEY (VenueId)";
using var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
List <Venue> venues = new List <Venue>
{
new Venue {
VenueId = 4, VenueName = "Venue 4"
},
new Venue {
VenueId = 19, VenueName = "Venue 19"
},
new Venue {
VenueId = 42, VenueName = "Venue 42"
},
};
await Task.WhenAll(venues.Select(venue =>
{
// Insert rows into the Venues table.
using var cmd = connection.CreateInsertCommand("Venues", new SpannerParameterCollection
{
{ "VenueId", SpannerDbType.Int64 },
{ "VenueName", SpannerDbType.String }
});
cmd.Parameters["VenueId"].Value = venue.VenueId;
cmd.Parameters["VenueName"].Value = venue.VenueName;
return(cmd.ExecuteNonQueryAsync());
}));
}
public async Task CreateDatabaseWithRetentionPeriodAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}";
using var connection = new SpannerConnection(connectionString);
var versionRetentionPeriod = "7d";
var createStatement = $"CREATE DATABASE `{databaseId}`";
var alterStatement = @$ "ALTER DATABASE `{databaseId}` SET OPTIONS
(version_retention_period = '{versionRetentionPeriod}')";
// The retention period cannot be set as part of the CREATE DATABASE statement,
// but can be set using an ALTER DATABASE statement directly after database creation.
using var createDbCommand = connection.CreateDdlCommand(
createStatement,
alterStatement
);
await createDbCommand.ExecuteNonQueryAsync();
}
public async Task CreateWithExtrasDrop()
{
string dbName = "t_" + Guid.NewGuid().ToString("N").Substring(0, 28);
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
const string tableCreate = @"CREATE TABLE TX (
K STRING(MAX) NOT NULL,
StringValue STRING(MAX),
) PRIMARY KEY (K)";
var createCmd = connection.CreateDdlCommand(
$"CREATE DATABASE {dbName}",
tableCreate);
await createCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
using (var connection = new SpannerConnection($"{_testFixture.NoDbConnectionString}/databases/{dbName}"))
{
var cmd = connection.CreateInsertCommand("TX", new SpannerParameterCollection
{
{ "K", SpannerDbType.String, "key" },
{ "StringValue", SpannerDbType.String, "value" }
});
await cmd.ExecuteNonQueryAsync();
cmd = connection.CreateSelectCommand("SELECT * FROM TX");
using (var reader = await cmd.ExecuteReaderAsync())
{
await reader.ReadAsync();
Assert.Equal("key", reader.GetFieldValue <string>("K"));
Assert.Equal("value", reader.GetFieldValue <string>("StringValue"));
}
}
using (var connection = new SpannerConnection(_testFixture.NoDbConnectionString))
{
var dropCommand = connection.CreateDdlCommand($"DROP DATABASE {dbName}");
await dropCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
private SpannerTestDatabase(string projectId)
{
TestLogger.Install();
ProjectId = projectId;
var builder = new SpannerConnectionStringBuilder
{
Host = SpannerHost,
DataSource = $"projects/{ProjectId}/instances/{SpannerInstance}",
EmulatorDetection = EmulatorDetection.EmulatorOrProduction
};
if (SpannerPort != null)
{
builder.Port = int.Parse(SpannerPort);
}
NoDbConnectionString = builder.ConnectionString;
SpannerClientCreationOptions = new SpannerClientCreationOptions(builder);
var databaseBuilder = builder.WithDatabase(SpannerDatabase);
ConnectionString = databaseBuilder.ConnectionString;
DatabaseName = databaseBuilder.DatabaseName;
MaybeCreateInstanceOnEmulator(projectId);
if (Fresh)
{
using (var connection = new SpannerConnection(NoDbConnectionString))
{
var createCmd = connection.CreateDdlCommand($"CREATE DATABASE {SpannerDatabase}");
createCmd.ExecuteNonQuery();
Logger.DefaultLogger.Debug($"Created database {SpannerDatabase}");
}
}
else
{
Logger.DefaultLogger.Debug($"Using existing database {SpannerDatabase}");
}
}
public async Task CreateTableWithDataTypesAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
// Define create table statement for table with supported datatypes columns.
string createTableStatement =
@"CREATE TABLE Venues (
VenueId INT64 NOT NULL,
VenueName STRING(100),
VenueInfo BYTES(MAX),
Capacity INT64,
AvailableDates ARRAY<DATE>,
LastContactDate DATE,
OutdoorVenue BOOL,
PopularityScore FLOAT64,
Revenue NUMERIC,
LastUpdateTime TIMESTAMP NOT NULL
OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (VenueId)";
var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
}
public static async Task CreatePlanetsDatabaseAsync(
string projectId, string instanceId, string databaseId)
{
// [START spanner_create_database]
// Initialize request connection string for database creation.
string connectionString =
$"Data Source=projects/{projectId}/instances/{instanceId}";
//Make the request.
using (var connection = new SpannerConnection(connectionString))
{
string createStatement = $"CREATE DATABASE `{databaseId}`";
var cmd = connection.CreateDdlCommand(createStatement);
try
{
await cmd.ExecuteNonQueryAsync();
}
catch (SpannerException e) when(e.ErrorCode == ErrorCode.AlreadyExists)
{
// OK.
}
}
// Update connection string with Database ID for table creation.
connectionString = connectionString + $"/databases/{databaseId}";
using (var connection = new SpannerConnection(connectionString))
{
// Define create table statement for table #1.
string createTableStatement =
@"CREATE TABLE Planets (
PlanetId INT64 NOT NULL,
PlanetName STRING(1024),
PlanetValue INT64,
SharesAvailable INT64
) PRIMARY KEY (PlanetId)";
// Make the request.
var cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
// Define create table statement for table #2.
createTableStatement =
@"CREATE TABLE Players (
PlayerId STRING(MAX) NOT NULL,
PlayerName STRING(1024),
PlanetDollars INT64
) PRIMARY KEY (PlayerId)";
// Make the request.
cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
// Define create table statement for table #3.
createTableStatement =
@"CREATE TABLE Transactions (
PlanetId INT64 NOT NULL,
PlayerId STRING(MAX) NOT NULL,
Amount INT64,
TimeStamp TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true)
) PRIMARY KEY (PlanetId, PlayerId, TimeStamp)";
// Make the request.
cmd = connection.CreateDdlCommand(createTableStatement);
await cmd.ExecuteNonQueryAsync();
}
// [END spanner_create_database]
}