הבלוג של ינון פרק

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

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

זה לקח המון זמן אבל גירסה 22.5 של node.js הפכה את העבודה עם SQLite להרבה יותר קלה באמצעות שילוב הספריה כמודול מובנה. זה תקציר של ה API:

import { DatabaseSync } from 'node:sqlite';
const database = new DatabaseSync(':memory:');

// Execute SQL statements from strings.
database.exec(`
  CREATE TABLE data(
    key INTEGER PRIMARY KEY,
    value TEXT
  ) STRICT
`);

// Create a prepared statement to insert data into the database.
const insert = database.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
// Execute the prepared statement with bound values.
insert.run(1, 'hello');
insert.run(2, 'world');

// Create a prepared statement to read data from the database.
const query = database.prepare('SELECT * FROM data ORDER BY key');

// Execute the prepared statement and log the result set.
console.log(query.all());

הפקודה exec פשוט מריצה קוד SQL, הפקודה prepare יוצרת פקודת SQL עם משתנים קשורים להרצה ואחרי שיצרתם פקודה מוכנה להרצה תוכלו להשתמש ב run כדי להריץ אותה או ב all כדי להריץ אותה ולקבל חזרה תוצאות.

ספריית SQLite כבר קיימת כמודול מובנה גם ב bun אבל כמובן עם API שונה, ורק deno נשארו מחוץ למשחק פה והשאירו את SQLite בתור ספריה חיצונית שצריך להתקין בנפרד.

כרגע כל ה API סינכרוני והכי חשוב בשביל להפעיל את הקוד יש להוסיף מתג ניסיוני לשורת הפקודה כלומר:

node --experimental-sqlite testdb.mjs

ממש ברגעים אלה הם מוסיפים את ההערה על המתג הניסיוני לעמוד התיעוד, תוכלו לבדוק אם זה כבר עלה לאוויר ולקרוא יותר פרטים על ה API בדף התיעוד כאן: https://nodejs.org/api/sqlite.html#sqlite

התוכנית המקורית שלי לפוסט סופשבוע היתה לפתור ולפרסם עוד יום של Advent Of Code, אבל מפה לשם את כל זמן הכתיבה בזבזתי על שאילתה מסובכת מדי לבסיס הנתונים שבסוף גם היא לא עבדה, ולכן במקום אני רוצה לשתף שלוש נקודות אליהן כדאי לשים לב כשהולכים מכות עם שאילתות (ובעצם עם פריימוורק באופן כללי)-

1. לפעמים זה שווה את המאמץ. יש שאילתות SQL שאחרי שמצליחים לכתוב אותן מבינים משהו חדש על SQL. כן יש דבר כזה Window Functions ואם צריך להשקיע עכשיו שעתיים בללמוד איך הן עובדות זה ממש שווה את ההשקעה.

2. לפעמים השאילתה באמת מסובכת מדי ולא שווה את המאמץ. ראינו כבר מספיק שאילתות מסובכות מדי שבסוף היה צריך לשכתב לשאילתות פשוטות יותר או להעביר חלק מהלוגיקה פנימה לקוד כי ה DB לא עמד בעומס.

3. לפעמים שאילתות מסובכות הן רמז לבעייה במבנה הטבלאות.

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

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

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

האתגר בקצרה ברמת הטבלאות נשמע כך-

1. יש טבלה בשם bundle שמחזיקה נתונים על קורסים. אנחנו רוצים לקבל את ה id של כל הקורסים שתלמיד סיים.

2. יש טבלה בשם lessons שמחזיקה את כל השיעורים. לכל שיעור גם יש id ובגלל ששיעור שייך לקורס יש לו גם bundle_id.

3. יש טבלה בשם user_in_lessons שבשביל הסיפור שלנו מספיק לחשוב שהיא מחזיקה שתי עמודות user_id ו lesson_id. אם יש בה שורה למשתמש ושיעור מסוים זה אומר שהמשתמש ראה את כל השיעור.

4. טבלה אחרונה בסיפור היא users. אנחנו מתחילים עם id מסוים מטבלה זו.

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

SELECT bundles.id
FROM bundles
JOIN lessons ON bundles.id = lessons.bundle_id
WHERE NOT EXISTS (
  SELECT *
  FROM lessons AS l
  LEFT JOIN user_in_lesson AS uil ON l.id = uil.lesson_id
  WHERE uil.user_id = [user_id] AND l.bundle_id = bundles.id
) IS NULL;

ונכון, היא לא עובדת, אבל היי יש פה רעיון מעניין, באמת אפשר להשתמש ב Sub Queries כדי לרוץ על כל הקורסים בלולאה בתוך בסיס הנתונים. רק צריך לתקן את תתי השאילתות ומגיעים לגירסה שבסוף הכנסתי לאתר (מתורגמת כבר לריילס):

@bundles = Bundle
.joins(:lessons)
.where("
  (SELECT count(*)
   FROM lessons AS l
   LEFT JOIN user_in_lessons AS uil ON l.id = uil.lesson_id
   WHERE uil.user_id = ? AND l.bundle_id = bundles.id)
   =
  (SELECT count(*) FROM lessons where lessons.bundle_id = bundles.id)", user_id).distinct

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

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

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

ש: איך אני מריץ שאילתת SQL מתוך הדפדפן?

ת: אתה לא

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

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

לאחרונה התחלתי לעבוד יותר ב knex.js בסביבת Node ובינתיים מאוד נהנה מהספריה והיכולות שלה. קנקס היא בעצם בונה שאילתות, קצת דומה ל Sequelize, רק שבמקום מבנה של ORM קנקס אימצה גישה יותר פתוחה ויותר קרובה ל SQL. הם לא מנסים להסתיר מכם את העובדה שאתם עובדים ב SQL, אלא לתת תחביר JavaScript קצת יותר גמיש במקום התחביר הטקסטואלי של SQL.

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

בשביל להבין את הגישה של קנקס טוב יותר ולהתרשם מהתחביר הנה 14 דוגמאות לשאילתות SQL לא טריוויאליות באמצעותו.

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

אנחנו רוצים למצוא מהו השיעור הארוך ביותר בכל פרק ובשאילת אחת.

בשביל לקבל תמונה של המידע בואו נתחיל עם שאילתה פשוטה:

select id, chapter_id, course_id, length from lessons where course_id = 2;

 id | chapter_id | course_id | duration
----+-------------------+-----------+--------
 17 |                 3 |         2 |     40
 18 |                 3 |         2 |     35
 20 |                 3 |         2 |     50
 22 |                 4 |         2 |     60
 21 |                 4 |         2 |     65
 19 |                 3 |         2 |     55
 23 |                 3 |         2 |     45
  9 |                 1 |         2 |      1
 24 |                 2 |         2 |     30
 13 |                 2 |         2 |     20
 70 |                 3 |         2 |     43
 71 |                 3 |         2 |     48
 11 |                 1 |         2 |     10
 10 |                 1 |         2 |      5
 12 |                 1 |         2 |     15
 14 |                 2 |         2 |     25
(16 rows)

ואנחנו רוצים לקבל רק את השורות:

 course_id | id | chapter_id | duration
-----------+----+-------------------+--------
         2 | 21 |                 4 |     65
         2 | 19 |                 3 |     55
         2 | 24 |                 2 |     30
         2 | 12 |                 1 |     15
(4 rows)

כלומר לכל פרק אנחנו רוצים רק את השיעור הארוך ביותר באותו פרק.

רעיון ראשון יהיה אולי GROUP BY, אבל מהר מאוד אתם תיזכרו שעם GROUP BY אנחנו מאבדים את כל השדות שלא בקבוצה, ולכן הוא לא יאפשר לנו לשלוף את ה id של השיעורים.

אבל GROUP BY כן יכול לתת לנו את ההתחלה של הפיתרון. קודם כל נשלוף את כל הנתונים בקבוצה לפי הפרק, ואחרי זה נעשה השלמה עם JOIN מהטבלא הראשית.

השליפה הראשונה לפי הפרק נראית כך:

select chapter_id, max(duration) as max_duration from lessons where course_id = 2 group by chapter_id;

 chapter_id | max_duration
------------+--------------
          1 |           15
          2 |           30
          3 |           55
          4 |           65

עכשיו אני יודע מה אתם חושבים: למה שלא תוסיף id לשליפה ונקרא לזה יום? נו, גם אני חשבתי את זה. זה מה שלפוסטגרס היה להגיד בתגובה:

ERROR: column "lessons.id" must appear in the GROUP BY clause or be used in an aggregate function

הוא צודק כמובן. במקום זה אנחנו צריכים להשלים את הנתון בדרך אחרת:

select l.course_id, l.id, f.chapter_id, l.duration
from (
    select chapter_id, max(duration) as max_duration
    from lessons group by chapter_id
) as x
inner join
lessons as l on l.chapter_id = x.chapter_id and l.duration = x.max_duration where l.course_id = 2;

בעזרת JOIN אני משלים את הנתונים החסרים מהטבלא המקורית וכך מקבל את כל המידע.

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

=> select distinct on(chapter_id) id, chapter_id, duration from lessons where course_id = 2 order by chapter_id, duration desc;

 id | chapter_id | duration
----+-------------------+--------
 12 |                 1 |     15
 24 |                 2 |     30
 19 |                 3 |     55
 21 |                 4 |     65
(4 rows)

הפוסט הבא הוא יותר תזכורת בשבילי מאשר בשבילכם, למרות שאם אתם כותבים SQL יכול להיות שגם לכם זה יבוא בהפתעה. מסתבר שהשאילתה הבאה לא עובדת טוב בכלל:

SELECT  courses.*, count(lessons.id) as lessons_count,
    count(course_labels.id) as labels_count
    FROM "courses"
    LEFT OUTER JOIN "course_labels" ON "course_labels"."course_id" = "courses"."id"
    LEFT OUTER JOIN "lessons" ON "lessons"."course_id" = "courses"."id"
    GROUP BY courses.id

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

הבעיה עם השאילתה היא שה join הכפול גורם לשכפול של כל השורות בטבלה השניה - במילים אחרות כך נראית התוצאה כשאני מוריד את ה count וה group by:

sqlite> SELECT courses.id, lessons.id as lessons_count, course_labels.id as labels_count_1 FROM "courses" LEFT OUTER JOIN "course_labels" ON "course_labels"."course_id" = "courses"."id" LEFT OUTER JOIN "lessons" ON "lessons"."course_id" = "courses"."id";
id|lessons_count|labels_count_1
1|1|6
1|2|6
1|3|6
1|5|6
2|4|
3||

ה id של ה course_label היחיד במערכת הבדיקה שלי הוא 6, והוא מופיע 4 פעמים כדי להתאים ל-4 שיעורים שיש בקורס.

הפיתרון במקרה של ספירה הוא ממש פשוט ומורכב מהמילה היחידה distinct. הנה השאילתה המתוקנת:

SELECT 
    courses.*,
    count(distinct lessons.id) as lessons_count,
    count(distinct course_labels.id) as labels_count_1
FROM "courses"
    LEFT OUTER JOIN "course_labels" ON "course_labels"."course_id" = "courses"."id"
    LEFT OUTER JOIN "lessons" ON "lessons"."course_id" = "courses"."id"
GROUP BY courses.id