/// <summary> /// Get database deletion command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database deletion command</returns> DatabaseExecutionCommand GetDatabaseDeletionCommand(IQueryTranslator translator, DefaultCommand command) { translator.DataAccessContext.SetCommand(command); #region query translation var tranResult = translator.Translate(command.Query); string conditionString = string.Empty; if (!string.IsNullOrWhiteSpace(tranResult.ConditionString)) { conditionString += "WHERE " + tranResult.ConditionString; } string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); string cmdText = string.Empty; string wrapObjectName = SQLiteManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};"; } else { var primaryKeyFields = DataManager.GetFields(CurrentDatabaseServerType, command.EntityType, EntityManager.GetPrimaryKeys(command.EntityType)).ToList(); if (primaryKeyFields.IsNullOrEmpty()) { throw new EZNEWException($"{command.EntityType?.FullName} not set primary key"); } string deleteTableShortName = "DTB"; cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} WHERE {string.Join("||", primaryKeyFields.Select(pk => deleteTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});"; } #endregion #region parameter CommandParameters parameters = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); var queryParameters = SQLiteManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = SQLiteManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }
/// <summary> /// Get database deletion command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database deletion command</returns> DatabaseExecutionCommand GetDatabaseDeletionCommand(IQueryTranslator translator, DefaultCommand command) { translator.DataAccessContext.SetCommand(command); #region query translation var tranResult = translator.Translate(command.Query); string conditionString = string.Empty; if (!string.IsNullOrWhiteSpace(tranResult.ConditionString)) { conditionString += "WHERE " + tranResult.ConditionString; } string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); string cmdText; string wrapObjectName = PostgreSqlManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {translator.ObjectPetName} {conditionString};"; } else { string deleteTableShortName = "DTB"; string deleteJoinTableShortName = "DJTB"; var primaryKeyFormatedResult = FormatWrapJoinPrimaryKeys(command.EntityType, translator.ObjectPetName, deleteTableShortName, deleteJoinTableShortName); cmdText = $"{preScript}DELETE FROM {wrapObjectName} AS {deleteTableShortName} USING (SELECT {string.Join(",", primaryKeyFormatedResult.Item1)} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString}) AS {deleteJoinTableShortName} WHERE {string.Join(" AND ", primaryKeyFormatedResult.Item2)};"; } #endregion #region parameter CommandParameters parameters = PostgreSqlManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); var queryParameters = PostgreSqlManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }
/// <summary> /// Get delete execute command /// </summary> /// <param name="translator">Translator</param> /// <param name="command">Command</param> /// <returns>Return delete execute command</returns> DatabaseExecuteCommand GetDeleteExecuteDbCommand(IQueryTranslator translator, RdbCommand command) { #region query translate var tranResult = translator.Translate(command.Query); string conditionString = string.Empty; if (!string.IsNullOrWhiteSpace(tranResult.ConditionString)) { conditionString += "WHERE " + tranResult.ConditionString; } string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script string objectName = DataManager.GetEntityObjectName(DatabaseServerType.MySQL, command.EntityType, command.ObjectName); string cmdText = $"{preScript}DELETE {translator.ObjectPetName} FROM `{objectName}` AS {translator.ObjectPetName} {joinScript} {conditionString};"; #endregion #region parameter CommandParameters parameters = ParseParameters(command.Parameters) ?? new CommandParameters(); var queryParameters = ParseParameters(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecuteCommand() { CommandText = cmdText, CommandType = GetCommandType(command), ForceReturnValue = command.MustReturnValueOnSuccess, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }
/// <summary> /// Execute command /// </summary> /// <param name="server">Database server</param> /// <param name="executionOptions">Execution options</param> /// <param name="commands">Commands</param> /// <returns>Return the affected data numbers</returns> public async Task <int> ExecuteAsync(DatabaseServer server, CommandExecutionOptions executionOptions, IEnumerable <ICommand> commands) { #region group execution commands IQueryTranslator translator = PostgreSqlManager.GetQueryTranslator(DataAccessContext.Create(server)); List <DatabaseExecutionCommand> databaseExecutionCommands = new List <DatabaseExecutionCommand>(); var batchExecutionConfig = DataManager.GetBatchExecutionConfiguration(server.ServerType) ?? BatchExecutionConfiguration.Default; var groupStatementsCount = batchExecutionConfig.GroupStatementsCount; groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount; var groupParameterCount = batchExecutionConfig.GroupParametersCount; groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount; StringBuilder commandTextBuilder = new StringBuilder(); CommandParameters parameters = null; int statementsCount = 0; bool forceReturnValue = false; int cmdCount = 0; DatabaseExecutionCommand GetGroupExecuteCommand() { var executionCommand = new DatabaseExecutionCommand() { CommandText = commandTextBuilder.ToString(), CommandType = CommandType.Text, MustAffectedData = forceReturnValue, Parameters = parameters }; statementsCount = 0; translator.ParameterSequence = 0; commandTextBuilder.Clear(); parameters = null; forceReturnValue = false; return(executionCommand); } foreach (var cmd in commands) { DatabaseExecutionCommand databaseExecutionCommand = GetDatabaseExecutionCommand(translator, cmd as DefaultCommand); if (databaseExecutionCommand == null) { continue; } //Trace log PostgreSqlManager.LogExecutionCommand(databaseExecutionCommand); cmdCount++; if (databaseExecutionCommand.PerformAlone) { if (statementsCount > 0) { databaseExecutionCommands.Add(GetGroupExecuteCommand()); } databaseExecutionCommands.Add(databaseExecutionCommand); continue; } commandTextBuilder.AppendLine(databaseExecutionCommand.CommandText); parameters = parameters == null ? databaseExecutionCommand.Parameters : parameters.Union(databaseExecutionCommand.Parameters); forceReturnValue |= databaseExecutionCommand.MustAffectedData; statementsCount++; if (translator.ParameterSequence >= groupParameterCount || statementsCount >= groupStatementsCount) { databaseExecutionCommands.Add(GetGroupExecuteCommand()); } } if (statementsCount > 0) { databaseExecutionCommands.Add(GetGroupExecuteCommand()); } #endregion return(await ExecuteDatabaseCommandAsync(server, executionOptions, databaseExecutionCommands, executionOptions?.ExecutionByTransaction ?? cmdCount > 1).ConfigureAwait(false)); }
/// <summary> /// Get database update command /// </summary> /// <param name="translator">Query translator</param> /// <param name="command">Command</param> /// <returns>Return a database update command</returns> DatabaseExecutionCommand GetDatabaseUpdateCommand(IQueryTranslator translator, DefaultCommand command) { if (command?.Fields.IsNullOrEmpty() ?? true) { throw new EZNEWException($"No fields are set to update"); } #region query translation translator.DataAccessContext.SetCommand(command); var tranResult = translator.Translate(command.Query); string conditionString = string.Empty; if (!string.IsNullOrWhiteSpace(tranResult.ConditionString)) { conditionString += "WHERE " + tranResult.ConditionString; } string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script CommandParameters parameters = PostgreSqlManager.ConvertParameter(command.Parameters) ?? new CommandParameters(); string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command); var fields = PostgreSqlManager.GetFields(command.EntityType, command.Fields); int parameterSequence = translator.ParameterSequence; List <string> updateSetArray = new List <string>(); foreach (var field in fields) { var parameterValue = parameters.GetParameterValue(field.PropertyName); var parameterName = field.PropertyName; string newValueExpression = string.Empty; if (parameterValue != null) { parameterSequence++; parameterName = PostgreSqlManager.FormatParameterName(parameterName, parameterSequence); parameters.Rename(field.PropertyName, parameterName); if (parameterValue is IModificationValue) { var modifyValue = parameterValue as IModificationValue; parameters.ModifyValue(parameterName, modifyValue.Value); if (parameterValue is CalculationModificationValue) { var calculateModifyValue = parameterValue as CalculationModificationValue; string calChar = PostgreSqlManager.GetSystemCalculationOperator(calculateModifyValue.Operator); newValueExpression = $"{translator.ObjectPetName}.{PostgreSqlManager.WrapKeyword(field.FieldName)}{calChar}{PostgreSqlManager.ParameterPrefix}{parameterName}"; } } } if (string.IsNullOrWhiteSpace(newValueExpression)) { newValueExpression = $"{PostgreSqlManager.ParameterPrefix}{parameterName}"; } updateSetArray.Add($"{PostgreSqlManager.WrapKeyword(field.FieldName)}={newValueExpression}"); } string cmdText; string wrapObjName = PostgreSqlManager.WrapKeyword(objectName); if (string.IsNullOrWhiteSpace(joinScript)) { cmdText = $"{preScript}UPDATE {wrapObjName} AS {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} {conditionString};"; } else { string updateTableShortName = translator.ObjectPetName; string updateJoinTableShortName = "UJTB"; var primaryKeyFormatedResult = FormatWrapJoinPrimaryKeys(command.EntityType, translator.ObjectPetName, updateTableShortName, updateJoinTableShortName); cmdText = $"{preScript}UPDATE {wrapObjName} AS {updateTableShortName} SET {string.Join(",", updateSetArray)} FROM (SELECT {string.Join(",", primaryKeyFormatedResult.Item1)} FROM {wrapObjName} AS {translator.ObjectPetName} {joinScript} {conditionString}) AS {updateJoinTableShortName} WHERE {string.Join(" AND ", primaryKeyFormatedResult.Item2)};"; } translator.ParameterSequence = parameterSequence; #endregion #region parameter var queryParameters = PostgreSqlManager.ConvertParameter(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecutionCommand() { CommandText = cmdText, CommandType = PostgreSqlManager.GetCommandType(command), MustAffectedData = command.MustAffectedData, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }
/// <summary> /// Execute command /// </summary> /// <param name="server">Database server</param> /// <param name="executeOption">Execute option</param> /// <param name="commands">Commands</param> /// <returns>Return effected data numbers</returns> public async Task <int> ExecuteAsync(DatabaseServer server, CommandExecuteOption executeOption, IEnumerable <ICommand> commands) { #region group execute commands IQueryTranslator translator = MySqlFactory.GetQueryTranslator(server); List <DatabaseExecuteCommand> executeCommands = new List <DatabaseExecuteCommand>(); var batchExecuteConfig = DataManager.GetBatchExecuteConfiguration(server.ServerType) ?? BatchExecuteConfiguration.Default; var groupStatementsCount = batchExecuteConfig.GroupStatementsCount; groupStatementsCount = groupStatementsCount < 0 ? 1 : groupStatementsCount; var groupParameterCount = batchExecuteConfig.GroupParametersCount; groupParameterCount = groupParameterCount < 0 ? 1 : groupParameterCount; StringBuilder commandTextBuilder = new StringBuilder(); CommandParameters parameters = null; int statementsCount = 0; bool forceReturnValue = false; int cmdCount = 0; DatabaseExecuteCommand GetGroupExecuteCommand() { var executeCommand = new DatabaseExecuteCommand() { CommandText = commandTextBuilder.ToString(), CommandType = CommandType.Text, ForceReturnValue = forceReturnValue, Parameters = parameters }; statementsCount = 0; translator.ParameterSequence = 0; commandTextBuilder.Clear(); parameters = null; forceReturnValue = false; return(executeCommand); } foreach (var cmd in commands) { DatabaseExecuteCommand executeCommand = GetExecuteDbCommand(translator, cmd as RdbCommand); if (executeCommand == null) { continue; } //Trace log MySqlFactory.LogExecuteCommand(executeCommand); cmdCount++; if (executeCommand.PerformAlone) { if (statementsCount > 0) { executeCommands.Add(GetGroupExecuteCommand()); } executeCommands.Add(executeCommand); continue; } commandTextBuilder.AppendLine(executeCommand.CommandText); parameters = parameters == null ? executeCommand.Parameters : parameters.Union(executeCommand.Parameters); forceReturnValue |= executeCommand.ForceReturnValue; statementsCount++; if (translator.ParameterSequence >= groupParameterCount || statementsCount >= groupStatementsCount) { executeCommands.Add(GetGroupExecuteCommand()); } } if (statementsCount > 0) { executeCommands.Add(GetGroupExecuteCommand()); } #endregion return(await ExecuteCommandAsync(server, executeOption, executeCommands, executeOption?.ExecuteByTransaction ?? cmdCount > 1).ConfigureAwait(false)); }
/// <summary> /// Get update execute command /// </summary> /// <param name="translator">Translator</param> /// <param name="command">Command</param> /// <returns>Return update execute command</returns> DatabaseExecuteCommand GetUpdateExecuteDbCommand(IQueryTranslator translator, RdbCommand command) { #region query translate var tranResult = translator.Translate(command.Query); string conditionString = string.Empty; if (!string.IsNullOrWhiteSpace(tranResult.ConditionString)) { conditionString += "WHERE " + tranResult.ConditionString; } string preScript = tranResult.PreScript; string joinScript = tranResult.AllowJoin ? tranResult.JoinScript : string.Empty; #endregion #region script CommandParameters parameters = ParseParameters(command.Parameters) ?? new CommandParameters(); string objectName = DataManager.GetEntityObjectName(DatabaseServerType.MySQL, command.EntityType, command.ObjectName); var fields = GetFields(command.EntityType, command.Fields); int parameterSequence = translator.ParameterSequence; List <string> updateSetArray = new List <string>(); foreach (var field in fields) { var parameterValue = parameters.GetParameterValue(field.PropertyName); var parameterName = field.PropertyName; string newValueExpression = string.Empty; if (parameterValue != null) { parameterSequence++; parameterName = FormatParameterName(parameterName, parameterSequence); parameters.Rename(field.PropertyName, parameterName); if (parameterValue is IModifyValue) { var modifyValue = parameterValue as IModifyValue; parameters.ModifyValue(parameterName, modifyValue.Value); if (parameterValue is CalculateModifyValue) { var calculateModifyValue = parameterValue as CalculateModifyValue; string calChar = GetCalculateChar(calculateModifyValue.Operator); newValueExpression = $"{translator.ObjectPetName}.`{field.FieldName}`{calChar}{ParameterPrefix}{parameterName}"; } } } if (string.IsNullOrWhiteSpace(newValueExpression)) { newValueExpression = $"{ParameterPrefix}{parameterName}"; } updateSetArray.Add($"{translator.ObjectPetName}.`{field.FieldName}`={newValueExpression}"); } string cmdText = $"{preScript}UPDATE `{objectName}` AS {translator.ObjectPetName} {joinScript} SET {string.Join(",", updateSetArray)} {conditionString};"; translator.ParameterSequence = parameterSequence; #endregion #region parameter var queryParameters = ParseParameters(tranResult.Parameters); parameters.Union(queryParameters); #endregion return(new DatabaseExecuteCommand() { CommandText = cmdText, CommandType = GetCommandType(command), ForceReturnValue = command.MustReturnValueOnSuccess, Parameters = parameters, HasPreScript = !string.IsNullOrWhiteSpace(preScript) }); }