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