SQLite DELETE: מחיקת שורות בבטחה עם WHERE ו-RETURNING

DELETE מסירה שורות, ולא שום דבר אחר

DELETE מוציאה שורות מטבלה. היא לא מוחקת את הטבלה, לא משנה את הסכמה שלה ולא נוגעת בטבלאות אחרות (אלא אם הגדרתם מחיקות מדורגות). המבנה קצר:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com', 1),
    ('boris@example.com', 0),
    ('rosa@example.com', 1);

DELETE FROM users WHERE id = 2;

SELECT * FROM users;

DELETE FROM users WHERE id = 2; מוצאת שורות שמתאימות לתנאי ומסירה אותן. שתי השורות האחרות לא נפגעות. הטבלה עצמה עדיין קיימת: אפשר להמשיך להכניס אליה.

המודל המחשבתי: DELETE היא SELECT שזורקת את השורות המתאימות במקום להחזיר אותן.

פסוקית ה-WHERE עושה את כל העבודה

כל DELETE רצינית חיה ומתה לפי פסוקית ה-WHERE שלה. כתבתם אותה נכון? הסרתם את מה שהתכוונתם. טעיתם? מחקתם יותר מהטבלה, ולפעמים את כולה.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    published INTEGER NOT NULL DEFAULT 0,
    views INTEGER NOT NULL DEFAULT 0
);

INSERT INTO posts (title, published, views) VALUES
    ('Draft one',   0, 0),
    ('Old hit',     1, 5000),
    ('Published',   1, 120),
    ('Empty draft', 0, 0);

DELETE FROM posts WHERE published = 0 AND views = 0;

SELECT * FROM posts;

שתי הטיוטות שלא פורסמו ושאין להן צפיות נעלמו. השורות שפורסמו שרדו כי התנאי לא התאים להן. אפשר להשתמש בכל ביטוי ש-WHERE מקבלת: IN, LIKE, BETWEEN, תתי שאילתות, צירופים של AND/OR.

הרגל ששווה לפתח: לפני שמריצים DELETE, הריצו קודם את אותה פסוקית WHERE כ-SELECT.

-- תצוגה מקדימה של מה שיימחק:
SELECT * FROM posts WHERE published = 0 AND views = 0;

-- מרוצים מהשורות? עכשיו מחקו אותן:
DELETE FROM posts WHERE published = 0 AND views = 0;

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

DELETE בלי WHERE מרוקנת את הטבלה

השמיטו את WHERE ו-DELETE תסיר כל שורה:

CREATE TABLE log (
    id INTEGER PRIMARY KEY,
    message TEXT
);

INSERT INTO log (message) VALUES ('one'), ('two'), ('three');

DELETE FROM log;

SELECT COUNT(*) AS remaining FROM log;

הטבלה ריקה אבל עדיין קיימת. ל-SQLite אין פקודת TRUNCATE: DELETE FROM table; היא המקבילה, ו-SQLite מפעילה "truncate optimization" פנימית שמשחררת את כל הדפים בבת אחת במקום להסיר שורות אחת אחת. מהיר, אבל עדיין פעולה טרנזקציונית שאפשר לבטל.

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

DELETE FROM log;
DELETE FROM sqlite_sequence WHERE name = 'log';

ל-INTEGER PRIMARY KEY רגיל (בלי AUTOINCREMENT), SQLite כבר משתמשת שוב במזהים בחופשיות, כך שאין בזה צורך.

מחיקת כמה שורות מסוימות

IN היא הדרך הנקייה ביותר למחוק קבוצה ידועה של שורות:

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

INSERT INTO tasks (title, status) VALUES
    ('write docs',   'done'),
    ('review PR',    'todo'),
    ('deploy',       'done'),
    ('write tests',  'todo'),
    ('archive',      'done');

DELETE FROM tasks WHERE id IN (1, 3, 5);

SELECT * FROM tasks;

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

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT,
    status TEXT
);
CREATE TABLE archived_status (status TEXT);

INSERT INTO tasks (title, status) VALUES
    ('a', 'done'), ('b', 'todo'), ('c', 'cancelled');
INSERT INTO archived_status VALUES ('done'), ('cancelled');

DELETE FROM tasks
WHERE status IN (SELECT status FROM archived_status);

SELECT * FROM tasks;

SQLite לא תומכת בתחביר DELETE ... JOIN כמו ש-MySQL תומכת, אבל תת שאילתה ב-WHERE עושה את אותה עבודה.

RETURNING: לראות מה מחקתם

הוסיפו RETURNING כדי לקבל בחזרה את השורות שנמחקו כתוצאה, בדיוק כמו SELECT:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO users (email, active) VALUES
    ('ada@example.com',   1),
    ('boris@example.com', 0),
    ('carol@example.com', 0);

DELETE FROM users
WHERE active = 0
RETURNING id, email;

תקבלו בחזרה את ה-id וה-email של כל שורה שנמחקה. זה לא יסולא בפז עבור:

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

RETURNING עובדת על INSERT, UPDATE ו-DELETE. יש לה עמוד משלה עם כל הפרטים.

ON DELETE CASCADE לשורות קשורות

כשטבלת הורה וטבלת ילד מקושרות ב-מפתח זר, מחיקת ההורה משאירה ילדים יתומים, אלא אם אומרים ל-SQLite למחוק באופן מדורג:

PRAGMA foreign_keys = ON;

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

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Le Guin'), (2, 'Borges');
INSERT INTO books (title, author_id) VALUES
    ('A Wizard of Earthsea', 1),
    ('The Dispossessed',     1),
    ('Ficciones',            2);

DELETE FROM authors WHERE id = 1;

SELECT * FROM books;

מחיקת הסופרת מוחקת גם את הספרים שלה. בלי ON DELETE CASCADE, אותה DELETE הייתה מצליחה ומשאירה ספרים יתומים (אם מפתחות זרים כבויים) או נכשלת עם שגיאת אילוץ (אם הם פועלים).

המלכודת הגדולה: מפתחות זרים כבויים כברירת מחדל ב-SQLite. צריך להריץ PRAGMA foreign_keys = ON; בכל חיבור. אם ה-pragma לא הוגדר, ON DELETE CASCADE פשוט מתעלמת בשקט, והספרים נשארים. רוב הדרייברים של אפליקציות מגדירים את זה בשבילכם או חושפים אפשרות; בדקו את שלכם.

אפשרויות מדורגות נוספות ששווה להכיר: ON DELETE SET NULL (מנקה את המפתח הזר), ON DELETE RESTRICT (מסרבת למחיקה אם יש ילדים), ON DELETE NO ACTION (ברירת המחדל, ברוב המקרים זהה ל-RESTRICT).

DELETE עם LIMIT (אפשרות בזמן קומפילציה)

חלק מגרסאות ה-build של SQLite תומכות ב-DELETE ... LIMIT, שימושי לכרסום בטבלאות ענקיות במנות:

DELETE FROM logs
WHERE created_at < '2024-01-01'
ORDER BY created_at
LIMIT 1000;

זה דורש ש-SQLite תהיה מקומפלת עם SQLITE_ENABLE_UPDATE_DELETE_LIMIT. בקבצים הרשמיים וברוב הספריות לשפות (sqlite3 של Python, better-sqlite3 של Node) האפשרות מופעלת. אם אצלכם לא, תקבלו שגיאת תחביר: עברו לתת שאילתה:

DELETE FROM logs
WHERE id IN (
    SELECT id FROM logs
    WHERE created_at < '2024-01-01'
    ORDER BY created_at
    LIMIT 1000
);

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

עטפו מחיקות גדולות בטרנזקציה

DELETE היא טרנזקציונית באופן מובלע: או שכל השורות המתאימות נמחקות או שאף אחת לא. אבל כשאתם עומדים למחוק הרבה, עטיפה בטרנזקציה מפורשת אומרת שאפשר לעשות ROLLBACK אם משהו נראה לא בסדר:

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

INSERT INTO accounts (balance) VALUES (100), (200), (0), (50), (0);

BEGIN;
    DELETE FROM accounts WHERE balance = 0;
    SELECT COUNT(*) AS remaining FROM accounts;
ROLLBACK;

SELECT COUNT(*) AS still_here FROM accounts;

ROLLBACK מבטל את המחיקה לגמרי. בסשן אמיתי, הייתם מבצעים COMMIT ברגע שהספירה נראית נכונה. טרנזקציות גם מהירות בהרבה כשמוחקים הרבה שורות בפקודה אחת בכל פעם: עטיפת הלולאה ב-BEGIN/COMMIT חוסכת fsync אחד לכל מחיקה.

דברים שלא מוחקים

כמה בלבולים נפוצים ששווה לציין:

• DELETE FROM table; מרוקנת את הטבלה אבל לא מוחקת אותה. השתמשו ב-DROP TABLE table; כדי להסיר את הטבלה עצמה.
• DELETE לא מקטינה את קובץ מסד הנתונים. הדפים מסומנים כפנויים לשימוש חוזר. כדי לשחרר מקום בדיסק, הריצו VACUUM; (מכוסה בפרק על ביצועים).
• מחיקת שורה לא מוחקת שורות ילד בטבלאות אחרות, אלא אם ON DELETE CASCADE מוגדרת וגם מפתחות זרים מופעלים.
• DELETE שלא מתאימה לאף שורה היא לא שגיאה. זו פקודה מוצלחת עם changes() = 0. בדקו את מספר השורות אם אתם צריכים לדעת.

הבא בתור: UPSERT

לעיתים קרובות אתם לא באמת רוצים למחוק: אתם רוצים להכניס שורה אם היא חדשה, או לעדכן אותה אם היא כבר קיימת. SQLite קוראת לזה UPSERT, והפסוקית ON CONFLICT הופכת את זה לפקודה אחת במקום שלוש. זה מה שבא אחר כך.