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 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)); }
public void ItShouldBePossibleToInsertMultipleValuesInOneStatement() { var stream = S.InsertNode( S.InsHead <Person>("Age", "Id", "LivesAtHouseId", "Name", "ParentId"), S.InsVal( S.InsConst(42), S.InsConst(2), S.InsConst(5), S.InsConst("'Kalle'"), S.InsConst(0)), S.InsVal( S.InsConst(12), S.InsConst(3), S.InsConst(3), S.InsConst("'Pelle'"), S.InsConst(2) ) ); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo("INSERT INTO Person (Age, Id, LivesAtHouseId, Name, ParentId) " + "VALUES (42, 2, 5, 'Kalle', 0), (12, 3, 3, 'Pelle', 2)")); }
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)); }
public void ItShouldBePossibleToDeleteWithASubQuery() { var stream = S.DeleteNode( S.From <Car>(), S.Where( S.BinExp( S.Col <Car>("DriverId"), BinaryOperation.In, S.SubExp( S.Select(S.Col <Person>("Id")), S.From <Person>(), S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(18))))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That( result, Is.EqualTo( "DELETE FROM Car CarRef " + "WHERE CarRef.DriverId IN (" + "SELECT PersonRef.Id " + "FROM Person PersonRef " + "WHERE PersonRef.Age < 18" + ")")); }
public void ItShouldBePossibleToInsertMultipleValuesInOneStatement() { var val1 = new Person { Id = 2, Name = "Kalle", Age = 42, LivesAtHouseId = 5, ParentId = 0 }; var val2 = new Person { Id = 3, Name = "Pelle", Age = 12, LivesAtHouseId = 3, ParentId = 2 }; var expr = Insert.Into <Person>() .AllColumns .Values(val1, val2); var result = expr.ToSqlExpression(); var expected = S.InsertNode( S.InsHead <Person>("Age", "Id", "LivesAtHouseId", "Name", "ParentId"), S.InsVal( S.InsConst(12), S.InsConst(3), S.InsConst(3), S.InsConst("'Pelle'"), S.InsConst(2) ), S.InsVal( S.InsConst(42), S.InsConst(2), S.InsConst(5), S.InsConst("'Kalle'"), S.InsConst(0)) ); Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldBePossibleToSelectFromACommonTableExpression() { var expr = S.SelectNode( S.With( S.TableDef <PersonLite>( S.Select(S.Col <Person>("Name"), S.Col <Person>("Age")), S.From <Person>(), S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(15))))), S.Select(S.Col <PersonLite>("Name")), S.From(new TableIdentifier <PersonLite>()), S.Where(S.BinExp(S.Col <PersonLite>("Age"), BinaryOperation.Equal, S.Const(42))) ); var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, expr); var expected = "WITH PersonLite AS (" + "SELECT PersonRef.Name, PersonRef.Age " + "FROM Person PersonRef " + "WHERE PersonRef.Age > 15" + ") " + "SELECT PersonLiteRef.Name " + "FROM PersonLite PersonLiteRef " + "WHERE PersonLiteRef.Age = 42"; Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldBePossibleToGenerateADeleteAllRowsFromTableStatement() { var stream = S.DeleteNode(S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"DELETE FROM Person PersonRef")); }
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 ItShouldBePossibleToPutAWhereExpressionOnADeleteStatement() { var stream = S.DeleteNode( 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(@"DELETE FROM Person PersonRef WHERE PersonRef.Age > 42")); }
public void ItShouldBePossibleToGroupByASingleColumn() { var stream = S.SelectNode( S.Select(S.Col <Person>("Name"), S.Aggregate(AggregateType.Max, S.Col <Person>("Age"))), 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) FROM Person PersonRef GROUP BY PersonRef.Name")); }
public void ItShouldBePossibleToUpdateInPlace() { var stream = S.UpdateNode( S.UpdHead <Person>( S.Ust <Person>("Age", S.BinExp(S.Col <Person>("Age"), BinaryOperation.Add, S.Const(1))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Age = Age + 1")); }
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 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 ItShouldBePossibleToPerformAGlobalUpdate() { var stream = S.UpdateNode( S.UpdHead <Person>( S.Ust <Person>("Name", "'Kalle'"))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Name = 'Kalle'")); }
public void ItShouldBePossibleToPerformAConditionalUpdate() { var stream = S.UpdateNode( S.UpdHead <Person>(S.Ust <Person>("Age", 42)), S.Where(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'")))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Age = 42 WHERE Name = 'Kalle'")); }
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 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 ItShouldBePossibleToSetMultipleValues() { var stream = S.UpdateNode( S.UpdHead <Person>( S.Ust <Person>("Name", "'Kalle'"), S.Ust <Person>("Age", 42))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Name = 'Kalle', Age = 42")); }
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 ItShouldBePossibleToSelectFromSeveralCommonTableExpressions() { var oldies = Table <PersonLite> .WithTableAlias("Oldies"); var younglings = Table <PersonLite> .WithTableAlias("YoungOnes"); var expr = S.SelectNode( S.With( S.TableDef( younglings, S.Select(S.Col <Person>("Name"), S.Col <Person>("Age")), S.From <Person>(), S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThanOrEqual, S.Const(15)))), S.TableDef( oldies, S.Select(S.Col <Person>("Name"), S.Col <Person>("Age")), S.From <Person>(), S.Where(S.BinExp(S.Col <Person>("Age"), BinaryOperation.GreaterThan, S.Const(40)))) ), S.Select(S.Col(oldies, "Name"), S.Col(younglings, "Name")), S.From( oldies, S.Join( oldies, younglings, Domain.AST.JoinType.InnerJoin, S.BinExp( S.BinExp(S.Col(oldies, "Age"), BinaryOperation.Subtract, S.Const(30)), BinaryOperation.Equal, S.Col(younglings, "Age")))) ); var result = SqlGen.SqlifyExpression(Dialects.AnsiSql.Dialect, expr); var expected = "WITH Oldies AS (" + "SELECT PersonRef.Name, PersonRef.Age " + "FROM Person PersonRef " + "WHERE PersonRef.Age > 40" + "), YoungOnes AS (" + "SELECT PersonRef.Name, PersonRef.Age " + "FROM Person PersonRef " + "WHERE PersonRef.Age <= 15" + ") " + "SELECT OldiesRef.Name, YoungOnesRef.Name " + "FROM Oldies OldiesRef " + "INNER JOIN YoungOnes YoungOnesRef ON OldiesRef.Age - 30 = YoungOnesRef.Age"; Assert.That(result, Is.EqualTo(expected)); }
public void ItShouldProperlyQuoteIdentifiers() { var stream = S.SelectNode( S.Select( S.Col <Employee>("Name"), S.Col <Employee>("Age"), S.Col <Employee>("Id")), S.From <Employee>()); var result = SqlGen.SqlifyExpression(Dialects.SqlServer.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT [EmployeeRef].[Name], [EmployeeRef].[Age], [EmployeeRef].[Id] FROM [HumanResources].[Employee] [EmployeeRef]")); }
public void ItShouldBePossibleToSelectWithColumnAliases() { var stream = S.SelectNode( S.Select( S.AliasedCol <Person>( AggregateType.Count, ValueNode.NullValue, "NumberOfPersons")), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT COUNT(*) AS NumberOfPersons FROM Person PersonRef")); }
public void ItShouldBePossibleToSqlifyASelectWithManyColumns() { var stream = S.SelectNode( S.Select( S.Col <Person>("Name"), S.Col <Person>("Age"), S.Col <Person>("Id")), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT PersonRef.Name, PersonRef.Age, PersonRef.Id FROM Person PersonRef")); }
public void ItShouldBePossibleToUseAnEmptyOverClause() { var expr = S.SelectNode( S.Select( S.Col <Person>("Name"), S.WinCol( AggregateType.Sum, S.Col <Person>("Age"))), S.From <Person>()); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, expr); Assert.That(result, Is.EqualTo("SELECT PersonRef.Name, SUM(PersonRef.Age) OVER () FROM Person PersonRef")); }
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 ItShouldBePossibleToHaveMultipleConditionsOnTheUpdate() { var stream = S.UpdateNode( S.UpdHead <Person>(S.Ust <Person>("Age", 42)), S.Where( S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Kalle'")), S.Or(S.BinExp(S.Col <Person>("Name"), BinaryOperation.Equal, S.Const("'Pelle'"))), S.And(S.BinExp(S.Col <Person>("Age"), BinaryOperation.LessThan, S.Const(18))))); var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream); Assert.That(result, Is.EqualTo("UPDATE Person SET Age = 42 WHERE Name = 'Kalle' OR Name = 'Pelle' AND Age < 18")); }
public void ItShouldProperlyQuoteIdentifiers() { var stream = S.SelectNode( S.Select( S.Col <Employee>("Name"), S.Col <Employee>("Age"), S.Col <Employee>("Id")), S.From <Employee>()); var result = SqlGen.SqlifyExpression(SqlLite.Dialect, stream); Assert.That(result, Is.EqualTo(@"SELECT ""EmployeeRef"".""Name"", ""EmployeeRef"".""Age"", ""EmployeeRef"".""Id"" FROM ""HumanResources"".""Employee"" ""EmployeeRef""")); }