public async Task InsertAlbum()
{
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
db.Albums.Add(new Albums
{
AlbumId = 1L,
Title = "Some title",
SingerId = 1L,
ReleaseDate = new SpannerDate(2000, 1, 1),
});
var updateCount = await db.SaveChangesAsync();
Assert.Equal(1L, updateCount);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request => {
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase);
Assert.Equal("Albums", mutation.Insert.Table);
var row = mutation.Insert.Values[0];
var cols = mutation.Insert.Columns;
Assert.Equal("1", row.Values[cols.IndexOf("AlbumId")].StringValue);
Assert.Equal("Some title", row.Values[cols.IndexOf("Title")].StringValue);
Assert.Equal("1", row.Values[cols.IndexOf("SingerId")].StringValue);
Assert.Equal("2000-01-01", row.Values[cols.IndexOf("ReleaseDate")].StringValue);
}
);
}
public async Task CanUpdateCommitTimestamp()
{
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
_fixture.SpannerMock.AddOrUpdateStatementResult($"{Environment.NewLine}SELECT `ColComputed`{Environment.NewLine}FROM `TableWithAllColumnTypes`{Environment.NewLine}WHERE TRUE AND `ColInt64` = @p0", StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = V1.TypeCode.String
}, "FOO"));
var row = new TableWithAllColumnTypes {
ColInt64 = 1L
};
db.TableWithAllColumnTypes.Attach(row);
row.ColBool = true;
await db.SaveChangesAsync();
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request =>
{
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Update, mutation.OperationCase);
Assert.Single(mutation.Update.Values);
var row = mutation.Update.Values[0];
var cols = mutation.Update.Columns;
Assert.Equal("spanner.commit_timestamp()", row.Values[cols.IndexOf("ColCommitTS")].StringValue);
}
);
}
public async Task DeleteSinger_DoesNotSelectFullName()
{
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
db.Singers.Remove(new Singers {
SingerId = 1L
});
var updateCount = await db.SaveChangesAsync();
Assert.Equal(1L, updateCount);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request =>
{
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Delete, mutation.OperationCase);
Assert.Equal("Singers", mutation.Delete.Table);
var keySet = mutation.Delete.KeySet;
Assert.False(keySet.All);
Assert.Empty(keySet.Ranges);
Assert.Single(keySet.Keys);
Assert.Single(keySet.Keys[0].Values);
Assert.Equal("1", keySet.Keys[0].Values[0].StringValue);
}
);
Assert.Empty(_fixture.SpannerMock.Requests.Where(request => request is ExecuteBatchDmlRequest));
Assert.Empty(_fixture.SpannerMock.Requests.Where(request => request is ExecuteSqlRequest));
Assert.Single(_fixture.SpannerMock.Requests.Where(request => request is CommitRequest));
}
public async Task CanInsertRowWithCommitTimestampAndComputedColumn()
{
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
var selectSql = $"{Environment.NewLine}SELECT `ColComputed`{Environment.NewLine}FROM `TableWithAllColumnTypes`{Environment.NewLine}WHERE TRUE AND `ColInt64` = @p0";
_fixture.SpannerMock.AddOrUpdateStatementResult(selectSql, StatementResult.CreateSingleColumnResultSet(new V1.Type {
Code = V1.TypeCode.String
}, "FOO"));
db.TableWithAllColumnTypes.Add(
new TableWithAllColumnTypes {
ColInt64 = 1L
}
);
await db.SaveChangesAsync();
Assert.Empty(_fixture.SpannerMock.Requests.Where(request => request is ExecuteBatchDmlRequest));
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is ExecuteSqlRequest).Select(request => (ExecuteSqlRequest)request),
request => Assert.Equal(selectSql.Trim(), request.Sql.Trim())
);
Assert.Single(_fixture.SpannerMock.Requests.Where(request => request is CommitRequest));
// Verify the order of the requests (that is, the Select statement should be outside the implicit transaction).
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest || request is ExecuteSqlRequest).Select(request => request.GetType()),
requestType => Assert.Equal(typeof(CommitRequest), requestType),
requestType => Assert.Equal(typeof(ExecuteSqlRequest), requestType)
);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request =>
{
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase);
Assert.Single(mutation.Insert.Values);
var row = mutation.Insert.Values[0];
var cols = mutation.Insert.Columns;
Assert.Equal("spanner.commit_timestamp()", row.Values[cols.IndexOf("ColCommitTS")].StringValue);
}
);
}
public async Task InsertSingerInTransaction()
{
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
using var transaction = await db.Database.BeginTransactionAsync();
db.Singers.Add(new Singers
{
SingerId = 1L,
FirstName = "Alice",
LastName = "Morrison",
});
var updateCount = await db.SaveChangesAsync();
await transaction.CommitAsync();
Assert.Equal(1L, updateCount);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request => {
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase);
Assert.Equal("Singers", mutation.Insert.Table);
var row = mutation.Insert.Values[0];
var cols = mutation.Insert.Columns;
Assert.Equal("1", row.Values[cols.IndexOf("SingerId")].StringValue);
Assert.Equal("Alice", row.Values[cols.IndexOf("FirstName")].StringValue);
Assert.Equal("Morrison", row.Values[cols.IndexOf("LastName")].StringValue);
Assert.Equal(-1, cols.IndexOf("FullName"));
}
);
// Verify that EF Core does NOT try to fetch the name of the Singer, even though it is a computed
// column that should normally be propagated. The fetch is skipped because the update uses mutations
// in combination with manual transactions. Trying to fetch the name of the singer is therefore not
// possible during the transaction.
Assert.Collection(_fixture.SpannerMock.Requests
.Where(request => request is CommitRequest || request is ExecuteSqlRequest)
.Select(request => request.GetType()),
request => Assert.Equal(typeof(CommitRequest), request));
}
public async Task InsertSinger()
{
var selectFullNameSql = AddSelectSingerFullNameResult("Alice Morrison", 0);
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
db.Singers.Add(new Singers
{
SingerId = 1L,
FirstName = "Alice",
LastName = "Morrison",
});
var updateCount = await db.SaveChangesAsync();
Assert.Equal(1L, updateCount);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
request => {
Assert.Single(request.Mutations);
var mutation = request.Mutations[0];
Assert.Equal(Mutation.OperationOneofCase.Insert, mutation.OperationCase);
Assert.Equal("Singers", mutation.Insert.Table);
var row = mutation.Insert.Values[0];
var cols = mutation.Insert.Columns;
Assert.Equal("1", row.Values[cols.IndexOf("SingerId")].StringValue);
Assert.Equal("Alice", row.Values[cols.IndexOf("FirstName")].StringValue);
Assert.Equal("Morrison", row.Values[cols.IndexOf("LastName")].StringValue);
Assert.Equal(-1, cols.IndexOf("FullName"));
}
);
// Verify that the SELECT for the FullName is done after the commit.
Assert.Collection(_fixture.SpannerMock.Requests
.Where(request => request is CommitRequest || request is ExecuteSqlRequest)
.Select(request => request.GetType()),
request => Assert.Equal(typeof(CommitRequest), request),
request => Assert.Equal(typeof(ExecuteSqlRequest), request));
Assert.Single(_fixture.SpannerMock.Requests
.Where(request => request is ExecuteSqlRequest sqlRequest && sqlRequest.Sql.Trim() == selectFullNameSql.Trim()));
}
public async Task ExplicitAndImplicitTransactionIsRetried(bool disableInternalRetries, bool useExplicitTransaction)
{
// Abort the next statement that is executed on the mock server.
_fixture.SpannerMock.AbortNextStatement();
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
IDbContextTransaction transaction = null;
if (useExplicitTransaction)
{
// Note that using explicit transactions in combination with mutations has a couple of side-effects:
// 1. Read-your-writes does not work.
// 2. Computed columns are not propagated to the current context.
transaction = await db.Database.BeginTransactionAsync();
if (disableInternalRetries)
{
transaction.DisableInternalRetries();
}
}
db.Venues.Add(new Venues
{
Code = "C1",
Name = "Concert Hall",
});
// We can only disable internal retries when using explicit transactions. Otherwise internal retries
// are always used.
if (disableInternalRetries && useExplicitTransaction)
{
await db.SaveChangesAsync();
var e = await Assert.ThrowsAsync <SpannerException>(() => transaction.CommitAsync());
Assert.Equal(ErrorCode.Aborted, e.ErrorCode);
}
else
{
var updateCount = await db.SaveChangesAsync();
Assert.Equal(1L, updateCount);
if (useExplicitTransaction)
{
await transaction.CommitAsync();
}
Assert.Empty(_fixture.SpannerMock.Requests.Where(request => request is ExecuteBatchDmlRequest));
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => (CommitRequest)request),
// The commit request is sent twice to the server, as the statement is aborted during the first attempt.
request =>
{
Assert.Single(request.Mutations);
Assert.Equal("Venues", request.Mutations.First().Insert.Table);
Assert.NotNull(request.TransactionId);
},
request =>
{
Assert.Single(request.Mutations);
Assert.Equal("Venues", request.Mutations.First().Insert.Table);
Assert.NotNull(request.TransactionId);
}
);
}
}
public async Task UpdateSinger_SelectsFullName()
{
// Setup results.
var selectSingerSql = AddFindSingerResult($"SELECT `s`.`SingerId`, `s`.`BirthDate`, `s`.`FirstName`, " +
$"`s`.`FullName`, `s`.`LastName`, `s`.`Picture`{Environment.NewLine}FROM `Singers` AS `s`{Environment.NewLine}" +
$"WHERE `s`.`SingerId` = @__p_0{Environment.NewLine}LIMIT 1");
var selectFullNameSql = AddSelectSingerFullNameResult("Alice Pieterson-Morrison", 0);
using var db = new MockServerSampleDbContextUsingMutations(ConnectionString);
var singer = await db.Singers.FindAsync(1L);
singer.LastName = "Pieterson-Morrison";
var updateCount = await db.SaveChangesAsync();
Assert.Equal(1L, updateCount);
Assert.Collection(
_fixture.SpannerMock.Requests
.Where(request => !new[] { typeof(BeginTransactionRequest), typeof(BatchCreateSessionsRequest) }.Contains(request.GetType()))
.Select(request => request.GetType()),
request => Assert.Equal(typeof(ExecuteSqlRequest), request),
request => Assert.Equal(typeof(CommitRequest), request),
request => Assert.Equal(typeof(ExecuteSqlRequest), request)
);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is ExecuteSqlRequest).Select(request => (ExecuteSqlRequest)request),
request =>
{
Assert.Equal(selectSingerSql.Trim(), request.Sql.Trim());
Assert.Null(request.Transaction?.Id);
},
request =>
{
Assert.Equal(selectFullNameSql.Trim(), request.Sql.Trim());
Assert.Null(request.Transaction?.Id);
}
);
Assert.Collection(
_fixture.SpannerMock.Requests.Where(request => request is CommitRequest).Select(request => request as CommitRequest),
request =>
{
Assert.Collection(
request.Mutations,
mutation =>
{
Assert.Equal(Mutation.OperationOneofCase.Update, mutation.OperationCase);
Assert.Equal("Singers", mutation.Update.Table);
Assert.Collection(
mutation.Update.Columns,
column => Assert.Equal("SingerId", column),
column => Assert.Equal("LastName", column)
);
Assert.Collection(
mutation.Update.Values,
row =>
{
Assert.Collection(
row.Values,
value => Assert.Equal("1", value.StringValue),
value => Assert.Equal("Pieterson-Morrison", value.StringValue)
);
}
);
}
);
}
);
}
