public async Task Create_JsonCommand_CommandPropertiesMatchExpected()
{
var providerFactory = new PostgreSqlProviderFactory(new DataAnnotationsDefinitionProvider());
using (var provider = providerFactory.Create(ConfigurationRoot.GetConnectionString("OrmTestDbPg")))
{
var target = new JsonCommandFactory();
await provider.DatabaseContext.OpenSharedConnectionAsync(CancellationToken.None);
var actual = target.Create(provider.DatabaseContext, "Select * from 'Mytable'", "MyParameter", new List <DependentRow>());
Assert.IsInstanceOfType(actual, typeof(NpgsqlCommand));
Assert.AreEqual("Select * from 'Mytable'", actual.CommandText);
var actualParameter = actual.Parameters["MyParameter"] as NpgsqlParameter;
Assert.IsNotNull(actualParameter);
Assert.AreEqual(NpgsqlDbType.Jsonb, actualParameter.NpgsqlDbType);
}
}
public void ExecuteForResults_GenericSubmission_DoesNotThrowException()
{
var providerFactory = new PostgreSqlProviderFactory(new DataAnnotationsDefinitionProvider());
using (var provider = providerFactory.Create(ConfigurationRoot.GetConnectionString("OrmTestDbPg")))
{
var identityRepository = new EntityRepository <DomainIdentity, DomainIdentityRow>(provider, this.mapper);
var domainIdentity = identityRepository.FirstOrDefault(
Query.Select <DomainIdentity>()
.Where(set => set.AreEqual(identity => identity.UniqueIdentifier, Environment.UserName)))
?? identityRepository.Save(
new DomainIdentity(Environment.UserName)
{
FirstName = "King",
MiddleName = "T.",
LastName = "Animal"
});
var expected = new GenericSubmission("My Submission", domainIdentity);
var internalId = new Field
{
Name = "Internal ID",
Description = "Unique ID used internally"
};
var firstName = new Field
{
Name = "First Name",
Description = "The person's first name"
};
var lastName = new Field
{
Name = "Last Name",
Description = "The person's last name"
};
var yearlyWage = new Field
{
Name = "Yearly Wage",
Description = "The base wage paid year over year."
};
var hireDate = new Field
{
Name = "Hire Date",
Description = "The date and time of hire for the person"
};
var bonusTarget = new Field
{
Name = "Bonus Target",
Description = "The target bonus for the person"
};
var contactNumbers = new Field
{
Name = "Contact Numbers",
Description = "A list of contact numbers for the person in order of preference"
};
expected.SetValue(internalId, 9234);
expected.SetValue(firstName, "Dan");
expected.SetValue(lastName, "The Man");
expected.SetValue(yearlyWage, 75100.35m);
expected.SetValue(hireDate, DateTimeOffset.Now);
expected.SetValue(bonusTarget, 1.59834578934);
expected.SetValue(
contactNumbers,
new List <string>
{
"423-222-2252",
"615-982-0012",
"+1-555-252-5521"
});
expected.Submit();
var fieldRepository = new EntityRepository <Field, FieldRow>(provider, this.mapper);
var fields = expected.SubmissionValues.Select(value => value.Field).Distinct().ToDictionary(field => field.Name, field => field);
var inclusionValues = fields.Keys.ToArray();
var existingFields =
fieldRepository.SelectEntities(new EntitySelection <Field>().Where(set => set.Include(field => field.Name, inclusionValues)));
foreach (var field in existingFields)
{
var output = fields[field.Name];
this.mapper.MapTo(field, output);
}
foreach (var field in fields.Values.Where(field => field.FieldId.HasValue == false))
{
fieldRepository.Save(field);
}
var submissionRepository = new EntityRepository <GenericSubmission, GenericSubmissionRow>(provider, this.mapper);
var transaction = provider.BeginTransaction();
submissionRepository.Save(expected);
var submissionId = expected.GenericSubmissionId.GetValueOrDefault();
Assert.AreNotEqual(0, submissionId);
// Set up the structured command provider.
var structuredCommandProvider = new JsonCommandFactory();
// Do the field values
var valuesList = from v in expected.SubmissionValues
select new FieldValueRow
{
FieldId = v.Field.FieldId.GetValueOrDefault(),
LastModifiedByDomainIdentifierId = domainIdentity.DomainIdentityId.GetValueOrDefault(),
LastModifiedTime = expected.SubmittedTime
};
var valuesCommand =
new JsonInsert <FieldValueRow>(provider.DatabaseContext)
.Returning(row => row.FieldValueId, row => row.FieldId);
var insertedValues = valuesCommand.ExecuteForResults(valuesList).ToList();
// Map back to the domain object.
foreach (var value in expected.SubmissionValues)
{
var input = insertedValues.FirstOrDefault(row => row.FieldId == value.Field.FieldId);
this.mapper.MapTo(input, value);
}
// Do the field value elements
var elementsList = (from e in expected.SubmissionValues.SelectMany(value => value.Elements)
select new FieldValueElementPgFlatRow
{
FieldValueElementId = e.FieldValueElementId,
FieldValueId = e.FieldValue.FieldValueId.GetValueOrDefault(),
Order = e.Order,
DateElement = e.Element as DateTimeOffset? ?? e.Element as DateTime?,
FloatElement = e.Element as double? ?? e.Element as float?,
IntegerElement = e.Element as long? ?? e.Element as int? ?? e.Element as short? ?? e.Element as byte?,
MoneyElement = e.Element as decimal?,
TextElement = e.Element as string // here we actually want it to be null if it is not a string
}).ToList();
var elementsCommand = new JsonInsert <FieldValueElementPgFlatRow>(provider.DatabaseContext)
.Returning(row => row.FieldValueId, row => row.Order);
// Reassign with our added identities
elementsList = elementsCommand.ExecuteForResults(elementsList).ToList();
foreach (var element in expected.SubmissionValues.SelectMany(value => value.Elements))
{
var input = elementsList.First(row => row.FieldValueId == element.FieldValue.FieldValueId && row.Order == element.Order);
this.mapper.MapTo(input, element);
}
var dateElementsCommand = new JsonInsert <DateElementRow>(provider.DatabaseContext)
.InsertInto(row => row.DateElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.DateElement);
dateElementsCommand.Execute(elementsList.Where(row => row.DateElement.HasValue));
var floatElementsCommand = new JsonInsert <FloatElementRow>(provider.DatabaseContext)
.InsertInto(row => row.FloatElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.FloatElement);
floatElementsCommand.Execute(elementsList.Where(row => row.FloatElement.HasValue));
var integerElementsCommand = new JsonInsert <IntegerElementRow>(provider.DatabaseContext)
.InsertInto(row => row.IntegerElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.IntegerElement);
integerElementsCommand.Execute(elementsList.Where(row => row.IntegerElement.HasValue));
var moneyElementsCommand = new JsonInsert <MoneyElementRow>(provider.DatabaseContext)
.InsertInto(row => row.MoneyElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.MoneyElement);
moneyElementsCommand.Execute(elementsList.Where(row => row.MoneyElement.HasValue));
var textElementsCommand = new JsonInsert <TextElementRow>(provider.DatabaseContext)
.InsertInto(row => row.TextElementId, row => row.Value)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.TextElement);
textElementsCommand.Execute(elementsList.Where(row => row.TextElement != null));
// Attach the values to the submission
var genericValueSubmissions = from v in insertedValues
select new GenericSubmissionValueRow
{
GenericSubmissionId = submissionId,
GenericSubmissionValueId = v.FieldValueId
};
var submissionCommand = new JsonInsert <GenericSubmissionValueRow>(provider.DatabaseContext);
submissionCommand.Execute(genericValueSubmissions);
transaction.Commit();
}
}
public void ExecuteForResults_MultipleGenericSubmissions_MatchesExpected()
{
var internalId = new Field
{
Name = "MERGE_Existing_Internal ID",
Description = "Unique ID used internally"
};
var firstName = new Field
{
Name = "MERGE_Existing_First Name",
Description = "The person's first name"
};
var lastName = new Field
{
Name = "MERGE_Existing_Last Name",
Description = "The person's last name"
};
var yearlyWage = new Field
{
Name = "MERGE_Existing_Yearly Wage",
Description = "The base wage paid year over year."
};
var hireDate = new Field
{
Name = "MERGE_NonExisting_Hire Date",
Description = "The date and time of hire for the person"
};
var bonusTarget = new Field
{
Name = "MERGE_NonExisting_Bonus Target",
Description = "The target bonus for the person"
};
var contactNumbers = new Field
{
Name = "MERGE_NonExisting_Contact Numbers",
Description = "A list of contact numbers for the person in order of preference"
};
var providerFactory = new PostgreSqlProviderFactory(new DataAnnotationsDefinitionProvider());
GenericSubmission baselineSubmission;
DomainIdentity domainIdentity2;
using (var provider = providerFactory.Create(ConfigurationRoot.GetConnectionString("OrmTestDbPg")))
{
// Set up the domain identity, not part of our validity testing.
var identityRepository = new EntityRepository <DomainIdentity, DomainIdentityRow>(provider, this.mapper);
var domainIdentity = identityRepository.FirstOrDefault(
Query.Select <DomainIdentity>()
.Where(set => set.AreEqual(identity => identity.UniqueIdentifier, Environment.UserName)))
?? identityRepository.Save(
new DomainIdentity(Environment.UserName)
{
FirstName = "King",
MiddleName = "T.",
LastName = "Animal"
});
var domainIdentifier2 = $"{Environment.UserName}2";
domainIdentity2 = identityRepository.FirstOrDefault(
Query.Select <DomainIdentity>()
.Where(set => set.AreEqual(identity => identity.UniqueIdentifier, domainIdentifier2)))
?? identityRepository.Save(
new DomainIdentity(domainIdentifier2)
{
FirstName = "Foo",
MiddleName = "J.",
LastName = "Bar"
});
// We will add to this submission later.
baselineSubmission = new GenericSubmission("My MERGE Submission", domainIdentity);
baselineSubmission.SetValue(internalId, 9234);
baselineSubmission.SetValue(firstName, "Dan");
baselineSubmission.SetValue(lastName, "The Man");
baselineSubmission.SetValue(yearlyWage, 72150.35m); // gonna get updated so lets check that this value got scrapped
baselineSubmission.Submit();
this.MergeSubmission(baselineSubmission, provider);
}
Assert.IsTrue(baselineSubmission.GenericSubmissionId.HasValue);
// Reusing the key lets us test whether updates are in fact working as expected.
var expected = new GenericSubmission(
"My Final MERGE Submission",
domainIdentity2,
baselineSubmission.GenericSubmissionId.GetValueOrDefault());
expected.SetValue(yearlyWage, 75100.35m);
expected.SetValue(hireDate, new DateTimeOffset(DateTimeOffset.Now.Date));
expected.SetValue(bonusTarget, 1.59834578934);
expected.SetValue(
contactNumbers,
new List <string>
{
"423-222-2252",
"615-982-0012",
"+1-555-252-5521"
});
expected.Submit();
GenericSubmission actual;
// Using a new provider clears any provider-level caches
using (var provider = providerFactory.Create(ConfigurationRoot.GetConnectionString("OrmTestDbPg")))
{
var fieldValueRepository = new EntityRepository <FieldValue, FieldValueRow>(provider, this.mapper);
// Get rid of all the previous fields.
fieldValueRepository.DeleteSelection(
Query.From <FieldValueRow>()
.Where(
set => set.Include(
row => row.FieldValueId,
baselineSubmission.SubmissionValues.Select(value => value.FieldValueId).ToArray())));
this.MergeSubmission(expected, provider);
var submissionRepository = new EntityRepository <GenericSubmission, GenericSubmissionRow>(provider, this.mapper);
actual = submissionRepository.FirstOrDefault(expected.GenericSubmissionId);
var genericSubmissionValueRepository = new EntityRepository <FieldValue, GenericSubmissionValueRow>(provider, this.mapper);
var values = genericSubmissionValueRepository.SelectEntities(
Query.Select <GenericSubmissionValueRow>()
.From(
set => set.InnerJoin(row => row.GenericSubmissionValueId, row => row.FieldValue.FieldValueId)
.InnerJoin(row => row.FieldValue.FieldId, row => row.FieldValue.Field.FieldId)
.InnerJoin(
row => row.FieldValue.LastModifiedByDomainIdentifierId,
row => row.FieldValue.LastModifiedBy.DomainIdentityId))
.Where(set => set.AreEqual(row => row.GenericSubmissionId, expected.GenericSubmissionId.GetValueOrDefault())))
.ToDictionary(value => value.FieldValueId.GetValueOrDefault(), value => value);
actual.Load(values.Values);
var valueElementRows = provider.SelectEntities(
Query.Select <FieldValueElementPgFlatRow>()
.From(
set => set.LeftJoin <DateElementRow>(row => row.FieldValueElementId, row => row.DateElementId)
.LeftJoin <FloatElementRow>(row => row.FieldValueElementId, row => row.FloatElementId)
.LeftJoin <IntegerElementRow>(row => row.FieldValueElementId, row => row.IntegerElementId)
.LeftJoin <MoneyElementRow>(row => row.FieldValueElementId, row => row.MoneyElementId)
.LeftJoin <TextElementRow>(row => row.FieldValueElementId, row => row.TextElementId))
.Where(set => set.Include(row => row.FieldValueId, values.Keys.ToArray())))
.ToList();
foreach (var key in values.Keys)
{
values[key]
.Load(
from e in valueElementRows
where e.FieldValueId == key
orderby e.Order
select new FieldValueElement(
e.DateElement ?? e.FloatElement ?? e.IntegerElement ?? e.MoneyElement ?? e.TextElement as object,
e.FieldValueElementId.GetValueOrDefault()));
}
}
var expectedElements = expected.SubmissionValues.SelectMany(value => value.Elements)
.OrderBy(element => element.FieldValueElementId)
.ToList();
var actualElements = actual.SubmissionValues.SelectMany(value => value.Elements).OrderBy(element => element.FieldValueElementId).ToList();
var firstExpectedElement = expectedElements.Skip(1).First();
var firstActualElement = actualElements.Skip(1).FirstOrDefault();
Assert.AreEqual(
firstExpectedElement,
firstActualElement,
string.Join(Environment.NewLine, firstExpectedElement.GetDifferences(firstActualElement)));
CollectionAssert.AreEqual(expectedElements, actualElements);
Assert.AreEqual(expected, actual, string.Join(Environment.NewLine, expected.GetDifferences(actual)));
CollectionAssert.AreEqual(
expected.SubmissionValues.OrderBy(x => x.FieldValueId).ToList(),
actual.SubmissionValues.OrderBy(x => x.FieldValueId).ToList());
}
public void ExecuteForResults_ListOfFields_DoesNotThrowException()
{
var internalId = new Field
{
Name = "INS_Internal ID",
Description = "Unique ID used internally"
};
var firstName = new Field
{
Name = "INS_First Name",
Description = "The person's first name"
};
var lastName = new Field
{
Name = "INS_Last Name",
Description = "The person's last name"
};
var yearlyWage = new Field
{
Name = "INS_Yearly Wage",
Description = "The base wage paid year over year."
};
var hireDate = new Field
{
Name = "INS_Hire Date",
Description = "The date and time of hire for the person"
};
var bonusTarget = new Field
{
Name = "INS_Bonus Target",
Description = "The target bonus for the person"
};
var contactNumbers = new Field
{
Name = "INS_Contact Numbers",
Description = "A list of contact numbers for the person in order of preference"
};
var fields = new List <Field>
{
internalId,
firstName,
lastName,
yearlyWage,
hireDate,
bonusTarget,
contactNumbers
};
var providerFactory = new PostgreSqlProviderFactory(new DataAnnotationsDefinitionProvider());
using (var provider = providerFactory.Create(ConfigurationRoot.GetConnectionString("OrmTestDbPg")))
{
var fieldRepository = new EntityRepository <Field, FieldRow>(provider, this.mapper);
// Delete the existing rows.
fieldRepository.DeleteSelection(Query.From <FieldRow>().Where(set => set.AreEqual(row => row.Name, "INS_%")));
var transaction = provider.BeginTransaction();
// Set up the structured command provider.
var structuredCommandProvider = new JsonCommandFactory();
var fieldInsertCommand =
new JsonInsert <FieldRow>(provider.DatabaseContext).Returning(
row => row.FieldId,
row => row.Description);
var actual = fieldInsertCommand.ExecuteForResults(
fields.Select(
field => new FieldRow
{
Name = field.Name,
Description = field.Description
}));
foreach (var fieldRow in actual)
{
Assert.AreNotEqual(0, fieldRow.FieldId);
}
transaction.Commit();
}
}