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();
}
}
/// <summary>
/// Merges the specified <paramref name="submission"/> into the repository backed by the <paramref name="provider"/>.
/// </summary>
/// <param name="submission">
/// The submission.
/// </param>
/// <param name="provider">
/// The provider.
/// </param>
private void MergeSubmission(GenericSubmission submission, IRepositoryProvider provider)
{
// Merge our existing fields
var fields = submission.SubmissionValues.Select(value => value.Field).Distinct().ToList();
var fieldItems = from f in fields
select new FieldRow
{
Name = f.Name,
Description = f.Description
};
// Merge in the field values.
var commandProvider = new JsonCommandFactory();
var transaction = provider.BeginTransaction();
var fieldsCommand = new JsonInsert <FieldRow>(provider.DatabaseContext).OnConflict(row => row.Name)
.Upsert(row => row.Description)
.Returning(row => row.FieldId, row => row.Name, row => row.Description);
var mergedFields = fieldsCommand.ExecuteForResults(fieldItems).ToList();
foreach (var field in fields)
{
var input = mergedFields.FirstOrDefault(f => string.Equals(f.Name, field.Name, StringComparison.Ordinal));
// Because we are doing a subset, and we know we will get back baseline fields. If MERGE is messed up this will error later when there
// aren't IDs for baseline fields.
if (input == null)
{
continue;
}
this.mapper.MapTo(input, field);
}
var submissionRepository = new EntityRepository <GenericSubmission, GenericSubmissionRow>(provider, this.mapper);
submissionRepository.Save(submission);
// Could be mapped as well.
var fieldValues = from v in submission.SubmissionValues
select new FieldValueTableTypeRow
{
FieldId = v.Field.FieldId.GetValueOrDefault(),
LastModifiedByDomainIdentifierId = v.LastModifiedBy.DomainIdentityId.GetValueOrDefault(),
LastModifiedTime = v.LastModifiedTime
};
// We use FieldValueId to essentially ensure we're only affecting the scope of this submission. FieldId on the select brings back
// only inserted rows matched back to their original fields.
var fieldValueCommand = new JsonInsert <FieldValueRow>(provider.DatabaseContext);
var mergedFieldValues = fieldValueCommand.OnConflict(row => row.FieldValueId)
.Upsert(row => row.LastModifiedByDomainIdentifierId, row => row.LastModifiedTime)
.Returning(row => row.FieldValueId, row => row.FieldId, row => row.LastModifiedByDomainIdentifierId, row => row.LastModifiedTime)
.ExecuteForResults(fieldValues)
.ToList();
Assert.IsTrue(mergedFieldValues.All(row => row.FieldValueId > 0));
// Map back to the domain object.
foreach (var value in submission.SubmissionValues)
{
var input = mergedFieldValues.First(row => row.FieldId == value.Field.FieldId);
this.mapper.MapTo(input, value);
Assert.IsTrue(value.FieldValueId.HasValue);
}
// Now merge in the field value elements.
// Do the field value elements
var valueElements = (from e in submission.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 elementMergeCommand = new JsonInsert <FieldValueElementRow>(provider.DatabaseContext);
var mergedValueElements = elementMergeCommand.OnConflict(row => row.FieldValueElementId)
.Upsert(row => row.Order)
.Returning(row => row.FieldValueElementId, row => row.FieldValueId, row => row.Order)
.ExecuteForResults(valueElements)
.ToList();
foreach (var element in valueElements)
{
var input = mergedValueElements.First(row => row.FieldValueId == element.FieldValueId && row.Order == element.Order);
this.mapper.MapTo(input, element);
Assert.IsTrue(element.FieldValueElementId.HasValue);
}
var dateElementsCommand = new JsonInsert <DateElementRow>(provider.DatabaseContext)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.DateElement)
.OnConflict(row => row.DateElementId)
.Upsert(row => row.Value);
dateElementsCommand.Execute(valueElements.Where(row => row.DateElement.HasValue));
var floatElementsCommand = new JsonInsert <FloatElementRow>(provider.DatabaseContext)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.FloatElement)
.OnConflict(row => row.FloatElementId)
.Upsert(row => row.Value);
floatElementsCommand.Execute(valueElements.Where(row => row.FloatElement.HasValue));
var integerElementsCommand = new JsonInsert <IntegerElementRow>(provider.DatabaseContext)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.IntegerElement)
.OnConflict(row => row.IntegerElementId)
.Upsert(row => row.Value);
integerElementsCommand.Execute(valueElements.Where(row => row.IntegerElement.HasValue));
var moneyElementsCommand = new JsonInsert <MoneyElementRow>(provider.DatabaseContext)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.MoneyElement)
.OnConflict(row => row.MoneyElementId)
.Upsert(row => row.Value);
moneyElementsCommand.Execute(valueElements.Where(row => row.MoneyElement.HasValue));
var textElementsCommand = new JsonInsert <TextElementRow>(provider.DatabaseContext)
.From <FieldValueElementPgFlatRow>(row => row.FieldValueElementId, row => row.TextElement)
.OnConflict(row => row.TextElementId)
.Upsert(row => row.Value);
textElementsCommand.Execute(valueElements.Where(row => row.TextElement != null));
// Attach the values to the submission
var genericValueSubmissions = from v in mergedFieldValues
select new GenericSubmissionValueTableTypeRow
{
GenericSubmissionId = submission.GenericSubmissionId.GetValueOrDefault(),
GenericSubmissionValueId = v.FieldValueId
};
var submissionCommand = new JsonInsert <GenericSubmissionValueRow>(provider.DatabaseContext).Upsert(
row => row.GenericSubmissionValueId,
row => row.GenericSubmissionId);
////.DeleteUnmatchedInSource(row => row.GenericSubmissionId); Can't with PostgreSQL
submissionCommand.Execute(genericValueSubmissions);
transaction.Commit();
}
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();
}
}
