/// <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 = PostgreSqlManager.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 sortString = string.IsNullOrWhiteSpace(tranResult.SortString) ? string.Empty : $"ORDER BY {tranResult.SortString}";
var queryFields = PostgreSqlManager.GetQueryFields(command.Query, command.EntityType, true);
string outputFormatedField = string.Join(",", PostgreSqlManager.FormatQueryFields(translator.ObjectPetName, queryFields, true));
if (string.IsNullOrWhiteSpace(tranResult.CombineScript))
{
cmdText = $"{tranResult.PreScript}SELECT {outputFormatedField} FROM {PostgreSqlManager.WrapKeyword(objectName)} AS {translator.ObjectPetName} {joinScript} {(string.IsNullOrWhiteSpace(tranResult.ConditionString) ? string.Empty : $"WHERE {tranResult.ConditionString}")} {sortString} {(size > 0 ? $"LIMIT {size} OFFSET 0" : string.Empty)}";
}
/// <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>
/// Execute database command
/// </summary>
/// <param name="server">Database server</param>
/// <param name="executionOptions">Execution options</param>
/// <param name="databaseExecutionCommands">Database execution commands</param>
/// <param name="useTransaction">Whether use transaction</param>
/// <returns>Return affected data number</returns>
async Task <int> ExecuteDatabaseCommandAsync(DatabaseServer server, CommandExecutionOptions executionOptions, IEnumerable <DatabaseExecutionCommand> databaseExecutionCommands, bool useTransaction)
{
int resultValue = 0;
bool success = true;
using (var conn = PostgreSqlManager.GetConnection(server))
{
IDbTransaction transaction = null;
if (useTransaction)
{
transaction = PostgreSqlManager.GetExecutionTransaction(conn, executionOptions);
}
try
{
foreach (var cmd in databaseExecutionCommands)
{
var cmdDefinition = new CommandDefinition(cmd.CommandText, PostgreSqlManager.ConvertCmdParameters(cmd.Parameters), transaction: transaction, commandType: cmd.CommandType, cancellationToken: executionOptions?.CancellationToken ?? default);
var executeResultValue = await conn.ExecuteAsync(cmdDefinition).ConfigureAwait(false);
success = success && (cmd.MustAffectedData ? executeResultValue > 0 : true);
resultValue += executeResultValue;
if (useTransaction && !success)
{
break;
}
}
if (!useTransaction)
{
return(resultValue);
}
if (success)
{
transaction.Commit();
}
else
{
resultValue = 0;
transaction.Rollback();
}
return(resultValue);
}
catch (Exception ex)
{
resultValue = 0;
transaction?.Rollback();
throw ex;
}
}
}
Tuple <IEnumerable <string>, IEnumerable <string> > FormatWrapJoinFields(IEnumerable <EntityField> fields, string translatorObjectPetName, string sourceObjectPetName, string targetObjectPetName)
{
var joinItems = fields.Select(field =>
{
string fieldName = PostgreSqlManager.WrapKeyword(field.FieldName);
return($"{sourceObjectPetName}.{fieldName} = {targetObjectPetName}.{fieldName}");
});
var queryItems = fields.Select(field =>
{
string fieldName = PostgreSqlManager.WrapKeyword(field.FieldName);
return($"{translatorObjectPetName}.{fieldName}");
});
return(new Tuple <IEnumerable <string>, IEnumerable <string> >(queryItems, joinItems));
}
/// <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 = PostgreSqlManager.ConvertParameter(command.Parameters),
CommandType = PostgreSqlManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
HasPreScript = true
});
}
if (command.ExecutionMode == CommandExecutionMode.CommandText)
{
return(GetTextCommand());
}
DatabaseExecutionCommand executionCommand;
switch (command.OperationType)
{
case CommandOperationType.Insert:
executionCommand = GetDatabaseInsertionCommand(queryTranslator, command);
break;
case CommandOperationType.Update:
executionCommand = GetDatabaseUpdateCommand(queryTranslator, command);
break;
case CommandOperationType.Delete:
executionCommand = GetDatabaseDeletionCommand(queryTranslator, command);
break;
default:
executionCommand = GetTextCommand();
break;
}
return(executionCommand);
}
public FieldConversionResult Convert(FieldConversionContext fieldConversionContext)
{
if (string.IsNullOrWhiteSpace(fieldConversionContext?.ConversionName))
{
return(null);
}
string formatedFieldName;
switch (fieldConversionContext.ConversionName)
{
case FieldConversionNames.StringLength:
formatedFieldName = string.IsNullOrWhiteSpace(fieldConversionContext.ObjectName)
? $"CHAR_LENGTH({fieldConversionContext.ObjectName}.{PostgreSqlManager.WrapKeyword(fieldConversionContext.FieldName)})"
: $"CHAR_LENGTH({PostgreSqlManager.WrapKeyword(fieldConversionContext.FieldName)})";
break;
default:
throw new EZNEWException($"{PostgreSqlManager.CurrentDatabaseServerType} does not support field conversion: {fieldConversionContext.ConversionName}");
}
return(new FieldConversionResult()
{
NewFieldName = formatedFieldName
});
}
/// <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 = PostgreSqlManager.FormatInsertionFields(command.EntityType, fieldCount, fields, command.Parameters, translator.ParameterSequence);
if (insertFormatResult == null)
{
return(null);
}
string cmdText = $"INSERT INTO {PostgreSqlManager.WrapKeyword(objectName)} ({string.Join(",", insertFormatResult.Item1)}) VALUES ({string.Join(",", insertFormatResult.Item2)});";
CommandParameters parameters = insertFormatResult.Item3;
translator.ParameterSequence += fieldCount;
return(new DatabaseExecutionCommand()
{
CommandText = cmdText,
CommandType = PostgreSqlManager.GetCommandType(command),
MustAffectedData = command.MustAffectedData,
Parameters = parameters
});
}
/// <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 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 sortBuilder = 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 queryItemCondition = TranslateCondition(query, condition, parameters, objectName);
if (!queryItemCondition.WithScripts.IsNullOrEmpty())
{
withScripts.AddRange(queryItemCondition.WithScripts);
recurveTableName = queryItemCondition.RecurveObjectName;
recurveTablePetName = queryItemCondition.RecurvePetName;
}
conditionBuilder.Append($" {(index > 0 ? condition.Connector.ToString().ToUpper() : string.Empty)} {queryItemCondition.ConditionString}");
index++;
}
}
#endregion
#region sort
if (useSort && !query.Sorts.IsNullOrEmpty())
{
foreach (var orderItem in query.Sorts)
{
sortBuilder.Append($"{ConvertOrderCriterionName(query, objectName, orderItem)} {(orderItem.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;
}
switch (combineEntry.Type)
{
case CombineType.Except:
var exceptFields = GetCombineFields(query, combineEntry.Query);
var exceptQuery = QueryManager.Create().SetEntityType(query.GetEntityType()).IsNull(exceptFields.First());
var exceptJoinItem = new JoinEntry()
{
Type = JoinType.LeftJoin,
JoinObjectFilter = combineEntry.Query,
JoinCriteria = exceptFields.Select(pk =>
{
var pkJoinField = new JoinField()
{
Name = pk,
Type = JoinFieldType.Field
};
return(RegularJoinCriterion.Create(FieldInfo.Create(pk), CriterionOperator.Equal, FieldInfo.Create(pk)) as IJoinCriterion);
}).ToList(),
JoinObjectExtraFilter = exceptQuery
};
query.Join(exceptJoinItem);
break;
case CombineType.Intersect:
var intersectFields = GetCombineFields(query, combineEntry.Query);
query.Join(intersectFields.ToDictionary(c => c, c => c), JoinType.InnerJoin, CriterionOperator.Equal, combineEntry.Query);
break;
default:
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(",", PostgreSqlManager.FormatQueryFields(combineObjectPetName, query, query.GetEntityType(), true, false))} FROM {PostgreSqlManager.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;
}
break;
}
}
}
#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))
{
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)} {PostgreSqlManager.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(",", PostgreSqlManager.FormatQueryFields(joinObjectPetName, joinEntry.JoinObjectFilter, joinEntry.JoinObjectFilter.GetEntityType(), false, false))} FROM {PostgreSqlManager.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}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)} IN (SELECT {recurveTablePetName}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)} FROM {recurveTableName} AS {recurveTablePetName})";
DataAccessContext.SetActivityQuery(query, location);
string queryObjectName = DataManager.GetEntityObjectName(DataAccessContext);
string withScript =
$"{recurveTableName} AS (SELECT {objectName}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)},{objectName}.{PostgreSqlManager.WrapKeyword(recurveRelationField.FieldName)} FROM {PostgreSqlManager.WrapKeyword(queryObjectName)} AS {objectName} {joinScript} {(string.IsNullOrWhiteSpace(nowConditionString) ? string.Empty : $"WHERE {nowConditionString}")} " +
$"UNION ALL SELECT {objectName}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)},{objectName}.{PostgreSqlManager.WrapKeyword(recurveRelationField.FieldName)} FROM {PostgreSqlManager.WrapKeyword(queryObjectName)} AS {objectName} JOIN {recurveTableName} AS {recurveTablePetName} " +
$"ON {(query.Recurve.Direction == RecurveDirection.Up ? $"{objectName}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)}={recurveTablePetName}.{PostgreSqlManager.WrapKeyword(recurveRelationField.FieldName)}" : $"{objectName}.{PostgreSqlManager.WrapKeyword(recurveRelationField.FieldName)}={recurveTablePetName}.{PostgreSqlManager.WrapKeyword(recurveField.FieldName)}")})";
