ארכיון

Archive for the ‘שפת SQL’ Category

איך לבנות Data Access Layer כמו שצריך

אני מייעץ לכל מיני חברות: גדולות, בינוניות וגם קטנות. מטבע הדברים, בחברות הקטנות ישנה גמישות רבה יותר לרעיונות חדשים ונכונות לשנות ולתקן טעויות הסטוריות.

בחברה אחת שכזו נעשית חשיבה כיצד נכון בשבילם לכתוב את ה-DAL.

האפשרויות הן:

  1. כתיבת משפטי SQL בתוך הקוד. לרוב נבנה הטקסט של המשפט והוא נשלח לביצוע ל-DB.
  2. קריאה ל- Stored Procedures מתוך הקוד והעברת פרמטרים אליהם ומהם.
  3. שימוש ב-ORM או Object Relational Mapping כמו Hibernate או Entity Framework.

אופציה מספר 1 (SQL בתוך הקוד) היא הגרועה שבהם וזאת מכמה סיבות ואלו העיקריות שבהן:

  1. משפטי ה-SQL שבקוד אינם נבדקים בזמן קומפילציה ועלולים "להתעופף" בזמן ריצה אם התחביר שגוי.
  2. שינויים בסכימה (הוספת/ביטול עמודות, שינוי טיפוס נתונים, שינוי בשמות טבלאות וכו') גוררים שינויים בקוד. קשה למצוא את כל המקומות שיש לשנותם. ה-deploy בסביבת היצור חייב יהיה לשנות את הקוד שמעל ה-DB.
  3. השאילתות שנוצרות בקוד הן לרוב – שאילתות דינאמיות עם כל המשמעויות המתבקשות של בעיות ביצועים.

לאופציה מספר 2 (Stored procedures)  יש גם כן מגרעות (אבל פחות מאשר לאופציה מס' 1):

  1. קריאה לפרוצדורות היא סוג של יציאה מעולם האובייקטים לעולם רלציוני – מה ששובר את עקרונות עיצוב מונחה עצמים.
  2. במערכת בינונית ומעלה אפשר למצוא מאות ואפילו אלפי פרוצדורות. אי אפשר למצוא את הידיים ואת הרגליים בהם והרבה פעמים קורה שמפתח כתב stored procedure ולא ידע שמישהו אחר כתב את אותה פרוצדורה לפניו – רק עם שם אחר.
  3. הרבה פעמים הלוגיקה העסקית "מתפזרת" לתוך פרוצדורה ואז היא מצוייה גם בשכבת BLL וגם בשכבת ה-DAL.
  4. לא כל כך נוח לתכנת כל פעולת SQL בשני שלבים – פיתוח קריאה לפרוצדורה בשפה עילית ופיתוח הפרוצדורה בשפת ה-DB (כמו PL/SQL או T-SQL).

לאופציה מספר 3 (ORM) יש גם מגרעות:

  1. קוד ה-SQL שמחולל ע"י ORM עלול להיות לא יעיל בעליל. ככל שרמה הסיבוכיות עולה – הסיכוי שה-SQL שנוצר יהיה איטי גובר.
  2. עבודה עם ORM דורשת לימוד מעמיק של היכולות והמגבלות.
  3. קשה לנסח שאילתות מורכבות בממשק כמו LINQ או HQL. מה שהולך בקלות ב-SQL, הולך קשה ב-LINQ.

מה עושים?

לדעתי – יש לנסות לקחת את הטוב משתי האופציות 2 ו-3. כלומר – כן ליישם ORM – אבל לשלב אותו עם Stored procedure אותם יש לכתוב בשביל משימות שה-ORM מתקשה ליישם בצורה אופטימלית.

עיבוד טרנזקציות? נוח מאד ב-ORM. שאילתות מורכבות? לך על Stored Procedures וחבר אותם ל-ORM.

אשמח לשמוע על רעיונות נוספים.

שימוש בטבלאות של חתכי אוכלוסיה בבניית דו"חות

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

לצורך ההדגמה, נביא דוגמה מעולם ה-HR שבאופן מסורתי משמש בלימוד בסיסי נתונים.

נניח שיש לנו טבלת עובדים במבנה הבא (טיפוסי הנתונים הם של SQL Server אבל בקלות אפשר להמיר אותם לטיפוסים של אורקל או כל בסיס נתונים אחר):

EmpId int
EmpName varchar
DeptId int
Salary float
Bonus float

נבנה טבלת חתכי אוכלוסיה דינאמית במבנה הבא:

PopulationId int
PopulationDesc varchar
DeptId_Ind bit
DeptId_values varchar
Salary_Ind bit
Salary_From float
Salary_To float

נכניס ערכים לדוגמה בטבלה:

Salary_

To

Salary_

From

Salary_

Ind

DeptId_

values

DeptId_

Ind

Population

Desc

Population

Id

 

 

Null

1,2,5

True

Important employees

1

999999

20000

False

1,5

False

Nice employees

2

5000

0

True

10,11,12

True

Underpaid

employees

3

999999

30000

True

 

NULL

Overpaid employees

4

הגדרנו 4 חתכי אוכלוסיה. כעת נוכל ליצור דו"ח שיציג לנו את נתונים סיכומיים בכל חתך. מה בעצם מגדיר כל חתך:

  • החתך "Importance employees" מגדיר עובדים העובדים במחלקות 1 או 2 או 5.
  • החתך "Nice employees" מגדיר עובדים שלא עובדים מחלקות 1 או 5 (לא עובדים בגלל ש-DeptId_Ind הוא False) ואשר לא מרוויחים בין 20000 ו- 999999 ש"ח (לא מרוויחים בגלל ש- SalaryInd הוא False).
  • החתך "Underpaid employees" מגדיר עובדים העובדים במחלקות 10 או 11 או 12 ומשכורתם בין 0 ל- 5000 ש"ח
  • החתך "Overpaid employees" אינו מתייחס כלל למחלקות (DeptId_Ind הוא NULL) ומתייחס לעובדים המרויחים מעל 30000 ש"ח. 

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

Select  PopulationDesc, sum(salary) "Total Sal", avg(salary) "Avg. sal", sum(bonus) "Total Bonus" 

From employees e cross join emp_population ep

 Where ((ep.DeptId IN (select word from fn_split(e.deptId, ',') f) AND ISNULL(DeptId_Ind, 1) = 1)

         OR                  

       (ep.DeptId NOT IN (select word from fn_split(e.deptId, ',') f) AND ISNULL(DeptId_Ind, 0) = 0)     

   AND 

       ((e.Salary between ep.Salary_From and ep.Salary_To AND ISNULL(Salary_Ind, 1) = 1) 

        OR

        (e.Salary NOT between ep.Salary_From and ep.Salary_To AND ISNULL(Salary_Ind, 0) = 0)        

       (( 

זהו SQL שלקוח מ-SQL Server. באותו אופן אפשר להתאימו לאורקל ולהחליף את הפונקציה ISNULL ב-NVL.

שימו לב לפונקציה fn_split שאמורה להפוך מחרוזת מופרדת בפסיקים (או תו מפריד אחר) לטבלת ערכים. פונקציה כזו אפשר להוריד ממגוון אתרים כמו למשל:http://www.sqlservercentral.com/scripts/Miscellaneous/31913

באורקל מדובר ב-pipelined function ולכן הקריאה לפונקציה תהיה יחד עם שימוש בפונקציה table. דוגמה לפונקציה שכזו באורקל אפשר להוריד מהבלוג הזה:http://edgewaters.blogspot.com/2008/10/oracle-plsql-split-function-boosts.html

 דוגמה לפלט אפשרי:

Population Desc Total Sal Avg. Sal Total Bonus
Important Employees 100000 22000 280000
Nice Employees 120000 15000 100000
Under Paid employees 150000 3000 0
Over Paid employees 1000000 42000 700000

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