טבלאות STRICT ב-SQLite: אכיפה נכונה של טיפוסי עמודות

למה קיימות טבלאות STRICT

התנהגות ברירת המחדל של SQLite עם טיפוסים ידועה כרגועה במיוחד. הצהירו על עמודה כ-INTEGER, הכניסו את המחרוזת "hello", ו-SQLite מושכת בכתפיים ושומרת את המחרוזת. הגמישות הזו הייתה בחירת עיצוב מכוונת בשנות ה-90, אבל היא מפתיעה אנשים שמגיעים מ-Postgres או מ-MySQL, והיא מסתירה באגים.

טבלאות STRICT, שנוספו ב-SQLite 3.37, מתקנות את זה. מפעילים אותן לכל טבלה בנפרד, ומאותו רגע טיפוסי העמודות אומרים את מה שהם אומרים.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    age   INTEGER
) STRICT;

INSERT INTO users (name, age) VALUES ('Ada', 30);
SELECT * FROM users;

מילת המפתח STRICT באה אחרי הסוגריים הסוגרים. כל השאר נראה כמו CREATE TABLE רגיל. ההבדל מופיע ברגע שמנסים לשים סוג לא נכון של ערך בעמודה.

מה STRICT אוכפת בפועל

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

CREATE TABLE accounts (
    id      INTEGER PRIMARY KEY,
    balance INTEGER
) STRICT;

INSERT INTO accounts (balance) VALUES (100);     -- תקין
INSERT INTO accounts (balance) VALUES ('100');   -- תקין, '100' מומר בלי בעיה
INSERT INTO accounts (balance) VALUES ('oops');  -- שגיאה

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

השגיאה שתראו:

Runtime error: cannot store TEXT value in INTEGER column accounts.balance

ברורה, מיידית, קשה להתעלם ממנה.

חמשת הטיפוסים המותרים

טבלאות STRICT מקבלות רק חמישה שמות טיפוסים:

• INTEGER: מספרים שלמים.
• REAL: מספרים בנקודה צפה.
• TEXT: מחרוזות.
• BLOB: בתים גולמיים.
• ANY: כל טיפוס, בלי המרה.

זהו. הכינויים המזדמנים ש-SQLite מקבלת בדרך כלל, VARCHAR(255), DOUBLE, BOOLEAN, DATETIME, INT, כולם זורקים שגיאה בתוך טבלת STRICT:

CREATE TABLE bad (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(255)
) STRICT;

השגיאה:

Parse error: unknown datatype for bad.name: "VARCHAR(255)"

הפתרון הוא להשתמש באחד מחמשת השמות הקנוניים. VARCHAR(255) הופך ל-TEXT, DATETIME הופך ל-TEXT (SQLite שומרת תאריכים כמחרוזות ISO בכל מקרה), BOOLEAN הופך ל-INTEGER (עם 0 ו-1).

פתח המילוט ANY

ANY הוא הטיפוס היחיד שמאפשר לעמודת STRICT להחזיק ערכים מסוגים שונים: שימושי לדברים כמו עמודת value כללית בטבלת מפתח/ערך:

CREATE TABLE settings (
    key   TEXT PRIMARY KEY,
    value ANY
) STRICT;

INSERT INTO settings VALUES ('max_users', 100);
INSERT INTO settings VALUES ('site_name', 'Coddy');
INSERT INTO settings VALUES ('ratio', 1.5);

SELECT key, value, typeof(value) FROM settings;

ANY מיוחד בתוך טבלאות STRICT: הוא שומר ערכים בלי כפיית הטיפוס שאותה מילה הייתה מרמזת עליה במקומות אחרים. מחרוזת '100' נשארת מחרוזת, ומספר שלם 100 נשאר מספר שלם. הקריאות ל-typeof() בשאילתה שלמעלה מוכיחות את זה.

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

STRICT ו-PRIMARY KEY

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

בטבלת STRICT, טיפוס העמודה נאכף בלי קשר לשאלה אם היא המפתח הראשי:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL
) STRICT;

INSERT INTO products VALUES ('A-001', 'Widget', 9.99);
INSERT INTO products VALUES (42, 'Gizmo', 14.99);  -- האם 42 מומר ל-'42'? לא: שגיאה.

SELECT * FROM products;

ההכנסה השנייה נכשלת. בטבלה שאינה STRICT, 42 היה נשמר בשקט בעמודת המפתח הראשי מסוג TEXT. כאן אומרים לכם.

ערבוב טבלאות STRICT ורגילות

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

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

CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    payload   -- לא הוצהר טיפוס, חלים כללי הזיקה
);

INSERT INTO users (name) VALUES ('Boris');
INSERT INTO events (user_id, payload) VALUES (1, 'login');
INSERT INTO events (user_id, payload) VALUES (1, 42);

SELECT id, payload, typeof(payload) FROM events;

לטבלת events אין STRICT ואין טיפוס מוצהר ל-payload, ולכן היא מקבלת כל מה שזורקים עליה. שימושי מדי פעם, מסוכן כברירת מחדל. שמרו אחסון בלי טיפוס למקרים שבהם אתם באמת צריכים עמודת "הכול הולך".

מתי להשתמש ב-STRICT

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

ותרו על STRICT כש:

• אתם מתחזקים מסד נתונים ישן של SQLite שהסכמה הקיימת שלו מסתמכת על טיפוסים רופפים.
• אתם מכוונים ל-SQLite ישנה מ-3.37 (אוקטובר 2021): מילת המפתח לא קיימת שם.
• אתם באמת רוצים שעמודה תחזיק טיפוסים מעורבים. גם אז, העדיפו STRICT ועמודת ANY על פני טבלה שאינה STRICT, כי כל השאר נשאר נאכף.

רשימת בדיקה קצרה להמרת טבלה רגילה ל-STRICT:

• החליפו VARCHAR, CHAR, NVARCHAR ב-TEXT.
• החליפו DOUBLE, FLOAT, NUMERIC ב-REAL.
• החליפו BOOLEAN, BIT, TINYINT ב-INTEGER.
• החליפו DATETIME, TIMESTAMP, DATE ב-TEXT (או ב-INTEGER אם אתם שומרים חותמות זמן של unix).
• הוסיפו STRICT אחרי הסוגריים הסוגרים.

הבא: מפתחות ראשיים

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