content top
Using constraints to enforce uniqueness of ordered sets of rows

Using constraints to enforce uniqueness of ordered sets of rows

בדרך כלל אני משתדל לתת לפוסטים שלי כותרות בעברית, אלא שהפעם הסתבכתי לגמרי והגעתי למשהו כמו "שימוש באילוצים כדי לאכוף יחודיות של סדרות של קבוצות נתונים ממויינות", שכנראה הוא פחות מובן ממה שבכותרת; לא בגלל דלות השפה העברית חלילה, אלא מפני שמדובר כאן בנושאים טכניים, וכשם שאינני מתרגם מונחים כמו Stored Procedure או Clustered Index או Service Broker, כך שכרנו יצא בהפסדנו אם ננסה לתרגם את הנ"ל.
אם למישהו יש רעיון מוצלח יותר לתרגום – כזה שגם יהיה ברור ל-DBA ממוצע הדובר עברית – הוא מוזמן לנסות את כוחו..
בכל מקרה- זו כותרת פוסט של אלכסנדר קוזנצוב שהתפרסם לפני מספר חודשים ועוסק בבעייה הבאה:
נתון לנו סט ממויין של מספר שורות בתוך טבלה, ואיננו רוצים שהסט במלואו ובאותו הסדר יחזור על עצמו שנית. למשל (דוגמה של הנ"ל): יש לנו טבלת מתכונים ולכל מתכון מספר שורות, למשל – מתכון לביצה קשה:
1. שים ביצים בסיר עם מים.
2. הרתח במשך מספר דקות וכבה את האש.
3. צנן את הביצים במים קרים.
כעת אנחנו רוצים להוסיף מתכון חדש לקבלת ביצים טריות שסדר הצעדים בו (מימין לשמאל) הוא 1 => 3 => 2, כלומר – להרתיח את המים ללא הביצים בפנים.. זה כמובן מגוחך מבחינה קולינרית, אבל זה רק משל. מי שממש מתעקש – יש מתכונים בהם מקלפים תפוחי אדמה ומבשלים אותם, ואחרים בהם מבשלים אותם על קליפתם ורק אחר כך מקלפים..
בכל מקרה – כיצד נאפשר להכניס לטבלה את הצעדים 1,2,3 על פי הסדר, ואת הצעדים 1,3,2 על פי הסדר; אך שלא ניתן יהיה להכניס אף אחד מהם שנית? כמובן שהכוונה אינה לטריגר שירוץ בדיעבד (זה גם פתרון!) אלא למשהו בסיסי ברמת ההגדרות של הטבלה. שימוש ב-Unique Index כזה או אחר לא יפתור את הבעייה, הוא אוכף יחודיות של שורות ולא של אוסף ממויין של שורות..
הפתרון של קוזנצוב (הכל בפוסט שלו) הוא ליצור Indexed View (עוד מונח בלתי "תרגים") עם Pivot Query על הטבלה; כלומר- שליפה עם Group By לפי מספר המתכון (לכל מתכון תהיה שורה אחת) ומספר עמודות כמספר הצעדים המקסימלי במתכונים, ועל זה ליצור Unique Index.
כלומר- המתכון הראשון יופיע בתור 1,1,2,3,0,0
והמתכון השני יופיע בתור 2,1,3,2,0,0
כל זה בהנחה שיש עד 5 צעדים במתכון.
יש לפתרון הזה מספר חסרונות: אם רוצים יותר צעדים – צריך להיערך מראש ולבנות את ה-Indexed View ואת ה-Unique Index קצת אחרת, ולקחת בחשבון שיש מגבלה של עד 16 עמודות באינדקס (מגבלה שניתן איכשהו לעקוף אותה על ידי יצירת עמודה מחושבת עם כל העמודות או אולי בדרך אחרת, אך במחיר של סיבוך נוסף); ובנוסף- צריך ליצור אובייקט נוסף – Indexed View – שגם איתו אפשר לחיות בשלום אם רוצים, אבל יש לזה מחיר.
Keep it simple קובע הכלל, ואת זה לא רק שניתן לתרגם לעברית אלא לצטט את החכם באדם – אַל-תִּתְחַכַּם יוֹתֵר (קוהלת ז 16).
הפתרון החלופי שאני הולך להציג אינו חף מבעיות וסיבוכים, אך נראה לי שהוא יותר אלגנטי מהנ"ל, ובכל מקרה – טוב תמיד לשמוע חוות דעת שנייה.
אני אשתמש בעמודת XML שתכיל סט ממויין של כל מתכון, כך שלכל מתכון תהיה שורה אחת ולא מספר שורות כמספר הצעדים בו. קוזנצוב בוודאי יטען להגנתו שזה בניגוד לכללים, אך עדיין מדובר בפתרון לבעייה..
ניצור טבלה:

If Object_ID('tempdb..#RecipeSteps2','U') Is Not Null Drop Table #RecipeSteps2;

Go


Create Table #RecipeSteps2(ID Int Primary Key,

                           Recipe XML,

                           RecipeIdx As Cast(Recipe As Varchar(900)));


Create Unique Index IX_#RecipeSteps2 On #RecipeSteps2(RecipeIdx);

Go

בעמודה ID נחזיק את מספר המתכון,

בעמודה Receipe נחזיק את הצעדים השונים של המתכון במבנה של XML,

ובעמודה ReceipeIdx המחושבת נחזיק את ה-XML הנ"ל בתור מחרוזת כדי ליצור עליה Unique Index (לא ניתן ליצור אותו על עמודת XML)).

כעת נכניס את שני המתכונים לטבלה:

Insert

Into    #RecipeSteps2(ID,Recipe)

Select  1 ID,

        (Select *

        From    (Values (1,1),

                        (2,2),

                        (3,3)) UniqueColumn(StepNumber, RecipeStepId)

        For XML Raw('Recipe'), Root('Root')) Recipe;


Insert

Into    #RecipeSteps2(ID,Recipe)

Select  2 ID,

        (Select *

        From    (Values (1,1),

                        (2,3),

                        (3,2)) UniqueColumn(StepNumber, RecipeStepId)

        For XML Raw('Recipe'), Root('Root')) Recipe;

כל אחת מ-2 הפקודות הנ"ל כוללת תת שאילתה שהופכת את שלושת הצעדים במתכון למשתנה XML:

Select * From #RecipeSteps2;

clip_image002

כיצד שולפים את הנתונים מהטבלה?

Select  RS2.ID,

        RS2xml.Loc.query('.').value('(/Recipe/@StepNumber)[1]','int') StepNumber,

        RS2xml.Loc.query('.').value('(/Recipe/@RecipeStepId)[1]','int') RecipeStepId

From    #RecipeSteps2 RS2

Cross Apply Recipe.nodes('/Root/Recipe') As RS2xml(Loc)

Order By RS2.ID,

        StepNumber;

clip_image004

יש לשים לב שלא נוכל להכניס את המתכונים מחדש עם ID אחר, למשל- ננסה להכניס את הראשון עם ID=3:

Insert

Into    #RecipeSteps2(ID,Recipe)

Select  3 ID,

        (Select *

        From    (Values (1,1),

                        (2,2),

                        (3,3)) UniqueColumn(StepNumber, RecipeStepId)

        For XML Raw('Recipe'), Root('Root')) Recipe;

ונקבל הודעת שגיאה בשל כפילות באינדקס על העמודה המחושבת.

מה החסרונות? גם כאן יש מגבלות: גודל העמודה המחושבת מוגבל ל-900 תווים, כי אחרת לא נוכל ליצור עליה Unique Index. זה עדיין מאפשר לנו לכלול מתכונים הכוללים כמה עשרות תווים, בעיקר אם נשנה את שמות התגיות ב-XML לתגיות חסכוניות יותר בנות תו אחד; אך לא נצטרך לשנות את האינדקס בהתאם למספר השורות בכל מתכון, ולא ליצור אובייקט נוסף.


כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

4 × 1 =