عبارات منظم پیشرفته در SQL Server با پایتون و CLR

عبارات منظم پیشرفته در SQL Server: قدرت پایتون و CLR

قابلیت‌های تطابق الگو در SQL Server با استفاده از عملگر `LIKE` و تابع `PATINDEX` محدود هستند. این ابزارها برای تطابق‌های ساده کافی هستند، اما برای عبارات منظم (Regular Expressions – Regex) که نیازمند منطق پیچیده‌تر، گروه‌بندی، تکرار و گزینه‌های دیگر هستند، کافی نیستند. خوشبختانه، SQL Server 2017 و نسخه‌های جدیدتر قابلیت اجرای کدهای پایتون را مستقیماً از داخل پایگاه داده فراهم می‌کنند، که این امکان را می‌دهد تا از کتابخانه قدرتمند `re` پایتون برای عملیات Regex در SQL Server بهره ببریم. این روش به شما اجازه می‌دهد تا با استفاده از توابع تعریف‌شده توسط کاربر (UDF) از پایتون، منطق Regex را در T-SQL ادغام کنید.

برای استفاده از پایتون برای قابلیت‌های Regex در SQL Server، ابتدا باید محیط را پیکربندی کنیم. این شامل نصب Python، فعال کردن اسکریپت‌های خارجی و CLR در SQL Server و سپس ایجاد زبان خارجی (External Language) و کتابخانه‌های خارجی (External Libraries) مورد نیاز است.

ابتدا باید قابلیت اسکریپت‌های خارجی را فعال کنید:

EXEC sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

سپس، CLR را فعال کنید:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

حال، زبان پایتون را به عنوان یک زبان خارجی ایجاد می‌کنیم:

CREATE EXTERNAL LANGUAGE Python
FROM (CONTENT = N'path_to_your_python_extensibility_installation')
GO

*نکته*: `path_to_your_python_extensibility_installation` را با مسیر نصب پایتون برای قابلیت توسعه اسکریپت‌های خارجی SQL Server جایگزین کنید (معمولاً در مسیر نصب SQL Server قرار دارد).

سپس، کتابخانه پایتون `re` را به عنوان یک کتابخانه خارجی ایجاد می‌کنیم. این کار امکان استفاده از این ماژول را در اسکریپت‌های پایتون اجرا شده توسط SQL Server فراهم می‌کند.

CREATE EXTERNAL LIBRARY regexlib
FROM (CONTENT = N'import re')
WITH (LANGUAGE = 'Python')
GO

حالا می‌توانیم توابع پایتون را ایجاد کنیم که قابلیت‌های Regex را ارائه می‌دهند. کتابخانه `re` پایتون چهار تابع اصلی برای کار با عبارات منظم دارد: `re.search()`, `re.match()`, `re.findall()` و `re.sub()`. هر یک از این توابع برای نیازهای مختلفی طراحی شده‌اند.

`re.search(pattern, string, flags=0)`

این تابع به دنبال اولین وقوع الگو در سراسر رشته می‌گردد و یک شیء match را برمی‌گرداند. اگر الگو پیدا نشود، `None` را برمی‌گرداند.

`re.match(pattern, string, flags=0)`

این تابع فقط در ابتدای رشته به دنبال الگو می‌گردد. اگر الگو در ابتدای رشته تطابق پیدا کند، یک شیء match برمی‌گرداند؛ در غیر این صورت، `None` را برمی‌گرداند.

`re.findall(pattern, string, flags=0)`

این تابع تمام تطابق‌های غیرهمپوشان الگو را در رشته پیدا می‌کند و آنها را به عنوان لیستی از رشته‌ها برمی‌گرداند.

`re.sub(pattern, repl, string, count=0, flags=0)`

این تابع تمام وقوع‌های الگو را در رشته با `repl` جایگزین می‌کند. `count` حداکثر تعداد جایگزینی‌ها را مشخص می‌کند.

برای استفاده از این توابع در SQL Server، ما توابع اسکالر تعریف‌شده توسط کاربر (UDF) ایجاد می‌کنیم که اسکریپت‌های پایتون مربوطه را اجرا می‌کنند.

برای `RegExMatch` که بررسی می‌کند آیا الگو در ابتدای رشته تطابق دارد یا خیر، از اسکریپت پایتون زیر استفاده می‌کنیم:

CREATE OR ALTER FUNCTION RegExMatch
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    DECLARE @script NVARCHAR(MAX);
    SET @script = N'
import re
match = re.match(r''' + @pattern + ''', text)
result = 1 if match else 0
';
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = @script,
        @input_data_1 = N'SELECT @text AS text',
        @output_data_1_name = N'result'
    WITH RESULT SETS ((result BIT));
    RETURN (SELECT result FROM #result);
END;
GO

مثال استفاده از `RegExMatch`:

SELECT dbo.RegExMatch('Hello World', 'Hello'); --1
SELECT dbo.RegExMatch('Hello World', 'World'); --0 (because it does not match at the beginning)
GO

برای `RegExSearch` که به دنبال الگو در سراسر رشته می‌گردد:

CREATE OR ALTER FUNCTION RegExSearch
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS BIT
AS
BEGIN
    DECLARE @script NVARCHAR(MAX);
    SET @script = N'
import re
search = re.search(r''' + @pattern + ''', text)
result = 1 if search else 0
';
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = @script,
        @input_data_1 = N'SELECT @text AS text',
        @output_data_1_name = N'result'
    WITH RESULT SETS ((result BIT));
    RETURN (SELECT result FROM #result);
END;
GO

مثال استفاده از `RegExSearch`:

SELECT dbo.RegExSearch('Hello World', 'World'); --1
SELECT dbo.RegExSearch('Hello World', 'Goodbye'); --0
GO

برای `RegExReplace` که تطابق‌ها را با یک رشته دیگر جایگزین می‌کند:

CREATE OR ALTER FUNCTION RegExReplace
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX),
    @replacement NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @script NVARCHAR(MAX);
    SET @script = N'
import re
result = re.sub(r''' + @pattern + ''', replacement, text)
';
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = @script,
        @input_data_1 = N'SELECT @text AS text, @replacement AS replacement',
        @output_data_1_name = N'result'
    WITH RESULT SETS ((result NVARCHAR(MAX)));
    RETURN (SELECT result FROM #result);
END;
GO

مثال استفاده از `RegExReplace`:

SELECT dbo.RegExReplace('Hello World', 'World', 'SQL Server'); --'Hello SQL Server'
SELECT dbo.RegExReplace('123-456-7890', '(\d{3})-(\d{3})-(\d{4})', r'\1.\2.\3'); --'123.456.7890'
GO

برای `RegExExtractAll` که تمام تطابق‌های یک الگو را استخراج می‌کند:

CREATE OR ALTER FUNCTION RegExExtractAll
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS TABLE (MatchValue NVARCHAR(MAX))
AS
EXTERNAL NAME regexlib.RegExExtractAll
GO

*نکته*: `RegExExtractAll` نیازمند یک کتابخانه CLR است که اسکریپت پایتون را برای بازگرداندن یک جدول فراخوانی کند. برای سادگی و اجتناب از پیچیدگی‌های CLR خالص، معمولاً این کار را با یک تابع `sp_execute_external_script` با خروجی جدول انجام می‌دهند.

بیایید یک تابع `RegExExtractAll` با استفاده از `sp_execute_external_script` تعریف کنیم که یک جدول از مقادیر تطبیق‌یافته برمی‌گرداند:

CREATE OR ALTER FUNCTION RegExExtractAll
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS TABLE (MatchValue NVARCHAR(MAX))
AS
EXTERNAL NAME [RegExPythonFunctions].[RegExFunctions].[RegExExtractAll]
GO

برای `RegExExtractAll` ما نیازمند یک توابع تعریف شده توسط کاربر هستیم که بتواند یک جدول از نتایج را بازگرداند. این موضوع معمولاً با توابع CLR یا اسکریپت‌های پایتون با خروجی جدول انجام می‌شود. برای `sp_execute_external_script`، می‌توانیم به این شکل عمل کنیم:

CREATE OR ALTER FUNCTION RegExExtractAll
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS TABLE
(
    MatchValue NVARCHAR(MAX)
)
AS
BEGIN
    RETURN (SELECT CAST(MatchValue AS NVARCHAR(MAX)) AS MatchValue
            FROM OPENROWSET('SQLNCLI',
                            'Server=(local);Trusted_Connection=yes;',
                            N'
SET NOCOUNT ON;
DECLARE @text NVARCHAR(MAX) = N''' + REPLACE(@text, '''', '''''') + N''';
DECLARE @pattern NVARCHAR(MAX) = N''' + REPLACE(@pattern, '''', '''''') + N''';

EXEC sp_execute_external_script
    @language = N''Python'',
    @script = N''
import re
results = re.findall(pattern, text)
import pandas as pd
OutputDataSet = pd.DataFrame(results, columns=["MatchValue"])
'',
    @input_data_1 = N''SELECT @text AS text, @pattern AS pattern'',
    @output_data_1_name = N''OutputDataSet''
    WITH RESULT SETS ((MatchValue NVARCHAR(MAX)));
') AS T
    );
END;
GO

این روش به دلیل نیاز به `OPENROWSET` پیچیده‌تر است. یک راه ساده‌تر و مستقیم‌تر، ایجاد یک تابع جدول‌بازگرداننده (TVF) با `sp_execute_external_script` است:

CREATE OR ALTER FUNCTION RegExExtractAllInline
(
    @text NVARCHAR(MAX),
    @pattern NVARCHAR(MAX)
)
RETURNS TABLE
(
    MatchValue NVARCHAR(MAX)
)
AS
RETURN
(
    EXEC sp_execute_external_script
        @language = N'Python',
        @script = N'
import re
results = re.findall(pattern, text)
import pandas as pd
OutputDataSet = pd.DataFrame(results, columns=["MatchValue"])
',
        @input_data_1 = N'SELECT @text AS text, @pattern AS pattern',
        @output_data_1_name = N'OutputDataSet'
    WITH RESULT SETS ((MatchValue NVARCHAR(MAX)))
);
GO

مثال استفاده از `RegExExtractAllInline`:

SELECT MatchValue FROM dbo.RegExExtractAllInline('123 apple 456 banana 789', '(\d+)');
GO

این کوئری تمام اعداد را از رشته استخراج می‌کند.

'^\d+$'

این الگو بررسی می‌کند که آیا یک رشته فقط شامل ارقام است.

'^[a-zA-Z]+$'

این الگو بررسی می‌کند که آیا یک رشته فقط شامل حروف (کوچک یا بزرگ) است.

'^[a-zA-Z0-9]+$'

این الگو بررسی می‌کند که آیا یک رشته فقط شامل حروف و اعداد است.

'^[a-zA-Z0-9 ]+$'

این الگو بررسی می‌کند که آیا یک رشته فقط شامل حروف، اعداد و فاصله است.

'^[a-zA-Z0-9 ]{5,10}$'

این الگو بررسی می‌کند که آیا یک رشته فقط شامل حروف، اعداد و فاصله است و طول آن بین ۵ تا ۱۰ کاراکتر است.

'^(?=\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[!@#$%^&*()_+])(.{8,})$'

این یک الگوی Regex پیچیده برای اعتبار سنجی رمز عبور است که شامل: حداقل یک رقم، حداقل یک حرف کوچک، حداقل یک حرف بزرگ، حداقل یک کاراکتر خاص و حداقل ۸ کاراکتر طول است.

'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'

این الگو یک آدرس ایمیل را اعتبار سنجی می‌کند.

**امنیت و مجوزها:**

هنگام استفاده از CLR و اسکریپت‌های خارجی، جنبه‌های امنیتی مهمی وجود دارد. برای اجرای اسکریپت‌های پایتون، حساب سرویس SQL Server باید مجوزهای کافی برای دسترسی به محیط پایتون و اجرای اسکریپت‌ها را داشته باشد. همچنین، برای توابع CLR، ممکن است نیاز باشد پایگاه داده را به عنوان `TRUSTWORTHY ON` علامت‌گذاری کنید یا ASSEMBLY را با `UNSAFE EXTERNAL ACCESS` ایجاد کنید که پیامدهای امنیتی دارد.

برای CLR، ممکن است نیاز به تنظیم پایگاه داده به `TRUSTWORTHY ON` باشد که توصیه نمی‌شود. بهتر است که ASSEMBLY ها را با سطح مجوز `UNSAFE` ایجاد کنید و برای آن یک گواهی‌نامه ایجاد کرده و به آن مجوز دهید.

-- NOT RECOMMENDED FOR PRODUCTION:
ALTER DATABASE [YourDatabaseName] SET TRUSTWORTHY ON;
GO

به جای آن، باید به کاربری که تابع را اجرا می‌کند، مجوزهای لازم را بدهید:

GRANT EXECUTE ON EXTERNAL LANGUAGE::Python TO public;
GO

**کارایی (Performance):**

استفاده از پایتون برای Regex در SQL Server سربار عملکردی دارد. هر بار که یک تابع UDF پایتون فراخوانی می‌شود، یک فرآیند پایتون جدید راه‌اندازی می‌شود (یا از یک Pool موجود استفاده می‌شود)، که شامل هزینه‌های اولیه است. برای مقادیر زیاد داده یا در حلقه، این سربار می‌تواند قابل توجه باشد.

* **مزایا:**
* قابلیت‌های Regex بسیار قدرتمندتر از `LIKE` و `PATINDEX`.
* انعطاف‌پذیری برای استفاده از هر کتابخانه پایتون دیگر در SQL Server.
* **معایب:**
* سربار عملکردی ناشی از فراخوانی اسکریپت‌های خارجی.
* پیچیدگی بیشتر در راه‌اندازی و نگهداری.

**نکات بهبود عملکرد:**

* **فراخوانی دسته‌ای:** به جای فراخوانی UDF برای هر سطر، سعی کنید داده‌ها را به صورت دسته‌ای به اسکریپت پایتون ارسال کنید و نتایج را به صورت دسته‌ای برگردانید.
* **کامپایل کردن الگوها:** در پایتون، الگوهای Regex را می‌توان با `re.compile()` پیش‌کامپایل کرد تا سرعت پردازش الگوهای تکراری افزایش یابد. در چارچوب توابع SQL Server UDF، این کار کمی دشوار است زیرا اسکریپت برای هر فراخوانی دوباره اجرا می‌شود، اما برای اسکریپت‌های پایتون که به صورت مستقیم اجرا می‌شوند یا با یک منطق مدیریت حالت پیچیده‌تر، قابل استفاده است.

در نهایت، استفاده از قابلیت‌های Regex پایتون در SQL Server یک راه حل قدرتمند برای چالش‌هایی است که `LIKE` و `PATINDEX` قادر به حل آنها نیستند. با این حال، همیشه باید هزینه‌های عملکردی را در نظر گرفت و بر اساس نیازهای خاص و حجم داده، بهترین رویکرد را انتخاب کرد.