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[]
{
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 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 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 void TestDeSerializeFromValue(
CultureInfo culture,
object expected,
SpannerDbType spannerDbType,
string inputJson,
TestType testType = TestType.Both)
{
if (testType == TestType.ClrToValue)
{
return;
}
WithCulture(culture, () =>
{
string infoAddendum = $"type:{expected?.GetType().Name}, spannerType:{spannerDbType}, input:{inputJson} ";
try
{
var wireValue = JsonParser.Default.Parse <Value>(inputJson);
var targetClrType = expected?.GetType() ?? typeof(object);
var actual = wireValue.ConvertToClrType(spannerDbType.ToProtobufType(), targetClrType);
Assert.Equal(expected, actual);
}
catch (Exception e)
{
Assert.True(false, infoAddendum + e);
throw;
}
});
}
public Task BindByteArrayList() => TestBind(
SpannerDbType.ArrayOf(SpannerDbType.Bytes),
new List <byte[]> {
new byte[] { 1, 2, 3 },
new byte[] { 4, 5, 6 },
null
});
public SpannerComplexTypeMapping(SpannerDbType complexType, System.Type clrType, bool unicode = false, int?size = null)
: base(complexType.ToString(), clrType, unicode: unicode, size: size)
{
_complexType = complexType;
_clrType = clrType;
_isArrayType = SpannerComplexTypeMapping.s_arrayTypes.Contains(complexType);
}
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")));
}
}
/// <summary>
/// Creates a key consisting of the given key parts.
/// </summary>
/// <param name="keyParts">The values that define the key</param>
public Key(params object[] keyParts)
{
var protobufValues = keyParts.Select(
v => SpannerDbType.FromClrType(v?.GetType())?.ToProtobufValue(v, SpannerConversionOptions.Default) ?? Value.ForNull());
KeyParts = new ListValue {
Values = { protobufValues }
};
}
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 void TestNullConversion_LegacyNullHandling(object expectedValue, SpannerDbType dbType, System.Type targetClrType)
{
var builder = new SpannerConnectionStringBuilder("UseClrDefaultForNull = true");
var options = SpannerConversionOptions.ForConnectionStringBuilder(builder);
var input = Value.ForNull();
var actual = dbType.ConvertToClrType(input, targetClrType, options, topLevel: true);
Assert.Equal(expectedValue, actual);
}
public void ValueMappings(object value, SpannerDbType spannerType, DbType adoType, System.Type defaultClrType)
{
var parameter = new SpannerParameter {
Value = value
};
Assert.Equal(defaultClrType, spannerType.DefaultClrType);
Assert.Equal(spannerType, parameter.SpannerDbType);
Assert.Equal(adoType, parameter.DbType);
}
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 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));
}
public void SetDefaultSize(SpannerDbType spannerType)
{
var param = new SpannerParameter {
SpannerDbType = spannerType
};
// The default size should be 0 for all types.
Assert.Equal(0, param.Size);
// Setting the size to the same (default) value should be allowed for all types.
param.Size = 0;
Assert.Equal(0, param.Size);
}
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));
}
private async Task TestBind <T>(SpannerDbType parameterType, T value, Func <SpannerDataReader, T> typeSpecificReader = null)
{
int rowsRead;
var valueRead = default(T);
using (var connection = await _testFixture.GetTestDatabaseConnectionAsync())
{
var cmd = connection.CreateSelectCommand(
"SELECT @v",
new SpannerParameterCollection {
new SpannerParameter("v", parameterType)
});
cmd.Parameters["v"].Value = value;
using (var reader = await cmd.ExecuteReaderAsync())
{
rowsRead = 0;
while (await reader.ReadAsync())
{
valueRead = reader.GetFieldValue <T>(0);
// optional extra test for certain built in types
if (typeSpecificReader != null)
{
Assert.Equal(typeSpecificReader(reader), valueRead);
// ReSharper disable once CompareNonConstrainedGenericWithNull
Assert.Equal(reader.IsDBNull(0), value == null);
}
rowsRead++;
}
}
}
Assert.Equal(1, rowsRead);
var valueAsArray = value as Array;
if (valueAsArray != null)
{
var valueReadAsArray = valueRead as Array;
Assert.NotNull(valueReadAsArray);
Assert.Equal(valueAsArray.Length, valueReadAsArray.Length);
for (int i = 0; i < valueAsArray.Length; i++)
{
// ReSharper disable once PossibleNullReferenceException
Assert.Equal(valueAsArray.GetValue(i), valueReadAsArray.GetValue(i));
}
}
else
{
Assert.Equal(value, valueRead);
}
}
public void DbTypeMappgings(SpannerDbType spannerType, DbType adoType)
{
var parameter = new SpannerParameter {
SpannerDbType = spannerType
};
Assert.Equal(adoType, parameter.DbType);
parameter.ResetDbType();
parameter.DbType = adoType;
Assert.Equal(spannerType, parameter.SpannerDbType);
}
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 StringConversions(string stringFormat, SpannerDbType spannerType, bool shouldEqual = true)
{
Assert.True(SpannerDbType.TryParse(stringFormat, out SpannerDbType result));
if (shouldEqual)
{
Assert.Equal(spannerType, result);
}
else
{
Assert.NotEqual(spannerType, result);
}
//roundtrip test.
Assert.True(SpannerDbType.TryParse(result.ToString(), out SpannerDbType result2));
Assert.Equal(result, result2);
}
public void InvalidSizeTest(SpannerDbType spannerType, int size)
{
if (size == -1)
{
Assert.Throws <ArgumentOutOfRangeException>(
() => new SpannerParameter {
SpannerDbType = spannerType.WithSize(size)
});
}
else
{
Assert.Throws <InvalidOperationException>(
() => new SpannerParameter {
SpannerDbType = spannerType.WithSize(size)
});
}
}
public void SizeTest(SpannerDbType spannerType, int size)
{
var parameter = new SpannerParameter {
SpannerDbType = spannerType.WithSize(size)
};
Assert.Equal(parameter.Size, size);
Assert.Equal(parameter.SpannerDbType.TypeCode, spannerType.TypeCode);
parameter = new SpannerParameter
{
SpannerDbType = spannerType,
Size = size
};
Assert.Equal(parameter.Size, size);
Assert.Equal(parameter.SpannerDbType.TypeCode, spannerType.TypeCode);
}
public async Task BindNull(SpannerDbType parameterType)
{
using (var connection = _fixture.GetConnection())
{
var cmd = connection.CreateSelectCommand("SELECT @v");
cmd.Parameters.Add("v", parameterType, null);
using (var reader = await cmd.ExecuteReaderAsync())
{
Assert.True(await reader.ReadAsync());
Assert.True(reader.IsDBNull(0));
Assert.Equal(DBNull.Value, reader.GetValue(0));
Assert.False(await reader.ReadAsync());
}
}
}
private async Task TestBindNonNull <T>(SpannerDbType parameterType, T value, Func <SpannerDataReader, T> typeSpecificReader = null)
{
int rowsRead;
var valueRead = default(T);
using (var connection = _fixture.GetConnection())
{
var cmd = connection.CreateSelectCommand("SELECT @v");
cmd.Parameters.Add("v", parameterType, value);
using (var reader = await cmd.ExecuteReaderAsync())
{
rowsRead = 0;
while (await reader.ReadAsync())
{
valueRead = reader.GetFieldValue <T>(0);
// Optional extra test for certain built in types
if (typeSpecificReader != null)
{
Assert.Equal(typeSpecificReader(reader), valueRead);
Assert.False(reader.IsDBNull(0));
}
rowsRead++;
}
}
}
Assert.Equal(1, rowsRead);
var valueAsArray = value as Array;
if (valueAsArray != null)
{
var valueReadAsArray = valueRead as Array;
Assert.NotNull(valueReadAsArray);
Assert.Equal(valueAsArray.Length, valueReadAsArray.Length);
for (int i = 0; i < valueAsArray.Length; i++)
{
Assert.Equal(valueAsArray.GetValue(i), valueReadAsArray.GetValue(i));
}
}
else
{
Assert.Equal(value, valueRead);
}
}
/// <inheritdoc />
protected override RelationalTypeMapping CreateMappingFromStoreType(string storeType)
{
if (SpannerDbType.TryParse(storeType, out SpannerDbType parsedType))
{
if (!parsedType.Size.HasValue &&
s_dbTypeMappings.TryGetValue(parsedType, out RelationalTypeMapping mapping))
{
return(mapping);
}
if (parsedType.DbType == DbType.String)
{
// return a sized string.
return(new StringTypeMapping(storeType, parsedType.DbType, true, parsedType.Size));
}
return(new SpannerComplexTypeMapping(parsedType));
}
return(null);
}
private async Task TestBind <T>(SpannerDbType parameterType, T value)
{
int rowsRead;
var valueRead = default(T);
using (var connection = await _testFixture.GetTestDatabaseConnectionAsync())
{
var cmd = connection.CreateSelectCommand(
"SELECT @v",
new SpannerParameterCollection {
new SpannerParameter("v", parameterType)
});
cmd.Parameters["v"].Value = value;
using (var reader = await cmd.ExecuteReaderAsync())
{
rowsRead = 0;
while (await reader.ReadAsync())
{
valueRead = reader.GetFieldValue <T>(0);
rowsRead++;
}
}
}
Assert.Equal(1, rowsRead);
var valueAsArray = value as Array;
if (valueAsArray != null)
{
var valueReadAsArray = valueRead as Array;
Assert.NotNull(valueReadAsArray);
Assert.Equal(valueAsArray.Length, valueReadAsArray.Length);
for (int i = 0; i < valueAsArray.Length; i++)
{
// ReSharper disable once PossibleNullReferenceException
Assert.Equal(valueAsArray.GetValue(i), valueReadAsArray.GetValue(i));
}
}
else
{
Assert.Equal(value, valueRead);
}
}
public SpannerStringTypeMapping(
string storeType = null,
bool unicode = false,
int?size = null,
bool fixedLength = false,
SpannerDbType sqlDbType = null,
StoreTypePostfix?storeTypePostfix = null)
: this(
new RelationalTypeMappingParameters(
new CoreTypeMappingParameters(typeof(string)),
storeType,
storeTypePostfix ?? StoreTypePostfix.Size,
GetDbType(unicode, fixedLength),
unicode,
size,
fixedLength),
sqlDbType)
{
}
