private string GetInitialDerivedTableJoin(JoinDescription joinDescription)
{
var derivedTableAlias = DerivedTableWrapperNameResolver.GetWrapperName(GetLeftTableAlias(joinDescription));
var joinPrefix = GetJoinPrefix(joinDescription);
var secondaryJoins = Joins
.Where(a => a.Id != joinDescription.Id)
.Where(a =>
{
var sameOuterTable = a.LeftTable.Equals(joinDescription.RightTable, StringComparison.InvariantCultureIgnoreCase);
var hasDerivedTable = !string.IsNullOrEmpty(a.ExplicitRightTableAlias);
var sameDerivedTable = hasDerivedTable && a.ExplicitRightTableAlias.Equals(derivedTableAlias, StringComparison.InvariantCultureIgnoreCase);
return(sameOuterTable && sameDerivedTable);
}).ToList();
var rightTableAlias = QBuilder.TableNameAliaser.GetTableAlias(joinDescription.RightTable);
var line = $"{joinPrefix} join ({joinDescription.DerivedTable}) as {derivedTableAlias} on {derivedTableAlias}.{joinDescription.LeftField} = {rightTableAlias}.{joinDescription.RightField}";
line += Environment.NewLine;
foreach (var secJoin in secondaryJoins)
{
secJoin.Consumed = true;
line += $" and {rightTableAlias}.{secJoin.LeftField} = {secJoin.ExplicitRightTableAlias}.{secJoin.RightField}";
}
return(line);
}
private RelationshipClass MakeJoin(RelationshipClass relationshipClass, Table leftTable, Table rightTable)
{
var virtualRelationshipClassDescription = new VirtualRelationshipClassDescription(_leftField, _rightField,
SelectedCardinality.RelationshipCardinality);
relationshipClass = leftTable.RelateTo(rightTable, virtualRelationshipClassDescription);
var joinDescription = new JoinDescription(relationshipClass)
{
JoinDirection = JoinDirection.Forward
};
joinDescription.JoinType = IsLeftOuterJoin ? JoinType.LeftOuterJoin : JoinType.InnerJoin;
if (LeftFields.Count > 1)
{
joinDescription.TargetFields = LeftFields.Select(item => item.Field).ToList();
}
var join = new Join(joinDescription);
var joinedTable = @join.GetJoinedTable();
if (joinedTable is FeatureClass)
{
LayerFactory.Instance.CreateFeatureLayer(joinedTable as FeatureClass, MapView.Active.Map, 0, LayerName);
}
return(relationshipClass);
}
private void FlipTablesIfLeftTableAlreadyAliased(JoinDescription joinDescription)
{
var searchResult = _alreadyAliasedTables.FirstOrDefault(a => a.Equals(joinDescription.LeftTable, StringComparison.InvariantCultureIgnoreCase));
var leftTableAlreadyAliased = searchResult != null;
if (leftTableAlreadyAliased)
{
new JoinDescriptionFlipper().Flip(joinDescription);
}
}
private string GetNonDerivedTableJoinLine(JoinDescription joinDescription)
{
FlipTablesIfLeftTableAlreadyAliased(joinDescription);
var joinPrefix = GetJoinPrefix(joinDescription);
var rightTableAlias = QBuilder.TableNameAliaser.GetTableAlias(joinDescription.RightTable);
var line = $"{joinPrefix}join {joinDescription.LeftTable} {QBuilder.TableNameAliaser.GetTableAlias(joinDescription.LeftTable)} on {QBuilder.TableNameAliaser.GetTableAlias(joinDescription.LeftTable)}.{joinDescription.LeftField}";
line += $" = {rightTableAlias}.{joinDescription.RightField}{Environment.NewLine}";
return(line);
}
private string GetLeftTableAlias(JoinDescription joinDescription)
{
var leftTableAlias = joinDescription.ExplicitLeftTableAlias;
if (string.IsNullOrEmpty(leftTableAlias))
{
leftTableAlias = QBuilder.TableNameAliaser.GetTableAlias(joinDescription.LeftTable);
}
return(leftTableAlias);
}
internal void SecondaryDerivedTableJoin(string rightField, string rightTable, QBuilder derivedTable, string derivedFieldName, string joinType)
{
var joinDescription = new JoinDescription
{
LeftTable = rightTable,
LeftField = rightField,
ExplicitRightTableAlias = DerivedTableWrapperNameResolver.GetWrapperName(derivedTable.DerivedTableName),
RightField = derivedFieldName,
JoinType = joinType,
};
Joins.Add(joinDescription);
}
private void TranslateToJoinDescription(DerivedTableJoinDescription derivedTableJoinDescription)
{
var joinDescription = new JoinDescription
{
JoinType = derivedTableJoinDescription.JoinType,
RightField = derivedTableJoinDescription.RightField,
RightTable = derivedTableJoinDescription.RightTable,
LeftField = derivedTableJoinDescription.LeftField,
ExplicitLeftTableAlias = derivedTableJoinDescription.QBuilder.DerivedTableName,
DerivedTable = derivedTableJoinDescription.QBuilder.Build(),
};
Joins.Add(joinDescription);
}
public void Flip(JoinDescription joinDescription)
{
var flippedVersion = new JoinDescription
{
LeftField = joinDescription.RightField,
LeftTable = joinDescription.RightTable,
RightTable = joinDescription.LeftTable,
RightField = joinDescription.LeftField,
};
joinDescription.LeftField = flippedVersion.LeftField;
joinDescription.LeftTable = flippedVersion.LeftTable;
joinDescription.RightField = flippedVersion.RightField;
joinDescription.RightTable = flippedVersion.RightTable;
}
internal bool TableFoundInJoin(string table, JoinDescription joinDescription)
{
var leftTable = string.Empty;
var rightTable = joinDescription.RightTable;
if (joinDescription.IsInitialDerivedTableJoin)
{
leftTable = DerivedTableWrapperNameResolver.GetWrapperName(joinDescription.ExplicitLeftTableAlias);
}
else
{
leftTable = joinDescription.LeftTable;
}
return(leftTable.Equals(table, StringComparison.CurrentCultureIgnoreCase) ||
rightTable.Equals(table, StringComparison.CurrentCultureIgnoreCase));
}
private string GetJoinLine(JoinDescription joinDescription)
{
if (joinDescription.IsInitialDerivedTableJoin)
{
return(GetInitialDerivedTableJoin(joinDescription));
}
else if (joinDescription.IsSecondaryDerivedTableJoin)
{
if (joinDescription.Consumed == false)
{
return(GetSecondaryDerivedTableJoin(joinDescription));
}
else
{
return(string.Empty);
}
}
else
{
return(GetNonDerivedTableJoinLine(joinDescription));
}
}
public void JoinDescriptionFlipIsDoneCorrectly()
{
const string leftTable = "leftTable";
const string rightTable = "RightTable";
const string leftField = "leftField";
const string rightField = "rightField";
var joinDescription = new JoinDescription
{
LeftField = rightField,
LeftTable = rightTable,
RightField = leftField,
RightTable = leftTable
};
new JoinDescriptionFlipper().Flip(joinDescription);
Assert.Equal(leftTable, joinDescription.LeftTable);
Assert.Equal(leftField, joinDescription.LeftField);
Assert.Equal(rightField, joinDescription.RightField);
Assert.Equal(rightTable, joinDescription.RightTable);
}
private string GetJoinPrefix(JoinDescription joinDescription)
{
switch (joinDescription.JoinType)
{
default:
var joinTypeIsUnsupported = true;
DataValidator.EvaluateImmediate(joinTypeIsUnsupported, $"Unsupported join type '{joinDescription.JoinType}'");
break;
case JoinTypes.Full:
return("Full Outer ");
case JoinTypes.Inner:
return(string.Empty);
case JoinTypes.RightJoin:
return("Right ");
case JoinTypes.LeftJoin:
return("Left ");
}
return(string.Empty);
}
