/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected override IDbDataParameter CreateParameter(object value, ITranslateContext context,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
if (value is bool)
{
value = ((bool)value) ? 1 : 0;
}
else if (value is Guid)
{
value = ((Guid)value).ToByteArray();
dbType = OracleDbType.Raw;
}
else if (value is DateTimeOffset)
{
var dto = (DateTimeOffset)value;
var zone = (dto.Offset < TimeSpan.Zero ? "-" : "+") + dto.Offset.ToString("hh\\:mm");
value = new OracleTimeStampTZ(dto.DateTime, zone);
}
// 补充 DbType
OracleParameter oracleParameter = (OracleParameter)base.CreateParameter(value, context, dbType, size, precision, scale, direction);
oracleParameter.DbType(dbType);
return(oracleParameter);
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected override IDbDataParameter CreateParameter(object value, ITranslateContext context,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
if (value is Guid)
{
// 转换一下GUID，否则保存后会出现乱码~
value = ((Guid)value).ToString();
}
else if (value is TimeSpan || value is DateTime || value is DateTimeOffset)
{
string result = null;
if (value is TimeSpan)
{
result = this.GetSqlValueOfTime(value, dbType, scale);
}
else if (value is DateTime)
{
result = this.GetSqlValueOfDateTime(value, dbType, scale);
}
else if (value is DateTimeOffset)
{
result = this.GetSqlValueOfDateTimeOffset(value, dbType, scale);
}
result = result.Trim('\'');
value = result;
dbType = DbType.String;
}
// 补充 DbType
SQLiteParameter sqliteParameter = (SQLiteParameter)base.CreateParameter(value, context, dbType, size, precision, scale, direction);
sqliteParameter.DbType(dbType);
return(sqliteParameter);
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// <para>因为会使用到默认值，故此重载仅限 INSERT / UPDATE 时使用</para>
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="column">数据列特性</param>
/// <returns></returns>
public string GetSqlValueWidthDefault(object value, ITranslateContext context, ColumnAttribute column)
{
if (value == null && column != null && column.Default != null)
{
// 插入值为空时使用默认值
return(column.Default.ToString());
}
if (value != null && column != null && column.Default != null)
{
// 值不为空，但只有.net默认值时也使用数据库默认值
Type t = value.GetType();
bool useDefault = false;
if (t == typeof(DateTime) && (((DateTime)value) == DateTime.MinValue || ((DateTime)value).ToString("yyyy-MM-dd") == "0001-01-01"))
{
useDefault = true;
}
// 插入值为空时使用默认值
if (useDefault)
{
return(column.Default.ToString());
}
}
return(this.GetSqlValue(value, context, column));
}
/// <summary>
/// 生成 value 对应的 SQL 片断
/// </summary>
/// <param name="value">SQL值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="column">数据列特性</param>
/// <returns></returns>
public string GetSqlValue(object value, ITranslateContext context, ColumnAttribute column)
{
return(column == null
? this.GetSqlValue(value, context, null, null, null, null, null)
: this.GetSqlValue(value, context, column.HasSetDbType ? column.DbType : null, column.HasSetSize ? new Nullable <int>(column.Size) : null,
column.HasSetPrecision ? new Nullable <int>(column.Precision) : null, column.HasSetScale ? new Nullable <int>(column.Scale) : null,
column.HasSetDirection ? new Nullable <ParameterDirection>(column.Direction) : null));
}
/// <summary>
/// 初始化 <see cref="WhereExpressionVisitor"/> 类的新实例
/// </summary>
/// <param name="ag">表别名解析器</param>
/// <param name="builder">SQL 语句生成器</param>
public WhereExpressionVisitor(AliasGenerator ag, ISqlBuilder builder)
: base(ag, builder)
{
_builder = builder;
_provider = _builder.Provider;
_constor = ((DbQueryProvider)_provider).Constor;
_context = _builder.TranslateContext;
}
/// <summary>
/// 实例化 <see cref="NpgSqlBuilder"/> 类的新实例
/// </summary>
/// <param name="context">解析SQL命令上下文</param>
public NpgSqlBuilder(ITranslateContext context)
: base(context)
{
var provider = context.Provider;
_escCharLeft = provider.QuotePrefix;
_escCharRight = provider.QuoteSuffix;
_caseSensitive = context != null && context.DbContext != null ? ((NpgDbContext)context.DbContext).CaseSensitive : false;
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected virtual IDbDataParameter CreateParameter(object value, ITranslateContext context,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
string name = string.Format("{0}{1}{2}", _provider.ParameterPrefix, AppConst.PARAMETER_NAME_PRIFIX, context.Parameters.Count);
var dbParameter = _provider.DbProvider.CreateParameter(name, value, null, size, precision, scale, direction);
context.Parameters.Add(dbParameter);
return(dbParameter);
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected override IDbDataParameter CreateParameter(object value, ITranslateContext context,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
// 补充 DbType
SqlParameter sqlParameter = (SqlParameter)base.CreateParameter(value, context, dbType, size, precision, scale, direction);
sqlParameter.DbType(dbType);
return(sqlParameter);
}
/// <summary>
/// 实例化 <see cref="SqlServerDbSelectCommand"/> 类的新实例
/// </summary>
/// <param name="context">解析SQL命令上下文</param>
/// <param name="ag">表别名解析器</param>
/// <param name="hasMany">是否包含一对多导航属性</param>
public SqlServerDbSelectCommand(ITranslateContext context, AliasGenerator ag, bool hasMany)
: base(context, ag, hasMany)
{
_ag = ag;
var dc = context.DbContext;
_isNoLock = ((SqlServerDbContext)dc).NoLock;
_withNoLock = ((SqlServerDbQueryProvider)dc.Provider).WidthNoLock;
}
/// <summary>
/// 实例化 <see cref="SqlBuilder"/> 类的新实例
/// </summary>
/// <param name="context">解析SQL命令上下文</param>
public SqlBuilder(ITranslateContext context)
{
_context = context;
_innerBuilder = new StringBuilder(128);
var provider = _context.Provider;
_constor = ((DbQueryProvider)provider).Constor;
_escCharLeft = provider.QuotePrefix;
_escCharRight = provider.QuoteSuffix;
_escCharQuote = provider.SingleQuoteChar;
}
/// <summary>
/// 实例化 <see cref="DbSelectCommand"/> 类的新实例
/// </summary>
/// <param name="context">解析SQL命令上下文</param>
/// <param name="ag">别名</param>
/// <param name="hasMany">是否包含一对多导航属性</param>
public DbSelectCommand(ITranslateContext context, AliasGenerator ag, bool hasMany)
: base(string.Empty, context != null ? context.Parameters : null, System.Data.CommandType.Text)
{
_ag = ag;
_context = context;
_hasMany = hasMany;
var provider = (DbQueryProvider)_context.DbContext.Provider;
_joinFragment = provider.CreateSqlBuilder(context);
_whereFragment = provider.CreateSqlBuilder(context);
}
/// <summary>
/// 访问 IDbQueryable.Contains 方法
/// </summary>
/// <param name="m">方法表达式</param>
protected override Expression VisitQueryableContains(MethodCallExpression m)
{
ITranslateContext context = _builder.TranslateContext;
var subquery = m.Arguments[0].Evaluate().Value as DbQueryable;
subquery.Parameterized = _builder.Parameterized;
// 可能会有几级嵌套，这里用 Builder.Ident 标记是第几层级
string[] subs = new[] { "p", "u", "v", "w", "x" };
var newContext = context != null?TranslateContext.Copy(context, subs[_builder.Indent]) : null;
var cmd = subquery.Translate(_builder.Indent + 1, false, newContext) as DbSelectCommand;
bool isDelete = context != null && ((MySqlTranslateContext)context).IsDelete;
if (this.NotOperands != null && this.NotOperands.Contains(m))
{
_builder.Append("NOT ");
}
_builder.Append("EXISTS(");
if (isDelete)
{
_builder.Append("SELECT 1 FROM(");
_builder.Append(cmd.CommandText);
_builder.Append(string.Format(") {0}0 WHERE ", subs[_builder.Indent]));
_builder.Append(cmd.SelectedColumnText);
_builder.Append(" = ");
_visitor.Visit(m.Arguments[1]);
_builder.Append(")");
}
else
{
_builder.Append(cmd.CommandText);
if (cmd.WhereFragment.Length > 0)
{
_builder.Append(" AND ");
}
else
{
_builder.Append("WHERE ");
}
_builder.Append(cmd.SelectedColumnText);
_builder.Append(" = ");
_visitor.Visit(m.Arguments[1]);
_builder.Append(")");
}
return(m);
}
/// <summary>
/// 使用新的别名拷贝一个新的上下文
/// </summary>
/// <param name="source"></param>
/// <param name="prifix"></param>
/// <returns></returns>
public static ITranslateContext Copy(ITranslateContext source, string prifix)
{
var context = source.DbContext;
var provider = source.DbContext.Provider;
var result = ((DbQueryProvider)provider).CreateTranslateContext(context);
result.Parameters = source.Parameters;
result.AliasPrefix = prifix;
result.CurrentExpressionType = source.CurrentExpressionType;
result.CurrentIsOutermost = source.CurrentIsOutermost;
return(result);
}
/// <summary>
/// 增加一个SQL参数
/// </summary>
/// <param name="value">值</param>
/// <param name="context">解析SQL命令时的参数上下文</param>
/// <param name="dbType">数据类型</param>
/// <param name="size">长度</param>
/// <param name="precision">精度</param>
/// <param name="scale">小数位</param>
/// <param name="direction">查询参数类型</param>
/// <returns></returns>
protected override IDbDataParameter CreateParameter(object value, ITranslateContext context,
object dbType, int?size = null, int?precision = null, int?scale = null, ParameterDirection?direction = null)
{
#if !netcore
if (value is TimeSpan)
{
// 如果不是 netcore，需要将timespan转为datetime类型才可以保存
value = new DateTime(((TimeSpan)value).Ticks);
dbType = NpgsqlDbType.Timestamp;
}
#endif
// 补充 DbType
NpgsqlParameter parameter = (NpgsqlParameter)base.CreateParameter(value, context, dbType, size, precision, scale, direction);
parameter.DbType(dbType);
return(parameter);
}
/// <summary>
/// 实例化 <see cref="NpgDbSelectCommand" /> 的新实例
/// </summary>
/// <param name="context">解析SQL命令上下文</param>
/// <param name="aliasGenerator">别名</param>
/// <param name="dbExpressionType">表达式类型</param>
/// <param name="hasMany">是否包含一对多导航属性</param>
public NpgDbSelectCommand(ITranslateContext context, AliasGenerator aliasGenerator, DbExpressionType dbExpressionType, bool hasMany)
: base(context, aliasGenerator, hasMany)
{
_aliasGenerator = aliasGenerator;
_onPhrase = ((DbQueryProvider)context.Provider).CreateSqlBuilder(context);
_dbExpressionType = dbExpressionType;
if (_dbExpressionType == DbExpressionType.Delete)
{
_keywordName = "USING ";
}
else if (_dbExpressionType == DbExpressionType.Update)
{
_keywordName = "FROM ";
}
_pad = "".PadLeft(_keywordName.Length, ' ');
}
/// <summary>
/// 访问 IDbQueryable.Contains 方法
/// </summary>
/// <param name="m">字段或属性表达式</param>
protected override Expression VisitQueryableContains(MethodCallExpression m)
{
ITranslateContext context = _builder.TranslateContext;
DbQueryable subquery = m.Arguments[0].Evaluate().Value as DbQueryable;
subquery.Parameterized = _builder.Parameterized;
// 可能会有几级嵌套，这里用 Builder.Ident 标记是第几层级
string[] subs = new[] { "p", "u", "v", "w", "x" };
var newContext = context != null?TranslateContext.Copy(context, subs[_builder.Indent]) : null;
bool isDelete = context != null && ((SQLiteTranslateContext)context).IsDelete;
var cmd = subquery.Translate(_builder.Indent + 1, false, newContext) as DbSelectCommand;
if (this.NotOperands != null && this.NotOperands.Contains(m))
{
_builder.Append("NOT ");
}
_builder.Append("EXISTS(");
_builder.Append(cmd.CommandText);
if (((DbSelectCommand)cmd).WhereFragment.Length > 0)
{
_builder.Append(" AND ");
}
else
{
_builder.Append("WHERE ");
}
_builder.Append(cmd.SelectedColumnText);
_builder.Append(" = ");
// exists 不能用别名
if (isDelete)
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(((MemberExpression)m.Arguments[1]).Expression.Type);
_builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
_builder.Append('.');
}
_visitor.Visit(m.Arguments[1]);
_builder.Append(")");
return(m);
}
// 生成集合对应的 SQL 片断
private string GetSqlValue(IEnumerable value, ITranslateContext context, object dbType, int?size, int?precision, int?scale, ParameterDirection?direction = null)
{
if (value == null)
{
return("NULL");
}
var iterator = value.GetEnumerator();
List <string> sqlValues = new List <string>();
while (iterator.MoveNext())
{
string text = this.GetSqlValue(iterator.Current, context, dbType, size, precision, scale, direction);
sqlValues.Add(text);
}
// =>a,b,c
string sql = string.Join(",", sqlValues);
return(sql);
}
/// <summary>
/// 访问参数列表
/// </summary>
/// <param name="expression">将访问的表达式</param>
/// <param name="isFilter">是否过滤条件</param>
internal void VisitArgument(Expression expression, bool isFilter = false)
{
ITranslateContext context = _builder.TranslateContext;
_tree.Select.Select = new DbExpression(DbExpressionType.Select, expression);
// 可能会有几级嵌套，这里用 Builder.Ident 标记是第几层级
string[] subs = new[] { "p", "u", "v", "w", "x" };
var newContext = context != null?TranslateContext.Copy(context, subs[_builder.Indent]) : null;
var cmd = Translator.Invoke(_tree.Select, _builder.Indent + 1, false, newContext) as DbSelectCommand;
_builder.Append('(');
_builder.Append(cmd.CommandText.Trim());
if (((DbSelectCommand)cmd).WhereFragment.Length > 0)
{
_builder.Append(" AND ");
}
else
{
_builder.Append("WHERE ");
}
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(_tree.Entity != null ? _tree.Entity.GetType() : _tree.Select.From);
foreach (var m in typeRuntime.KeyMembers)
{
_builder.AppendMember(string.Format("{0}0", subs[_builder.Indent]), m.Member, typeRuntime.Type);
_builder.Append(" = ");
_builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
_builder.Append('.');
_builder.AppendMember(null, m.Member, typeRuntime.Type);
_builder.Append(" AND ");
}
_builder.Length -= 5;
_builder.Append(')');
}
/// <summary>
/// 创建 DELETE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateDeleteCommand <T>(DbQueryDeleteTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("DELETE FROM ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0 ");
if (tree.Entity != null)
{
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
{
throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
}
object entity = tree.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (FieldAccessorBase m in typeRuntime.KeyMembers)
{
var value = m.Invoke(entity);
var sqlExpression = this.Constor.GetSqlValue(value, context, m.Column);
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
builder.Append(sqlExpression);
builder.Append(" AND ");
}
builder.Length -= 5;
}
else if (tree.Select != null)
{
// 解析查询用来确定是否需要嵌套
var cmd = this.TranslateSelectCommand(tree.Select, 0, false, this.CreateTranslateContext(context.DbContext)) as DbSelectCommand;
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || (tree.Select.Joins != null && tree.Select.Joins.Count > 0))
{
// 最外层仅选择 RowID 列
var outQuery = tree.Select;
outQuery.Select = new DbExpression(DbExpressionType.Select, Expression.Constant("t0.RowId", typeof(string)));
var iterator = outQuery;
while (iterator.Subquery != null)
{
var subquery = new OracleDbQuerySelectTree(iterator.Subquery);
iterator.Subquery = subquery;
iterator = subquery;
}
// 解析成 RowId IN 结构
cmd = (DbSelectCommand)this.TranslateSelectCommand(tree.Select, 1, false, context);
builder.Append("WHERE t0.RowId IN(");
builder.AppendNewLine(cmd.CommandText);
builder.Append(')');
}
else
{
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
if (tree.Select.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, builder);
visitor.Visit(tree.Select.Joins);
}
if (tree.Select.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, builder);
visitor.Visit(tree.Select.Wheres);
}
}
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 UPDATE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateUpdateCommand <T>(DbQueryUpdateTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0");
if (tree.Entity != null)
{
object entity = tree.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(context);
bool useKey = false;
int length = 0;
builder.AppendNewLine(" SET");
foreach (var item in typeRuntime.Members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
if (m.Column != null && m.Column.IsIdentity)
{
goto LABEL; // Fix issue# 自增列同时又是主键
}
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
LABEL:
var value = m.Invoke(entity);
var seg = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
if (m.Column == null || !m.Column.IsIdentity)
{
builder.Append(seg);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (m.Column != null && m.Column.IsKey)
{
useKey = true;
whereBuilder.AppendMember("t0", m.Member, typeRuntime.Type);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
whereBuilder.Append(" AND ");
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require T must have key column.");
}
builder.Length = length;
whereBuilder.Length -= 5;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (tree.Expression != null)
{
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
DbExpressionVisitor visitor = null;
var cmd = new DbSelectCommand(context, ag, tree.Select.SelectHasMany);
if (tree.Select.Joins != null)
{
visitor = new JoinExpressionVisitor(ag, cmd.JoinFragment);
visitor.Visit(tree.Select.Joins);
}
cmd.WhereFragment.AppendNewLine();
cmd.WhereFragment.AppendNewLine("SET");
visitor = new UpdateExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Expression);
if (tree.Select.Wheres != null)
{
visitor = new WhereExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Select.Wheres);
cmd.AddNavMembers(visitor.NavMembers);
}
builder.Append(cmd.CommandText);
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 DELETE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateDeleteCommand <T>(DbQueryDeleteTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("DELETE t0 FROM ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0 ");
if (tree.Entity != null)
{
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
{
throw new XFrameworkException("Delete<T>(T value) require entity must have key column.");
}
object entity = tree.Entity;
builder.AppendNewLine();
builder.Append("WHERE ");
foreach (FieldAccessorBase m in typeRuntime.KeyMembers)
{
var value = m.Invoke(entity);
var sqlExpression = this.Constor.GetSqlValue(value, context, m.Column);
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
builder.Append(sqlExpression);
builder.Append(" AND ");
}
builder.Length -= 5;
}
else if (tree.Select != null)
{
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
var cmd = new DbSelectCommand(context, ag, tree.Select.SelectHasMany);
// 标记当前解析上下文是删除语句产生的
var isDelete = ((MySqlTranslateContext)context).IsDelete;
((MySqlTranslateContext)context).IsDelete = true;
if (tree.Select.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, cmd.JoinFragment);
visitor.Visit(tree.Select.Joins);
}
if (tree.Select.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, cmd.WhereFragment);
visitor.Visit(tree.Select.Wheres);
cmd.AddNavMembers(visitor.NavMembers);
}
builder.Append(cmd.CommandText);
// 恢复当前解析上下文的删除标记
((MySqlTranslateContext)context).IsDelete = isDelete;
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
/// <summary>
/// 创建 INSERT 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
if (tree.Entity != null)
{
object entity = tree.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context);
// 指定插入列
var members = typeRuntime.Members;
if (tree.EntityColumns != null && tree.EntityColumns.Count > 0)
{
members = new MemberAccessorCollection();
for (int i = 0; i < tree.EntityColumns.Count; i++)
{
Expression curExpr = tree.EntityColumns[i];
if (curExpr.NodeType == ExpressionType.Lambda)
{
curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
}
if (curExpr.NodeType != ExpressionType.MemberAccess)
{
throw new XFrameworkException("Can't read field name from expression {0}", tree.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
members[name] = typeRuntime.Members[name];
}
}
foreach (var item in members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
if (m != typeRuntime.Identity)
{
columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type);
columnsBuilder.Append(',');
var value = m.Invoke(entity);
string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
valuesBuilder.Append(sqlExpression);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (tree.Bulk == null || !tree.Bulk.OnlyValue)
{
builder.Append("INSERT INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendTab();
builder.Append("VALUES");
}
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (tree.Bulk != null && !tree.Bulk.IsEndPos)
{
builder.Append(",");
}
if (tree.Bulk == null && typeRuntime.Identity != null)
{
builder.Append(';');
builder.AppendNewLine();
builder.Append("SELECT LAST_INSERT_ID()");
builder.AppendAs(AppConst.AUTO_INCREMENT_NAME);
}
}
else if (tree.Select != null)
{
builder.Append("INSERT INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
var srcExpressionType = context.CurrentExpressionType;
var srcIsOutermost = context.CurrentIsOutermost;
context.CurrentExpressionType = DbExpressionType.Insert;
context.CurrentIsOutermost = true;
var cmd = this.TranslateSelectCommandImpl(tree.Select, 0, true, context) as DbSelectCommand;
context.CurrentExpressionType = srcExpressionType;
context.CurrentIsOutermost = srcIsOutermost;
int index = 0;
foreach (var column in cmd.SelectedColumns)
{
builder.AppendMember(column.NewName);
if (index < cmd.SelectedColumns.Count - 1)
{
builder.Append(',');
}
index++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd.CommandText);
}
if (tree.Bulk == null || tree.Bulk.IsEndPos)
{
builder.Append(';');
}
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
// 解译 SELECT 语句
private DbRawCommand TranslateSelectCommandImpl(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有聚合函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 8.如果只有 Skip 没有 Take，则使用 Row_Number() Over()分页语法，其它使用 LIMIT ## OFFSET 语法
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subquery = tree.Subquery as DbQuerySelectTree;
if (tree.SelectHasMany && subquery != null && subquery.Aggregate != null)
{
tree = subquery;
}
var srcDbExpressionType = context.CurrentExpressionType;
var srcIsOutmost = context.CurrentIsOutermost;
if (srcDbExpressionType == null)
{
context.CurrentExpressionType = DbExpressionType.Select;
}
if (srcIsOutmost == null || !isOutermost)
{
context.CurrentIsOutermost = isOutermost;
}
bool useAggregate = tree.Aggregate != null;
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
// 第一层的表别名
string alias = context != null && !string.IsNullOrEmpty(context.AliasPrefix) ? (context.AliasPrefix + "0") : "t0";
bool useSubquery = tree.HasDistinct || tree.GroupBy != null || tree.Skip > 0 || tree.Take > 0;
bool useOrderBy = (!useAggregate || tree.Skip > 0) && !tree.HasAny && (!tree.SelectHasMany || (tree.Skip > 0 || tree.Take > 0));
AliasGenerator ag = this.PrepareTableAlias(tree, context != null ? context.AliasPrefix : null);
var result = new DbSelectCommand(context, ag, tree.SelectHasMany);
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
ISqlBuilder sf = null;
jf.Indent = indent;
#region 嵌套查询
if (useAggregate && useSubquery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, alias);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
context.CurrentIsOutermost = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (tree.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useAggregate && !useSubquery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor_ = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor_.Visit(tree.Aggregate);
result.AddNavMembers(visitor_.NavMembers);
}
else
{
// DISTINCT 子句
if (tree.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!tree.HasAny)
{
// SELECT 范围
ColumnExpressionVisitor visitor = null;
if (tree.Skip > 0 && tree.Take == 0)
{
sf = this.CreateSqlBuilder(context);
sf.Indent = jf.Indent + 1;
visitor = new ColumnExpressionVisitor(ag, sf, tree);
visitor.Visit(tree.Select);
}
else
{
visitor = new ColumnExpressionVisitor(ag, jf, tree);
visitor.Visit(tree.Select);
}
result.SelectedColumns = visitor.SelectedColumns;
result.SelectedColumnText = visitor.SelectedColumnText;
result.SelectedNavs = visitor.SelectedNavDescriptors;
result.AddNavMembers(visitor.NavMembers);
if (sf != null)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in result.SelectedColumns)
{
jf.AppendMember(alias, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < result.SelectedColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
jf.Append(sf);
jf.Append(',');
jf.AppendNewLine();
if (tree.OrderBys == null || tree.OrderBys.Count == 0)
{
throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'.");
}
jf.Append("ROW_NUMBER() OVER(");
var visitor3 = new OrderByExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor3.Visit(tree.OrderBys, false);
result.AddNavMembers(visitor3.NavMembers);
jf.Append(") Row_Number0");
}
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (tree.Subquery != null)
{
// 子查询
jf.Append("(");
var cmd = this.TranslateSelectCommandImpl(tree.Subquery, indent + 1, false, context);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else if (tree.FromSql != null)
{
if (tree.FromSql.DbContext == null)
{
tree.FromSql.DbContext = context.DbContext;
}
DbRawSql rawSql = tree.FromSql;
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
// 子查询
jf.Append('(');
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(tree.From);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
if (tree.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, jf);
visitor.Visit(tree.Joins);
}
wf.Indent = jf.Indent;
// WHERE 子句
if (tree.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, wf);
visitor.Visit(tree.Wheres);
result.AddNavMembers(visitor.NavMembers);
}
// GROUP BY 子句
if (tree.GroupBy != null)
{
var visitor = new GroupByExpressionVisitor(ag, wf);
visitor.Visit(tree.GroupBy);
result.AddNavMembers(visitor.NavMembers);
}
// HAVING 子句
if (tree.Havings != null)
{
var visitor = new HavingExpressionVisitor(ag, wf, tree.GroupBy);
visitor.Visit(tree.Havings);
result.AddNavMembers(visitor.NavMembers);
}
// ORDER 子句
if (tree.OrderBys != null && tree.OrderBys.Count > 0 && useOrderBy)// && !groupByPaging)
{
var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null);
visitor.Visit(tree.OrderBys);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 分页查询
// LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。
// LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
// 初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。
// Limit n,-1 语法不支持，使用ROW_Number()语法代替
if (tree.Take > 0)
{
wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(tree.Take, context));
wf.AppendFormat(" OFFSET {0}", this.Constor.GetSqlValue(tree.Skip, context));
}
#endregion
#region 嵌套查询
if (useAggregate && useSubquery)
{
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
//if (tree.HasMany && subquery != null && subquery.OrderBys.Count > 0 && subquery.Aggregate == null && !(subquery.Skip > 0 || subquery.Take > 0))
if (tree.SelectHasMany && subquery.Aggregate == null &&
subquery != null && subquery.OrderBys != null && subquery.OrderBys.Count > 0 && !(subquery.Skip > 0 || subquery.Take > 0))
{
result.CombineFragments();
var visitor = new OrderByExpressionVisitor(ag, jf, null, null);
visitor.Visit(subquery.OrderBys);
}
#endregion
#region 并集查询
// UNION 子句
if (tree.Unions != null && tree.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < tree.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbRawCommand cmd = this.TranslateSelectCommandImpl(tree.Unions[index], indent, isOutermost, context);
jf.Append(cmd.CommandText);
}
}
#endregion
#region 分页查询
if (sf != null)
{
// 合并 WHERE
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.AppendNewLine();
jf.Append("WHERE ");
if (tree.Skip > 0)
{
jf.AppendMember(alias, "Row_Number0");
jf.Append(" > ");
jf.Append(this.Constor.GetSqlValue(tree.Skip, context));
}
}
#endregion
#region Any 子句
// 'Any' 子句
if (tree.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (tree.Take == 0 && tree.Skip == 0)
{
jf.AppendNewLine();
jf.Append("LIMIT 0,1");
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 还原状态
context.CurrentExpressionType = srcDbExpressionType;
context.CurrentIsOutermost = srcIsOutmost;
#endregion
return(result);
}
/// <summary>
/// 解析 SELECT 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="indent">缩进</param>
/// <param name="isOutermost">是否是最外层查询</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateSelectCommand(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context)
{
var cmd = (DbSelectCommand)this.TranslateSelectCommandImpl(tree, indent, isOutermost, context);
cmd.CombineFragments();
if (isOutermost)
{
cmd.JoinFragment.Append(';');
}
return(cmd);
}
/// <summary>
/// 解析 SELECT 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="indent">缩进</param>
/// <param name="isOutermost">是否是最外层查询</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateSelectCommand(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context)
{
// 说明：
// 1.OFFSET 前必须要有 'ORDER BY'，即 'Skip' 子句前必须使用 'OrderBy' 子句
// 2.在有聚合函数的<MAX,MIN...>情况下，如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句，则需要使用嵌套查询
// 3.'Any' 子句将翻译成 IF EXISTS...
// 4.分组再分页时需要使用嵌套查询，此时子查询不需要 'OrderBy' 子句，但最外层则需要
// 5.'Skip' 'Take' 子句视为语义结束符，在其之后的子句将使用嵌套查询
// 6.导航属性中有 1:n 关系的，需要使用嵌套查询，否则分页查询会有问题
// 8.如果有分页，则使用嵌套
// 9.如果有分页还有OrderBy，则使用嵌套的嵌套
// 导航属性中有1:n关系，只统计主表
// 例：AccountList = a.Client.AccountList,
var subquery = tree.Subquery as DbQuerySelectTree;
if (tree.SelectHasMany && subquery != null && subquery.Aggregate != null)
{
tree = subquery;
}
var srcDbExpressionType = context.CurrentExpressionType;
var srcIsOutmost = context.CurrentIsOutermost;
if (srcDbExpressionType == null)
{
context.CurrentExpressionType = DbExpressionType.Select;
}
if (srcIsOutmost == null || !isOutermost)
{
context.CurrentIsOutermost = isOutermost;
}
bool useAggregate = tree.Aggregate != null;
// 没有聚合函数或者使用 'Skip' 子句，则解析OrderBy
// 导航属性如果使用嵌套，除非有 TOP 或者 OFFSET 子句，否则不能用ORDER BY
// 第一层的表别名
string alias = context != null && !string.IsNullOrEmpty(context.AliasPrefix) ? (context.AliasPrefix + "0") : "t0";
bool useSubquery = tree.HasDistinct || tree.GroupBy != null || tree.Skip > 0 || tree.Take > 0;
bool useOrderBy = (!useAggregate || tree.Skip > 0) && !tree.HasAny && (!tree.SelectHasMany || (tree.Skip > 0 || tree.Take > 0));
AliasGenerator ag = this.PrepareTableAlias(tree, context != null ? context.AliasPrefix : null);
var result = new DbSelectCommand(context, ag, tree.SelectHasMany);
ISqlBuilder jf = result.JoinFragment;
ISqlBuilder wf = result.WhereFragment;
(jf as OracleSqlBuilder).UseQuote = isOutermost;
jf.Indent = indent;
#region 嵌套查询
if (useAggregate && useSubquery)
{
// SELECT
jf.Append("SELECT ");
jf.AppendNewLine();
// SELECT COUNT(1)
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, alias);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
// SELECT COUNT(1) FROM
jf.AppendNewLine();
jf.Append("FROM ( ");
indent += 1;
jf.Indent = indent;
(jf as OracleSqlBuilder).UseQuote = false;
context.CurrentIsOutermost = false;
}
#endregion
#region 择子句
// SELECT 子句
if (jf.Indent > 0)
{
jf.AppendNewLine();
}
jf.Append("SELECT ");
if (tree.HasAny)
{
jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM (");
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT 1 ");
}
if (useAggregate && !useSubquery)
{
// 如果有聚合函数，并且不是嵌套的话，则直接使用SELECT <MAX,MIN...>，不需要解析选择的字段
jf.AppendNewLine();
var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, null);
visitor.Visit(tree.Aggregate);
result.AddNavMembers(visitor.NavMembers);
}
else
{
// DISTINCT 子句
if (tree.HasDistinct)
{
jf.Append("DISTINCT ");
}
#region 择字段
if (!tree.HasAny)
{
// SELECT 范围
ISqlBuilder sf = this.CreateSqlBuilder(context);
sf.Indent = jf.Indent + ((tree.Skip > 0 || tree.Take > 0) ? 2 : 0);
(sf as OracleSqlBuilder).UseQuote = (tree.Skip > 0 || tree.Take > 0) ? false : (jf as OracleSqlBuilder).UseQuote;
var visitor = new OracleColumnExpressionVisitor(ag, sf, tree);
visitor.Visit(tree.Select);
result.SelectedColumns = visitor.SelectedColumns;
result.SelectedColumnText = visitor.SelectedColumnText;
result.SelectedNavs = visitor.SelectedNavDescriptors;
result.AddNavMembers(visitor.NavMembers);
// 分页，产生两层嵌套
if (tree.Skip > 0 || tree.Take > 0)
{
// 第一层嵌套
int index = 0;
jf.AppendNewLine();
foreach (var column in result.SelectedColumns)
{
jf.AppendMember(alias, column.Name);
jf.AppendAs(column.NewName);
index += 1;
if (index < result.SelectedColumns.Count)
{
jf.Append(',');
jf.AppendNewLine();
}
}
jf.AppendNewLine();
jf.Append("FROM(");
// 第二层嵌套
indent += 1;
isOutermost = false;
jf.Indent = indent;
(jf as OracleSqlBuilder).UseQuote = isOutermost;
jf.AppendNewLine();
jf.Append("SELECT");
jf.AppendNewLine();
foreach (var column in result.SelectedColumns)
{
jf.AppendMember(alias, column.NewName);
jf.AppendAs(column.NewName);
jf.Append(',');
jf.AppendNewLine();
}
jf.Append("ROWNUM");
jf.AppendAs("Row_Number0");
jf.AppendNewLine();
jf.Append("FROM(");
// 第三层嵌套
indent += 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append("SELECT");
}
jf.Append(sf);
}
#endregion
}
#endregion
#region 顺序解析
// FROM 子句
jf.AppendNewLine();
jf.Append("FROM ");
if (tree.Subquery != null)
{
// 子查询
jf.Append('(');
DbRawCommand cmd = this.TranslateSelectCommand(tree.Subquery, indent + 1, false, context);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else if (tree.FromSql != null)
{
if (tree.FromSql.DbContext == null)
{
tree.FromSql.DbContext = context.DbContext;
}
DbRawSql rawSql = tree.FromSql;
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
string sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
// 子查询
jf.Append('(');
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
jf.Append(cmd.CommandText);
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.Append(' ');
}
else
{
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(tree.From);
jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
jf.Append(' ');
jf.Append(alias);
jf.Append(' ');
}
// LEFT<INNER> JOIN 子句
if (tree.Joins != null)
{
var visitor = new JoinExpressionVisitor(ag, jf);
visitor.Visit(tree.Joins);
}
wf.Indent = jf.Indent;
// WHERE 子句
if (tree.Wheres != null)
{
var visitor = new WhereExpressionVisitor(ag, wf);
visitor.Visit(tree.Wheres);
result.AddNavMembers(visitor.NavMembers);
}
// GROUP BY 子句
if (tree.GroupBy != null)
{
var visitor = new GroupByExpressionVisitor(ag, wf);
visitor.Visit(tree.GroupBy);
result.AddNavMembers(visitor.NavMembers);
}
// HAVING 子句
if (tree.Havings != null)
{
var visitor = new HavingExpressionVisitor(ag, wf, tree.GroupBy);
visitor.Visit(tree.Havings);
result.AddNavMembers(visitor.NavMembers);
}
// ORDER 子句
if (tree.OrderBys != null && tree.OrderBys.Count > 0 && useOrderBy)
{
var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null);
visitor.Visit(tree.OrderBys);
result.AddNavMembers(visitor.NavMembers);
}
#endregion
#region 嵌套查询
if (useAggregate && useSubquery)
{
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 嵌套导航
// TODO Include 从表，没分页，OrderBy 报错
if (tree.SelectHasMany && subquery.Aggregate == null &&
subquery != null && subquery.OrderBys != null && subquery.OrderBys.Count > 0 && !(subquery.Skip > 0 || subquery.Take > 0))
{
// OrderBy("a.CloudServer.CloudServerName");
result.CombineFragments();
var visitor = new OrderByExpressionVisitor(ag, jf, null, null);
visitor.Visit(subquery.OrderBys);
}
#endregion
#region 并集查询
// UNION 子句
if (tree.Unions != null && tree.Unions.Count > 0)
{
result.CombineFragments();
for (int index = 0; index < tree.Unions.Count; index++)
{
jf.AppendNewLine();
jf.Append("UNION ALL");
if (indent == 0)
{
jf.AppendNewLine();
}
DbRawCommand cmd2 = this.TranslateSelectCommand(tree.Unions[index], indent, isOutermost, context);
jf.Append(cmd2.CommandText);
}
}
#endregion
#region 分页查询
if (tree.Take > 0 || tree.Skip > 0)
{
// 合并 WHERE
result.CombineFragments();
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
jf.AppendNewLine();
jf.Append("WHERE ");
if (tree.Skip > 0)
{
jf.AppendMember(alias, "Row_Number0");
jf.Append(" > ");
jf.Append(this.Constor.GetSqlValue(tree.Skip, context));
}
if (tree.Take > 0)
{
if (tree.Skip > 0)
{
jf.Append(" AND ");
}
jf.AppendMember(alias, "Row_Number0");
jf.Append(" <= ");
jf.Append(this.Constor.GetSqlValue((tree.Skip + tree.Take), context));
}
}
#endregion
#region Any子句
// 'Any' 子句
if (tree.HasAny)
{
// 产生 WHERE 子句
result.CombineFragments();
// 如果没有分页，则显式指定只查一笔记录
if (tree.Take == 0 && tree.Skip == 0)
{
if (tree.Wheres != null && tree.Wheres.Count > 0)
{
jf.Append(" AND ROWNUM <= 1");
}
else
{
jf.AppendNewLine();
jf.Append("WHERE ROWNUM <= 1");
}
}
indent -= 1;
jf.Indent = indent;
jf.AppendNewLine();
jf.Append(") ");
jf.Append(alias);
}
#endregion
#region 还原状态
context.CurrentExpressionType = srcDbExpressionType;
context.CurrentIsOutermost = srcIsOutmost;
#endregion
return(result);
}
/// <summary>
/// 创建 INSRT 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);;
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
if (tree.Entity != null)
{
// 如果没有Sequence列，使用 INSERT ALL INTO 语法，否则就一条一条逐行写入~~
// 批量 INSERT，自增列不会自动赋值
object entity = tree.Entity;
ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context);
ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context);
// 指定插入列
var members = typeRuntime.Members;
if (tree.EntityColumns != null && tree.EntityColumns.Count > 0)
{
members = new MemberAccessorCollection();
for (int i = 0; i < tree.EntityColumns.Count; i++)
{
Expression curExpr = tree.EntityColumns[i];
if (curExpr.NodeType == ExpressionType.Lambda)
{
curExpr = (curExpr as LambdaExpression).Body.ReduceUnary();
}
if (curExpr.NodeType != ExpressionType.MemberAccess)
{
throw new XFrameworkException("Can't read field name from expression {0}", tree.EntityColumns[i]);
}
MemberExpression member = curExpr as MemberExpression;
string name = member.Member.Name;
members[name] = typeRuntime.Members[name];
}
}
// 自增列参数
IDbDataParameter seqParameter = null;
// 自增列标记
ColumnAttribute seqColumn = null;
foreach (var item in members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type);
columnsBuilder.Append(',');
if (m == typeRuntime.Identity)
{
seqColumn = m.Column;
if (tree.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
string parameterName = string.Format("{0}{1}{2}", this.ParameterPrefix, AppConst.PARAMETER_NAME_PRIFIX, context.Parameters.Count);
seqParameter = this.DbProvider.CreateParameter(parameterName, -1, direction: ParameterDirection.Output);
context.Parameters.Add(seqParameter);
valuesBuilder.Append(seqParameter.ParameterName);
valuesBuilder.Append(',');
}
else
{
valuesBuilder.AppendMember(((OracleColumnAttribute)m.Column).SEQName);
valuesBuilder.Append(".NEXTVAL");
valuesBuilder.Append(',');
}
}
else
{
var value = m.Invoke(entity);
string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
valuesBuilder.Append(sqlExpression);
valuesBuilder.Append(',');
}
}
columnsBuilder.Length -= 1;
valuesBuilder.Length -= 1;
if (tree.Bulk == null)
{
// 非批量INSERT，产生一个 OUTPUT 类型的参数
if (seqParameter != null)
{
seqParameter.Direction = ParameterDirection.Output;
seqParameter.DbType = DbType.Int64;
builder.Append("SELECT ");
builder.AppendMember(((OracleColumnAttribute)seqColumn).SEQName);
builder.Append(".NEXTVAL INTO ");
builder.Append(seqParameter.ParameterName);
builder.Append(" FROM DUAL;");
builder.AppendNewLine();
//useSEQ = true;
}
builder.Append("INSERT ");
}
else
{
// 批量 INSERT
if (!tree.Bulk.OnlyValue || seqColumn != null)
{
builder.Append("INSERT ");
}
// 如果有自增列则不使用 INSERT ALL INTO 语法
if (!tree.Bulk.OnlyValue && seqColumn == null)
{
builder.Append("ALL ");
}
}
builder.Append("INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
builder.Append(columnsBuilder);
builder.Append(')');
builder.AppendNewLine();
builder.AppendTab();
builder.Append("VALUES");
builder.Append('(');
builder.Append(valuesBuilder);
builder.Append(')');
if (tree.Bulk == null)
{
builder.Append(';');
}
else
{
if (seqColumn != null)
{
if (tree.Bulk.IsEndPos)
{
builder.Append(";");
}
else
{
builder.AppendNewLine(";");
}
}
else
{
builder.AppendNewLine();
if (tree.Bulk.IsEndPos)
{
builder.Append("SELECT 1 FROM DUAL;");
}
}
}
}
else if (tree.Select != null)
{
builder.Append("INSERT INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append('(');
var srcExpressionType = context.CurrentExpressionType;
var srcIsOutermost = context.CurrentIsOutermost;
context.CurrentExpressionType = DbExpressionType.Insert;
context.CurrentIsOutermost = false;
var cmd = this.TranslateSelectCommand(tree.Select, 0, false, context) as DbSelectCommand;
context.CurrentExpressionType = srcExpressionType;
context.CurrentIsOutermost = srcIsOutermost;
int index = 0;
foreach (var column in cmd.SelectedColumns)
{
builder.AppendMember(column.Name);
if (index < cmd.SelectedColumns.Count - 1)
{
builder.Append(',');
}
index++;
}
builder.Append(')');
builder.AppendNewLine();
builder.Append(cmd.CommandText);
builder.Append(';');
}
var result = new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text);
return(result);
}
/// <summary> /// 创建 SQL 构造器 /// </summary> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> protected internal override ISqlBuilder CreateSqlBuilder(ITranslateContext context) => new OracleSqlBuilder(context);
/// <summary>
/// 解析 SQL 命令
/// <para>
/// 返回的已经解析语义中执行批次用 null 分开
/// </para>
/// </summary>
/// <param name="dbQueryables">查询语句</param>
/// <returns></returns>
public override List <DbRawCommand> Translate(List <object> dbQueryables)
{
bool haveBegin = false;
ITranslateContext context = null;
var sqlList = new List <DbRawCommand>();
for (int i = 0; i < dbQueryables.Count; i++)
{
object obj = dbQueryables[i];
if (obj == null)
{
continue;
}
if (obj is IDbQueryable)
{
DbQueryable dbQuery = (DbQueryable)obj;
dbQuery.Parameterized = true;
if (context == null)
{
context = this.CreateTranslateContext(dbQuery.DbContext);
}
if (context.Parameters == null)
{
context.Parameters = new List <IDbDataParameter>(8);
}
var cmd = dbQuery.Translate(0, true, context);
if (cmd is DbSelectCommand)
{
// 查询单独执行
if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null)
{
sqlList.Add(null);
}
sqlList.Add(cmd);
sqlList.Add(null);
context = this.CreateTranslateContext(dbQuery.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
else
{
// 增删改
if (!haveBegin)
{
sqlList.Add(new DbRawCommand("BEGIN"));
haveBegin = true;
}
sqlList.Add(cmd);
if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
if (haveBegin)
{
sqlList.Add(new DbRawCommand("END;"));
haveBegin = false;
sqlList.Add(null);
}
context = this.CreateTranslateContext(dbQuery.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
if (i + 1 < dbQueryables.Count)
{
// 检查下一条是否是选择语句
bool isQuery = false;
if (dbQueryables[i + 1] is IDbQueryable)
{
var tree = ((DbQueryable)dbQueryables[i + 1]).Parse();
isQuery = tree is DbQuerySelectTree;
}
else if ((dbQueryables[i + 1] is string))
{
string sql = dbQueryables[i + 1].ToString();
string method = string.Empty;
if (sql.Length > 6)
{
method = sql.Substring(0, 6).Trim().ToUpper();
}
isQuery = method == "SELECT";
}
else if (dbQueryables[i + 1] is DbRawSql)
{
string sql = ((DbRawSql)dbQueryables[i + 1]).CommandText;
string method = string.Empty;
if (sql.Length > 6)
{
method = sql.Substring(0, 6).Trim().ToUpper();
}
isQuery = method == "SELECT";
}
// 如果下一条是SELECT 语句，则需要结束当前语句块
if (isQuery)
{
if (haveBegin)
{
sqlList.Add(new DbRawCommand("END;"));
haveBegin = false;
sqlList.Add(null);
}
context = this.CreateTranslateContext(dbQuery.DbContext);
context.Parameters = new List <IDbDataParameter>(8);
}
}
}
}
else if (obj is DbRawSql || obj is string)
{
string sql = string.Empty;
if (obj is string)
{
sql = obj.ToString();
}
else
{
DbRawSql rawSql = (DbRawSql)obj;
// 解析参数
object[] args = null;
if (rawSql.Parameters != null)
{
args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray();
}
sql = rawSql.CommandText;
if (args != null && args.Length > 0)
{
sql = string.Format(sql, args);
}
}
string methodName = string.Empty;
if (sql.Length > 6)
{
methodName = sql.Substring(0, 6).Trim().ToUpper();
}
if (methodName == "SELECT")
{
if (sqlList.Count > 0 && (i - 1) >= 0 && sqlList[sqlList.Count - 1] != null)
{
sqlList.Add(null);
}
}
var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text);
sqlList.Add(cmd);
if (methodName == "SELECT")
{
sqlList.Add(cmd);
sqlList.Add(null);
context = this.CreateTranslateContext(obj is DbRawSql ? ((DbRawSql)obj).DbContext : null);
context.Parameters = new List <IDbDataParameter>(8);
}
else if (cmd.Parameters != null && cmd.Parameters.Count > 1000)
{
// 1000个参数，就要重新分批
if (haveBegin)
{
sqlList.Add(new DbRawCommand("END;"));
sqlList.Add(null);
haveBegin = false;
}
context = this.CreateTranslateContext(obj is DbRawSql ? ((DbRawSql)obj).DbContext : null);
context.Parameters = new List <IDbDataParameter>(8);
}
}
else
{
if (!haveBegin)
{
sqlList.Add(new DbRawCommand("BEGIN"));
haveBegin = true;
}
// 解析批量插入操作
List <IDbQueryable> bulkList = obj as List <IDbQueryable>;
if (bulkList != null && bulkList.Count > 0)
{
this.TranslateBulk(sqlList, bulkList);
}
}
if (haveBegin && i == dbQueryables.Count - 1)
{
sqlList.Add(new DbRawCommand("END;"));
}
}
return(sqlList);
}
/// <summary> /// 解析成 SQL 命令 /// </summary> /// <param name="indent">缩进</param> /// <param name="isOutQuery">是否外层查询，内层查询不需要结束符(;)</param> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> internal override DbRawCommand Translate(int indent, bool isOutQuery, ITranslateContext context) => ((DbQueryProvider)this.Provider).Translate(this, indent, isOutQuery, context);
/// <summary>
/// 创建 UPDATE 命令
/// </summary>
/// <param name="tree">查询语义</param>
/// <param name="context">解析SQL命令上下文</param>
/// <returns></returns>
protected override DbRawCommand TranslateUpdateCommand <T>(DbQueryUpdateTree tree, ITranslateContext context)
{
ISqlBuilder builder = this.CreateSqlBuilder(context);
var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>();
builder.Append("UPDATE ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.Append(" t0 SET");
builder.AppendNewLine();
if (tree.Entity != null)
{
object entity = tree.Entity;
ISqlBuilder whereBuilder = this.CreateSqlBuilder(context);
bool useKey = false;
int length = 0;
foreach (var item in typeRuntime.Members)
{
var m = item as FieldAccessorBase;
if (m == null || !m.IsDbField)
{
continue;
}
if (m.Column != null && m.Column.IsIdentity)
{
goto LABEL; // Fix issue# 自增列同时又是主键
}
builder.AppendMember(null, m.Member, typeRuntime.Type);
builder.Append(" = ");
LABEL:
if (m.Column == null || !m.Column.IsIdentity)
{
var value = m.Invoke(entity);
var sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column);
builder.Append(sqlExpression);
length = builder.Length;
builder.Append(',');
builder.AppendNewLine();
}
if (m.Column != null && m.Column.IsKey)
{
useKey = true;
}
}
if (!useKey)
{
throw new XFrameworkException("Update<T>(T value) require T must have key column.");
}
// ORACLE 需要注意参数顺序问题
int index = -1;
foreach (FieldAccessorBase m in typeRuntime.KeyMembers)
{
var column = m.Column;
var value = m.Invoke(entity);
var seg = this.Constor.GetSqlValueWidthDefault(value, context, column);
index += 1;
whereBuilder.AppendMember(null, m.Member, typeRuntime.Type);
whereBuilder.Append(" = ");
whereBuilder.Append(seg);
if (index < typeRuntime.KeyMembers.Count - 1)
{
whereBuilder.Append(" AND ");
}
}
builder.Length = length;
builder.AppendNewLine();
builder.Append("WHERE ");
builder.Append(whereBuilder);
}
else if (tree.Expression != null)
{
// SELECT 表达式
LambdaExpression lambda = tree.Expression as LambdaExpression;
var body = lambda.Body;
Expression expression = null;
if (body.NodeType == ExpressionType.MemberInit)
{
var initExpression = body as MemberInitExpression;
var bindings = new List <MemberBinding>(initExpression.Bindings);
foreach (var m in typeRuntime.KeyMembers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
var binding = Expression.Bind(m.Member, member);
if (!bindings.Any(x => x.Member == m.Member))
{
bindings.Add(binding);
}
}
expression = Expression.MemberInit(initExpression.NewExpression, bindings);
}
else if (body.NodeType == ExpressionType.New)
{
var newExpression = body as NewExpression;
var bindings = new List <MemberBinding>();
for (int index = 0; index < newExpression.Members.Count; index++)
{
var m = (FieldAccessorBase)typeRuntime.Members.FirstOrDefault(a => a is FieldAccessorBase && (
a.Name == newExpression.Members[index].Name ||
((FieldAccessorBase)a).Column != null && ((FieldAccessorBase)a).Column.Name == newExpression.Members[index].Name));
if (m == null)
{
throw new XFrameworkException("Member {0}.{1} not found.", typeRuntime.Type.Name, newExpression.Members[index].Name);
}
var binding = Expression.Bind(m.Member, newExpression.Arguments[index].Type != m.CLRType
? Expression.Convert(newExpression.Arguments[index], m.CLRType)
: newExpression.Arguments[index]);
bindings.Add(binding);
}
foreach (var m in typeRuntime.KeyMembers)
{
var member = Expression.MakeMemberAccess(lambda.Parameters[0], m.Member);
var binding = Expression.Bind(m.Member, member);
if (!bindings.Any(x => x.Member == m.Member))
{
bindings.Add(binding);
}
}
var newExpression2 = Expression.New(typeRuntime.Constructor.Member);
expression = Expression.MemberInit(newExpression2, bindings);
}
// 解析查询以确定是否需要嵌套
tree.Select.Select = new DbExpression(DbExpressionType.Select, expression);
var cmd = (DbSelectCommand)this.TranslateSelectCommand(tree.Select, 0, false, this.CreateTranslateContext(context.DbContext));
if ((cmd.NavMembers != null && cmd.NavMembers.Count > 0) || (tree.Select.Joins != null && tree.Select.Joins.Count > 0))
{
// 无法使用 DISTINCT, GROUP BY 等子句从视图中选择 ROWID 或采样。UPDATE 不能用rowid
// 有导航属性或者关联查询，使用 MERGE INTO 语法。要求必须有主键
if (typeRuntime.KeyMembers == null || typeRuntime.KeyMembers.Count == 0)
{
throw new XFrameworkException("Update<T>(Expression<Func<T, object>> updateExpression) require entity must have key column.");
}
builder.Length = 0;
builder.Append("MERGE INTO ");
builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary);
builder.AppendNewLine(" t0");
builder.Append("USING (");
cmd = (DbSelectCommand)this.TranslateSelectCommand(tree.Select, 1, false, context);
builder.AppendNewLine(cmd.CommandText);
builder.Append(") t1 ON (");
foreach (var m in typeRuntime.KeyMembers)
{
builder.AppendMember("t0", m.Member, typeRuntime.Type);
builder.Append(" = ");
builder.AppendMember("t1", m.Member, typeRuntime.Type);
builder.Append(" AND ");
}
builder.Length -= 5;
builder.Append(')');
// UPDATE
builder.AppendNewLine();
builder.AppendNewLine("WHEN MATCHED THEN UPDATE SET ");
// SET 字段
var visitor = new OracleUpdateExpressionVisitor(null, builder);
visitor.Visit(tree.Expression);
}
else
{
// 直接 SQL 的 UPDATE 语法
DbExpressionVisitor visitor = null;
AliasGenerator ag = this.PrepareTableAlias(tree.Select, context.AliasPrefix);
visitor = new UpdateExpressionVisitor(ag, builder);
visitor.Visit(tree.Expression);
if (tree.Select.Wheres != null)
{
visitor = new WhereExpressionVisitor(ag, builder);
visitor.Visit(tree.Select.Wheres);
}
}
}
builder.Append(';');
return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text));
}
