protected override void OnVisit(ExpressionVisitorContext context, TableDefinition statement)
{
if (statement.IsTemporary)
{
context.WriteLine($"CREATE 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 VisitOutput(ExpressionVisitorContext context, ReturningClause returning)
{
if (returning == null)
{
return;
}
context.WriteLine();
context.Write("OUTPUT ");
if (returning.Members == null || returning.Members.Count == 0)
{
context.Write("DELETED.*");
}
else
{
int index = 0;
foreach (var member in returning.Members)
{
if (index++ > 0)
{
context.Write(",");
}
context.Write((member.Mode == ReturningClause.ReturningMode.Deleted ? "DELETED." : "INSERTED.") + member.Field.Name);
}
}
if (returning.Table != null)
{
context.Write(" INTO ");
context.Write(context.Dialect.GetIdentifier(returning.Table.Identifier()));
}
}
protected override void VisitWhere(ExpressionVisitorContext context, UpdateStatement statement, IExpression where)
{
/*
* 注意：由于 MySQL 的 UPDATE 语句不支持 FROM 子句，因此必须将其改写为多表修改的语法。
* 由于 FROM 子句中可能包含 JOIN 类型语句，所以必须将 JOIN 子句中的条件式添加到 UPDATE 语句的 WHERE 子句中。
*/
if (statement.HasFrom)
{
var conditions = ConditionExpression.And();
foreach (var source in statement.From)
{
if (source is JoinClause join)
{
conditions.Add(join.Conditions);
}
}
if (conditions.Count > 0)
{
conditions.Add(where);
where = conditions;
}
}
//调用基类同名方法
base.VisitWhere(context, statement, where);
}
protected override void OnVisit(ExpressionVisitorContext context, ExistStatement statement)
{
//调用基类同名方法
base.OnVisit(context, statement);
//限制最多只返回一条记录
context.Write(" LIMIT 1");
}
protected override void VisitFrom(ExpressionVisitorContext context, DeleteStatement statement, ICollection <ISource> sources)
{
//生成OUTPUT(RETURNING)子句
this.VisitOutput(context, statement.Returning);
//调用基类同名方法
base.VisitFrom(context, statement, sources);
}
protected override void VisitSelectOption(ExpressionVisitorContext context, SelectClause clause)
{
//调用基类同名方法
base.VisitSelectOption(context, clause);
//限制只返回一条记录
context.Write("TOP(1) ");
}
protected override void VisitValues(ExpressionVisitorContext context, InsertStatement statement, ICollection <IExpression> values, int rounds)
{
//生成OUTPUT(RETURNING)子句
this.VisitOutput(context, statement.Returning);
//调用基类同名方法
base.VisitValues(context, statement, values, rounds);
}
protected override void OnVisit(ExpressionVisitorContext context, SelectStatement statement)
{
//调用基类同名方法
base.OnVisit(context, statement);
if (statement.Paging != null && statement.Paging.PageSize > 0)
{
this.VisitPaging(context, statement.Paging);
}
}
public string GetDeleteSql <T>(Expression <Func <T, bool> > expression, out IDictionary <string, dynamic> param)
{
var context = new ExpressionVisitorContext(SqlGenerator);
var visitor = new WhereExpressionVisitor(context);
visitor.Visit(expression);
var whereSql = visitor.Condition.ToSql(SqlGenerator);
param = context.Parameters;
var targetMapper = TypeMapperCache.GetTypeMapper(typeof(T));
return(SqlGenerator.GetDeleteSql(targetMapper, null, whereSql));
}
protected virtual void VisitPaging(ExpressionVisitorContext context, Paging paging)
{
if (context.Output.Length > 0)
{
context.WriteLine();
}
context.Write("LIMIT " + paging.PageSize.ToString());
if (paging.PageIndex > 1)
{
context.Write(" OFFSET " + ((paging.PageIndex - 1) * paging.PageSize).ToString());
}
}
protected virtual void VisitPaging(ExpressionVisitorContext context, Paging paging)
{
if (context.Output.Length > 0)
{
context.WriteLine();
}
if (paging.PageIndex > 0)
{
context.Write($"OFFSET {(paging.PageIndex - 1) * paging.PageSize} ROWS FETCH NEXT {paging.PageSize} ROWS ONLY");
}
else
{
context.Write($"OFFSET 0 ROWS FETCH NEXT {paging.PageSize} ROWS ONLY");
}
}
public string GetUpdateSql <T>(Expression <Func <T, bool> > expression, Expression <Func <T, object> > paramInput, object value, out IDictionary <string, dynamic> paramOuput)
{
var context = new ExpressionVisitorContext(SqlGenerator);
var whereVisitor = new WhereExpressionVisitor(context);
whereVisitor.Visit(expression);
var whereSql = whereVisitor.Condition.ToSql(SqlGenerator);
var setVisitor = new MemberExpressionVisitor(context);
setVisitor.Visit(paramInput);
var setSql = string.Format("{0} = {1}", setVisitor.Column.ToSql(SqlGenerator), context.AddParameter(value));
paramOuput = context.Parameters;
return(SqlGenerator.GetUpdateSql(TypeMapperCache.GetTypeMapper(typeof(T)), setSql, whereSql));
}
protected override void VisitTables(ExpressionVisitorContext context, UpdateStatement statement, IList <TableIdentifier> tables)
{
if (tables.Count > 1)
{
throw new DataException("The generated Update statement is incorrect, The data engine does not support multi-table updates.");
}
if (string.IsNullOrEmpty(tables[0].Alias))
{
context.Visit(tables[0]);
}
else
{
context.Write(tables[0].Alias);
}
}
protected override void VisitTables(ExpressionVisitorContext context, UpdateStatement statement, IList <TableIdentifier> tables)
{
//调用基类同名方法
base.VisitTables(context, statement, tables);
/*
* 注意：由于 MySQL 的 UPDATE 语句不支持 FROM 子句，因此必须将其改写为多表修改的语法。
*/
if (statement.HasFrom)
{
foreach (var source in statement.From)
{
switch (source)
{
case TableIdentifier table:
if (!tables.Contains(table))
{
context.Write(",");
context.Visit(table);
}
break;
case JoinClause join:
if (join.Target is TableIdentifier target)
{
if (!tables.Contains(target))
{
context.Write(",");
context.Visit(target);
}
}
else
{
throw new DataException($"The {MySqlDriver.Key} driver does not support the FROM clause of the UPDATE statement contain an expression of type '{join.Target.GetType().Name}'.");
}
break;
default:
throw new NotSupportedException($"The {MySqlDriver.Key} driver does not support the FROM clause of the UPDATE statement contain an expression of type '{source.GetType().Name}'.");
}
}
}
}
public string GetUpdateSql <T>(Expression <Func <T, bool> > expression, IDictionary <Expression <Func <T, object> >, object> paramInput, out IDictionary <string, dynamic> paramOuput)
{
var context = new ExpressionVisitorContext(SqlGenerator);
var whereVisitor = new WhereExpressionVisitor(context);
whereVisitor.Visit(expression);
var whereSql = whereVisitor.Condition.ToSql(SqlGenerator);
var setVisitor = new MemberExpressionVisitor(context);
var setSql = new StringBuilder();
foreach (KeyValuePair <Expression <Func <T, object> >, object> item in paramInput)
{
setVisitor.Visit(item.Key);
setSql.AppendFormat("{0} = {1},", setVisitor.Column.ToSql(SqlGenerator), context.AddParameter(item.Value));
}
paramOuput = context.Parameters;
return(SqlGenerator.GetUpdateSql(TypeMapperCache.GetTypeMapper(typeof(T)), setSql.Remove(setSql.Length - 1, 1).ToString(), whereSql));
}
protected override void VisitStatement(ExpressionVisitorContext context, IStatementBase statement)
{
switch (statement)
{
case TableDefinition table:
MySqlTableDefinitionVisitor.Instance.Visit(context, table);
break;
case SelectStatement select:
MySqlSelectStatementVisitor.Instance.Visit(context, select);
break;
case DeleteStatement delete:
MySqlDeleteStatementVisitor.Instance.Visit(context, delete);
break;
case InsertStatement insert:
MySqlInsertStatementVisitor.Instance.Visit(context, insert);
break;
case UpdateStatement update:
MySqlUpdateStatementVisitor.Instance.Visit(context, update);
break;
case UpsertStatement upsert:
MySqlUpsertStatementVisitor.Instance.Visit(context, upsert);
break;
case AggregateStatement aggregate:
MySqlAggregateStatementVisitor.Instance.Visit(context, aggregate);
break;
case ExistStatement exist:
MySqlExistStatementVisitor.Instance.Visit(context, exist);
break;
case ExecutionStatement execution:
MySqlExecutionStatementVisitor.Instance.Visit(context, execution);
break;
default:
throw new DataException($"Not supported '{statement}' statement.");
}
}
protected override void OnVisit(ExpressionVisitorContext context, SelectStatement statement)
{
//由于分页子句必须依赖于排序(OrderBy)子句，所以在没有指定排序子句的情况下默认以主键进行排序
if (statement.Paging != null && statement.Paging.PageSize > 0 && statement.OrderBy == null && statement.Table != null)
{
statement.OrderBy = new OrderByClause();
foreach (var key in statement.Table.Entity.Key)
{
statement.OrderBy.Add(statement.Table.CreateField(key));
}
}
//调用基类同名方法
base.OnVisit(context, statement);
if (statement.Paging != null && statement.Paging.PageSize > 0 && statement.OrderBy != null)
{
this.VisitPaging(context, statement.Paging);
}
}
protected override void VisitExists(ExpressionVisitorContext context, IExpression expression)
{
//查找当前表达式所属的语句
var statement = context.Find <IStatement>();
//如果当前 Exists/NotExists 表达式位于查询语句，则不需要添加额外的包裹层
if (statement is SelectStatementBase)
{
base.VisitExists(context, expression);
return;
}
/*
* 注意：由于 MySQL 不支持在 Update/Delete 等写语句中的 Exists/NotExists 子句中包含上层表别名
* 解决该缺陷的小窍门是对其内部的子查询语句再包裹一个查询语句，可参考：
* https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
* https://stackoverflow.com/questions/5816840/delete-i-cant-specify-target-table
* https://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO
*/
context.Write("SELECT * FROM (");
base.VisitExists(context, expression);
context.WriteLine(") AS t_" + Zongsoft.Common.Randomizer.GenerateString());
}
protected override void OnVisit(ExpressionVisitorContext context, UpsertStatement statement)
{
const string SOURCE_ALIAS = "SRC";
const string TARGET_ALIAS = "TAR";
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);
//visitor.Output.Append(" AS " + TARGET_ALIAS);
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.WriteLine(" 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.VisitOutput(context, statement.Returning);
context.WriteLine(";");
}
protected override void OnVisit(ExpressionVisitorContext context, UpsertStatement statement)
{
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the upsert statment.");
}
var index = 0;
context.Write("INSERT INTO ");
//visitor.Visit(statement.Table);
context.Write(context.Dialect.GetIdentifier(statement.Table));
context.Write(" (");
foreach (var field in statement.Fields)
{
if (index++ > 0)
{
context.Write(",");
}
//visitor.Visit(field);
context.Write(context.Dialect.GetIdentifier(field.Name));
}
index = 0;
context.WriteLine(") VALUES ");
foreach (var value in statement.Values)
{
if (index++ > 0)
{
context.Write(",");
}
if (index % statement.Fields.Count == 1)
{
context.Write("(");
}
var parenthesisRequired = value is IStatementBase;
if (parenthesisRequired)
{
context.Write("(");
}
context.Visit(value);
if (parenthesisRequired)
{
context.Write(")");
}
if (index % statement.Fields.Count == 0)
{
context.Write(")");
}
}
index = 0;
context.WriteLine(" ON DUPLICATE KEY UPDATE ");
if (statement.Updation.Count > 0)
{
foreach (var item in statement.Updation)
{
if (index++ > 0)
{
context.Write(",");
}
context.Write(context.Dialect.GetIdentifier(item.Field));
context.Write("=");
var parenthesisRequired = item.Value is IStatementBase;
if (parenthesisRequired)
{
context.Write("(");
}
context.Visit(item.Value);
if (parenthesisRequired)
{
context.Write(")");
}
}
}
else
{
foreach (var field in statement.Fields)
{
//忽略修改序列字段
if (field.Token.Property is Metadata.IDataEntitySimplexProperty simplex && simplex.Sequence != null)
{
continue;
}
if (index++ > 0)
{
context.Write(",");
}
context.Write(context.Dialect.GetIdentifier(field.Name));
context.Write("=VALUES(");
context.Write(context.Dialect.GetIdentifier(field.Name));
context.Write(")");
}
}
context.WriteLine(";");
}
protected override void VisitFrom(ExpressionVisitorContext context, UpdateStatement statement, ICollection <ISource> sources)
{
/*
* 由于 MySQL 的 UPDATE 语句不支持 FROM 子句，故不输出任何内容，且不调用基类同名方法以避免生成错误的语句。
*/
}
