Как извлечь перекрестные ссылки на базы данных, используя скриптдом API
Microsoft разоблачила scriptdom API для анализа и генерации TSQL. Я новичок в этом и все еще играю с этим. Я хочу знать, как получить перекрестные ссылки на базы данных из запросов, подобных этому.
UPDATE t3
SET description = 'abc'
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.id = t2.t1_id)
LEFT OUTER JOIN database3.dbo.table3 t3
ON (t3.id = t2.t3_id)
INNER JOIN database2.dbo.table4 t4
ON (t4.id = t2.t4_id)
То, что я хочу, это список ссылок:
database1.dbo.table1.id = database2.dbo.table2.t1_id
database3.dbo.table3.id = database2.dbo.table2.t3_id
database2.dbo.table4.id = database2.dbo.table2.t4_id
Тем не менее, для последней записи database2.dbo.table4.id = database2.dbo.table2.t4_idоба столбца с двух концов находятся в одной базе данных database2это не то, что я хочу. Итак, мой последний требуемый результат:
database1.dbo.table1.id = database2.dbo.table2.t1_id
database3.dbo.table3.id = database2.dbo.table2.t3_id
Возможно ли реализовать с scriptdom?
2 ответа
Надежная реализация не легка. Для ограниченной проблемы, поставленной в этом вопросе, решение относительно простое - ударение "относительно". Я предполагаю следующее:
- Запрос имеет только один уровень - в нем нет UNION, подзапросов, выражений WITH или других вещей, которые вводят новые области действия для псевдонимов (и это может очень быстро усложниться).
- Все идентификаторы в запросе полностью определены, поэтому нет сомнений в том, на какой объект он ссылается.
Стратегия решения выглядит следующим образом: сначала мы TSqlFragment составить список всех псевдонимов таблиц, а затем снова посетить его, чтобы получить все эквивалентности, расширяя псевдонимы по пути. Используя этот список, мы определяем список эквайоинов, которые не относятся к одной и той же базе данных. В коде:
var sql = @"
UPDATE t3
SET description = 'abc'
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.id = t2.t1_id)
LEFT OUTER JOIN database3.dbo.table3 t3
ON (t3.id = t2.t3_id)
INNER JOIN database2.dbo.table4 t4
ON (t4.id = t2.t4_id)
";
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
IList<ParseError> errors;
TSqlScript script;
using (var reader = new StringReader(sql)) {
script = (TSqlScript) parser.Parse(reader, out errors);
}
// First resolve aliases.
var aliasResolutionVisitor = new AliasResolutionVisitor();
script.Accept(aliasResolutionVisitor);
// Then find all equijoins, expanding aliases along the way.
var findEqualityJoinVisitor = new FindEqualityJoinVisitor(
aliasResolutionVisitor.Aliases
);
script.Accept(findEqualityJoinVisitor);
// Now list all aliases where the left database is not the same
// as the right database.
foreach (
var equiJoin in
findEqualityJoinVisitor.EqualityJoins.Where(
j => !j.JoinsSameDatabase()
)
) {
Console.WriteLine(equiJoin.ToString());
}
Выход:
database3.dbo.table3.id = database2.dbo.table2.t3_id
database1.dbo.table1.id = database2.dbo.table2.t1_id
AliasResolutionVisitor это простая вещь:
public class AliasResolutionVisitor : TSqlFragmentVisitor {
readonly Dictionary<string, string> aliases = new Dictionary<string, string>();
public Dictionary<string, string> Aliases { get { return aliases; } }
public override void Visit(NamedTableReference namedTableReference ) {
Identifier alias = namedTableReference.Alias;
string baseObjectName = namedTableReference.SchemaObject.AsObjectName();
if (alias != null) {
aliases.Add(alias.Value, baseObjectName);
}
}
}
Мы просто просматриваем все ссылки на именованные таблицы в запросе и, если у них есть псевдоним, добавляем их в словарь. Обратите внимание, что это будет с треском провалено, если будут введены подзапросы, потому что у этого посетителя нет понятия области видимости (и действительно, добавить область видимости для посетителя намного сложнее, потому что TSqlFragment не предлагает способа аннотировать дерево разбора или даже пройти его от узла).
EqualityJoinVisitor интереснее
public class FindEqualityJoinVisitor : TSqlFragmentVisitor {
readonly Dictionary<string, string> aliases;
public FindEqualityJoinVisitor(Dictionary<string, string> aliases) {
this.aliases = aliases;
}
readonly List<EqualityJoin> equalityJoins = new List<EqualityJoin>();
public List<EqualityJoin> EqualityJoins { get { return equalityJoins; } }
public override void Visit(QualifiedJoin qualifiedJoin) {
var findEqualityComparisonVisitor = new FindEqualityComparisonVisitor();
qualifiedJoin.SearchCondition.Accept(findEqualityComparisonVisitor);
foreach (
var equalityComparison in findEqualityComparisonVisitor.Comparisons
) {
var firstColumnReferenceExpression =
equalityComparison.FirstExpression as ColumnReferenceExpression
;
var secondColumnReferenceExpression =
equalityComparison.SecondExpression as ColumnReferenceExpression
;
if (
firstColumnReferenceExpression != null &&
secondColumnReferenceExpression != null
) {
string firstColumnResolved = resolveMultipartIdentifier(
firstColumnReferenceExpression.MultiPartIdentifier
);
string secondColumnResolved = resolveMultipartIdentifier(
secondColumnReferenceExpression.MultiPartIdentifier
);
equalityJoins.Add(
new EqualityJoin(firstColumnResolved, secondColumnResolved)
);
}
}
}
private string resolveMultipartIdentifier(MultiPartIdentifier identifier) {
if (
identifier.Identifiers.Count == 2 &&
aliases.ContainsKey(identifier.Identifiers[0].Value)
) {
return
aliases[identifier.Identifiers[0].Value] + "." +
identifier.Identifiers[1].Value;
} else {
return identifier.AsObjectName();
}
}
}
Это охотится за QualifiedJoin экземпляры и, если мы их найдем, мы, в свою очередь, исследуем условие поиска, чтобы найти все вхождения сравнений на равенство. Обратите внимание, что это работает с вложенными условиями поиска: в Bar JOIN Foo ON Bar.Quux = Foo.Quux AND Bar.Baz = Foo.Baz, мы найдем оба выражения.
Как мы их находим? Используя другого маленького посетителя:
public class FindEqualityComparisonVisitor : TSqlFragmentVisitor {
List<BooleanComparisonExpression> comparisons =
new List<BooleanComparisonExpression>()
;
public List<BooleanComparisonExpression> Comparisons {
get { return comparisons; }
}
public override void Visit(BooleanComparisonExpression e) {
if (e.IsEqualityComparison()) comparisons.Add(e);
}
}
Ничего сложного здесь нет. Нетрудно сложить этот код в другого посетителя, но я думаю, что это понятнее.
Вот и все, за исключением некоторого вспомогательного кода, который я представлю без комментариев:
public class EqualityJoin {
readonly SchemaObjectName left;
public SchemaObjectName Left { get { return left; } }
readonly SchemaObjectName right;
public SchemaObjectName Right { get { return right; } }
public EqualityJoin(
string qualifiedObjectNameLeft, string qualifiedObjectNameRight
) {
var parser = new TSql120Parser(initialQuotedIdentifiers: false);
IList<ParseError> errors;
using (var reader = new StringReader(qualifiedObjectNameLeft)) {
left = parser.ParseSchemaObjectName(reader, out errors);
}
using (var reader = new StringReader(qualifiedObjectNameRight)) {
right = parser.ParseSchemaObjectName(reader, out errors);
}
}
public bool JoinsSameDatabase() {
return left.Identifiers[0].Value == right.Identifiers[0].Value;
}
public override string ToString() {
return String.Format("{0} = {1}", left.AsObjectName(), right.AsObjectName());
}
}
public static class MultiPartIdentifierExtensions {
public static string AsObjectName(this MultiPartIdentifier multiPartIdentifier) {
return string.Join(".", multiPartIdentifier.Identifiers.Select(i => i.Value));
}
}
public static class ExpressionExtensions {
public static bool IsEqualityComparison(this BooleanExpression expression) {
return
expression is BooleanComparisonExpression &&
((BooleanComparisonExpression) expression).ComparisonType == BooleanComparisonType.Equals
;
}
}
Как я упоминал ранее, этот код довольно хрупкий. Предполагается, что запросы имеют определенную форму, и он может потерпеть неудачу (весьма плохо, давая ошибочные результаты), если они этого не делают. Основной открытой задачей было бы расширить ее, чтобы она могла правильно обрабатывать области и неквалифицированные ссылки, а также другие странности, которые может иметь скрипт T-SQL, но, тем не менее, я думаю, что это полезная отправная точка.
Возможно, еще один способ попытаться это выполнить ваш запрос как:
SET SHOWPLAN_XML ON;
UPDATE t3
SET description = 'abc'
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.id = t2.t1_id)
LEFT OUTER JOIN database3.dbo.table3 t3
ON (t3.id = t2.t3_id)
INNER JOIN database2.dbo.table4 t4
ON (t4.id = t2.t4_id)
Это возвращает план запроса XML. В XML вы найдете условия соединения в узле RelOp. Например, для цикла хеширования вы увидите что-то вроде:
<RelOp NodeId="7" PhysicalOp="Hash Match" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0177716" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0243408" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
.. some stuff cut from here
<Hash>
..
<ProbeResidual>
<ScalarOperator ScalarString="[database2].[dbo].[table4].[Id] as [t4].[Id]=[database2].[dbo].[table2].[t4_Id] as [t2].[t4_Id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[database2]" Schema="[dbo]" Table="[table4]" Alias="[t4]" Column="Id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[database2]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="t4_Id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
Для вложенного цикла что-то вроде:
<NestedLoops Optimized="0">
<Predicate>
<ScalarOperator ScalarString="[database3].[dbo].[table3].[Id] as [t3].[Id]=[database2].[dbo].[table2].[t3_id] as [t2].[t3_id]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[database3]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="Id" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[database2]" Schema="[dbo]" Table="[table2]" Alias="[t2]" Column="t3_id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
Возможно, вы могли бы затем обработать это в C#, чтобы извлечь все объединения, а затем сравнить базы данных, содержащиеся в ссылках на столбцы.
Извиняюсь за форматирование.