/// <summary>
/// 解析Any函数
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
protected virtual void ReplaceAny(WhereCompilerInfo whereCompiler, Match match, string key)
{
var subWhere = TranslateManyWhere(whereCompiler, match);
whereCompiler.Builder.Append(subWhere.Builder);
whereCompiler.Builder.Append(">0");
}
/// <summary>
/// 转换1对多条件
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="property"></param>
/// <param name="subText"></param>
/// <param name="chainProperties"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
protected virtual WhereCompilerInfo GetSubWhereCompiler(WhereCompilerInfo whereCompiler,
OrmPropertyInfo property,
string subText, IList <OrmPropertyInfo> chainProperties,
string propertyName)
{
var subWhereCompiler = new WhereCompilerInfo(property.Map.GetMapObject(), subText,
new TableInfo {
Joins = new Dictionary <string, JoinInfo>()
},
new StringBuilder())
{
Query = whereCompiler.Query
};
Translate(subWhereCompiler);
var lastIndex = propertyName.LastIndexOf('.');
if (lastIndex > -1)
{
propertyName = propertyName.Substring(0, lastIndex);
}
if (subWhereCompiler.Builder.Length > 0)
{
subWhereCompiler.Builder.Append(" and ");
}
subWhereCompiler.Builder.Insert(0, " where ");
subWhereCompiler.Builder.Insert(0, string.Format("(select count(1) from {0} "
, subWhereCompiler.GetJoinTable()));
subWhereCompiler.Builder.AppendFormat("{0}={1})"
, whereCompiler.GetFieldName(property.Map.ObjectProperty, propertyName)
, subWhereCompiler.GetFieldName(property.Map.MapObjectProperty, null));
return(subWhereCompiler);
}
/// <summary>
/// 替换Contains关键字
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
/// <returns></returns>
protected virtual void ReplaceContains(WhereCompilerInfo whereCompiler, Match match, string key)
{
var m = Regex.Match(match.Value, PropertyPattern);
var chainProperties = new List <OrmPropertyInfo>();
var lastIndex = m.Value.LastIndexOf('.');
var propertyName = match.Value.Trim().Substring(0, lastIndex);
string firstName = GetStringMothedName(whereCompiler, propertyName, chainProperties);
m = Regex.Match(match.Value, BreakersPattern);
string lastName = GetStringMothedName(whereCompiler, m.Value.Trim().Trim('(').Trim(')'), chainProperties);
if (ContainsArray(whereCompiler, firstName, lastName))
{
return;
}
if (chainProperties.Count > 0 && chainProperties[0].IsCustom)
{
propertyName = firstName.Contains("@SysParameter0") ? firstName : lastName;
var paramterName = firstName.Contains("@SysParameter0") ? lastName : firstName;
whereCompiler.Builder.AppendFormat(propertyName.Replace("@SysParameter0", paramterName));
}
else
{
whereCompiler.Builder.AppendFormat("({0} like {1} +'%' or {0} like '%'+{1} +'%' or {0} like '%'+{1})",
firstName, lastName);
}
}
/// <summary>
/// 添加属性
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <returns></returns>
protected virtual bool AppendProperty(WhereCompilerInfo whereCompiler, Match match)
{
if (!Regex.IsMatch(match.Value, PropertyPattern))
{
return(false);
}
var key = match.Value.Trim();
if (key.Contains("@"))
{
whereCompiler.Builder.Append(match.Value);
return(true);
}
if (Regex.IsMatch(match.Value, NumberPattern))
{
whereCompiler.Builder.Append(match.Value);
return(true);
}
var chainProperties = whereCompiler.Object.GetChainProperties(key);
var fieldName = GetFeildNameAndAddJoins(whereCompiler, chainProperties, key);
fieldName = Regex.IsMatch(match.NextMatch().Value.Trim(), ConnectorPattern) &&
(whereCompiler.PreviousMatch == null || Regex.IsMatch(whereCompiler.PreviousMatch.Value.Trim(), ConnectorPattern))
? string.Format("{0}=1", fieldName)
: fieldName;
whereCompiler.Builder.Append(fieldName);
return(true);
}
/// <summary>
/// 得到Sql语句
/// </summary>
/// <param name="obj"></param>
/// <param name="selectComplier"></param>
/// <param name="tableSql"></param>
/// <param name="whereComplier"></param>
/// <param name="havingComplier"></param>
/// <param name="orderbyComplier"></param>
/// <param name="groupbyComplier"></param>
/// <param name="query"></param>
/// <returns></returns>
protected virtual string GetSql(OrmObjectInfo obj, QueryCompilerInfo selectComplier, string tableSql,
WhereCompilerInfo whereComplier, QueryCompilerInfo groupbyComplier,
QueryCompilerInfo orderbyComplier, WhereCompilerInfo havingComplier,
QueryInfo query)
{
var sql = new StringBuilder();
var orderbyExp = query.PageSize != 0
? GetDefaultOrderby(obj, query, orderbyComplier)
: orderbyComplier.Builder.ToString();
if (query.PageSize == 0)
{
BuilderSql(sql, selectComplier.Builder.ToString(), tableSql, whereComplier.Builder.ToString(),
groupbyComplier.Builder.ToString(), havingComplier.Builder.ToString(), orderbyExp, query.IsDisinct);
}
else
{
BuilderPageSql(sql, selectComplier.Builder.ToString(), tableSql, whereComplier.Builder.ToString(),
groupbyComplier.Builder.ToString(), havingComplier.Builder.ToString(), orderbyExp, query);
}
if (query.IsReturnCount && query.PageSize > 0)
{
BuilderCountSql(sql, obj, selectComplier, tableSql, whereComplier.Builder.ToString(),
groupbyComplier.Builder.ToString(), havingComplier.Builder.ToString(), query.IsDisinct);
}
return(sql.ToString());
}
/// <summary>
/// 替换操作符
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
protected virtual void AppendOperator(WhereCompilerInfo whereCompiler, Match match)
{
string operatorName = match.Value.Trim().Replace(" ", "");
switch (operatorName)
{
case "&&": whereCompiler.Builder.Append(" and "); return;
case "||": whereCompiler.Builder.Append(" or "); return;
case "!": whereCompiler.Builder.Append("not "); return;
}
if (operatorName.Equals("==") || operatorName.Equals("!="))
{
if (match.NextMatch().Value.Replace(" ", "").Equals("null"))
{
whereCompiler.Builder.Append(operatorName.Equals("==") ? " is " : " is not ");
return;
}
}
if (operatorName.Equals("=="))
{
operatorName = "=";
}
whereCompiler.Builder.Append(operatorName);
}
/// <summary>
/// 解析条件
/// </summary>
/// <param name="whereCompiler"></param>
/// <returns></returns>
public virtual void Translate(WhereCompilerInfo whereCompiler)
{
whereCompiler.Builder = new StringBuilder();
if (!string.IsNullOrEmpty(whereCompiler.Exp))
{
AppendSql(whereCompiler);
}
var builder = new StringBuilder();
if (whereCompiler.IsSaveWhere)
{
var tableName = whereCompiler.SaveCompiler.SaveInfo.SetTableName;
builder.Append("exists(select * from ");
builder.Append(whereCompiler.GetJoinTable(whereCompiler.SaveCompiler.SaveInfo));
builder.AppendFormat(" where {0}.{1}={2}"
, tableName, whereCompiler.Object.PrimaryProperty.FieldName
, whereCompiler.GetFieldName(whereCompiler.Object.PrimaryProperty, null));
if (whereCompiler.Builder.Length > 0)
{
builder.Append(" and ");
builder.Append(whereCompiler.Builder);
}
builder.Append(")");
whereCompiler.Builder = builder;
}
}
/// <summary>
/// 转换一对多
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <returns></returns>
protected virtual WhereCompilerInfo TranslateManyWhere(WhereCompilerInfo whereCompiler, Match match)
{
var lastIndex = Regex.Match(match.Value, PropertyPattern).Value.LastIndexOf('.');
var propertyName = match.Value.Trim().Substring(0, lastIndex);
var chainProperties = whereCompiler.Object.GetChainProperties(propertyName);
return(TranslateManyWhere(whereCompiler, match, propertyName, chainProperties));
}
/// <summary>
/// 翻译查询一对关系
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="subText"></param>
/// <param name="chainProperties"></param>
/// <param name="propertyName"></param>
/// <returns></returns>
protected virtual WhereCompilerInfo TranslateQueryManyWhere(WhereCompilerInfo whereCompiler,
string subText,
IList <OrmPropertyInfo> chainProperties,
string propertyName)
{
var property = chainProperties[chainProperties.Count - 1];
return(GetSubWhereCompiler(whereCompiler, property, subText, chainProperties, propertyName));
}
/// <summary>
/// 设置查询的条件
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="chainProperties"></param>
/// <param name="propertyName"></param>
protected virtual string GetFeildNameAndAddJoins(WhereCompilerInfo whereCompiler, IList <OrmPropertyInfo> chainProperties, string propertyName)
{
var property = chainProperties[chainProperties.Count - 1];
if (whereCompiler.Table.Joins != null)
{
whereCompiler.AddJoins(chainProperties);
}
var fieldName = whereCompiler.GetFieldName(property, propertyName);
return(fieldName);
}
/// <summary>
/// 得到where语句
/// </summary>
/// <param name="whereCompiler"></param>
/// <returns></returns>
protected virtual void AppendSql(WhereCompilerInfo whereCompiler)
{
FillterQuotation(whereCompiler);
var match = Regex.Match(whereCompiler.Exp, Pattern);
while (match.Length > 0)
{
AppendWhereSql(whereCompiler, match);
whereCompiler.PreviousMatch = match;
match = match.NextMatch();
}
}
/// <summary>
/// 替换字符函数
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="whereString"></param>
protected virtual void ReplaceStringMothed(WhereCompilerInfo whereCompiler, Match match, string whereString)
{
var m = Regex.Match(match.Value, PropertyPattern);
var chainProperties = new List <OrmPropertyInfo>();
var lastIndex = m.Value.LastIndexOf('.');
var propertyName = match.Value.Trim().Substring(0, lastIndex);
string firstName = GetStringMothedName(whereCompiler, propertyName, chainProperties);
m = Regex.Match(match.Value, BreakersPattern);
string lastName = GetStringMothedName(whereCompiler, m.Value.Trim().Trim('(').Trim(')'), chainProperties);
whereCompiler.Builder.AppendFormat(whereString, firstName, lastName);
}
/// <summary>
/// 得到比较操作名称
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="name"></param>
/// <param name="chainProperties"></param>
/// <returns></returns>
protected virtual string GetStringMothedName(WhereCompilerInfo whereCompiler, string name,
List <OrmPropertyInfo> chainProperties)
{
if (string.IsNullOrEmpty(name) || name.Equals("null"))
{
return(name);
}
if (name.Contains("@"))
{
return(name);
}
chainProperties.AddRange(whereCompiler.Object.GetChainProperties(name));
return(GetFeildNameAndAddJoins(whereCompiler, chainProperties, name));
}
/// <summary>
/// 转换一对多
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="propertyName"></param>
/// <param name="chainProperties"></param>
/// <returns></returns>
protected virtual WhereCompilerInfo TranslateManyWhere(WhereCompilerInfo whereCompiler, Match match,
string propertyName, IList <OrmPropertyInfo> chainProperties)
{
var m = Regex.Match(match.Value, BreakersPattern);
var subText = m.Value.Trim().Substring(1, m.Value.Length - 2);
if (whereCompiler.Table.Joins != null)
{
whereCompiler.AddJoins(chainProperties);
}
var subWhereCompiler = TranslateQueryManyWhere(whereCompiler, subText, chainProperties, propertyName);
return(subWhereCompiler);
}
/// <summary>
/// 解析Having
/// </summary>
/// <param name="havingCompiler"></param>
/// <param name="exp"></param>
/// <returns></returns>
public virtual void Translate(WhereCompilerInfo havingCompiler, string exp)
{
if (string.IsNullOrEmpty(exp))
{
return;
}
var match = Regex.Match(exp, Pattern);
while (match.Length > 0)
{
AppendHavingSql(havingCompiler, match);
havingCompiler.PreviousMatch = match;
match = match.NextMatch();
}
}
/// <summary>
/// 添加属性
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <returns></returns>
protected virtual bool AppendProperty(WhereCompilerInfo whereCompiler, Match match)
{
if (!Regex.IsMatch(match.Value.Trim(), PropertyPattern))
{
return(false);
}
var key = match.Value.Trim();
if (key.Contains("@"))
{
whereCompiler.Builder.Append(match.Value);
return(true);
}
if (Regex.IsMatch(match.Value, NumberPattern))
{
whereCompiler.Builder.Append(match.Value);
return(true);
}
if (key.Equals("null"))
{
whereCompiler.Builder.Append(match.Value);
return(true);
}
var chainProperties = whereCompiler.Object.GetChainProperties(key);
var fieldName = GetFeildNameAndAddJoins(whereCompiler, chainProperties, key);
if (
(Regex.IsMatch(match.NextMatch().Value.Trim(), ConnectorPattern) ||
match.NextMatch().Value.Trim().Length == 0) &&
(whereCompiler.PreviousMatch == null ||
Regex.IsMatch(whereCompiler.PreviousMatch.Value.Trim(), ConnectorPattern) ||
whereCompiler.PreviousMatch.Value.Trim().Equals("!"))
)
{
if (whereCompiler.PreviousMatch != null && whereCompiler.PreviousMatch.Value.Trim().Equals("!"))
{
whereCompiler.Builder.Remove(whereCompiler.Builder.Length - 4, 4);
fieldName = string.Format("{0}=0", fieldName);
}
else
{
fieldName = string.Format("{0}=1", fieldName);
}
}
whereCompiler.Builder.Append(fieldName);
return(true);
}
/// <summary>
/// 解析All函数
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
protected virtual void ReplaceAll(WhereCompilerInfo whereCompiler, Match match, string key)
{
var lastIndex = Regex.Match(match.Value, PropertyPattern).Value.LastIndexOf('.');
var propertyName = match.Value.Trim().Substring(0, lastIndex);
var chainProperties = whereCompiler.Object.GetChainProperties(propertyName);
var property = chainProperties[chainProperties.Count - 1];
var subWhere = TranslateManyWhere(whereCompiler, match, propertyName, chainProperties);
string mapTableName = whereCompiler.Table.Joins != null?whereCompiler.Query.GetGetTableName(property.Map.GetMapObject())
: whereCompiler.SaveCompiler.SaveInfo.GetSetTableName(property.Map.GetMapObject());
var allWhere = string.Format("=(select count(1) from {0} _AllCountTable where _AllCountTable.{1}={2})"
, mapTableName, property.Map.MapObjectProperty.FieldName
, whereCompiler.GetFieldName(property.Map.ObjectProperty, propertyName));
whereCompiler.Builder.Append(subWhere.Builder);
whereCompiler.Builder.Append(allWhere);
}
/// <summary>
/// 拼接SQL
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
protected virtual void AppendWhereSql(WhereCompilerInfo whereCompiler, Match match)
{
var keyMatch = Regex.Match(match.Value, KeyPattern);
var key = keyMatch.Value.Replace(" ", "");
var handler = GetMatchHandler(key);
if (handler != null)
{
handler(whereCompiler, match, key);
return;
}
var rev = AppendProperty(whereCompiler, match);
if (!rev)
{
AppendOperator(whereCompiler, match);
}
}
/// <summary>
/// 翻译查询
/// </summary>
/// <param name="obj"></param>
/// <param name="query"></param>
/// <param name="queryCompiler"></param>
public virtual void TranslateQuery(OrmObjectInfo obj, QueryInfo query, QueryCompilerInfo queryCompiler)
{
var table = new TableInfo {
Joins = new Dictionary <string, JoinInfo>()
};
var selectComplier = new QueryCompilerInfo(obj, query.SelectExp, table, null)
{
Query = query, TranslateQuery = TranslateQuery
};
var groupbyComplier = new QueryCompilerInfo(obj, query.GroupByExp, table, null)
{
Query = query
};
var orderbyComplier = new QueryCompilerInfo(obj, query.OrderByExp, table, null)
{
Query = query
};
var whereComplier = new WhereCompilerInfo(obj, query.WhereExp, table, null)
{
Query = query
};
var havingComplier = new WhereCompilerInfo(obj, query.HavingExp, table, null)
{
Query = query
};
SelectCompiler.Translate(selectComplier);
WhereCompiler.Translate(whereComplier);
GroupbyCompiler.Translate(groupbyComplier);
HavingCompiler.Translate(havingComplier);
OrderbyCompiler.Translate(orderbyComplier);
if (queryCompiler != null && !string.IsNullOrEmpty(queryCompiler.Chainon))
{
if (whereComplier.Builder.Length > 0)
{
whereComplier.Builder.Append(" and ");
}
whereComplier.Builder.Append(string.Format(queryCompiler.Chainon, table.AsName));
}
var tableSql = selectComplier.GetJoinTable(query);
query.Sql = GetSql(obj, selectComplier, tableSql, whereComplier, groupbyComplier, orderbyComplier,
havingComplier, query);
}
/// <summary>
/// 替换引号
/// </summary>
/// <param name="whereCompiler"></param>
/// <returns></returns>
protected virtual void FillterQuotation(WhereCompilerInfo whereCompiler)
{
var builder = new StringBuilder();
var match = Regex.Match(whereCompiler.Exp, QuotationPattern);
whereCompiler.Query.SqlParameters = whereCompiler.Query.SqlParameters ?? (whereCompiler.Query.Parameters == null?new Dictionary <string, object>():new Dictionary <string, object>(whereCompiler.Query.Parameters));
while (match.Length > 0)
{
var name = string.Format("P{0}", whereCompiler.Query.SqlParameters.Count + 1);
whereCompiler.Query.SqlParameters.Add(name, match.Value);
builder.Append(whereCompiler.Exp.Substring(0, match.Index));
builder.AppendFormat("@{0}", name);
builder.Append(whereCompiler.Exp.Substring(match.Index + match.Length + 1,
whereCompiler.Exp.Length - match.Index - match.Length - 1));
match = match.NextMatch();
}
if (builder.Length > 0)
{
whereCompiler.Exp = builder.ToString();
}
}
/// <summary>
/// 添加函数
/// </summary>
/// <param name="havingCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
protected virtual void AppendHavingMethod(WhereCompilerInfo havingCompiler, Match match, string key)
{
var m = Regex.Match(match.Value, BreakersPattern);
if (!m.Success)
{
return;
}
var propertyName = m.Value.Trim('(').Trim(')');
var chainProperties = havingCompiler.Object.GetChainProperties(propertyName);
havingCompiler.AddJoins(chainProperties);
havingCompiler.Builder.Append(key.Equals("Average") ? "Avg" : key);
if (key.Equals("Count"))
{
havingCompiler.Builder.Append("1)");
}
else
{
Translate(havingCompiler, string.Format("{0})", propertyName));
}
}
/// <summary>
/// 数组Contains方法
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="firstName"></param>
/// <param name="lastName"></param>
/// <returns></returns>
protected virtual bool ContainsArray(WhereCompilerInfo whereCompiler, string firstName, string lastName)
{
if (!firstName.Contains("@"))
{
return(false);
}
var pName = firstName.Replace("@", "");
if (whereCompiler.Query.SqlParameters.ContainsKey(pName) && whereCompiler.Query.SqlParameters[pName] == null)
{
whereCompiler.Builder.AppendFormat("{0} is null", lastName);
return(true);
}
if (whereCompiler.Query.SqlParameters.ContainsKey(pName) && whereCompiler.Query.SqlParameters[pName] is Array)
{
var array = whereCompiler.Query.SqlParameters[pName] as Array;
if (array.Length == 0)
{
whereCompiler.Builder.AppendFormat("{0} is null", lastName);
return(true);
}
whereCompiler.Builder.AppendFormat("{0} in (", lastName);
for (int i = 0; i < array.Length; i++)
{
var name = string.Format("{0}_{1}", pName, i);
whereCompiler.Query.SqlParameters.Add(name, array.GetValue(i));
whereCompiler.Builder.AppendFormat("@{0}", name);
if (i != array.Length - 1)
{
whereCompiler.Builder.Append(",");
}
}
whereCompiler.Query.SqlParameters.Remove(pName);
whereCompiler.Builder.Append(")");
return(true);
}
return(false);
}
/// <summary>
/// 拼接自定义条件
/// </summary>
/// <param name="saveCompile"></param>
/// <param name="where"></param>
protected virtual void AppendCustomerWhere(SaveCompilerInfo saveCompile, StringBuilder where)
{
var whereCompile = new WhereCompilerInfo(saveCompile.SaveInfo.Object,
saveCompile.SaveInfo.Information.WhereExp, new TableInfo {
Joins = new Dictionary <string, JoinInfo>()
},
new StringBuilder(), true, saveCompile)
{
Query = new QueryInfo {
Object = saveCompile.SaveInfo.Object, Parameters = saveCompile.SaveInfo.Information.Parameters
}
};
WhereCompiler.Translate(whereCompile);
if (whereCompile.Query.SqlParameters != null)
{
foreach (var sqlParameter in whereCompile.Query.SqlParameters)
{
AddParamter(saveCompile.Command, sqlParameter.Key, sqlParameter.Value);
}
}
where.Append(whereCompile.Builder);
saveCompile.IsSaveParameters = true;
}
/// <summary>
/// 匹配结束
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
protected virtual void ReplaceEndsWith(WhereCompilerInfo whereCompiler, Match match, string key)
{
ReplaceStringMothed(whereCompiler, match, "{0} like '%'+ {1}");
}
/// <summary>
/// 解析Having
/// </summary>
/// <param name="havingCompiler"></param>
/// <returns></returns>
public virtual void Translate(WhereCompilerInfo havingCompiler)
{
havingCompiler.Builder = new StringBuilder();
Translate(havingCompiler, havingCompiler.Exp);
}
/// <summary>
/// 替换IsNullOrEmpty函数
/// </summary>
/// <param name="whereCompiler"></param>
/// <param name="match"></param>
/// <param name="key"></param>
protected virtual void ReplaceIsNullOrEmpty(WhereCompilerInfo whereCompiler, Match match, string key)
{
ReplaceStringMothed(whereCompiler, match, "({0} is null or {0}='')");
}