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