Используем IronPython из Transact SQL

в 12:12, , рубрики: .net, DLR, ironpython, python, sql server, метки: , , ,

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 файл Modulessys.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

using System;
using Microsoft.Scripting.Hosting;
using IronPython.Hosting;
 
namespace pyCore
{
    /// <summary>
    /// Ответственнен за создание и инициализацию движка, выполнение кода.
    /// </summary>
    public static class pyCore
    {
        static ScriptEngine engine;
        static ScriptRuntime runtime;
 
        /// <summary>
        /// Статический конструктор, в котором мы инициализируем движок и среду выполнения
        /// </summary>
        static pyCore() {
            engine = Python.CreateEngine();
            runtime = engine.Runtime;
        }
 
        /// <summary>
        /// Установка переменной скрипта
        /// </summary>
        /// <param name="scope">Контекст выполнения</param>
        /// <param name="name">Имя переменной для доступа из скрипта</param>
        /// <param name="value">Значение переменной</param>
        public static void py_setvar (ScriptScope scope,string name, object value) {
            scope.SetVariable(name,value);
        }
 
        /// <summary>
        /// Выполнение переданного скрипта.
        /// </summary>
        /// <param name="cmd">Текст скрипта</param>
        /// <param name="scope">Контекст выполнения. Если null юудет создан новый контекст.</param>
        /// <param name="args">Аргументы для передачи в скрипт. Будут доступны из массива args</param>
        /// <returns></returns>
        public static object py_exec(string cmd, ScriptScope scope = null, params object [] args)
        {
            // Если контекст не передан - создаем новый
            var CurrentScope = (scope ?? runtime.CreateScope());
            // Если переданы аргументы, передаем их в скрипт
            if (args != null)
            {
                CurrentScope.SetVariable("args",args);
            }
            // Подготавливаем скрипт к выполнению
            var source = engine.CreateScriptSourceFromString(cmd, Microsoft.Scripting.SourceCodeKind.AutoDetect);
            // Возвращаем результат работы скрипта
            return source.Execute(CurrentScope);
        }
 
        /// <summary>
        /// Создаем новый контекст выполнения
        /// </summary>
        /// <returns></returns>
        public static ScriptScope CreateScope()
        {
            return engine.CreateScope();
        }
 
    }
}

Основной интерес представляет функция 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:WindowsMicrosoft.NETFrameworkv2.0.50727

Скрипт создания всех необходимых сборок

CREATE ASSEMBLY ExtensionAttribute
FROM N'<Полный путь к сборке…>Microsoft.Scripting.ExtensionAttribute.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY ScriptingCore
FROM N'<Полный путь к сборке…>Microsoft.Scripting.Core.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY Scripting
FROM N'<Полный путь к сборке…>Microsoft.Scripting.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY SystemRuntimeRemoting
FROM N'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Runtime.Remoting.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY MicrosoftDynamic
FROM N'<Полный путь к сборке…>Microsoft.Dynamic.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY ScriptingDebugging
FROM N'<Полный путь к сборке…>Microsoft.Scripting.Debugging.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY IronPythonModules
FROM N'<Полный путь к сборке…>IronPython.Modules.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY PYCORE
FROM N'<Полный путь к сборке…>pyCore.dll'
WITH PERMISSION_SET = UNSAFE

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.

Код агрегирующей функции

/// <summary>
/// Агрегирующая функция
/// </summary>
[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined, // Используем собственный алгоритм сериализации, реализуя IBinarySerialize
    IsInvariantToNulls = false, 
    IsInvariantToDuplicates = false, 
    IsInvariantToOrder = false, 
    MaxByteSize = 8000) 
]
public class pyAggregate : IBinarySerialize
{
    /// <summary>
    /// Контекст выполнения
    /// </summary>
    public ScriptScope scope;
 
    string init;
    string merge;
    string terminate;
    string accumulate;
    string save;
    string restore;
 
    /// <summary>
    /// Используется для инициализации структур данных
    /// </summary>
    public void Init()
    {
        // Создаем новый контекст
        scope = pyCore.pyCore.CreateScope();
    }
 
    /// <summary>
    /// Аккумулирование (Обработка следующего значения из множества)
    /// </summary>
    /// <param name="value">Следующее значение в множестве</param>
    /// <param name="init">Код инициализации</param>
    /// <param name="accumulate">Код аккумулирования</param>
    /// <param name="merge">Код слияния частичных результатов</param>
    /// <param name="terminate">Код завершения</param>
    /// <param name="save">Код сериализации</param>
    /// <param name="restore">Код десериализации</param>
    public void Accumulate(object value, string init, string accumulate, string merge, string terminate, string save, string restore)
    {
        // передаем скрипту значение, которое должно быть обработано
        scope.SetVariable("value", value);
 
        // Если инициализации еще не было - сохраняем во внутренние поля тексты скриптов и выполняем скрипт инициализации
        if (this.init == null)
        {
            this.init = init;
            this.merge = merge;
            this.terminate = terminate;
            this.accumulate = accumulate;
            this.save = save;
            this.restore = restore;
 
            pyCore.pyCore.py_exec(this.init, scope);
        }
        
        // выполняем скрипт аккумулирования
        pyCore.pyCore.py_exec(this.accumulate, scope);
    }
 
    /// <summary>
    /// Слияние частичных результатов
    /// </summary>
    /// <param name="other"></param>
    public void Merge(pyAggregate other)
    {
        pyCore.pyCore.py_setvar(scope, "otherdata", other);
        pyCore.pyCore.py_exec(this.merge, scope);
    }
 
    /// <summary>
    /// Возврат результата работы функции
    /// </summary>
    /// <returns></returns>
    public object Terminate()
    {
        return pyCore.pyCore.py_exec(this.terminate, scope);
    }
 
    /// <summary>
    /// Сериализация. Реализуем интерфейс IBinarySerialize
    /// </summary>
    /// <param name="r">Поток чтения</param>
    public void Read(BinaryReader r)
    {
        // Достаем тексты скриптов
        this.init = r.ReadString();
        this.merge = r.ReadString();
        this.accumulate = r.ReadString();
        this.terminate = r.ReadString();
        this.save = r.ReadString();
        this.restore = r.ReadString();
 
        // Создаем новый контекст
        scope = pyCore.pyCore.CreateScope();
        // Передаем в скрипт ссылку на BinaryReader, 
        // чтобы он восстановил свое внутреннее состояние
        pyCore.pyCore.py_setvar(scope, "reader", r);
        pyCore.pyCore.py_exec(this.restore, scope);
        
    }
    
    /// <summary>
    /// Десериализация. Реализуем интерфейс IBinarySerialize
    /// </summary>
    /// <param name="w">Поток записи</param>
    public void Write(BinaryWriter w)
    {
        // Сохраняем тексты скриптов
        w.Write(this.init);
        w.Write(this.merge);
        w.Write(this.accumulate);
        w.Write(this.terminate);
        w.Write(this.save);
        w.Write(this.restore);
 
        // Передаем в скрипт ссылку на BinaryWriter, 
        // чтобы он сохранил свое внутреннее состояние        
        pyCore.pyCore.py_setvar(scope, "writer", w);
        pyCore.pyCore.py_exec(this.save, scope);
    }
}

Мы реализуем интерфейс 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.

Автор: bocharovf

Источник

* - обязательные к заполнению поля

Главная   |  Архив новостей  |   Android  |   Google  |   Apple  |   Microsoft  |   Информационная безопасность  |   Веб – разработка
Публикации RSS  |  Комментарии RSS
https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js