public void ItShouldHandleASimpleUnionAll() { var stream = AST.SqlExpression.NewSelect( AST.SelectExpression.NewPlain( AST.PlainSelectExpression.NewSet( S.UnionAll( S.PlainSelect( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Age"), AST.BinaryOperation.GreaterThan, S.Const(42)))), S.PlainSelect( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Name"), AST.BinaryOperation.Equal, S.Const("'Kalle'")))) )))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); var expected = "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42 " + "UNION ALL " + "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Name = 'Kalle'"; Assert.That(result, Is.EqualTo(expected)); }
private SqlExpression GetExpectedJoinStream(JoinType joinKeyword) { return (S.SelectNode( S.Select(S.Star <Person>(), S.Star <Car>()), S.From <Person>( S.Join <Person, Car>(joinKeyword, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId"))) ))); }
public void ItShouldBeAbleToParameterizeAQuery() { var stream = S.SelectNode( S.Select(S.Star<Person>()), S.From<Person>(), S.Where(S.BinExp(S.Col<Person>("Age"), BinaryOperation.GreaterThan, S.Param("age")))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > @age")); }
public void ItShouldBePossibleToSpecifyNullsFirstOrdering() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), orderBy: S.OrderBy(S.OrderClause(S.Col <Person>("Age"), Direction.Ascending, NullsOrdering.NullsFirst))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef ORDER BY PersonRef.Age ASC NULLS FIRST")); }
public void ItShouldBePossibleToSqlifyASimpleSelect() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef")); }
public void ItShouldBePossibleToOrderDescendingByAColumn() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), orderBy: S.OrderBy(S.OrderClause(S.Col <Person>("Age"), Direction.Descending))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef ORDER BY PersonRef.Age DESC")); }
public void ItShouldIncludeTheSchemaInTheGeneratedSql() { var stream = S.SelectNode( S.Select(S.Star <Employee>()), S.From <Employee>(), S.Where(S.BinExp(S.Col <Employee>("Salary"), BinaryOperation.GreaterThan, S.Const(25000)))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT EmployeeRef.* FROM HumanResources.Employee EmployeeRef WHERE EmployeeRef.Salary > 25000")); }
public void ItShouldBePossibleToSqlifyAWhere() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42)))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42")); }
public void ItCanSupportFlippingOrdersForInvertedComparisonWithNull() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Null(), BinaryOperation.NotEqual, S.Col <Person>("Age")))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age IS NOT NULL")); }
public void ItShouldBePossibleToSqlifyAWhereWithAndOrsThatIsNotInBinaryExpressions() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(42)), S.And(S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(12))), S.Or(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Karl'"))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age < 42 AND PersonRef.Age > 12 OR PersonRef.Name = 'Karl'")); }
public void ItShouldBePossibleToSqlifyAQueryWithAnInSubExpression() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp( S.Col <Person>("Id"), BinaryOperation.In, S.SubExp( S.Select(S.Col <Car>("DriverId")), S.From <Car>())))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Id IN (SELECT CarRef.DriverId FROM Car CarRef)")); }
public void ItShouldBePossibleToSqlifyACompareAgainstASubExpressionInAWhereExpression() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp( S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.SubExp( S.Select(S.Col <Car>("Age")), S.From <Car>(), S.Where(S.BinExp(S.Col <Car>("Id"), BinaryOperation.Equal, S.Const(42))))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > (SELECT CarRef.Age FROM Car CarRef WHERE CarRef.Id = 42)")); }
public void ItShouldBeAbleToSqlifyMultipleTableJoins() { var stream = S.SelectNode( S.Select(S.Star <Person>(), S.Star <Car>(), S.Star <House>()), S.From <House>( S.Join <House, Person>(JoinType.InnerJoin, S.BinExp(S.Col <House>("Id"), BinaryOperation.Equal, S.Col <Person>("LivesAtHouseId"))), S.Join <Person, Car>(JoinType.LeftJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId"))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( @"SELECT PersonRef.*, CarRef.*, HouseRef.* " + "FROM House HouseRef " + "INNER JOIN Person PersonRef ON HouseRef.Id = PersonRef.LivesAtHouseId " + "LEFT JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId")); }
public void ItShouldBePossibleToDoARecursiveCommonTableExpression() { var stream = S.SelectNode( S.With( S.TableDef <RecursivePerson>( S.UnionAll( S.PlainSelect( S.Select( S.AliasedCol(S.Const(0), "Level"), S.Col <Person>("Name"), S.Col <Person>("ParentId")), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'")))), S.PlainSelect( S.Select( S.AliasedCol( S.BinExp(S.Col <RecursivePerson>("Level"), BinaryOperation.Add, S.Const(1)), "Level"), S.Col <Person>("Name"), S.Col <Person>("ParentId")), S.From <Person>( S.Join <Person, RecursivePerson>( AST.JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <RecursivePerson>("ParentId")))))))), S.PlainSelect( S.Select(S.Star <RecursivePerson>()), S.From <RecursivePerson>())); var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, stream); var expected = "WITH RecursivePerson AS (" + "SELECT 0 AS Level, PersonRef.Name, PersonRef.ParentId FROM Person PersonRef WHERE PersonRef.Name = 'Kalle' " + "UNION ALL " + "SELECT RecursivePersonRef.Level + 1 AS Level, PersonRef.Name, PersonRef.ParentId " + "FROM Person PersonRef " + "INNER JOIN RecursivePerson RecursivePersonRef ON PersonRef.Id = RecursivePersonRef.ParentId" + ") " + "SELECT RecursivePersonRef.* FROM RecursivePerson RecursivePersonRef"; Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldBePossibleToOrderBySeveralColumns() { var stream = S.SelectNode( S.Select(S.Star <Person>(), S.Star <Car>()), S.From <Person>( S.Join <Person, Car>(JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))), orderBy: S.OrderBy( S.OrderClause(S.Col <Person>("Age"), Direction.Ascending), S.OrderClause(S.Col <Car>("Brand"), Direction.Descending))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( @"SELECT PersonRef.*, CarRef.* FROM Person PersonRef " + "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " + "ORDER BY PersonRef.Age ASC, CarRef.Brand DESC")); }
public void ItShouldAddParensWhenNecessaryToPreserveAndOrPredecence() { var stream = S.SelectNode( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp( S.BinExp( S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(10)), BinaryOperation.Or, S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Karl'")) ), BinaryOperation.And, S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42)) ))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.* FROM Person PersonRef WHERE (PersonRef.Age < 10 OR PersonRef.Name = 'Karl') AND PersonRef.Age > 42")); }
public void ItShouldBePossibleToInnerJoinATableOnSeveralOtherTables() { var expr = Select.Star <Car>() .From <House>() .InnerJoin <House, Person>((h, p) => h.Id == p.LivesAtHouseId) .InnerJoin <House, Person, Car>((h, p, c) => c.Brand == h.Address && p.LivesAtHouseId == h.Id); var result = expr.ToSqlExpression(); var expected = S.SelectNode( S.Select(S.Star <Car>()), S.From <House>( S.Join <House, Person, Car>( JoinType.InnerJoin, S.BinExp( S.BinExp(S.Col <Car>("Brand"), BinaryOperation.Equal, S.Col <House>("Address")), BinaryOperation.And, S.BinExp(S.Col <Person>("LivesAtHouseId"), BinaryOperation.Equal, S.Col <House>("Id")))), S.Join <House, Person>( JoinType.InnerJoin, S.BinExp(S.Col <House>("Id"), BinaryOperation.Equal, S.Col <Person>("LivesAtHouseId"))))); Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldBePossibleToUseTheParameterInMoreComplexExpressions() { var stream = S.SelectNode( S.Select(S.Star<Person>()), S.From<Person>(), S.Where( S.BinExp( S.BinExp(S.Col<Person>("Age"), BinaryOperation.GreaterThan, S.Param("age")), BinaryOperation.And, S.BinExp(S.Param("age"), BinaryOperation.LessThan, S.Const(90)) ), S.Or(S.BinExp(S.Col<Person>("Age"), BinaryOperation.LessThan, S.Param("age"))) )); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo( @"SELECT PersonRef.* " + "FROM Person PersonRef " + "WHERE PersonRef.Age > @age "+ "AND @age < 90 " + "OR PersonRef.Age < @age")); }