تحويل الإجراءات المخزنة والوظائف من خادم SQL إلى PostgreSQL

الدروس التعليمية
PostgreSQL SQL

الإجراءات المخزنة والدوال تقوم بتنفيذ منطق العمل في قاعدة البيانات. عند ترحيل قاعدة بيانات SQL Server إلى PostgreSQL، ستحتاج إلى تحويل الإجراءات المخزنة والدوال بشكل صحيح، مع الانتباه إلى معالجة المعلمة، واسترجاع مجموعة الصفوف، وبناء الجملة الخاصة الأخرى.

قاعدة البيانات SQL Server تستخدم لهجة SQL تسمى Transact-SQL (أو T-SQL) للإجراءات المخزنة والدوال، بينما تستخدم PostgreSQL لغة Procedural Language/PostgreSQL (أو PL/pgSQL) لنفس الغرض. هذه اللغات لها بنية بناء وإمكانيات مختلفة بشكل كبير، لذا يجب تحليل الإجراءات المخزنة والدوال بعناية وتحويلها.

أيضًا، بعض ميزات T-SQL ليس لها مكافئ مباشر في PL/pgSQL، وبالتالي، يتطلب ذلك تنفيذًا بديلًا لتلك الحالات. وأخيرًا، يجب تحسين الإجراءات المخزنة والدوال لمحرك PostgreSQL لضمان أدائها بكفاءة.

استرجاع مجموعة الصفوف

كل من SQL Server وPostgreSQL يسمحان بإرجاع مجموعة صفوف، وعادة ما تكون نتيجة استعلام SELECT، من الإجراءات المخزنة أو الوظائف، لكن البنية النحوية تختلف. إذا كانت إجراءة المخزنة في T-SQL تحتوي على SELECT كآخر بيان في الهيكل، فهذا يعني أنها ترجع مجموعة صفوف. PL/pgSQL يتطلب إما إعلانًا مسبقًا للمجموعة الصفوف المرجعة كجدول أو استرجاع البيانات من خلال refcursor.

عندما تحتوي مجموعة الصفوف المرجعة على عدد قليل من الأعمدة مع أنواع واضحة، يمكنك استخدام ميزة RETURNS TABLE في PostgreSQL.

في T-SQL:

SQL

  

CREATE PROCEDURE GetCustomerOrders @CustomerID INT
AS
    SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID = @CustomerID;
GO

 

في PL/pgSQL، يمكن أن يكون الأمر كالتالي: 

SQL

  

CREATE OR REPLACE FUNCTION GetCustomerOrders(CustomerID INT)
RETURNS TABLE(OrderID INT, OrderDate TIMESTAMP, Amount DECIMAL) 
AS $$
BEGIN
    RETURN QUERY SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID = GetCustomerOrders.CustomerID;
END; 
$$ LANGUAGE plpgsql;

ويمكن أن يبدو الكود المستدعي لـ PostgreSQL كالتالي:

SQL

  

SELECT * FROM GetCustomerOrders(5);

إذا كانت مجموعة الصفوف المرجعة تعقيدية ومن الصعب تحديد نوع البيانات لكل عمود، فقد لا يعمل النهج أعلاه. لهذه الحالات، الحل البديل هو استخدام refcursor.

في T-SQL:

SQL

  

CREATE PROCEDURE GetSalesByRange
	@DateFrom DATETIME,
	@DateTo DATETIME
AS
    SELECT C.CustomerID, C.Name AS CustomerName, C.FirstName, C.LastName, 
    	C.Email AS CustomerEmail, C.Mobile, C.AddressOne, C.AddressTwo, C.City, 
        C.ZipCode, CY.Name AS Country, ST.TicketID, TT.TicketTypeID, 
        TT.Name AS TicketType, PZ.PriceZoneID, PZ.Name AS PriceZone, 
        ST.FinalPrice AS Price, ST.Created, ST.TransactionType, 
        COALESCE(VME.ExternalEventID, IIF(E.ExternalID = '', NULL, E.ExternalID), '0') AS ExternalID, 
        E.EventID, ES.[Name] AS Section, ST.RowName, ST.SeatName
    FROM [Event] E WITH (NOLOCK) 
    INNER JOIN EventCache EC WITH (NOLOCK) ON E.EventID = EC.EventID
    INNER JOIN SaleTicket ST WITH (NOLOCK) ON E.EventID = ST.EventID
    INNER JOIN EventSection ES WITH (NOLOCK) ON ST.EventSectionID = ES.EventSectionID
    INNER JOIN Customer C WITH (NOLOCK) ON ST.CustomerID = C.CustomerID
    INNER JOIN Country CY WITH (NOLOCK) ON C.CountryID = CY.CountryID
    INNER JOIN TicketType TT WITH (NOLOCK) ON ST.TicketTypeID = TT.TicketTypeID
    INNER JOIN PriceZone PZ WITH (NOLOCK) ON ST.PriceZoneID = PZ.PriceZoneID
    LEFT OUTER JOIN VenueManagementEvent VME ON VME.EventID = E.EventID
WHERE ST.Created BETWEEN @DateFrom AND @DateTo 
ORDER BY ST.Created
GO

في PL/pgSQL:

SQL

  

CREATE OR REPLACE FUNCTION GetSalesByRange (
	V_DateFrom TIMESTAMP(3),
	V_DateTo TIMESTAMP(3),
	V_rc refcursor
) 
RETURNS refcursor
AS $$
BEGIN
	OPEN V_rc FOR
	SELECT C.CustomerID, C.Name AS CustomerName, C.FirstName, C.LastName, 
		C.Email AS CustomerEmail, C.Mobile, C.AddressOne, C.AddressTwo, C.City, 
		C.ZipCode, CY.Name AS Country, ST.TicketID, TT.TicketTypeID, 
		TT.Name AS TicketType, PZ.PriceZoneID, PZ.Name AS PriceZone,
		ST.FinalPrice AS Price, ST.Created, ST.TransactionType,
		COALESCE(
			VME.ExternalEventID, 
			(CASE WHEN E.ExternalID = '' THEN NULL ELSE E.ExternalID END), 
			'0') AS ExternalID, 
		E.EventID, ES.Name AS Section, ST.RowName, ST.SeatName
	FROM Event E 
	INNER JOIN EventCache EC ON E.EventID = EC.EventID
	INNER JOIN SaleTicket ST ON E.EventID = ST.EventID
	INNER JOIN EventSection ES ON ST.EventSectionID = ES.EventSectionID
	INNER JOIN Customer C ON ST.CustomerID = C.CustomerID
	INNER JOIN Country CY ON C.CountryID = CY.CountryID
	INNER JOIN TicketType TT ON ST.TicketTypeID = TT.TicketTypeID
    INNER JOIN PriceZone PZ ON ST.PriceZoneID = PZ.PriceZoneID
	LEFT OUTER JOIN VenueManagementEvent VME ON VME.EventID = E.EventID
	WHERE ST.Created BETWEEN V_DateFrom AND V_DateTo
	ORDER BY ST.Created;

	RETURN V_rc;
END; 
$$ LANGUAGE plpgsql;

ويمكن أن يبدو الكود المستدعي لـ PostgreSQL كالتالي:

SQL

  

BEGIN;
SELECT GetSalesByRange(
	'2024-01-01'::TIMESTAMP(3), 
	'2025-01-01'::TIMESTAMP(3), 
	'mycursorname'
);
FETCH 4 FROM mycursorname;
COMMIT;

 

إعلان المتغيرات المحلية

يسمح T-SQL بإعلان المتغيرات المحلية في أي مكان داخل جسم الإجراء المخزن أو الوظيفة. PL/pgSQL يتطلب أن يتم إعلان جميع المتغيرات المحلية قبل كلمة BEGIN:

SQL

  

CREATE OR REPLACE FUNCTION CreateEvent(…)
AS $$
DECLARE
	v_EventID INT;
	v_EventGroupID INT;
BEGIN
…
END; 
$$ LANGUAGE plpgsql;

 

في قاعدة بيانات SQL Server، يمكن تعريف متغيرات الجدول على النحو التالي:

SQL

  

DECLARE @Products TABLE
(
	ProductID int,
	ProductTitle varchar(100),
	ProductPrice decimal (8,2)
)

 

لا يدعم PostgreSQL هذه الميزة؛ يجب استخدام الجداول المؤقتة بدلاً من ذلك: 

SQL

  

CREATE TEMP TABLE Products
(
	ProductID int,
	ProductTitle varchar(100),
	ProductPrice decimal (8,2)
)

 

تذكر أن الجداول المؤقتة يتم إسقاطها تلقائيًا في نهاية الجلسة أو العملية الحالية. إذا كنت بحاجة إلى إدارة عمر الجدول بشكل صريح، استخدم تعليمة DROP TABLE IF EXISTS.

انتبه إلى التحويل المناسب لأنواع البيانات من SQL Server إلى أنواع بيانات PostgreSQL عند تحويل تعريف المتغيرات.

قيمة آخر عمود تزايد تلقائيًا

بعد تشغيل استعلام INSERT، قد تحتاج إلى الحصول على القيمة المولدة للعمود الذي يتزايد تلقائيًا. في T-SQL، يمكن الحصول عليها كما يلي

SQL

  

CREATE TABLE aitest (id int identity, val varchar(20));
INSERT INTO aitest(val) VALUES ('one'),('two'),('three');
SELECT @LastID = SCOPE_IDENTITY();

تسمح PostgreSQL بالوصول إلى آخر قيمة تم إدراجها عبر تسلسل تلقائي يحمل دائمًا اسم {اسم_الجدول}_{اسم_العمود}_seq:

SQL

  

CREATE TABLE aitest (id serial, val varchar(20));
INSERT INTO aitest(val) VALUES ('one'),('two'),('three');
LastID := currval('aitest_id_seq’);

 

الدوال المدمجة

عند تحويل الإجراءات والدوال المخزنة من SQL Server إلى PostgreSQL، يجب تحويل جميع الدوال المدمجة والعمليات الخاصة إلى مكافئات وفقًا للقواعد التالية:

  • يجب استبدال الدالة CHARINDEX بمكافئ PostgreSQL POSITION
  • يجب تحويل الوظيفةCONVERT إلى PostgreSQL وفقًا للقواعد المحددة في هذه المقالة
  • الوظيفةDATEADD($interval, $n_units, $date) يمكن تحويلها إلى تعبيرات PostgreSQL التي تستخدم العامل + تبعًا لقيمة $interval على النحو التالي:

    DAY / DD / D / DAYOFYEAR / DY

    ($date + $n_units * interval ‘1 day’)::date
    HOUR / HH ($date + $n_units * interval ‘1 hour’)::date
    MINUTE / MI / N ($date + $n_units * interval ‘1 minute’)::date
    MONTH / MM / M ($date + $n_units * interval ‘1 month’)::date
    QUARTER / QQ / Q ($date + $n_units * 3 * interval ‘1 month’)::date
    SECOND / SS / S ($date + $n_units * interval ‘1 second’)::date
    WEEK / WW / WK ($date + $n_units * interval ‘1 week’)::date
    WEEKDAY / DW / W ($date + $n_units * interval ‘1 day’)::date
    YEAR / YY ($date + $n_units * interval ‘1 year’)::date

  • يمكن تقليد وظيفة DATEDIFF($interval, $date1, $date2) في خادم SQL عبر DATE_PART كما يلي:
    DAY / DD / D / DAYOFYEAR / DY

    date_part(‘day’, $date2 – $date1)::int
    HOUR / HH 24 * date_part(‘day’, $date2 – $date1)::int + date_part(‘hour’, $date2 – $date1)
    MINUTE / MI / N 1440 * date_part(‘day’, $date2 – $date1)::int + 60 * date_part(‘hour’, $date2 – $date1) + date_part(‘minute’, $date2 – $date1)
    MONTH / MM / M (12 * (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int + date_part(‘month’, $date2) – date_part(‘month’, $date1))::int
    SECOND / SS / S 86400 * date_part(‘day’, $date2 – $date1)::int + 3600 * date_part(‘hour’, $date2 – $date1) + 60 * date_part(‘minute’, $date2 – $date1) + date_part(‘second’, $date2 – $date1)
    WEEK / WW / WK TRUNC(date_part(‘day’, $date2 – $date1) / 7)
    WEEKDAY / DW / W date_part(‘day’, $date2 – $date1)::int
    YEAR / YY (date_part(‘year’, $date2) – date_part(‘year’, $date1))::int

  • يجب استبدال كل حالة من DATEPART بـ DATE_PART
  • يجب تحويل وظيفة SQL Server GETDATE إلى PostgreSQL NOW()
  • يجب تحويل المشغل الشرطي IIF($condition, $first, $second) إلى CASE WHEN $condition THEN $first ELSE $second END
  • يجب استبدال كل حدوث لـISNULL بـCOALESCE
  • يجب تحويل وظيفة SQL Server REPLICATE إلى ما يعادلها في PostgreSQL، وهي REPEAT
  • يجب استبدال كل حدوث لـSPACE($n) بـREPEAT(' ', $n)

الاستنتاج

تعد عملية هجرة الإجراءات المخزنة والوظائف بين قاعدي بيانات اثنين DBMSs إجراء معقد إلى حد ما يتطلب الكثير من الوقت والجهد. على الرغم من عدم إمكانية أتمتة العملية بالكامل، يمكن أن تساعد بعض الأدوات المتاحة عبر الإنترنت في أتمتة العملية جزئيًا.

Source:
https://dzone.com/articles/convert-stored-procedures-sql-server-to-postgresql