Страницы

пятница, 25 декабря 2015 г.

Реализация объектно-ориентированных типов данных

В Transact-SQL можно реализовать разнообразные пользовательские типы данных, основанные на clr-сборках. У этих типов могут быть свои полноценные методы и свойства (в том числе статические). Рассмотрим пример реализации списка. Потребуется реализовать структуру, реализующую интерфейс INullable. Также требуется обеспечить собственную сериализацию, поскольку работать придется со ссылочным типом, списком. Для этого потребуется реализовать интерфейс IBinarySerializer, с методами Read и Write. Обязательно также реализовать методы ToString, Parse, Null, свойство IsNull:

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined, MaxByteSize=-1)]
public struct SqlList : INullable, IBinarySerialize
{
    private bool m_Null;
    private List<string> m_lst;

    public override string ToString()
    {
        if (this.IsNull)
        {
            return "null";
        }
        return string.Join(",", m_lst);
    }

    public bool IsNull
    {
        get
        {
            return m_Null;
        }
    }

    public static SqlList Null
    {
        get
        {
            SqlList lst = new SqlList();
            lst.m_Null = true;
            return lst;
        }
    }

    public static SqlList Parse(SqlString lst)
    {
        if (lst.IsNull)
            return Null;
        SqlList _lst = new SqlList();
        char[] _delim = {','};
        _lst.m_lst = lst.ToString().Split(_delim).ToList();
       
        return _lst;
    }

Добавим также статический метод для получения списка из строки с произвольным разделителем, и методы для добавления и удаления элементов в список, очистки и сортировки списка:

    public static SqlList ParseDelim(SqlString lst, SqlString delim)
    {
        if (lst.IsNull)
            return Null;
        SqlList _lst = new SqlList();
        char[] _delim = delim.ToString().ToCharArray();
        _lst.m_lst = lst.ToString().Split(_delim).ToList();

        return _lst;
    }

    public SqlList AddMember(SqlString vcNewMember)
    {
        SqlList _lst = new SqlList();
        _lst.m_lst = this.m_lst;
        _lst.m_lst.Add(vcNewMember.ToString());
        return _lst;
    }

    public SqlList RemoveMember(SqlString vcMember)
    {
        SqlList _lst = new SqlList();
        _lst.m_lst = this.m_lst;
        _lst.m_lst.Remove(vcMember.ToString());
        return _lst;
    }

    public SqlList Clear()
    {
        SqlList _lst = new SqlList();
        _lst.m_lst = this.m_lst;
        _lst.m_lst.Clear();
        return _lst;
    }

    public SqlList Sort()
    {
        SqlList _lst = new SqlList();
        _lst.m_lst = this.m_lst;
        _lst.m_lst.Sort();
        return _lst;
    }

Для удобства добавим метод для извлечения определенного члена списка по номеру, метод для проверки равенства двух экземпляров и свойство для определения числа элементов:

    public SqlString GetItem(SqlInt32 iItem)
    {
        return (SqlString)this.m_lst.ElementAt((int)iItem);
    }

    public SqlBoolean Equal(SqlList lst)
    {
        if (this.m_lst == null || lst.m_lst == null)
        {
            return false;
        }
        if(this.m_lst.Count != lst.m_lst.Count)
        {
            return false;
        }
        for (int i = 0; i < this.m_lst.Count - 1; i++)
        {
            if (this.m_lst.ElementAt(i) != lst.m_lst.ElementAt(i))
            {
                return false;
            }
        }
        return true;
    }

    public SqlInt32 GetCount
    {
        get
        {
            return this.m_lst.Count;
        }
    }

Остается реализовать методы Read, Write;

    public void Read(BinaryReader r)
    {
        if (r == null) throw new ArgumentNullException("r");
        var count = r.ReadInt32();
        m_lst = new List<string>(count);
        for (int i = 0; i < count; i++)
        {
            m_lst.Add(r.ReadString());
        }
    }

    public void Write(BinaryWriter w)
    {
        if (w == null) throw new ArgumentNullException("w");
        w.Write(m_lst.Count);
        foreach (string b in m_lst)
        {
            w.Write(b);
        }
    }
}

Теперь можно перейти к развертыванию сборки и тестированию типа:

if not exists
(
       select *
       from sys.assemblies
       where name = N'Lists'
)
begin
       create assembly Lists
       from 'C:\ListLib.dll'
end
else if
(
       select clr.binFile
       from openrowset ( bulk 'C:\ListLib.dll', single_blob ) clr ( binFile )
)
<> 
(
       select asmfile.content
       from
             sys.assemblies asm
                    inner join
             sys.assembly_files asmfile on asm.assembly_id = asmfile.assembly_id
       where asm.name = N'Lists'
)
begin
       alter assembly Lists
       from 'C:\ListLib.dll'
end
go

if type_id ( N'dbo.List' ) is null
begin
       create type List
       external name Lists.SqlList
end
go

Проверим работу методов, создадим экземпляры структуры с помощью метода ParseDelim, вставим в список несколько строковых элементов, отсортируем их и извлечем:

declare @lst dbo.List, @lst1 dbo.List, @lst2 dbo.List
set @lst = dbo.List::ParseDelim(N'd,c,b', ',')
set @lst = @lst.AddMember(N'a')
select @lst.GetItem(0), @lst.GetItem(1), @lst.GetItem(2), @lst.GetItem(3)
set @lst = @lst.Sort()
select @lst.GetItem(0), @lst.GetItem(1), @lst.GetItem(2), @lst.GetItem(3)

select @lst.GetCount














Можно наполнить второй список и проверить работу метода Equals:

set @lst1 = dbo.List::ParseDelim('a,b,c', ',')
set @lst1 = @lst1.AddMember('d')
select @lst.Equal(@lst1)

Автор: на Комментариев нет:
Ярлыки:

воскресенье, 22 ноября 2015 г.

CLR-агрегирующие функции

Рассмотрим задачу, в которой для таблицы с группами строк требуется найти максимальное по модулю значение вместе со знаком найденного элемента.
Пример:

if object_id ( N'tempdb..#data', N'U' ) is not null
begin
       drop table #data
end
go

create table #data
(
       iGrpId int    not null,
       iRowId int    not null,
       iAttr  float  not null,
       primary key clustered ( iGrpId asc, iRowId asc ) on [primary]
) on [primary]
go

insert into #data
(
       iGrpId,
       iRowId,
       iAttr
)
values
       ( 1, 1, 20 ),
       ( 1, 2, -20 ),
       ( 1, 3, 30 ),
       ( 2, 1, 10 ),
       ( 2, 2, 20 ),
       ( 2, 3, -10 ),
       ( 2, 4, -30 )
go

Требуется написать запрос для получения таких результатов:









Чтобы получить такой результат максимально удобным способом, используя один запрос, создадим clr-агрегирующую функцию. Ниже представлен ее код.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute
(
    Microsoft.SqlServer.Server.Format.Native,
    IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false
)]
public struct AbsOperations
{
    private SqlDouble dMaxVal;
    private bool bNullExists;
    private bool bAllNull;

    public void Init()
    {
        dMaxVal = 0;
        bNullExists = false;
        bAllNull = false;
    }

    public void Accumulate(SqlDouble Value)
    {
        if (Value.IsNull)
        {
            bNullExists = true;
        }
        else
        {
            double dMaxVal_ = double.Parse(dMaxVal.ToString()), Value_ = double.Parse(Value.ToString());
            bNullExists = false;
            if(Math.Abs(dMaxVal_) < Math.Abs(Value_))
            {
                dMaxVal = Value;
            }
        }
    }

    public void Merge(AbsOperations grp)
    {
        if (grp.dMaxVal.IsNull)
        {
            if (grp.bNullExists)
            {
                bNullExists = true;
            }
            if (!grp.bAllNull)
            {
                bAllNull = false;
            }

            double dMaxVal_ = double.Parse(dMaxVal.ToString()), Value = double.Parse(grp.dMaxVal.ToString());
            if (Math.Abs(dMaxVal_) < Math.Abs(Value))
            {
                dMaxVal = grp.dMaxVal;
            }
        }
    }

    public SqlDouble Terminate()
    {
        if (bAllNull)
        {
            return SqlDouble.Null;
        }
        return dMaxVal;
    }
}

В структуре имеется ряд методов. Методы Init и Terminate отвечают за первоначальную инициализацию и возврат значения для группы соответственно. Логика инкапсулирована в методе Accumulate. В случае если вычисления в группе распараллеливаются на несколько потоков, их результаты надо в конце собрать в одно значение. Этой цели служит метод Merge. Развернем сборку и создадим пользовательскую агрегирующую функцию:

if not exists
(
       select *
       from sys.assemblies
       where name = N'AbsOperations'
)
begin
       create assembly AbsOperations
             from 'C:\В\Программирование\Проекты\tmp\SqlServerProject4\bin\Debug\SqlServerProject4.dll'
             with permission_set = safe
end
go

if object_id ( N'dbo.GetMaxAbs', N'AF' ) is null
begin
       create aggregate dbo.GetMaxAbs ( @fRow float )
       returns float
       external name AbsOperations.AbsOperations
end
go

Теперь можно написать запрос для решения задачи;

select iGrpId, AgrgrAttr = dbo.GetMaxAbs ( iAttr )
from #data
group by iGrpId

Метод по созданию собственных агрегирующих функций необычайно полезен. Он дает возможность реализовать самые разнообразные вычисления в группе: конкатенацию строк, произведения чисел, битовые операции. Можно добавить логику для вычленения отдельных членов в группе и многое другое.
Автор: на Комментариев нет:
Ярлыки:
Подписаться на: Сообщения (Atom)