Использование CLR-функцийв Microsoft SQL Serverна примере сжатия данных

Начиная с версии 2005, Microsoft SQL Server позволяетразрабатывать хранимые процедуры и функции не только на T-SQL, но и на разных языках платформы .NET. В этой статье мы рассмотримсоздание функций для сжатия данных в MS SQL.

Данный подход я использовал в своей системе поиска плагиата “The Crot” (http://siberiasoft.info), чтобы немного обойтиограничение Express Edition на размер базы данных (4 GB).

Примечание

В MS SQL 2008 появились возможности сжатия данных и резервныхкопий, возможно вам подойдет и этот вариант. Подробности как всегда в BOL – http://msdn.microsoft.com/en-us/library/cc280449.aspx.

Для сжатия данных в этом примере мы будем использоватьбесплатную open source библиотеку SharpZipLib, которую можно загрузить по адресу – http://www.icsharpcode.net/OpenSource/SharpZipLib/.

Итак, начнем. Запустите Visual Studio и создайте новый проект Class Library:

Далее, переименуйте созданный по умолчанию класс Class1 в CompressionFunctions, и пометьте егомодификатором static.

using System;

using System.Collections.Generic;

using System.Text;

namespace CLRFunctions

{

public static class CompressionFunctions

{

}

Распакуйте библиотеку SharpZipLib, добавьте еев решение (File->Add->Existing Project…), и добавьте ссылку на этот проект в проект CLRFunctions (Project->Add reference…, вкладка Projects).

Теперь создадим в классе Compression методы для упаковки ираспаковки данных. Эти методы будут соответствовать скалярным функциям MS SQL. Для того чтобыпревратить обычный метод в CLR-функцию,надо всего лишь пометить его атрибутом SqlFunctionAttribute. Этот атрибут находится в пространстве имен Microsoft.SqlServer.Server, так что для удобства добавьтеего в список using.

Рассмотрим метод для упаковки данных. Этот метод будетпринимать на вход произвольные двоичные данные, упаковывать их, и возвращатьопять же двоичные данные. На сервере эти данные будут храниться в столбце типа varbinary(max), который в .NET представлентипом SqlBytes (которыйнаходится в пространстве имен System.Data.SqlTypes, так что добавьте и его всписок using). Полнуютаблицу соответствия типов MS SQL типам .NET CLR можно найти в MSDN в статье SQL Server Data Types andTheir .NET Framework Equivalents.

Итак, метод будет выглядеть следующим образом:

[SqlFunctionAttribute()]

public static SqlBinary ZipData(SqlBinarydata)

{

}

Теперь само сжатие. SharpZipLib содержит класс DeflaterOutputStream, которыйумеет упаковывать потоки:

[SqlFunctionAttribute()]

public static SqlBinary ZipData(SqlBinarydata)

{

//Создаем поток впамяти,

//куда положимданные из параметра data

MemoryStreamcompress = new MemoryStream();

//Собственнообъект, который инкапсулирует алгоритм сжатия,

//конструкторпринимает два параметра - уровень сжатия,

//и добавлять илинет заголовок zip-архива,

//true - недобавлять, нам он не нужен

Deflater deflater =new Deflater(Deflater.BEST_COMPRESSION, true);

//Создаем сжимающий поток

DeflaterOutputStream zos = new DeflaterOutputStream(compress,deflater);

//Записываемв сжимающий поток данные из параметра data

zos.Write(data.Value,0, data.Value.Length);

zos.Close();

//Преобразуемсжатый поток в массив байт и возвращаем как SqlBinary

return new SqlBinary(compress.ToArray());

}

Распаковка также достаточно просто – для этого нампотребуется класс InflaterInputStream:

[SqlFunction()]

public static SqlBinary UnZipData(SqlBinarydata)

{

//Создаем поток впамяти, содержащий упакованные данные

//на основании параметра data

MemoryStream uncompressed = new MemoryStream(data.Value);

//Объект,инкапсулирующий алгоритм распаковки,

//заголовка у наснет, так передаем true в конструктор

Inflater inflater =new Inflater(true);

//Создаем распаковывающий поток

InflaterInputStream zis = newInflaterInputStream(uncompressed, inflater);

//Промежуточныйбуфер для копирования данных из распаковывающего потока

//врезультирующий поток, содержаший распакованные данные

byte[] buff = newbyte[4096];

MemoryStream result = newMemoryStream();

//Копируемданные в результирующий поток

StreamUtils.Copy(zis,result, buff);

//Преобразуемраспакованный поток в массив байт и возвращаем как SqlBinary

return new SqlBinary(result.ToArray());

}

На этом этапе откомпилируйте проект, и давайте посмотрим,как зарегистрировать и использовать эти функции в MS SQL. Откройте Microsoft SQL Server Management Studio исоздайте новую БД для экспериментов.

Чтобы можно было использовать приведенные выше функции вбазе данных, сначала необходимо зарегистрировать сборку, полученную врезультате компиляции проекта. Это можно сделать CREATE ASSEMBLY, в моем случае этовыглядит так:

use CLRFunctions

alter database CLRFunctions set trustworthy on

create assembly CLRFunctions

from 'C:\Work\Tutorials\CLRFunctions\bin\Debug\CLRFunctions.dll'

with permission_set = unsafe

В этом сценарии мы сначала изменяем настройки trustworthy базыданных на on, чтобы можно было зарегистрировать «небезопасную» сборку ICSharpCode.SharpZipLib.dll, на которую ссылается наша сборка CLRFunctions. Без этого SQL Server непозволит зарегистрировать сборку.

После этого необходимо создать в БД функции-обертки длянаших CLR-функций,используя следующий сценарий:

create function dbo.unzip_data(@data varbinary(max))

returns varbinary(max)

external name CLRFunctions.[CLRFunctions.CompressionFunctions].UnZipData

create function dbo.zip_data(@data varbinary(max))

returns varbinary(max)

external name CLRFunctions.[CLRFunctions.CompressionFunctions].ZipData

Внешнее имя функции должно указываться в виде <Имя сборки>.[<Пространствоимен>.<Имя класса>].<Имя метода>.

Давайте протестируем работу функций сначала простымсценарием:

select cast (

dbo.unzip_data(

dbo.zip_data(

cast('Some really stupid test data' as varbinary(max))

)

) as varchar(max))

Если вы все сделали правильно, в результате должна получитьсяоригинальная строка.

И более замороченным сценарием на случайных данных:

declare @test_compression table (

data varbinary(max)

)

insert into @test_compression (

data

)

select top 1000

cast(

replicate(

cast(newid() as varchar(36)), abs(checksum(newid())) % 5000 + 5000

) as varbinary(max)

)

from master.sys.objects s1,master.sys.objectss2, master.sys.objects s3

select sum(datalength(data)) as uncompressed

, sum(datalength(dbo.zip_data(data))) as compressed

, cast(

(sum(datalength(dbo.zip_data(data))) * 1.0 / sum(datalength(data))) * 100

as numeric(5, 2)

) as ratio

from @test_compression

Важное примечание

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

drop functiondbo.unzip_data

drop function dbo.zip_data

drop function dbo.split_text

drop assemblyCLRFunctions

Еще примечание

Тестируйте,прежде чем использовать в эксплуатационной среде.

Бонус – создание табличной CLR-функции

Функции, созданные выше, представляли собой простыескалярные функции – получаем на вход данные, возвращаем один результат. Помимоскалярных функций можно создавать и табличные CLR-функции. Давайте рассмотрим это напростом примере – создадим функцию, которая принимает на вход текст, разбиваетего на слова в соответствии с переданными разделителями, и возвращает слова ввиде таблицы.

Вернитесь в Visual Studio и откройте проект, содержащий функции сжатия и распаковки.Добавьте в проект новый класс, назовите его TextFunctions и пометьте модификаторами public иstatic:

namespace CLRFunctions

{

public static class TextFunctions

{

}

Основное отличие табличных CLR-функций в том, что они возвращаютинтерфейс IEnumerable и для них необходимо указывать имя метода, который будетзаполнять и материализовывать отдельные записи:

[SqlFunction(FillRowMethodName= "SplitTextFillRow")]

public static IEnumerable SplitText(stringtext, string separators)

{

//Разбиваем текстна элементы в соответствии с переданными разделителями

string[] items =text.Split(separators.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

//Этотмассив нужен для преобразования типа string к типу CLR SqlChars

SqlChars[] ichars =new SqlChars[items.Length];

//Преобразовываем элементы к типу SqlChars

for (int i = 0; i< items.Length; i++)

{

ichars[i]= new SqlChars(items[i]);

}

return ichars;

}

//Этотметод заполняет и материализует одну строку набора данных

public static void SplitTextFillRow(objecto, out SqlCharsitem)

{

item = (SqlChars)o;

}

Скомпилируйте и зарегистрируйте сборку, зарегиструйтефункцию, и протестируйте ее работу:

select *

from dbo.split_text(

'Some really stupid test data. Yeah, really stupid!'

, ' . ,')

That’s all,folks! Sai gobe!

Приложение – сценарий регистрации/перерегистрации сборки и функций

use CLRFunctions

drop function dbo.unzip_data

drop function dbo.zip_data

drop function dbo.split_text

drop assembly CLRFunctions

alter database CLRFunctions set trustworthy on

create assembly CLRFunctions

from 'C:\Work\Tutorials\CLRFunctions\bin\Debug\CLRFunctions.dll'

with permission_set = unsafe

create function dbo.unzip_data(@data varbinary(max))

returns varbinary(max)

external name CLRFunctions.[CLRFunctions.CompressionFunctions].UnZipData

create function dbo.zip_data(@data varbinary(max))

returns varbinary(max)

external name CLRFunctions.[CLRFunctions.CompressionFunctions].ZipData

create function dbo.split_text(@text nvarchar(max), @separators nvarchar(128))

returns table (item nvarchar(max))

external name CLRFunctions.[CLRFunctions.TextFunctions].SplitText

Microsoft SQL Server и связанные технологии

четверг, 21 мая 2009 г.