עם העבודה על jiyuu, יוצא לי לא מעט לעבוד עם sqlite. למי שלא מכיר, זה מסד נתונים אשר מיושם בספריה, ללא שרת, ללא צורך בהתקנה ועם תמיכה ב-Transactions.
סכמת מסד הנתונים של jiyuu אינה מסובכת כל כך. היא כוללת כמה טבלאות פשוטות שמחזיקות פוסטים, תגובות, תגיות וקונפיגורציה. אבל, למרות הפשטות, היה חסר לי מאפיין אחד חשוב, שיעזור לי לשמור על הנקיון ועל תקינות המידע: Foreign Keys.
הסבר מהיר: Foreign Keys הוא מנגנון במסדי נתונים שמאפשר יצירת קשר בין טבלאות שונות. בעזרת Foreign Key ניתן לקשר מידע בטבלה א' למידע בטבלה ב' כך שלא יכול להיות מידע בעמודה מקושרת בטבלה א' שאינו קיים בטבלה ב'. קישור זה הוא בד"כ דו-כיווני: לא ניתן יהיה למחוק מידע מטבלה ב', כל עוד ישנם רשומות בטבלה א' שעדיין מפנות אליה.
דוגמא:
CREATE TABLE tbl1 (
id INTEGER PRIMARY KEY NOT NULL,
col1 VARCHAR(20),
col2 INTEGER NOT NULL CONSTRAINT fk_tbl2_id REFERENCES tbl2(id))
זהו התחביר ליצירת Foreign Keys ב-SQLite (אפילו שהוא לא תומך ב-Foreign Keys. עוד על זה מיד). לפי הדוגמא, לא ניתן יהיה להכניס שורה חדשה לטבלה tbl1, במידה והערך של col2 לא יהיה קיים ב-tbl2. בכיוון השני, ברגע שיש מידע שבטבלה tbl1 אשר מקושר ל-tbl2, לא ניתן יהיה למחוק את הרשומה שאליה המידע קשור ב-tbl2.
רק רגע, אבל הרגע אמרתי ש-Foreign Keys לא נתמך ב-SQLite. המצב כרגע הוא שהפקודות קיימות, אך SQLite לא מתייחס אליהם או עושה להם Parsing\Enforcing. אז איך כן ניתן ליישם Foreign Keys שישמרו על אחידות המידע שלי? התשובה פשוטה: Triggers
הפוסט הזה הופך לשעור ב-SQL… אז מה זה Triggers? אלו פקודות SQL אשר מורות למסד הנתונים לבצע פקודה מסויימת כאשר מתרחש מקרה מסויים. ניתן לנצל יכולת זו כדי לתפוס כל מחיקה, יצירה או שינוי של רשומה כדי לוודא שאין התנגשות עם Foreign Key. נצטרך לצור 3 Trigger-ים: אחד עבור יצירה, מחיקה ועדכון רשומה, בעבור כל טבלה שבא הגדרנו Foreign Key Constraint
CREATE TRIGGER fk_insert_tbl1
BEFORE INSERT ON tbl1
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "tbl1" violates foreign key constraint "fk_insert_tbl1"')
WHERE (SELECT id FROM tbl2 WHERE id = NEW.col2) IS NULL;
END;
CREATE TRIGGER fk_update_tbl1
BEFORE UPDATE ON tbl1
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'update on table "tbl1" violates foreign key constraint "fk_update_tbl1"')
WHERE (SELECT id FROM tbl2 WHERE id = NEW.col2) IS NULL;
END;
CREATE TRIGGER fk_delete_tbl2
BEFORE DELETE ON tbl2
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'delete on table "tbl2" violates foreign key constraint "fk_delete_tbl2"')
WHERE (SELECT col2 FROM tbl1 WHERE col2 = OLD.id) IS NOT NULL;
END;
הרבה מידע, אני יודע. אבל אם מתעמקים טיפה, לא מסובך מדי. 2 הדוגמאות הראשונות בעצם דואגות כי לפני יצירה\שינוי ב-tbl1, ייבדק כי קיים ערך מתאים ב-tbl2. הדוגמא השלישית פועלת בכלל על tbl2 בזמן מחיקת רשומה, ומוודאת כי אין רשומות מקושרות ב-tbl1. שימו לב כי אם השדה col2 ב-tbl1 לא היה מוגדר כ-NOT NULL, הפלט היה קצת אחר, אם לדוגמא ניקח את ה Insert Trigger הבא:
CREATE TRIGGER fk_insert_tbl1
BEFORE INSERT ON tbl1
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "tbl1" violates foreign key constraint "fk_insert_tbl1"')
WHERE NEW.col2 IS NOT NULL AND (SELECT id FROM tbl2 WHERE id = NEW.col2) IS NULL;
END;
התוספת היא השורה WHERE NEW.col2 IS NOT NULL, אשר מוודא כי השדה החדש אינו ריק לפני שה-Foreign Key נאכף.
למחפשים עוד מידע, את הדוגמאות שלי שאבתי מהקישור הבא, והשתמשתי בכלי הבא כדי לייצר Triggers מה-Foreign Keys שלי.
אז עכשיו יש ל-jiyuu מסד נתונים מסודר, נקי ונכון. וכדי להפוך את הסכמה לקריאה יותר, השארתי את פקודות ב-Foreign Key המקוריות.
