/// <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 execution command
/// </summary>
/// <param name="queryTranslator">Query translator</param>
/// <param name="command">Command</param>
/// <returns>Return a database execution command</returns>
DatabaseExecutionCommand GetDatabaseExecutionCommand(IQueryTranslator queryTranslator, DefaultCommand command)
{
DatabaseExecutionCommand GetTextCommand()
{
return(new DatabaseExecutionCommand()
{
CommandText = command.Text,
Parameters = SQLiteManager.ConvertParameter(command.Parameters),
CommandType = SQLiteManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
HasPreScript = true
});
}
if (command.ExecutionMode == CommandExecutionMode.CommandText)
{
return(GetTextCommand());
}
DatabaseExecutionCommand databaseExecutionCommand;
switch (command.OperationType)
{
case CommandOperationType.Insert:
databaseExecutionCommand = GetDatabaseInsertionCommand(queryTranslator, command);
break;
case CommandOperationType.Update:
databaseExecutionCommand = GetDatabaseUpdateCommand(queryTranslator, command);
break;
case CommandOperationType.Delete:
databaseExecutionCommand = GetDatabaseDeletionCommand(queryTranslator, command);
break;
default:
databaseExecutionCommand = GetTextCommand();
break;
}
return(databaseExecutionCommand);
}
/// <summary>
/// Get database insertion execution command
/// </summary>
/// <param name="translator">Query translator</param>
/// <param name="command">Command</param>
/// <returns>Return a database insertion command</returns>
DatabaseExecutionCommand GetDatabaseInsertionCommand(IQueryTranslator translator, DefaultCommand command)
{
translator.DataAccessContext.SetCommand(command);
string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command);
var fields = DataManager.GetEditFields(CurrentDatabaseServerType, command.EntityType);
var fieldCount = fields.GetCount();
var insertFormatResult = SQLiteManager.FormatInsertionFields(command.EntityType, fieldCount, fields, command.Parameters, translator.ParameterSequence);
if (insertFormatResult == null)
{
return(null);
}
string cmdText = $"INSERT INTO {SQLiteManager.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});";
CommandParameters parameters = insertFormatResult.Item3;
translator.ParameterSequence += fieldCount;
return(new DatabaseExecutionCommand()
{
CommandText = cmdText,
CommandType = SQLiteManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
Parameters = parameters
});
}
/// <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 = SQLiteManager.ConvertParameter(command.Parameters) ?? new CommandParameters();
string objectName = translator.DataAccessContext.GetCommandEntityObjectName(command);
var fields = SQLiteManager.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 = SQLiteManager.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 = SQLiteManager.GetSystemCalculationOperator(calculateModifyValue.Operator);
newValueExpression = $"{translator.ObjectPetName}.{SQLiteManager.WrapKeyword(field.FieldName)}{calChar}{SQLiteManager.ParameterPrefix}{parameterName}";
}
}
}
if (string.IsNullOrWhiteSpace(newValueExpression))
{
newValueExpression = $"{SQLiteManager.ParameterPrefix}{parameterName}";
}
updateSetArray.Add($"{SQLiteManager.WrapKeyword(field.FieldName)}={newValueExpression}");
}
string cmdText = string.Empty;
string wrapObjectName = SQLiteManager.WrapKeyword(objectName);
if (string.IsNullOrWhiteSpace(joinScript))
{
cmdText = $"{preScript}UPDATE {wrapObjectName} AS {translator.ObjectPetName} SET {string.Join(",", updateSetArray)} {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 updateTableShortName = "UTB";
cmdText = $"{preScript}UPDATE {wrapObjectName} AS {updateTableShortName} SET {string.Join(",", updateSetArray)} WHERE {string.Join("||", primaryKeyFields.Select(pk => updateTableShortName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} IN (SELECT {string.Join("||", primaryKeyFields.Select(pk => translator.ObjectPetName + "." + SQLiteManager.WrapKeyword(pk.FieldName)))} FROM {wrapObjectName} AS {translator.ObjectPetName} {joinScript} {conditionString});";
}
translator.ParameterSequence = parameterSequence;
#endregion
#region parameter
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)
});
}