MS SQL Server 9 “Yukon”. Интеграция с .NET
Антон Злыгостев (Sinclair)
ЮКОН
— река в Канаде и США (Аляска). Длина 3700 км, площадь бассейна 855 тыс.кв.км. Истоки — в горах, ограничивающих с юго-востока плоскогорье Юкон; главный исток — река Льюис, берущая начало в Кордильерах. Впадает в залив Нортон Берингова моря, образуя дельту.
Большая Советская Энциклопедия
Не так давно в руки некоторых представителей RSDN Team попал предварительный релиз следующей версии MS SQL Server. Это даже не бета-версия, (что неудивительно – ведь до ожидаемого поступления финального варианта в продажу остался целый год), но мы не могли устоять перед искушением заглянуть в будущее.
Со всей ответственностью заявляю – маркетинговые материалы, публикуемые по данному продукту, не отражают и малой доли новых возможностей. Из одного перечисления нововведений можно было бы сделать журнальную статью, поэтому исследователи разделили обязанности между собой. В данной статье рассматривается технология интеграции .NET и MS SQL Server.
Общая информация
Предыстория
Современные коммерческие РСУБД не могут позволить себе ограничиться ролью пассивного хранилища данных, поддерживая только SQL. Необходима поддержка возможностей процедурного программирования. До недавнего времени типичным решением этой проблемы было специфичное для производителя расширение стандартного SQL для написания триггеров и хранимых процедур (PL/SQL, T-SQL). На тот не столь уж редкий случай, когда этого расширения не хватало для удовлетворения потребностей разработчиков, предлагались не менее специфичные способы использовать «внешний» по отношению к серверу код (пользовательские функции в Interbase, расширенные хранимые процедуры в MS SQL и т.д.).
Основным недостатком первого решения является откровенная узость SQL, хотя бы и расширенного. «Дотянуть» SQL до полноценного языка программирования общего назначения – нереально. К тому же, он по необходимости является интерпретируемым языком, что ограничивает его быстродействие при выходе за пределы табличных операций. Например, написать хранимую процедуру для шифрования PGP вполне можно и на T-SQL (благо там не нужно ничего, кроме арифметики). Но скорость ее работы будет, мягко говоря, недостаточной.
При использовании внешнего кода возникает другая проблема – в большинстве случаев ему трудно получить доступ к контексту РСУБД, использующей его. Как правило, все взаимодействие происходит через «замочную скважину» точки входа в DLL. Да, для вычисления хеша MD5 такая технология вполне подходит, но возможности взаимодействия подобного кода с ядром СУБД слишком ограничены.
Несколько лет назад Oracle предложил использовать Java в качестве языка программирования для своей РСУБД. Эта практика не прошла незамеченной в Редмонде, и вот теперь Microsoft готовит ответный удар.
Следующие объекты MS SQL Server могут быть созданы с использованием .NET:
Хранимые процедуры.
Триггеры.
Функции (скалярные и табличные).
Агрегирующие функции.
Пользовательские типы данных.
Для написания кода этих объектов потребуется .NET Framework версии 1.2 или выше.
Загрузка кода в базу данных
В отличие от расширенных хранимых процедур, код которых находится во внешних динамических библиотеках, код .NET хранится внутри соответствующей базы данных. Это обеспечивает дополнительное удобство при администрировании – восстановление базы из резервной копии или перенос на другой сервер (detach/attach) не нарушит целостности приложения.
Есть два основных способа выполнить загрузку сборки в базу данных:
вручную, при помощи операторов T-SQL и любого клиентского приложения (пойдет даже старый Query Analyzer. Он хоть и не так красив в строю, как новый MS SQL Server Workbench, зато у него нет привычки падать и отжиматься в самые неподходящие моменты).
При помощи средств автоматического развертывания, встроенных в MS Visual Studio .NET codename Whidbey.
Как только сборка загружена в базу данных, ее код можно использовать для создания различных объектов. Особенности этих двух способов описаны в следующих двух подразделах.
Загрузка кода при помощи T-SQL
Загрузка кода производится при помощи оператора CREATE ASSEMBLY:
CREATE ASSEMBLY assembly_name
[ AUTHORIZATION owner_name ]
FROM { < client_assembly_specifier > | < assembly_bits > [,...n] }
[ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]
< client_assembly_specifier > :: =
'[machine_name]share_name[path]manifest_file_name'
< assembly_bits > :: =
{ varbinary_literal | varbinary_expression }
Код будет загружен в текущую базу данных соединения. Чтобы сменить базу, предварительно выполните команду USE database_name.
Имя параметра
Описание
assembly_name
Имя сборки .NET (assembly). Оно должно быть уникальным в пределах текущей базы данных, удовлетворять требованиям MS SQL Server, предъявляемых к идентификаторам, и при этом совпадать с именем файла сборки.
AUTHORIZATION owner_name
Указывает имя роли, которая будет считаться владельцем данной сборки. Можно указывать только те роли, в которые входит текущий пользователь, или те, на которые у него есть права IMPERSONATE. По умолчанию владельцем станет сам текущий пользователь.
<client_assembly_specifier>
Строковое выражение, которое указывает локальный или сетевой путь к файлу сборки. Можно загружать только однофайловые сборки. Все сборки, от которых зависит указанная, также будут автоматически загружены в базу данных. Если они уже присутствуют в базе, то у выполняющего операцию пользователя должны быть права REFERENCES для них. Если сборки не найдены ни в базе, ни в том же каталоге, где и основная, или на них нет соответстующих прав, оператор не будет выполнен.
assembly_bits
Бинарное представление сборки. Если сборок несколько, то первой должна идти главная из них, а потом те, от которых она зависит. Такая технология позволяет миновать фазу записи на диск при динамической генерации кода и избежать проблем с доступом к файловой системе. Именно таким образом происходит развертывание сборок из-под MS Visual Studio 8.0 (Whidbey).
PERMISSION_SET {SAFE | EXTERNAL_ACCESS | UNSAFE }
Уровень прав, которые MS SQL Server предоставит коду сборки при его исполнении. По умолчанию используется SAFE.SAFE – самый ограниченный уровень. Код в такой сборке не может получить доступ к внешнему миру (файловой системе, сети, переменным окружения или реестру).EXTERNAL_ACCESS позволяет выходить за пределы MS SQL Server, но с определенными ограничениями. Полный список ограничений на данный момент недоступен, но по крайней мере исполнение не-менеджед кода запрещено.UNSAFE предоставляет неограниченный доступ к ресурсам, как внешним, так и внутренним. Такие сборки могут загрузить в базу данных только члены группы sysadmin.В большинстве случаев должно хватать уровня SAFE. При этом есть гарантия, что злонамеренный разработчик не предоставит бинарный код, делающий какие-либо гадости от имени сервиса SQL Server или текущего пользователя. Все примеры в этой статье работают с уровнем SAFE.
Таблица 1. Параметры оператора CREATE ASSEMBLY.
Загрузка при помощи Visual Studio
Загружать код вручную не очень-то удобно. Особенно это сказывается во время разработки приложения, когда отлаживаемый код то и дело необходимо исправлять. Новая версия Visual Studio позволяет существенно облегчить этот процесс. Достаточно создать в студии проект типа SQL Server Project (не путать с Database Project!), и в меню Build появятся команды Deploy, которые (помимо компиляции исходного кода) сделают всю необходимую работу по размещению ваших сборок в базе данных. При открытии проекта Visual Studio попросит указать сервер и базу данных, в которую будет выполняться развертывание.
Естественно, «внутри» Visual Studio использует те же самые команды T-SQL. Как правило, параметры этих команд определяются автоматически. Для управления процессом развертывания используются (конечно же!) атрибуты. Классы этих атрибутов реализованы в сборке Microsoft.VisualStudio.DataTools.SqlAttributes.
СОВЕТ
Если у вас возникнет желание разработать свои средства автоматического развертывания проектов SQL Server, обратите внимание на эти атрибуты. Поддержка их поможет использовать код, написанный другими разработчиками, без дополнительных усилий
На уровне сборки для управления процессом развертывания определен один класс атрибута System.Data.Sql.SqlAssemblyAttribute. При создании проекта SQL Server Project в файл AssemblyInfo.cs автоматически будет добавлен этот атрибут. Его использование выглядит вот так:
[assembly: SqlAssembly(<name>, Authorization = <authorization>)]
Строковый параметр name соответствует параметру assembly_name оператора CREATE ASSEMBLY, параметр Authorization – параметру owner_name (см. предыдущий раздел).
Для того, чтобы Visual Studio могла корректно зарегистрировать ваши типы, процедуры, функции или триггеры, их код тоже надо будет снабдить соответствующими атрибутами. Подробности приведены далее.
Отладка кода
Говорят, что идеальные программисты с первого раза пишут безошибочный код. Если вы один из них, то можете пропустить этот раздел и переходить прямо к написанию кода.
Однако если вы, как и я, периодически испытываете взрыв эмоций при виде сообщения об ошибке, которое обрезано ровно перед тем местом, где должен быть номер строки и имя файла, то вас несомненно обрадует тот факт, что код .NET, хранящийся в базе данных, можно отлаживать с удобством и комфортом. Лично я пользовался для отладки все той же Visual Studio Whidbey, и выглядело это примерно так:
Прежде всего, нужно выполнить развертывание проекта (меню Build->Deploy). Настоятельно рекомендую выбирать отладочную конфигурацию проекта.
Теперь выясните идентификатор процесса (PID) MS SQL Server. Процесс называется “sqlservr.exe”. Те, у кого запущен только один экземпляр SQL Server, могут сразу переходить к пункту 3. У меня Yukon стоит рядом с MSDE, поэтому таких процессов нашлось два. Чтобы избежать неоднозначности, можно просто остановить лишние серверы, а можно подключиться к нужному и выполнить команду SELECT ServerProperty('ProcessID')
Теперь нужно подключиться к этому процессу для отладки. Меню Debug->Attach to Process… покажет диалог подключения к процессу. Выберите нужный процесс, и смело жмите Attach.
Все. Теперь вы можете ставить точки останова в исходных текстах классов, загруженных в сервер. Кроме того, по умолчанию отладчик будет перехватывать все исключения .NET.
Не забудьте сделать Debug->Detach All перед тем, как перекомпилировать проект. Отладчик Visual Studio блокирует файлы с отладочной информацией, что мешает компилятору произвести Build.
Хранимые процедуры
В новой версии MS SQL Server синтаксис оператора CREATE PROCEDURE был расширен. Вот фрагмент из SQL Server Books Online:
CREATE PROC [ EDURE ] [schema_name.] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH < procedure_option > [ ,...n ]
[ FOR REPLICATION ]
AS { < sql_statement > [ ...n ] | <.NET_Framework_reference> }
--
<.NET_Framework_reference> ::=
EXTERNAL NAME assembly_name:class_name[::method_name]
Как видно из этого фрагмента, теперь вместо указания тела процедуры на T-SQL можно указать метод класса из загруженной ранее сборки. К этому методу предъявляются следующие требования:
Это должен быть статический метод (не конструктор и не деструктор класса)
Число параметров должно совпадать с числом параметров в описании хранимой процедуры, а их типы должны быть совместимы с типами данных соответствующих параметров. Если параметр процедуры объявлен как OUTPUT, то соответствующий параметр метода должен передаваться по ссылке.
Метод должен либо не иметь возвращаемого значения, либо возвращать значение одного из следующих типов: SQLInt32, SQLInt16, System.Int32, System.Int16
Для успешного создания такой хранимой процедуры необходимо быть владельцем соответствующей сборки или иметь для нее права REFERENCES.
Давайте перейдем от слов к делу и попробуем создать хранимую процедуру.
Минимальный код хранимой процедуры на C# выглядит вот таким образом:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public class StoredProcedure
{
[SqlProcedure]
public static void MyProcedure()
{
}
};
Очевидно, он не очень функционален. Тем не менее, метод StoredProcedure.MyProcedure уже можно зарегистрировать в базе данных качестве хранимой процедуры, вызвать (например, из Query Analyzer), и убедиться, что он успешно выполняется (то есть ничего не делает).
Обратите внимание на атрибут SqlProcedure (System.Data.Sql.SqlProcedureAttribute). Этот атрибут не несет никакой информации для MS SQL Server. Он используется MS Visual Studio Whidbey при развертывании проекта – для методов, помеченных таким атрибутом, автоматически будут вызваны соответствующие операторы CREATE PROCEDURE. По умолчанию будет предпринята попытка назначить хранимой процедуре такое же имя, как и у метода. Это поведение можно изменить, воспользовавшись единственным свойством атрибута – Name. Если заменить девятую строку примера выше на [SqlProcedure("MyProcName")], то хранимая процедура будет называться MyProcName.
Здравствуй, мир
Останавливаться на том, каким образом хранимая процедура обрабатывает данные, смысла нет – это обычный C#, и его особенности хорошо известны. Давайте научим ее общаться с внешним миром. Для начала доведем ее до уровня Кернигана и Ритчи:
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;
public class StoredProcedure
{
[SqlProcedure("HelloWorld")]
public static void MyProcedure()
{
SqlContext.GetPipe().Send("Hello, Yukon!");
}
};
Эта процедура демонстрирует еще один важный компонент, связывающий .NET с MS SQL Server: класс System.Data.SqlServer.SqlContext. Этот класс содержит несколько статических методов, обеспечивающих доступ к контексту, в котором выполняется код. В данном случае мы получаем доступ к объекту класса System.Data.SqlServer.SqlPipe, который представляет серверную сторону соединения с клиентом. Именно в эту «трубу» SQL Server отправляет результаты выполнения запросов. Если хранимая процедура должна возвращать какие-то данные в клиентское приложение, то без SqlPipe не обойтись.
В этом примере мы используем метод SqlPipe.Send(String msg), предназначенный для отправки текстовых сообщений. Его функциональность аналогична команде print в T-SQL. Остальные методы SqlPipe предназначены для отправки табличных данных:
Метод или свойство
Описание
public void Execute (System.Data.SqlServer.SqlCommand command )public void Execute
(System.Data.SqlServer.SqlExecutionContext request )
Выполняет указанную команду или запрос и возвращает результат клиенту. Аналог выполнения оператора SELECT … FROM … в хранимой процедуре на T-SQL.
public void Send (System.Data.SqlServer.SqlError se)
Возвращает клиенту указанную ошибку.
public void Send (System.Data.Sql.ISqlReader reader)
Отправляет клиенту все записи из указанного набора.
public void SendResultsStart (System.Data.Sql.ISqlRecord record , bool sendRow)
Посылает клиенту первую запись в наборе записей. Устанавливает свойство SendingResults в true.
public System.Boolean SendingResults { get; }
Указывает, что процесс отправки набора записей не окончен.
public void SendResultsRow (System.Data.Sql.ISqlRecord record)public void Send (System.Data.Sql.ISqlRecord record )
Посылает клиенту очередную запись в наборе. Требует SendingResults == true.
public void SendResultsEnd ( )
Сигнализирует об окончании набора записей и устанавливает свойство SendingResults в false.
Таблица 2.
Таким образом, помимо передачи клиенту набора данных, полученного от сервера, можно формировать результаты вручную. С точки зрения клиента это будет выглядеть как обычный набор записей.
Возвращаем произвольные данные
Пока что документация весьма скупо освещает этот вопрос, но после нескольких экспериментов мне удалось создать вот такую процедуру:
[SqlProcedure()]
public static void CurrencyCourse(
[SqlMapping(typeof(SqlDateTime))] DateTime start,
[SqlMapping(typeof(SqlDateTime))] DateTime end)
{
using (SqlCommand cmd = SqlContext.GetCommand())
{
cmd.CommandText = @"
select changeDate, course from Course
where changeDate between @start and @end";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
DateTime current = start;
SqlDecimal course = SqlDecimal.Null; // сначала курс отсутствует;
SqlMetaData[] recstruct = new SqlMetaData[2];
recstruct[0] = new SqlMetaData("D", SqlDbType.DateTime);
recstruct[1] = new SqlMetaData("course", SqlDbType.Decimal, 10, 4);
SqlDataRecord rec = new SqlDataRecord(recstruct);
SqlPipe pipe = SqlContext.GetPipe();
pipe.SendResultsStart(rec, false);
using (SqlDataReader r = cmd.ExecuteReader())
{
while (r.Read())
{
rec.SetSqlDecimal(1, course);
while(current < r.GetDateTime(0))
{
rec.SetDateTime(0, current);
pipe.SendResultsRow(rec);
current = current.AddDays(1);
}
course = r.GetDecimal(1);
}
}
rec.SetSqlDecimal(1, course);
while (current <= end)
{
rec.SetDateTime(0, current);
pipe.SendResultsRow(rec);
current = current.AddDays(1);
}
pipe.SendResultsEnd();
}
}
Эта процедура превращает данные в таблице изменения курсов некой валюты (Course) в таблицу ежедневных значений курса, повторяя предыдущее значение для тех дней, в которые изменений не происходило.
На этот раз у процедуры есть параметры. Чтобы помочь инструментам автоматического развертывания (например, той же MS VS Whidbey) определить SQL-типы параметров хранимой процедуры, для параметров метода можно указать атрибут SqlMapping (System.Data.Sql.SqlMappingAttribute). Его единственный параметр и задает тип для параметра процедуры. В данном случае этот атрибут является избыточным – параметры типа DateTime автоматически отображаются в тип SQL datetime (которому соответствует тип CLR System.Data.SqlTypes.SqlDateTime), но в более сложных случаях им придется пользоваться для устранения неоднозначности.
Чтобы выполнить запрос к данным сервера, мы воспользуемся еще одним статическим методом класса SqlContext – SqlContext.GetCommand().
Чтобы возвратить данные клиенту, нужен экземпляр класса, реализующего интерфейс System.Data.Sql.ISqlRecord. В данном случае использован System.Data.Sql.SqlDataRecord. Его конструктор требует указать желаемую структуру записи. Эта структура описывается массивом объектов класса System.Data.Sql.SqlMetaData. В каждом объекте задается имя и тип соответствующей колонки. Мы описываем структуру, соответствующую в терминах SQL вот такой «таблице»:
(
D datetime,
course decimal(10, 4)
)
Создав запись, мы инициируем процесс отправки при помощи вызова:
pipe.SendResultsStart(rec, false);
Второй параметр говорит о том, что саму запись отправлять клиенту не нужно; вместо этого метаданные записи используются для инициализации отправляемого набора записей.
Дальше все просто – мы читаем очередную запись из SqlDataReader, полученного в результате исполнения команды, заполняем поля в SqlDataRecord, и отправляем ее клиенту. Дополнительный цикл в конце досылает записи для дат между последним изменением и концом запрошенного интервала.
Отправив все, что хотелось, мы сигнализируем клиенту об окончании набора при помощи вызова
pipe.SendResultsEnd();
Стоит отметить, что результаты возвращаются напрямую клиенту, т.е. код, который вызвал процедуру, не имеет над этим процессом никакого контроля. Повторное использование такого кода в серверной части приложения маловероятно. В следующем разделе мы узнаем о том, как можно обойти это ограничение.
Функции
В рамках T-SQL функции делятся на два вида: скалярные и табличные.
ПРИМЕЧАНИЕ
Есть еще агрегатные функции, но их реализация существенным образом отличается от «обычных», и поэтому мы рассмотрим их в следующем разделе.
С точки зрения .NET, эти два типа функций устроены почти одинаково. Как и хранимые процедуры, они реализуются при помощи статических методов класса. Отличие заключается в том, как они возвращают значения. Есть три варианта:
Возвращаем значение произвольного типа. Это скалярная функция.
Возвращаем System.Data.Sql.ISqlReader. Структура данных в нем должна совпадать с декларированной структурой результата функции. Это табличная функция.
Возвращаем void. Внутри функции вручную формируем возвращаемые данные через SqlContext.GetReturnResultSet(). Это тоже табличная функция.
Все эти варианты подробно рассмотрены далее.
ПРИМЕЧАНИЕ
В отличие от встроенных функций, обращаться к «самодельным» нужно с уважением – предваряя имя функции именем схемы (которое по умолчанию совпадает с именем ее владельца). Например, я вызывал функцию из следующего подраздела примерно вот так:
select dbo.RevertString(“Beavis rulez”)
Скалярные функции
Это самая простая разновидность функций. В качестве примера напишем свой вариант встроенной функции reverse:
[SqlFunc()]
[SqlFunction(
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = true,
IsPrecise = true)]
public static SqlString RevertString(SqlString str)
{
if (str.IsNull)
return SqlString.Null;
System.Text.StringBuilder sb = new
System.Text.StringBuilder(str.Value.Length);
for (int i=str.Value.Length-1; i>=0; i--)
sb.Append(str.Value[i]);
return new SqlString(sb.ToString());
}
Поскольку реализация самой функции примитивна, остановимся на том, что ее окружает.
Во-первых, к методу применен атрибут SqlFunc. Как и SqlProcedure, он позволяет указать средствам автоматического развертывания информацию, необходимую для правильного построения команды CREATE FUNCTION. В данном случае никаких параметров не использовано – атрибут просто указывает, что данный метод надо будет зарегистрировать как функцию. Более подробно мы рассмотрим возможности этого атрибута чуть позже.
А вот следующий атрибут – SQLFunction – уже используется «внутри» MS SQL Server для определения того, как можно эту функцию использовать. В таблице 3 приведено описание параметров этого атрибута:
Имя параметра
Описание
DataAccess
Какой доступ осуществляет функция к пользовательским данным в базе:DataAccessKind.None – никакого.DataAccessKind.Read – читает данные.
SystemDataAccess
Какой доступ осуществляет функция к системным данным в базе:SystemDataAccessKind.None – никакого.SystemDataAccessKind.Read – читает данные.
IsDeterministic
Является ли функция детерминистической, т.е. зависит ли ее возвращаемое значение только от переданных параметров.
IsPrecise
Выполняет ли функция округления в процессе работы.
Таблица 3.
В нашем случае ни к каким данным доступа не происходит, возвращаемое значение зависит только от переданного параметра, и значение является точным, а не приближенным.
ПРИМЕЧАНИЕ
Это позволяет использовать эту функцию в максимально широком контексте – например, можно создать вычисляемую колонку на ее основе, и даже индекс по этой колонке. Это может быть полезно для сортировки, например, списка получателей e-mail. Сортировка по обращенному адресу поставит рядом адреса в одном домене, и можно будет оптимизировать рассылку писем.
Возвращаем ISqlReader
Во многих случаях табличная функция выполняет роль параметризованного view – данные берутся из таблиц, и, после применения операторов SQL к исходным данным и параметрам, результат возвращается в вызывающий код. Создадим функцию, которая будет возвращать список изменений курса валют, произшедших в заданном диапазоне дат:
[SqlFunc(TableDefinition = "D datetime, course decimal(10, 4)")]
[SqlFunction(DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = false, IsPrecise = true)]
public static ISqlReader GetCourseChanges(DateTime start, DateTime end)
{
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = @"
select changeDate, course from Course
where changeDate between @start and @end";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
return cmd.ExecuteReader();
}
ПРЕДУПРЕЖДЕНИЕ
Увы, пока что мне не удалось заставить этот пример работать. Сервер неуклонно возвращает ошибку «Reader is closed». Каким образом избежать закрытия Reader после возвращения его серверу, я пока не понял.
Работаем с SqlResultSet
Для тех случаев, когда необходимо сформировать возвращаемый набор данных вручную, предусмотрен доступ к нему через метод контекста SqlContext.GetReturnResultSet(). Объект, возвращаемый этим методом, уже проинициализирован в соответствии с декларированной структурой функции. В него нужно добавить требуемые записи. В принципе, можно как добавлять, так и удалять/изменять записи, если это кажется необходимым. Воспроизведем поведение хранимой процедуры CurrencyCourse, созданной в конце предыдущего раздела:
[SqlFunc(TableDefinition = "D datetime, course decimal(10, 4) NULL")]
[SqlFunction(DataAccess = DataAccessKind.Read,
SystemDataAccess = SystemDataAccessKind.None,
IsDeterministic = false, IsPrecise = true)]
public static void GetCourseTable(DateTime start, DateTime end)
{
using (SqlCommand cmd = SqlContext.GetCommand())
{
cmd.CommandText = @"
select changeDate, course from Course
where changeDate between @start and @end";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
DateTime current = start;
SqlDecimal course = SqlDecimal.Null;
SqlResultSet source = cmd.ExecuteResultSet(ResultSetOptions.None);
SqlResultSet dest = SqlContext.GetReturnResultSet();
SqlDataRecord rec;
while (source.Read())
{
while (current < source.GetDateTime(0))
{
rec = dest.CreateRecord();
rec.SetSqlDecimal(1, course);
rec.SetDateTime(0, current);
dest.Insert(rec);
current = current.AddDays(1);
}
course = source.GetDecimal(1);
}
while (current <= end)
{
rec = dest.CreateRecord();
rec.SetDateTime(0, current);
rec.SetSqlDecimal(1, course);
dest.Insert(rec);
current = current.AddDays(1);
}
}
}
Обратите внимание, что теперь в атрибуте SqlFunction содержится значение свойства DataAccess = DataAccessKind.Read, указывая на то, что функция читает данные из базы.
ПРЕДУПРЕЖДЕНИЕ
Обратите внимание также на то, что на этот раз для доступа к данным мы используем SqlResultSet вместо SqlDataReader. Дело в том, что одновременно читать из базы и работать с возвращаемым набором записей нельзя – возникает исключение с сообщением о том, что данное соединение уже используется. Возможно, данная особенность поведения будет изменена при выпуске финальной версии. Но пока единственным способом написать подобную функцию является чтение данных целиком до начала формирования выходного набора данных.
Агрегирующие функции
Большинству разработчиков для построения своих приложений вполне хватает стандартного набора агрегирующих функций. Однако теперь настал праздник и для редких любителей сделать что-то необычное – в новом MS SQL Server можно реализовать свой способ выйти за пределы SUM, AVG и СOUNT.
Создаются они при помощи оператора CREATE AGGREGATE:
CREATE AGGREGATE [ schema_name. ] aggregate_name
( @param_name < input_sqltype > )
RETURNS < return_sqltype >
EXTERNAL NAME assembly_name [ :class_name ]
< input_sqltype > ::=
system_scalar_type | { [ udt_schema_name. ] udt_type_name }
< return_sqltype > ::=
system_scalar_type | { [ udt_schema_name. ] udt_type_name }
На этот раз написания одного метода недостаточно. Вместо этого для подсчета агрегатов используются объекты. Идея проста – по мере просмотра исходных данных мы накапливаем то, что нужно накапливать, а зетем выводим накопленное в выходной набор. Соответственно для каждого из этих действий нужно реализовать по методу:
Имя метода
Описание
public void Init()public void Init(input_type value)
Инициализирует объект. Вызывается один раз на группу агрегируемых значений. Если реализована версия метода с одним параметром, то SQL Server может использовать ее для передачи первого значения в группе. Тип параметра value (input_type) должен быть совместимым с тем типом, который указан как input_sqltype в операторе CREATE AGGREGATE.
public void Accumulate(input_type value)
После инициализации объекта, сервер вызывает этот метод по одному разу для каждого агрегируемого значения. (На список подаваемых на вход значений, помимо состава полей в операторе GROUP BY, оказывает влияние также и наличие ключевого слова distinct перед агрегируемым выражением. Как и для встроенных функций, это ключевое слово приведет к тому, что в список для каждой группы попадут только различные значения агрегируемого выражения). Тип параметра value должен быть совместимым с тем типом, который указан как input_sqltype в операторе CREATE AGGREGATE.
public return_type Terminate()
Несмотря на страшное название, этот метод всего лишь должен вернуть то самое агрегированное значение, которое было вычислено для группы входных значений. Тип результата должен быть совместимым с тем типом, который указан как return_sqltype в операторе CREATE AGGREGATE.
public void Merge(udagg_type group)
Этот метод предназначен для случаев, когда SQL Server создает больше одного агрегирующего объекта на одну группу входных значений. Например, при выполнении запроса на многопроцессорной машине, входные данные могут быть разделены на несколько потоков для одновременной обработки. Перед выводом данных необходимо выполнить слияние рассчитанных агрегатных значений. Именно это и делает этот метод. Он принимает единственный параметр того же класса, в котором объявлен.
Таблица 4.
Помимо этих методов, у класса должен быть определен конструктор без аргументов (иначе SQL Server не сможет создавать объекты этого класса). Кроме того, должна быть обеспечена возможность сериализации объектов – для случаев, когда серверу нужно сохранить промежуточный результат на диске. Мы отложим описание подробностей сериализации до следующего раздела, а пока что попробуем сделать свою функцию для вычисления среднего геометрического.
Тем, кто плохо помнит школьный курс, напомню, что среднее геометрическое из N чисел – это корень N-ной степени из их произведения. (А среднее арифметическое N чисел, которое обычно и подразумевается под термином «среднее значение» - это сумма этих чисел, деленная на N).
ПРИМЕЧАНИЕ
К сожалению, расчет среднего геометрического по определению очень быстро приводит к переполнению даже на очень небольших наборах входных данных – произведение растет слишком быстро. Поэтому мы схитрим и воспользуемся тем математическим фактом, что произведение N чисел равно экспоненте от суммы их логарифмов. Вместо извлечения корня степени N (а это то же самое, что и возведение в степень 1/N) мы поделим на N сумму логарифмов перед применением функции Exp().
[Serializable]
[SqlUserDefinedAggregate(Format.Native, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true)]
[StructLayout(LayoutKind.Sequential)]
public class AvgGeom: INullable
{
private double _agg;
private int _count;
private bool _isNull = true;
#region User-Defined Attribute Required Methods
public void Init()
{
_agg = 0;
_count = 0;
_isNull = true;
}
public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
{
_agg += System.Math.Log(Value.Value);
_count++;
_isNull = false;
}
}
public void Merge(AvgGeom Group)
{
if (!Group.IsNull)
{
_agg += Group._agg;
_count += Group._count;
_isNull = false;
}
}
public SqlDouble Terminate()
{
if (IsNull)
return SqlDouble.Null;
else
return new SqlDouble(System.Math.Exp(_agg / _count));
}
#endregion
#region INullable Members
public bool IsNull
{
get
{
return _isNull;
}
}
#endregion
}
В первую очередь обратим внимание на атрибут SqlUserDefinedAggregate, который предваряет описание нашего класса. В нем определено несколько параметров (таблица 5).
Имя параметра
Описание
Format
Формат сериализации объектов этого класса. Подробности – в следующем разделе.
MaxByteSize
Максимальный размер сериализованного объекта. Подробности – в следующем разделе.
IsInvariantToDuplicates
Зависит ли агрегированное значение от наличия дубликатов во входных данных (по умолчанию – да). Например, для функции MIN() совершенно неважно, сколько раз повторяются входные значения, а для функции SUM() – важно. Оптимизатор запросов SQL Server может использовать эту информацию для минимизации количества вызовов метода Accumulate.
IsInvariantToNulls
Влияет ли наличие NULL-значений во входных данных на агрегированное значение. Для большинства встроенных агрегирующих функций (кроме COUNT()) это так.
IsNullIfEmpty
Означает, что агрегирующая функция возвращает NULL для пустых входных наборов. Например, функция MIN при выполнении на пустом наборе возвращает как раз NULL , а функция COUNT() – 0.
IsInvariantToOrder
Данный параметр пока не документирован; судя по названию, он должен определять, влияет ли на результат порядок подачи значений в метод Accumulate(). См. примечание после таблицы
Таблица 5.
ПРЕДУПРЕЖДЕНИЕ
Все встроенные агрегирующие функции (а также наш пример) являются коммутативными, что позволяет серверу выбирать порядок сканирования входных данных по своему усмотрению. Однако, например, результат функций типа First() или Last(), (которые должны возвращать соответственно первое или последнее значение в наборе), очевидным образом зависит от порядка входных значений. Тем не менее, пока непонятно, как можно использовать подобные функции – дело в том, что синтаксис SQL не позволяет определять порядок агрегирования записей. Оператор ORDER BY применим только к выходному набору записей, и использовать в нем можно только те поля, по которым выполняется группировка. В обычных вложенных запросах (по результатам которых можно строить запросы с группировкой) применение ORDER BY запрещено. Скорее всего (это только мое предположение!) разработчики MS SQL Server Yukon предполагают использовать свойство SqlUserDefinedAggregateAttribute.IsInvariantToOrder для тех случаев, когда программист каким-либо способом все же может гарантировать определенное упорядочивание входных данных – это свойство должно убедить сервер воздержаться от переупорядочивания записей перед агрегированием. Пока что мне не удалось обнаружить какого-либо влияния этого свойства на поведение сервера.
Для того, чтобы наш объект мог принимать значение NULL, необходимо реализовать интерфейс INullable. Этот интерфейс определяет единственное read-only свойство bool IsNull. Все классы из System.Data.SqlTypes реализуют этот интерфейс. В нашем примере объект принимает значение NULL при инициализации, и перестает быть Null сразу, как только ему будет передано не-NULL значение в метод Accumulate или Merge.
Пользовательские типы данных
Систему типов SQL Server можно расширить с помощью пользовательских типов данных (User-defined Types, UDT). Пользовательские типы реализуются как управляемый класс на любом из CLR-языков и регистрируются в SQL Server. Такой тип можно использовать для определения типа колонки в таблице, или как переменную (параметр процедуры) в выражении Т-SQL. При этом методы объектов можно вызывать прямо из T-SQL.
Создание пользовательского типа данных
В T-SQL пользовательский тип данных регистрируется при помощи оператора CREATE TYPE:
CREATE TYPE [ type_schema_name. ] type_name
{ [ FROM base_type [ ( precision [ , scale ] ) | ( 'urn:schema-namespace' ) ]
[ NULL | NOT NULL ] ]
| [ EXTERNAL NAME [ assembly_schema_name. ] assembly_name [ :class_name ] ]
}
В операторе указывается имя класса из предварительно загруженной в базу сборки.
Альтернативой прямому использованию T-SQL, как и в других случаях, служит автоматическое развертывание проектов MS Visual Studio .Net Whidbey. Классы, помеченные атрибутом SqlUserDefinedType (мы подробно рассмотрим его чуть позже – при обсуждении сериализации) автоматически регистрируются в качестве пользовательских типов при развертывании проектов типа SQL Server Project.
Для того, чтобы класс .NET можно было использовать в качестве пользовательского типа данных SQL Server, он должен выполнять некоторые обязанности:
Иметь конструктор без параметров. Как правило, он возвращает экземпляр, соответствующий значению NULL (об этом далее).
Поддерживать NULL-значения. Класс должен реализовывать интерфейс INullable, который описан в предыдущем разделе. Также необходима реализация в классе статического свойства Null, котор