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");
}
}
protected override void OnVisit(ExpressionVisitorContext context, ExistStatement statement)
{
//调用基类同名方法
base.OnVisit(context, statement);
//限制最多只返回一条记录
context.Write(" LIMIT 1");
}
protected override void VisitSelectOption(ExpressionVisitorContext context, SelectClause clause)
{
//调用基类同名方法
base.VisitSelectOption(context, clause);
//限制只返回一条记录
context.Write("TOP(1) ");
}
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}'.");
}
}
}
}
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 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 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)
{
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 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(";");
}
