/// <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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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> /// 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( ); }