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 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 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 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 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 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 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 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 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 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 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 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"""));
}
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 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 ItShouldBePossibleToSpecifyColumnsForAnInsert()
{
var stream =
S.InsertNode(
S.InsHead <Person>("Name", "Age"),
S.InsVal(
S.InsConst("'Kalle'"),
S.InsConst(42))
);
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(
result,
Is.EqualTo("INSERT INTO Person (Name, Age) VALUES ('Kalle', 42)"));
}
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 ItShouldBePossibleToPartitionByAColumn()
{
var expr =
S.SelectNode(
S.Select(
S.Col <Person>("Name"),
S.WinCol(
AggregateType.Max,
S.Col <Person>("Age"),
new[] { S.Col <Person>("Name") })),
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) FROM Person PersonRef"));
}
public void ItShouldBePossibleToOrderByAColumn()
{
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) })),
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) FROM Person PersonRef"));
}
public void ItShouldBePossibleToUpdateValuesToASubExpression()
{
var stream =
S.UpdateNode(
S.UpdHead <Person>(
S.Ust <Person>(
"Age",
S.SubExp(
S.Select(S.Aggregate(AggregateType.Max, S.Col <Car>("Age"))),
S.From <Car>()))));
var result = SqlGen.SqlifyExpression(AnsiSql.Dialect, stream);
Assert.That(result, Is.EqualTo("UPDATE Person SET Age = (SELECT MAX(CarRef.Age) FROM Car CarRef)"));
}
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 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 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 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 ItShouldBePossibleToDoASimpleInsert()
{
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))
);
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)"));
}
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"));
}
