public void ItShouldBePossibleToPartitionByMultipleColumns() { var expr = S.SelectNode( S.Select( S.Col <Person>("Name"), S.WinCol( AggregateType.Max, S.Col <Person>("Age"), partitionBy: new[] { S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId"), })), S.From <Person>() ); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr); Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, MAX(PersonRef.Age) OVER (PARTITION BY PersonRef.Name, PersonRef.LivesAtHouseId) FROM Person PersonRef")); }
public void ItShouldBePossibleToBothPartitionAndOrderByAColumn() { var expr = S.SelectNode( S.Select( S.Col <Person>("Name"), S.WinCol( AggregateType.Sum, S.Col <Person>("Age"), partitionBy: new[] { S.Col <Person>("Name") }, orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), Direction.Ascending) } )), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr); Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (PARTITION BY PersonRef.Name ORDER BY PersonRef.Age ASC) FROM Person PersonRef")); }
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 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 ItShouldBePossibleToUseAndAndOrOnAWhereStatementOnADeleteStatement() { var stream = S.DeleteNode( S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(42)), S.And(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'"))), S.Or(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Henrik'"))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo( "DELETE FROM Person PersonRef " + "WHERE PersonRef.Age > 42 " + "AND PersonRef.Name = 'Kalle' " + "OR PersonRef.Name = 'Henrik'")); }
public void ItCanGenerateSqlForToLower() { var stream = S.SelectNode( S.Select(S.Func(AST.FunctionType.Lower, new[] { S.Col <Person>("Name") })), S.From <Person>(), S.Where( S.BinExp( S.Func(AST.FunctionType.Lower, new[] { S.Col <Person>("Name") }), BinaryOperation.Equal, S.Const("'kalle'")) )); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); string expected = "SELECT LOWER(PersonRef.Name) FROM Person PersonRef WHERE LOWER(PersonRef.Name) = 'kalle'"; Assert.That(result, Is.EqualTo(expected)); }
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 ItShouldBePossibleToJoinATableToItself() { var child = Table <Person> .WithReferenceName("child"); var parent = Table <Person> .WithReferenceName("parent"); var stream = S.SelectNode( S.Select(S.Col(child, "Name"), S.Col(parent, "Name")), S.From( child, S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id"))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("SELECT child.Name, parent.Name FROM Person child " + "INNER JOIN Person parent ON child.ParentId = parent.Id")); }
public void ItShouldBePossibleToOrderByMultipleColumns() { var expr = S.SelectNode( S.Select( S.Col <Person>("Name"), S.WinCol( AggregateType.Sum, S.Col <Person>("Age"), orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), Direction.Ascending, NullsOrdering.NullsLast), S.OrderClause(S.Col <Person>("Name"), Direction.Descending), })), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr); Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER (ORDER BY PersonRef.Age ASC NULLS LAST, PersonRef.Name DESC) FROM Person PersonRef")); }
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 ItShouldBePossibleToDoPagingWithACommonTableExpression() { // To be honest, this case should be covered by the other test cases so this test case is a bit redundant. // However, using common table expressions for paging is a quite common technique and it's good to know for sure that it works as expected, so // let's do some bad practice testing and test something that's already covered by other tests! var expr = S.SelectNode( S.With( S.TableDef <PersonLitePagingResult>( S.Select( S.Col <Person>("Name"), S.Col <Person>("Age"), S.AliasedCol( "RowNumber", AggregateType.RowNumber, S.Null(), orderBy: new[] { S.OrderClause(S.Col <Person>("Age"), AST.Direction.Ascending) })), S.From <Person>()) ), S.Select(S.Col <PersonLitePagingResult>("Name"), S.Col <PersonLitePagingResult>("Age")), S.From <PersonLitePagingResult>(), S.Where( S.BinExp( S.BinExp(S.Col <PersonLitePagingResult>("RowNumber"), BinaryOperation.GreaterThanOrEqual, S.Const(10)), BinaryOperation.And, S.BinExp(S.Col <PersonLitePagingResult>("RowNumber"), BinaryOperation.LessThan, S.Const(20)) ))); var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, expr); var expected = "WITH PersonLitePagingResult AS (" + "SELECT PersonRef.Name, PersonRef.Age, ROW_NUMBER() OVER (ORDER BY PersonRef.Age ASC) AS RowNumber " + "FROM Person PersonRef" + ") " + "SELECT PersonLitePagingResultRef.Name, PersonLitePagingResultRef.Age " + "FROM PersonLitePagingResult PersonLitePagingResultRef " + "WHERE PersonLitePagingResultRef.RowNumber >= 10 AND PersonLitePagingResultRef.RowNumber < 20"; Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldBePossibleToGroupByColumnsFromSeveralTables() { var stream = S.SelectNode( S.Select(S.Col <Person>("Age"), S.Col <Car>("Brand"), S.Aggregate(AggregateType.Count, S.Null())), S.From <Person>( S.Join <Person, Car>( JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))), groupBy: S.GroupBy(S.Col <Person>("Age"), S.Col <Car>("Brand"))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( "SELECT PersonRef.Age, CarRef.Brand, COUNT(*) " + "FROM Person PersonRef " + "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " + "GROUP BY PersonRef.Age, CarRef.Brand")); }
public void ItShouldBePossibleToMixAliasedAndUnAliasedColumns() { var stream = S.SelectNode( S.Select( S.Col <Person>("Name"), S.AliasedCol <Person>( AggregateType.Max, S.Col <Person>("Age"), "MaxAge"), S.AliasedCol <Person>( AggregateType.Min, S.Col <Person>("Age"), "MinAge")), S.From <Person>(), groupBy: S.GroupBy(S.Col <Person>("Name"))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.Name, MAX(PersonRef.Age) AS MaxAge, MIN(PersonRef.Age) AS MinAge FROM Person PersonRef GROUP BY PersonRef.Name")); }
public void ItShouldBePossibleToUseASingleHavingExpression() { var stream = S.SelectNode( S.Select(S.Col <Person>("Name"), S.Aggregate(AggregateType.Avg, S.Col <Person>("Age"))), S.From <Person>(), groupBy: S.GroupBy( new[] { S.Col <Person>("Name") }, S.And(S.BinExp(S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")), BinaryOperation.GreaterThan, S.Const(42))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( "SELECT PersonRef.Name, AVG(PersonRef.Age) " + "FROM Person PersonRef " + "GROUP BY PersonRef.Name " + "HAVING AVG(PersonRef.Age) > 42")); }
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 ItShouldBePossibleToSqlifyAWhereWithAndAndOr() { 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.Const(42)), BinaryOperation.Or, S.BinExp( S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(10)), BinaryOperation.And, 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 OR PersonRef.Age < 10 AND PersonRef.Name = 'Karl'")); }
public void ItShouldBePossibleToJoinATableToItself() { var child = Table <Person> .WithReferenceName("child"); var parent = Table <Person> .WithReferenceName("parent"); var expr = Select.Column(child, p => new { p.Name }) .Column(parent, p => new { p.Name }) .From(child) .InnerJoin(child, parent, (p1, p2) => p1.ParentId == p2.Id); var result = expr.ToSqlExpression(); var expected = S.SelectNode( S.Select(S.Col(parent, "Name"), S.Col(child, "Name")), S.From( child, S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id"))))); Assert.That(result, Is.EqualTo(expected)); }
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 ItShouldHandleASimpleMinus() { var stream = AST.SqlExpression.NewSelect( AST.SelectExpression.NewPlain( AST.PlainSelectExpression.NewSet( S.Minus( S.PlainSelect( S.Select(S.Star <Person>()), S.From <Person>()), S.PlainSelect( S.Select(S.Star <Person>()), S.From <Person>(), S.Where( S.BinExp(S.Col <Person>("Age"), AST.BinaryOperation.GreaterThan, S.Const(42)))))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); var expected = "SELECT PersonRef.* FROM Person PersonRef " + "MINUS " + "SELECT PersonRef.* FROM Person PersonRef WHERE PersonRef.Age > 42"; Assert.That(result, Is.EqualTo(expected)); }
public void ItCanGenerateSqlForAConcatFunctionCall() { var stream = S.SelectNode( S.Select(S.Func(AST.FunctionType.Concat, new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), S.Col <Person>("Name") })), S.From <Person>( S.Join <Person, Car>( AST.JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))), S.Where( S.BinExp( S.Func(AST.FunctionType.Concat, new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), }), BinaryOperation.Equal, S.Const("'kallesaab'")))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); string expected = "SELECT CONCAT(PersonRef.Name, CarRef.Brand, PersonRef.Name) " + "FROM Person PersonRef " + "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " + "WHERE CONCAT(PersonRef.Name, CarRef.Brand) = 'kallesaab'"; 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")); }
public void ItShouldBePossibleToUseMultipleHavingExpressions() { var stream = S.SelectNode( S.Select( S.Col <Person>("Name"), S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")), S.Col <Car>("Brand"), S.Aggregate(AggregateType.Min, S.Col <Car>("Age"))), S.From <Person>( S.Join <Person, Car>(JoinType.InnerJoin, S.BinExp(S.Col <Person>("Id"), BinaryOperation.Equal, S.Col <Car>("DriverId")))), groupBy: S.GroupBy( new[] { S.Col <Person>("Name"), S.Col <Car>("Brand"), }, S.And(S.BinExp(S.Aggregate(AggregateType.Avg, S.Col <Person>("Age")), BinaryOperation.GreaterThan, S.Const(42))), S.And(S.BinExp(S.Aggregate(AggregateType.Min, S.Col <Car>("Age")), BinaryOperation.GreaterThan, S.Const(2))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( "SELECT PersonRef.Name, AVG(PersonRef.Age), CarRef.Brand, MIN(CarRef.Age) " + "FROM Person PersonRef " + "INNER JOIN Car CarRef ON PersonRef.Id = CarRef.DriverId " + "GROUP BY PersonRef.Name, CarRef.Brand " + "HAVING AVG(PersonRef.Age) > 42 " + "AND MIN(CarRef.Age) > 2")); }
public void ItShouldBePossibleToUpdateMultipleInPlace() { var stream = S.UpdateNode( S.UpdHead <Person>( S.Ust <Person>("Age", S.BinExp(S.Col <Person>("Age"), BinaryOperation.Subtract, S.Const(2))), S.Ust <Person>("Name", S.BinExp(S.Const("'Kalle'"), BinaryOperation.Add, S.Col <Person>("Name"))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Age = Age - 2, Name = 'Kalle' + Name")); }
public void ItShouldBePossibleToGroupByMultipleColumns() { var stream = S.SelectNode( S.Select(S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId"), S.Aggregate(AggregateType.Min, S.Col <Person>("Age"))), S.From <Person>(), groupBy: S.GroupBy(S.Col <Person>("Name"), S.Col <Person>("LivesAtHouseId"))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( @"SELECT PersonRef.Name, PersonRef.LivesAtHouseId, MIN(PersonRef.Age) FROM Person PersonRef " + "GROUP BY PersonRef.Name, PersonRef.LivesAtHouseId")); }
public void ItShouldBePossibleToInnerJoinATableOnItselfSeveralTimes() { var child = Table <Person> .WithReferenceName("child"); var parent = Table <Person> .WithReferenceName("parent"); var grandparent = Table <Person> .WithReferenceName("grandparent"); var expr = Select.Column(grandparent, gp => new { gp.Name, gp.Age }) .From(child) .InnerJoin(child, parent, (c, p) => c.ParentId == p.Id) .InnerJoin(child, parent, grandparent, (c, p, gp) => p.ParentId == gp.Id && c.LivesAtHouseId == gp.LivesAtHouseId); var result = expr.ToSqlExpression(); var expected = S.SelectNode( S.Select( S.Col(grandparent, "Name"), S.Col(grandparent, "Age")), S.From( child, S.Join( child, parent, grandparent, JoinType.InnerJoin, S.BinExp( S.BinExp(S.Col(parent, "ParentId"), BinaryOperation.Equal, S.Col(grandparent, "Id")), BinaryOperation.And, S.BinExp(S.Col(child, "LivesAtHouseId"), BinaryOperation.Equal, S.Col(grandparent, "LivesAtHouseId")))), S.Join(child, parent, JoinType.InnerJoin, S.BinExp(S.Col(child, "ParentId"), BinaryOperation.Equal, S.Col(parent, "Id"))))); 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"))) ))); }