/// <summary>
/// Gets the resolve version master query.
/// </summary>
/// <param name="field">The field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private string GetResolveVersionMasterQuery(MultiCrossReferenceFieldDefinition field, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT
""{1}""
,""{2}""
,(CASE WHEN v.""{3}"" IS NULL OR v.""{3}"" = 0 THEN v.""{1}"" ELSE v.""{3}"" END) ""{3}""
,""{4}""
FROM
""{5}"" v
INNER JOIN ""{6}"" jt ON jt.""{7}"" = v.""{1}""
WHERE v.""{8}"" = 0
)",
aliasCollection.GetResolveVersionMasterQueryName(field),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId),
OracleNamesTranslator.Translate(GetJoinTableChildKey(field)),
OracleNamesTranslator.Translate(field.DeclaringProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(field)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(field)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
return result.ToString();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private string GetReverseCrossReferenceItemsQuery(
MultiReverseCrossReferenceFieldDefinition join,
MultiCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (referenceField.LinkField != null)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{1}"" AS ""{2}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}"" AND q2.""{6}"" = 0
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(join.DeclaringProcess.SystemName),
OracleNamesTranslator.Translate(referenceField.LinkField.SystemName),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName));
}
else
{
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{2}"" AS ""{7}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}""
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(referenceField)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(referenceField)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName));
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",");
result.AppendFormat(
@"
{0}
AS
(
SELECT
MIN(""Id"") ""Id""
,q1.""{1}"" AS ""{5}""
FROM {2} q1
INNER JOIN (
SELECT
MAX(""{3}"") ""{3}""
,""{1}""
,""{4}""
FROM {2}
GROUP BY ""{1}"", ""{4}""
) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}""
GROUP BY q1.""{1}"", q1.""{4}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId),
OracleNamesTranslator.Translate(referenceField.SystemName));
}
}
return result.ToString();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private string GetReverseCrossReferenceItemsQuery(
ReverseCrossReferenceFieldDefinition join, ChecklistFieldDefinition referenceField, TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
result.AppendFormat(
@"
{0}
AS
(
SELECT q1.""{1}"", q2.""{2}"" AS ""{7}""
FROM
""{3}"" q1
INNER JOIN ""{4}"" q2 ON q2.""{5}"" = q1.""{1}""
WHERE q1.""{6}"" = 0
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(Constants.IdColumnName),
OracleNamesTranslator.Translate(GetJoinTableChildKey(referenceField)),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName),
OracleNamesTranslator.Translate(GetJoinTableName(referenceField)),
OracleNamesTranslator.Translate(GetJoinTableMasterKey(referenceField)),
OracleNamesTranslator.Translate(Constants.IsRemovedColumnName),
OracleNamesTranslator.Translate(referenceField.SystemName));
return result.ToString();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.InvalidOperationException"></exception>
private string GetReverseCrossReferenceItemsQuery(
MultiReverseCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
var singleCrossReference = join.ReferenceField as SingleCrossReferenceFieldDefinition;
if (singleCrossReference != null)
return GetReverseCrossReferenceItemsQuery(join, singleCrossReference, aliasCollection);
var multiCrossReference = join.ReferenceField as MultiCrossReferenceFieldDefinition;
if (multiCrossReference != null)
return GetReverseCrossReferenceItemsQuery(join, multiCrossReference, aliasCollection);
var checklist = join.ReferenceField as ChecklistFieldDefinition;
if (checklist != null)
return GetReverseCrossReferenceItemsQuery(join, checklist, aliasCollection);
throw new InvalidOperationException();
}
/// <summary>
/// Gets the reverse cross reference items query.
/// </summary>
/// <param name="join">The join.</param>
/// <param name="referenceField">The reference field.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>System.String.</returns>
private static string GetReverseCrossReferenceItemsQuery(
SingleReverseCrossReferenceFieldDefinition join,
SingleCrossReferenceFieldDefinition referenceField,
TableAliasCollection aliasCollection)
{
var result = new StringBuilder();
if (!join.ShowLatestVersion)
{
result.AppendFormat(
@"
{0}
AS
(
SELECT MIN(""Id"") ""Id"", ""{1}""
FROM ""{2}""
WHERE ""IsRemoved"" = 0
GROUP BY ""{1}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(referenceField.SystemName),
OracleNamesTranslator.Translate(join.ReferencedProcess.SystemName));
}
else
{
result.Append(GetResolveVersionMasterQuery(referenceField, aliasCollection)).AppendLine(",").AppendFormat(
@"
{0}
AS
(
SELECT
MIN(""Id"") ""Id""
,q1.""{1}""
FROM
{2} q1
INNER JOIN (
SELECT
MAX(""{3}"") ""{3}""
,""{1}""
,""{4}""
FROM {2}
GROUP BY ""{1}"", ""{4}""
) q2 ON q2.""{4}"" = q1.""{4}"" AND q2.""{3}"" = q1.""{3}"" AND q2.""{1}"" = q1.""{1}""
GROUP BY q1.""{1}""
)",
aliasCollection.GetReverseCrossReferenceItemsQueryName(join),
OracleNamesTranslator.Translate(referenceField.SystemName),
aliasCollection.GetResolveVersionMasterQueryName(referenceField),
OracleNamesTranslator.Translate(Constants.VersionNumber),
OracleNamesTranslator.Translate(Constants.VersionMasterId));
}
return result.ToString();
}
/// <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 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 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>
/// 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 join.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="join">The join.</param>
/// <param name="aliasCollection">The alias collection.</param>
private void AddJoin(StringBuilder sql, ReferenceFieldDefinition join, TableAliasCollection aliasCollection)
{
var singleCrossReferenceJoin = join as SingleCrossReferenceFieldDefinition;
if (singleCrossReferenceJoin != null)
AddSingleCrossReferenceJoin(sql, singleCrossReferenceJoin, aliasCollection);
var multiCrossReferenceJoin = join as MultiCrossReferenceFieldDefinition;
if (multiCrossReferenceJoin != null)
AddMultiCrossReferenceJoin(sql, multiCrossReferenceJoin, aliasCollection);
var checklistJoin = join as ChecklistFieldDefinition;
if (checklistJoin != null)
AddChecklistJoin(sql, checklistJoin, aliasCollection);
var reverseCrossReference = join as ReverseCrossReferenceFieldDefinition;
if (reverseCrossReference != null)
AddReverseCrossReferenceJoin(sql, reverseCrossReference, aliasCollection);
}
/// <summary>
/// Gets the join queries.
/// </summary>
/// <param name="dependency">The dependency.</param>
/// <param name="aliasCollection">The alias collection.</param>
/// <returns>List{System.String}.</returns>
private List<string> GetJoinQueries(ExpressionDependency dependency, TableAliasCollection aliasCollection)
{
var queries = new List<string>();
foreach (var joinField in dependency.JoinFields)
{
var singleReverseCrossReference = joinField as SingleReverseCrossReferenceFieldDefinition;
if (singleReverseCrossReference != null)
queries.Add(GetReverseCrossReferenceItemsQuery(singleReverseCrossReference, aliasCollection));
var multiReverseCrossReference = joinField as MultiReverseCrossReferenceFieldDefinition;
if (multiReverseCrossReference != null)
queries.Add(GetReverseCrossReferenceItemsQuery(multiReverseCrossReference, aliasCollection));
}
return queries;
}
/// <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();
}
