/// <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)); } }