private void Clear()
{
JoinStatement.Clear();
WhereStatement.Clear();
StatmentTemplate.Clear();
_parameters.Clear();
}
/// <inheritdoc />
public JoinClause Join(string tableName, JoinType joinType = JoinType.InnerJoin)
{
var clause = new JoinClause(this, joinType, tableName);
JoinStatement.Add(clause);
return(clause);
}
public override void Build(ParsedSql ret, List <string> tokens, ref int i)
{
var token = tokens[i];
if (operators.Contains(token))
{
_js.Operator = token;
_onLeft = false;
i++;
}
if (Constants.BooleanOperators.Contains(token))
{
_js = new JoinStatement();
i++;
}
else if (_onLeft)
{
_js.LeftClause = token;
i++;
}
else
{
_js.RightClause = token;
ret.Select.Joins.Add(_js);
i++;
}
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
default:
throw new ArgumentOutOfRangeException(nameof(clause.JoinType), $"MySql doesn't support {clause.JoinType} join type.");
}
sb.Append($"`{clause.ToTable}` ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
}
if (TakeRows.HasValue)
{
sb.AppendLine();
if (SkipRows.HasValue)
{
sb.Append($"LIMIT {SkipRows}, {TakeRows}");
}
else
{
sb.Append($"LIMIT {TakeRows}");
}
}
// Return the built query.
return(sb.ToString());
}
internal (String sql, IEnumerable <MapperParameter> parameters) Build()
{
StatmentTemplate = StatmentTemplate.Replace(_joinPlaceHolder, JoinStatement.ToString());
StatmentTemplate = StatmentTemplate.Replace(_wherePlaceHolder, WhereStatement.ToString());
return(StatmentTemplate.ToString(), _parameters);
}
/// <inheritdoc />
public override string Build()
{
var sb = new StringBuilder("SELECT ");
// Output Distinct.
if (IsDistinct)
{
sb.Append("DISTINCT ");
}
if (!SkipRows.HasValue && TakeRows.HasValue)
{
sb.Append($"TOP {TakeRows} ");
if (TopIsPercent)
{
sb.Append("PERCENT ");
}
}
// Output column names.
sb.Append(SelectedColumns.Any() ? string.Join(", ", SelectedColumns.Select(WrapVariable)) : "*");
// Output table names.
if (SelectedTables.Any())
{
sb.Append($" FROM {string.Join(", ", SelectedTables.Select(WrapVariable))}");
}
// Output joins.
if (JoinStatement.Any())
{
foreach (var clause in JoinStatement)
{
sb.AppendLine();
switch (clause.JoinType)
{
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.OuterJoin:
sb.Append("OUTER JOIN ");
break;
case JoinType.LeftJoin:
sb.Append("LEFT JOIN ");
break;
case JoinType.RightJoin:
sb.Append("RIGHT JOIN ");
break;
}
sb.Append($"[{clause.ToTable}] ON ");
sb.Append(CreateComparisonClause(
$"{clause.ToTable}.{clause.ToColumn}",
clause.ComparisonOperator,
new SqlLiteral($"{clause.FromTable}.{clause.FromColumn}")));
}
}
// Output where statement.
if (WhereStatement.Any())
{
sb.AppendLine();
sb.Append($"WHERE {string.Join(" AND ", WhereStatement.Select(BuildWhereClauseString))}");
}
// Output GroupBy statement.
if (GroupByColumns.Count > 0)
{
sb.AppendLine();
sb.Append($"GROUP BY {string.Join(", ", GroupByColumns.Select(WrapVariable))}");
}
// TODO: Output having statement.
/*
* if (Having.ClauseLevels > 0)
* {
* // Check if a Group By Clause was set
* if (groupByColumns.Count == 0)
* {
* throw new Exception("Having statement was set without Group By");
* }
* if (buildCommand)
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement(() => command));
* }
* else
* {
* sb.Append(" HAVING " + Having.BuildWhereStatement());
* }
* }
*/
// Output OrderBy statement.
if (OrderByStatement.Any())
{
sb.AppendLine();
sb.Append($"ORDER BY {string.Join(", ", OrderByStatement.Select(BuildOrderByClauseString))}");
// Works only in SQL Server 2012 and upper.
// TODO use BETWEEN if it require
if (SkipRows.HasValue)
{
sb.AppendLine();
sb.Append($"OFFSET {SkipRows} ROWS");
if (TakeRows.HasValue)
{
sb.AppendLine();
sb.Append($"FETCH NEXT {TakeRows} ROWS ONLY");
}
}
}
// Return the built query.
return(sb.ToString());
}
public OnJoinStatementBuilder()
{
_onLeft = true;
_js = new JoinStatement();
}
