Табличные CLR-функции для ТЧайников
Довелось читать разработку под SQL Server для разработчиков, прежде не использовавших технологии Microsoft. В частности, CLR-программирование. Процедуры и скалярные функции воспринимаются нормально, на UDT традиционно всегда лучше отводить побольше времени, некоторые трудности в понимании обозначились на табличных функциях и агрегатах. Вообще говоря, странно. За больше, чем 3 года, я думал, все уже давно во всем разобрались. По крайней мере, материалов сейчас больше, чем достаточно. Ну давайте пусть будет бесконечность плюс один. Начнем с табличных функций. Самый быстрый способ въезжания в материал есть решение практической задачи. Выберем в качестве элементарной задачи написание функции Dir, т.е. выводящей список файлов заданной папки.
Делай раз. Открыли VS, сказали создать новый проект, выбрали язык -> Database -> SQL Server Project.
рис.1
Либо. Спустились ниже -> Database Projects -> SQL CLR -> SQL Server Project c иконкой того языка (С#, VB, …), на котором будете его делать.
рис.2
Один комик утверждал, что для этого на VS предварительно требуется нашлепнуть Microsoft® Visual Studio Team System 2008 Database Edition GDR R2 (https://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en). Не надо песен. Там, кстати, в даунлоудах написано, что добавляет эта нашлепка. Для CLRных проектов на SQL Server и Business Intelligence Projects достаточно базовой установки SQL Server.
Делай два. Установили соединение с SQL Server.
рис.3
При необходимости соединение можно установить потом или поменять в свойствах проекта (меню Project -> SqlServerProject1 Properties -> закладка Database).
рис.4
Соединение требуется для автоматического деплоймента / отладки проекта из VS, хотя все это можно при желании проделать руками из SSMS.
Делай три. Кликом по меню Project или правым кликом по проекту в Solution Explorer добавили в проект заготовку типа User-Defined Function. Назвали ее Dir.
рис.5
Делай четыре. В Dir.cs написали следующий код:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
struct row_item
{
public string fullName;
public DateTime dateModified;
public long size;
public bool isDir;
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "Dir", FillRowMethodName = "FillRow",
TableDefinition = "fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)")]
public static IEnumerable InitMethod(string folder)
{
List<row_item> enumResult = new List<row_item>();
foreach (string fileName in Directory.GetFiles(folder, "*", SearchOption.AllDirectories))
{
FileInfo fi = new FileInfo(fileName);
row_item r = new row_item(); r.fullName = fileName; r.dateModified = fi.LastWriteTimeUtc; r.size = fi.Length; r.isDir = false;
enumResult.Add(r);
}
foreach (string dirName in Directory.GetDirectories(folder, "*", SearchOption.AllDirectories))
{
DirectoryInfo di = new DirectoryInfo(dirName);
row_item r = new row_item(); r.fullName = dirName; r.dateModified = di.LastWriteTime; r.isDir = true;
enumResult.Add(r);
}
return enumResult;
}
public static void FillRow(Object o, out SqlString fullName, out DateTime? dateModified, out SqlInt64 size, out SqlBoolean isDir, out SqlString parent)
{
row_item r = (row_item)o;
fullName = r.fullName; dateModified = r.dateModified; size = r.size; isDir = r.isDir; parent = Path.GetDirectoryName(r.fullName);
}
}
Скрипт 1.
Пояснения к коду. Table-valued UDF состоит из двух методов. Первый (InitMethod) должен возвращать IEnumerable (foreach - https://msdn.microsoft.com/ru-ru/library/system.collections.ienumerable.aspx) коллекцию объектов. Рассматривайте ее как полуфабрикат таблицы, которая будет возвращаться данной UDF. Объекты – это, условно говоря, ее записи. В нашем случае запись почти представлена стр-рой row_item. В вывод попадает еще поле parent, но оно, можно сказать, вычисляемое, поэтому в стр-ру не входит. Если вы одолели ужасный русский перевод https://msdn.microsoft.com/ru-ru/library/ms131103.aspx, то почерпнули, что T-SQLная UDF отличается от CLRной тем, что выбабахивает весь резалтсет чохом, тогда как CLRная – это потоковый доступ. Потоковый доступ означает, что всякий раз, когда мы в foreach продвигаемся на Next, дергается второй метод, который в нашем случае называется FillRow, возвращающий следующую запись. Своего рода делегат. Но SQLные проекты делегатов не понимают, поэтому его имя оговаривается в атрибутах метода InitMethod. Метод FillRow имеет в кач-ве первого параметра объект = очередной элемент коллекции, т.е. прототип очередной записи, а дальше идут выходные параметры по кол-ву полей резалтсета, возвращаемого данной UDF. Это уже точный набор полей, не полуфабрикатный. Он должен соответствовать структуре таблицы, прописанной в атрибуте TableDefinition метода InitMethod. Из этого атрибута стр-ра таблицы методом copy-paste впоследствии переносится в Т-SQL в случае ручного создания функции create function dbo.Dir(@folder nvarchar(1000)) returns table (fullName nvarchar(1000), dateModified datetime2, ...) as external name MyAssembly.UserDefinedFunctions.InitMethod. При автоматизированном деплойменте VS все сделает сама. Все. Кто считает, что это сложно, почитайте замечательную Бушменскую книжку "A First Look at SQL Server 2005 for Developers", Published: Addison-Wesley Professional (July 5, 2004), как это выглядело на заре появления Юкона, стр.86 – 92. Особенно обратите внимание на комментарий Many methods removed for clarity, see full example on Web site, чтоб служба медом не казалась. Хотя... ISqlReader вместо IEnumerable, ISqlRecord вместо отвязного набора переменных, GetSqlMetaData вместо "структуры" таблицы в строковом атрибуте, возможно, выглядели стройнее, строже и аккуратней.
Делай пять. Подписываем и деплоим проект, как описано в материале "Подписание внешней или небезопасной сборки" (https://blogs.msdn.com/alexejs/archive/2009/05/11/0-9-8-7-6-5-5-6.aspx). Подписание необходимо, поскольку сборка обращается к файловой системе, т.е. должна иметь permission_set = external_access. После того, как сборка подписана и с ее ключом проассоциирован логин на SQL Server, которому выданы права external access assembly, Шаг 4 можно выполнить из VS, выбрав меню Build -> Deploy Solution.
Отладка выполняется также из VS. В коде Скрипт 1 ставим брыкпойнт, в файле Test.sql проекта пишем
select * from dbo.Dir('c:\Temp') и жмем Run.
рис.6
Замечания.
1. Несмотря на атрибут [Microsoft.SqlServer.Server.SqlFunction(Name = "Dir" , ...)] соотв-й UDF метод при деплойменте видится по своему оригинальному имени:
create function dbo.Dir(@folder nvarchar(1000)) returns table (fullName nvarchar(1000), dateModified datetime2, size bigint, isDir bit, parent nvarchar(1000)) as external name MyAssembly.UserDefinedFunctions.InitMethod
2. В статье про соответствия типов https://msdn.microsoft.com/ru-ru/library/ms131092.aspx говорится, что новому типу datetime2 вполне соответствует SqlDateTime. Тем не менее, если в параметры FillRow поставить out SqlDateTime dateModified, ругается, что он не соответствует декларации таблицы в атрибуте перед InitMethod: [Microsoft.SqlServer.Server.SqlFunction(…, TableDefinition = "…, dateModified datetime2, …")]. Приходится вместо SqlDateTime использовать дотнетовский DateTime с возм-ю Nullов. Наверно, стоит накатить VSTS 2008 Database Edition GDR R2. Там как раз обещана нормальная поддержка всех новых типов данных, включая Intrinsic (date, datetime2, datetimeoffset, time).