/// <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") });
}
/// <summary>
/// Refs the get multi reverse cross reference field select.
/// <author>unknown</author>
/// </summary>
/// <param name="tableName">Name of the table.</param>
/// <param name="displayField">The display field.</param>
/// <param name="tableAliases">The table aliases.</param>
/// <param name="cteString">The cte string.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.NotImplementedException"></exception>
protected override string RefGetMultiReverseCrossReferenceFieldSelect(
string tableName,
ReferenceDisplayFieldDefinition displayField,
TableAliasCollection tableAliases,
StringBuilder cteString)
{
throw new NotImplementedException();
}
/// <summary>
/// Refs the get field select.
/// <author>unknown</author>
/// </summary>
/// <param name="displayField">The display field.</param>
/// <param name="tableAliases">The table aliases.</param>
/// <param name="cteString">The cte string.</param>
/// <param name="fromString">From string.</param>
/// <param name="whereString">The where string.</param>
/// <returns>System.String.</returns>
/// <exception cref="System.NotImplementedException"></exception>
protected override string RefGetFieldSelect(
ReferenceDisplayFieldDefinition displayField,
TableAliasCollection tableAliases,
StringBuilder cteString,
StringBuilder fromString,
StringBuilder whereString)
{
throw new NotImplementedException();
}
/// <summary>
/// Refs the add single cross ref outer joins.
/// <author>unknown</author>
/// </summary>
/// <param name="joinsString">The joins string.</param>
/// <param name="tableAliases">The table aliases.</param>
/// <param name="joinField">The join field.</param>
/// <param name="path">The path.</param>
/// <param name="field">The field.</param>
/// <exception cref="System.NotImplementedException"></exception>
protected override void RefAddSingleCrossRefOuterJoins(
StringBuilder joinsString,
TableAliasCollection tableAliases,
ReferenceJoinFieldDefinition joinField,
string path = "",
ReferenceFieldDefinition field = null)
{
throw new NotImplementedException();
}
/// <summary>
/// Refs the add base process outer joins.
/// <author>unknown</author>
/// </summary>
/// <param name="joinsString">The joins string.</param>
/// <param name="tableAliases">The table aliases.</param>
/// <param name="mainProcess">The main process.</param>
/// <param name="path">The path.</param>
/// <exception cref="System.NotImplementedException"></exception>
protected override void RefAddBaseProcessOuterJoins(
StringBuilder joinsString,
TableAliasCollection tableAliases,
ReferenceProcessDefinition mainProcess,
string path = "")
{
throw new NotImplementedException();
}
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 string GetFilter(IFieldMetadata field, TableAliasCollection aliasCollection)
{
var singleCR = field as SingleCrossReferenceFieldMetadata;
if (singleCR != null)
return GetFilter(singleCR, aliasCollection);
var multiCR = field as MultiCrossReferenceFieldMetadata;
if (multiCR != null)
return GetFilter(multiCR, aliasCollection);
throw new NotSupportedException(
string.Format(
CultureInfo.InvariantCulture,
"The field '{0}' from process '{1}' is not a valid reference field.",
field.Name,
field.DeclaringProcess.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;
}
