public void TestCreateSQL_WithJoin_SecondLevel_TwoBranches()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source branch1 = new Source("JoinBranch1", "MY_BRANCH_1");
Source branch2 = new Source("JoinBranch2", "MY_BRANCH_2");
Source.Join join = CreateAndAddJoin(source, joinSource);
Source.Join branchJoin1 = CreateAndAddJoin(joinSource, branch1);
Source.Join branchJoin2 = CreateAndAddJoin(joinSource, branch2);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, branch1, branch2));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
Source.Join.JoinField joinFieldBranch1 = branchJoin1.JoinFields[0];
Source.Join.JoinField joinFieldBranch2 = branchJoin2.JoinFields[0];
string expectedSql = string.Format("((({0} a1 JOIN {1} a2 ON a1.{4} = a2.{5}) JOIN {2} a3 ON a2.{6} = a3.{7}) JOIN {3} a4 ON a2.{8} = a4.{9})",
sourceDB.EntityName, joinSource.EntityName, branch1.EntityName, branch2.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName,
joinFieldBranch1.FromField.FieldName, joinFieldBranch1.ToField.FieldName,
joinFieldBranch2.FromField.FieldName, joinFieldBranch2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void TestCreateSQL_WithInheritanceJoin_TwoLevels()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source joinSource2 = new Source("JoinSource2", "MY_JOINED_TABLE_2");
Source.Join join = CreateAndAddInheritanceJoin(source, joinSource);
Source.Join join2 = CreateAndAddInheritanceJoin(joinSource, joinSource2);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource, joinSource2));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
Source.Join.JoinField joinField2 = join2.JoinFields[0];
string expectedSql = string.Format("(({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3}) JOIN {4} a3 ON a2.{5} = a3.{6})",
sourceDB.EntityName,
joinSource.EntityName, joinField.FromField.FieldName, joinField.ToField.FieldName,
joinSource2.EntityName, joinField2.FromField.FieldName, joinField2.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
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 TestPrepareSource_ExistingSource_Relationship_TwoLevels()
{
//---------------Set up test pack-------------------
IClassDef engineClassDef = Engine.LoadClassDef_IncludingCarAndOwner();
Source carSource = Source.FromString("Car.Owner");
Source source = carSource;
//---------------Execute Test ----------------------
QueryBuilder.PrepareSource(engineClassDef, ref source);
//---------------Test Result -----------------------
Assert.IsNotNull(source);
Assert.AreNotSame(carSource, source);
Assert.AreEqual("Engine", source.Name);
Assert.AreEqual("Table_Engine", source.EntityName);
Assert.AreEqual(1, source.Joins.Count);
Source.Join join = source.Joins[0];
Source childSource = join.ToSource;
Assert.IsNotNull(childSource);
Assert.AreSame(carSource, childSource);
Assert.AreEqual("Car", childSource.Name);
Assert.AreEqual("car_table", childSource.EntityName);
Assert.AreEqual(1, join.JoinFields.Count);
Source.Join.JoinField joinField = join.JoinFields[0];
QueryField fromField = joinField.FromField;
Assert.AreSame(source, fromField.Source);
Assert.AreEqual("CarID", fromField.PropertyName);
Assert.AreEqual("CAR_ID", fromField.FieldName);
QueryField toField = joinField.ToField;
Assert.AreSame(childSource, toField.Source);
Assert.AreEqual("CarID", toField.PropertyName);
Assert.AreEqual("CAR_ID", toField.FieldName);
Assert.AreEqual(1, childSource.Joins.Count);
Source.Join childJoin = childSource.Joins[0];
Source grandChildSource = childJoin.ToSource;
Assert.IsNotNull(grandChildSource);
Assert.AreSame(carSource.ChildSource, grandChildSource);
Assert.AreEqual("Owner", grandChildSource.Name);
Assert.AreEqual("contact_person", grandChildSource.EntityName);
Assert.AreEqual(1, childJoin.JoinFields.Count);
Source.Join.JoinField childJoinField = childJoin.JoinFields[0];
QueryField childFromField = childJoinField.FromField;
Assert.AreSame(carSource, childFromField.Source);
Assert.AreEqual("OwnerId", childFromField.PropertyName);
Assert.AreEqual("OWNER_ID", childFromField.FieldName);
QueryField childToField = childJoinField.ToField;
Assert.AreSame(grandChildSource, childToField.Source);
Assert.AreEqual("ContactPersonID", childToField.PropertyName);
Assert.AreEqual("ContactPersonID", childToField.FieldName);
}
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 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 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 TestCreateSQL_WithJoin()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("({0} a1 JOIN {1} a2 ON a1.{2} = a2.{3})", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
public void TestCreateSQL_WithJoin_WithDelimiter()
{
//-------------Setup Test Pack ------------------
Source source = new Source("MySource", "MY_SOURCE");
Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE");
Source.Join join = CreateAndAddJoin(source, joinSource);
SourceDB sourceDB = new SourceDB(source);
//-------------Execute test ---------------------
SqlFormatter myFormatter = new SqlFormatter("[", "]", "TOP", "");
string sql = sourceDB.CreateSQL(myFormatter, CreateAliases(source, joinSource));
//-------------Test Result ----------------------
Source.Join.JoinField joinField = join.JoinFields[0];
string expectedSql = string.Format("([{0}] a1 JOIN [{1}] a2 ON a1.[{2}] = a2.[{3}])", source.EntityName, joinSource.EntityName,
joinField.FromField.FieldName, joinField.ToField.FieldName);
Assert.AreEqual(expectedSql, sql);
}
private static void AssertJoinFieldsListEqual(List <Source.Join.JoinField> expectedJoinFields, List <Source.Join.JoinField> actualJoinFields, string context)
{
Assert.AreEqual(expectedJoinFields.Count, actualJoinFields.Count, context + ".Count");
foreach (Source.Join.JoinField expectedJoinField in expectedJoinFields)
{
string expectedJoinToPropertyName = expectedJoinField.ToField.PropertyName;
string expectedJoinFromPropertyName = expectedJoinField.FromField.PropertyName;
Source.Join.JoinField actualJoinField = actualJoinFields.Find(delegate(Source.Join.JoinField joinField)
{
return(joinField.FromField.PropertyName == expectedJoinFromPropertyName &&
joinField.ToField.PropertyName == expectedJoinToPropertyName);
});
string expectedJoinFieldDesc = String.Format("from {0}.{1} to {2}.{3}",
expectedJoinField.FromField.Source.Name,
expectedJoinFromPropertyName,
expectedJoinField.ToField.Source.Name,
expectedJoinToPropertyName);
Assert.IsNotNull(actualJoinField, string.Format(
"{0}: Could not find a join field {1}.", context, expectedJoinFieldDesc));
AssertJoinFieldsEqual(expectedJoinField, actualJoinField,
string.Format("{0}(JoinField {1})", context, expectedJoinFieldDesc));
}
}
public void TestPrepareSource_ExistingSource_RelationshipName()
{
//---------------Set up test pack-------------------
IClassDef engineClassDef = Engine.LoadClassDef_IncludingCarAndOwner();
Source originalSource = new Source("Car", null);
Source source = originalSource;
//---------------Execute Test ----------------------
QueryBuilder.PrepareSource(engineClassDef, ref source);
//---------------Test Result -----------------------
Assert.IsNotNull(source);
Assert.AreNotSame(originalSource, source);
Assert.AreEqual("Engine", source.Name);
Assert.AreEqual("Table_Engine", source.EntityName);
Assert.AreEqual(1, source.Joins.Count);
Source.Join join = source.Joins[0];
Source childSource = join.ToSource;
Assert.IsNotNull(childSource);
Assert.AreSame(originalSource, childSource);
Assert.AreEqual("Car", childSource.Name);
Assert.AreEqual("car_table", childSource.EntityName);
Assert.AreEqual(1, join.JoinFields.Count);
Source.Join.JoinField field = join.JoinFields[0];
QueryField fromField = field.FromField;
Assert.AreSame(source, fromField.Source);
Assert.AreEqual("CarID", fromField.PropertyName);
Assert.AreEqual("CAR_ID", fromField.FieldName);
QueryField toField = field.ToField;
Assert.AreSame(childSource, toField.Source);
Assert.AreEqual("CarID", toField.PropertyName);
Assert.AreEqual("CAR_ID", toField.FieldName);
}
private static void AssertJoinFieldsEqual(Source.Join.JoinField expectedJoinField, Source.Join.JoinField actualJoinField, string context)
{
AssertQueryFieldsEqual(expectedJoinField.FromField, actualJoinField.FromField, context + ".FromField");
AssertQueryFieldsEqual(expectedJoinField.ToField, actualJoinField.ToField, context + ".ToField");
}
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;
}
