public virtual string GetSelect(RawSelectBuilder builder)
{
var select = GetSelectClausule(builder);
var source = GetSource(builder);
var where = GetWhereClausule(builder);
var orderBy = GetOrderByClausule(builder);
var groupBy = GetGroupByClausule(builder);
var having = GetHavingClausule(builder);
var result = $"{select} {source}";
if (!string.IsNullOrWhiteSpace(where))
{
result += $" {where}";
}
if (!string.IsNullOrWhiteSpace(groupBy))
{
result += $" {groupBy}";
}
if (!string.IsNullOrWhiteSpace(having))
{
result += $" {having}";
}
if (!string.IsNullOrWhiteSpace(orderBy))
{
result += $" {orderBy}";
}
return(result);
}
public virtual string GetHavingClausule(RawSelectBuilder builder)
{
if (!builder.HavingSqls.Any())
{
return("");
}
var result = new StringBuilder($"{Keyword(SqlKeyword.Having)} ");
var count = 0;
foreach (var w in builder.HavingSqls)
{
if (count > 0)
{
if (w.PreOperator == WhereOperator.And)
{
result.Append($" {Keyword(SqlKeyword.And)} ");
}
else if (w.PreOperator == WhereOperator.Or)
{
result.Append($" {Keyword(SqlKeyword.Or)} ");
}
}
result.Append($"{w.Expression}");
}
return(result.ToString());
}
public virtual string GetImplicitJoinWhere(RawSelectBuilder builder)
{
if (!builder.JoinSqls.Any())
{
return("");
}
return(builder.JoinSqls.Select(j => j.Condition).Aggregate((current, next) => current + $" {Keyword(SqlKeyword.And)} " + next));
}
public virtual string GetSource(RawSelectBuilder builder)
{
if (JoinStyle == JoinStyle.Explicit)
{
return(GetSourceExplicitJoins(builder));
}
return(GetImplicitJoin(builder));
}
public virtual string GetGroupByClausule(RawSelectBuilder builder)
{
if (!builder.GroupBySqls.Any())
{
return("");
}
return($"{Keyword(SqlKeyword.GroupBy)} " + string.Join(", ", builder.GroupBySqls.ToArray()));
}
public virtual string GetImplicitJoin(RawSelectBuilder builder)
{
var result = new StringBuilder(Keyword(SqlKeyword.From) + $" {GetSourceSqlWithAlias(builder.MainSourceSql.Expression, builder.MainSourceSql.Alias)}");
foreach (var j in builder.JoinSqls)
{
result.Append($"{Symbol(SqlSymbols.Comma)} {GetSourceSqlWithAlias(j.Expression, j.Alias)}");
}
return(result.ToString().TrimEnd());
}
public virtual string GetSelectClausule(RawSelectBuilder builder)
{
var columns = builder.SelectSqls.Any()
? string.Join($"{Symbol(SqlSymbols.Comma)} ",
builder.SelectSqls.Select(
c => c.Expression + (c.Alias != null ? $" {Keyword(SqlKeyword.As)} {AlaisToSql(c.Alias)}" : ""))
.ToArray())
: Keyword(SqlKeyword.AllColumns);
return(Keyword(SqlKeyword.Select) + " " + columns);
}
protected void AddSubQuery(RawSelectBuilder subQueryBuilder)
{
var sql = subQueryBuilder.GetSqlText();
foreach (var p in subQueryBuilder.Parameters)
{
_builder.AddParameter(p.Value);
var paramName = _builder.Parameters.Last().Key;
sql = sql.Replace(p.Key, paramName);
}
Result.Append(sql);
}
public void SelectTable()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0]";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectTableWithSchemaAndAlias()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[WH].[CUSTOMERS]", "T1000");
var spec = "SELECT * FROM [WH].[CUSTOMERS] AS [T1000]";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectTableWithExplicitJoin()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.JoinSql("[INVOICE]", "INVOICE.CUSTOMERID = CUSTOMER.ID");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0] INNER JOIN [INVOICE] AS [T1] ON INVOICE.CUSTOMERID = CUSTOMER.ID";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectFromTableWithGroupBy()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.GroupBySql("COUNTRY").GroupBySql("TYPE");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0] GROUP BY COUNTRY, TYPE";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectFromTableWithOrderBy()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.OrderBySql("ID").OrderBySql(OrderByDirection.Desc, "NAME").OrderBySql(OrderByDirection.Asc, "BALANCE");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0] ORDER BY ID ASC, NAME DESC, BALANCE ASC";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectFromTableWithWhere()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.WhereSql("NAME LIKE @PARAM1").WhereSql("ID > 100").WhereSql(WhereOperator.Or, "ID > 10000");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0] WHERE NAME LIKE @PARAM1 AND ID > 100 OR ID > 10000";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectColumnsFromTable()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.SelectSql("_ID", "ID").SelectSql("NAME");
var spec = "SELECT _ID AS [ID], NAME FROM [CUSTOMERS] AS [T0]";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public void SelectTableWithImplicitJoin()
{
Configuration.SetDialect(new TestImplicitJoinDialect());
var b = new RawSelectBuilder();
b.FromSql("[CUSTOMERS]");
b.JoinSql("[INVOICE]", "INVOICE.CUSTOMERID = CUSTOMER.ID");
b.JoinSql("[INVOICEDETAIL]", "INVOICE.ID = INVOICEDETAIL.ID");
var spec = "SELECT * FROM [CUSTOMERS] AS [T0], [INVOICE] AS [T1], [INVOICEDETAIL] AS [T2] WHERE (INVOICE.CUSTOMERID = CUSTOMER.ID AND INVOICE.ID = INVOICEDETAIL.ID)";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public virtual string GetSourceExplicitJoins(RawSelectBuilder builder)
{
if (builder.MainSourceSql == null)
{
return("");
}
var result = new StringBuilder(Keyword(SqlKeyword.From) + $" {GetSourceSqlWithAlias(builder.MainSourceSql.Expression, builder.MainSourceSql.Alias)}");
foreach (var j in builder.JoinSqls)
{
result.Append($" {GetJoinTypeLiteral(j.Type)} {GetSourceSqlWithAlias(j.Expression, j.Alias)} {Keyword(SqlKeyword.On)} {j.Condition}");
}
return(result.ToString());
}
public virtual string GetOrderByClausule(RawSelectBuilder builder)
{
if (!builder.OrderBySqls.Any())
{
return("");
}
var orderBy =
string.Join(", ", builder.OrderBySqls.Select(
o => $"{o.Expression}" + (o.Direction == OrderByDirection.Asc ? " ASC" : " DESC"))
.ToArray());
return($"{Keyword(SqlKeyword.OrderBy)} " + orderBy);
}
public void SelectTableWithColumnsExplicitJoinWhereOrderBy()
{
Configuration.SetDialect(new TestDialect());
var b = new RawSelectBuilder();
b.FromSql("[WH].[INVOICE]");
b.SelectSql("T0.DATE, T0.TOTAL");
b.SelectSql("T1.NAME, T1.BALANCE");
b.JoinSql("[CUS].[CUSTOMER]", "T1.CUSTOMERID = T2.ID");
var spec = "SELECT T0.DATE, T0.TOTAL, T1.NAME, T1.BALANCE FROM [WH].[INVOICE] AS [T0] INNER JOIN [CUS].[CUSTOMER] AS [T1] ON T1.CUSTOMERID = T2.ID";
var gen = b.GetSqlText();
Assert.Equal(spec, gen);
}
public virtual string GetWhereClausule(RawSelectBuilder builder)
{
var result = new StringBuilder($"{Keyword(SqlKeyword.Where)} ");
var implicitJoinWhere = "";
if (JoinStyle == JoinStyle.Implicit)
{
implicitJoinWhere = GetImplicitJoinWhere(builder);
result.Append(
$"{Symbol(SqlSymbols.StartGroup)}{implicitJoinWhere}{Symbol(SqlSymbols.EndGroup)}");
if (builder.WhereSqls.Any())
{
result.Append($" {Keyword(SqlKeyword.And)} ");
}
}
if (implicitJoinWhere == "" && !builder.WhereSqls.Any())
{
return("");
}
var whereCount = 0;
foreach (var w in builder.WhereSqls)
{
if (whereCount > 0)
{
if (w.PreOperator == WhereOperator.And)
{
result.Append($" {Keyword(SqlKeyword.And)} ");
}
else if (w.PreOperator == WhereOperator.Or)
{
result.Append($" {Keyword(SqlKeyword.Or)} ");
}
}
result.Append($"{w.Expression}");
whereCount++;
}
return(result.ToString());
}
public Visitor(Dictionary <string, TableDefinition> definitionsByParameterName, RawSelectBuilder builder)
{
_definitionsByParameterName = definitionsByParameterName;
_builder = builder;
}
