public void GetCommandText_TableValueInsertForNonIdentityKey_CommandTextMatchesExpected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var structuredCommandProvider = new Mock <IDbTableCommandFactory>();
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new PostgreSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
////structuredCommandProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
structuredCommandProvider
.Setup(
provider => provider.Create(
It.IsAny <IDatabaseContext>(),
It.IsAny <string>(),
It.IsAny <string>(),
It.IsAny <IEnumerable <GenericSubmissionValueRow> >()))
.Returns(new Mock <IDbCommand>().Object);
var submissionCommand = new JsonInsert <GenericSubmissionValueRow>(databaseContext.Object);
const string Expected = @"INSERT INTO ""dbo"".""GenericSubmissionValue""
(""GenericSubmissionValueId"", ""GenericSubmissionId"")
SELECT t.""GenericSubmissionValueId"", t.""GenericSubmissionId""
FROM jsonb_to_recordset(@GenericSubmissionValueRows::jsonb) AS t (""GenericSubmissionValueId"" bigint, ""GenericSubmissionId"" integer);
";
var actual = submissionCommand.GetCommandText <GenericSubmissionValueRow>("GenericSubmissionValueRows");
Assert.AreEqual(Expected, actual);
}
}
public void GetCommandText_JsonInsertForNonIdentityKey_CommandTextMatchesExpected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new TransactSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
var submissionCommand = new JsonInsert <GenericSubmissionValueRow>(databaseContext.Object);
const string Expected = @"DECLARE @sourceRows table([GenericSubmissionValueId] bigint, [GenericSubmissionId] int);
INSERT INTO @sourceRows ([GenericSubmissionValueId], [GenericSubmissionId])
SELECT [GenericSubmissionValueId], [GenericSubmissionId] FROM OPENJSON(@GenericSubmissionValueRows)
WITH ([GenericSubmissionValueId] bigint '$.GenericSubmissionValueId', [GenericSubmissionId] int '$.GenericSubmissionId');
INSERT INTO [dbo].[GenericSubmissionValue]
([GenericSubmissionValueId], [GenericSubmissionId])
SELECT [GenericSubmissionValueId], [GenericSubmissionId] FROM @sourceRows AS source;
";
var actual = submissionCommand.GetCommandText <GenericSubmissionValueTableTypeRow>("GenericSubmissionValueRows");
Assert.AreEqual(Expected, actual);
}
}
public void GetCommandText_JsonInsertWithIdentityColumnSelectResults_CommandTextMatchesExpected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var structuredCommandProvider = new Mock <IDbTableCommandFactory>();
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new TransactSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
////structuredCommandProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
structuredCommandProvider
.Setup(
provider => provider.Create(
It.IsAny <IDatabaseContext>(),
It.IsAny <string>(),
It.IsAny <string>(),
It.IsAny <IEnumerable <FieldValueTableTypeRow> >()))
.Returns(new Mock <IDbCommand>().Object);
var valuesCommand = new JsonInsert <FieldValueRow>(databaseContext.Object)
.SelectFromInserted(row => row.FieldValueId, row => row.FieldId)
.SelectFromSource <FieldValueTableTypeRow>(
set => set.On(row => row.FieldId, row => row.FieldId),
row => row.LastModifiedByDomainIdentifierId,
row => row.LastModifiedTime);
const string Expected = @"DECLARE @sourceRows table([FieldId] int, [LastModifiedByDomainIdentifierId] int, [LastModifiedTime] datetimeoffset);
INSERT INTO @sourceRows ([FieldId], [LastModifiedByDomainIdentifierId], [LastModifiedTime])
SELECT [FieldId], [LastModifiedByDomainIdentifierId], [LastModifiedTime] FROM OPENJSON(@FieldValueRows)
WITH ([FieldId] int '$.FieldId', [LastModifiedByDomainIdentifierId] int '$.LastModifiedByDomainIdentifierId', [LastModifiedTime] datetimeoffset '$.LastModifiedTime');
DECLARE @inserted table([FieldValueId] bigint, [FieldId] int);
INSERT INTO [dbo].[FieldValue]
([FieldId], [LastModifiedByDomainIdentifierId], [LastModifiedTime])
OUTPUT INSERTED.[FieldValueId], INSERTED.[FieldId]
INTO @inserted ([FieldValueId], [FieldId])
SELECT [FieldId], [LastModifiedByDomainIdentifierId], [LastModifiedTime] FROM @sourceRows AS source;
SELECT i.[FieldValueId], i.[FieldId], s.[LastModifiedByDomainIdentifierId], s.[LastModifiedTime]
FROM @inserted AS i
INNER JOIN @sourceRows AS s
ON i.[FieldId] = s.[FieldId];
";
var actual = valuesCommand.GetCommandText <FieldValueTableTypeRow>("FieldValueRows");
Assert.AreEqual(Expected, actual);
}
}
public void GetCommandText_InsertJsonOnConflictUpdateWithReturn_MatchesSelected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var structuredCommandProvider = new Mock <IDbTableCommandFactory>();
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new PostgreSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
////structuredCommandProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
structuredCommandProvider
.Setup(
provider => provider.Create(
It.IsAny <IDatabaseContext>(),
It.IsAny <string>(),
It.IsAny <string>(),
It.IsAny <IEnumerable <FieldValueRow> >()))
.Returns(new Mock <IDbCommand>().Object);
var valuesCommand = new JsonInsert <FieldValueRow>(databaseContext.Object)
.Upsert(row => row.LastModifiedByDomainIdentifierId, row => row.LastModifiedTime)
.Returning(row => row.FieldId);
const string Expected = @"INSERT INTO ""dbo"".""FieldValue""
(""FieldId"", ""LastModifiedByDomainIdentifierId"", ""LastModifiedTime"")
SELECT t.""FieldId"", t.""LastModifiedByDomainIdentifierId"", t.""LastModifiedTime""
FROM jsonb_to_recordset(@FieldValueRows::jsonb) AS t (""FieldId"" integer, ""LastModifiedByDomainIdentifierId"" integer, ""LastModifiedTime"" timestamp with time zone)
ON CONFLICT (""FieldValueId"")
DO UPDATE SET ""LastModifiedByDomainIdentifierId"" = EXCLUDED.""LastModifiedByDomainIdentifierId"", ""LastModifiedTime"" = EXCLUDED.""LastModifiedTime""
RETURNING ""FieldId"";
";
var actual = valuesCommand.GetCommandText <FieldValueRow>("FieldValueRows");
Assert.AreEqual(Expected, actual);
}
}
public void GetCommandText_JsonInsertForFlattenedType_MatchesExpected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var structuredCommandProvider = new Mock <IDbTableCommandFactory>();
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new TransactSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
////structuredCommandProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
structuredCommandProvider
.Setup(
provider => provider.Create(
It.IsAny <IDatabaseContext>(),
It.IsAny <string>(),
It.IsAny <string>(),
It.IsAny <IEnumerable <FieldValueElementTableTypeRow> >()))
.Returns(new Mock <IDbCommand>().Object);
var dateElementsCommand = new JsonInsert <DateElementRow>(databaseContext.Object)
.InsertInto(row => row.DateElementId, row => row.Value)
.From <FieldValueElementTableTypeRow>(row => row.FieldValueElementId, row => row.DateElement);
const string Expected = @"DECLARE @sourceRows table([FieldValueElementId] bigint, [DateElement] datetimeoffset);
INSERT INTO @sourceRows ([FieldValueElementId], [DateElement])
SELECT [FieldValueElementId], [DateElement] FROM OPENJSON(@FieldValueElementRows)
WITH ([FieldValueElementId] bigint '$.FieldValueElementId', [DateElement] datetimeoffset '$.DateElement');
INSERT INTO [dbo].[DateElement]
([DateElementId], [Value])
SELECT [FieldValueElementId], [DateElement] FROM @sourceRows AS source;
";
var actual = dateElementsCommand.GetCommandText <FieldValueElementTableTypeRow>("FieldValueElementRows");
Assert.AreEqual(Expected, actual);
}
}
public void GetCommandText_TableValuedInsertForFlattenedType_MatchesExpected()
{
var databaseContext = new Mock <IDatabaseContext>();
var mockProvider = new Mock <IRepositoryProvider>();
mockProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
using (mockProvider.Object)
{
var structuredCommandProvider = new Mock <IDbTableCommandFactory>();
var repositoryAdapter = new Mock <IRepositoryAdapter>();
repositoryAdapter.Setup(adapter => adapter.DefinitionProvider).Returns(new DataAnnotationsDefinitionProvider());
repositoryAdapter.Setup(adapter => adapter.NameQualifier).Returns(new PostgreSqlQualifier());
databaseContext.Setup(context => context.RepositoryAdapter).Returns(repositoryAdapter.Object);
////structuredCommandProvider.Setup(provider => provider.DatabaseContext).Returns(databaseContext.Object);
structuredCommandProvider
.Setup(
provider => provider.Create(
It.IsAny <IDatabaseContext>(),
It.IsAny <string>(),
It.IsAny <string>(),
It.IsAny <IEnumerable <FieldValueElementPgFlatRow> >()))
.Returns(new Mock <IDbCommand>().Object);
var dateElementsCommand = new JsonInsert <DateElementRow>(databaseContext.Object)
.InsertInto(row => row.DateElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.DateElement);
const string Expected = @"INSERT INTO ""dbo"".""DateElement""
(""DateElementId"", ""Value"")
SELECT t.""FieldValueElementId"", t.""DateElement""
FROM jsonb_to_recordset(@FieldValueElementRows::jsonb) AS t (""FieldValueElementId"" bigint, ""DateElement"" timestamp with time zone);
";
var actual = dateElementsCommand.GetCommandText <FieldValueElementPgFlatRow>("FieldValueElementRows");
Assert.AreEqual(Expected, actual);
}
}