/// <summary>
/// Format any sub queries that need to appear in the where/on clause.
/// </summary>
internal void RenderSubqueryChildren(ConjunctionTracker conjunctions, SqlBuilderContext sb)
{
foreach (SqlTable child in SubqueryChildren)
{
conjunctions.RenderSql(sb);
if (child.JoinHint == JoinHint.NotExists)
{
sb.Append("not ");
}
sb.Append("exists (");
sb.Indent( );
sb.AppendOnNewLine("select 1");
var fromClause = new SqlFromClause
{
RootTable = child
};
fromClause.RenderSql(sb);
sb.AddJoinCondition(child, this, child.JoinColumn, child.ForeignColumn);
child.RenderTableConditions("where", false, sb);
sb.EndIndent( );
sb.AppendOnNewLine(")");
}
}
/// <summary>
/// Render a single conjunction (e.g. where/on/and)
/// </summary>
/// <param name="sb">Sql Builder</param>
public void RenderSql(SqlBuilderContext sb)
{
if (_first)
{
if (_inline)
{
sb.Append(_introClause);
sb.Append(" ");
}
else
{
sb.AppendOnNewLine(_introClause);
sb.Indent( );
sb.StartNewLine( );
}
}
else
{
if (_inline)
{
sb.Append(" ");
sb.Append(_conjunction);
sb.Append(" ");
}
else
{
sb.AppendOnNewLine(_conjunction);
sb.Append(" ");
}
}
}
/// <summary>
/// Generates the SQL text for the whole query.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSql(SqlBuilderContext sb)
{
var first = new First( );
if (!string.IsNullOrEmpty(SqlPreamble))
{
sb.Append(SqlPreamble);
first.Value = false;
}
foreach (SqlSelectStatement statement in Statements)
{
if (!first)
{
sb.AppendOnNewLine("");
}
statement.RenderSql(sb);
}
if (!string.IsNullOrEmpty(SqlPostamble))
{
if (!first)
{
sb.AppendOnNewLine("");
}
sb.Append(SqlPostamble);
}
}
/// <summary>
/// Finalise any processing.
/// </summary>
/// <param name="sb">Sql Builder</param>
public void FinishSql(SqlBuilderContext sb)
{
if (AnyRendered && !_inline)
{
sb.EndIndent( );
}
}
/// <summary>
/// Renders a table's name and alias for the FROM clause.
/// Or render the entire sub query if the table represents a sub query.
/// </summary>
/// <param name="table"></param>
/// <param name="sb"></param>
private static void RenderTableNameOrSubquery(SqlTable table, SqlBuilderContext sb)
{
if (table.SubQuery != null)
{
// Table is sub query
sb.Append("(");
sb.Indent( );
table.SubQuery.RenderSql(sb);
sb.EndIndent( );
sb.AppendOnNewLine(")");
}
else
{
// Table name
sb.Append(table.NameContainsSql ? table.Name : SqlBuilder.EscapeSqlIdentifier(table.Name));
}
// Render alias
// Note: Table aliases are assumed to not require escaping or delimiting
// as they are generated by the alias manager
if (!string.IsNullOrEmpty(table.TableAlias))
{
sb.Append(" ");
sb.Append(table.FullTableAlias ?? table.TableAlias);
}
}
/// <summary>
/// Writes the select clause of the query.
/// </summary>
/// <param name="sb"></param>
public void RenderSql(SqlBuilderContext sb)
{
var first = new First( );
// Render each query
foreach (SqlQuery query in Queries)
{
if (!first)
{
// Write the union statement
switch (UnionType)
{
case SqlUnionType.Union:
sb.AppendOnNewLine("union");
break;
case SqlUnionType.Except:
sb.AppendOnNewLine("except");
break;
case SqlUnionType.UnionAll:
sb.AppendOnNewLine("union all");
break;
}
}
query.RenderSql(sb);
}
}
/// <summary>
/// Renders the select statement, including the with clause.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSqlStatement(SqlBuilderContext sb)
{
if (WithClause != null)
{
WithClause.RenderSql(sb);
}
RootQuery.RenderSql(sb);
}
/// <summary>
/// Recursively steps through joined tables, rendering their join SQL.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="sb">The sb.</param>
private static void RenderFromRecursive(SqlTable table, SqlBuilderContext sb)
{
// Order by join type so that inner joins appear before left joins
// to ensure that inner joins don't coerce left joins into misbehaving.
IOrderedEnumerable <SqlTable> orderedChildren = table.Children.OrderBy(GetTablePriority);
foreach (SqlTable child in orderedChildren)
{
string joinString = GetJoinString(child.JoinType);
sb.AppendOnNewLine(joinString);
sb.Append(" ");
// A join can be rendered inline so long as it is not a left join with inner joined children
// (as the inner joins will artificially constrain the left join unless they are nested in brackets)
// And as long as it has no special conditions, as they may refer to child nodes.
bool nest = child.JoinType == JoinType.Left && child.Children.Any(grandChild => grandChild.JoinType == JoinType.Inner);
if (child.HasCustomConditions && child.Children.Count > 0)
{
nest = true;
}
if (nest)
{
sb.Append("(");
sb.Indent( );
sb.StartNewLine( );
RenderTableNameOrSubquery(child, sb);
RenderFromRecursive(child, sb);
sb.EndIndent( );
sb.AppendOnNewLine(") ");
}
else
{
RenderTableNameOrSubquery(child, sb);
sb.Append(" ");
}
// Render 'ON' clause conditions
// Apply join condition
if (child.JoinColumn != null)
{
sb.AddJoinCondition(child, table, child.JoinColumn, child.ForeignColumn);
}
child.RenderTableConditions("on", true, sb);
if (!nest)
{
RenderFromRecursive(child, sb);
}
}
}
/// <summary>
/// Writes the SQL order-by clause.
/// </summary>
public void RenderSql(SqlBuilderContext sb)
{
if (Items.Count == 0)
{
return;
}
sb.AppendOnNewLine("order by");
sb.Indent( );
// These list are very short .. so faster than dict
var visited = new List <string>(Items.Count);
var first = new First( );
foreach (SqlOrderItem orderItem in Items)
{
if (!string.IsNullOrWhiteSpace(orderItem.Expression.StaticError))
{
continue;
}
// Prevent duplicates, as T-SQL doesn't like them
var sqlExpr = orderItem.Expression.OrderingSql;
if (visited.Contains(sqlExpr))
{
continue;
}
visited.Add(sqlExpr);
// Seperator
if (!first)
{
sb.Append(",");
}
// Append expression
sb.AppendOnNewLine(sqlExpr);
if (orderItem.Direction == OrderByDirection.Descending)
{
sb.Append(" desc");
}
}
// Paging clause
if (Offset != null && FetchNext != null)
{
sb.AppendOnNewLine("offset " + Offset.Sql + " rows");
sb.AppendOnNewLine("fetch next " + FetchNext.Sql + " rows only");
}
sb.EndIndent( );
}
/// <summary>
/// Renders any additional conditions that apply to this specific table.
/// </summary>
/// <param name="conjunctions">Tracks where/on/and keywords. Call MUST ensure FinishSql gets called.</param>
/// <param name="sb">The SQL builder.</param>
internal void RenderTableConditions(ConjunctionTracker conjunctions, SqlBuilderContext sb)
{
// Conditions
foreach (string condition in FullJoinConditions.Concat(Conditions))
{
conjunctions.RenderSql(sb);
string conditionSql = condition.Replace("$", TableAlias);
sb.Append(conditionSql);
}
RenderSubqueryChildren(conjunctions, sb);
}
/// <summary>
/// Renders any additional conditions that apply to this specific table.
/// </summary>
/// <param name="sqlClause">The SQL clause used to render conditions ('on' or 'where').</param>
/// <param name="inline"></param>
/// <param name="sb">The SQL builder.</param>
internal void RenderTableConditions(string sqlClause, bool inline, SqlBuilderContext sb)
{
var conjunction = new ConjunctionTracker(sqlClause, "and", inline);
RenderTableConditions(conjunction, sb);
// Ensure that there is at least some clause
if (sqlClause == "on" && !conjunction.AnyRendered)
{
conjunction.RenderSql(sb);
sb.Append("1=1");
}
conjunction.FinishSql(sb);
}
/// <summary>
/// Generates the SQL text for the whole query.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSql(SqlBuilderContext sb)
{
SelectClause.RenderSql(sb);
FromClause.RenderSql(sb);
WhereClause.RenderSql(sb, this);
GroupByClause.RenderSql(sb);
HavingClause.RenderSql(sb);
OrderClause.RenderSql(sb);
if (!string.IsNullOrEmpty(ForClause))
{
sb.AppendOnNewLine(ForClause);
}
}
/// <summary>
/// Generates the SQL text for the FROM clause.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSql(SqlBuilderContext sb)
{
if (RootTable == null)
{
throw new InvalidOperationException("Cannot build SQL unless a root table has been specified.");
}
// Prepare join types
ConstrainInWhereClause.Add(RootTable);
PrepareJoinsRecursively(RootTable, RootTable);
PrepareRightJoins(RootTable.ParentQuery);
// Render from
sb.AppendOnNewLine("from");
sb.Indent( );
sb.StartNewLine( );
RenderTableNameOrSubquery(RootTable, sb);
RenderFromRecursive(RootTable, sb);
sb.EndIndent( );
}
/// <summary>
/// Generates the SQL text for the whole query.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderSql(SqlBuilderContext sb)
{
if (RootQuery == null)
{
throw new InvalidOperationException("RootQuery has not been specified.");
}
if (CurrentCte.Count > 0)
{
throw new Exception("'CurrentCte' stack is not empty. All CTEs should have been popped prior to render.");
}
if (RenderAsTempTable)
{
RenderTempTable(sb);
}
else
{
RenderSqlStatement(sb);
}
}
/// <summary>
/// Writes the select clause of the query.
/// </summary>
/// <param name="sb"></param>
public void RenderSql(SqlBuilderContext sb)
{
sb.AppendOnNewLine("select");
if (Items.Count == 0)
{
sb.Append(" distinct 1 as 'fake column' ");
}
else
{
if (Distinct)
{
sb.Append(" distinct");
}
sb.Indent();
var first = new First();
foreach (SqlSelectItem selectItem in Items)
{
if (!first)
{
sb.Append(",");
}
string sql = UseResultSql ? selectItem.Expression.ResultSql : selectItem.Expression.Sql;
// Append expression
sb.AppendOnNewLine(sql);
if (!string.IsNullOrEmpty(selectItem.Alias))
{
sb.Append(" ");
sb.Append(SqlBuilder.EscapeSqlIdentifier(selectItem.Alias));
}
}
sb.EndIndent();
}
}
/// <summary>
/// Writes the SQL having clause.
/// </summary>
public void RenderSql(SqlBuilderContext sb, SqlQuery query)
{
var conjunctions = new ConjunctionTracker("where", "and", false);
// Render individual table conditions that want to be moved to the query
// This will include the root table
foreach (SqlTable table in query.FromClause.ConstrainInWhereClause)
{
table.PrepareTableConditions( );
table.RenderTableConditions(conjunctions, sb);
}
// Render query-level conditions
foreach (SqlExpression condition in Conditions)
{
conjunctions.RenderSql(sb);
sb.Append(condition.Sql);
}
conjunctions.FinishSql(sb);
}
/// <summary>
/// Writes the SQL order-by clause.
/// </summary>
public void RenderSql(SqlBuilderContext sb)
{
if (Expressions.Count == 0)
{
return;
}
sb.AppendOnNewLine("group by");
sb.Indent( );
var first = new First( );
foreach (SqlExpression grouping in Expressions)
{
if (!first)
{
sb.Append(",");
}
// Append expression
sb.AppendOnNewLine(grouping.Sql);
}
sb.EndIndent( );
}
/// <summary>
/// Writes the select clause of the query.
/// </summary>
/// <param name="sb"></param>
public void RenderSql(SqlBuilderContext sb)
{
if (Items == null || Items.Count == 0)
{
return;
}
sb.AppendOnNewLine(";with");
var first = new First( );
foreach (SqlCte cte in OrderCtesByDependencies( ))
{
if (!first)
{
sb.Append(",");
}
if (!string.IsNullOrEmpty(cte.RawSql))
{
sb.AppendOnNewLine(cte.RawSql);
}
else
{
sb.AppendOnNewLine(SqlBuilder.EscapeSqlIdentifier(cte.Name));
sb.Append(" as");
sb.AppendOnNewLine("(");
sb.Indent( );
cte.Union.RenderSql(sb);
sb.EndIndent( );
sb.AppendOnNewLine(")");
}
}
sb.AppendOnNewLine("");
sb.AppendOnNewLine("--Main query");
}
/// <summary>
/// Renders a temp table or table variable, and inserts the query into it.
/// </summary>
/// <param name="sb">The SQL text formatter.</param>
public void RenderTempTable(SqlBuilderContext sb)
{
if (string.IsNullOrEmpty(Name))
{
throw new Exception("SqlSelectStatement.Name must be set when rendering as a temp table.");
}
bool tableVariable = Name.StartsWith("@");
bool tempTable = Name.StartsWith("#");
if (!tableVariable && !tempTable)
{
throw new Exception("Table name must start with @ or # for a table variable, or temp table, respectively.");
}
// Table declaration
if (tableVariable)
{
sb.AppendOnNewLine("declare " + Name + " as table");
}
else
{
sb.AppendOnNewLine("create table " + Name);
}
// Column declarations
sb.AppendOnNewLine("(");
sb.Indent( );
var first = new First( );
int col = 0;
foreach (SqlSelectItem item in RootQuery.SelectClause.Items)
{
string alias = item.Alias;
if (string.IsNullOrEmpty(item.Alias))
{
alias = "col" + (col++).ToString(CultureInfo.InvariantCulture);
}
// throw new Exception("Queries rendered in temp tables must have an alias for every column.");
if (!first)
{
sb.Append(",");
}
const string colType = "bigint"; // for now
const string options = " primary key"; // for now
sb.AppendOnNewLine(alias + " " + colType + options);
}
sb.EndIndent( );
sb.AppendOnNewLine(")");
// Insert query contents into temp table
if (WithClause != null)
{
WithClause.RenderSql(sb);
}
sb.AppendOnNewLine("insert into " + Name);
sb.Indent( );
RootQuery.RenderSql(sb);
sb.EndIndent( );
}
