/// <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 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 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(); }
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 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 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 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(); }