【SQL】CLR聚合函数什么鬼

【SQL】CLR聚合函数什么鬼

之前写过一个合并字符串的CLR聚合函数，基本是照抄MS的示例，外加了一些处理，已经投入使用很长时间，没什么问题也就没怎么研究，近日想改造一下，遇到一些问题，遂捣鼓一番，有些心得，记录如下。

一、杂项

• CLR聚合函数在SQL中虽然是函数，但在C#中实际上是以一个类或结构的形式存在的（是的，实现聚合函数的实体可以是结构，也可以是类），这点有别于CLR普通函数，后者是类中的一个方法，鉴于此，下文把实现聚合函数的类或结构统称为聚合类，以免读者思维混淆
• 聚合类必须用特性[SqlUserDefinedAggregate]进行描述。MS示例中还用了[Serializable]特性，经测这个特性不是必须的
• 聚合类在执行时总是会被序列化和反序列化，这就要求聚合类要满足可序列化，详情在下面有说
• SqlUserDefinedAggregate特性的IsInvariantToNulls、IsInvariantToDuplicates、IsInvariantToOrder、IsNullIfEmpty这几个属性是给SQL引擎的查询处理器使用的，大概是用作优化执行计划的依据之类，跟聚合类的实现没有关系。什么意思，比如IsInvariantToNulls=true，是告诉SQL查询处理器，我这个聚合函数不管你字段中有没有null（指SQL中的null，下同），返回的结果都是一致的，那么查询处理器可能就会根据这个来确定是否复用已存在的执行计划；它并不是说，会自动帮你过滤掉传入Accumulate方法的null值。换句话，设置IsInvariantToNulls=true后，Accumulate中该进来null的照样会进来，你该处理的还得处理。同样的，其它几个属性也是如此，它们不会帮你负责任何事，该你操的心还得操。所以既然如此，那么你的实现最好与上述属性描述的一致，不然欺骗了查询处理器，估计有好果子给你吃。就好比你跟MM说请她吃6块钱的麻辣烫，实际上你只买了1块钱的，后果自己脑补

二、执行顺序

经打断点调试，聚合类是按如下顺序执行：

1. 进入Init方法。这是聚合开始后的第一步，试过给类加无参构造函数，但没进去，这里聚合类就已经在暗自向你警告，不要把它当一般class看待。至于为什么，不知道，望高人解答
2. 进入Accumulate方法（这一步不是必然发生的，稍后说明）。该方法的参数就是最后部署到SQL中后，调用聚合时可传入的参数（SQL 2005只支持1个参数），相当于面向SQL的一个入口，要聚合的元素会逐个逐个被扔进来，进来一个执行一次，完了再进来一个再执行一次，所以该方法会根据要聚合的元素个数循环执行多次，当然，如果元素为0个，就不会执行该方法，会直接跳到下一步，这就是为什么说这一步不是必然发生的。需要说明的是，聚合函数的工作是以分组为一个周期，就是GROUP BY出来有几组，聚合函数就会调用几次，这里说的是每一次中的执行顺序，所以Accumulate方法的循环次数是单组的行数，并不是所有组的行数。举个栗子，GROUP BY出来2组，第1组有2行，第2组有3行，那么整个聚合函数会被调用2次，第一次中Accumulate会循环2次，完了进行下一步，整个周期完成后，再开始聚合下一个组，显然，第二轮中Accumulate会循环3次
3. 进行序列化。干毛要序列化，我也想知道，我只知道这步之后，聚合类的所有字段的值都会清空（准确说是重置为类型默认值），所以如果不在序列化时抓住机会赶紧保存数据的话，将会使之前在Init和Accumulate中做的工作全部泡汤，因为在下一步的反序列化过程中你将得不到任何数据，进而导致在最终的Terminate方法中将无数据可返回！所以序列化这一步一定要着重理解。这一步只有在自己实现序列化的情况下才看得出来发生过。简单说聚合类的序列化行为分为两种，由SqlUserDefinedAggregate特性的Format属性指定，该属性（是个枚举类型）共有3个值：Unknown、Native、UserDefined，其中Unknown是作为一个缺省值存在，类似其它枚举中的None之类，代表尚未设置，在使用SqlUserDefinedAggregate特性时，Format必须指定为Native或UserDefined，如果是Unknown，则会抛异常。所以聚合类的序列化行为就只能是Native、UserDefined两种：

• Native。代表聚合类交给CLR去序列化和反序列化，不需要自己实现，看起来很美，但是Native方式有些前提，就是聚合类只能存在值类型的成员，不能有引用类型的成员，包括string，并且如果聚合类是class而不是struct，那必须用[StructLayout(LayoutKind.Sequential)]特性进行标记，如果不满足上述条件，部署必败。所以有时候你不能偷懒，必须用UserDefined方式
• UserDefined。意味着必须自己实现序列化和反序列化行为，具体是通过让聚合类实现IBinarySerialize接口进行，正如MS示例那样。该接口有两个方法，public void Write(BinaryWriter w)和public void Read(BinaryReader r)，分别代表序列化和反序列化过程。终于说到重点了，在聚合类进行到序列化这一步时，你要负责把你想保存的所有数据都写入到w（一个BinaryWriter实例）的基础流中，具体可通过w的Write方法进行，也可以直接访问w.BaseStream操作基础流，或者像new BinaryFormatter().Serialize(w.BaseStream, obj)这样，把整个对象用BinaryFormatter写到基础流中，总之方法多样，属于流的知识范畴，本文不赘述，反正最终目的就是把数据写进w.BaseStream，保险起见，写完以后可以w.Flush()一下。这里就要说到第二个重点了，事关可写入的数据量的问题，SqlUserDefinedAggregate特性有个MaxByteSize属性，当采用UserDefined方式时，必须指定该属性，表示在序列化时最多可以写进多少字节的数据。不指定就是0，就是什么数据也保存不了~玩毛。MaxByteSize可以设置的最大值是由SqlUserDefinedAggregateAttribute.MaxByteSizeValue常量决定的，而这个常量.net2.0-3.5都是8000，后续版本不知道有没有变动。也就是说，序列化时，最多可以写入8000字节的数据，可以保存4000汉字？哎哟不错哦~NONONO，据我调试，w的编码方式是UTF8（不确定跟环境有没有关系，因为w是CLR负责传入的，什么情况下传入什么编码的w，无从考究。如果是固定传UTF8，那只能说有点坑非ASCII区的人民了），且不可更改，也就是1个汉字可能占据3~4个字节，按3字节也就2600个汉字左右，应当说很不富裕，只能求神拜佛应用中触碰不到这个极限。所以我的意见，一定要省着用这个容量，只保存必要的数据，不要图省事把整个整个的对象序列化进去。比如MS的示例就只把StringBuilder中的string塞进去，而没有把整个StringBuilder对象塞进去

进行反序列化。上回说道，把想保存的数据序列化，到了这一步，自然就是把数据取出来。同样，可以通过r（一个 BinaryReader)的各种ReadXXX方法取，也可以访问r.BaseStream操作基础流取出数据。这里头脑要保持清醒，就是取出数据以后是为了在Terminate方法中处理并返回结果，而不是非得把成员对象还原了，然后再去Terminate中操作对象。什么意思，还拿MS的示例说事，不过这次它是反面教材，在Read中得到之前保存的 string以后，没必要还原成 StringBuilder，完全可以用一个 string字段去接住，然后在Terminate处理该字段并返回就好了 进入Terminate方法。上面说过Accumulate是面向SQL的入口，而Terminate就是出口了，聚合计算的结果就是通过Terminate返回给调用者，所以该方法的返回 类型就是在SQL中得到的 类型。通过上文，知道在Accumulate和Terminate之间，隔了一个序列化与反序列化的环节，并且要知道在序列化后， 类字段的值已经被清空过了，已经不是当初那个 类字段了（除非在反序列化时你把它们还原了）。清楚这一点，你就应该知道像这样的看起来天经地义的做法：

public void Accumulate(SqlString str)
{
    s = str;
}

public SqlString Terminate()
{
    return new SqlString(s);
}

在聚合类中是极大的错误，除非s在序列化时得到保存并在反序列化时进行还原。

开始下一组的Init→Accumulate→序列化/反序列化→Terminate。当然如果没有下一组，整个结束

完了？不是还有个Merge方法么，很抱歉，我也不知道这货什么时候才会用到。在我多次调试中，始终没遇到执行Merge的情况。根据MSDN文档所述，我的猜测是，CLR并不保证在一次聚合中都使用同一个聚合类实例，它随时有可能另开一个实例来工作，并利用新开实例的Merge方法将旧实例的数据并入新实例中，完了释放旧实例。不知道这个猜测对不对，撸过高手若清楚，还望指教，谢过先。如果这个猜测没错的话，显然Merge方法要做的就是把旧实例（other）的数据并入当前实例，具体应该怎么写读者应该已心中有数了。要注意的是，如果聚合类是设计为只处理非重复元素的话，那么可以保证在每个实例中存储的元素都是唯一的，但两个实例中的元素却有可能存在相同，在实现Merge时要留意这一点，要确保并入后的数据仍然是唯一的。

三、最后

目前在我看来，聚合类它虽然在C#中是个类/结构，但处处透着古怪，比如没有执行构造函数，运行期间又要清空类字段并转而采用序列化和反序列化的方式传递状态，使它又不那么像一个正常的类，所以我建议在完全弄清楚它之前，不要使用一些OOP的手法去实现它，比如继承重写什么的，想都不要想，老老实实填空就好。另外，对于文中提出的疑惑，希望得到高手指教，再次谢过。

最后附上一枚改造好的字符串聚合（忽略null、空白、重复字串、移除首尾空白）：

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;

namespace AhDung.SqlClr
{
    [SqlUserDefinedAggregate(
        Format.UserDefined,
        IsInvariantToNulls = true,
        IsInvariantToDuplicates = true,
        IsInvariantToOrder = true,
        MaxByteSize = SqlUserDefinedAggregateAttribute.MaxByteSizeValue)
    ]
    public class JoinString : IBinarySerialize
    {
        string sptr, result;
        Dictionary<string, object> dic;

        public void Init()
        {
            sptr = string.Empty;
            dic = new Dictionary<string, object>(StringComparer.CurrentCultureIgnoreCase);//忽略大小写
        }

        public void Accumulate(SqlString str, SqlString separater)
        {
            if (sptr.Length == 0 && !separater.IsNull && !string.IsNullOrEmpty(separater.Value)) { sptr = separater.Value; }
            string s;
            if (str.IsNull || (s = str.Value.Trim()).Length == 0 || dic.ContainsKey(s)) { return; }
            dic.Add(s, null);
        }

        public void Merge(JoinString other)
        {
            foreach (string s in other.dic.Keys)
            {
                if (dic.ContainsKey(s)) { continue; }
                dic.Add(s, null);
            }
        }

        public SqlString Terminate()
        {
            return new SqlString(result);
        }

        public void Read(BinaryReader r)
        {
            result = r.ReadString();
        }

        public void Write(BinaryWriter w)
        {
            string[] ss = new string[dic.Count];
            dic.Keys.CopyTo(ss, 0);
            w.Write(string.Join(sptr, ss));
        }
    }
}

- 完 -

posted on 2015-03-30 09:59 ahdung 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/ahdung/p/4377236.html