SQLite Generated Columns: VIRTUAL מול STORED עם דוגמאות

עמודה מחושבת היא עמודה שהערך שלה מחושב

עמודה מחושבת (generated column) היא עמודה שהערך שלה מגיע מביטוי, ולא מ-INSERT. מצהירים על הנוסחה פעם אחת ב-CREATE TABLE, ו-SQLite דואגת לכל השאר. אף פעם לא כותבים אליה, וניסיון לעשות זאת הוא שגיאה.

הדוגמה הקצרה ביותר האפשרית:

CREATE TABLE products (
    id      INTEGER PRIMARY KEY,
    price   REAL NOT NULL,
    tax     REAL NOT NULL,
    total   REAL GENERATED ALWAYS AS (price + tax)
);

INSERT INTO products (price, tax) VALUES (100.0, 8.5);
INSERT INTO products (price, tax) VALUES (49.99, 4.25);

SELECT id, price, tax, total FROM products;

total מעולם לא הוכנסה, אבל היא מופיעה בכל שורה. SQLite מחשבת אותה מחדש מ-price + tax בכל פעם שקוראים את השורה. עדכנו אחת מהעמודות, ו-total תתעדכן בהתאם.

צירוף המילים GENERATED ALWAYS AS הוא חובה. ה-ALWAYS הוא פורמליות של תקן SQL: אין ב-SQLite אפשרות אחרת.

VIRTUAL מול STORED

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

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- מחושבת מחדש בכל SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- מחושבת פעם אחת ונשמרת בדיסק
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

INSERT INTO orders (qty, unit) VALUES (3, 19.99);
SELECT * FROM orders;

המודל המנטלי:

• VIRTUAL: עולה אפס בתים בדיסק, ועולה זמן מעבד בכל קריאה. זולה להוספה וזולה לשינוי בהמשך.
• STORED: עולה מקום בדיסק, ולא עולה שום דבר נוסף בקריאה. משתלמת כשהביטוי יקר או כשקוראים את העמודה הרבה יותר משכותבים אליה.

אם לא כותבים מילת מפתח, מקבלים VIRTUAL. זו כמעט תמיד ברירת המחדל הנכונה.

למה בכלל? ערכים נגזרים שאפשר לאנדקס

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

נניח שאתם רוצים חיפוש אימייל שלא רגיש לאותיות גדולות וקטנות:

CREATE TABLE users (
    id           INTEGER PRIMARY KEY,
    email        TEXT NOT NULL,
    email_lower  TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);

CREATE INDEX users_email_lower ON users(email_lower);

INSERT INTO users (email) VALUES ('Ada@Example.com'), ('boris@example.com');

SELECT id, email FROM users WHERE email_lower = 'ada@example.com';

האינדקס מכסה את הצורה באותיות קטנות. שאילתה שמסננת לפי email_lower משתמשת באינדקס ישירות. ל-SQLite יש אפילו אינדקסים על ביטויים (CREATE INDEX ... ON users(lower(email))), אבל עמודה מחושבת הופכת את הערך הנגזר לעמודה אמיתית שאפשר לעשות לה SELECT, להפנות אליה ב-views ולהשתמש בה שוב מקוד האפליקציה.

חילוץ ערכים מ-JSON

עמודות מחושבות מבריקות מעל JSON. התמיכה של SQLite ב-JSON נותנת את ->> לחילוץ ערך סקלרי; עטפו אותו בעמודה מחושבת וקיבלתם שדה עם טיפוס שאפשר לאנדקס, מעל blob גמיש.

CREATE TABLE events (
    id       INTEGER PRIMARY KEY,
    payload  TEXT NOT NULL,
    user_id  INTEGER GENERATED ALWAYS AS (payload ->> '$.user_id') VIRTUAL,
    kind     TEXT    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL
);

CREATE INDEX events_user_id ON events(user_id);

INSERT INTO events (payload) VALUES
    ('{"user_id": 1, "kind": "login"}'),
    ('{"user_id": 2, "kind": "purchase"}'),
    ('{"user_id": 1, "kind": "logout"}');

SELECT id, kind FROM events WHERE user_id = 1;

user_id ו-kind נראות לשאילתות שלכם כמו עמודות רגילות, אבל הנתונים יושבים ב-payload. שנו את ה-JSON, והעמודות מתעדכנות. האינדקס על user_id הופך את החיפוש למהיר.

כללים ואילוצים

כמה דברים ש-SQLite אוכפת, וכדאי להכיר לפני שנתקלים בהם:

• הביטוי חייב להיות דטרמיניסטי. random(), datetime('now') ופונקציות לא דטרמיניסטיות אחרות אסורות. הערך צריך להיות ניתן לשחזור מהשורה.
• הביטוי יכול להפנות רק לעמודות באותה שורה. בלי תת-שאילתות, בלי צבירות, בלי טבלאות אחרות.
• אי אפשר לבצע INSERT או UPDATE ישירות לעמודה מחושבת. INSERT INTO products (total) VALUES (5) היא שגיאה.
• אי אפשר להוסיף עמודות STORED עם ALTER TABLE ... ADD COLUMN. רק VIRTUAL אפשר להוסיף בדיעבד.
• לעמודות מחושבות יכולים להיות אילוצי NOT NULL, CHECK, UNIQUE ואפילו FOREIGN KEY. מבחינה זו הן מתנהגות כמו כל עמודה אחרת.

הדגמה מהירה של כלל הכתיבה:

sqlite> INSERT INTO products (price, tax, total) VALUES (10, 1, 999);
Runtime error: cannot INSERT into generated column "total"

הפתרון הוא להוציא את העמודה המחושבת מרשימת ה-INSERT ולתת ל-SQLite לחשב אותה.

לבחור VIRTUAL או STORED

ההחלטה מסתכמת בדרך כלל ביחס בין קריאות לכתיבות ובעלות הביטוי:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- חשבון זול, אין בעיה לחשב מחדש בכל קריאה.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- ביטוי כבד יותר, נשלף לעתים קרובות, נכתב לעתים רחוקות → לשמור אותו.
    bucket      TEXT GENERATED ALWAYS AS (
        CASE
            WHEN celsius < 0  THEN 'freezing'
            WHEN celsius < 15 THEN 'cool'
            WHEN celsius < 25 THEN 'mild'
            ELSE 'hot'
        END
    ) STORED
);

INSERT INTO measurements (celsius) VALUES (-3), (10), (22), (31);
SELECT celsius, fahrenheit, bucket FROM measurements;

כללי אצבע:

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

עמודות מחושבות מול Views

יש חפיפה עם views: שניהם חושפים ערכים מחושבים בלי לשמור אותם (טוב, לפעמים). החלוקה היא בדרך כלל:

• עמודה מחושבת שייכת לשורה אחת ולטבלה אחת. השתמשו בה לנגזרות ברמת השורה: עיצוב אימייל, חילוץ שדה JSON, חישוב סכום.
• View הוא שאילתה שמורה. השתמשו בו כשהחישוב כולל JOIN, צבירה או סינון על פני שורות.

אפשר לשלב ביניהם. View יכול לבצע SELECT מטבלה שיש בה עמודות מחושבות ולצרף הקשר נוסף. עמודות מחושבות יושבות בשכבת האחסון; views יושבים בשכבת השאילתות.

הצעד הבא: ATTACH DATABASE

עמודות מחושבות מאפשרות לטבלה אחת לחשב ערכים משלה. העמוד הבא הולך בכיוון ההפוך: חיבור של כמה מסדי נתונים של SQLite בבת אחת עם ATTACH DATABASE, כך ששאילתה אחת יכולה להשתרע על פני כמה קבצים.