Как получить максимум от CLR

Три способа получения прибыли от новых функций SQL Server 2005

В SQL Server 2005 привнесено много нового, но для прикладного разработчика интеграция с .NET - самая ценная возможность. SQL Server 2005 может задействовать определяемые пользователем функции (UDF), агрегирования (далее UDA) и типы (далее UDT), хранимые процедуры и триггеры, написанные на языках CLR, таких как Visual Basic .NET и C#. О выдающихся достижениях в области программирования баз данных можно написать книгу, а многие из функций, реализуемых в CLR, недоступны сегодня в T-SQL. В этой статье я расскажу о том, как CLR работает в SQL Server 2005 и как лучше подойти к написанию и использованию кода CLR в SQL Server 2005. Мы рассмотрим три примера, в которых показано, как с помощью CLR заменить расширенные хранимые процедуры, представлена работа с триггерами CLR и использование CLR UDA. Эти простые примеры помогут разобраться в том, как пользоваться преимуществами новых разработок в SQL Server 2005.

Интеграция с CLR: взгляд в упор

Самое большое преимущество от интеграции с CLR состоит в том, что языки CLR, такие как Visual Basic и C#, можно использовать для работы со строкам и с API, такими, например, как .NET Framework Base Class Libraries (BCL), применять API для криптографии, работать с регулярными выражениями и вызывать внешние ресурсы, такие как файловая система Windows, Web-службы и Windows Event Viewer.

Другое преимущество заключается в том, что база данных SQL Server включает в себя CLR, и, таким образом, загрузка и выгрузка сборок происходит в домене приложений Appdomain, который поддерживается SQL Server. Домены приложений в основном образуют границы безопасности, обеспечивающие изоляцию, загрузку и выгрузку управляемого кода (т. е. кода, который выполняется в среде исполнения). Правильнее использовать Appdomains, а не расширенные хранимые процедуры sp_OA*. Например, потому, что процедуры sp_OA* могут обрабатывать COM-объекты и любой компонент может обратиться к внутренним структурам памяти сервера и остановить службу SQL Server. Введение Appdomains устраняет подобные риски, поскольку управляемый код изолирован границей Appdomains, что не дает ему возможности обращаться к внутренним структурам памяти сервера. С компилированным кодом CLR можно обращаться к системным ресурсам за пределами SQL Server и выполнять сложные математические расчеты, которые нельзя сделать в T-SQL. А благодаря поддержке языков T-SQL и CLR в SQL Server 2005 можно теперь ответить на любое требование бизнеса.

В первый раз, выполняя вызов управляемого кода в SQL Server 2005, запрос предписывает SQL Server загрузить CLR. После этого любой вызов управляемого кода выполняется в CLR. Назначение управляемой среды состоит в решении таких задач, как распределение памяти, обработка исключений и выполнение предписаний политики защиты SQL Server и CLR. Как уже упоминалось, через CLR управляемый код может обращаться к BCL, обеспечивая полный доступ для манипуляций со строками, регулярными выражениями, инфраструктурой Web-служб, вводом/выводом файлов и API для криптографии. BCL привносит высокую производительность, масштабируемость и систему защиты в приложения базы данных, созданных в среде .NET Framework.

Рисунок. Базовая архитектура SQL Server 2005

На рисунке показана архитектура SQL Server 2005 CLR. SQL OS (уровень абстракций SQL Server, лежащий выше ресурсов операционной системы) в первую очередь ответственна за обработку ключевых процессов, таких как транзакции; наложение атомарных, последовательных, изолированных, надежных (ACID) правил; управление блокировками; управление памятью; поддержка SQL Server Engine. CLR находится внутри среды SQL Server Engine. Уровень хостинга облегчает взаимодействие между SQL OS и CLR и прежде всего ответствен за загрузку и выгрузку сборки, управление памятью, выполнение предписаний модели защиты, определение числа распределенных потоков и нитей, обнаружение блокировок и формирование контекста исполнения. Введение языков CLR в SQL Server не удалось бы осуществить без изменений в SQL Server Engine, которые были сделаны для интеграции среды исполнения CLR.

Использование Visual Studio 2005

Применение Visual Studio 2005 облегчает разработку и развертывание объектов CLR, хотя оно и необязательно при разработке объектов, основанных на CLR. Чтобы начать работать с CLR в Visual Studio 2005, нужно открыть окно New Project и выбрать Database из списка Visual Studio installed templates, как показано на экране 1. Далее следует щелкнуть правой кнопкой мыши на выбранном проекте и указать новый элемент. Здесь можно увидеть список шаблонов объектов CLR, которые можно создавать в SQL Server 2005. Шаблоны с текстами кода Visual Studio значительно упрощают написание и помогают избежать рутинной работы при создании баз данных, основанных на CLR. Например, чтобы развернуть любую функцию или выражение как объект SQL Server 2005 CLR, следует объявить объект как Public Shared, доступный в пределах класса .NET, который инкапсулирует реализацию. Шаблоны автоматически добавляют необходимые атрибуты класса для развертывания объекта CLR.

Один раз построив проект, Visual Studio компилирует сборку и инкапсулирует в нее все сборки, на которые есть ссылки. При развертывании сборки загружается ее двоичный код в базу данных SQL Server, связанную с проектом. SQL Server 2005 также содержит новые для развертывания операторы DDL, о которых я расскажу чуть позже.

Замена расширенных хранимых процедур

Создание расширенных хранимых процедур, традиционное поле деятельности для специалиста по C ++, может быть трудным делом, а их использование - даже опасным. Как уже упоминалось, расширенные хранимые процедуры выполняются в том же контексте безопасности, что и SQL Server, и в окружении процессов SQL Server. Таким образом, плохо написанная неуправляемая расширенная хранимая процедура способна «свалить с ног» процесс сервера. Однако код на .NET - это строго типизированный код (т. е. он работает с типами только строго определенными способами, разрешающими совместимые определения типов данных и увеличивая способность к взаимодействию кода). Код также выполняется в границах домена приложений Appdomain, так что он не может обращаться к случайным областям памяти SQL Server и остальному коду .NET. Код .NET также более безопасен в применении, нежели расширенные хранимые процедуры. Чтобы лучше понять эту отличительную особенность и более четко представить себе мощь BCL, рассмотрим следующую расширенную хранимую процедуру:

exec xp_fixeddrives

Эта недокументированная расширенная хранимая процедура возвращает разделы жестких дисков (HDD) и количество свободного пространства, доступного на каждом из них. Если переписать эту хранимую процедуру на язык CLR вроде Visual Basic .NET или C#, потребуется использовать пространство имен System.IO, которое включает класс DriveInfo, содержащий необходимую информацию о свободном пространстве каждого диска. Класс DriveInfo обладает и другими свойствами, такими как AvailableFreeSpace, DriveFormat, TotalSize и VolumeLabel. Таким образом, при замене расширенной хранимой процедуры xp_fixeddrives расширяются функциональные возможности: добавляется способность обращаться к дополнительной информации на диске. С использованием расширенных хранимых процедур в SQL Server это сделать невозможно, а в ранних версиях нужно было создавать собственные расширенные хранимые процедуры, написание которых могло быть делом нелегким.

Давайте более подробно рассмотрим создание хранимой процедуры CLR, опираясь на листинг 1. Процедуры в классах Visual Studio 2005 следует создавать с префиксом SQLProcedure, который будет характеризовать этот класс. В шаблонах Visual Studio данный атрибут создается автоматически. Затем нужно получить окружение класса SQLPipe, который позволит управляемой хранимой процедуре возвращать результирующий набор данных вызывающей хранимой процедуре. Экземпляр класса SQLPipe доступен управляемым хранимым процедурам через класс SqlContext. Класс SqlContext предоставляет экземпляр контекста среды исполнения кода для поддержки свойств текущего подключения и контекста транзакции, под которым были вызваны объекты CLR. И класс SQLPipe, и класс SqlContext определены в пространстве имен System.Data. SqlServer, на которое автоматически ссылаются шаблоны Visual Studio. Затем следует сформировать коллекцию метаданных, определяющую типы данных полей, которые будут посланы методу SqlDataRecord и, позднее, запросу с помощью SQLPipe. Методы SendResultsStart, SendResultsRow и SendResultsEnd используются соответственно для запуска, отправления данных и закрытия передачи данных.

Использование триггеров CLR

Другая важная особенность CLR - это способность создавать триггеры, основанные на CLR. Скажем, нам нужно подтвердить корректность части данных в момент вставки, например, адреса электронной почты, который вводится пользователем. Сегодня в приложениях обычно используется громоздкая проверка правильности во внешнем интерфейсе, для которой требуется многократное подтверждение данных с уровня внешнего прикладного интерфейса. Но в SQL Server 2005 расширенный язык T-SQL позволяет вызывать хранимые процедуры как Web-службу, так что разработчики приложения для SQL Server 2005 должны использовать проверку достоверности на уровне интерфейса базы данных. CLR может пригодиться при построении трудоемкой системы на уровне базы данных. В этом примере, который приведен в листинге 1, показано, как создать CLR-триггер ValidateEmail.

Чтобы выполнить эту задачу, следует использовать регулярные выражения, как это сделано в тексте листинга 2 по метке А, которые доступны в среде .NET Framework. Регулярные выражения (кратко - regex или regexp) - это специальная текстовая строка, описывающая образец поиска. Регулярные выражения находятся в пространстве имен System.Text. Regular Expressions. Кроме того, как и в случае с хранимыми процедурами, нужно выставлять префикс классов триггера в атрибуте метода , как в тексте кода по метке B. В этом атрибуте описывается операция, которая вызовет триггер. Name - это имя триггера в процессе развертывания. А Target - имя таблицы, к которой привязан триггер по указанному событию. Шаблоны Visual Studio добавляют образцы атрибутов к классу, и соответствующий параметр следует изменить. Атрибуты важны на фазе развертывания, когда используется Visual Studio 2005.

Для проверки адреса электронной почты пользователь должен найти образец, который будет похож на xxxx@xxxx.com. Следует использовать SQLPipe для отправления данных обратно в SQL Server при выполнении хранимых процедур. Чтобы получить текст триггера, нужно применить метод GetTriggerContext, описанный в тексте кода по метке C. Однажды получив этот контекст, можно обращаться к таблицам INSERTED и DELETED в месте выполнения триггера SQL Server. Эти псевдотаблицы заполняются автоматически, как только оператор DML вроде INSERT, UPDATE или DELETE применяется к таблице базы данных. Для обращения к псевдотаблицам нужно создать объект SQLCommand по примеру того, как это сделано в метке D, и получить адрес электронной почты из поля EmailID псевдотаблицы INSERTED.

Однажды получив адрес из поля EmailID, можно использовать метод Regex.IsMatch, как показано в тексте по метке E, для проверки всех адресов электронной почты, которые соответствуют заданной маске. Проверка правильности при использовании онлайн-службы почтовых сообщений не выполняется. В применении подсоединенных систем для проверки правильности необходимости нет, и в этом состоит преимущество использования регулярных выражений.

В зависимости от значения возвращаемой величины (булево значение True или False), триггер либо заканчивает работу с ошибкой, как показано в коде по метке F, либо позволяет процессу вставить адрес электронной почты в таблицу. Если формат адреса неправильный, система выдает сообщение об ошибке, показанное на экране 2 (в листинге 5 представлен сценарий создания этого сообщения об ошибке). Заметим, что сообщение об ошибке включает в себя название триггера, так чтобы можно было использовать это сообщение в целях исправления. Добавим немного функциональных возможностей к триггеру, описанному в листинге 2. Если формат электронной почты правильный, можно посылать почтовое сообщение для уведомления пользователя о том, что адрес принят. Код листинга 6 создает сообщение о приеме.

Этот текст почтового сообщения будет выполняться синхронно с выполнением триггера. С этим дополнением к функциональности можно не только проверять адрес электронной почты, но также, используя CLR BCL, и добавлять адреса. Такая проверка правильности адреса из прикладного приложения в предыдущих версиях SQL Server была невозможна.

Использование CLR UDA

Другое важное нововведение в SQL Server 2005 CLR - функции UDA. В дополнение к стандартным функциям агрегирования в SQL Server, таким как SUM, COUNT, MIN и MAX, функции UDA позволяют создавать собственные функции агрегирования для целей консолидации значений в поле, а также для других сложных математических операций. Например, предположим, что у нас имеется таблица Department, которая содержит поле product_key, и требуется найти общее количество продуктов для данного отдела. Эта функция агрегации в SQL Server 2000 недоступна. Можно написать собственный код, чтобы получить результат, но большинство доморощенных решений довольно громоздки и могут использовать только временные таблицы с явной организацией циклов или сложные формулы с логарифмическими выражениями.

В листинге 3 показан текст кода на Visual Basic .NET. В тексте создается UDA, которая ищет значение в поле. Требуется открыть Visual Studio 2005 и добавить в проект User Defined Aggregate. В шаблоне будут перечислены структурные методы для создания UDA. Необходимые для этого UDA методы стандартны: Init, Accumulate, Merge и Terminate. Эти методы, которые выделены в листинге 3, являются вызовом, процессом агрегации, объединением (в случае параллелизма) и завершают позиции для функции агрегирования. Как в случае с другими уже созданными объектами CLR, название класса должно иметь префикс с атрибутом SqlUserDefinedAggregate, и в Visual Studio шаблон создает этот атрибут автоматически. Также нужно отметить такой класс, как Serializable. Типичный вызов нового UDA будет похож на следующий:

SELECT Dept_ID, dbo.clr
_product(product_key)
FROM #temp_dept
GROUP BY Dept_ID

На экране 3 показан результат работы этого запроса.

Экран 3. Результат работы листинга 4

Давайте рассмотрим другой путь, позволяющий высвободить возможности UDA. Скажем, таблица Department включает данные о сотрудниках для каждого отдела в виде списка, разделенного запятыми. Если использовать запросы на T-SQL для SQL Server 2000, потребуется создавать временные таблицы и задействовать все типы циклов для формирования значений, разделенных запятой, как сделано в сценарии листинга 7. А в CLR можно использовать конкатенацию UDA, как в листинге 4, и вызывать ее следующим простым выражением:

SELECT Dept_ID, dbo.clr
_concat(name)
FROM #temp_dept
GROUP BY Dept_ID

Экран 4. Строки с разделителями-запятыми

На экране 4 показан результат работы данного запроса. Сравнивая код из листинга 7 и листинга 8, можно подумать, что для создания UDA этого мало. Однако UDA представляет собой код многократного использования, который может применяться в базе данных для многократных запросов. Кроме того, код UDA более изящен и предусматривает меньшее число манипуляций со строками, которые приводят к перегрузке процессора сервера и не выполняются так же хорошо, как класс построения строк в .NET. Использование класса построения строк позволяет задействовать потенциал существующей среды .NET Framework в оптимизации работы со строками.

Развертывание объектов CLR

Объекты CLR можно развертывать в ручном или автоматическом режиме. В Visual Studio 2005 следует просто щелкнуть правой кнопкой мыши на проекте и выбрать Deploy из контекстного меню. Операция развертывания в Visual Studio 2005 использует привычные атрибуты SqlProcedure, SqlFunction, SqlTrigger и SqlUserDefinedAggregate и применяется для автоматического выполнения рутинной работы в базе данных. Чтобы увидеть список сборок для конкретной базы данных, следует зайти в Object Explorer в Visual Studio и выбрать базу данных, затем выбрать Programmability, Assemblies. В ручном режиме можно задействовать выражение DDL, как в листинге 8. Краткое описание системы безопасности .NET, используемой для сборок, приведено во врезке «Настройки безопасности для сборок CLR в SQL Server». Можно также дополнительно загрузить исходный текст сборок в SQL Server, чтобы воспользоваться отладкой объектов CLR.

Можно также получать данные о развернутых сборках CLR из системных таблиц SQL Server. Таблица sys.assemblies хранит информацию о сборках. Таблица sys.assembly_references содержит ссылки зависимостей к другим каталогизированным сборкам, а таблица sys.assembly_files содержит тексты сборок CLR (если, конечно, текст уже загружен).

Обычный способ использования системных таблиц состоит в получении информации об атрибутах сборок. Например, предложение из листинга 9 возвращает некоторые данные, связанные со сборкой, такие как версия и набор разрешений, под которыми эта сборка развернута. Сведения о параметрах настройки защиты, которые можно использовать для сборок .NET, изложены во врезке «Настройки безопасности для сборок CLR в SQL Server». Щелкнув правой кнопкой мыши по сборке и выбирая View Dependencies в SQL Server Management Studio, можно просмотреть список объектов CLR, которые были развернуты с кодом листинга 8.

T-SQL жив!

Хотя интеграция с CLR интересует разработчиков SQL Server, добавление языков .NET в SQL Server не подразумевает, что клиенту придется заменить весь свой код T-SQL. CLR может повышать производительность в некоторых случаях, что было невозможно в прежних редакциях SQL Server, поэтому реализация преимуществ CLR стоит затрат. Но SQL Server 2005 также включает существенные улучшения языка T-SQL, которые делают его более производительным: общие табличные выражения (common table expression, CTE), которые поддерживают рекурсивные запросы; PIVOT и UnPivot с поддержкой транспозиции строк в столбцы и наоборот; возможности сортировки; включение типа данных XML и XQuery. Эти возможности поднимают решения с использованием наборов на новый качественный уровень.

Чтобы определиться с тем, когда использовать язык T-SQL, а когда - язык CLR, нужно помнить, что расширения CLR более уместны, когда необходим высокий уровень вычислений или манипуляций с текстом. Для задач, имеющих дело с большим количеством данных, решения на T-SQL лучше, чем решения на CLR, потому что T-SQL более тесно работает с данными и не требует сложных транзакций (многочисленных переходов) между CLR и SQL OS. В таких процедурах, как шифрование, работа с текстом, операции ввода/вывода и вызов Web-служб, CLR предлагает задействовать мощные API с возможностями, которых нет в SQL Server 2000 и более ранних версиях. И непременно следует проверять каждый кусок текста кода с точки зрения безопасности, надежности и производительности. Использование .NET BCL позволяет любую задачу реализовать по-разному.

Настройки безопасности для сборок CLR в SQL Server

Для поддержки управляемого кода в базе данных SQL Server разработчики Microsoft создали специальные настройки безопасности для защиты важных объектов CLR, таких как сборки. Сборки представляют собой в основном управляемые библиотеки Dynamic Link Libraris (DLL), которые содержат метаданные и информацию о зависимостях и используются как единица развертывания. Поскольку сборки хранятся в базе данных, они резервируются и восстанавливаются вместе с ней.

Каждая сборка привязана к одному из трех наборов разрешений, которые необходимо указать в предложении CREATE ASSEMBLY DDL.

• SAFE - это стандартный набор разрешений, но он весьма жесткий. С набором SAFE возможен только доступ к данным в локальной базе данных для выполнения вычислений по ее данным.
• EXTERNAL_ACCESS - следующий уровень в иерархии разрешений. Этот набор позволяет осуществлять доступ к внешним ресурсам типа файловой системы, Windows Event Viewer и Web-службам. Этот тип доступа в SQL Server 2000 и более ранних версиях отсутствовал. Данный набор разрешений ограничивает операции наподобие доступа к указателю, что гарантирует устойчивость сборки.
• Набор UNSAFE предполагает полное доверие к сборке и не предусматривает ограничений. Этот набор напоминает подход к внешним хранимым процедурам: предполагается, что весь код - безопасный. Однако данный набор не допускает создания небезопасных сборок пользователями с разрешениями системного администратора. Разработчики Microsoft рекомендуют избегать создания небезопасных сборок, насколько это возможно.

Настройки SAFE и EXTERNAL_ACCESS подходят для требований данной статьи. Но возможны специфические ситуации, в которых может понадобиться вызов неуправляемого кода из сборки CLR. В этом случае придется задействовать набор UNSAFE. При использовании EXTERNAL_ACCESS, возможно, потребуются особые учетные данные для доступа к внешним ресурсам, к которым с вашей учетной записью в SQL Server доступ невозможен. В SQL Server 2000 и ранее приходилось в таком случае использовать учетную запись службы SQL Server, но в SQL Server 2005 можно применить команду CREATE CREDENTIALS и указать учетную запись Windows, а затем задействовать ALTER LOGIN для указания учетной записи SQL Server с теми данными, которые были созданы. В SQL Server 2005 Books Online (BOL) содержится дополнительная информация по использованию набора UNSAFE.

Заключение

В этой статье мы лишь поверхностно коснулись того, что можно делать при помощи объектов .NET 2.0 CLR в SQL Server 2005. Рекомендую использовать примеры, наглядно демонстрирующие, что можно сделать на уровне приложений для базы данных. Например, теперь обращаться к Web-службам можно внутри логики базы данных. И пользуйтесь объектами базы данных, которые можно создавать, применяя богатые возможности .NET 2.0 Framework и языков программирования.