DbExpressions - 迈向独立的一步

seesharper

4.24/5 (11投票s)

2011年1月26日

CPOL

9分钟阅读

78008

336

SQL 的抽象语法树实现

下载源码 - 89.55 KB

引言

假设我们正在开发一个需要支持多种数据库系统的应用程序。

我们有什么选择呢？

基本上，我会说我们有三个选择

坚持ANSI SQL，并确保所有SQL在所有支持的数据库上运行。
为每个目标数据库保留每个语句的单独版本。
使用ORM映射器，将SQL从代码中抽象出来。

让我们快速回顾一下每个选项。

ANSI SQL

ANSI标准旨在定义一种表达SQL语法的通用方式。
这意味着我们应该能够编写一个查询，并且无论目标DBMS是什么，都能幸福地生活下去。

听起来很棒，但事实真的如此吗？

让我们来测试一下，编写一个查询，返回某个列的string长度。

我们首先使用Microsoft SQL Server

SELECT LEN(ProductName) FROM Products

如果我们使用相同的查询并针对MySql执行它，会怎么样？

“FUNCTION northwind.LEN does not exist”

这并不是我们所期望的结果。出了什么问题呢？

显然MySql不理解LEN函数，但为什么呢？

如果进一步调查，我们会发现LEN的ANSI SQL版本是CHAR_LENGTH。

好的，让我们重写查询并再次执行MySql。

 SELECT CHAR_LENGTH(ProductName) FROM Products

成功！！！

我们现在有一个符合ANSI标准的查询。

所以如果我们再次切换回Microsoft SQL Server，这也应该能工作，对吗？

让我们来查明。

“'CHAR_LENGTH' is not a recognized built-in function name.”

不行，SQL Server不支持此函数，我们又回到了维护此查询的两个版本，一个用于Microsoft SQL Server，一个用于MySql。

我们到处都能看到这种情况。大多数数据库系统都实现了标准定义的功能，但名称不同。

尽管它们可能在一定程度上声称符合ANSI标准，但它们之间的差异之大可能会让你感到惊讶。

为每个DBMS保留单独的查询版本

我不这么认为。:)

使用ORM映射器

大多数对象关系映射器，例如NHibernate和Entity Framework，都支持广泛的数据库系统。

抽象内置在映射器本身中，因此我们无需考虑每个数据库的语义。

过去，每个ORM映射器都提供自己的查询语言，这几乎将你的代码与该特定的映射器（例如NHibernate）绑定在一起。

借助Linq（语言集成查询语言），我们能够从底层提供程序中抽象出来，并通过通用接口（Linq）处理数据。

这些零散的部分开始就位。

唯一的问题是，这种抽象内置在Linq提供程序本身中，不能在其他场景中使用。

如果我们不想使用ORM映射器，但仍希望实现DBMS独立性，该怎么办？

引入DbExpressions

为了解决上述问题，我们的想法是将每个DBMS的实现细节抽象到抽象语法树中，并提供一个流畅的接口来帮助构建此树。

为此库设定了以下要求

支持所有四种查询类型（SELECT、UPDATE、DELETE和INSERT）。

支持最常见的基本string、numeric和datetime函数。

支持最常见的语言结构，例如JOIN、GROUP BY、ORDER BY等。

支持提供程序模型，使为新数据库系统实现新提供程序变得容易。

开箱即用支持Microsoft SQL Server和MySql。

这如何工作？

如果您熟悉.NET中表达式的工作方式，您很快就会理解此库背后的思想。

例如，Expression<T>委托编译成IL，而DbExpression“编译”成特定于供应商的SQL方言。

查询由树中的一组节点表示，当访问（访问者模式）时，每个DbExpression都会转换为预期的语法。

有许多不同的表达式类型，它们可以使用static DbExpressionFactory类。

这意味着我们可以手动构建表达式树，如下所示

var dbExpressionFactory = new DbExpressionFactory(); 
var columnExpression = dbExpressionFactory.Column("SomeColumn"); 
var constantExpression = dbExpressionFactory.Constant(1); 
var binaryExpression = dbExpressionFactory.MakeBinary
    (DbBinaryExpressionType.Equal, columnExpression, constantExpression); 
Console.WriteLine(binaryExpression);

当此DbExpression被翻译（Microsoft SQL）时，输出将是

“([SomeColumn] = @p0)”

虽然手动构建表达式树在某些场景中可能很有用，但如果我们只想编写查询并执行它，这可能会非常麻烦。

该库提供了一个流畅的接口，使我们能够编写如下查询

selectQuery.Select(f => f.Length(f.Column("ProductID"))) _
    .From(f => f.Table("Products"));

Select方法有两个重载，定义如下

DbSelectQuery Select(this DbSelectQuery dbSelectQuery, _
params Func<dbexpressionfactory, dbexpression>[] expressionSelector)

DbSelectQuery Select(this DbSelectQuery dbSelectQuery, DbExpression expression)

DbExpressionFactory的实例被传入Select方法，以便于构造查询。

正如我们所看到的，还有一个只接受DbExpression实例的重载，如果我们结合手动构建查询片段和流畅API，这非常强大。

var factory = new DbExpressionFactory(); 
var projection = factory.List(new[] {factory.Column("SomeColumn"), _
        factory.Column("SomeOtherColumn")}); 
var selectQuery = new DbSelectQuery(); 
selectQuery.Select(projection).From(f => f.Table("SomeTable"));

现在，这看起来比string连接好多了，而且它是DBMS独立的。

分页

分页是选择数据“页”的能力，这意味着在1000行可能的行中，我们可能希望返回第10到第20行。

不同SQL方言的分页SQL语法可能差异很大。

MySql使用LIMIT和OFFSET关键字非常优雅地解决了这个问题，而在SQL Server中这会变得稍微复杂一些。

DbExpressions库提供了Skip和Take方法，类似于我们在Linq中找到的方法。

selectQuery.Select(f => f.Column("ProductID")) 
    .From(f => f.Table("Products")).Skip(10).Take(10);

这意味着我们现在可以编写带有分页的查询，而无需担心所需的实际语法。

将表达式翻译成SQL

将查询翻译成SQL只是访问表达式树中的每个节点，并根据目标DBMS返回适当语法的问题。

你们中的一些人可能还记得Matt Warren首次讨论的ExpressionVisitor类，后来在MSDN文档中提供给我们。

ExpressionVisitor类负责访问每个节点，并在发生变化时重写树。

这个类在将Expression<T>委托转换为另一种表示形式（例如SQL）时是必不可少的。

DbExpressions库有一个类似的类，它在我们需要检查（或重写）表达式树时用作基类。

从DbExpressionVisitor类派生，我们找到了abstract DbQueryTranslator，它用作特定于提供程序的查询翻译器的基类。

如果我们查看MySqlQueryTranslator类，我们会发现以下代码，它将DbColumnExpression翻译成MySql可以理解的内容。

protected override DbExpression VisitColumnExpression(DbColumnExpression columnExpression)
{ 
    var sqlFragment = QuoteIdentifier(columnExpression.ColumnName); 
    return ExpressionFactory.Sql(sqlFragment); 
}

如果您想了解有关如何实现自定义查询转换器的更多信息，请参阅此处。

运算符重载

DbExpressons库使用运算符重载，因此我们可以以更自然的方式编写查询。

所以，我们不是这样写

selectQuery.Select(f => f.Column("ProductID")) 
    .From(f => f.Table("Order Details")).Where_
    (f => f.GreaterThan(f.Column("ProductID"),f.Constant(10)));

我们可以写

selectQuery.Select(f => f.Column("ProductID")) 
    .From(f => f.Table("Order Details")).Where(e => e.Column("ProductID") > 10);

参数化查询

我们都知道SQL注入的危险，我们需要确保每个常量，例如字符串文字或数字，都表示为查询中的参数。

DbExpressions库通过将DbConstantExpression实例转换为数据参数来处理这个问题。

这意味着如果我们写

selectQuery.Select(f => f.Column("ProductID")) 
    .From(f => f.Table("Order Details")).Where(e => e.Column("ProductID") > 10);

我们最终得到以下输出（Microsoft SQL）

SELECT [ProductID] FROM [Order Details] WHERE ([ProductID] > @p0)

查询执行

所以我们有了查询，我们需要执行它。

我们首先需要做的是将语法树“编译”成特定于供应商的SQL语句。

虽然Expression<T>委托有Compile方法，但DbExpression的对应方法称为Translate。

它翻译成什么？它翻译成一个已填充所有参数的IDbCommand实例。

var command = selectQuery.Translate(); 
using (IDbConnection connection = CreateConnection() ) 
{ 
    var reader = command.ExecuteReader(); 
    while(reader.Read()) 
    { 
        //Do stuff 
    } 
}

配置

DbExpressions库的配置非常简单，包括添加新提供程序和指定默认提供程序的功能。

<?xml version="1.0" encoding="utf-8" ?> 
<configuration> 
    <configSections> 
        <section name="dbExpressions" 
	type ="DbExpressions.Configuration.DbExpressionSettings, DbExpressions"/> 
    </configSections> 
    <dbExpressions defaultProvider="System.Data.SqlClient"> 
        <queryTranslators> 
            <queryTranslator providerName="System.Data.SqlClient" 
		type ="SqlQueryTranslator, DbExpressions"></queryTranslator> 
        </queryTranslators> 
    </dbExpressions> 
</configuration>

注意：这只是一个示例配置，不是必需的，因为MySql和Microsoft SQL Server的支持已与DbExpressions库捆绑在一起。

我们还可以使用DbQueryTranslatorFactory类上的方法添加新提供程序或设置默认提供程序。

DbQueryTranslatorFactory.RegisterQueryTranslator
("System.Data.SqlClient", typeof (SqlQueryTranslator)); 

DbQueryTranslatorFactory.SetDefaultProvider("System.Data.SqlClient");

分析表达式树

除了将表达式树翻译成SQL之外，我们还可以在将树翻译成SQL之前检查、分析甚至修改它。

你可能想知道这样的需求何时以及如何出现，但我会给你举一个例子。

如果你想重写表达式树的某些部分，DbExpressionVisitor就是“负责人”。

它负责访问每个节点，并在发生任何更改时重写（子）树。

这需要继承DbExpressionVisitor类并重写相应的Visitxxx方法。

为了使这更容易一些，该库支持对DbExpressions的搜索和替换。

给定以下查询

dbSelectQuery.Select(f => f.Column("SomeColumn")).From(f => f.Table("SomeTable"));

虽然这是一个非常简单的例子，但我们可以使用Find方法找到所有列引用。

var result = expression.Find<DbColumnExpression>(c => c.ColumnName == "SomeColumn");

Find方法搜索具有给定谓词的所有DbColumnExpression实例并返回结果。

另一个特性是能够用一个表达式替换另一个表达式。

在实现MySqlQueryTranslator时，我确实很好地利用了这一点。

作为支持的数学函数之一，我们找到了Square方法，它实际上只返回任何给定数字的平方。

SQL Server已经支持该函数，因此这只是生成调用Square方法的语法的问题。

另一方面，在MySql中，没有函数可以做到这一点。好吧，至少不是直接的。

如果我们取数字3的平方，那就是9，对吧？

我认为如果我们取3并将其提高到2的幂，结果也会是一样的。

幸运的是，MySql中有一个函数可以做到这一点（Power）。

所以，我基本上需要做的是将所有对Square方法的引用替换为对Power函数的调用。

使用Replace方法，这就像在公园里散步一样。

以下代码摘自MySqlQueryTranslator

private DbExpression ReplaceSquareWithPower(DbExpression dbExpression)
{                                    
    return dbExpression.Replace<DbMathematicalFunctionExpression>(
        dme => dme.MathematicalFunctionExpressionType == 
		DbMathematicalFunctionExpressionType.Square,
        te => ExpressionFactory.Power(te.Arguments[0], ExpressionFactory.Constant(2))); 
}

我们在这里做的是，我们正在搜索树，无论多么复杂，以查找表示Square方法的DbMathematicalFunctionExpression实例。

对于每次出现，我们都将该函数调用替换为对Power方法的调用。

MySql

为了将此库与MySql一起使用，您需要安装MySql Connector for .NET。这是MySql的本机.NET数据提供程序。

该库已使用版本6.3.5进行测试

结束语

DbExpressions库托管在CodePlex上，更新和修复将在那里提供。

如果您遇到任何问题或有疑问，请随时在讨论列表或问题跟踪器中发布。

如果您愿意为项目做出贡献，我们将不胜感激，尤其是拥有Oracle知识的人，以便我们也能获得对Oracle的支持。