ייצור אוטומטי של סקריפטי התקנה והסרה של SQL CLR Functions / Stored Procedures

לפני כמה ימים פרסמתי פוסט על משהו קטן שעבדתי עליו בין תרגיל אינפי לתרגיל לינארית, שכולל למעשה אוסף של functions ו- stored procedures המאפשרים עבודה מול Redis מ- SQL Server, כאשר מרבית הפרוייקט זה סה”כ תפירה בין קוד C# קיים לעבודה מול Redis ל- SQL Server – שמתבצעת באמצעות SQL CLR, פיצ’ר חביב (למרות שהיה יכול להיות טוב משמעותית) שמאפשר לשלב ולכתוב רכיבים שונים בדטאבייס (functions, stored procedures וכו’) ב- C#.

הדרישה שהייתה לי לצרכי פיתוח הייתה פשוטה – שבסוף כל build יהיה לי קובץ install.sql שכשאני מריץ אותו הוא מכיל את כל ה- CREATE statements של ה- CLR functions / procedures שלי, ו- uninstall.sql שכולל את ה- DROP statements הרלוונטיים. הדבר העיקרי ממנו רציתי להימנע זה התענוג (המפוקפק) בלהכין create statements לכל פונקציה בנפרד, ואז כל פעם שמשנים במהלך הפיתוח את הפרמטרים לעדכן גם בקוד C# וגם בסקריפט ה-SQL-י ולייצר למעשה שכפול מיותר של הגדרות.

הפיתרון הראשון שאליו הסתכלתי היה לממש הכל באמצעות SSDT. למי שלא מכיר, SSDT (SQL Server Data Tools), הוא פרוייקט של מיקרוסופט של תוסף ל- Visual Studio שמאפשר לייצג דטאבייס בתור פרוייקט ב-Visual Studioהכולל item-ים שונים: טבלאות, views, stored procedures, טריגרים, SQL CLR Functions – בקיצור, פחות או יותר כל מה שרוצים. SSDT תומך בתרחיש deployment באמצעות dacpac או באמצעות יצירת סקריפטי מיגרציה SQL-יים.
בעבר עשיתי מספר ניסיונות ב-SSDT לשימושים היותר קונבנציונליים שלו (ניהול סכימה של DB בתור פרוייקט) ופסלתי אותו כי הוא לא היה נוח (גם מבחינת תרחיש העבודה, וגם לשימוש באופן כללי). החלטתי שזה יכול להיות ניסיון טוב להשתמש בו.

צריך להבין שהתרחיש המקורי אליו מיועד SSDT שונה מהתרחיש שהיה לי במקרה הספיציפי הזה. התרחיש המקורי הוא כאמור שימוש לניהול סכימה של DB. בתרחיש המסויים שלי, אני רוצה לנהל פרוייקט שמייצג משהו שאנשים יכולים “להתקין” לתוך ה-DB שלהם. ובכל זאת, חשבתי שבזכות היכולת שלו לעקוב אחרי שינויי סכימאות, והעובדה שניתן להוסיף אליו CLR functions וכו’ הוא יתאים. די מהר התבדיתי – העבודה מולו מאד לא נוחה. כשאומרים לו לייצר סקריפטים (ולא לעבוד עם DAC), אז כל ייצור סקריפט לפרוייקט קטנטן לוקח מס’ דקות. אמנם לא רב, אבל הרבה יותר מהמס’ שניות שנראה לי סביר ונוח לצרכי עבודה שוטפת. בנוסף, התברר לי שהוא לא תומך בפונקציונאליות בסיסית מאד שרציתי – למשל קביעת default values שונים ל- CLR functions (ב-connect זה עדיין בקשה פתוחה).
מכיוון שהוא לא היה כיפי לשימוש, וגם לא נתן כל מה שרציתי – החלטתי שהוא לא מתאים לצורך הזה.

אז מכיוון שלמרות הכל לא רציתי לכתוב בעצמי באמצעות copy-paste את ה-SQL-ים של ההתקנה וההסרה, החלטתי לפתח משהו פשוט שייצר אותם עבורי עם Reflection. בסופו של דבר, יצרתי פרוייקט שמכיל אוסף של attributes שמתארים את כל ה- metadata הנוסף שצריך עבור ייצור הסקריפטים הללו, מעבר לזה שקיים כבר ב SqlFunctionAttribute וב- SqlProcedureAttribute.  כל מתודה שיצרתי שאמורה להתווסף כ- CLR function/procedure כוללת את ה- attributes הרלוונטיים.
בנוסף, יצרתי console application קטן שמייצר ע”ס ה-attributes האלה CREATE / DROP statements ומשלב אותם בקובץ טקסט שמכיל טמפלייט רלוונטי (שכולל אפשרות גם לעשות include לקבצי SQL אחרים). יש קובץ טמפלייט גם להתקנה וגם להסרה, שעל פיהם מג’ונרט הסקריפט התקנה והסרה הסופי, באמצעות post build events.

מכיוון שרוב הפרוייקט שלי מבוסס על CLR funcitons ו- CLR procedures, זה מה שהיה חשוב לי לפתור. את הקוד T-SQL שכן קיים, אני משלב בקובץ template באמצעות תמיכה ב- include (כלומר, אין משהו חכם שמתמודד עם stored procedures / functions שכתובים ב- T-SQL).  כמובן שפונקציונאליות אחרת שאני לא משתמש בה בכלל (כמו CLR Triggers) בכלל  לא כלולה בפרוייקט הזה.

אם אתם כותבים משהו שמבוסס על SQL CLR (ובכנות, נראה לי שזה משהו די נדיר), יכול להיות שזה יהיה רלוונטי עבורכם (למרות שאני מקווה ש- SSDT פשוט ישתפר, כי הוא הפיתרון הטבעי לזה). הקוד הרלוונטי זמין ב- GitHub.

SQL: להפוך XML במבנה לא ידוע (חלקית) לתצוגה טבלאית

היכולת לעבוד עם XML-ים ב- SQL Server לא חדשה בכלל וקיימת מזה גרסאות. בין הדברים שמתאפשרים בעבודה עם XML-ים זה אכסון XML-ים בטבלאות באמצעות datatype מותאם לכך, פונקציות לפירסור XML, ויכולת לייצא תוצאות של שאילתות לפורמט XML במקום לפורמט טבלאי.

למשל, אם אנחנו רוצים לשלוף מ- sys.tables ולהציג את התוצאות כ-XML בפורמט טבלאי, כל שצריך לעשות זה להוסיף FOR XML statement בסוף. ניתן בצורה הזאת גם להשיג שליטה מוגבלת על מבנה ה-XML שמתקבל. למשל, בדוגמא הבאה, אני מגדיר שיהיה root element בשם items ושכל שורה תהיה עטופה בתור item:

SELECT *

FROM sys.tables

FOR XML PATH('item'), ROOT('items')

ככה ייראה ה-XML שיתקבל:

image

אבל, מה קורה כשאנחנו רוצים להפוך את ה-XML הזה לתצורה טבלאית בחזרה? לצורך כך יש לנו את הפונקציה OPENXML. הפונקציה הזאת יודעת לקבל handle שמתקבל ע”י קריאה ל- sp_xml_preparedocument שעושים לפני כן, ויודעת להפוך את ה-XML הזה לתצורה של שורות וטבלאות.

מתי זה מסתבך? כשרוצים לעשות משהו גנרי שהופך XML מהסגנון הזה שראינו ל- rowset, בלי שאנחנו יודעים את שמות העמודות. למה זה מסתבך? כי OPENXML דורשת לקבל את שמות העמודות וה- data type שיהיה ב- rowset שיוחזר.

מה אפשר לעשות? לחלק את הבעייה לשתי שלבים: בשלב הראשון, לקבל את השמות של האלמנטים. בשלב השני, לקרוא ל- OPENXML ולהעביר את שמות האלמנטים שיקבלנו.

ה-stored procedure הבא עושה בדיוק את זה.. מייצר את רשימת העמודות מה-XML (הוא מצפה ל-XML במבנה של root element בשם items ובתוכו אלמנטים בשם item) ואז משתמש ב-OPENXML בשביל להפוך את ה-XML ל-rowset עם שמות העמודות שמועברות דינמית:

 

CREATE PROCEDURE [dbo].[ConvertXmlToRowset]

(

    @input xml

)

AS

BEGIN

    DECLARE @cols table (ColName nvarchar(500))

 

    INSERT INTO @cols(ColName)

    SELECT    

            keyValueSet.Name

    FROM   @input.nodes('/items/item') T(c)

    OUTER APPLY (

                    SELECT CAST(T.c.query('.') as xml) xmlData

                )  rowXml

    OUTER APPLY (

                    SELECT

                        C.Name

                    FROM rowXml.xmlData.nodes('/item/*') as T(C)

                    OUTER APPLY (

                                    SELECT

                                        T.C.value('local-name(.)', 'nvarchar(max)') as Name

                                    UNION ALL

                                    SELECT

                                        A.C.value('.', 'nvarchar(max)') as Value

                                    FROM T.C.nodes('@*') as A(C)

                                ) as C

                ) keyValueSet

 

    DECLARE @colsNames NVARCHAR(2000)

    SELECT @colsNames =COALESCE(@colsNames + ', ', '') + '[' +ColName + '] nvarchar(max)'

    FROM @cols

    GROUP BY ColName

 

    DECLARE @query nvarchar(max)=

    '

    DECLARE @doc nvarchar(max) = ''' + CAST(@input as nvarchar(max)) + '''

    DECLARE @idoc int 

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    

    SELECT    *

    FROM       OPENXML (@idoc, ''/items/item'',2)

            WITH (' + @colsNames + ')

    '

    EXECUTE(@query)

END

 

 

 

GO

 

 

ודוגמא לשימוש:

DECLARE @xml xml = (SELECT * FROM sys.tables FOR XML PATH('item'), ROOT('items'))

EXECUTE redisql.ConvertXmlToRowset @input = @xml

image

החסרון בשיטה הזאת, הוא שה-rowset שמתקבל בסוף לא זהה למקורי. ה- data type המקורי של העמודות לא נשמר, ובמימוש הזה גם הסדר לא נשמר. הפיתרון הנ”ל היה אחד הפתרונות ששקלתי כשמימשתי את שמירת ה- Rowsets ב- RediSql, ופסלתי אותו בסוף בגלל הסיבה הזאת – הוא לא מייצג נאמנה את המקור. עם זאת, במידה שזה פחות חשוב לכם ומבחינתכם לקבל את כל המידע בתור nvarchar(max) מתאים, עדיין יכול להיות מדובר בפיתרון שימושי.

בהצלחה.