Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Изменения:

- Добавлена скалярная функция dbo.Regex_Match
- В существующую табличную функцию dbo.Regex_Matches в результирующий набор добавлена колонка group_name (при использовании regex named groups там будет имя группы)
- Пофиксена какая-то мелочь...
21 май 15, 23:55    [17673944]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Пардон. Предыдущие скрипты:

regex_update-22.05.2015.sql
regex_install-22.05.2015.sql

содержат Debug сборку.

Поверх нужно накатывать этот скрипт, в нем Release сборка.

К сообщению приложен файл (regex_fix-22.05.2015.sql - 56Kb) cкачать
22 май 15, 00:24    [17673987]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Использование Named Capturing Groups в ext.Regex_Matches

+ Named Capturing Groups


Подробнее о Named capturing Groups можно прочитать тут

В ext.Regex_Matches добавлена колонка group_name. В этой колонке содержится имя группы, которой соответствует значение в колонке value текущей строки.

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
)
select
    id, group_name, value
from
    t0
        cross apply
     ext.Regex_Matches
     (
         d,
         -- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	 N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)',
	 NULL
     ) m




id group_name value
----------- ------------------------------ ------------------------------
1 0 house==155
1 house 155
1 0 postal==600025
1 postal 600025
1 0 city==Владимир
1 city Владимир
1 0 street==Октябрьская
1 street Октябрьская
2 0 postal==773
2 postal 773
2 0 city==Певек
2 city Певек
2 0 street==Красная
2 street Красная
2 0 house==3423
2 house 3423
3 0 city==Владивосток
3 city Владивосток
3 0 postal==748
3 postal 748
3 0 street==Мира
3 street Мира
3 0 house==554
3 house 554
4 0 street==Октябрьская
4 street Октябрьская
4 0 postal==858
4 postal 858
4 0 city==Казань
4 city Казань
4 0 house==443
4 house 443

Используя pivot можно вывернуть эти данные в строки:

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
),
t1 as
(
    select
        id, group_name as [col_name], value
    from
        t0
            cross apply
	ext.Regex_Matches
	(
	    d,
            -- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	    N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)',
            NULL
	) m
)
select *
from
    t1
        pivot
    (max(value) for [col_name] in (city, street, house, postal)) p;




id city street house postal
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
1 Владимир Октябрьская 155 600025
2 Певек Красная 3423 773
3 Владивосток Мира 554 748
4 Казань Октябрьская 443 858

(4 row(s) affected)




+ update обертки в приложении (мелкие исправления).

К сообщению приложен файл (regex-23.05.2015.zip - 17Kb) cкачать
22 май 15, 12:47    [17676179]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ ...
В этой версии была добавлена поддержка колонки group_name для Named Capturing Groups. Внутри обертки пришлось отказаться от вызова статического метода Regex.Matches и на каждый вызов пересоздавать Regex объект. Только имея экземпляр объекта Regex, можно получить доступ к названиям Capturing Groups. Публичные конструкторы класса Regex под капотом разбирают регулярку, создают промежуточный код, который потом интерпретируется. На это тратится время. Тоже самое делают и статические методы, НО под капотом они используют закрытый конструктор Regex, который имеет дополнительный параметр, указывающий нужно ли переиспользовать уже созданный ранее код регулярки.

https://msdn.microsoft.com/en-us/library/8zbs0h2f(v=vs.110).aspx
To improve performance, the regular expression engine maintains an application-wide cache of compiled regular expressions. The cache stores regular expression patterns that are used only in static method calls.


Рефлектор показывает тоже самое. Была мысль поступать также, но вызвать закрытый конструктор Regex, не потеряв в производительности не удалось (Reflection). Если у кого то есть мысли, исходники открыты на githab'е.


Потому код разделен на три ветки:

https://github.com/churupaha/RegexWrapper
SAFE сборка. Состояние кода соответствует этому посту 17676179
т. е. в ней сидит описанная выше проблема с пересозданием объектов (ext.Regex_Matches пересоздает объекты Regex
с нуля при каждом вызове обертки), но так как здесь
производительность кого-то устроила, было решено оставить этот вариант.


https://github.com/churupaha/RegexWrapper/tree/v1
SAFE сборка. Совместима со всем, что написано в этой теме, за исключением 17676179.
Поддержка колонки group_name нет. Обертка ext.Regex_Matches под капотом вызывает статический Regex.Matches. т. е. не содержит описанной выше проблемы.



https://github.com/churupaha/RegexWrapper/tree/v2
UNSAFE сборка. Тоже самое, что и ветка v1, но было добавлено новое API в схеме regex. Оно решает следующие проблемы:

1) Несмотря на то, что статические методы под капотом используют ранее сгенерированный код регулярки, сами объекты Regex создаются при каждом вызове. В закрытом конструкторе Regex устанавливаются ссылки на уже существующий в кеше код регулярки (проверено рефлектором). При обработке большого количества строк, это будет приводить к замусориванию .NET Heap объектами Regex, что будет приводить к частым срабатываниям GC.

2) Поддерживается колонка group_name, как pltcm 17676179, при этом нет постоянного пересоздания класса Regex.

3) Поддержка опции Compiled Regular Expressions.

https://msdn.microsoft.com/en-us/library/8zbs0h2f(v=vs.110).aspx
By default, the regular expression engine compiles a regular expression to a sequence of internal instructions (these are high-level codes that are different from Microsoft intermediate language, or MSIL). When the engine executes a regular expression, it interprets the internal codes.

If a Regex object is constructed with the RegexOptions.Compiled option, it compiles the regular expression to explicit MSIL code instead of high-level regular expression internal instructions. This allows the.NET Framework's just-in-time (JIT) compiler to convert the expression to native machine code for higher performance.

However, generated MSIL cannot be unloaded. The only way to unload code is to unload an entire application domain (that is, to unload all of your application's code.). Effectively, once a regular expression is compiled with the RegexOptions.Compiled option, the .NET Framework never releases the resources used by the compiled expression, even if the regular expression was created by a Regex object that is itself released to garbage collection.

You must be careful to limit the number of different regular expressions you compile with the RegexOptions.Compiled option to avoid consuming too many resources. If an application must use a large or unbounded number of regular expressions, each expression should be interpreted, not compiled. However, if a small number of regular expressions are used repeatedly, they should be compiled with RegexOptions.Compiled for better performance. An alternative is to use precompiled regular expressions. You can compile all of your expressions into a reusable DLL by using the CompileToAssembly method. This avoids the need to compile at runtime while still benefiting from the speed of compiled regular expressions.



Дополнительное API представлено следующими хранимками и функциями:

-- Создает объект Regex и возвращает его handle
regex.Alloc @pattern, @options, @handle output

-- Переиспользуют объект Regex
regex.Matches	@handle, @input
regex.Split	@handle, @input
regex.IsMatch	@handle, @input
regex.Match	@handle, @input
regex.Replace	@handle, @input, @replacement

-- Освобождает объект Regex, соответствующий handle'у
regex.Free @handle


т. е. объект Regex, представленный handle'ом, переиспользуется функциями. Передача некоего числа, описывающего
объект в .NET Heap в TSQL-код, да так, чтобы объект оставался на своем месте, а не был собран как мусор GC и прочее
между вызовами функций заставило сделать сборку UNSAFE.

О параллельных планах https://msdn.microsoft.com/en-us/library/6h453d2h(v=vs.110).aspx
The Regex class itself is thread safe and immutable (read-only). That is, Regex objects can be created on any thread and shared between threads; matching methods can be called from any thread and never alter any global state.


Итого, в этой ветке поддерживается два API в схемах:

1) ext - stateless API (которое было ранее)
2) regex - дополнительное statefull API



Пример адаптированного варианта 17676179 под дополнительное API

declare
	@rh bigint;

exec regex.Alloc
	-- здесь значения параметров выделены в именованные группы (?<group_name>[^|]*)
	@pattern = N'postal==(?<postal>[^|]*)|city==(?<city>[^|]*)|street==(?<street>[^|]*)|house==(?<house>[^|]*)', 
	@options = null, 
	@handle = @rh output;

with 
t0(id, d) as
(
    select 1, N'house==155||postal==600025||city==Владимир||street==Октябрьская||' union all
    select 2, N'postal==773||city==Певек||street==Красная||house==3423||' union all
    select 3, N'city==Владивосток||postal==748||street==Мира||house==554||' union all
    select 4, N'street==Октябрьская||postal==858||city==Казань||house==443||'
),
t1 as
(
    select
        id, group_name as [col_name], value
    from
        t0
            cross apply
	regex.Matches
	(
	    @rh,
	    d
	) m
)
select *
from
    t1
        pivot
    (max(value) for [col_name] in (city, street, house, postal)) p;

exec [regex].[Free] @handle = @rh;


Это дополнительное API сам толком не использовал, оно, экспериментальное. Может кому-то понадобится. Скрипты для установки будут позже.
17 июн 15, 23:29    [17785250]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
+ к предыдущему посту

К сообщению приложен файл. Размер - 11Kb
17 июн 15, 23:35    [17785280]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
В архиве код из одноименных веток см. 17785250

SAFE install-18.06.2015\safe\

regex-install-safe-master-18.06.2015.sql
regex-install-safe-v1-18.06.2015.sql
regex-uninstall.sql




UNSAFE install-18.06.2015\unsafe\

regex-install-unsafe-v2-18.06.2015.sql
RegexWrapperPublicKey.snk
regex-uninstall.sql




+ как установить UNSAFE сборку

Чтобы поставить UNSAFE. Правильнее всего - самим просмотреть код, сгенерировать свою пару ключей скомпилировать/подписать/раздать права/установить.


Но если это тестовый сервер для "поиграться", то можно использовать публичный ключ из архива RegexWrapperPublicKey.snk, соответствующим закрытым ключем подписана сборка.

1) раздать права, например, так

use master;
go

create asymmetric key __RegexWrapper from file = 'C:\temp\RegexWrapperPublicKey.snk';
go

create login __RegexWrapper from asymmetric key __RegexWrapper;
go

grant unsafe assembly to __RegexWrapper;
go


2) Выполнить regex-install-unsafe-v2-18.06.2015.sql из архива

Как будет время, выложу какие-нибудь тесты с Compiled Regular Expressions (когда в функцию regex.Alloc передается параметр @options = '... | Compiled | ...'.




К сообщению приложен файл (install-18.06.2015.zip - 32Kb) cкачать
18 июн 15, 09:30    [17785946]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
Тест Compiled vs Interpreted Regex

regex_type        exec_count  avg_cpu_time_ms avg_elapsed_time_ms total_cpu_time_ms total_elapsed_time_ms
----------------- ----------- --------------- ------------------- ----------------- ---------------------
COMPILED REGEX 50 2584 2641 129234 132080
INTERPRETED REGEX 50 3388 3423 169437 171188

(2 row(s) affected)

Все сильно зависит от регулярки, данных, их объема и т. п..

+ Исходник теста (данные теста в архиве)

:setvar data_file "c:\temp\compiled_regex_test\data.txt"
:setvar compiled_res_file "c:\temp\compiled_regex_test\compiled_res.txt"
:setvar interpreted_res_file "c:\temp\compiled_regex_test\interpreted_res.txt"
:setvar executions "50"

/*

	Тестируем INTERPRETED REGEX, результаты выводим в файл $(interpreted_res_file)

*/
:out $(interpreted_res_file)
go

-- Загружаем данные. ~ 2Mb см. в приложении data.txt
declare
	@data nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(data_file)', single_nclob) as t);

declare
	@rh bigint,
	@dummy int,
	@cnt int;

-- создаем регулярку
exec regex.Alloc @pattern = N'\b(\w+((\r?\n)|,?\s))*\w+[.?:;!]', @options = N'Multiline', @handle = @rh output;

-- первый прогон
select @dummy = 1
from
	regex.Matches(@rh, @data) t;

-- $(executions) раз выполняем одно и то же регулярное выражение
set @cnt = $(executions);

while (@cnt > 0)
begin
	set statistics time on;

	select @dummy = 1
	from
		regex.Matches(@rh, @data) t;

	set statistics time off;

	set @cnt -= 1;
end;

exec regex.Free @rh;
go

/*

	Тестируем COMPILED REGEX, результаты выводим в файл $(compiled_res_file)

*/
:out $(compiled_res_file)
go

-- Загружаем данные. ~ 2Mb см. в приложении data.txt
declare
	@data nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(data_file)', single_nclob) as t);

declare
	@rh bigint,
	@dummy int,
	@cnt int;

-- создаем регулярку
exec regex.Alloc @pattern = N'\b(\w+((\r?\n)|,?\s))*\w+[.?:;!]', @options = N'Multiline | Compiled', @handle = @rh output;

-- первый прогон
select @dummy = 1
from
	regex.Matches(@rh, @data) t;

-- 50 раз выполняем одно и то же регулярное выражение
set @cnt = $(executions);

while (@cnt > 0)
begin
	set statistics time on;

	select @dummy = 1
	from
		regex.Matches(@rh, @data) t;

	set statistics time off;

	set @cnt -= 1;
end;

exec regex.Free @rh;
go

:out stdout
go

declare
	@interpretted_res nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(interpreted_res_file)', single_clob) as t),
	@compiled_res nvarchar(max) = (select bulkcolumn from openrowset(bulk N'$(compiled_res_file)', single_clob) as t);

with t0 as
(
	select
		d.regex_type,
		r.match, 
		r.[group], 
		cast(r.value as int) as value
	from
		(
			values
			(N'INTERPRETED REGEX', @interpretted_res),
			(N'COMPILED REGEX', @compiled_res)
		) as d(regex_type, res_data)
			cross apply
		ext.Regex_Matches(d.res_data,  N'SQL Server Execution Times:\s*CPU time = (\d+) ms,  elapsed time = (\d+) ms.', null) r
	where
		r.[group] > 0
)
select
	p.regex_type,
	count(1) exec_count,
	avg(p.[1]) as avg_cpu_time_ms, 
	avg(p.[2]) as avg_elapsed_time_ms,
	sum(p.[1]) as total_cpu_time_ms, 
	sum(p.[2]) as total_elapsed_time_ms
from
	t0
		pivot
	(max(value) for [group] in ([1], [2])) p
group by
	p.regex_type
go

18 июн 15, 13:10    [17787132]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
В качестве тестовых данных использовалось это, предварительно конвертированное в Unicode (~2Mb). На sql.ru не влазит (более 150 Kb).
18 июн 15, 13:15    [17787153]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
По производительности/оптимизации пара ссылок для полноты картины:

Best Practices for Regular Expressions in the .NET Framework


Optimizing Regular Expression Performance, Part I: Working with the Regex Class and Regex Objects

В последней ссылке приводятся замеры. Все что в этих ссылках приводится справедливо и для этой обертки.
18 июн 15, 13:23    [17787183]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
stut
Member

Откуда:
Сообщений: 491
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*) --почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+. Что значит такач комбинация как \\ или больше -- или сами по себе они ничего не значат? И что вообще значат круглые скобки -- групировка -- в каком значение-- я например могу растолковать это выражение как група1--0 или больше любых символов--група2--0 или больше цыфр--група3--0 или больше любых символов.
1 окт 15, 20:30    [18223092]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
stut
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*)


stut
почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+.


Что значит такач комбинация как \\ или больше -- или сами по себе они ничего не значат? И что вообще значат круглые скобки -- групировка -- в каком значение-- я например могу растолковать это выражение как група1--0 или больше любых символов--група2--0 или больше цыфр--група3--0 или больше любых символов.[/quot]

строка вида: g34f347fg374fweuyfw\ddddddd3874 соответствует регексу

группа1: g34f347fg374fweuyfw
группа2: \ddddddd
группа3: 3874
1 окт 15, 20:53    [18223199]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
churupaha
Member

Откуда: Краснодар
Сообщений: 1015
stut
Подскажите что например значит такой регекс -- (.*)(\\d+)(.*)


если вы напишите такое в коде на T-SQL, то строка вида: g34f347fg374fweuyfw\ddddddd3874 будет соответствовать регексу, причем:

группа1: g34f347fg374fweuyfw
группа2: \ddddddd
группа3: 3874

stut
почему здесь цыфра(ы) обозначаются как \\d+ -- а не \d+.


откуда этот регекс? из когда на каком языке? там принято экранировать '\'? когда выдергиваете ее из кода - убирайте экранирующие слэши. и тогда смысл регулярки будет такой, который задумывал автор:

g34f347fg374fweuyfw\ddddddd3874вувувувувувуву

группа1: g34f347fg374fweuyfw\ddddddd387
группа2: 4
группа3: вувувувувувуву
1 окт 15, 20:58    [18223228]     Ответить | Цитировать Сообщить модератору
 Re: FAQ: Regex. Парсим, заменяем, разбиваем, крутим/вертим...  [new]
stut
Member

Откуда:
Сообщений: 491
churupaha,
Я имел ввиду Java -- ибо в этом контексте я изучал эту тему.
4 окт 15, 23:04    [18233953]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить