public void Concat()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Concat(person["Name"], person["SurName"]);
QueryResult result = engine.Compile(func);
Assert.Equal("\"PERSON\".\"NAME\" || \"PERSON\".\"SURNAME\"", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Concat()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Concat(person["Name"], person["SurName"]);
QueryResult result = engine.Compile(func);
Assert.Equal("CONCAT(\"person\".\"name\", \"person\".\"surname\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Cast()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Cast(person["Salary"], sql.Type("VARCHAR"));
QueryResult result = engine.Compile(func);
Assert.Equal("CAST(\"PERSON\".\"SALARY\" AS VARCHAR)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Upper()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Upper(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("UPPER(\"person\".\"name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Ceiling()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Ceiling(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("CEILING(\"person\".\"salary\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Count_Column()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Count(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("COUNT(\"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void SumDistinct()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.SumDistinct(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("SUM(DISTINCT \"person\".\"salary\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Trim()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Trim(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(`person`.`Name`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Coalesce()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Coalesce(person["Name"], person["SurName"]);
QueryResult result = engine.Compile(func);
Assert.Equal("COALESCE(`person`.`Name`, `person`.`SurName`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void AvgDistinct()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.AvgDistinct(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("AVG(DISTINCT `person`.`Salary`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Round()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Round(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("ROUND(`person`.`Salary`)", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Sum()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Sum(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("SUM([person].[Salary])", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Count_Distinct()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.CountDistinct(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("COUNT(DISTINCT \"PERSON\".\"NAME\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void LTrim()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.LTrim(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("LTRIM(\"PERSON\".\"NAME\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Lower()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Lower(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("LOWER([person].[Name])", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Max()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Max(person["Salary"]);
QueryResult result = engine.Compile(func);
Assert.Equal("MAX(\"PERSON\".\"SALARY\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Over_Value()
{
IAlias person = sql.Alias("person");
ISelect select = sql.Select().Add(SqlFn.Sum(person["Salary"]))
.Over(o => o.PartitionBy(x => x.Add(person["DepartmentId"])));
QueryResult result = engine.Compile(select);
Assert.Equal("SELECT SUM(\"person\".\"Salary\") OVER(PARTITION BY \"person\".\"DepartmentId\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Concat_With_Or()
{
engine.AddFunction(FunctionName.Concat, FunctionHelper.ConcatOr);
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Concat(person["Name"], person["SurName"]);
QueryResult result = engine.Compile(func);
Assert.Equal("\"person\".\"Name\" || \"person\".\"SurName\"", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Trim_Both()
{
engine.AddFunction(FunctionName.Trim, FunctionHelper.TrimBoth);
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Trim(person["Name"]);
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(\"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>(), result.Parameters);
}
public void Trim_Character()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Trim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(@p0 FROM \"person\".\"name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = ","
}, result.Parameters);
}
public void Round_Places()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Round(person["Salary"], 2m);
QueryResult result = engine.Compile(func);
Assert.Equal("ROUND(\"person\".\"salary\", @p0)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = 2m
}, result.Parameters);
}
public void NullIf()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.NullIf(person["Name"], "empty");
QueryResult result = engine.Compile(func);
Assert.Equal("NULLIF(\"person\".\"name\", @p0)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = "empty"
}, result.Parameters);
}
public void Substring()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Substring(person["Name"], 2, 4);
QueryResult result = engine.Compile(func);
Assert.Equal("SUBSTRING(`person`.`Name`, @p0, @p1)", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = 2, ["@p1"] = 4
}, result.Parameters);
}
public void RTrim_Character()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.RTrim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(TRAILING @p0 FROM `person`.`Name`)", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = ","
}, result.Parameters);
}
public void Replace()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Replace(person["Name"], "a", "b");
QueryResult result = engine.Compile(func);
Assert.Equal("REPLACE(`person`.`Name`, @p0, @p1)", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = "a", ["@p1"] = "b"
}, result.Parameters);
}
public void RTrim_Character()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.RTrim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("RTRIM(\"PERSON\".\"NAME\", :p0)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
[":p0"] = ","
}, result.Parameters);
}
public void LTrim_Character()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.LTrim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("LTRIM([person].[Name], @p0)", result.Sql);
Assert.Equal(new Dictionary <string, object>()
{
["@p0"] = ","
}, result.Parameters);
}
public void Substring()
{
IAlias person = sql.Alias("person");
IFunction func = SqlFn.Substring(person["Name"], 2, 4);
QueryResult result = engine.Compile(func);
Assert.Equal("SUBSTR(\"PERSON\".\"NAME\", :p0, :p1)", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
[":p0"] = 2,
[":p1"] = 4
}, result.Parameters);
}
public void RTrim_Character_Trim_Trailing()
{
engine.AddFunction(FunctionName.RTrim, FunctionHelper.TrimTrailing);
IAlias person = sql.Alias("person");
IFunction func = SqlFn.RTrim(person["Name"], ",");
QueryResult result = engine.Compile(func);
Assert.Equal("TRIM(TRAILING @p0 FROM \"person\".\"Name\")", result.Sql);
Assert.Equal(new Dictionary <string, object>
{
["@p0"] = ","
}, result.Parameters);
}
public void To_String()
{
IAlias person = sql.Alias("person");
IAlias dept = sql.Alias("dept");
IQuery query = sql.Query
.Select(dept["Id"], dept["Name"], SqlFn.Count())
.From(person)
.Join(dept)
.On(dept["Id"].Eq(person["DepartmentId"]))
.Where(person["Active"].Eq(true))
.GroupBy(dept["Id"], dept["Name"])
.Having(SqlFn.Count().Gt(10))
.OrderBy(x => x.Add(dept["Name"]))
.Offset(10, 20);
Assert.Equal("SELECT dept.Id, dept.Name, COUNT(*) FROM person INNER JOIN dept "
+ "ON dept.Id = person.DepartmentId WHERE person.Active = true "
+ "GROUP BY dept.Id, dept.Name HAVING COUNT(*) > 10 ORDER BY dept.Name "
+ "OFFSET 10 FETCH 20", query.ToString());
}
