О компании Услуги Портфолио. Выполненные проекты Поддержка Отзывы клиентов Контактная информация ООО Брутка: разработка программного обеспечения и продвижение Вашего бизнеса в Internet

Архив новостей


Регулярные выражения облегчают сопоставление шаблонов и извлечение данных

Н
есмотря на то что T-SQL является исключительно мощным языком обработки данных, он плохо приспособлен для анализа текста и манипуляций с ним. Попытки проведения анализа сколько-нибудь сложного текста с помощью встроенных строковых функций приводят к существенному утяжелению этих функций и сохраненных процедур, вследствие чего их становится трудно отлаживать и поддерживать. Но, может быть, существует более подходящий путь?
На самом деле гораздо более эффективное и элегантное решение предлагают регулярные выражения. Легко убедиться в том, как они могут быть полезны при сравнении текстов с целью идентификации записей, хотя при этом они способны на гораздо большее. Я покажу, каким образом можно решать разнообразные задачи, для которых средства SQL Server™ 2000 считались неудобными или неприменимыми, а теперь стали возможными с появлением SQL Server 2005, благодаря его поддержке хостинга среды CLR (common language runtime).
Регулярные выражения для SQL не новость. Oracle встроил регулярные выражения в базу данных 10g, и многие решения для баз данных с открытым исходным кодом также используют своего рода библиотеки регулярных выражений. По существу регулярные выражения могли бы использоваться и в более ранних версиях SQL Server, но механизм их обработки там был недостаточен.
С помощью сохраненной процедуры sp_OACreate может быть использован любой объект автоматического управления OLE с внедренными регулярными выражениями, однако сначала нужно создать объект COM, затем сделать, по крайней мере, один вызов IDispatch, а затем ликвидировать объект. Для большинства задач это было совершенно недостаточно и являлось причиной многих проблем, связанных с работоспособностью. Единственной альтернативой было создание расширенной сохраненной процедуры. Однако сегодня существует SQLCLR, определенная пользователем (user-defined function, UDF) функция CLR, которая позволяет создавать эффективный и менее подверженный ошибкам набор функций с помощью Microsoft® .NET Framework.
Определенные пользователем функции 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.

Сопоставление шаблонов
Определение соответствия строки шаблону представляет собой простейший вид использования регулярных выражений и, он легко реализуется.
Сначала используется поле 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, как будет показано далее, дает их гораздо больше.

Извлечение данных
Для извлечения данных из строки могут использоваться функции группировки регулярных выражений. Разработанная мной функция RegexGroup обеспечивает эту возможность для T-SQL:
[SqlFunction]
public static SqlChars RegexGroup(
SqlChars input, SqlString pattern, SqlString name )
{
Regex regex = new Regex( pattern.Value, Options );
Match match = regex.Match( new string( input.Value ) );
return match.Success ?
new SqlChars( match.Groups[name.Value].Value ) : SqlChars.Null;
}
Эта функция точно так же, как и функция RegexMatch, создает объект Regex. Однако вместо того, чтобы проводить тестирование на соответствие, для первого же найденного во входной строке соответствия создается объект Match. Объект Match используется для извлечения указанной группы. Если во входной строке никакого соответствия не было обнаружено, функцией возвращается пустое значение. Функция будет работать и в том случае, если вместо именованных групп будет отдано предпочтение использованию нумерованных. Просто передавайте целое значение функции внутри кода SQL, и оно будет неявно переводиться в тип nvarchar, в результате чего будет возвращаться соответствующая группа.
Для извлечения одних фрагментов данных из других можно использовать функцию RegexGroup в списке SELECT. Например, для столбца, в котором содержатся URL-адреса, можно легко провести их синтаксический разбор с целью нахождения характерных фрагментов. Данный запрос использует группировку для нахождения каждого отдельного сервера, занесенного в столбец Url таблицы UrlTable.
select distinct dbo.RegexGroup( [Url],
N'https?://(?<server>([\w-]+\.)*[\w-]+)', N'server' )
from [UrlTable]
Эту функцию можно также использовать в столбцах с вычисленными значениями. Следующее определение таблицы разделяет электронные адреса на почтовые ящики и домены.
CREATE TABLE [Email]
(
[Address] nvarchar(max),
[Mailbox] as dbo.RegexGroup( [Address],
N'(?<mailbox>[^@]*)@', N'mailbox' ),
[Domain] as dbo.RegexGroup( [Address], N'@(?<domain>.*)', N'domain' )
Столбец с почтовыми ящиками будет возвращать почтовый ящик или имя пользователя электронным адресом. Столбец с доменами будет возвращать домен электронного адреса.

Хранилище шаблонов
Все шаблоны, используемые этими функциями, представляют собой обыкновенные строки, и любая из них может храниться в таблице базы данных. Большинство баз данных, содержащих данные международного характера, имеют таблицу, представляющую страны. Добавив к такой таблице несколько дополнительных столбцов, можно хранить в ней тестовые шаблоны, специфичные для каждой конкретной страны. Это позволяет ввести ограничение для адресной строки, обеспечивающее варьирование по этой строке для данной страны.
В базах данных, хранящих данные от имени клиентов, как правило, уже имеется таблица, представляющая клиента. Такая таблица может использоваться для хранения группирующих шаблонов, описывающих способ, с помощью которого необработанные данные клиента сохраняются в базе данных, что позволяет создать вычисляемые столбцы действительно необходимых данных, получаемых из данных клиента. Например, если банковский счет клиента имеет уникальную структуру и нужно получить только отдельные фрагменты этого счета, можно легко создать выражение, позволяющее извлечь корректный фрагмент информации из счета каждого клиента.

Совпадения
Вместо того, чтобы определять, совпадает ли строка с шаблоном, иногда бывает более необходимо извлечь каждое совпадение. Раньше для этого типа извлечения потребовались бы итерационно перемещающиеся по строке курсоры. Этот процесс является достаточно медленным, а сам код труден для понимания и поддержки. Регулярные выражения представляют собой гораздо более подходящее средство для выполнения такой операции. Первостепенная проблема здесь – как возвратить все требуемые данные в рамках логической структуры SQL. Решением этой проблемы являются табличные функции.
Табличные функции отчасти похожи на функции, описанные выше, но имеют два существенных отличия. Во-первых, атрибуты, используемые в этом методе, должны полностью декларировать структуру возвращаемой таблицы. Во вторых, существует два таких метода. Первый метод возвращает счетный объект вместо фактического результата работы функции. Второй прогоняется с целью заполнения счетными объектами полей каждой строки. Каждое значение, получаемое от нумератора, должно соответствовать одной строке набора результатов. Интерфейс ICollection в .NET Framework обеспечивает выполнение IEnumerable, что означает возможность возвращения любого набора первым методом. Класс Regex содержит метод Matches, возвращающий объект MatchCollection, который можно использовать. Проблема с объектом MatchCollection состоит в том, что строка должна быть обработана полностью до возвращения результата методом Matches. SQL Server включает в себя оптимизацию, которая зависит от осуществляемой при необходимости обработки, так что вместо возвращения всего набора наперед, лучше использовать написанный самостоятельно нумератор, который возвращает каждое совпадение по требованию. Это решение на самом деле зависит от того, как используется функция, и должно быть надежно проверено до оптимизации нумератора.
Класс MatchNode возвращает отдельное совпадение в строке, отслеживая его положение в наборе возвращенных совпадений. Класс MatchIterator является счетным и управляет обработкой регулярного выражения. Он использует новое выходное ключевое слово для значительно более легкой процедуры создания нумератора, чем это было в предыдущих версиях оболочки. Каждое совпадение, обнаруженное внутри строки, он будет возвращать по требованию.
Метод RegexMatches возвращает новый класс MatchIterator. Арибут SqlFunctionAttribute метода RegexMatches также включает в себя несколько дополнительных свойств. Свойство TableDefinition устанавливается для определения таблицы функции. Свойство FillRowMethodName устанавливается для названия метода, который должен вызываться на каждой итерации возвращаемого счетного объекта. В данном случае этот метод называется FillMatchRow.
Для каждой итерации класса MatchIterator класс MatchNode передается методу FillMatchRow в качестве первого аргумента. Остальные параметры метода FillMatchRow должны декларироваться как выходные параметры и должны соответствовать определению таблицы, определенной в первой функции. Функция FillMatchRow попросту использует свойства MatchNode для заполнения полей данными.
Наконец, с помощью этой функции можно с легкостью извлекать из строки разнообразные фрагменты данных. Чтобы проиллюстрировать использование функции RegexMatches, с помощью приведенного ниже запроса обработаем строку и определим, сколько различных слов в ней содержится:
declare @text nvarchar(max), @pattern nvarchar(max)
select
@text = N'Here are four words.',
@pattern = '\w+'
select count(distinct [Text])
from dbo.RegexMatches( @text, @pattern )
Приведенный пример является достаточно исчерпывающим. Он демонстрирует определенный потенциал использования данной функции, а путем удаления явных ключевых слов он возвращает полное количество слов в строке. Многие веб-узлы ограничивают ввод текста некоторой его произвольной длиной. Вместо этого с помощью данного типа теста в сочетании с новой нотацией nvarchar(max) становится возможным ограничение ввода с помощью подсчета слов. Этот тип запроса может использоваться для различных нужд аналитической обработки, однако функция RegexMatches может быть также использована и для более общих задач. К сожалению, этот тип запроса также представляет собой излишне усердное использование регулярных выражений. Операция разбиения, выполняемая в данном случае с помощью выражения "\w+", могла бы быть легко выполнена с помощью метода String.Split, который работает гораздо быстрее. Регулярные выражения – это мощное средство, но прежде чем их использовать, следует понять, насколько оправдано их применение: в наличии могут быть и более простые инструменты, которые в отдельных случаях можно использовать с большей эффективностью.
На форумах MSDN® часто обсуждаются вопросы о том, каким образом можно передавать список значений сохраненной процедуре. Кроме того, мне приходилось встречать различные сложные методы передачи такого списка в текущий список для установления коррелирующих записей. Должен сказать, что функция RegexMatches реализует гораздо более чистый подход.
declare @pattern nvarchar(max), @list nvarchar(max)
select @pattern = N'[^,]+', @list = N'2,4,6'
select d.* from [Data] d
inner join dbo.RegexMatches( @list, @pattern ) re
on d.[ID] = re.[Text]
Этот шаблон совпадает с любой группой символов, не содержащей запятые. Для таблицы с именем Data, содержащей столбец ID с целыми значениями, этот запрос будет возвращать каждую идентифицированную в списке запись. Он становится более полезным при рассмотрении особенностей неявного приведения типов в SQL Server. Этот же запрос можно использовать для целочисленных данных, а также для данных типа "дата/время", GUID и с плавающей запятой. В других обеспечивающих большую гибкость методах обработки списка значений требуется применение различных функций и сохраненных процедур. Эта функция применима для списков, не использующих запятую в качестве разделителя. Она также может обрабатывать списки, использующие в качестве разделителя пробел, точку с запятой, клавишу табуляции, возврат каретки или любые другие идентифицируемые символы.

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

2007-03-23

 

Архив новостей: новости IT, описание технологий, цены