ROWID ב-SQLite: מפתחות ראשיים נסתרים, כינויים ו-WITHOUT ROWID

לכל טבלה יש עמודה סודית

צרו טבלת SQLite רגילה, וכבר יש לכם עמודה אחת שלא הצהרתם עליה:

CREATE TABLE notes (
    title TEXT,
    body  TEXT
);

INSERT INTO notes (title, body) VALUES ('first', 'hello');
INSERT INTO notes (title, body) VALUES ('second', 'world');

SELECT rowid, title, body FROM notes;

עמודת ה-rowid הזו אמיתית. SQLite מקצה אחת כזו לכל שורה בכל טבלה רגילה, בין אם ביקשתם ובין אם לא. זה מספר שלם עם סימן באורך 64 ביט, ייחודי בתוך הטבלה, וזה המפתח האמיתי ש-SQLite משתמשת בו כדי למצוא שורות באחסון ה-B-tree שלה. חשבו עליו כעל עמוד השדרה של הטבלה: האינדקס שמחזיק את כל השאר מסודר.

בדרך כלל לא רואים אותו כי SELECT * לא כולל אותו. צריך לבקש אותו בשם.

ל-ROWID יש שלושה כינויים

מכיוון ש-rowid מופיע כל כך הרבה ב-SQL שנכתב למסדי נתונים אחרים, SQLite מקבלת שלושה שמות לאותה עמודה:

CREATE TABLE notes (title TEXT);
INSERT INTO notes VALUES ('a'), ('b'), ('c');

SELECT rowid, oid, _rowid_, title FROM notes;

rowid, oid ו-_rowid_ מתייחסים כולם לאותה עמודה נסתרת. אם הצהרתם על עמודה אמיתית עם אחד מהשמות האלה, העמודה שלכם מנצחת והכינוי לא זמין, אבל זה הקאץ' היחיד. בקוד היומיומי פשוט כתבו rowid.

INTEGER PRIMARY KEY הוא הביטוי הקסום

הנה החלק שמפיל את כל מי שמגיע ממסדי נתונים אחרים. אם מצהירים על עמודה בדיוק כ-INTEGER PRIMARY KEY, העמודה הזו לא נשמרת בנפרד: היא הופכת ל-rowid:

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT
);

INSERT INTO users (name) VALUES ('Ada'), ('Boris'), ('Rosa');

SELECT rowid, id, name FROM users;

rowid ו-id הם אותה עמודה בשני שמות. הכנסות שמשמיטות את id מקבלות מספר שלם שנבחר אוטומטית (בדרך כלל ה-rowid המקסימלי ועוד 1). זו הסיבה ש-INTEGER PRIMARY KEY היא הדרך היעילה ביותר לתת לטבלה ב-SQLite מפתח שעולה אוטומטית: אין עמודה נוספת, אין אינדקס נוסף, רק ה-rowid עצמו.

הכתיב המדויק חשוב. INT PRIMARY KEY הוא לא אותו דבר: ל-INT ול-INTEGER יש כאן השפעות שונות:

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE b (id INT PRIMARY KEY, name TEXT);

INSERT INTO a (name) VALUES ('x');
INSERT INTO b (name) VALUES ('y');

SELECT rowid, id, name FROM a;
SELECT rowid, id, name FROM b;

בטבלה a, id ו-rowid תואמים. בטבלה b, id היא עמודה רגילה ו-rowid הוא המספר השלם הנסתר הנפרד. גרוע מזה, b.id לא מתמלא אוטומטית בהכנסה: הוא NULL עד שמגדירים אותו. היצמדו ל-INTEGER PRIMARY KEY (המילה המלאה) כשאתם רוצים את התנהגות הכינוי.

קבלת ה-ROWID של הכנסה

אחרי INSERT רוצים לעתים קרובות לדעת איזה rowid הוקצה הרגע, בדרך כלל כדי לקשר אליו שורת ילד. SQLite נותנת לכם את last_insert_rowid():

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('first');
INSERT INTO posts (title) VALUES ('second');

SELECT last_insert_rowid();

הפונקציה מחזירה את ה-rowid של ההכנסה המוצלחת האחרונה בחיבור הנוכחי. רוב הדרייברים של מסדי נתונים חושפים את אותו ערך בתור cursor.lastrowid או משהו דומה. פסוקית RETURNING (שמוסברת בהמשך) היא דרך נוספת לקבל אותו בחזרה כחלק מההכנסה עצמה.

ROWID אינם קבועים

ה-rowid של שורה יציב כל עוד השורה קיימת, אבל הוא לא מזהה לכל החיים. VACUUM יכול למספר מחדש ערכי rowid, ואם מוחקים שורה, הכנסה עתידית יכולה להשתמש שוב במספר:

CREATE TABLE items (name TEXT);
INSERT INTO items VALUES ('a'), ('b'), ('c');

DELETE FROM items WHERE name = 'c';
INSERT INTO items VALUES ('d');

SELECT rowid, name FROM items;

שימו לב שהשורה החדשה עשויה להשתמש שוב ב-rowid הישן ועשויה שלא, תלוי בגרסה ובנסיבות: הנקודה היא שאי אפשר לסמוך על כך שהוא יישאר ייחודי לנצח. אם צריך מזהה ששורד מחיקות, VACUUM וייצוא, הצהירו על עמודת INTEGER PRIMARY KEY משלכם (שמקבעת את הערך לשורה הזו) ושקלו את מילת המפתח AUTOINCREMENT אם אתם צריכים במפורש ערכים שרק עולים ולעולם לא משמשים שוב.

טבלאות WITHOUT ROWID

לפעמים ה-rowid הוא תקורה שאתם לא רוצים, לרוב כשהמפתח האמיתי שלכם אינו מספר שלם. טבלת ערים שהמפתח שלה הוא השם, למשל, מקבלת שני מבנים: ה-B-tree של ה-rowid, ואינדקס נפרד על name כדי לאכוף את המפתח הראשי. WITHOUT ROWID מאחד אותם למבנה אחד:

CREATE TABLE cities (
    name       TEXT PRIMARY KEY,
    population INTEGER
) WITHOUT ROWID;

INSERT INTO cities VALUES ('Lisbon', 545000), ('Paris', 2102000);

SELECT * FROM cities WHERE name = 'Lisbon';
-- SELECT rowid FROM cities;   -- שגיאה: no such column: rowid

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

• אין rowid, oid או _rowid_: העמודות האלה פשוט לא קיימות.
• last_insert_rowid() לא מתעדכנת בהכנסות לטבלה הזו.
• קלט ופלט הדרגתי של BLOB וכמה תכונות שכפול אינם זמינים.
• לטבלה חייב להיות PRIMARY KEY מוצהר.

WITHOUT ROWID היא אופטימיזציה מחושבת, לא ברירת מחדל. השתמשו בה כשהמפתח הראשי אינו מספר שלם והטבלה גדולה או עתירת כתיבות. לטבלאות רגילות עם מפתח מספרי, מבנה ה-rowid הרגיל כבר אופטימלי.

המודל המנטלי

בתמצית:

• לכל טבלת SQLite רגילה יש מפתח נסתר של מספר שלם באורך 64 ביט בשם rowid.
• INTEGER PRIMARY KEY (בכתיב המדויק) הופך את העמודה שלכם לכינוי שלו.
• השתמשו ב-last_insert_rowid() כדי לקרוא מה הוקצה הרגע.
• אפשר להשתמש שוב ב-rowid אחרי מחיקות, ו-VACUUM יכול למספר אותם מחדש.
• טבלאות WITHOUT ROWID מוותרות על המפתח הנסתר ומשתמשות ישירות במפתח הראשי שהצהרתם עליו: שימושי למפתחות שאינם מספרים שלמים, אבל הן מוותרות על כמה תכונות.

ברוב הזמן לא חושבים על rowid בכלל. מצהירים על id INTEGER PRIMARY KEY, נותנים ל-SQLite לטפל במספור, וממשיכים הלאה. המנגנון חשוב כשמכווננים אחסון, קוראים סכמות קיימות, או תוהים למה INT PRIMARY KEY מתנהג אחרת מ-INTEGER PRIMARY KEY.

הבא: NOT NULL ו-DEFAULT

עכשיו כשהזהות של השורה מסודרת, השכבה הבאה היא לוודא ששאר העמודות מחזיקות ערכים הגיוניים. NOT NULL ו-DEFAULT הן שתי הפסוקיות שעושות את רוב העבודה הזו, והן הבאות בתור.