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 子句,故不输出任何内容,且不调用基类同名方法以避免生成错误的语句。 */ }