Источник: habrahabr
bocharovf
Transact SQL великолепный язык, функциональности которого более чем достаточно для решения большинства часто возникающих задач. Однако иногда возникают задачи, которые с его помощью решать долго и/или неудобно. Пожалуй, самым ярким примером является продвинутый парсинг строк, в котором приходится использовать регулярные выражения или просто хитрый и закрученный алгоритм. Начиная с SQL Server 2005, эта проблема решается созданием хранимой процедуры/функции CLR. Но этот подход требует перекомпиляции и развертывания сборки при внесении изменений. А так хочется, не покидая Management Studio, изменять поведение своих процедур.
Естественным образом возникает желание встроить в T-SQL поддержку какого-нибудь скриптового языка, чтобы выполнять код на лету. Благодаря DLR (Dynamic Language Runtime) в .Net Framework 4 у нас появилась такая возможность. Исключительно в силу личных пристрастий автора в качестве такого языка был выбран IronPython.
Под катом пошаговая инструкция и демонстрация результата.
Каким должен быть результат
Я хочу получить функцию вида
select [dbo].[pyExecute](
'
import re
re.findall("\d+","Всем счастья в 2013 !")[0]
'
)
Неплохо было бы иметь так же агрегирующую функцию и хранимую процедуру, использующую код на python.
Что нам потребуется
Для реализации задуманного мы будем использовать SQL Server 2008 R2, Visual Studio 2010 и IronPython 2.6.2. IronPython придется собирать из исходниковисправив всего одну строчку кода (об этом чуть позже).
Настройка сервера
Для начала создадим отдельную базу для экспериментов. В дальнейших примерах я использую базу с именем CLR.
-- Указываем серверу доверять нашей базе и нашему коду
ALTER DATABASE CLR SET TRUSTWORTHY ON
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- Включаем выполнение управляемого кода
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Сборка IronPython из исходников
Инициализация движка python в контексте Sql Server будет вызывать ошибку, исправить которую можно немного подкорректировав исходники. Для этого скачиваем исходные коды IronPython 2.6.2 и открываем проект. Находим в проекте IronPython файл Modules\sys.cs и в функции GetPrefix повторяем код, использующийся для сборки под Silverlight. Таким образом функция GetPrefix будет всегда возвращать пустую строку.
private static string GetPrefix() {
string prefix;
#if SILVERLIGHT
prefix = String.Empty;
#else
// prefix теперь всегда равен пустой строке
prefix = String.Empty;
/*
try {
prefix = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
} catch (SecurityException) {
prefix = String.Empty;
}
*/
#endif
return prefix;
}
Собираем проект и получаем сборки IronPython.dll, IronPython.Modules.dll, Microsoft.Dynamic.dll, Microsoft.Scripting.dll, Microsoft.Scripting.Core.dll, Microsoft.Scripting.Debugging.dll, Microsoft.Scripting.ExtensionAttribute.dll. Советую скопировать их в отдельную папку, так как они нам в дальнейшем еще понадобятся.
Создание наших сборок
Теперь мы можем смело открывать Visual Studio и создавать наши сборки. Нам потребуется решение с двумя проектами. Первый проект pyCore - это библиотека классов, непосредственно исполняющая код на языке IronPython. Второй проект pySQL - проект базы данных для CLR, использующий сборку pyCore и содержащий код наших функций и хранимых процедур.
pyCore
Целевым фреймворком выбираем .net 3.5. Добавляем в проект ссылки на сборки IronPython.dll, IronPython.Modules.dll, Microsoft.Scripting.dll, Microsoft.Scripting.Core.dll. Напомню, что эти библиотеки мы получаем после сборки IronPython из исходников. Наш проект будет содержать всего один статический класс pyCore, ответственный за создание и инициализацию движка IronPython, управление контекстом (scope) и выполнение переданного скрипта.
Код библиотеки pyCore
Основной интерес представляет функция py_exec, которая принимает текст скрипта, контекст выполнения и аргументы, которые должны быть переданы в скрипт.
Теперь необходимо создать сборку pyCore в базе данных CLR. Для этого выполним следующий скрипт:
CREATE ASSEMBLY PYCORE
FROM N'<Полный путь к сборке…>\pyCore.dll'
WITH PERMISSION_SET = UNSAFE
Скорее всего, Вы получите ошибку, следующего вида:
Assembly 'pyCore' references assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.', which is not present in the current database.
Иными словами - не все библиотеки, необходимые для работы pyCore, присутствуют в базе. Чтобы не утомлять читателя, я приведу сразу скрипт, загружающий все необходимое. После ключевого слова FROM необходимо указать полный путь к сборке. Большинство сборок получаем собрав IronPython из исходников. Сборку System.Runtime.Remoting.dll можно найти в C:\Windows\Microsoft.NET\Framework\v2.0.50727\
Скрипт создания всех необходимых сборок
pySQL
Черная работа сделана, и самое время начать реализовывать процедуры и функции доступные из SQL Server. Создадим проект баз данных CLR и в строке соединения укажем нашу базу данных для тестов. Теперь необходимо добавить ссылку на сборку pyCore. Если вы правильно указали строку соединения с базой данных в проекте, то при добавлении новой ссылки вы увидите все сборки, существующие в базе данных. Среди них выбираем pyCore, Scripting и ScriptingCore.
Функция CLR
Добавим в проект новый элемент - пользовательскую функцию.
public partial class UserDefinedFunctions
{
/// <summary>
/// Выполнение переданного кода на языке IronPython
/// </summary>
/// <param name="cmd">Текст скрипта</param>
/// <returns>Результат работы скрипта</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object pyFunc(string cmd)
{
return pyCore.pyCore.py_exec(cmd);
}
/// <summary>
/// То же самое но с 1 аргументом
/// </summary>
/// <param name="cmd">Текст скрипта</param>
/// <param name="arg1">Аргумент 1. Из скрипта доступен как args[0]</param>
/// <returns>Результат работы скрипта</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object pyFunc1(string cmd, object arg1)
{
return pyCore.pyCore.py_exec(cmd,null,arg1);
}
/// <summary>
/// То же самое но с 2 аргументами
/// </summary>
/// <param name="cmd">Текст скрипта</param>
/// <param name="arg1">Аргумент 1. Из скрипта доступен как args[0]</param>
/// <param name="arg2">Аргумент 2. Из скрипта доступен как args[1]</param>
/// <returns>Результат работы скрипта</returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object pyFunc2(string cmd, object arg1, object arg2)
{
return pyCore.pyCore.py_exec(cmd,null,arg1,arg2);
}
// pyFunc3, 4, ... , N
};
В функциях не происходит ничего интересного - прямой вызов py_exec и перенаправление аргументов. Здесь предусмотрено два варианта использования: передача параметров в скрипт при формировании текста скрипта и явная передача параметров через массив args. Второй способ, на мой взгляд, более читабелен и безопасен.
-- Передача параметров при составлении скрипта
select dbo.pyFunc('"'+name+'".upper()')
from sys.all_objects
-- Явная передача параметров
select dbo.pyFunc1('str(args[0]).upper()',name)
from sys.all_objects
При объявлении функции CLR в SQL Server происходит сравнение сигнатур, которое не понимает или по каким-то другим причинам не учитывает ключевого слова params. В результате приходится объявлять несколько функций с различным числом параметров. В реальности, редко встречается необходимость создавать функции с числом параметров больше трех-четырех, так что это не очень существенное ограничение.
Процедура CLR
public partial class StoredProcedures
{
/// <summary>
/// Хранимая процедура CLR
/// </summary>
/// <param name="cmd">Исполняемый код</param>
/// <returns>Результат работы Int,Число</returns>
[Microsoft.SqlServer.Server.SqlProcedure]
public static int pyProc(string cmd)
{
var scope = pyCore.pyCore.CreateScope();
// Передаем скрипту ссылку на SqlPipe
scope.SetVariable("Pipe", SqlContext.Pipe);
return (int)pyCore.pyCore.py_exec(cmd,scope);
}
};
Внутреннее устройство процедуры немного отличается от функции. Дополнительно передаем в скрипт ссылку на экземпляр объекта SqlPipe, чтобы можно было возвращать табличный результат и выводить сообщения.
Агрегирующая функция
Агрегирующую функцию нельзя создать, используя Transact SQL. Единственный вариант - использование сборок CLR. Почему это так становится ясно при первом взгляде на структуру агрегирующей функции CLR.
Код агрегирующей функции
Мы реализуем интерфейс IBinarySerialize чтобы предоставить скрипту возможность сохранять свое состояние и промежуточный результат вычислений. Так как функция Init не принимает аргументов, скрипт инициализации приходится выполнять при первом запуске функции Accumulate. Наша агрегирующая функция принимает тексты скриптов для обработки каждого события. Сами тексты скриптов сохраняются во внутренних полях объекта и сериализуются.
Создание сборки pySQL в базе и объявление функций
Теперь, когда сборка готова, ее необходимо развернуть в базе CLR.
CREATE ASSEMBLY PYSQL
FROM N'<Полный путь к сборке…>\pySQL.dll'
WITH PERMISSION_SET = UNSAFE
Теперь объявим наши функции и процедуры.
-- Вариант передачи параметра, путем составления скрипта по частям
CREATE FUNCTION [dbo].[pyFunc] (@cmd nvarchar(MAX))
RETURNS sql_variant
AS EXTERNAL NAME PYSQL.[UserDefinedFunctions].[pyFunc];
GO
-- Вариант с явной передачей параметров
CREATE FUNCTION [dbo].[pyFunc1] (@cmd nvarchar(MAX), @arg1 sql_variant)
RETURNS sql_variant
AS EXTERNAL NAME PYSQL.[UserDefinedFunctions].[pyFunc1];
GO
-- Вариант с явной передачей параметров
CREATE FUNCTION [dbo].[pyFunc2] (@cmd nvarchar(MAX), @arg1 sql_variant, @arg2 sql_variant)
RETURNS sql_variant
AS EXTERNAL NAME PYSQL.[UserDefinedFunctions].[pyFunc2];
GO
-- Хранимая процедура
CREATE PROCEDURE pyProc (@code nvarchar(MAX))
AS EXTERNAL NAME PYSQL.StoredProcedures.pyProc
GO
-- Агрегирующая функция
CREATE AGGREGATE [dbo].[pyAggregate] (
@value sql_variant,
@init nvarchar(MAX),
@accumulate nvarchar(MAX),
@merge nvarchar(MAX),
@terminate nvarchar(MAX),
@save nvarchar(MAX),
@restore nvarchar(MAX)
)
RETURNS sql_variant
EXTERNAL NAME PYSQL.[pyAggregate];
GO
Результаты
Если вы дочитали до этого момента, значит, вы вправе наградить себя, увидев результаты своего труда.
Функция
Для начала решим задачу с регулярными выражениями - найдем email адреса в строке. Саму строку будем вставлять непосредственно в скрипт при его формировании.
select [dbo].[pyFunc](
'
import re
mails = re.findall("[.\\-_a-z0-9]+@(?:[a-z0-9][\\-a-z0-9]+\\.)+[a-z]{2,6}","'+doc+'")
result = "Найдено: "
for mail in mails:
result += mail + ","
result[:-1]
'
)
from (
select 'В этой строке нужно найти somebody@gmail.com' doc union
select 'А в этой через запятую person1@mail.ru и person2@list.ru' doc
) SAMPLE_DATA
Результат:
Найдено: somebody@gmail.com
Найдено: person1@mail.ru,person2@list.ru
То же самое, но с использованием параметров. На мой взгляд - более красивый способ, однако требующий объявления функции с нужным числом аргументов.
select [dbo].[pyFunc1](
'
import re
mails = re.findall("[.\\-_a-z0-9]+@(?:[a-z0-9][\\-a-z0-9]+\\.)+[a-z]{2,6}",str(args[0]))
result = "Найдено: "
for mail in mails:
result += mail + ","
result[:-1]
', SAMPLE_DATA.doc
)
from (
select 'В этой строке нужно найти somebody@gmail.com' doc union
select 'А в этой через запятую person1@mail.ru и person2@list.ru' doc
) SAMPLE_DATA
Результат естественно тот же.
Такую функцию можно использовать и для вычисления сложных математических функций, не встроенных в SQL Server и для динамического вычисления выражений (этого можно добиться и с помощью sp_execute).
declare @InputFromula as nvarchar(MAX)
SET @InputFromula = 'math.log(math.cosh(int(args[0]))/math.e,int(args[1]))'
select [dbo].[pyFunc2] (
'import math
'+@InputFromula,
100,5
)
Хранимая процедура
Приведу сразу полный пример. Здесь мы пишем текстовое сообщение, используя объект SqlPipe, заботливо переданный нашему скрипту, потом формируем таблицу, заполняем данными и возвращаем в качестве результата.
exec pyProc
'
import clr
clr.AddReference("System.Data")
from Microsoft.SqlServer.Server import *
from System.Data import *
from System import DateTime
Pipe.Send("Пишем сообщение: поехали!")
metadata = (
SqlMetaData("Имя работника", SqlDbType.NVarChar, 12),
SqlMetaData("Ожидаемая заработная плата", SqlDbType.Int),
SqlMetaData("Ожидаемая дата повышения", SqlDbType.DateTime)
)
record = SqlDataRecord(metadata)
record.SetString(0, "bocharovf");
record.SetInt32(1, 1000000);
record.SetDateTime(2, DateTime.Now);
#выдаем табличный результат
Pipe.Send(record)
1
'
Результат выполнения:
Пишем сообщение: поехали!
| Имя работника | Ожидаемая заработная плата | Ожидаемая дата повышения |
| bocharovf | 1000000 | 2012-12-31 02:39:51.293 |
(1 row(s) affected)
Агрегирующая функция
Перечислим через запятую языки, использованные в статье, с использованием нашей агрегирующей функции. В скриптах сериализации и десериализации используем ссылки на экземпляры классов BinaryReader и BinaryWriter, переданные нашему скрипту. Результат накапливается в переменной data.
select dbo.pyAggregate
(
-- Агрегируемое значение
SAMPLE_DATA.[language],
-- Инициализация
'data = ""',
-- Аккумуляция
'data += str(value) + ", "',
'# nop',
-- Вывод результата
'data[:-2]',
-- Сериализация
'writer.Write(str(data))',
-- Десереализация
'data = reader.ReadString()'
) as Languages
,SAMPLE_DATA.IsUsed
from
(
select 'C#' [language], 'Используется в статье' IsUsed union
select 'T-SQL', 'Используется в статье' union
select 'IronPython', 'Используется в статье' union
select 'Cobol', 'Не используется в статье' union
select 'Ada', 'Не используется в статье' union
select 'Lisp', 'Не используется в статье' union
select 'Fortran', 'Не используется в статье'
) SAMPLE_DATA
group by SAMPLE_DATA.IsUsed
Результат:
| Languages | IsUsed |
| C#, IronPython, T-SQL | Используется в статье |
| Ada, Cobol, Fortran, Lisp | Не используется в статье |
(2 row(s) affected)
Производительность
Как и следовало ожидать, производительность невелика. Например, скорость вывода строки справа налево стандартной функцией T-SQL REVERSE и с помощью операции среза в python отличается почти в 80 раз.
Безопасность
exec pyProc
'
from System.Net import WebClient
content = WebClient().DownloadString("http://habrahabr.ru/")
Pipe.Send(content[:4000])
1
'
Так как наши возможности ничем не ограничены, мы можем выполнить любой код, в том числе обратится к ресурсу в интернете или удалить файл с диска. Поэтому раздавать права нужно с осторожностью и использовать преимущественно передачу параметров в функцию вместо составления скрипта по частям.
Вместо заключения
Каждый инструмент нужно использовать только там, где это действительно имеет смысл. Не стоит пытаться переписывать все ваши хранимые процедуры и функции с использованием IronPython. Скорее функции на IronPython подойдут для реализации сложных алгоритмов, использующих функциональность, отсутствующую в Transact SQL или обработки данных из внешних источников (файловая система, интернет). Помимо IronPython желающие могут встроить поддержку IronRuby или, например, Javascript .NET.