/// <summary> /// Query datas /// </summary> /// <typeparam name="T">Data type</typeparam> /// <param name="server">Database server</param> /// <param name="command">Command</param> /// <returns>Return datas</returns> public async Task <IEnumerable <T> > QueryAsync <T>(DatabaseServer server, ICommand command) { if (command.Query == null) { throw new EZNEWException($"{nameof(ICommand.Query)} is null"); } #region query translation IQueryTranslator translator = SQLiteManager.GetQueryTranslator(DataAccessContext.Create(server, command)); var tranResult = translator.Translate(command.Query); string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script string cmdText; switch (command.Query.ExecutionMode) { case QueryExecutionMode.Text: cmdText = tranResult.ConditionString; break; case QueryExecutionMode.QueryObject: default: int size = command.Query.QuerySize; string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); string orderString = string.IsNullOrWhiteSpace(tranResult.SortString) ? string.Empty : $"ORDER BY {tranResult.SortString}"; var queryFields = SQLiteManager.GetQueryFields(command.Query, command.EntityType, true); string outputFormatedField = string.Join(",", SQLiteManager.FormatQueryFields(translator.ObjectPetName, queryFields, true)); if (string.IsNullOrWhiteSpace(tranResult.CombineScript)) { cmdText = $"{tranResult.PreScript}SELECT {outputFormatedField} FROM {SQLiteManager.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {orderString} {(size > 0 ? $"LIMIT 0,{size}" : string.Empty)}"; }
/// <summary> /// Execute translation /// </summary> /// <param name="query">Query object</param> /// <param name="location">Query location</param> /// <param name="parameters">Parameters</param> /// <param name="objectName">Entity object name</param> /// <param name="useSort">Indicates whether use sort</param> /// <returns>Return a translation result</returns> public QueryTranslationResult ExecuteTranslation(IQuery query, QueryLocation location, CommandParameters parameters = null, string objectName = "", bool useSort = true) { if (query == null) { return(QueryTranslationResult.Empty); } StringBuilder conditionBuilder = new StringBuilder(); if (query.ExecutionMode == QueryExecutionMode.QueryObject) { StringBuilder orderBuilder = new StringBuilder(); parameters = parameters ?? new CommandParameters(); objectName = string.IsNullOrWhiteSpace(objectName) ? DefaultObjectPetName : objectName; List <string> withScripts = new List <string>(); string recurveTableName = string.Empty; string recurveTablePetName = string.Empty; #region condition if (!query.Conditions.IsNullOrEmpty()) { int index = 0; foreach (var condition in query.Conditions) { var conditionResult = TranslateCondition(query, condition, parameters, objectName); if (!conditionResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(conditionResult.WithScripts); recurveTableName = conditionResult.RecurveObjectName; recurveTablePetName = conditionResult.RecurvePetName; } conditionBuilder.Append($" {(index > 0 ? condition.Connector.ToString().ToUpper() : string.Empty)} {conditionResult.ConditionString}"); index++; } } #endregion #region sort if (useSort && !query.Sorts.IsNullOrEmpty()) { foreach (var sortEntry in query.Sorts) { orderBuilder.Append($"{ConvertSortFieldName(query, objectName, sortEntry)} {(sortEntry.Desc ? DescKeyWord : AscKeyWord)},"); } } #endregion #region combine StringBuilder combineBuilder = new StringBuilder(); if (!query.Combines.IsNullOrEmpty()) { foreach (var combineEntry in query.Combines) { if (combineEntry?.Query == null) { continue; } var combineObjectPetName = GetNewSubObjectPetName(); string combineObjectName = DataAccessContext.GetCombineEntityObjectName(combineEntry.Query); var combineQueryResult = ExecuteTranslation(combineEntry.Query, QueryLocation.Combine, parameters, combineObjectPetName, true); string combineConditionString = string.IsNullOrWhiteSpace(combineQueryResult.ConditionString) ? string.Empty : $"WHERE {combineQueryResult.ConditionString}"; combineBuilder.Append($" {GetCombineOperator(combineEntry.Type)} SELECT {string.Join(",", SQLiteManager.FormatQueryFields(combineObjectPetName, query, query.GetEntityType(), true, false))} FROM {SQLiteManager.WrapKeyword(combineObjectName)} AS {combineObjectPetName} {(combineQueryResult.AllowJoin ? combineQueryResult.JoinScript : string.Empty)} {combineConditionString}"); if (!combineQueryResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(combineQueryResult.WithScripts); recurveTableName = combineQueryResult.RecurveObjectName; recurveTablePetName = combineQueryResult.RecurvePetName; } } } #endregion #region join bool allowJoin = true; StringBuilder joinBuilder = new StringBuilder(); StringBuilder joinExtraCondition = new StringBuilder(); if (!query.Joins.IsNullOrEmpty()) { foreach (var joinEntry in query.Joins) { if (joinEntry == null || joinEntry.JoinObjectFilter == null) { continue; } if (joinEntry.JoinObjectFilter.GetEntityType() == null) { throw new EZNEWException("IQuery object must set entity type if use in join operation"); } string joinObjectPetName = GetNewSubObjectPetName(); var joinQueryResult = ExecuteTranslation(joinEntry.JoinObjectFilter, QueryLocation.Join, parameters, joinObjectPetName, true); if (string.IsNullOrWhiteSpace(joinQueryResult.CombineScript)) { var joinResult = GetJoinCondition(query, joinEntry, parameters, objectName, joinObjectPetName); if (!joinResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(joinResult.WithScripts); recurveTableName = joinResult.RecurveObjectName; recurveTablePetName = joinResult.RecurvePetName; } var joinConnection = joinResult.ConditionString; if (!string.IsNullOrWhiteSpace(joinQueryResult.ConditionString)) { if (joinQueryResult.AllowJoin && PositionJoinConditionToConnection(joinEntry.Type)) { joinConnection += $"{(string.IsNullOrWhiteSpace(joinConnection) ? " ON" : " AND ")}{joinQueryResult.ConditionString}"; } else { conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.ConditionString}"); } } if (!string.IsNullOrWhiteSpace(joinQueryResult.JoinExtraConditionString)) { conditionBuilder.Append($"{(conditionBuilder.Length == 0 ? string.Empty : " AND ")}{joinQueryResult.JoinExtraConditionString}"); } joinBuilder.Append($" {GetJoinOperator(joinEntry.Type)} {SQLiteManager.WrapKeyword(DataAccessContext.GetJoinEntityObjectName(joinEntry.JoinObjectFilter))} AS {joinObjectPetName}{joinConnection}"); if (joinEntry.JoinObjectExtraFilter != null) { var extraQueryResult = ExecuteTranslation(joinEntry.JoinObjectExtraFilter, QueryLocation.Join, parameters, joinObjectPetName, true); if (!string.IsNullOrWhiteSpace(extraQueryResult.ConditionString)) { joinExtraCondition.Append(joinExtraCondition.Length > 0 ? $" AND {extraQueryResult.ConditionString}" : extraQueryResult.ConditionString); } } if (joinQueryResult.AllowJoin && !string.IsNullOrWhiteSpace(joinQueryResult.JoinScript)) { joinBuilder.Append($" {joinQueryResult.JoinScript}"); } } else { var combineJoinObjName = GetNewSubObjectPetName(); var joinResult = GetJoinCondition(query, joinEntry, parameters, objectName, combineJoinObjName); if (!joinResult.WithScripts.IsNullOrEmpty()) { withScripts.AddRange(joinResult.WithScripts); recurveTableName = joinResult.RecurveObjectName; recurveTablePetName = joinResult.RecurvePetName; } var joinConnection = joinResult.ConditionString; joinBuilder.Append($" {GetJoinOperator(joinEntry.Type)} (SELECT {string.Join(",", SQLiteManager.FormatQueryFields(joinObjectPetName, joinEntry.JoinObjectFilter, joinEntry.JoinObjectFilter.GetEntityType(), false, false))} FROM {SQLiteManager.WrapKeyword(DataAccessContext.GetJoinEntityObjectName(joinEntry.JoinObjectFilter))} AS {joinObjectPetName} {(joinQueryResult.AllowJoin ? joinQueryResult.JoinScript : string.Empty)} {(string.IsNullOrWhiteSpace(joinQueryResult.ConditionString) ? string.Empty : "WHERE " + joinQueryResult.ConditionString)} {joinQueryResult.CombineScript}) AS {combineJoinObjName}{joinConnection}"); } 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(); string joinExtraConditionString = joinExtraCondition.ToString(); if (query.Recurve != null) { allowJoin = false; string nowConditionString = conditionString; if (!string.IsNullOrWhiteSpace(joinExtraConditionString)) { nowConditionString = string.IsNullOrWhiteSpace(nowConditionString) ? joinExtraConditionString : $"{nowConditionString} AND {joinExtraConditionString}"; joinExtraConditionString = string.Empty; } EntityField recurveField = DataManager.GetField(DatabaseServerType, query, query.Recurve.DataField); EntityField recurveRelationField = DataManager.GetField(DatabaseServerType, query, query.Recurve.RelationField); var recurveTable = GetNewRecurveTableName(); recurveTablePetName = recurveTable.Item1; recurveTableName = recurveTable.Item2; conditionString = $"{objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)} IN (SELECT {recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)} FROM {SQLiteManager.WrapKeyword(recurveTableName)} AS {recurveTablePetName})"; DataAccessContext.SetActivityQuery(query, location); string queryObjectName = DataManager.GetEntityObjectName(DataAccessContext); string withScript = $"{recurveTableName} AS (SELECT {objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)},{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)} FROM {SQLiteManager.WrapKeyword(queryObjectName)} AS {objectName} {joinScript} {(string.IsNullOrWhiteSpace(nowConditionString) ? string.Empty : $"WHERE {nowConditionString}")} " + $"UNION ALL SELECT {objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)},{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)} FROM {SQLiteManager.WrapKeyword(queryObjectName)} AS {objectName} JOIN {recurveTableName} AS {recurveTablePetName} " + $"ON {(query.Recurve.Direction == RecurveDirection.Up ? $"{objectName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)}={recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)}" : $"{objectName}.{SQLiteManager.WrapKeyword(recurveRelationField.FieldName)}={recurveTablePetName}.{SQLiteManager.WrapKeyword(recurveField.FieldName)}")})";