אתגר רקורסיבי ב SQL

ארכיטקט

שלום לכולם, שאלה די מורכבת, אני מקווה להיות ברור.

אני בונה מסד נתונים שיכול להכיל כל תוכן ספרותי עברי. שזה פרוייקט בפני עצמו, יעלה בל"נ לקוד פתוח.
בואו ניקח את התנ"ך כדוגמא לבעיה. המילה הראשונה בספר בראשית ממוקמת בתוך עץ די ברור כך: תנך>תורה>בראשית>פרק א>פסוק א>מילה 1.
עד כאן הכל בסדר. בפועל הכנתי באמת טבלה שכל רשומה בה מכילה מילה בודדת בלבד, וטבלה נוספת שבעצם מנהלת את העץ, כאשר בסופו של דבר כל מילה משוייכת למולקולה הקטנה ביותר בעץ, ויש הפנייה של מזהה המילה למזהה של אותו גרופ (כך אני קורא לצומת בעץ) שאליה היא משתייכת. כך שבעצם, הפסוק הוא הגרופ הקטן ביותר בעץ הזה (השלב שאחריו זאת כבר מילה בודדת שהיא אולי גרופ של אותיות ניקוד וטעמים, אבל לא נכנסתי לעומק הזה כרגע) ולכן כל מילה מצביעה על פסוק בטבלת הגרופים.

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

הנה המבנה:

CREATE TABLE [Contents].[ContentGroups]
(
	[ID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [GroupType] INT NULL, 
    [GroupName] NVARCHAR(50) NULL, 
    [SequenceNumber] INT NULL,
	[ParentGroupID] INT NULL, 
    [XMLData] XML NULL, 
    CONSTRAINT [FK_ContentGroups_ToGroupsAndDividingTypes] FOREIGN KEY (GroupType) REFERENCES [Contents].GroupsAndDividingTypes(ID) on update cascade
	
)


CREATE TABLE [Contents].[MainContent]
(
	[ID]   INT           NOT NULL IDENTITY, 
    [GroupID] int not null,
	[SequenceNumber] int not null,
	[Content] NVARCHAR(MAX) NOT NULL, 
    [ContentWithNiqqud] NVARCHAR(MAX) NULL, 
    [VersionID] INT NULL, 
    CONSTRAINT [PK_Content] PRIMARY KEY ([ID]), 
    CONSTRAINT [FK_Content_ToContentGroups] FOREIGN KEY ([GroupID]) REFERENCES [Contents].[ContentGroups]([ID]) on update cascade, 
    CONSTRAINT [FK_Content_ToVersions] FOREIGN KEY (VersionID) REFERENCES Contents.Versions(ID)
)

הבעיה שלי היא כדלהלן הואיל ויש חלוקות נוספות לאותו טקסט עצמו, בואו ניקח לדוגמא את הפרשיות (לא פרשת השבוע אל הפרשיות הפתוחות והסגורות) אם אני רוצה ליצור סופר גרופ של פרשיות, עלי בעצם להתנהל מול אותו תוכן בגרופים נוספים במקביל, שימו לב שאין לי שום ביטוח איפה חותכים אותי, כי פרשיות נחתכות גם באמצע פסוק. בתלמוד זה קיים בצורת דפים, שורות, לעומת פרקים, ואפילו סוגיות. כשכל אחד כזה יכול לחתוך שורה באמצע.

השלב הראשון בפתרון שלי היה כך:

CREATE TABLE [Contents].[ContentToSuperGroup]
(
	[ID] INT NOT NULL PRIMARY KEY IDENTITY, 
    [GroupID] INT NOT NULL, 
    [FromContentID] INT NOT NULL, 
    [ToContentID] INT NOT NULL, 
    [VersionID] INT NULL, 
    CONSTRAINT [FK_ContentToGroup_ToContentGroups] FOREIGN KEY ([GroupID]) REFERENCES [Contents].ContentGroups(ID)  ON DELETE cascade, 
    CONSTRAINT [FK_ContentToGroup_ToContent] FOREIGN KEY ([FromContentID]) REFERENCES [Contents].[MainContent](ID), 
    CONSTRAINT [FK_ContentToSuperGroup_ToContent2] FOREIGN KEY (ToContentID) REFERENCES [Contents].[MainContent](ID) 
)

כעת אני צריך לבנות סוג של פרוצדורת שירות, שתקבל מזהה של גרופ ותחזיר לי את התוכן שלו, כלומר תעשה סלקט על התוכן הרלוונטי. ובשביל זה אני צריך לרדוף אחרי עץ מטורף ב SQL (שזה אמנם אפשרי בשיטה שצויינה באותו פוסט) אבל נתקעתי כשרציתי לעשות אורדר ביי לפי הסדר על מנת לקבל מנעד נכון של רשומות (כי על ID מלאכותי אני לא יכול לסמוך, וכי מי ערב לי מתי יכניסו את מסכת ברכות ומתי את עוקצין??)

אז ניסיתי לעשות את זה:

CREATE VIEW [Contents].[GroupsPosition]
	AS 
 WITH cte (GroupID,ParentGroupID,Path,Total,PositionInTree,SequenceNumber,ParentSequenceNumber) AS
(
SELECT ID,ParentGroupID, cast (GroupName as nvarchar(max)) as Path,cast ([SequenceNumber] as bigint) as Total,1 as PositionInTree,[SequenceNumber],[SequenceNumber] as ParentSequenceNumber
FROM Contents.ContentGroups WHERE ParentGroupID IS NULL

UNION ALL

SELECT g.ID ,g.ParentGroupID,
CAST(cte.path + '\' + IsNull(GroupName,g.[SequenceNumber]) AS NVARCHAR(MAX)) as Path,cast (CONCAT(Total,'0',g.[SequenceNumber]) as bigint) as Total,cte.PositionInTree+1 as PositionInTree,g.[SequenceNumber],cte.SequenceNumber as ParentSequenceNumber
FROM  Contents.ContentGroups g INNER JOIN cte ON cte.GroupID  = g.ParentGroupID
)
SELECT * FROM cte
--ייתכן שיש לבצע את הרקורסיה בצורה הפוכה כלומר להתחיל מכל השכבה התחתית אלו שאין להם ילדים ולטפס במעלה העץ למצוא את ההורים

וזה עדיין לא מאפשר לי להגיע לאורדר ביי אמיתי, כלומר להגיע לתוכן כטבלה מסודרת מילה אחרי מילה. העמודה Total בכל צורה שלא גלגלתי אותה לא נתנה את השירות שאני מצפה אליו, מכיוון שהיא לוקחת בחשבון את כל עומק העץ, ויש מקומות שמגיעים לתוכן עם יותר צמתים ממקומות אחרים (לדוגמא: תנך>נביאים>מלכים>מלכים א>פרק א>פסוק א>מילה 1, זה עץ עם עומק 7 בגלל שהוספנו באמצע את מלכים)

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

מקווה שאני לא מטריח, זה באמת אתגר כבד, אני עובד עליו הרבה זמן בהתנדבות. אם נפתור את הבעיה הזו, זאת תהיה פריצת דרך בשביל הפרוייקט שאני עובד עליו ביחד עם עוד 3 מתנדבים בכירים מתחום התוכנה.

תודה.

פורסם במקור בפורום CODE613 ב22/07/2016 13:17 (+03:00)

dovid

הבעיה הזו הגיעה לפתחי לפני כשש שנים.
היה מדובר על התורה בלבד. ושמה יש פרשות, יש פרקים, ועמודים (ס"ת) שאין כל קשר בין כל חלוקה.
אחרי המחשבה, שאז השקעתי, תכננתי כזה דבר:
טבלה גולמית של המולקולה הקטנה ביותר, אצלי זה היה אות (!) עם מזהה (חלק זה לפחות אצלי בכלל לא היה בDB אלא בקובץ מת אבל בDB זה אותו רעיון).
בשביל החלוקות טבלה מטופשת שבה יש סוג חלוקה, ערך, ערך חלוקה מספרי, מזהה "מולקולה". למשל: פרשה, "בראשית", 1, 0. או עמוד, 25, null, 12400.
לחלוקות היררכיות כמו פרק>פסוק אפשר לעשות קימבון של שתיהם: פסוק, "כג:יד", 256, 12589. ואפשר לעשות בנפרד ולבנות טבלה להכיר את יחסי הגומלין ביניהם.
התשאול לא מסובך בכלל. בהינתן חלוקה קל לתת את המיקום ובהינתן מזהה של "מולקולה" אז מחפשים את הערכים הקרובים ביותר מכל סוג (Group) לכיון מטה (MAX של קטן מ).

ברוח זה גם המלצתי בנושא אחר לאברהם http://tchumim.com/topic/1047
שמה גם טענתי שלא צריך את הDB אבל גם במקומות שצריך (כמו הפרוייקט שהיה לי) עדיין אני חושב שזה המבנה הנכון.

פורסם במקור בפורום CODE613 ב22/07/2016 14:22 (+03:00)

תחומים - פורום חרדי מקצועי

אתגר רקורסיבי ב SQL