MS SQL Server 9 “Yukon”. Интеграция с .NET

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, которое возвращает NULL-объект этого класса, т.е. должно быть MyClass.Null.IsNull == true. Все методы должны корректно обрабатывать передачу в параметрах как экземпляра Null, так и значения null.

Поддерживать конверсию в строку и обратно: в классе должен быть определен метод Parse(SqlString s) и должным образом перекрыт метод ToString().

Поддерживать сериализацию. Поскольку объекты этого класса будут храниться на диске, необходимо обеспечить преобразование в «плоский» формат (а также восстановление).

Сериализация является настолько существенной частью поведения пользовательских типов, что ей посвящен отдельный подраздел этой статьи. А пока что мы попробуем создать свой несложный тип данных.

В качестве упражнения я реализовал тип «точка на плоскости». Он умеет представлять свои координаты как в декартовых, так и в полярных координатах.

using System; using System.Data.Sql; using System.Data.SqlTypes; using System.Text.RegularExpressions; using System.Runtime.InteropServices; [Serializable] [SqlUserDefinedType(Format.Native)] [StructLayout(LayoutKind.Sequential)] public class SqlPoint: INullable { #region NULLability private bool _isNull = true; public bool IsNull { get { return _isNull; } } public static SqlPoint Null { get { return new SqlPoint(); } } #endregion #region Конверсия в строку и обратно public override string ToString() { return IsNull? "null" : String.Format("X: {0}; Y: {1}", x, y); } public static SqlPoint Parse(SqlString s) { // Я не очень хорошо владею регулярными выражениями, // тем не менее, этот метод в состоянии преобразовать // результат вызова метода ToString обратно в SqlPoint. if (s.IsNull || s.Value.ToLower() == "null") return Null; SqlPoint p = new SqlPoint(); Regex t = new Regex(@"x:(?<x>\d*(\.\d+)?)\s*\W*y:(?<y>\d*(\.\d+)?)", RegexOptions.IgnoreCase); Match match = t.Match(s.Value); p.x = SqlDouble.Parse(match.Groups["x"].Value); p.y = SqlDouble.Parse(match.Groups["y"].Value); return p; } #endregion #region Наши данные private double _x=0; private double _y=0; public SqlDouble x { get { return IsNull ? SqlDouble.Null : new SqlDouble(_x); } set { if (!value.IsNull) { _x = value.Value; _isNull = false; } } } public SqlDouble y { get { return IsNull? SqlDouble.Null: new SqlDouble(_y); } set { if (!value.IsNull) { _y = value.Value; _isNull = false; } } } public SqlDouble R // А эти свойства мы будем вычислять. { get { return IsNull ? SqlDouble.Null : new SqlDouble(System.Math.Sqrt(_y*_y +_x*_x)); } set { if (value.IsNull) { _isNull = true; return; } double alpha = Alpha.IsNull? 0 : Alpha.Value; _x = System.Math.Cos(alpha) * value.Value; _y = System.Math.Sin(alpha) * value.Value; } } public SqlDouble Alpha { get { return (IsNull) ? SqlDouble.Null : new SqlDouble(System.Math.Atan2(_y, _x)); } set { if (value.IsNull) { _isNull = true; return; } double r = R.IsNull ? 0 : R.Value; _x = System.Math.Cos(value.Value) * r; _y = System.Math.Sin(value.Value) * r; } } #endregion }

После регистрации данного класса как пользовательского типа выполнение вот таких запросов:

declare @p SqlPoint set @p::x = 3 set @p::y = 4 select @p::x, @p::y, @p::R, @p::ToString() set @p::R = 10 select @p::x, @p::y, @p::R, @p::ToString() set @p::Alpha = 0 select @p::x, @p::y, @p::R, @p::ToString() set @p = convert(SqlPoint, ' x:6; y:5.00') select @p::x, @p::y, @p::R, @p::ToString()

Приводит к результатам, приведенным в таблице 6:

@p::x	@p::y	@p::R	@p::ToString()
3.0	4.0	5.0	X: 3; Y: 4
6.0000000000000009	7.9999999999999991	10.0	X: 6; Y: 8
10.0	0.0	10.0	X: 10; Y: 0
6.0	5.0	7.810249675906654	X: 6; Y: 5

Таблица 6

В приведенном выше примере продемонстрирована работа с членами объектов пользовательских типов. Синтаксис достаточно прост – оператором выбора члена объекта выступает двойное двоеточие.

Свойства

Свойства ведут себя очень похоже на переменные с такими «странными» именами. В соответствии с наличием в декларации свойства get и set его можно использовать в присваиваниях как справа, так и слева. Все свойства нашего класса поддерживают и чтение, и запись.

ПРЕДУПРЕЖДЕНИЕ Изменять состояние объекта в геттере свойства крайне не рекомендуется! Дело в том, что SQL Server предполагает, что чтение свойства не приведет к изменению состояния. Если вы прочитаете значение такого свойства у переменной, то новое состояние объекта доживет ровно до окончания выполнения геттера, а затем он будет возвращен в исходное состояние.

Методы

Все публичные методы класса будут доступны в T-SQL. Реализация методов предоставляет значительно большую гибкость, чем реализация свойств, но также накладывает на разработчика некоторую ответственность.

Константные методы

По умолчанию считается, что все методы являются константными, т.е. не изменяют состояния объекта. Это позволяет использовать их в операторах select – идеология SQL запрещает какие-либо изменения данных в читающих запросах. Иначе бы невозможно было обеспечить необходимые свойства изоляции транзакций. При этом так же, как и для геттеров свойств, сервер принудительно обеспечивает константность – изменения, произведенные в «обычном» методе будут отменены сразу после выполнения метода, даже для переменных T-SQL.

Для проверки этих рассуждений добавим в наш класс вот такой метод:

public SqlDouble ResetR(SqlDouble newR) { SqlDouble Result = R; R = newR; return Result; }

Попробуем воспользоваться «дырой» и неявно изменить состояние объекта:

declare @p SqlPoint set @p::x=3 set @p::y=4 select @p::R select @p::ResetR(10) select @p::R

Программисту на традиционных объектно-ориентированных языках естественно ожидать получения различных результатов во втором и третьем запросах – ведь вызов ResetR модифицирует приватное поле объекта. Увы, во всех трех случаях вернется одно и то же значение.

Неконстантные методы

Конечно же, SQL Server позволяет объектам иметь и неконстантные методы. Такие методы нужно помечать атрибутом SqlMethod со свойством IsMutator, установленным в true. При этом неконстантным методам запрещено возвращать какие-либо значения. Для иллюстрации реализуем «правильную» версию метода ResetR в нашем классе:

[SqlMethod(IsMutator=true, OnNullCall=false)] public void ResetR2(SqlDouble newR) { R = newR; }

Подробнее об атрибуте SqlMethod

Атрибут SqlMethod (System.Data.Sql.SqlMethodAttribute) унаследован от атрибута SqlFunction, рассмотренного ранее при описании функций. У него есть конструктор без параметров и два новых свойства. Одно из них, IsMutator, мы уже рассмотрели. Второе – OnNullCall – пока недокументировано; скорее всего речь идет об оптимизации выполнения запросов, при которой сервер может игнорировать вызовы методов на NULL-объектах. Тем не менее пока что мне не удалось добиться проявления каких-либо эффектов, связанных с этим свойством.

Сериализация

Поскольку любой сервер баз данных непрерывно перемещает данные из памяти на диск и обратно, первостепенной задачей является обеспечение эффективного механизма преобразования «живых» объектов в пригодный для хранения формат и обратно. Известно, что универсального решения не существует. Поэтому разработчики SQL Server предоставили программистам широкий выбор возможностей по управлению этим процессом.

Основу управления сериализацией закладывают обязательные для пользовательских типов атрибуты Serializable и System.Data.Sql.SqlUserDefinedTypeAttribute.

У второго из них есть следующие параметры:

Format

// using System.Data.Sql Format SqlUserDefinedTypeAttribute.Format {get; set}

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

Native - в этом случае MS SQL Server использует стандартный способ преобразования объекта в бинарное представление. Не требует от разработчика почти никаких усилий, и заявлен в документации как «самый эффективный в большинстве случаев». Для того, чтобы можно было использовать этот формат, все поля класса должны быть блиттируемыми. Этот специфичный для .NET термин означает наличие общего представления для управляемой и неуправляемой памяти. К счастью, встроенные скалярные типы, а также их массивы и структуры, построенные из них, являются блиттируемыми. Увы, тип System.String (как и все ссылочные типы) блиттируемым не является. Кроме ограничения по типам полей, класс должен быть помечен атрибутом [StructLayout(LayoutKind.Sequential)]. Для этого формата нельзя указывать параметр SqlUserDefinedTypeAttribute.MaxByteSize.

SerializeDataWithMetaData – в этом случае вместе с данными каждого объекта хранится также информация об их структуре. Этот формат по умолчанию установлен в пользовательских типах, созданных по шаблону Visual Studio (Project->Add New Item… User-Defined Type). Он не требует никаких дополнительных действий от разработчика, и не накладывает практически никаких ограничений на содержимое класса. Однако его эффективность заметно ниже, чем у Native формата – замеров я не производил, но длина бинарного представления объекта говорит сама за себя.

SerializeData – этот формат должен быть промежуточным между Native и SerializeDataWithMetaData. Идея в том, чтобы хранить структурную информацию ровно один раз на класс, а в представлениях объектов хранить только сами данные. Увы, текущая версия сервера не поддерживает этот формат

UserDefined – для тех, кто предпочитает полный контроль над происходящим. В этом случае параметр MaxByteSize является обязательным, а класс должен реализовать интерфейс IBinarySerialize. Теоретически, позволяет добиться сравнимой с Native-форматом производительности, при отсутствии ограничений на хранимые данные.

MaxByteSize

// using System.Data.Sql int SqlUserDefinedTypeAttribute.MaxByteSize {get; set}

Это свойство определяет максимально занимаемое бинарным представлением объекта количество байт. Его применение обязательно только в случае UserDefined-формата (поскольку в этом случае у сервера нет способа оценить размеры буфера, выделяемого для сохранения). Значение этого свойства не может превышать SqlUserDefinedTypeAttribute.MaxByteSizeValue.

IsFixedLength

// using System.Data.Sql bool SqlUserDefinedTypeAttribute.IsFixedLength {get; set}

Устанавливайте этот параметр, если все экземпляры класса занимают одинаковое количество байт при сохранении в бинарном представлении.

IsByteOrdered

// using System.Data.Sql bool SqlUserDefinedTypeAttribute.IsByteOrdered {get; set}

В тех редких случаях, когда результаты сравнения любых двух экземпляров пользовательского типа совпадают с результатами лексикографического сравнения их бинарных представлений, указание этого параметра позволит серверу выполнять операции сортировки и индексации. Его наличие означает, что для сравнения не нужно десериализовывать объекты. К сожалению, пока что MS SQL Server не поддерживает использования пользовательских типов, не являющихся двоично упорядоченными, в предикатах сравнения, операторах order by и group by, а также ограничениях ссылочной целостности.

Триггеры

Реализация триггеров в .NET не слишком отличается от реализации процедур, рассмотренной в начале этой статьи. Телом триггера будет служить статический метод класса, и все сказанное о параметрах и общении с внешним миром остается справедливым. Можно считать триггер частным случаем хранимой процедуры, которая вызывается по какому-либо событию.

Для создаются триггера на T-SQL существует соответствующий вариант оператора CREATE TRIGGER:

CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ...n ] | EXTERNAL NAME < method specifier > } } } < method_specifier > ::= assembly_name:class_name[::method_name]

Как в остальных случаях, механизм автоматического развертывания проектов в MS Visual Studio Whidbey предоставляет удобную альтернативу – атрибут SQLTrigger:

Имя параметра	Описание
string Name	Имя триггера, соответствует параметру trigger_name в T-SQL.
string ForClause	Событие, запускающее триггер. Например, "INSTEAD OF INSERT”, или “FOR CREATE_ASSEMBLY” (обратите внимание, что новые DDL-триггеры тоже поддерживаются)
string Target	Объект, с которым ассоциируется триггер. Для классических DML-триггеров это имя таблицы или view, для DDL-триггеров это либо “ALL SERVER” для перехвата всех событий в пределах сервера, либо “DATABASE”, чтобы ограничиться только текущей базой.

Таблица 7.

ПРИМЕЧАНИЕ Все три свойства этого атрибута – только для чтения. Их можно установить, воспользовавшись одним из двух перегруженных конструкторов: SqlTrigger(name, target, forClause) или SqlTrigger(target, forClause).

Приведем пример простого триггера, который будет срабатывать при создании таблиц:

[SqlTrigger ("DATABASE", "AFTER CREATE_TABLE")] public static void AttachAnotherTrigger() { SqlTriggerContext ctx = SqlContext.GetTriggerContext(); string xml = ctx.EventData.ToSqlString().Value; Regex p = new Regex("<object>(?<tablename>.*)</object>", RegexOptions.IgnoreCase); string tableName = p.Match(xml).Groups["tablename"].Value; SqlContext.GetPipe().Send(String.Format("Table {0} created\n", tableName)); using (SqlCommand cmd = SqlContext.GetCommand()) { cmd.CommandText = String.Format( @"create trigger {0}_insert on {0} for insert as external name TestingYukon:CTriggerTest::AnotherTrigger", tableName); cmd.ExecuteNonQuery(); } }

Этот несложный DDL-триггер с каждой создаваемой в текущей базе таблицей связывает DML триггер на вставку. Имя таблицы, на которой произошло срабатывание, извлекается из свойства SqlChars SqlTriggerContext.EventData. Это пока недокументированное (к сожалению) свойство предоставляет исчерпывающую информацию о событии, вызвавшем срабатывание триггера, в формате XML. Вот так выглядит типичное значение, попадающее в наш триггер:

<EVENT_INSTANCE> <PostTime>2004-01-15T04:13:59.600</PostTime> <SPID>56</SPID> <EventType>CREATE_TABLE</EventType> <Database>Northwind</Database> <Schema>dbo</Schema> <Object>testtrigger</Object> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>create table testtrigger(id int identity)&#x0D;</CommandText> </TSQLCommand> </EVENT_INSTANCE>

Из всех этих подробностей нас пока интересует только содержимое элемента <object>, которое вытаскивается банальным регулярным выражением (не сомневаюсь, что более искушенные разработчики не упустят случая применить XPath и XSLT). В этом примере выполнялся следующий T-SQL-скрипт:

create table testtrigger(id int identity) insert into testtrigger default values drop table testtrigger --drop trigger AttachAnotherTrigger on database

ПРЕДУПРЕЖДЕНИЕ Текущая версия MS Visual Studio Whidbey некорректно обрабатывает удаление DML – триггеров при автоматическом развертывании. В отличие от обычных триггеров при их удалении нужно указывать не только имя, но также и контекст (сервер или база данных) с которым связан триггер. Именно с этим связано наличие закомментированной строки в конце скрипта.

Помимо свойства EventData, у класса SqlTriggerContext есть еще два свойства.

Свойство TriggerAction (одноименного типа) предоставляет более удобный доступ к типу действия, вызвавшего срабатывание триггера, чем элемент <EventType>, содержащийся в EventData.

Для DML-триггеров доступно также свойство bool[] ColumnsUpdated – массив флагов, определяющих, какие из колонок подверглись изменению. Аналогичная функциональность в триггерах T-SQL достигается при помощи функции UPDATE().

Прямого доступа к псевдотаблицам inserted и deleted нет; но их можно прочитать используя SqlCommand, полученную уже знакомым нам методом GetCommand() класса SqlContext.

Вот полный текст класса, в котором объявлены оба триггера:

using System; using System.Data; using System.Data.Sql; using System.Data.SqlServer; using System.Data.SqlTypes; using System.Text.RegularExpressions; public class CTriggerTest { [SqlTrigger ("DATABASE", "AFTER CREATE_TABLE")] public static void AttachAnotherTrigger() { SqlTriggerContext ctx = SqlContext.GetTriggerContext(); Regex p = new Regex("<object>(?<tablename>.*)</object>", RegexOptions.IgnoreCase); string xml = ctx.EventData.ToSqlString().Value; string tableName = p.Match(xml).Groups["tablename"].Value; SqlContext.GetPipe().Send(String.Format("Table {0} created\n", tableName)); using (SqlCommand cmd = SqlContext.GetCommand()) { cmd.CommandText = String.Format( @"create trigger {0}_insert on {0} for insert as external name TestingYukon:CTriggerTest::AnotherTrigger", tableName); cmd.ExecuteNonQuery(); } } public static void AnotherTrigger() { SqlContext.GetPipe().Send("Row Insert Intercepted\n"); } }

Как нетрудно заметить, никакого атрибута методу AnotherTrigger не назначено – мы регистрируем его вручную. Кстати, еще одним преимуществом триггеров на .NET-языках по сравнению с T-SQL является возможность назначить одно и то же тело на различные объекты.

Информация к размышлению

Я включил в этот раздел результаты некоторых экспериментов с MS SQL Server Yukon, которые не очень хорошо вписываются в общую структуру статьи.

Yukon и Generics

Одним из наиболее интересных нововведений в .NET 1.2 являются Generic-и. Они позволяют облегчить повторное использование кода, вместе с тем повышая надежность приложений.

Поскольку программирование под Yukon требует именно этой версии .NET, логично поинтересоваться перспективами использования в нем данного новшества.

Увы, напрямую использовать Generic-тип в Yukon нельзя. В одном из своих первых экспериментов я пытался создать обобщенную агрегирующую функцию. Такой подход кажется вполне логичным – многие агрегирующие функции естественным образом обобщаются на произвольные типы данных. В примере, который я привел в данной статье, функция AvgGeom принимает и возвращает SqlDouble. При ее применении к числам с фиксированной запятой возникают ненужные накладные расходы на преобразование типов во время выполнения запроса. Писать же по версии этой функции для каждого числового типа – расточительно и просто скучно.

Однако, попытка зарегистрировать generic-класс в качестве агрегирующей функции не удалась. Такие классы Yukon просто «не видит». Это, в общем-то, логично – насколько мне известно, обязанность генерации «окончательного» класса по его generic-прототипу лежит на компиляторе. А его-то как раз в сервере нет! Поэтому использование конструкций вида GenericType<SQLDecimal> в параметре EXTERNAL NAME любого из операторов CREATE лишено смысла.

Следующим шагом стала попытка предоставить серверу обычный класс, унаследованный от специализированной generic-реализации. Увы, это тоже не привело к успеху – сервер «не видит» публичные унаследованные методы. Последним ударом в этот бубен стала такая попытка:

public new void Init() { base.Init(); }

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

ПРИМЕЧАНИЕ В данный момент это выглядит как простая недоработка. Возможно, это поведение будет исправлено в финальной версии.

Кстати, обратите внимание, что необходимая функциональность класса агрегирующей функции не выражена в терминах какого-либо интерфейса – это связано именно с тем, что типы параметров могут меняться. В терминах generic можно было бы выразить этот гипотетический интерфейс примерно так:

public interface IAggregating<input_type, return_type> : INullable { void Init(); void Accumulate(input_type value); return_type Terminate(); void Merge(IAggregating<input_type, return_type> other); }

Однако разработчики Yukon проигнорировали эту возможность. В итоге, пригодность класса для реализации агрегирующей функции проверяется только на этапе его регистрации в базе данных.

Yukon и ООП

Перспектива внедрения .NET-кода в SQL Server заинтересовала меня в первую очередь возможностями ООП, которого так остро порой не хватает в RDBMS. Однако радость моя оказалась преждевременной – из инкапсуляции, наследования и полиморфизма поддерживается только первая парадигма. Наследование и полиморфизм приходится оставить «за дверью», т.е. по ту сторону оператора CREATE ASSEMBLY.

Во-первых, в Yukon типы совместимы только сами с собой и с binary. Это означает, что если колонка в таблице продекларирована как TypeA, то никаких наследников этого типа туда положить нельзя. Увы. При регистрации в базе отношения между классами исчезают.

Во-вторых, полиморфизм в смысле виртуальных методов подразумевает поддержку наследования, а ее-то как раз и нету. То есть виртуальные методы в Yukon ничем не лучше невиртуальных. Даже самый тоталитарный вариант полиморфизма – перегрузка методов – не работает. В пользовательском типе нельзя использовать более одного публичного члена с заданным именем (то есть иметь можно, но попытка обращения к нему приведет к ошибке).

Yukon и индексеры

Использовать индексеры в рамках T-SQL нельзя. По крайней мере, способа это сделать я не нашел. Увы.

Yukon и метапрограммирование

Поддержка .NET-триггеров для метаданных вкупе с возможностью использовать бинарное представление сборки в операторе CREATE ASSEMBLY открывает широкие перспективы метапрограммированию. Я не экспериментировал с этой функциональностью, но теоретически кажется осуществимой разработка мета-кода, который будет динамически формировать сборки и классы при наступлении различных событий, поддерживая, таким образом, более сложные взаимосвязи между объектами базы данных, чем предоставлены Microsoft. Сценарии развертывания теперь могут быть практически неограниченно сложными, при внешней простоте. Одним из самых простых вариантов мне видится триггер на оператор CREATE ASSEMBLY, который будет анализировать содержимое сборки на предмет классов, размеченных соответствующими атрибутами, и выполнять автоматическую регистрацию соответствующих объектов в базе данных.

Yukon и другие

Не так давно в форуме RSDN промелькнула ссылка на статью Эндрю Айзенберга и Джима Мелтона, SQL-программы, использующие язык программирования JAVA, опубликованную издательством OSP почти пять лет назад. В ней упоминается стандарт SQL/PSM, предложенный в 1996 году. Как ни странно, но синтаксис новых конструкций T-SQL во многом похож на предлагаемый в этом стандарте. Основное отличие касается отсутствия параметра language, которое, скорее всего, объясняется неразличимостью языков в .NET. Это можно понять как намерение Microsoft поддерживать вавилонское столпотворение «снаружи» сервера, нивелируя языковые различия благодаря природе .NET.

Список литературы

Microsoft Development Environment Whidbey (8.0.30703.4),

Microsoft .NET Framework 1.2 (1.2.30703),

Microsoft SQL Server Yukon (9.00.608)

Microsoft Word 2003 (11.5604.5703)

Nescafe Gold (ТУ 9198-330-605473-98)

RSDN Authoring Pack (3.1)

Основная информация по теме статьи находится в MSDN и SQL Server Books Online.

Для подготовки данной применялись материалы сети Интернет из общего доступа