protected virtual void VisitGroupBy(ExpressionVisitorContext context, GroupByClause clause)
{
if (clause == null || clause.Keys.Count == 0)
{
return;
}
if (context.Output.Length > 0)
{
context.WriteLine();
}
context.Write("GROUP BY ");
int index = 0;
foreach (var key in clause.Keys)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(key);
}
if (clause.Having != null)
{
context.WriteLine();
context.Write("HAVING ");
context.Visit(clause.Having);
}
}
protected virtual void VisitFields(ExpressionVisitorContext context, UpdateStatement statement, ICollection <FieldValue> fields)
{
var index = 0;
foreach (var field in fields)
{
if (index++ > 0)
{
context.WriteLine(",");
}
context.Visit(field.Field);
context.Write("=");
var parenthesisRequired = field.Value is IStatementBase;
if (parenthesisRequired)
{
context.Write("(");
}
context.Visit(field.Value);
if (parenthesisRequired)
{
context.Write(")");
}
}
}
public static void VisitJoin(this ExpressionVisitorContext context, JoinClause joining)
{
context.WriteLine();
switch (joining.Type)
{
case JoinType.Inner:
context.Write("INNER JOIN ");
break;
case JoinType.Left:
context.Write("LEFT JOIN ");
break;
case JoinType.Right:
context.Write("RIGHT JOIN ");
break;
case JoinType.Full:
context.Write("FULL JOIN ");
break;
}
switch (joining.Target)
{
case TableIdentifier table:
context.Visit(table);
if (string.IsNullOrEmpty(joining.Name))
{
context.WriteLine(" ON");
}
else
{
context.WriteLine(" ON /* " + joining.Name + " */");
}
break;
case SelectStatement subquery:
context.Write("(");
//递归生成子查询语句
context.Visit(subquery);
if (string.IsNullOrEmpty(subquery.Alias))
{
context.WriteLine(") ON");
}
else
{
context.WriteLine(") AS " + subquery.Alias + " ON");
}
break;
}
context.Visit(joining.Conditions);
}
public static void VisitFrom(this ExpressionVisitorContext context, ICollection <ISource> sources, Action <ExpressionVisitorContext, JoinClause> join)
{
if (sources == null || sources.Count == 0)
{
return;
}
context.Write(" FROM ");
foreach (var source in sources)
{
switch (source)
{
case TableIdentifier table:
context.Visit(table);
break;
case SelectStatement subquery:
context.Write("(");
//递归生成子查询语句
context.Visit(subquery);
if (string.IsNullOrEmpty(subquery.Alias))
{
context.Write(")");
}
else
{
context.Write(") AS " + subquery.Alias);
}
break;
case JoinClause joining:
if (join == null)
{
VisitJoin(context, joining);
}
else
{
join(context, joining);
}
break;
}
}
}
protected virtual void VisitOrderBy(ExpressionVisitorContext context, OrderByClause clause)
{
if (clause == null || clause.Members.Count == 0)
{
return;
}
if (context.Output.Length > 0)
{
context.WriteLine();
}
context.Write("ORDER BY ");
int index = 0;
foreach (var member in clause.Members)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(member.Field);
if (member.Mode == SortingMode.Descending)
{
context.Write(" DESC");
}
}
}
protected override void OnVisit(ExpressionVisitorContext context, TableDefinition statement)
{
if (statement.IsTemporary)
{
context.WriteLine($"CREATE TEMPORARY TABLE {statement.Name} (");
}
else
{
context.WriteLine($"CREATE TABLE {statement.Name} (");
}
int index = 0;
foreach (var field in statement.Fields)
{
if (index++ > 0)
{
context.WriteLine(",");
}
context.Visit(field);
}
context.WriteLine(");");
}
private void VisitReturning(ExpressionVisitorContext context, ReturningClause returning)
{
if (returning == null)
{
return;
}
context.WriteLine();
context.Write("RETURNING ");
if (returning.Members == null || returning.Members.Count == 0)
{
context.Write("*");
}
else
{
int index = 0;
foreach (var member in returning.Members)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(member.Field);
}
}
if (returning.Table != null)
{
context.Write(" INTO ");
context.Write(context.Dialect.GetIdentifier(returning.Table.Identifier()));
}
}
protected override void OnVisit(ExpressionVisitorContext context, UpdateStatement statement)
{
if (statement.Tables == null || statement.Tables.Count == 0)
{
throw new DataException("Missing required tables in the update statement.");
}
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the update statment.");
}
if (statement.Returning != null && statement.Returning.Table != null)
{
context.Visit(statement.Returning.Table);
}
context.Write("UPDATE ");
this.VisitTables(context, statement, statement.Tables);
context.WriteLine(" SET");
this.VisitFields(context, statement, statement.Fields);
this.VisitFrom(context, statement, statement.From);
this.VisitWhere(context, statement, statement.Where);
context.WriteLine(";");
}
protected virtual void VisitInto(ExpressionVisitorContext context, IIdentifier into)
{
if (into == null)
{
return;
}
context.Write(" INTO ");
context.Visit(into);
}
protected override void OnVisit(ExpressionVisitorContext context, InsertStatement statement)
{
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the insert statment.");
}
if (statement.Returning != null && statement.Returning.Table != null)
{
context.Visit(statement.Returning.Table);
}
context.Write("INSERT INTO ");
context.Visit(statement.Table);
this.VisitFields(context, statement, statement.Fields);
this.VisitValues(context, statement, statement.Values, statement.Fields.Count);
context.WriteLine(";");
}
protected virtual void VisitTables(ExpressionVisitorContext context, UpdateStatement statement, IList <TableIdentifier> tables)
{
for (int i = 0; i < tables.Count; i++)
{
if (i > 0)
{
context.Write(",");
}
context.Visit(tables[i]);
}
}
protected override void OnVisit(ExpressionVisitorContext context, DeleteStatement statement)
{
if (statement.Returning != null && statement.Returning.Table != null)
{
context.Visit(statement.Returning.Table);
}
context.Write("DELETE ");
this.VisitTables(context, statement, statement.Tables);
this.VisitFrom(context, statement, statement.From);
this.VisitWhere(context, statement, statement.Where);
context.WriteLine(";");
}
public static void VisitWhere(this ExpressionVisitorContext context, IExpression where)
{
if (where == null)
{
return;
}
if (context.Output.Length > 0)
{
context.WriteLine();
}
context.Write("WHERE ");
context.Visit(where);
}
protected override void OnVisited(ExpressionVisitorContext context, SelectStatement statement)
{
if (context.Depth == 0)
{
context.WriteLine(";");
}
if (statement.Paging != null && statement.Paging.Enabled)
{
/*
* 注意：有分组子句的分页和没有分组子句的分页查询是不同的。
*/
if (statement.GroupBy == null)
{
context.WriteLine("SELECT COUNT(*)");
this.VisitFrom(context, statement.From);
this.VisitWhere(context, statement.Where);
}
else
{
context.WriteLine("SELECT COUNT(0) FROM (");
context.WriteLine("SELECT ");
int index = 0;
foreach (var key in statement.GroupBy.Keys)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(key);
}
this.VisitFrom(context, statement.From);
this.VisitWhere(context, statement.Where);
this.VisitGroupBy(context, statement.GroupBy);
context.WriteLine(") AS __wrapping__");
}
}
//调用基类同名方法
base.OnVisited(context, statement);
}
protected virtual void VisitFields(ExpressionVisitorContext context, InsertStatement statement, ICollection <FieldIdentifier> fields)
{
int index = 0;
context.Write(" (");
foreach (var field in fields)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(field);
}
context.Write(")");
}
protected virtual void VisitValues(ExpressionVisitorContext context, InsertStatement statement, ICollection <IExpression> values, int rounds)
{
int index = 0;
context.WriteLine(" VALUES");
foreach (var value in values)
{
if (index > 0)
{
context.Write(",");
}
if (index % rounds == 0)
{
context.Write("(");
}
var parenthesisRequired = value is IStatementBase;
if (parenthesisRequired)
{
context.Write("(");
}
context.Visit(value);
if (parenthesisRequired)
{
context.Write(")");
}
if (++index % rounds == 0)
{
context.Write(")");
}
}
}
protected virtual void VisitSelect(ExpressionVisitorContext context, SelectClause clause)
{
if (context.Output.Length > 0)
{
context.WriteLine();
}
context.Write("SELECT ");
this.VisitSelectOption(context, clause);
int index = 0;
foreach (var member in clause.Members)
{
if (index++ > 0)
{
context.WriteLine(",");
}
context.Visit(member);
}
}
protected override void OnVisit(ExpressionVisitorContext context, UpsertStatement statement)
{
if (statement.Returning != null && statement.Returning.Table != null)
{
context.Visit(statement.Returning.Table);
}
const string SOURCE_ALIAS = "SRC";
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the upsert statment.");
}
context.Write("MERGE INTO ");
context.Visit(statement.Table);
context.WriteLine(" USING (SELECT ");
for (int i = 0; i < statement.Values.Count; i++)
{
if (i > 0)
{
context.Write(",");
}
context.Visit(statement.Values[i]);
}
context.WriteLine(") AS " + SOURCE_ALIAS + " (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
context.Write(",");
}
context.Write(statement.Fields[i].Name);
}
context.WriteLine(") ON");
for (int i = 0; i < statement.Entity.Key.Length; i++)
{
var field = Metadata.DataEntityPropertyExtension.GetFieldName(statement.Entity.Key[i], out _);
if (i > 0)
{
context.Write(" AND ");
}
if (string.IsNullOrEmpty(statement.Table.Alias))
{
context.Write($"{field}={SOURCE_ALIAS}.{field}");
}
else
{
context.Write($"{statement.Table.Alias}.{field}={SOURCE_ALIAS}.{field}");
}
}
if (statement.Updation.Count > 0)
{
context.WriteLine();
context.Write("WHEN MATCHED");
if (statement.Where != null)
{
context.Write(" AND ");
context.Visit(statement.Where);
}
context.WriteLine(" THEN");
context.Write("\tUPDATE SET ");
int index = 0;
foreach (var item in statement.Updation)
{
if (index++ > 0)
{
context.Write(",");
}
context.Visit(item.Field);
context.Write("=");
var parenthesisRequired = item.Value is IStatementBase;
if (parenthesisRequired)
{
context.Write("(");
}
context.Visit(item.Value);
if (parenthesisRequired)
{
context.Write(")");
}
}
}
context.WriteLine();
context.WriteLine("WHEN NOT MATCHED THEN");
context.Write("\tINSERT (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
context.Write(",");
}
context.Write(context.Dialect.GetIdentifier(statement.Fields[i]));
}
context.Write(") VALUES (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
context.Write(",");
}
context.Write(SOURCE_ALIAS + "." + statement.Fields[i].Name);
}
context.Write(")");
//输出返回子句
this.VisitReturning(context, statement.Returning);
context.WriteLine(";");
}