public static QsiActionNode VisitUpsertStatement(UpsertStatement node)
{
var insertAction = new PUpsertActionNode
{
Hints = node.Hint?.Hints
};
if (TableVisitor.VisitNamedTableNode(node.Table) is QsiTableAccessNode tableAccessNode)
{
insertAction.Target.SetValue(tableAccessNode);
}
else
{
throw new QsiException(QsiError.Syntax);
}
if (node.Columns.Any())
{
insertAction.Columns = node.Columns
.Select(IdentifierVisitor.Visit)
.ToArray();
}
if (node.Values.Any())
{
var row = new QsiRowValueExpressionNode();
row.ColumnValues.AddRange(node.Values.Select(ExpressionVisitor.Visit));
insertAction.Values.Add(row);
}
else if (node.Select != null)
{
insertAction.ValueTable.SetValue(TableVisitor.VisitSelectStatement(node.Select));
}
if (node.OnDupKeyIgnore)
{
insertAction.ConflictBehavior = QsiDataConflictBehavior.Ignore;
}
else if (node.OnDupKeyPairs.Any())
{
var conflictAction = new QsiDataConflictActionNode();
conflictAction.SetValues.AddRange(node.OnDupKeyPairs.Select(VisitDupKeyPair));
insertAction.ConflictBehavior = QsiDataConflictBehavior.Update;
insertAction.ConflictAction.SetValue(conflictAction);
}
PTree.RawNode[insertAction] = node;
return(insertAction);
}
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(IExpressionVisitor visitor, 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.");
}
visitor.Output.Append("MERGE INTO ");
visitor.Visit(statement.Table);
//visitor.Output.Append(" AS " + TARGET_ALIAS);
visitor.Output.AppendLine(" USING (SELECT ");
for (int i = 0; i < statement.Values.Count; i++)
{
if (i > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(statement.Values[i]);
}
visitor.Output.AppendLine(") AS " + SOURCE_ALIAS + " (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
visitor.Output.Append(",");
}
visitor.Output.Append("[" + statement.Fields[i].Name + "]");
}
visitor.Output.AppendLine(") ON");
for (int i = 0; i < statement.Entity.Key.Length; i++)
{
var field = Metadata.DataEntityPropertyExtension.GetFieldName(statement.Entity.Key[i], out _);
if (i > 0)
{
visitor.Output.Append(" AND ");
}
if (string.IsNullOrEmpty(statement.Table.Alias))
{
visitor.Output.Append($"[{field}]={SOURCE_ALIAS}.[{field}]");
}
else
{
visitor.Output.Append($"{statement.Table.Alias}.[{field}]={SOURCE_ALIAS}.[{field}]");
}
}
if (statement.Updation.Count > 0)
{
visitor.Output.AppendLine();
visitor.Output.Append("WHEN MATCHED");
if (statement.Where != null)
{
visitor.Output.Append(" AND ");
visitor.Visit(statement.Where);
}
visitor.Output.AppendLine(" THEN");
visitor.Output.Append("\tUPDATE SET ");
int index = 0;
foreach (var item in statement.Updation)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(item.Field);
visitor.Output.Append("=");
visitor.Visit(item.Value);
}
}
visitor.Output.AppendLine();
visitor.Output.AppendLine("WHEN NOT MATCHED THEN");
visitor.Output.Append("\tINSERT (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
visitor.Output.Append(",");
}
visitor.Output.Append(visitor.Dialect.GetIdentifier(statement.Fields[i]));
}
visitor.Output.Append(") VALUES (");
for (int i = 0; i < statement.Fields.Count; i++)
{
if (i > 0)
{
visitor.Output.Append(",");
}
visitor.Output.Append(SOURCE_ALIAS + ".[" + statement.Fields[i].Name + "]");
}
visitor.Output.Append(")");
//输出返回子句
this.VisitOutput(visitor, statement.Returning);
visitor.Output.AppendLine(";");
}
protected override void OnVisit(IExpressionVisitor visitor, UpsertStatement statement)
{
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the upsert statment.");
}
var index = 0;
visitor.Output.Append("INSERT INTO ");
visitor.Visit(statement.Table);
visitor.Output.Append(" (");
foreach (var field in statement.Fields)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(field);
}
index = 0;
visitor.Output.AppendLine(") VALUES ");
foreach (var value in statement.Values)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
if (index % statement.Fields.Count == 1)
{
visitor.Output.Append("(");
}
visitor.Visit(value);
if (index % statement.Fields.Count == 0)
{
visitor.Output.Append(")");
}
}
index = 0;
visitor.Output.AppendLine(" ON DUPLICATE KEY UPDATE ");
for (var i = 0; i < statement.Fields.Count; i++)
{
//忽略主键（即不更新主键的字段值）
if (statement.Fields[i].Token.Property != null && statement.Fields[i].Token.Property.IsPrimaryKey)
{
continue;
}
if (index++ > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(statement.Fields[i]);
visitor.Output.Append("=");
visitor.Visit(statement.Values[i]);
}
visitor.Output.AppendLine(";");
}
protected override void OnVisit(IExpressionVisitor visitor, UpsertStatement statement)
{
if (statement.Fields == null || statement.Fields.Count == 0)
{
throw new DataException("Missing required fields in the upsert statment.");
}
var index = 0;
visitor.Output.Append("INSERT INTO ");
visitor.Visit(statement.Table);
visitor.Output.Append(" (");
foreach (var field in statement.Fields)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(field);
}
index = 0;
visitor.Output.AppendLine(") VALUES ");
foreach (var value in statement.Values)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
if (index % statement.Fields.Count == 1)
{
visitor.Output.Append("(");
}
visitor.Visit(value);
if (index % statement.Fields.Count == 0)
{
visitor.Output.Append(")");
}
}
if (statement.Updation.Count > 0)
{
index = 0;
visitor.Output.AppendLine(" ON DUPLICATE KEY UPDATE ");
foreach (var item in statement.Updation)
{
if (index++ > 0)
{
visitor.Output.Append(",");
}
visitor.Visit(item.Field);
visitor.Output.Append("=");
visitor.Visit(item.Value);
}
}
visitor.Output.AppendLine(";");
}
