مستندسازی پیشرفته SQL Server: ایجاد سیستم راهنمای مبتنی بر کامنت با CLR Stored Procedure
در دنیای توسعه SQL Server، مستندسازی دقیق و کارآمد برای T-SQL Stored Procedureها، Functionها، Viewها و Triggerها از اهمیت بالایی برخوردار است. این مستندات به توسعه‌دهندگان جدید کمک می‌کند تا با کدهای موجود آشنا شوند، و به تیم‌های فعلی اجازه می‌دهد تا تغییرات و به‌روزرسانی‌ها را با سرعت و دقت بیشتری انجام دهند. با این حال، حفظ و نگهداری مستندات اغلب یک چالش بزرگ است، به‌خصوص زمانی که تغییرات سریع و متعددی در کد رخ می‌دهد.
یکی از رویکردهای موثر برای مستندسازی، استفاده از سیستم راهنمای مبتنی بر کامنت (Comment Based Help) است. این رویکرد به توسعه‌دهندگان امکان می‌دهد تا توضیحات و راهنمایی‌های مربوط به یک شیء دیتابیس را مستقیماً در کنار کد آن شیء، به صورت کامنت، بنویسند. این روش نه تنها تضمین می‌کند که مستندات همیشه در کنار کد مربوطه قرار دارند، بلکه باعث می‌شود به‌روزرسانی آن‌ها نیز بسیار ساده‌تر شود. در این مقاله، ما به بررسی چگونگی پیاده‌سازی یک فریم‌ورک راهنمای مبتنی بر کامنت با استفاده از CLR Stored Procedure در SQL Server می‌پردازیم. این روش به شما کمک می‌کند تا مستندسازی خود را خودکار کرده و دسترسی به اطلاعات مربوط به اشیاء دیتابیس را برای همه کاربران و توسعه‌دهندگان آسان‌تر کنید.
مشکل در مستندسازی اشیاء T-SQL
در طول چرخه عمر توسعه نرم‌افزار، ایجاد و نگهداری مستندات مرتبط با Stored Procedureها، Functionها، Viewها و Triggerها در SQL Server یک نیاز ضروری است. این مستندات معمولاً شامل اطلاعاتی درباره هدف شیء، پارامترها، مقادیر بازگشتی، و وابستگی‌ها هستند. متأسفانه، به دلایل مختلف، بسیاری از تیم‌ها در حفظ این مستندات با چالش مواجه می‌شوند. گاهی اوقات این مستندات در فایل‌های جداگانه ذخیره می‌شوند که به راحتی از دست می‌روند یا با کدهای اصلی همگام نمی‌مانند. در موارد دیگر، زمان و منابع کافی برای به‌روزرسانی مستمر مستندات وجود ندارد، که منجر به اطلاعات منسوخ و گمراه‌کننده می‌شود. این کمبود مستندات می‌تواند منجر به سردرگمی توسعه‌دهندگان، افزایش زمان اشکال‌زدایی (debugging) و کاهش کیفیت کلی کد شود. برای حل این مشکل، نیاز به یک رویکرد یکپارچه و خودکار برای مستندسازی داریم که مستقیماً با کدهای T-SQL پیوند خورده باشد.
مروری بر راه‌حل: استفاده از CLR Stored Procedure برای راهنمای مبتنی بر کامنت
برای غلبه بر چالش‌های مستندسازی، راه‌حلی را پیشنهاد می‌کنیم که از قابلیت‌های CLR (Common Language Runtime) در SQL Server بهره می‌برد. CLR به شما اجازه می‌دهد تا کد مدیریت‌شده (مانند C# یا VB.NET) را مستقیماً درون SQL Server اجرا کنید، که این امر انعطاف‌پذیری و قدرت بیشتری نسبت به T-SQL خالص فراهم می‌آورد. ایده اصلی این است که:
1. **کامنت‌گذاری کد:** توسعه‌دهندگان Stored Procedureها، Functionها، Viewها و Triggerها را با کامنت‌های خاصی که فرمت مشخصی برای راهنما دارند، مستندسازی می‌کنند.
2. **ایجاد CLR Stored Procedure:** یک CLR Stored Procedure در C# (یا زبان دیگر .NET) نوشته می‌شود که قادر است متن Stored Procedureهای SQL Server را بخواند، کامنت‌های راهنمای مشخص شده را استخراج کند و آن‌ها را در قالبی خوانا به عنوان خروجی نمایش دهد.
3. **انتشار و استفاده:** این CLR Stored Procedure در SQL Server منتشر شده و سپس می‌توان آن را مانند هر Stored Procedure دیگری فراخوانی کرد تا راهنمای مربوط به یک شیء دیتابیس را بازیابی کند.
این رویکرد تضمین می‌کند که مستندات همیشه در کنار کد هستند و با هر تغییر در کد، می‌توانند به راحتی به‌روزرسانی شوند. علاوه بر این، یک CLR Stored Procedure می‌تواند منطق پیچیده‌تری را برای تجزیه و تحلیل کامنت‌ها و ارائه خروجی‌های فرمت‌شده مدیریت کند.
مراحل پیاده‌سازی CLR Stored Procedure برای راهنمای مبتنی بر کامنت
بیایید گام به گام نحوه پیاده‌سازی این راه‌حل را بررسی کنیم.
گام 1: ایجاد پروژه CLR در Visual Studio
اولین گام ایجاد یک پروژه Visual Studio برای CLR Stored Procedure است. این پروژه شامل کدهای C# خواهد بود که مسئول خواندن، تجزیه و تحلیل و نمایش کامنت‌های راهنما هستند.
1. **پروژه جدید:** یک پروژه جدید از نوع “SQL Server Database Project” در Visual Studio ایجاد کنید.
2. **افزودن Stored Procedure CLR:** روی پروژه کلیک راست کرده، “Add” و سپس “Stored Procedure…” را انتخاب کنید. در پنجره باز شده، “C# Stored Procedure” را انتخاب کرده و نامی مانند `usp_Help` به آن بدهید.
کد زیر یک Stored Procedure CLR نمونه را نشان می‌دهد که می‌تواند متن یک شیء (مانند یک Stored Procedure) را از `sys.sql_modules` دریافت کرده و سپس خطوط کامنت مشخص شده را استخراج کند. برای سادگی، این مثال فقط خطوطی را که با `/*` شروع می‌شوند و شامل کلمه کلیدی `DESCRIPTION` هستند، در نظر می‌گیرد و آن‌ها را به عنوان راهنما برمی‌گرداند.
کد csharp :
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
using System.Text.RegularExpressions;
public partial class StoredProcedures
{
// این ویژگی مشخص می کند که متد زیر یک SQL Server Stored Procedure است.
// این ویژگی به SQL Server کمک می کند تا تابع را به درستی ثبت کند.
[Microsoft.SqlServer.Server.SqlProcedure]
public static void usp_Help (SqlString objectName)
{
// یک شیء از نوع SqlConnection برای اتصال به پایگاه داده فعلی SQL Server ایجاد می کنیم.
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
// یک SqlCommand برای فراخوانی sys.sp_helptext ایجاد می کنیم
// تا متن تعریف شیء SQL Server را بدست آوریم.
using (SqlCommand command = new SqlCommand("EXEC sp_helptext @objname", connection))
{
// پارامتر ورودی به sp_helptext را اضافه می کنیم.
SqlParameter parameter = new SqlParameter("@objname", SqlDbType.NVarChar);
parameter.Value = objectName.Value;
command.Parameters.Add(parameter);
// SqlDataReader برای خواندن نتایج sp_helptext.
using (SqlDataReader reader = command.ExecuteReader())
{
// یک لیست از رشته ها برای نگهداری خطوط کمکی استخراج شده.
List helpLines = new List();
// هر خط از تعریف شیء را می خوانیم.
while (reader.Read())
{
SqlString textLine = reader.GetSqlString(0);
if (!textLine.IsNull)
{
string line = textLine.Value.Trim();
// در اینجا می توانیم منطق پیچیده تری برای تجزیه کامنت ها اضافه کنیم.
// به عنوان مثال، خطوطی که با /* و */ مشخص شده اند و حاوی برچسب های خاصی هستند.
// برای این مثال ساده، خطوطی را که شامل DESCRIPTION: هستند استخراج می کنیم.
if (line.StartsWith("-- DESCRIPTION:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- PARAMETERS:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- RETURNS:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- EXAMPLE:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- AUTHOR:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- CREATED DATE:", StringComparison.OrdinalIgnoreCase) ||
line.StartsWith("-- LAST MODIFIED:", StringComparison.OrdinalIgnoreCase)
)
{
helpLines.Add(line);
}
}
}
// حالا نتایج را به SQL Server برمی گردانیم.
// SqlContext.Pipe.Send روش مناسبی برای بازگرداندن داده ها از CLR SP است.
// می توانیم یک SqlDataRecord ایجاد کنیم تا خروجی ساختاریافته داشته باشیم.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("HelpText", SqlDbType.NVarChar, -1));
SqlContext.Pipe.SendResultsStart(record);
foreach (string line in helpLines)
{
record.SetString(0, line.Replace("--", "").Trim()); // حذف -- و فاصله های اضافی
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
}
connection.Close();
}
}
}
در کد بالا، ویژگی زیر یک Stored Procedure CLR را در SQL Server تعریف می‌کند:
`[Microsoft.SqlServer.Server.SqlProcedure]`
این ویژگی (Attribute) به SQL Server اطلاع می‌دهد که متد `usp_Help` یک Stored Procedure است که می‌تواند از طریق T-SQL فراخوانی شود. این ویژگی برای ثبت صحیح Stored Procedure CLR در کاتالوگ SQL Server ضروری است.
این کد C# از `sp_helptext` برای بازیابی تعریف متنی یک شیء دیتابیس استفاده می‌کند. سپس، خطوطی را که با `— DESCRIPTION:` یا سایر برچسب‌های مشخص شروع می‌شوند، فیلتر کرده و آن‌ها را به عنوان راهنمای فرمت‌شده از طریق `SqlContext.Pipe.SendResultsRow` به SQL Server برمی‌گرداند.
گام 2: استقرار (Deploy) اسمبلی CLR در SQL Server
پس از کامپایل پروژه Visual Studio (که یک فایل DLL تولید می‌کند)، باید این اسمبلی را در SQL Server مستقر کنید. قبل از استقرار، باید قابلیت CLR را در SQL Server فعال کنید و پایگاه داده خود را برای اعتماد به اسمبلی‌ها پیکربندی کنید.
ابتدا، CLR را در سرور SQL Server فعال می‌کنیم (اگر قبلاً فعال نشده باشد):
sp_configure 'show advanced options', 1;
RECONFIGURE;
go
sp_configure 'clr enabled', 1;
RECONFIGURE;
go
سپس، اسمبلی (DLL) را در SQL Server ایجاد می‌کنیم. نام اسمبلی را جایگزین `[Path_To_Your_DLL]` کنید. (فرض بر این است که فایل DLL شما `SQLCLRHelp.dll` است که از پروژه Visual Studio شما تولید شده است.)
USE [YourDatabaseName];
GO
-- اگر اسمبلی از قبل وجود دارد، آن را حذف می کنیم تا بتوانیم نسخه جدید را ایجاد کنیم.
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'SQLCLRHelp')
BEGIN
DROP ASSEMBLY SQLCLRHelp;
END
GO
-- اسمبلی را از فایل DLL در SQL Server ایجاد می کنیم.
-- مسیر فایل DLL باید دقیق باشد.
CREATE ASSEMBLY SQLCLRHelp
FROM 'C:\YourProjectFolder\bin\Debug\SQLCLRHelp.dll' -- مسیر کامل فایل DLL را اینجا قرار دهید
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO
-- اگر Stored Procedure از قبل وجود دارد، آن را حذف می کنیم.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Help]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Help];
GO
-- Stored Procedure CLR را ایجاد می کنیم.
-- نام Stored Procedure (usp_Help) باید با نام متد در کد C# مطابقت داشته باشد.
-- [SQLCLRHelp].[StoredProcedures].[usp_Help] به ترتیب:
-- [نام اسمبلی].[نام کلاس].[نام متد]
CREATE PROCEDURE [dbo].[usp_Help]
@objectName NVARCHAR(MAX)
AS EXTERNAL NAME [SQLCLRHelp].[StoredProcedures].[usp_Help];
GO
توضیح `PERMISSION_SET = EXTERNAL_ACCESS`:
چون CLR Stored Procedure ما برای خواندن متن شیء نیاز به دسترسی به `sp_helptext` و در نهایت به کاتالوگ سیستمی دارد، نیاز به سطح دسترسی `EXTERNAL_ACCESS` دارد. این سطح دسترسی به اسمبلی اجازه می‌دهد به منابع خارجی SQL Server (مانند فایل سیستم) و اطلاعات خارج از محدوده پایگاه داده فعلی دسترسی داشته باشد. اگر اسمبلی شما فقط عملیات درون حافظه‌ای را انجام می‌دهد، می‌توانید از `SAFE` استفاده کنید، اما برای این مورد `EXTERNAL_ACCESS` لازم است.
در محیط تولید، استفاده از `EXTERNAL_ACCESS` نیازمند ملاحظات امنیتی بیشتری است. توصیه می‌شود که گواهی‌نامه (Certificate) یا کلید نام قوی (Strong Name Key) برای امضای اسمبلی و ایجاد Login بر اساس آن در SQL Server استفاده شود تا ریسک‌های امنیتی کاهش یابد.
گام 3: تست CLR Stored Procedure
حالا که CLR Stored Procedure مستقر شده است، می‌توانیم آن را تست کنیم. ابتدا، یک Stored Procedure نمونه با کامنت‌های راهنمای مشخص ایجاد می‌کنیم:
USE [YourDatabaseName];
GO
-- اگر Stored Procedure از قبل وجود دارد، آن را حذف می کنیم.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_GetCustomerOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_GetCustomerOrders];
GO
CREATE PROCEDURE [dbo].[usp_GetCustomerOrders]
@CustomerID INT
AS
-- DESCRIPTION: این Stored Procedure اطلاعات سفارشات یک مشتری خاص را بازیابی می کند.
-- PARAMETERS:
-- @CustomerID: شناسه منحصر به فرد مشتری.
-- RETURNS: مجموعه ای از سفارشات شامل OrderID، OrderDate، TotalAmount.
-- EXAMPLE: EXEC usp_GetCustomerOrders @CustomerID = 101;
-- AUTHOR: محمد رضایی
-- CREATED DATE: 2023-10-27
-- LAST MODIFIED: 2023-10-27
BEGIN
SELECT
o.OrderID,
o.OrderDate,
o.TotalAmount
FROM
dbo.Orders o
WHERE
o.CustomerID = @CustomerID;
END;
GO
حالا Stored Procedure `usp_Help` را برای بازیابی راهنمای `usp_GetCustomerOrders` فراخوانی می‌کنیم:
EXEC dbo.usp_Help @objectName = 'usp_GetCustomerOrders';
GO
خروجی مورد انتظار:
HelpText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DESCRIPTION: این Stored Procedure اطلاعات سفارشات یک مشتری خاص را بازیابی می کند.
PARAMETERS:
@CustomerID: شناسه منحصر به فرد مشتری.
RETURNS: مجموعه ای از سفارشات شامل OrderID، OrderDate، TotalAmount.
EXAMPLE: EXEC usp_GetCustomerOrders @CustomerID = 101;
AUTHOR: محمد رضایی
CREATED DATE: 2023-10-27
LAST MODIFIED: 2023-10-27
این خروجی نشان می‌دهد که `usp_Help` به درستی کامنت‌های راهنمای ما را استخراج کرده است.
گام 4: گسترش راهنما به Functionها، Viewها و Triggerها
طراحی فعلی CLR Stored Procedure برای خواندن متن هر شیء دیتابیس (Stored Procedure, Function, View, Trigger) که تعریف آن در `sys.sql_modules` ذخیره شده باشد، کار می‌کند. بنابراین، برای مستندسازی سایر اشیاء، کافیست آن‌ها را با همان الگوی کامنت‌گذاری ایجاد کنید.
مثلاً برای یک تابع (Function):
USE [YourDatabaseName];
GO
IF OBJECT_ID (N'dbo.fn_CalculateTax', N'FN') IS NOT NULL
DROP FUNCTION dbo.fn_CalculateTax;
GO
CREATE FUNCTION dbo.fn_CalculateTax
(
@Amount DECIMAL(18, 2),
@TaxRate DECIMAL(5, 2)
)
RETURNS DECIMAL(18, 2)
AS
-- DESCRIPTION: این تابع میزان مالیات را بر اساس مبلغ و نرخ مالیات محاسبه می کند.
-- PARAMETERS:
-- @Amount: مبلغ اصلی برای محاسبه مالیات.
-- @TaxRate: نرخ مالیات به صورت درصد (مثلاً 0.05 برای 5%).
-- RETURNS: میزان مالیات محاسبه شده.
-- EXAMPLE: SELECT dbo.fn_CalculateTax(100.00, 0.08);
-- AUTHOR: سارا احمدی
-- CREATED DATE: 2023-10-27
-- LAST MODIFIED: 2023-10-27
BEGIN
RETURN @Amount * @TaxRate;
END;
GO
و سپس راهنمای آن را فراخوانی کنید:
EXEC dbo.usp_Help @objectName = 'fn_CalculateTax';
GO
گام 5: ادغام راهنما در SQL Server Management Studio (SSMS) Object Explorer
یکی از راه‌های بهبود دسترسی به مستندات، استفاده از قابلیت Extended Properties در SQL Server است. این ویژگی به شما امکان می‌دهد تا متادیتای سفارشی را به اشیاء دیتابیس اضافه کنید. می‌توانید یک Stored Procedure ایجاد کنید که از خروجی `usp_Help` استفاده کرده و این اطلاعات را به عنوان Extended Property ذخیره کند. سپس SSMS می‌تواند این ویژگی‌ها را در Object Explorer نمایش دهد.
برای مثال، می‌توانید یک Stored Procedure بنویسید که هر زمان شیئی ایجاد یا تغییر می‌کند، راهنمای آن را در یک Extended Property ذخیره کند:
USE [YourDatabaseName];
GO
-- Stored Procedure برای به روز رسانی Extended Property از روی کامنت ها.
CREATE OR ALTER PROCEDURE [dbo].[usp_UpdateObjectHelpProperty]
@objectName NVARCHAR(MAX)
AS
BEGIN
SET NOCOUNT ON;
-- متغیر برای نگهداری متن راهنما
DECLARE @HelpText NVARCHAR(MAX);
SET @HelpText = N'';
-- جدول موقت برای گرفتن خروجی usp_Help
CREATE TABLE #HelpOutput (HelpText NVARCHAR(MAX));
INSERT INTO #HelpOutput (HelpText)
EXEC dbo.usp_Help @objectName = @objectName;
SELECT @HelpText = STRING_AGG(HelpText, CHAR(13) + CHAR(10))
FROM #HelpOutput;
DROP TABLE #HelpOutput;
-- حذف Extended Property موجود اگر وجود داشته باشد
IF EXISTS (
SELECT 1
FROM sys.extended_properties ep
INNER JOIN sys.objects o ON ep.major_id = o.object_id
WHERE o.name = @objectName AND ep.name = N'HelpDocumentation'
)
BEGIN
EXEC sp_dropextendedproperty
@name = N'HelpDocumentation',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'PROCEDURE', @level1name = @objectName; -- یا FUNCTION, VIEW, etc.
END;
-- اضافه کردن Extended Property جدید
EXEC sp_addextendedproperty
@name = N'HelpDocumentation',
@value = @HelpText,
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'PROCEDURE', @level1name = @objectName; -- یا FUNCTION, VIEW, etc.
END;
GO
شما باید نوع `level1type` را بر اساس نوع شیء `objectName` تنظیم کنید (برای Stored Procedure، ‘PROCEDURE’ است، برای Function، ‘FUNCTION’ و غیره). سپس می‌توانید `usp_UpdateObjectHelpProperty` را برای هر شیء فراخوانی کنید:
EXEC dbo.usp_UpdateObjectHelpProperty @objectName = 'usp_GetCustomerOrders';
GO
EXEC dbo.usp_UpdateObjectHelpProperty @objectName = 'fn_CalculateTax';
GO
بعد از اجرای این Stored Procedure، در SSMS، می‌توانید به Properties شیء مورد نظر بروید و در بخش “Extended Properties” راهنمای کامل آن را مشاهده کنید. این کار به توسعه‌دهندگان و مدیران دیتابیس کمک می‌کند تا بدون نیاز به اجرای کد خاصی، مستندات مربوط به اشیاء را به سرعت مشاهده کنند و فرآیند مدیریت SQL Server را بهبود می‌بخشد.
این فریم‌ورک یک راه قدرتمند برای افزایش شفافیت و کارایی در محیط‌های توسعه SQL Server فراهم می‌کند. با نگهداری مستندات در کنار کد، همگام‌سازی آن‌ها ساده‌تر شده و بار نگهداری به شکل چشمگیری کاهش می‌یابد.