/// <summary>
/// Translate Single Criteria
/// </summary>
/// <param name="criteria">criteria</param>
/// <param name="parameters">parameters</param>
/// <returns></returns>
TranslateResult TranslateCriteria(IQuery query, Criteria criteria, Dictionary <string, object> parameters, string objectName)
{
if (criteria == null)
{
return(TranslateResult.Empty);
}
IQuery valueQuery = criteria.Value as IQuery;
string parameterName = criteria.Name + ParameterSequence++;
string sqlOperator = GetOperator(criteria.Operator);
if (valueQuery != null)
{
string valueQueryObjectName = DataManager.GetQueryRelationObjectName(ServerType.SQLServer, valueQuery);
var valueQueryField = DataManager.GetField(ServerType.SQLServer, valueQuery, valueQuery.QueryFields[0]);
string subObjName = "TSB" + subObjectSequence;
subObjectSequence++;
var subQueryResult = ExecuteTranslate(valueQuery, parameters, subObjName, true);
string topString = "";
if (sqlOperator != InOperator && sqlOperator != NotInOperator)
{
topString = "TOP 1";
}
string conditionString = subQueryResult.ConditionString;
if (!string.IsNullOrWhiteSpace(conditionString))
{
conditionString = "WHERE " + conditionString;
}
var valueQueryCondition = string.Format("{0} {1} (SELECT {2} {3}.[{4}] FROM [{5}] AS {6}{7} {8} {9})"
, ConvertCriteriaName(valueQuery, objectName, criteria)
, sqlOperator
, topString
, subObjName
, valueQueryField.FieldName
, valueQueryObjectName
, subObjName
, subQueryResult.JoinScript
, conditionString
, subQueryResult.OrderString);
var valueQueryResult = TranslateResult.CreateNewResult(valueQueryCondition);
if (!subQueryResult.WithScripts.IsNullOrEmpty())
{
valueQueryResult.WithScripts = new List <string>(subQueryResult.WithScripts);
valueQueryResult.RecurveObjectName = subQueryResult.RecurveObjectName;
valueQueryResult.RecurvePetName = subQueryResult.RecurvePetName;
}
return(valueQueryResult);
}
parameters.Add(parameterName, FormatCriteriaValue(criteria.Operator, criteria.GetCriteriaRealValue()));
var criteriaCondition = string.Format("{0} {1} {2}{3}"
, ConvertCriteriaName(query, objectName, criteria)
, sqlOperator
, parameterPrefix
, parameterName);
return(TranslateResult.CreateNewResult(criteriaCondition));
}
/// <summary>
/// translate query condition
/// </summary>
/// <param name="queryItem">query condition</param>
/// <returns></returns>
TranslateResult TranslateCondition(IQuery query, Tuple <QueryOperator, IQueryItem> queryItem, Dictionary <string, object> parameters, string objectName)
{
if (queryItem == null)
{
return(TranslateResult.Empty);
}
Criteria criteria = queryItem.Item2 as Criteria;
if (criteria != null)
{
return(TranslateCriteria(query, criteria, parameters, objectName));
}
IQuery groupQuery = queryItem.Item2 as IQuery;
if (groupQuery != null && groupQuery.Criterias != null && groupQuery.Criterias.Count > 0)
{
groupQuery.SetEntityType(query.EntityType);
if (groupQuery.Criterias.Count == 1)
{
var firstCriterias = groupQuery.Criterias[0];
if (firstCriterias.Item2 is Criteria)
{
return(TranslateCriteria(groupQuery, firstCriterias.Item2 as Criteria, parameters, objectName));
}
return(TranslateCondition(groupQuery, firstCriterias, parameters, objectName));
}
StringBuilder subCondition = new StringBuilder("(");
List <string> groupWithScripts = new List <string>();
string recurveTableName = string.Empty;
string recurveTablePetName = string.Empty;
int index = 0;
foreach (var subQueryItem in groupQuery.Criterias)
{
var subGroupResult = TranslateCondition(groupQuery, subQueryItem, parameters, objectName);
if (!subGroupResult.WithScripts.IsNullOrEmpty())
{
recurveTableName = subGroupResult.RecurveObjectName;
recurveTablePetName = subGroupResult.RecurvePetName;
groupWithScripts.AddRange(subGroupResult.WithScripts);
}
subCondition.AppendFormat("{0} {1}"
, index > 0 ? " " + subQueryItem.Item1.ToString() : ""
, subGroupResult.ConditionString);
index++;
}
var groupResult = TranslateResult.CreateNewResult(subCondition.Append(")").ToString());
groupResult.RecurveObjectName = recurveTableName;
groupResult.RecurvePetName = recurveTablePetName;
groupResult.WithScripts = groupWithScripts;
return(groupResult);
}
return(TranslateResult.Empty);
}
/// <summary>
/// Execute Translate
/// </summary>
/// <param name="query">query object</param>
/// <param name="paras">parameters</param>
/// <param name="objectName">query object name</param>
/// <returns></returns>
public TranslateResult ExecuteTranslate(IQuery query, Dictionary <string, object> paras = null, string objectName = "", bool subQuery = false)
{
if (query == null)
{
return(TranslateResult.Empty);
}
StringBuilder conditionBuilder = new StringBuilder();
if (query.QueryType == QueryCommandType.QueryObject)
{
StringBuilder orderBuilder = new StringBuilder();
Dictionary <string, object> parameters = paras ?? new Dictionary <string, object>();
objectName = string.IsNullOrWhiteSpace(objectName) ? ObjPetName : objectName;
List <string> withScripts = new List <string>();
string recurveTableName = string.Empty;
string recurveTablePetName = string.Empty;
#region query condition
if (query.Criterias != null && query.Criterias.Count > 0)
{
int index = 0;
foreach (var queryItem in query.Criterias)
{
var queryItemCondition = TranslateCondition(query, queryItem, parameters, objectName);
if (!queryItemCondition.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(queryItemCondition.WithScripts);
recurveTableName = queryItemCondition.RecurveObjectName;
recurveTablePetName = queryItemCondition.RecurvePetName;
}
conditionBuilder.AppendFormat("{0} {1}"
, index > 0 ? " " + queryItem.Item1.ToString() : ""
, queryItemCondition.ConditionString);
index++;
}
}
#endregion
#region sort
if (!subQuery && query.Orders != null && query.Orders.Count > 0)
{
foreach (var orderItem in query.Orders)
{
orderBuilder.AppendFormat("{0} {1},"
, ConvertOrderCriteriaName(query, objectName, orderItem)
, orderItem.Desc ? "DESC" : "ASC");
}
}
#endregion
#region join
bool allowJoin = true;
StringBuilder joinBuilder = new StringBuilder();
if (!query.JoinItems.IsNullOrEmpty())
{
foreach (var joinItem in query.JoinItems)
{
if (joinItem == null || joinItem.JoinQuery == null)
{
continue;
}
if (joinItem.JoinQuery.EntityType == null)
{
throw new EZNEWException("IQuery object must set entity type if use in join operation");
}
string joinObjName = "TSB" + subObjectSequence;
subObjectSequence++;
var joinQueryResult = ExecuteTranslate(joinItem.JoinQuery, parameters, joinObjName, true);
if (!joinQueryResult.ConditionString.IsNullOrEmpty())
{
conditionBuilder.AppendFormat("{0}{1}"
, conditionBuilder.Length == 0 ? "" : " AND"
, joinQueryResult.ConditionString);
}
joinBuilder.AppendFormat(" {0} `{1}` AS {2}{3}"
, GetJoinOperator(joinItem.JoinType)
, DataManager.GetQueryRelationObjectName(ServerType.MySQL, joinItem.JoinQuery)
, joinObjName
, GetJoinCondition(query, joinItem, objectName, joinObjName));
if (!joinQueryResult.JoinScript.IsNullOrEmpty())
{
joinBuilder.AppendFormat(" {0}", joinQueryResult.JoinScript);
}
if (!joinQueryResult.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(joinQueryResult.WithScripts);
recurveTableName = joinQueryResult.RecurveObjectName;
recurveTablePetName = joinQueryResult.RecurvePetName;
}
}
}
string joinScript = joinBuilder.ToString();
#endregion
#region recurve script
string conditionString = conditionBuilder.ToString();
if (query.RecurveCriteria != null)
{
allowJoin = false;
string nowConditionString = conditionBuilder.ToString();
EntityField recurveField = DataManager.GetField(ServerType.MySQL, query, query.RecurveCriteria.Key);
EntityField recurveRelationField = DataManager.GetField(ServerType.MySQL, query, query.RecurveCriteria.RelationKey);
var recurveIndex = recurveObjectSequence++;
recurveTableName = string.Format("{0}{1}", TreeTableName, recurveIndex);
recurveTablePetName = string.Format("{0}{1}", TreeTablePetName, recurveIndex);
conditionString = string.Format("{0}.`{1}` IN (SELECT {3}.`{1}` FROM `{2}` AS {3})"
, objectName
, recurveField.FieldName
, recurveTableName
, recurveTablePetName);
string firstObjectPetName = objectName;
string secondObjectPetName = objectName;
string firstTreeTablePetName = recurveTablePetName;
string queryObjectName = DataManager.GetQueryRelationObjectName(ServerType.MySQL, query);
string withScript = string.Format("{0} AS (SELECT {1}.`{2}`,{1}.`{3}` FROM `{4}` AS {1}{8} {5} UNION ALL SELECT {6}.`{2}`,{6}.`{3}` FROM `{4}` AS {6} JOIN {0} AS {7}"
, recurveTableName
, firstObjectPetName
, recurveField.FieldName
, recurveRelationField.FieldName
, queryObjectName
, string.IsNullOrWhiteSpace(nowConditionString) ? "" : string.Format("WHERE {0}", nowConditionString)
, secondObjectPetName
, firstTreeTablePetName
, joinScript);
if (query.RecurveCriteria.Direction == RecurveDirection.Up)
{
withScript = string.Format("{0} ON {1}.`{2}`={3}.`{4}`)"
, withScript
, secondObjectPetName
, recurveField.FieldName
, firstTreeTablePetName
, recurveRelationField.FieldName);
}
else
{
withScript = string.Format("{0} ON {1}.`{2}`={3}.`{4}`)"
, withScript
, secondObjectPetName
, recurveRelationField.FieldName
, firstTreeTablePetName
, recurveField.FieldName);
}
withScripts.Add(withScript);
}
var result = TranslateResult.CreateNewResult(conditionString, orderBuilder.ToString().Trim(','), parameters);
result.JoinScript = joinScript;
result.AllowJoin = allowJoin;
result.WithScripts = withScripts;
result.RecurveObjectName = recurveTableName;
result.RecurvePetName = recurveTablePetName;
#endregion
return(result);
}
else
{
conditionBuilder.Append(query.QueryText);
return(TranslateResult.CreateNewResult(conditionBuilder.ToString(), string.Empty, query.QueryTextParameters));
}
}