public static IEnumerable <object[]> GetSpannerDbTypes()
{
yield return(new object[]
{ SpannerDbType.ArrayOf(SpannerDbType.String), SpannerDbType.ArrayOf(SpannerDbType.String) });
yield return(new object[]
{ SpannerDbType.ArrayOf(SpannerDbType.Bytes), SpannerDbType.ArrayOf(SpannerDbType.Bytes) });
yield return(new object[]
{
SpannerDbType.StructOf(
new Dictionary <string, SpannerDbType>
{
{ "StringValue", SpannerDbType.String },
{ "StringValue2", SpannerDbType.String },
{ "FloatValue", SpannerDbType.Float64 },
{ "BoolArrayValue", SpannerDbType.ArrayOf(SpannerDbType.Bool) },
}),
SpannerDbType.StructOf(
new Dictionary <string, SpannerDbType>
{
{ "StringValue", SpannerDbType.String },
{ "StringValue2", SpannerDbType.String },
{ "FloatValue", SpannerDbType.Float64 },
{ "BoolArrayValue", SpannerDbType.ArrayOf(SpannerDbType.Bool) },
})
});
}
public Task BindByteArrayList() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Bytes),
new List <byte[]> {
new byte[] { 1, 2, 3 },
new byte[] { 4, 5, 6 },
null
});
public async Task ParallelWriteAsync()
{
string[] keys = new string[] { IdGenerator.FromGuid(), IdGenerator.FromGuid(), IdGenerator.FromGuid() };
await RetryHelpers.ExecuteWithRetryAsync(async() =>
{
using var scope = new TransactionScope();
using var connection = _fixture.GetConnection();
await connection.OpenAsync();
await Task.WhenAll(keys.Select(key =>
{
using var cmd = connection.CreateInsertCommand(_fixture.TableName);
cmd.Parameters.Add("K", SpannerDbType.String).Value = key;
cmd.Parameters.Add("StringValue", SpannerDbType.String).Value = "text";
return(cmd.ExecuteNonQueryAsync());
}));
scope.Complete();
});
// Read the inserted values.
using var connection = _fixture.GetConnection();
using var command = connection.CreateSelectCommand($"SELECT COUNT(*) AS C FROM {_fixture.TableName} WHERE K IN UNNEST(@Keys)");
command.Parameters.Add("Keys", SpannerDbType.ArrayOf(SpannerDbType.String)).Value = keys;
using var reader = command.ExecuteReader();
Assert.True(reader.Read());
Assert.Equal(keys.Length, reader.GetInt32(0));
}
public async Task <List <Venue> > QueryWithArrayAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
// Initialize a list of dates to use for querying.
var exampleList = new List <DateTime>
{
DateTime.Parse("2020-10-01"),
DateTime.Parse("2020-11-01")
};
using var connection = new SpannerConnection(connectionString);
var cmd = connection.CreateSelectCommand(
"SELECT VenueId, VenueName, AvailableDate FROM Venues v, "
+ "UNNEST(v.AvailableDates) as AvailableDate "
+ "WHERE AvailableDate in UNNEST(@ExampleArray)");
cmd.Parameters.Add("ExampleArray", SpannerDbType.ArrayOf(SpannerDbType.Date), exampleList);
var venues = new List <Venue>();
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
venues.Add(new Venue
{
VenueId = reader.GetFieldValue <int>("VenueId"),
VenueName = reader.GetFieldValue <string>("VenueName"),
AvailableDates = new List <DateTime> {
reader.GetFieldValue <DateTime>("AvailableDate")
}
});
}
return(venues);
}
public static IEnumerable <object[]> GetSpannerDbTypes()
{
yield return(new object[]
{ SpannerDbType.ArrayOf(SpannerDbType.String), SpannerDbType.ArrayOf(SpannerDbType.String) });
yield return(new object[]
{ SpannerDbType.ArrayOf(SpannerDbType.Bytes), SpannerDbType.ArrayOf(SpannerDbType.Bytes) });
yield return(new object[]
{
new SpannerStruct
{
{ "StringValue", SpannerDbType.String, null },
{ "StringValue2", SpannerDbType.String, null },
{ "FloatValue", SpannerDbType.Float64, null },
{ "BoolArrayValue", SpannerDbType.ArrayOf(SpannerDbType.Bool), null },
}.GetSpannerDbType(),
new SpannerStruct
{
{ "StringValue", SpannerDbType.String, null },
{ "StringValue2", SpannerDbType.String, null },
{ "FloatValue", SpannerDbType.Float64, null },
{ "BoolArrayValue", SpannerDbType.ArrayOf(SpannerDbType.Bool), null },
}.GetSpannerDbType()
});
}
public async Task TestChunking()
{
Logger.Instance.Info($"Seed={_seed}");
var rowsRead = 0;
int rowsToWrite = _random.Next(1, 6);
using (var connection = await _testFixture.GetTestDatabaseConnectionAsync())
{
await connection.OpenAsync();
using (var tx = await connection.BeginTransactionAsync())
{
using (var cmd = connection.CreateInsertCommand(
_testFixture.ChunkingTestTable, new SpannerParameterCollection
{
new SpannerParameter("K", SpannerDbType.String),
new SpannerParameter("StringValue", SpannerDbType.String),
new SpannerParameter("StringArrayValue", SpannerDbType.ArrayOf(SpannerDbType.String)),
new SpannerParameter("BytesValue", SpannerDbType.Bytes),
new SpannerParameter("BytesArrayValue", SpannerDbType.ArrayOf(SpannerDbType.Bytes))
}))
{
cmd.Transaction = tx;
//write 1-5 rows
for (var i = 0; i < rowsToWrite; i++)
{
await InsertRowAsync(cmd);
}
await tx.CommitAsync();
}
}
using (var readCmd = connection.CreateSelectCommand($"SELECT * FROM {_testFixture.ChunkingTestTable}"))
{
using (var reader = (SpannerDataReader)await readCmd.ExecuteReaderAsync())
{
var keySet = new HashSet <string>();
while (await reader.ReadAsync())
{
var k = reader.GetFieldValue <string>("K");
if (!_addedKeys.Contains(k))
{
continue; // this key is from a previous test run.
}
rowsRead++;
Assert.True(keySet.Add(k));
Assert.Equal(_stringValues[k], reader.GetFieldValue <string>("StringValue"));
Assert.Equal(_stringArrayValues[k], reader.GetFieldValue <string[]>("StringArrayValue"));
Assert.Equal(_bytesValues[k], reader.GetFieldValue <byte[]>("BytesValue"));
Assert.Equal(_bytesArrayValues[k], reader.GetFieldValue <byte[][]>("BytesArrayValue"));
}
}
}
}
Assert.Equal(rowsToWrite, rowsRead);
}
public async Task WriteNulls()
{
Assert.Equal(
1, await InsertAsync(
new SpannerParameterCollection
{
new SpannerParameter("BoolValue", SpannerDbType.Bool, null),
new SpannerParameter("Int64Value", SpannerDbType.Int64, null),
new SpannerParameter("Float64Value", SpannerDbType.Float64, null),
new SpannerParameter("StringValue", SpannerDbType.String, null),
new SpannerParameter("TimestampValue", SpannerDbType.Timestamp, null),
new SpannerParameter("DateValue", SpannerDbType.Date, null),
new SpannerParameter(
"BoolArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Bool),
null),
new SpannerParameter(
"Int64ArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Int64),
null),
new SpannerParameter(
"Float64ArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Float64),
null),
new SpannerParameter(
"StringArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.String),
null),
new SpannerParameter(
"BytesArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Bytes),
null),
new SpannerParameter(
"TimestampArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Timestamp),
null),
new SpannerParameter(
"DateArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Date),
null)
}));
using (var reader = await GetLastRowAsync())
{
Assert.Null(reader.GetValue(reader.GetOrdinal("BoolValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("Int64Value")));
Assert.Null(reader.GetValue(reader.GetOrdinal("Float64Value")));
Assert.Null(reader.GetValue(reader.GetOrdinal("StringValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("TimestampValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("DateValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("BoolArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("Int64ArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("Float64ArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("StringArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("BytesArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("TimestampArrayValue")));
Assert.Null(reader.GetValue(reader.GetOrdinal("DateArrayValue")));
}
}
public void DeserializeArrayOfStruct()
{
var wireValue = JsonParser.Default.Parse <Value>($"[ {s_sampleStructSerialized} ]");
var dbType = SpannerDbType.ArrayOf(s_sampleStruct.GetSpannerDbType());
var actual = dbType.ConvertToClrType <List <object> >(wireValue, SpannerConversionOptions.Default);
Assert.Equal(1, actual.Count);
AssertSampleStruct((SpannerStruct)actual[0]);
}
public async Task WriteEmpties()
{
Assert.Equal(
1, await InsertAsync(
new SpannerParameterCollection
{
{
"BoolArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Bool),
new bool[0]
},
{
"Int64ArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Int64),
new long[0]
},
{
"Float64ArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Float64),
new double[0]
},
{
"StringArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.String),
new string[0]
},
{
"BytesArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Bytes),
new byte[0][]
},
{
"TimestampArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Timestamp),
new DateTime[0]
},
{
"DateArrayValue",
SpannerDbType.ArrayOf(SpannerDbType.Date),
new DateTime[0]
}
}));
using (var reader = await GetLastRowAsync())
{
Assert.Equal(new bool[] { }, reader.GetFieldValue <bool[]>(reader.GetOrdinal("BoolArrayValue")));
Assert.Equal(new long[] { }, reader.GetFieldValue <long[]>(reader.GetOrdinal("Int64ArrayValue")));
Assert.Equal(new double[] { }, reader.GetFieldValue <double[]>(reader.GetOrdinal("Float64ArrayValue")));
Assert.Equal(new string[] { }, reader.GetFieldValue <string[]>(reader.GetOrdinal("StringArrayValue")));
Assert.Equal(new byte[][] { }, reader.GetFieldValue <byte[][]>(reader.GetOrdinal("BytesArrayValue")));
Assert.Equal(
new DateTime[] { }, reader.GetFieldValue <DateTime[]>(reader.GetOrdinal("TimestampArrayValue")));
Assert.Equal(new DateTime[] { }, reader.GetFieldValue <DateTime[]>(reader.GetOrdinal("DateArrayValue")));
}
}
public void DeserializeNullableDoubleArrayContainingNull()
{
var protobuf = new Value {
ListValue = new ListValue {
Values = { Value.ForNumber(5.5), Value.ForNull(), Value.ForNumber(10.5) }
}
};
var options = SpannerConversionOptions.Default;
var expected = new double?[] { 5.5, null, 10.5 };
Assert.Equal(expected, SpannerDbType.ArrayOf(SpannerDbType.Float64).ConvertToClrType <double?[]>(protobuf, options));
}
public void DeserializeStringArrayContainingNull()
{
var protobuf = new Value {
ListValue = new ListValue {
Values = { Value.ForString("x"), Value.ForNull(), Value.ForString("y") }
}
};
var options = SpannerConversionOptions.Default;
var expected = new[] { "x", null, "y" };
Assert.Equal(expected, SpannerDbType.ArrayOf(SpannerDbType.String).ConvertToClrType <string[]>(protobuf, options));
}
public void SerializeStringArrayContainingNull()
{
var input = new[] { "x", null, "y" };
var expected = new Value {
ListValue = new ListValue {
Values = { Value.ForString("x"), Value.ForNull(), Value.ForString("y") }
}
};
var options = SpannerConversionOptions.Default;
Assert.Equal(expected, SpannerDbType.ArrayOf(SpannerDbType.String).ToProtobufValue(input, options));
}
protected override void ConfigureParameter(DbParameter parameter)
{
// This key step will configure our SpannerParameter with this complex type, which will result in
// the proper type conversions when the requests go out.
if (!(parameter is SpannerParameter spannerParameter))
{
throw new ArgumentException($"Spanner-specific type mapping {GetType().Name} being used with non-Spanner parameter type {parameter.GetType().Name}");
}
base.ConfigureParameter(parameter);
spannerParameter.SpannerDbType = SpannerDbType.ArrayOf(SpannerDbType.Date);
}
public void DeserializeDoubleArrayContainingNull()
{
var protobuf = new Value {
ListValue = new ListValue {
Values = { Value.ForNumber(5.5), Value.ForNull(), Value.ForNumber(10.5) }
}
};
var options = SpannerConversionOptions.Default;
// The null value causes an InvalidCastException.
var dbType = SpannerDbType.ArrayOf(SpannerDbType.Float64);
Assert.Throws <InvalidCastException>(() => dbType.ConvertToClrType <double[]>(protobuf, options));
}
public void DeserializeComplexStruct()
{
var complexStruct = new SpannerStruct
{
{ "StructField", s_sampleStruct.GetSpannerDbType(), s_sampleStruct },
{ "ArrayField", SpannerDbType.ArrayOf(SpannerDbType.Int64), GetIntsForArray().Select(x => (long)x).ToList() }
};
var wireValue = JsonParser.Default.Parse <Value>($"[ {s_sampleStructSerialized}, [ \"4\", \"5\", \"6\" ] ]");
var actual = complexStruct.GetSpannerDbType().ConvertToClrType <SpannerStruct>(wireValue, SpannerConversionOptions.Default);
Assert.Equal(2, actual.Count);
Assert.Equal("StructField", actual[0].Name);
Assert.Equal(s_sampleStruct.GetSpannerDbType(), actual[0].Type);
AssertSampleStruct((SpannerStruct)actual[0].Value);
Assert.Equal("ArrayField", actual[1].Name);
Assert.Equal(SpannerDbType.ArrayOf(SpannerDbType.Int64), actual[1].Type);
Assert.Equal(new[] { 4L, 5L, 6L }, actual[1].Value);
}
public async Task <List <int> > QueryDataWithNestedStructFieldAsync(string projectId, string instanceId, string databaseId)
{
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
SpannerStruct name1 = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Elena" },
{ "LastName", SpannerDbType.String, "Campbell" }
};
SpannerStruct name2 = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, "Hannah" },
{ "LastName", SpannerDbType.String, "Harris" }
};
SpannerStruct songInfo = new SpannerStruct
{
{ "song_name", SpannerDbType.String, "Imagination" },
{ "artistNames", SpannerDbType.ArrayOf(name1.GetSpannerDbType()), new[] { name1, name2 } }
};
var singerIds = new List <int>();
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT SingerId, @song_info.song_name "
+ "FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING>(FirstName, LastName) "
+ "IN UNNEST(@song_info.artistNames)");
cmd.Parameters.Add("song_info", songInfo.GetSpannerDbType(), songInfo);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var singerId = reader.GetFieldValue <int>("SingerId");
singerIds.Add(singerId);
Console.WriteLine($"SingerId: {singerId}");
Console.WriteLine($"Song Name: {reader.GetFieldValue<string>(1)}");
}
return(singerIds);
}
public async Task <List <int> > QueryDataWithArrayOfStructAsync(string projectId, string instanceId, string databaseId)
{
// [START spanner_create_user_defined_struct]
var nameType = new SpannerStruct
{
{ "FirstName", SpannerDbType.String, null },
{ "LastName", SpannerDbType.String, null }
};
// [END spanner_create_user_defined_struct]
// [START spanner_create_array_of_struct_with_data]
var bandMembers = new List <SpannerStruct>
{
new SpannerStruct {
{ "FirstName", SpannerDbType.String, "Elena" }, { "LastName", SpannerDbType.String, "Campbell" }
},
new SpannerStruct {
{ "FirstName", SpannerDbType.String, "Gabriel" }, { "LastName", SpannerDbType.String, "Wright" }
},
new SpannerStruct {
{ "FirstName", SpannerDbType.String, "Benjamin" }, { "LastName", SpannerDbType.String, "Martinez" }
},
};
// [END spanner_create_array_of_struct_with_data]
var singerIds = new List <int>();
string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";
using var connection = new SpannerConnection(connectionString);
using var cmd = connection.CreateSelectCommand(
"SELECT SingerId FROM Singers WHERE STRUCT<FirstName STRING, LastName STRING> "
+ "(FirstName, LastName) IN UNNEST(@names)");
cmd.Parameters.Add("names", SpannerDbType.ArrayOf(nameType.GetSpannerDbType()), bandMembers);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
singerIds.Add(reader.GetFieldValue <int>("SingerId"));
}
return(singerIds);
}
public static IEnumerable <object[]> AllSpannerTypes()
{
yield return(new object[] { SpannerDbType.Bytes });
yield return(new object[] { SpannerDbType.String });
yield return(new object[] { SpannerDbType.Bool });
yield return(new object[] { SpannerDbType.Date });
yield return(new object[] { SpannerDbType.Float64 });
yield return(new object[] { SpannerDbType.Int64 });
yield return(new object[] { SpannerDbType.Numeric });
yield return(new object[] { SpannerDbType.Timestamp });
yield return(new object[] { SpannerDbType.Json });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Bytes) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.String) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Bool) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Date) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Float64) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Int64) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Numeric) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Timestamp) });
yield return(new object[] { SpannerDbType.ArrayOf(SpannerDbType.Json) });
}
public Task BindBooleanEmptyArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Bool),
new bool[] { });
public Task BindBooleanArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Bool),
new bool?[] { true, null, false });
public Task BindTimestampNullArray() => TestBind <DateTime?[]>(
SpannerDbType.ArrayOf(SpannerDbType.Timestamp),
null);
public Task BindTimestampEmptyArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Timestamp),
new DateTime?[] { });
public Task BindTimestampArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Timestamp),
new DateTime?[]
{ new DateTime(2017, 5, 26, 3, 15, 0), null, new DateTime(2017, 5, 9, 12, 30, 0) });
public Task BindStringNullArray() => TestBind <string[]>(
SpannerDbType.ArrayOf(SpannerDbType.String),
null);
public Task BindStringEmptyArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.String),
new string[] { });
public Task BindStringArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.String),
new[] { "abc", null, "123" });
public Task BindInt64NullArray() => TestBind <long[]>(
SpannerDbType.ArrayOf(SpannerDbType.Int64),
null);
public Task BindInt64EmptyArray() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Int64),
new long[] { });
public Task BindInt64Array() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Int64),
new long?[] { 1, null, 0 });
public Task BindFloat64NullArray() => TestBind <double[]>(
SpannerDbType.ArrayOf(SpannerDbType.Float64),
null);