Сопоставление шаблонов

Определенные пользователем функции CLR

Определенные пользователем функции CLR являются попросту статическими методами (общие функции в Visual Basic), определенными внутри сборки.NET. Чтобы использовать объекты SQLCLR, необходимо зарегистрировать сборку в SQL Server с помощью нового оператора CREATE ASSEMBLY, а затем создавать каждый объект, указывая на его исполнение в сборке. С целью поддержки создания определенных пользователем функций CLR применение оператора CREATE FUNCTION было расширено для функций. При использовании SQL Server Project среда разработки Visual Studio® 2005 для упрощения процесса берет на себя от имени пользователя все регистрационные процедуры. Этот тип проекта отличается от большинства проектов Visual Studio, поскольку при попытке отладки (или запуска без отладки) проект перекомпилируется и получающаяся сборка, как и все определенные в ней объекты SQLCLR, устанавливаются и регистрируются в SQL Server. Затем интегрированная среда разработки запускает тестовый сценарий, предназначенный для данного проекта. Для упрощения процесса отладки как в сценарии SQL, так и в.NET-коде пользователя могут быть установлены точки прерывания.

Добавление функции происходит практически так же, как и добавление нового класса к какому-либо другому типу проекта. Нужно просто добавить в проект новый элемент, а затем при появлении запроса выбрать User-Defined Function. К частичному классу, содержащему все функции пользователя, добавляется новый метод. Новый метод также будет иметь собственный атрибут SqlFunction. Он используется средой Visual Studio для создания SQL-операторов, необходимых при регистрации функции. Поля IsDeterministic, IsPrecise, DataAccess и SystemDataAccess атрибута SqlFunction также используются для различных целей SQL Server.

Сопоставление шаблонов

Определение соответствия строки шаблону представляет собой простейший вид использования регулярных выражений и, как видно из рис. 1, он легко реализуется.

Figure 1 Сопоставление строк

Копировать код

public static partial class UserDefinedFunctions

{

public static readonly RegexOptions Options =

RegexOptions.IgnorePatternWhitespace |

RegexOptions.Singleline;

[SqlFunction]

public static SqlBoolean RegexMatch(

SqlChars input, SqlString pattern)

{

Regex regex = new Regex(pattern.Value, Options);

return regex.IsMatch(new string(input.Value));

}

}

Сначала используется поле Options для сохранения параметров регулярного выражения применительно к функциям. В данном случае выбраны RegexOptions.SingleLine и RegexOptions.IgnorePatternWhitespace. Первый из них устанавливает однострочный режим, а второй исключает неизолированные пробелы из регулярного выражения и помечает комментарии значком "решетка". Другой параметр, которым, возможно, захочется воспользоваться после тщательного размышления и анализа, является RegexOption.Compiled. При использовании Compiled с тяжело обрабатываемыми выражениями, пока их количество не так велико, существенный рост эффективности будет налицо. Определенно должны компилироваться выражения, используемые многократно. Однако не следует использовать параметр Compiled с регулярными выражениями, которые эксплуатируются лишь изредка, поскольку он приводит к повышенным издержкам на автозагрузку и перегрузке памяти. У пользователя может возникнуть соблазн расширить мою функцию RegexMatch общего назначения еще одним параметром, определяющим, следует ли компилировать данное выражение; таким образом в каждом конкретном случае пользователь может решать, насколько оправданы потери эффективности вследствие введения этой дополнительной нагрузки.

После задания применяемых параметров RegexOptions определяется функция RegexMatch, где вместо типа данных SqlString используется SqlChars. Тип данных SqlString преобразуется в тип nvarchar(4,000), тогда как SqlChars преобразуется в тип nvarchar(max). Новая функциональная возможность задания максимального размера допускает увеличение строк до значений, превышающих предел SQL Server 2000, равный 8000 байт. Для большей гибкости nvarchar(max) в настоящей статье используется в как можно более обобщенном виде. Однако, если соответствующие строки содержат менее 4000 символов, эффективность при использовании nvarchar(4,000) может быть значительно выше. Поэтому сначала необходимо оценить конкретные потребности и программировать код с их учетом.

Остальная часть кода для данного метода довольно проста. Создается экземпляр Regex с определенными параметрами и готовым шаблоном, после чего для определения, соответствует ли шаблону введенное значение, применяется метод IsMatch. Теперь необходимо добавить к тестовому сценарию простой запрос:

Копировать код

select dbo.RegexMatch(N'123-45-6789', N'^\d{3}-\d{2}-\d{4}$')

Шаблоном в этой инструкции является простой тест на личный номер в системе страхования США. Установите точку прерывания для нового запроса, после чего начинайте отладку работы функции. Эта функция позволяет выполнять множество различных тестов, но здесь мы рассмотрим лишь некоторые особенности, которые большинство людей не принимает во внимание. Например, очень важно придерживаться соглашения о присвоении имен, действующего в пределах базы данных, а написание запроса для подтверждения того, что все сохраненные процедуры удовлетворяют нормативам данной организации, является затруднительным. Функция RegexMatch решает эту задачу гораздо проще. Например, так выполняет эту задачу следующий тест запроса:

Копировать код

select ROUTINE_NAME

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE = N'PROCEDURE'

and dbo.RegexMatch(ROUTINE_NAME,

N'^usp_(Insert|Update|Delete|Select)([A-Z][a-z]+)+$') = 0

Этот запрос проверяет, каждая ли сохраненная процедура имеет перед своим именем префикс "usp_", за которым следует "Insert", "Update", "Delete" или "Select". Он также проверяет, чтобы каждое слово в названии объекта начиналось с заглавной буквы. Сравните те четыре строки с этой сверхупрощенной версией, использующей только встроенные функции:

Копировать код

select ROUTINE_NAME

from INFORMATION_SCHEMA.ROUTINES

where ROUTINE_TYPE = N'PROCEDURE'

and (LEN(ROUTINE_NAME) < 11

or LEFT(ROUTINE_NAME, 4) <> N'usp_'

or SUBSTRING(ROUTINE_NAME, 5, 6) not in

(N'Insert', N'Update', N'Delete', N'Select'))

Даже несмотря на то, что этот запрос в большей степени является кодом, он, фактически, упускает несколько функций, присутствующих в версии с регулярными выражениями. Во-первых, он нечувствителен к регистру символов, так что использование сопоставлений внутри запроса на выполнение тестов может привести к неверным результатам. Во-вторых, он не выполняет некоторые тесты на фактическое имя объекта, содержащееся в имени процедуры. В-третьих, все четыре строки, тестируемые в запросе, имеют длину шесть символов, что позволило упростить код извлечением отдельной подстроки длиной шесть символов и использовать ее во всех допустимых операциях сравнения. Это не является проблемой в данном конкретном примере, поскольку все имена команд состоят из шести символов, но представьте себе стандарт, который задает более сложные глаголы, наподобие "Get", "List" или "Find". Этими глаголами легко манипулирует функция RegexMatch, поскольку они являются всего лишь дополнением к списку.

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

Копировать код

CREATE TABLE [Account]

(

[AccountNumber] nvarchar(20) CHECK (dbo.RegexMatch(

[AccountNumber], '^[A-Z]{3,5}\d{5}-\d{3}$') = 1),

[PhoneNumber] nchar(13) CHECK (dbo.RegexMatch(

[PhoneNumber], '^\(\d{3}\)\d{3}-\d{4}$') = 1),

[ZipCode] nvarchar(10) CHECK (dbo.RegexMatch(

[ZipCode], '^\d{5}(\-\d{4})?$') = 1)

)

Содержимое столбца AccountNumber проверяется на соответствие произвольной договоренности о том, оно должно начинаться с трех-пяти букв, за которыми следует пять цифр, затем тире и еще три цифры. Как телефонные номера, так и почтовые индексы, проверяются на соответствие стандартам США для форматов телефонных номеров и почтовых индексов. Функция RegexMatch предоставляет множество возможностей для SQL Server, однако, внедрение регулярных выражений в.NET, как будет показано далее, дает их гораздо больше.