Inside Microsoft SQL Server 2005: T-SQL Programming （数据类型|第一章） 基于CLR的自定义数据类型

基于CLR的自定义类型

SQL Server 2005中集成了.NET与XML，因此用户可以非常方便地自定义类型。

自定义类型的理论基础

首先需要在这里讨论的是一个饱受争议的话题：在增加.NET与XML集成后，SQL Server是否变成了一个对象数据库管理系统？事实上，正是有了.NET与XML的支持，SQL Server成为了一个更加完整的关系数据库管理系统。

• 域与关系

在面向对象编程里，一个最重要的思想是一个类利用它的模板去初始化对象。那么在关系世界里，什么是最重要的思想呢？

在关系世界中，域定义了包含的对象，关系定义了命题。利用域与关系，你能够描述整个子系统，可以说用域与关系表述现实世界是必须的以及足够的。

实际上，关系数据库是基于数据集理论来建立的，一个关系就是一个数据集，一个数据集有多个成员，其中一个可以作为主键。

• 关系与类

开发人员通常认为关系对于数据库的意义就如同类对于面向对象编程的意义。但是，这种观点本身是错误的，我们可以尝试着换一个角度来思考这个问题。

首先，关系是一个数据集，刚插入、更改或删除时，实际上是用一个数据集替换了原有的数据集。

其次，在编程中，变量是一个相对于时间与空间的常量，变量的改变可以看作时间与空间上用一个常量值替换了另一个常量值。

从上面两段话中，我们可以看出一点相似之处。唯一的不同之处在于，变量可能会变得非常快，并且在内存之中只会储存很短的一段时间；而数据集在数据库中可能会储存很长一段时间。但是实际上时间的长短是相对的，内存与硬盘存储的界限也是相对的。从这层推断上来看，关系并不能等同于类，而应该等同于变量。

（另一种简单的逻辑也可以验证上述观点，假设关系等同于类，由于类可以包含子类，那么关系也应当可以包含子关系，但是实际上这是不能被数据库所接受的）

• 域与类

根据Fabian Pascal (Practical Issues in Database Management [Addison-Wesley Professional, 2000])，域包含如下内容：

1. 一个名称
2. 一个或多个有名称的表述方法
3. 类型限制
4. 一组运算符

总得来说，域是一种数据内部复杂的、由运算符集决定的数据类型。而类也可以按照这样的定义类定义。可以看出，域和类是等同的。

• 复杂域

首先，我们如何去判断一个域是否复杂，这取决于我们对这个域是否熟悉。例如，对于integer这个域，我们知道其中的运算符以及使用方法，我们固然认为这是一个简单的域。而对于DateTime这样的域，我们刚开始可以对它的运算符以及使用方法都不是很了解，我们固然认为它很复杂。

对于一个复杂域，为什么我们不能用一个独有的表述形式与运算方法去构造它呢？因为对于每个不同的人，每种不同的需要，对于同一个域的理解都可能会有所不同。因此我们需要自定义的去创建复杂域。

• 创建自定义类型的语言

SQL Server使用了.NET语言来创建自定义类型而不是采用了SQL语言。

使用一个数据类型需要实现许多个细微的方法。而诸如T-SQL这种非过程性语言包含了太多命令块，因此需要特别主要控制与限制方法的输入。正则表达式可以非常好地完成这个任务。

创建自定义类型

我们可以以cn = a +bi为例来学习创建自定义类型

where i = square root of -1(i.e. i * i = -1).

Basic arithmetic operations are defined as:

1. Addition: (a + bi)+ (c + di)= (a + c)+ (b + d)i

2. Subtraction: (a + bi) - (c + di)= (a - c)+ (b - d)i

3. Multiplication: (a + bi) × (c + di)= (ac - bd)+ (bc + ad)i

4. Division: (a + bi)/(c + di)= ((ac + bd)+ (bc - ad)i)/(c²+ d²)

   
   

• 自定义类型的要求

在支持运算符之前，一个自定义类型需要首先支持输入与表述，即微软要求每个自定类型都必须有ToString和Parse函数。

同时，所有数据类型都必须支持未知的值。你可能用自定义类型的一个状态来表示这个未知的值，也可以使用一个标志。当你创建一个自定义类型时，你可以实现System.Data.SqlTypes.INullable接口并创建一个共有静态只读的Null属性。

SQL Server能够识别类的继承，并且可以调用子类从基类继承的函数，但是SQL Server不能够直接地调用基类的函数。相反，在.NET应用程序中，基类的函数是可以被调用的，所以你希望在客户端能够调用一些额外的功能而服务器端不能使用的情况下，可以考虑使用类的继承。

你需要定义如何序列化你的自定义类型。形式分为Native与UserDefined。Native表示会将所有结构列出来。顺序表示先定义的变量会首先被列出来；正常化表示所有标量会被转换成字符串。

Native形式很方便，但是却有一个坏处，即你必须使用.NET的值类型来创建你的自定义类型。唯一有问题的是当你使用String类型时，由于String是一个引用，你必须使用自定义的序列化来处理它。同时，自定义类型需要支持XML。

C#以及VB的自定义类型可以在SQL Server 2005的范例中找到：C:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\UserDefinedDataType

更多的要求请见：http://msdn.microsoft.com/zh-cn/library/ms131082.aspx

• 创建自定义类型

Visual Studio .NET 2005有创建SQL Server 2005 CLR对象的模板。假设我们使用Visual Studio 2005 Professional Edition或更高版本，我们需要首先新建一个Project，选择Visual C# Project -> Database Subtype -> SQL Server Project Template。

第一部分是namespaces的声明：（引用中包含了正则表达式的namespaces。这个类型使用了Native形式，并且采用了INULLable接口）

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using System.Globalization;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,IsByteOrdered = true)]

public struct ComplexNumberCS : INullable

{

第二部分是变量的声明：

//Regular expression used to parse values of the form (a,bi) 

 private static readonly Regex _parser = new Regex(@"\A\(\s*(?<real>\-?\d+(\.\d+)?)\s*,\s*(?<img>\-?\d+ (\.\d+)?)\s*i\s*\)\Z", RegexOptions.Compiled | RegexOptions.ExplicitCapture); 

 // Real and imaginary parts 

 private double _real; 

 private double _imaginary; 

 // Internal member to show whether the value is null 

 private bool _isnull; 

 // Null value returned equal for all instances 

 private const string NULL = "<<null complex>>"; 

 private static readonly ComplexNumberCS NULL_INSTANCE = new ComplexNumberCS(true);

第三部分有两个构造函数：

// Constructor for a known value 

 public ComplexNumberCS(double real, double imaginary) 

 {

this._real = real; 

this._imaginary = imaginary; 

 this._isnull = false;

} 

 // Constructor for an unknown value 

private ComplexNumberCS(bool isnull)

 { 

 this._isnull = isnull; 

 this._real = this._imaginary = 0; 

 }

第四部分是ToString函数：（正如之前提到的，ToString是每个自定义类型（UDT）必须具备的）

// Default string representation 

 public override string ToString() 

 { 

 return this._isnull ? NULL : ("(" + this._real.ToString(CultureInfo.InvariantCulture) + "," + this._imaginary.ToString(CultureInfo.InvariantCulture) + "i)"); 

 }

第五部分是只读的函数，用于获取该类型的空值：

// Null handling 

 public bool IsNull { 

 get 

 { 

 return this._isnull; 

 } 

} 

 public static ComplexNumberCS Null 

 { 

 get 

 { 

 return NULL_INSTANCE; 

 } 

 }

第六部分是Parse函数：

// Parsing input using regular expression 

 public static ComplexNumberCS Parse(SqlString sqlString) 

 { 

string value = sqlString.ToString(); 

if (sqlString.IsNull || value == NULL) 

 return new ComplexNumberCS(true); 

 // Check whether the input value matches the regex pattern 

 Match m = _parser.Match(value); 

 // If the input's format is incorrect, throw an exception 

 if (!m.Success) 

throw new ArgumentException( "Invalid format for complex number. " + "Format is (n, mi) where n and m are floating " + "point numbers in normal (not scientific) format " + "(nnnnnn.nn)."); 

 // If everything is OK, parse the value; 

 // we will get two double type values 

 return new ComplexNumberCS(double.Parse(m.Groups[1].Value, CultureInfo.InvariantCulture), double.Parse(m.Groups[2].Value, CultureInfo.InvariantCulture)); 

 }

第七部分实现了基本的运算：

// Properties to deal with real and imaginary parts separately 

 public double Real 

 { 

get { if (this._isnull) 

 throw new InvalidOperationException(); 

return this._real; 

 } 

set 

 { 

 this._real = value; 

 } 

 } 

 public double Imaginary 

 { 

 get 

 { 

 if (this._isnull)

 throw new InvalidOperationException(); 

 return this._imaginary; 

 } 

 set 

 { 

 this._imaginary = value; 

 } 

 }

第八部分定义了算术运算：

// Region with arithmetic operations 

 #region arithmetic operations 

 // Addition 

 public ComplexNumberCS AddCN(ComplexNumberCS c) 

 { 

 // null checking 

 if (this._isnull || c._isnull) 

 return new ComplexNumberCS(true); 

 // addition 

 return new ComplexNumberCS(this.Real + c.Real, this.Imaginary + c.Imaginary); 

 } 

 // Subtraction public ComplexNumberCS SubCN(ComplexNumberCS c) 

 { 

 // null checking 

 if (this._isnull || c._isnull) 

 return new ComplexNumberCS(true); 

 // subtraction 

 return new ComplexNumberCS(this.Real - c.Real, this.Imaginary - c.Imaginary); 

 } 

 // Multiplication 

 public ComplexNumberCS MulCN(ComplexNumberCS c) 

 {

 // null checking

 if (this._isnull || c._isnull) 

 return new ComplexNumberCS(true); 

 // multiplication 

 return new ComplexNumberCS(this.Real * c.Real - this.Imaginary * c.Imaginary, this.Imaginary * c.Real + this.Real * c.Imaginary); 

 }

 // Division public ComplexNumberCS DivCN(ComplexNumberCS c) 

 {

 // null checking

 if (this._isnull || c._isnull) 

 return new ComplexNumberCS(true); 

 // division return new ComplexNumberCS( (this.Real * c.Real + this.Imaginary * c.Imaginary) / (c.Real * c.Real + c.Imaginary * c.Imaginary), (this.Imaginary * c.Real - this.Real * c.Imaginary) / (c.Real * c.Real + c.Imaginary * c.Imaginary) ); 

 } 

 #endregion

}

• 利用T-SQL部署自定义类型

首先可以利用T-SQL语言来打开CLR功能，默认关闭：

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

接着利用T-SQL语言来导入Assembly：（PERMISSION_SET用来空值Assembly的权限）

CREATE ASSEMBLY ComplexNumberCS

FROM ' \ComplexNumberCS\ComplexNumberCS\bin\Debug\ComplexNumberCS.dll'

WITH PERMISSION_SET = SAFE;

现在你就可以在SQL Server中使用这个数据类型了，例如：

CREATE TABLE dbo.CNUsage

( 

 id INT IDENTITY(1,1) NOT NULL, 

 cn ComplexNumberCS NULL

);

但是需要注意的是，当你利用SELECT语句查询的时候，SQL Server并不能正确的翻译自定义类型。一个可能的查询结果如下：

IDCN10xC000000000000000C0080000000000000020xBFF0000000000000C01C00000000000000

但是如果你调用ToString函数的话，SQL Server就能够正常工作：

SELECT id, cn.ToString() AS cn

FROM dbo. CNUsage;

IDCN1

(2,3i)

2

(1,7i)

同时的，我们还可以利用C#来写出一个聚合函数，例如：

C# ComplexNumberCS_SUM UDA
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct ComplexNumberCS_SUM
{
    ComplexNumberCS cn;

    public void Init()
    {
        cn = ComplexNumberCS.Parse("(0, 0i)");
    }

    public void Accumulate(ComplexNumberCS Value)
    {
        cn = cn.AddCN(Value);
    }

    public void Merge(ComplexNumberCS_SUM Group)
    {
        Accumulate(Group.Terminate());
    }

    public ComplexNumberCS Terminate()
    {
        return cn;
    }
}

接着可以将聚合函数加入SQL Server之中：

-- Alter assembly to add the ComplexNumberCS_SUM UDA

ALTER ASSEMBLY ComplexNumberCS

FROM 'C:\ComplexNumberCS\ComplexNumberCS\bin\Debug\ComplexNumberCS.dll';

GO

-- Create the aggregate function

CREATE AGGREGATE dbo.ComplexNumberCS_SUM(@input ComplexNumberCS)

RETURNS ComplexNumberCS

EXTERNAL NAME ComplexNumberCS.[ComplexNumberCS_SUM];

最后我们将可以在SQL Server中使用该聚合函数：

SELECT dbo.ComplexNumberCS_SUM(cn).ToString() AS ComplexSum

FROM CNUsage

WHERE cn IS NOT NULL;