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; }