public void TestLeafProperties_AlternateConstructor()
{
//-------------Setup Test Pack ------------------
QueryField field1 = new QueryField("MyField", "MyField", null);
//-------------Test Pre-conditions --------------
//-------------Execute test ---------------------
Criteria criteria = new Criteria(field1, Criteria.ComparisonOp.Equals, "MyValue");
//-------------Test Result ----------------------
Assert.AreSame(field1, criteria.Field);
Assert.AreEqual("MyValue", criteria.FieldValue);
Assert.AreEqual(Criteria.ComparisonOp.Equals, criteria.ComparisonOperator);
}
public void TestJoinField_Constructor()
{
//-------------Setup Test Pack ------------------
string tableName = "MY_SOURCE";
Source source = new Source("MySource", tableName);
string joinTableName = "MY_JOINED_TABLE";
Source joinSource = new Source("JoinSource", joinTableName);
QueryField fromField = new QueryField("FromField", "FROM_FIELD", source);
QueryField toField = new QueryField("ToField", "TO_FIELD", joinSource);
//-------------Execute test ---------------------
Source.Join.JoinField joinField = new Source.Join.JoinField(fromField, toField);
//-------------Test Result ----------------------
Assert.AreSame(fromField, joinField.FromField);
Assert.AreSame(toField, joinField.ToField);
}
public void TestSetCriteria_AddsJoins()
{
//---------------Set up test pack-------------------
var selectQuery = new SelectQuery();
const string sourceName = "mysource";
selectQuery.Source = new Source(sourceName);
var fieldSource = new Source(sourceName);
var expectedSourceName = TestUtil.GetRandomString();
fieldSource.JoinToSource(new Source(expectedSourceName));
var field = new QueryField("testfield", "testfield", fieldSource);
var criteria = new Criteria(field, Criteria.ComparisonOp.Equals, "value");
//---------------Execute Test ----------------------
selectQuery.Criteria = criteria;
//---------------Test Result -----------------------
Assert.AreEqual(1, selectQuery.Source.Joins.Count);
Assert.AreEqual(selectQuery.Source, selectQuery.Source.Joins[0].FromSource);
Assert.AreEqual(expectedSourceName, selectQuery.Source.Joins[0].ToSource.Name);
}
public void TestJoinStructure()
{
//-------------Setup Test Pack ------------------
string tableName = "MY_SOURCE";
Source source = new Source("MySource", tableName);
string joinTableName = "MY_JOINED_TABLE";
Source joinSource = new Source("JoinSource", joinTableName);
Source.Join join = new Source.Join(source, joinSource);
QueryField fromField = new QueryField("FromField", "FROM_FIELD", source);
QueryField toField = new QueryField("ToField", "TO_FIELD", joinSource);
//-------------Execute test ---------------------
join.JoinFields.Add(new Source.Join.JoinField(fromField, toField));
source.Joins.Add(join);
//-------------Test Result ----------------------
Assert.AreEqual(1, source.Joins.Count);
Assert.AreSame(join, source.Joins[0]);
Assert.AreSame(fromField, join.JoinFields[0].FromField);
Assert.AreSame(toField, join.JoinFields[0].ToField);
}
private static void PrepareSourceTree(Source currentSource, ref ClassDef currentClassDef)
{
while (currentSource != null)
{
Source childSource = currentSource.ChildSource;
currentSource.EntityName = currentClassDef.GetTableName();
if (childSource != null)
{
string relationshipName = childSource.Name;
IRelationshipDef relationshipDef = currentClassDef.GetRelationship(relationshipName);
if (relationshipDef == null)
{
string message = string.Format("'{0}' does not have a relationship called '{1}'.",
currentClassDef.ClassName, relationshipName);
throw new RelationshipNotFoundException(message);
}
foreach (RelPropDef relPropDef in relationshipDef.RelKeyDef)
{
string ownerFieldName = currentClassDef.GetPropDef(relPropDef.OwnerPropertyName).DatabaseFieldName;
string relatedFieldName =
relationshipDef.RelatedObjectClassDef.GetPropDef(relPropDef.RelatedClassPropName).DatabaseFieldName;
QueryField fromField = new QueryField(relPropDef.OwnerPropertyName, ownerFieldName, currentSource);
QueryField toField = new QueryField(relPropDef.RelatedClassPropName, relatedFieldName, childSource);
currentSource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fromField, toField));
}
currentClassDef = (ClassDef) relationshipDef.RelatedObjectClassDef;
}
currentSource = childSource;
}
}
///<summary>
/// Prepares
///</summary>
///<param name="currentSource"></param>
///<param name="classDef"></param>
///<param name="field"></param>
///<returns></returns>
public static IPropDef PrepareField(Source currentSource, IClassDef classDef, QueryField field)
{
IClassDef classDefOfField = classDef;
if (classDef.IsUsingClassTableInheritance())
classDefOfField = classDef.GetPropDef(field.PropertyName).ClassDef;
IClassDef fieldClassDef;
PrepareSource(classDefOfField, ref currentSource, out fieldClassDef);
field.Source = currentSource;
if (fieldClassDef != null)
return fieldClassDef.GetPropDef(field.PropertyName);
return null;
}
protected virtual Source GetCorrectEngineSourceStructure()
{
Source engineSource = new Source("Engine", "table_class_Engine");
Source partSource = GetCorrectPartSourceStructure();
Source.Join join = engineSource.InheritanceJoins.AddNewJoinTo(partSource, Source.JoinType.InnerJoin);
QueryField engineQueryField = new QueryField("EngineID", "field_Engine_ID", engineSource);
QueryField partQueryField = new QueryField("PartID", "field_Part_ID", partSource);
Source.Join.JoinField joinField = new Source.Join.JoinField(engineQueryField, partQueryField);
join.JoinFields.Add(joinField);
return engineSource;
}
protected virtual Source GetCorrectPartSourceStructure()
{
Source partSource = new Source("Part", "table_class_Part");
Source entitySource = new Source("Entity", "table_Entity");
Source.Join join = partSource.InheritanceJoins.AddNewJoinTo(entitySource, Source.JoinType.InnerJoin);
QueryField partQueryField = new QueryField("PartID", "field_Part_ID", partSource);
QueryField entityQueryField = new QueryField("EntityID", "field_Entity_ID", entitySource);
Source.Join.JoinField joinField = new Source.Join.JoinField(partQueryField, entityQueryField);
join.JoinFields.Add(joinField);
return partSource;
}
public void Test_ToString_ShouldUseAliases()
{
//---------------Set up test pack-------------------
const string sourceName = "mysource";
var source1 = new Source(sourceName);
Source field1Source = source1;
QueryField field1 = new QueryField("testfield", "testfield", field1Source);
Criteria criteria = new Criteria(field1, Criteria.ComparisonOp.Equals, "myvalue");
IDictionary<string, string> aliases = new Dictionary<string, string>() { { source1.ToString(), "a1" } };
CriteriaDB criteriaDb = new CriteriaDB(criteria);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
string whereClause = criteriaDb.ToString(sqlFormatter, value => "Param", aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase("a1.[testfield] = Param", whereClause);
}
public void Test_CreateSQL_ShouldUseAliasesInCriteria_WhenNotCriteria()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
const string sourceName = "mysource";
var source1 = new Source(sourceName);
selectQuery.Source = source1;
Source field1Source = source1;
QueryField field1 = new QueryField("testfield", "testfield", field1Source);
selectQuery.Fields.Add(field1.FieldName, field1);
selectQuery.Criteria = new Criteria(
null,
Criteria.LogicalOp.Not,
new Criteria(field1, Criteria.ComparisonOp.Is, null));
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase("SELECT a1.[testfield] FROM [mysource] a1 WHERE NOT (a1.[testfield] IS NULL)", statement.Statement.ToString());
}
public void Test_MergeTwoSources_CheckThatSecondMergeDoesntAffectSourceOfFirstField()
{
//---------------Set up test pack-------------------
Source originalSource = new Source("Allegation", "Allegation");
Source receivedDateSource1 = new Source("Allegation");
receivedDateSource1.JoinToSource(new Source("Request"));
Source institutionSource = new Source("Allegation");
Source reqSource = new Source("Request");
reqSource.JoinToSource(new Source("Institution"));
institutionSource.JoinToSource(reqSource);
QueryField receivedDate1Field = new QueryField("ReceivedDate", "ReceivedDate", receivedDateSource1);
QueryField institutionField = new QueryField("Name", "Name", institutionSource);
originalSource.MergeWith(receivedDate1Field.Source);
//---------------Assert preconditions---------------
Assert.AreEqual("Allegation.Request", receivedDate1Field.Source.ToString());
Assert.AreEqual("Allegation.Request.Institution", institutionField.Source.ToString());
//---------------Execute Test ----------------------
originalSource.MergeWith(institutionField.Source);
//---------------Test Result -----------------------
Assert.AreEqual("Allegation.Request", receivedDate1Field.Source.ToString());
Assert.AreEqual("Allegation.Request.Institution", institutionField.Source.ToString());
}
public void TestCreateSQL_WithJoin_TwoFields()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
QueryField fromField = new QueryField("FromField2", "FROM_FIELD2", source);
QueryField toField = new QueryField("ToField2", "TO_FIELD2", joinSource);
join.JoinFields.Add(new Source.Join.JoinField(fromField, toField));
Source.Join.JoinField joinField1 = join.JoinFields[0];
Source.Join.JoinField joinField2 = join.JoinFields[1];
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
//-------------Test Result ----------------------
string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3} AND a1.{4} = a2.{5})",
source.EntityName, joinSource.EntityName,
joinField1.FromField.FieldName, joinField1.ToField.FieldName,
joinField2.FromField.FieldName, joinField2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void GetFormattedStringWith_WhenConstructedWithQueryFieldWithNoTableName_ShouldReturnCorrectString(string left, string right)
{
//---------------Set up test pack-------------------
var queryField = new QueryField("property", "field", null);
var sut = Create(queryField);
var formatter = new SqlFormatter(left, right, "", "");
var expected = string.Join("", new[] { "count(", left, "field", right, ")" });
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, EmptyAliases());
//---------------Test Result -----------------------
Assert.AreEqual(expected, result);
}
public void GetFormattedStringWith_WhenConstructedWithQueryFieldWithTableName_ShouldReturnCorrectString(string left, string right)
{
//---------------Set up test pack-------------------
var queryField = new QueryField("property", "field", new Source("table"));
var sut = Create(queryField);
var formatter = new SqlFormatter(left, right, "", "");
var expected = string.Join("", new[] { "count(", left, "table", right, ".", left, "field", right, ")" });
var aliases = new Dictionary<string, string>();
aliases["table"] = "table";
//---------------Assert Precondition----------------
//---------------Execute Test ----------------------
var result = sut.GetFormattedStringWith(formatter, aliases);
//---------------Test Result -----------------------
Assert.AreEqual(expected, result);
}
public void Test_ToString_WithFieldHavingSource_WhenAliasMissing_ShouldThrowError()
{
//---------------Set up test pack-------------------
const string sourceName = "mysource";
const string propertyName = "testproperty";
QueryField queryField = new QueryField(propertyName, "testfield", new Source(sourceName));
Criteria criteria = new Criteria(queryField, Criteria.ComparisonOp.Equals, "myvalue");
IDictionary<string, string> aliases = new Dictionary<string, string>();
CriteriaDB criteriaDb = new CriteriaDB(criteria);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
var habaneroDeveloperException = Assert.Throws<HabaneroDeveloperException>(() =>
{
criteriaDb.ToString(sqlFormatter, value => "Param", aliases);
});
//---------------Test Result -----------------------
Assert.IsNotNull(habaneroDeveloperException);
var expectedMessage = string.Format("The source '{0}' for the property '{1}' " + "in the given criteria does not have an alias provided for it.", sourceName, queryField.PropertyName);
var expectedDeveloperMessage = expectedMessage
+ " The criteria object may have not been prepared correctly before the aliases were set up.";
Assert.AreEqual(expectedMessage, habaneroDeveloperException.Message);
Assert.AreEqual(expectedDeveloperMessage, habaneroDeveloperException.DeveloperMessage);
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
var mysource = new Source("mysource");
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("myjoinedtosource");
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
mysource.Joins.Add(new Source.Join(mysource, joinedTableSource));
mysource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
SourceDB sourceDB = new SourceDB(mysource);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
IDictionary<string, string> aliases = new Dictionary<string, string>() { { mysource.ToString(), "a1" }, { joinedTableSource.ToString(), "a2"} };
//---------------Execute Test ----------------------
string sql = sourceDB.CreateSQL(sqlFormatter, aliases);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"([mysource] a1 JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield])", sql);
}
public void TestMergeWith_IncludesInheritanceJoinFields()
{
//-------------Setup Test Pack ------------------
Source originalSource = new Source("FromSource", "FromSourceEntity");
Source otherSource = new Source("FromSource", "FromSourceEntity");
Source childSource = new Source("ToSource", "ToSourceEntity");
Source.Join join = new Source.Join(otherSource, childSource);
QueryField field1 = new QueryField("FromSourceProp1", "FromSourceProp1Field", otherSource);
QueryField field2 = new QueryField("ToSourceProp1", "ToSourceProp1Field", childSource);
otherSource.InheritanceJoins.Add(join);
join.JoinFields.Add(new Source.Join.JoinField(field1, field2));
//-------------Execute test ---------------------
originalSource.MergeWith(otherSource);
//-------------Test Result ----------------------
Assert.AreEqual(1, originalSource.InheritanceJoins.Count);
Assert.AreEqual(1, originalSource.InheritanceJoins[0].JoinFields.Count);
Assert.AreEqual(field1, originalSource.InheritanceJoins[0].JoinFields[0].FromField);
Assert.AreEqual(field2, originalSource.InheritanceJoins[0].JoinFields[0].ToField);
}
private static Source.Join CreateJoin(Source fromSource, Source toSource)
{
Source.Join join = new Source.Join(fromSource, toSource);
QueryField fromField = new QueryField("FromField", "FROM_FIELD", fromSource);
QueryField toField = new QueryField("ToField", "TO_FIELD", toSource);
join.JoinFields.Add(new Source.Join.JoinField(fromField, toField));
return join;
}
public void Test_CreateSQL_ShouldUseAliasesInOrderByClause_WhenOrderByFieldSourceIsSpecified()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
var mysource = new Source("mysource");
selectQuery.Source = mysource;
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
selectQuery.Fields.Add(fieldOnMySource.FieldName, fieldOnMySource);
var orderCriteriaField = OrderCriteriaField.FromString("testfield");
orderCriteriaField.Source = mysource;
selectQuery.OrderCriteria = new OrderCriteria();
selectQuery.OrderCriteria.Add(orderCriteriaField);
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"SELECT a1.[testfield] FROM [mysource] a1 ORDER BY a1.[testfield] ASC", statement.Statement.ToString());
}
private static void AssertQueryFieldsEqual(QueryField expectedQueryField, QueryField actualQueryField, string context)
{
Assert.AreEqual(expectedQueryField.Source, actualQueryField.Source, context + ".Source");
Assert.AreEqual(expectedQueryField.PropertyName, actualQueryField.PropertyName, context + ".PropertyName");
Assert.AreEqual(expectedQueryField.FieldName, actualQueryField.FieldName, context + ".FieldName");
}
public void Test_CreateSQL_ShouldUseAliasesInJoins()
{
//---------------Set up test pack-------------------
SelectQuery selectQuery = new SelectQuery();
var mysource = new Source("mysource");
selectQuery.Source = mysource;
QueryField fieldOnMySource = new QueryField("testfield", "testfield", mysource);
Source joinedTableSource = new Source("mysource");
joinedTableSource.JoinToSource(new Source("myjoinedtosource"));
QueryField fieldOnJoinedTableSource = new QueryField("testfield", "testfield", joinedTableSource);
joinedTableSource.Joins[0].JoinFields.Add(new Source.Join.JoinField(fieldOnMySource, fieldOnJoinedTableSource));
selectQuery.Fields.Add(fieldOnMySource.FieldName, fieldOnMySource);
selectQuery.Criteria = new Criteria(fieldOnJoinedTableSource, Criteria.ComparisonOp.Equals, "myvalue");
SelectQueryDB query = new SelectQueryDB(selectQuery, DatabaseConnection.CurrentConnection);
SqlFormatter sqlFormatter = new SqlFormatter("[", "]", "", "LIMIT");
//---------------Execute Test ----------------------
ISqlStatement statement = query.CreateSqlStatement(sqlFormatter);
//---------------Test Result -----------------------
StringAssert.AreEqualIgnoringCase(
"SELECT a1.[testfield] FROM ([mysource] a1 " +
"JOIN [myjoinedtosource] a2 on a1.[testfield] = a2.[testfield]) " +
"WHERE a2.[testfield] = ?Param0", statement.Statement.ToString());
}
public void TestMergeWith()
{
//-------------Setup Test Pack ------------------
Source originalSource = new Source("FromSource", "FromSourceEntity");
Source.JoinList originalJoinList = new Source.JoinList(originalSource);
Source otherSource = new Source("FromSource", "FromSourceEntity");
Source.JoinList joinList = new Source.JoinList(otherSource);
Source childSource = new Source("ToSource", "ToSourceEntity");
Source.Join join = joinList.AddNewJoinTo(childSource, Source.JoinType.InnerJoin);
QueryField field1 = new QueryField("FromSourceProp1", "FromSourceProp1Field", otherSource);
QueryField field2 = new QueryField("ToSourceProp1", "ToSourceProp1Field", childSource);
join.JoinFields.Add(new Source.Join.JoinField(field1, field2));
//-------------Execute test ---------------------
originalJoinList.MergeWith(joinList);
//-------------Test Result ----------------------
Assert.AreEqual(1, originalJoinList.Count);
Assert.AreEqual(1, originalJoinList[0].JoinFields.Count);
Assert.AreEqual(field1, originalJoinList[0].JoinFields[0].FromField);
Assert.AreEqual(field2, originalJoinList[0].JoinFields[0].ToField);
}
