public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct); return(this);
}
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
if (string.IsNullOrWhiteSpace(strSelectSql))
{
strSelectSql = "*";
}
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Sql.AppendFormat("SELECT {0}{1} FROM {2} {3} {4} LIMIT {5},{6}", strDistinctSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, pageSize * (pageIndex - 1), pageSize);
return(this);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingSchemaTypes);
var visitor = new OrderByVisitor();
fragment.Accept(visitor);
var offenders = (from o in visitor.NotIgnoredStatements(RuleId)
from e in o.OrderByElements
where e.Expression is IntegerLiteral
select e).Distinct();
foreach (var offender in offenders)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, offender));
}
return(problems);
}
public virtual ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strTopSql = top > 0 ? string.Format("TOP {0} ", top) : string.Empty;
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
if (!isRand)
{
Sql.AppendFormat("SELECT {0}{1}{2} FROM {3} {4} {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
}
else if (string.IsNullOrWhiteSpace(strOrderBySql))
{
Sql.AppendFormat("SELECT {0}{1}{2}{5} FROM {3} {4} ORDER BY NEWID()", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",NEWID() as newid" : "");
}
else
{
Sql.AppendFormat("SELECT {2} FROM (SELECT {0} {1} *{6} FROM {3} {4} ORDER BY NEWID()) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",NEWID() as newid" : "");
}
return(this);
}
public virtual ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct); return(this);
}
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.Map.FieldMap.PrimaryState.Key == null, "不指定排序字段时，需要设置主键ID");
strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name) : strOrderBySql);
var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC");
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
Sql.AppendFormat("SELECT {0}TOP {2} {1} FROM (SELECT TOP {3} * FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse);
return(this);
}
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct);
return(this);
}
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct" : string.Empty;
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && (ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0), "不指定排序字段时，需要设置主键ID");
strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql);
Sql.Append(string.Format("SELECT {1} FROM (SELECT {0} {1},ROW_NUMBER() OVER({2}) as Row FROM {3} {4}) a WHERE Row BETWEEN {5} AND {6};", strDistinctSql, strSelectSql, strOrderBySql, Name, strWhereSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize));
return(this);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var topVisitor = new TopRowFilterVisitor();
fragment.Accept(topVisitor);
if (topVisitor.Count > 0)
{
var orderByvisitor = new OrderByVisitor();
fragment.Accept(orderByvisitor);
if (orderByvisitor.Count < 1)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, topVisitor.Statements[0]));
}
}
return(problems);
}
public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
var randField = ",dbms_random.value as newid";
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
if (!isRand)
{
Sql.Append(BuilderTop(top, $"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}"));
}
else if (!isDistinct && string.IsNullOrWhiteSpace(strOrderBySql))
{
Sql.Append(BuilderTop(top, $"SELECT {strSelectSql}{randField} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} ORDER BY dbms_random.value"));
}
else
{
Sql.Append(BuilderTop(top, $"SELECT * {randField} FROM (SELECT {strDistinctSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}) s ORDER BY dbms_random.value"));
}
return(this);
}
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct);
return(this);
}
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Sql.Append($"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql} LIMIT {pageSize} OFFSET {pageSize * (pageIndex - 1)}");
return(this);
}
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false)
{
// 不分页
if (pageIndex == 1)
{
ToList(pageSize, isDistinct);
return(this);
}
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strDistinctSql = isDistinct ? "Distinct" : string.Empty;
Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0, "不指定排序字段时，需要设置主键ID");
strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql);
var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC");
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
Sql.Append(string.Format("SELECT {0} TOP {2} {1} FROM (SELECT TOP {3} {1} FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse));
return(this);
}
public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false)
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
var strTopSql = top > 0 ? $"TOP {top}" : string.Empty;
var strDistinctSql = isDistinct ? "Distinct " : string.Empty;
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
if (!isRand)
{
Sql.Append($"SELECT {strDistinctSql}{strTopSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}");
}
else if (string.IsNullOrWhiteSpace(strOrderBySql))
{
Sql.Append(string.Format("SELECT {0}{1} {2}{5} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : ""));
}
else
{
Sql.Append(string.Format("SELECT {2} FROM (SELECT {0}{1} *{6} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : ""));
}
return(this);
}
/// <summary>
/// 添加OrderBy 别名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="value"></param>
/// <param name="alias"></param>
public void OrderBy <T>(Expression <Func <IEnumerable <T>, IOrderedEnumerable <T> > > value, string alias)
{
if (value != null)
{
OrderByVisitor <T> orderByVisitor = new OrderByVisitor <T>();
OrderText = orderByVisitor.Translate(value, alias);
}
}
/// <summary>
/// 获取排序参数
/// </summary>
/// <param name="source">数据源</param>
/// <returns></returns>
public static (string sortColumn, string sortType) GetOrderBy(this IQueryable source)
{
var visitor = new OrderByVisitor();
visitor.Visit(source.Expression);
return(visitor.OrderParam);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new OrderByVisitor();
fragment.Accept(visitor);
foreach (var orderByClause in visitor.InvalidOrderByClauseWithNumber)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, orderByClause);
problem.Severity = SqlRuleProblemSeverity.Error;
problems.Add(problem);
}
return(problems);
}
public virtual ISqlParam ToEntity()
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Sql.AppendFormat("SELECT TOP 1 {0} FROM {1} {2} {3}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
return(this);
}
public override ISqlParam GetValue()
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Sql.AppendFormat("SELECT {0} FROM {1} {2} {3} rownum <=1", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql);
return(this);
}
public override ISqlParam GetValue()
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
Sql.Append($"SELECT TOP 1 {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}");
return(this);
}
public override ISqlParam ToEntity()
{
var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect);
var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere);
var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy);
if (!string.IsNullOrWhiteSpace(strWhereSql))
{
strWhereSql = "WHERE " + strWhereSql;
}
if (!string.IsNullOrWhiteSpace(strOrderBySql))
{
strOrderBySql = "ORDER BY " + strOrderBySql;
}
var strTopSql = (string.IsNullOrWhiteSpace(strWhereSql) ? "WHERE" : "AND") + " rownum <=1";
Sql.Append($"SELECT {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strTopSql} {strOrderBySql}");
return(this);
}
