/// <summary> /// Generates queries for items that are already saved in the database. /// </summary> /// <returns> /// The <see cref="QueryDefinition"/>. /// </returns> /// <remarks> /// The queries generated by this method will work only for items that are already saved in the database. /// Only the source process id is retrieved from the <see cref="IEditableRoot"/> object. /// </remarks> public QueryDefinition GenerateForSavedItem() { var fieldNames = !string.IsNullOrEmpty(FieldPath) ? FieldPath.Split('.') : new string[0]; var aliasCollection = new TableAliasCollection(); var path = string.Empty; var currentProcess = InputProcess; var sourceList = new List<string> { string.Format( CultureInfo.InvariantCulture, "[dbo].[{0}] {1}", currentProcess.Name, aliasCollection.GetTableAlias(path, currentProcess)) }; for (var i = 0; i < fieldNames.Length; ++i) { var field = currentProcess.GetField(fieldNames[i]); // Add joins to base process that declares the field. sourceList.AddRange(GetBaseProcessJoins(path, currentProcess, field.DeclaringProcess, aliasCollection)); // Add joins to process referenced by field. sourceList.AddRange(GetReferenceFieldJoins(path, field, aliasCollection)); path = path + field.Name + "."; currentProcess = GetReferencedProcess(field); } sourceList.AddRange(GetBaseProcessJoins(path, currentProcess, OutputProcess, aliasCollection)); var query = new StringBuilder(); query.AppendFormat( CultureInfo.InvariantCulture, @"SELECT DISTINCT {0}.[Id] FROM {1} WHERE {2}.[IsRemoved] = 0 AND {2}.[Id] = @id", aliasCollection.GetTableAlias(path, OutputProcess), string.Join(Environment.NewLine, sourceList.Select(s => " " + s)), aliasCollection.GetTableAlias(string.Empty, InputProcess)); return new QueryDefinition(query.ToString(), new IParameterBuilder[] { GenericParameterBuilder.CreateFromField("@id", SqlDbType.Int, "Id") }); }
private static string GetFilter(MultiCrossReferenceFieldMetadata field, TableAliasCollection aliasCollection) { return string.Format( CultureInfo.InvariantCulture, "EXISTS (SELECT 1 FROM @{1}Ids.nodes('/r') items(r) WHERE items.r.value('.', 'int') = {0}.[Id])", aliasCollection.GetTableAlias(string.Empty, field.ReferencedProcess), field.Name); }
private static string GetFilter(SingleCrossReferenceFieldMetadata field, TableAliasCollection aliasCollection) { return string.Format( CultureInfo.InvariantCulture, "{0}.[Id] = @{1}Id", aliasCollection.GetTableAlias(string.Empty, field.ReferencedProcess), field.Name); }
private static IEnumerable<string> GetReferenceFieldJoins(string path, MultiCrossReferenceFieldMetadata field, TableAliasCollection aliasCollection) { if (field.LinkField == null) { yield return string.Format( CultureInfo.InvariantCulture, "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[Id]", QueryGeneratorUtils.GetJoinTableName(field), aliasCollection.GetJoinTableAlias(path, field), QueryGeneratorUtils.GetJoinTableMasterKey(field), aliasCollection.GetTableAlias(path, field.DeclaringProcess)); yield return string.Format( CultureInfo.InvariantCulture, "INNER JOIN [dbo].[{1}] {2} ON {2}.[Id] = {0}.[{3}] AND {2}.[IsRemoved] = 0", aliasCollection.GetJoinTableAlias(path, field), field.ReferencedProcess.Name, aliasCollection.GetTableAlias(path + field.Name + ".", field.ReferencedProcess), QueryGeneratorUtils.GetJoinTableChildKey(field)); } else { yield return string.Format( CultureInfo.InvariantCulture, "INNER JOIN [dbo].[{0}] {1} ON {1}.[{2}] = {3}.[Id] AND {1}.[IsRemoved] = 0", field.ReferencedProcess.Name, aliasCollection.GetTableAlias(path + field.Name + ".", field.ReferencedProcess), field.LinkField.Name, aliasCollection.GetTableAlias(path, field.DeclaringProcess)); } }
private static IEnumerable<string> GetReferenceFieldJoins(string path, SingleCrossReferenceFieldMetadata field, TableAliasCollection aliasCollection) { yield return string.Format( CultureInfo.InvariantCulture, @"INNER JOIN [dbo].[{0}] {1} ON {1}.[Id] = {2}.[{3}] AND {1}.[IsRemoved] = 0", field.ReferencedProcess.Name, aliasCollection.GetTableAlias(path + field.Name + ".", field.ReferencedProcess), aliasCollection.GetTableAlias(path, field.DeclaringProcess), field.Name); }
private static IEnumerable<string> GetBaseProcessJoins( string path, IProcessMetadata sourceProcess, IProcessMetadata targetProcess, TableAliasCollection aliasCollection) { var joinList = new List<string>(); while (sourceProcess != targetProcess) { if (sourceProcess.BaseProcess == null) throw new InvalidOperationException("invalid reference"); joinList.Add( string.Format( CultureInfo.InvariantCulture, @"INNER JOIN [dbo].[{0}] {1} ON {1}.[Id] = {2}.[ParentId] AND {1}.[IsRemoved] = 0", sourceProcess.BaseProcess.Name, aliasCollection.GetTableAlias(path, sourceProcess.BaseProcess), aliasCollection.GetTableAlias(path, sourceProcess))); sourceProcess = sourceProcess.BaseProcess; } return joinList; }