/// <summary>
/// Adds the reverse cross reference join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private static void AddReverseCrossReferenceJoin(
StringBuilder sql, ReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN {0} ON {0}.\"{1}\" = {2}.\"{3}\"",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(join.ReferenceField.SystemName),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName))
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{2}\"",
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
aliasCollection.GetTableAlias(join.ReferencedProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetReverseCrossReferenceItemsQueryName(join));
}
/// <summary>
/// Adds the checklist join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private void AddChecklistJoin(StringBuilder sql, ChecklistFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\"",
OracleNamesTranslator.Translate(GetJoinTableName(join)),
aliasCollection.GetJoinTableAlias(join),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(join)),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName))
.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\" AND {1}.\"{5}\" = 0",
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
aliasCollection.GetTableAlias(join.ReferencedProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetJoinTableAlias(join),
OracleNamesTranslator.Translate(GetJoinTableChildKey(join)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
/// <summary>
/// Adds the base table joins.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="process">The process.</param>
/// <param name="lastProcess">The last process.</param>
/// <param name="aliasCollection">The alias collection.</param>
private static void AddBaseTableJoins(
StringBuilder sql, ProcessDefinition process, ProcessDefinition lastProcess, TableAliasCollection aliasCollection)
{
if (process == lastProcess || process.BaseProcess == null)
return;
sql.AppendLine()
.Append("\t")
.AppendFormat(
@"INNER JOIN ""{0}"" {1} ON {1}.""{2}"" = {3}.""{4}""",
OracleNamesTranslator.Translate(process.BaseProcess.SystemName),
aliasCollection.GetTableAlias(process.BaseProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetTableAlias(process),
OracleNamesTranslator.Translate(Constants.BaseIdColumnName));
AddBaseTableJoins(sql, process.BaseProcess, lastProcess, aliasCollection);
}
/// <summary>
/// Adds the single cross reference join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private static void AddSingleCrossReferenceJoin(StringBuilder sql, SingleCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
sql.AppendLine()
.Append("\t")
.AppendFormat(
"INNER JOIN \"{0}\" {1} ON {1}.\"{2}\" = {3}.\"{4}\" AND {1}.\"{5}\" = 0",
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
aliasCollection.GetTableAlias(join.ReferencedProcess),
OracleNamesTranslator.Translate(Constants.IdColumnName),
aliasCollection.GetTableAlias(join.DeclaringProcess),
OracleNamesTranslator.Translate(join.SystemName),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
/// <summary>
/// Generates the expression affected items query.
/// </summary>
/// <param name="dependency">The dependency.</param>
/// <returns>System.String.</returns>
private string GenerateExpressionAffectedItemsQuery(ExpressionDependency dependency)
{
var sql = new StringBuilder();
var aliasCollection = new TableAliasCollection();
var queries = GetJoinQueries(dependency, aliasCollection);
if (queries.Count > 0)
{
sql.AppendLine("WITH");
sql.AppendLine(string.Join(Environment.NewLine + ",", queries));
}
sql.AppendFormat(@"SELECT DISTINCT {0}.""Id""
FROM
""{1}"" {0}", aliasCollection.GetTableAlias(dependency.DependentProcess), OracleNamesTranslator.Translate(dependency.DependentProcess.SystemName));
var currentProcess = dependency.DependentProcess;
foreach (var @join in dependency.JoinFields)
{
AddBaseTableJoins(sql, currentProcess, @join.DeclaringProcess, aliasCollection);
AddJoin(sql, @join, aliasCollection);
currentProcess = @join.ReferencedProcess;
}
sql.AppendLine()
.AppendFormat(
"WHERE {0}.\"{1}\" = 0 AND {2}.\"{3}\" = :id",
aliasCollection.GetTableAlias(dependency.DependentProcess),
Constants.IsRemovedColumnName,
aliasCollection.GetTableAlias(dependency.JoinFields.Last().ReferencedProcess),
Constants.IdColumnName);
return sql.ToString();
}
