private static string GetResolveVersionMasterQuery(SingleCrossReferenceFieldDefinition field, TableAliasCollection aliasCollection) { var result = new StringBuilder(); result.AppendFormat( @" {0} AS ( SELECT v.[{1}] ,v.[{2}] ,(CASE WHEN v.[{3}] IS NULL OR v.[{3}] = 0 THEN v.[{1}] ELSE v.[{3}] END) [{3}] ,v.[{4}] FROM [dbo].[{5}] v WHERE v.[{6}] = 0 )", aliasCollection.GetResolveVersionMasterQueryName(field), Constants.IdColumnName, Constants.VersionNumber, Constants.VersionMasterId, field.SystemName, field.DeclaringProcess.SystemName, Constants.IsRemovedColumnName); return result.ToString(); }
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 [dbo].[{5}] v INNER JOIN [dbo].[{6}] jt ON jt.[{7}] = v.[{1}] WHERE v.[{8}] = 0 )", aliasCollection.GetResolveVersionMasterQueryName(field), Constants.IdColumnName, Constants.VersionNumber, Constants.VersionMasterId, GetJoinTableChildKey(field), field.DeclaringProcess.SystemName, GetJoinTableName(field), GetJoinTableMasterKey(field), Constants.IsRemovedColumnName); return result.ToString(); }
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 [dbo].[{3}] q1 INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] AND q2.[{6}] = 0 WHERE q1.[{6}] = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), Constants.IdColumnName, referenceField.SystemName, join.ReferencedProcess.SystemName, join.DeclaringProcess.SystemName, referenceField.LinkField.SystemName, Constants.IsRemovedColumnName); } else { if (!join.ShowLatestVersion) { result.AppendFormat( @" {0} AS ( SELECT q1.[{1}], q2.[{2}] AS [{7}] FROM [dbo].[{3}] q1 INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] WHERE q1.[{6}] = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), Constants.IdColumnName, GetJoinTableChildKey(referenceField), join.ReferencedProcess.SystemName, GetJoinTableName(referenceField), GetJoinTableMasterKey(referenceField), Constants.IsRemovedColumnName, 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), GetJoinTableChildKey(referenceField), aliasCollection.GetResolveVersionMasterQueryName(referenceField), Constants.VersionNumber, Constants.VersionMasterId, referenceField.SystemName); } } return result.ToString(); }
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 [dbo].[{3}] q1 INNER JOIN [dbo].[{4}] q2 ON q2.[{5}] = q1.[{1}] WHERE q1.[{6}] = 0 )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), Constants.IdColumnName, GetJoinTableChildKey(referenceField), join.ReferencedProcess.SystemName, GetJoinTableName(referenceField), GetJoinTableMasterKey(referenceField), Constants.IsRemovedColumnName, referenceField.SystemName); return result.ToString(); }
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(); }
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 [dbo].[{2}] WHERE [IsRemoved] = 0 GROUP BY [{1}] )", aliasCollection.GetReverseCrossReferenceItemsQueryName(join), referenceField.SystemName, 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), referenceField.SystemName, aliasCollection.GetResolveVersionMasterQueryName(referenceField), Constants.VersionNumber, Constants.VersionMasterId); } return result.ToString(); }
private void AddChecklistJoin(StringBuilder sql, ChecklistFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]", GetJoinTableName(join), aliasCollection.GetJoinTableAlias(join), GetJoinTableMasterKey(join), aliasCollection.GetTableAlias(join.DeclaringProcess), Constants.IdColumnName) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetJoinTableAlias(join), GetJoinTableChildKey(join), Constants.IsRemovedColumnName); }
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), join.ReferenceField.SystemName, aliasCollection.GetTableAlias(join.DeclaringProcess), Constants.IdColumnName) .AppendLine() .Append("\t") .AppendFormat( "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{2}]", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetReverseCrossReferenceItemsQueryName(join)); }
private static void AddSingleCrossReferenceJoin(StringBuilder sql, SingleCrossReferenceFieldDefinition join, TableAliasCollection aliasCollection) { sql.AppendLine() .Append("\t") .AppendFormat( @"INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}] AND {1}.[{5}] = 0", join.ReferencedProcess.SystemName, aliasCollection.GetTableAlias(join.ReferencedProcess), Constants.IdColumnName, aliasCollection.GetTableAlias(join.DeclaringProcess), join.SystemName, Constants.IsRemovedColumnName); }
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 [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[{4}]", process.BaseProcess.SystemName, aliasCollection.GetTableAlias(process.BaseProcess), Constants.IdColumnName, aliasCollection.GetTableAlias(process), Constants.BaseIdColumnName); AddBaseTableJoins(sql, process.BaseProcess, lastProcess, aliasCollection); }
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); }
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; }
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 [dbo].[{1}] {0}", aliasCollection.GetTableAlias(dependency.DependentProcess), 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(); }