如何使用scriptdom API提取跨数据库引用 [英] How to extract cross databases references using scriptdom API
问题描述
Microsoft公开了 scriptdom API 解析并生成TSQL.我是新手，仍然在玩.我想知道如何从像这样的查询中获取跨数据库引用.
Microsoft has exposed the scriptdom API to parse and generate TSQL. I'm new to it and still playing with it. I want to know how to get the cross databases references from queries like this one.
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)
我想要的是参考文献列表:
What I want is a list of the references:
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，这不是我想要的.因此，我最终所需的结果是:
However, for the last entry database2.dbo.table4.id = database2.dbo.table2.t4_id, both of the columns from the 2 ends are from the same database database2, this is not what I want. So my final required result is:
database1.dbo.table1.id = database2.dbo.table2.t1_id
database3.dbo.table3.id = database2.dbo.table2.t3_id
是否可以用scriptdom实现?
推荐答案
可靠的实现并不容易.对于此问题提出的有限问题，解决方案相对简单-相对"强调.我假设以下情况:
A robust implementation is not easy. For the limited problem as posed in this question, the solution is relatively simple -- stress "relatively". I assume the following:
- 查询只有一个级别-没有UNION，子查询，WITH表达式或其他引入别名新作用域的东西(这会很快变得复杂).
- 查询中的所有标识符都是完全限定的，因此毫无疑问它所指的是对象.
解决方案策略如下:我们首先访问TSqlFragment列出所有表别名，然后再次访问它以获得所有等值连接，并在此过程中扩展别名.使用该列表，我们确定不引用同一数据库的等联接列表.在代码中:
The solution strategy looks like this: we first visit the TSqlFragment to make a list of all table aliases, then visit it again to get all equijoins, expanding aliases along the way. Using that list, we determine the list of equijoins that do not refer to the same database. In code:
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无法提供注释树的解析方法，甚至无法将其从列表中移出.节点).
We simply go through all the named table references in the query and, if they have an alias, add this to a dictionary. Note that this will fail miserably if subqueries are introduced, because this visitor has no notion of scope (and indeed, adding scope to a visitor is much harder because the TSqlFragment offers no way to annotate the parse tree or even walk it from a node).
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中，我们将找到两个表达式.
This hunts for QualifiedJoin instances and, if we find them, we in turn examine the search condition to find all occurrences of equality comparisons. Note that this does work with nested search conditions: in Bar JOIN Foo ON Bar.Quux = Foo.Quux AND Bar.Baz = Foo.Baz, we will find both expressions.
我们如何找到它们?使用另一个小的访客:
How do we find them? Using another small visitor:
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);
}
}
这里没有什么复杂的.将此代码折叠到其他访问者中并不难，但是我认为这很清楚.
Nothing complicated here. It wouldn't be hard to fold this code into the other visitor, but I think this is clearer.
就是这样，除了一些我将不加评论地显示的帮助程序代码:
That's it, except for some helper code which I'll present without comment:
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脚本可能具有的其他怪异之处，但是我认为这仍然是一个有用的起点.
As I mentioned before, this code is quite brittle. It assumes queries have a particular form, and it could fail (quite badly, by giving misleading results) if they don't. A major open challenge would be to extend it so it can handle scopes and unqualified references correctly, as well as the other weirdness a T-SQL script can feature, but I think it's a useful starting point nevertheless.
这篇关于如何使用scriptdom API提取跨数据库引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
