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_NoFields() { //-------------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); source.Joins.Add(join); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- Exception exception = null; try { sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); } catch (Exception ex) { exception = ex; } //-------------Test Result ---------------------- Assert.IsNotNull(exception, "An error was expected when creating SQL with joins that have no fields"); Assert.IsInstanceOf(typeof(HabaneroDeveloperException), exception); string expectedMessage = string.Format("SQL cannot be created for the source '{0}' because it has a join to '{1}' without join fields", sourceDB.Name, join.ToSource.Name); StringAssert.Contains(expectedMessage, exception.Message); }
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 TestCreateSQL_Simple_WithDelimiter() { //-------------Setup Test Pack ------------------ const string tableName = "MY_SOURCE"; Source source = new Source("MySource", tableName); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(new SqlFormatter("[", "]", "TOP", ""), CreateAliases(source)); //-------------Test Result ---------------------- Assert.AreEqual(string.Format("[{0}] a1", tableName), sql); }
public void TestCreateSQL_Simple() { //-------------Setup Test Pack ------------------ string tableName = "MY_SOURCE"; Source source = new Source("MySource", tableName); SourceDB sourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = sourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source)); //-------------Test Result ---------------------- Assert.AreEqual(tableName + " a1", sql); }
public void TestCreateSQL_LeftJoin() { //-------------Setup Test Pack ------------------ Source source = new Source("MySource", "MY_SOURCE"); Source joinSource = new Source("JoinSource", "MY_JOINED_TABLE"); Source.Join join = CreateAndAddJoin(source, joinSource); join.JoinType = Source.JoinType.LeftJoin; SourceDB leftJoinSourceDB = new SourceDB(source); //-------------Execute test --------------------- string sql = leftJoinSourceDB.CreateSQL(GetSqlFormatter(), CreateAliases(source, joinSource)); //-------------Test Result ---------------------- StringAssert.Contains("LEFT JOIN", sql); }
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); }
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); }
private void AppendFrom(StringBuilder builder) { SourceDB source = new SourceDB(_selectQuery.Source); builder.AppendFormat(" FROM {0}", source.CreateSQL(_sqlFormatter, Aliases)); }