public void GroupBySimpleListAlias()
{
ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL);
GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c);
g.Append(false, "a", "b", "c");
string result = g.GetSql("t");
string sql = "[t].[a], [t].[b], [t].[c]";
Assert.AreEqual(sql, result);
}
public Select(IFormatter parameters, string tableAlias = "")
{
Query = SqlQuery.Select;
Formatter = parameters;
TableAlias = tableAlias;
Columns = new ColumnsListAggregation(Formatter);
Join = new JoinList(Formatter);
Where = new WhereList(Formatter);
OrderBy = new OrderByList(Formatter);
GroupBy = new GroupByList(Formatter, Columns);
}
public void ColumnsAggregations1()
{
ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL);
c.FuncCount("all");
c.FuncMax("cnt", "max_cnt");
string result = c.GetSql();
string sql = "COUNT([all]), MAX([cnt]) as 'max_cnt'";
Assert.AreEqual(sql, result);
}
public void GroupBySimpleList3()
{
ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL);
GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c);
g.Append(true, "a", "b", "c");
string result = g.GetSql();
string sql = "[a], [b], [c]";
Assert.AreEqual(sql, result);
Assert.AreEqual(c.Count, g.Count);
}
public void ColumnsAggregations1()
{
SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
var c = new ColumnsListAggregation(SuperSql.DefaultFormatter);
c.FuncCount("all");
c.FuncMax("cnt", "max_cnt");
var result = c.GetSql();
var sql = "COUNT([all]), MAX([cnt]) as 'max_cnt'";
Assert.Equal(sql, result);
}
public void ColumnsAggregations1()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
c.FuncCount("all");
c.FuncMax("cnt", "max_cnt");
string result = c.GetSql();
string sql = "COUNT([all]), MAX([cnt]) as 'max_cnt'";
Assert.AreEqual(sql, result);
}
public void GroupBySimpleListAlias()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c);
g.Append(false, "a", "b", "c");
string result = g.GetSql("t");
string sql = "[t].[a], [t].[b], [t].[c]";
Assert.Equal(sql, result);
}
public void GroupBySimpleListAlias()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.Append(false, "a", "b", "c");
string result = g.GetSql("t");
string sql = "[t].[a], [t].[b], [t].[c]";
Assert.AreEqual(sql, result);
}
public void GroupByRaw2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c);
g.Raw("[t].[column]", "[g].[guid]");
string result = g.GetSql();
string sql = "[t].[column], [g].[guid]";
Assert.Equal(sql, result);
}
public void GroupByRaw2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.Raw("[t].[column]", "[g].[guid]");
string result = g.GetSql();
string sql = "[t].[column], [g].[guid]";
Assert.AreEqual(sql, result);
}
public void GroupBySimpleList3()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.Append(true, "a", "b", "c");
string result = g.GetSql();
string sql = "[a], [b], [c]";
Assert.AreEqual(sql, result);
Assert.AreEqual(c.Count, g.Count);
}
public void GroupByAggregation2()
{
ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL);
GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c);
g.FuncMin("sm");
string result1 = g.GetSql();
string sql1 = "[sm]";
string result2 = c.GetSql();
string sql2 = "MIN([sm])";
Assert.AreEqual(sql1, result1);
Assert.AreEqual(sql2, result2);
Assert.AreEqual(c.Count, g.Count);
}
public void GroupByAggregation2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c);
g.FuncMin("sm");
string result1 = g.GetSql();
string sql1 = "[sm]";
string result2 = c.GetSql();
string sql2 = "MIN([sm])";
Assert.Equal(sql1, result1);
Assert.Equal(sql2, result2);
Assert.Equal(c.Count, g.Count);
}
public void GroupByAggregationMany()
{
ColumnsListAggregation c = new ColumnsListAggregation(global::SqlBuilder.Format.MsSQL);
GroupByList g = new GroupByList(global::SqlBuilder.Format.MsSQL, c);
g.FuncSum("sm", "asm");
g.FuncMax("mx", "amx");
g.FuncMin("mn", "amn");
string result1 = g.GetSql();
string sql1 = "[sm], [mx], [mn]";
string result2 = c.GetSql();
string sql2 = "SUM([sm]) as 'asm', MAX([mx]) as 'amx', MIN([mn]) as 'amn'";
Assert.AreEqual(sql1, result1);
Assert.AreEqual(sql2, result2);
Assert.AreEqual(c.Count, g.Count);
}
public void GroupByAggregation1()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.FuncMax("sm");
string result1 = g.GetSql();
string sql1 = "[sm]";
string result2 = c.GetSql();
string sql2 = "MAX([sm])";
Assert.AreEqual(sql1, result1);
Assert.AreEqual(sql2, result2);
Assert.AreEqual(c.Count, g.Count);
}
public void GroupByAggregationMany()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.FuncSum("sm", "asm");
g.FuncMax("mx", "amx");
g.FuncMin("mn", "amn");
g.FuncCount("fg", "acn");
string result1 = g.GetSql();
string sql1 = "[sm], [mx], [mn], [fg]";
string result2 = c.GetSql();
string sql2 = "SUM([sm]) as 'asm', MAX([mx]) as 'amx', MIN([mn]) as 'amn', COUNT([fg]) as 'acn'";
Assert.AreEqual(sql1, result1);
Assert.AreEqual(sql2, result2);
Assert.AreEqual(c.Count, g.Count);
}
public void GroupByAlias2()
{
SqlBuilder.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(SqlBuilder.DefaultFormatter);
GroupByList g = new GroupByList(SqlBuilder.DefaultFormatter, c);
g.Append(false, "c1");
g.SetTableAlias("oh2");
g.Append(false, "c2");
g.SetTableAlias("oh3");
g.Append(false, "c3");
g.SetTableAlias();
g.Append(false, "c4");
g.Raw("[t].[column]");
string result = g.GetSql();
string sql = "[c1], [oh2].[c2], [oh3].[c3], [c4], [t].[column]";
Assert.AreEqual(sql, result);
}
public void GroupByAlias2()
{
NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter = FormatterLibrary.MsSql;
ColumnsListAggregation c = new ColumnsListAggregation(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter);
GroupByList g = new GroupByList(NKnife.Storages.SQL.Common.SuperSql.DefaultFormatter, c);
g.Append(false, "c1");
g.SetTableAlias("oh2");
g.Append(false, "c2");
g.SetTableAlias("oh3");
g.Append(false, "c3");
g.SetTableAlias();
g.Append(false, "c4");
g.Raw("[t].[column]");
string result = g.GetSql();
string sql = "[c1], [oh2].[c2], [oh3].[c3], [c4], [t].[column]";
Assert.Equal(sql, result);
}
