פסוקית RETURNING ב-SQLite: קבלת שורות מ-INSERT, UPDATE ו-DELETE

דרך לראות מה קרה הרגע

כשמריצים INSERT, UPDATE או DELETE, SQLite אומרת לכם כמה שורות הושפעו, אבל לא אילו שורות, או איך נראים הערכים הסופיים שלהן. הפתרון הקלאסי הוא SELECT נוסף אחר כך. זה שתי פניות, שתי פקודות, וחלון זמן קטן שבו מישהו אחר יכול לשנות את השורה ביניהן.

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

CREATE TABLE notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO notes (title) VALUES ('First note')
RETURNING id, title, created_at;

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

RETURNING נוספה ב-SQLite 3.35.0 (מרץ 2021). אם הפקודה שלכם נדחית כשגיאת תחביר, בדקו עם SELECT sqlite_version();: גרסאות ישנות לא מכירות את מילת המפתח.

קבלת המזהה שנוצר

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

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO users (email) VALUES ('rosa@example.com')
RETURNING id;

לפני RETURNING, הייתם מכניסים ואז קוראים ל-last_insert_rowid() (או למקבילה בדרייבר שלכם) על אותו חיבור. זה עדיין עובד, אבל זה קסם של מצב החיבור: קל לטעות בו עם מאגרי חיבורים או תהליכונים. RETURNING id מפורש, מקומי לפקודה, ועובד אותו דבר לא משנה מה מחזיק את החיבור.

אם הטבלה שלכם לא מצהירה על INTEGER PRIMARY KEY מפורש, עדיין אפשר לקבל את מזהה השורה המובלע:

CREATE TABLE logs (event TEXT);

INSERT INTO logs (event) VALUES ('startup')
RETURNING rowid, event;

לכל טבלת SQLite רגילה יש rowid, ו-RETURNING תחזיר אותו.

כמה עמודות וביטויים

RETURNING מקבלת את אותה צורה כמו רשימת העמודות של SELECT. רשמו עמודות, השתמשו ב-*, בנו ביטויים, תנו להם כינויים:

CREATE TABLE line_items (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL
);

INSERT INTO line_items (product, price, quantity)
VALUES ('Notebook', 4.50, 3)
RETURNING id, product, price * quantity AS subtotal;

RETURNING * שימושית כשרוצים הכול, כולל ברירות מחדל שמסד הנתונים מילא, בלי לקרוא בשם לכל עמודה:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (name) VALUES ('login')
RETURNING *;

רואים את ה-id החדש, את ה-name שהעברתם ואת חותמת הזמן ש-SQLite חישבה.

RETURNING עם UPDATE

ב-UPDATE, RETURNING נותנת את הערכים שאחרי העדכון: השורה כפי שהיא נראית אחרי שהשינויים שלכם הוחלו:

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance INTEGER NOT NULL
);
INSERT INTO accounts (name, balance) VALUES ('Ada', 100), ('Boris', 50);

UPDATE accounts
SET balance = balance + 25
WHERE name = 'Ada'
RETURNING id, name, balance;

מקבלים בחזרה את היתרה החדשה של Ada, 125, ולא את הישנה, 100. זה הופך את RETURNING למושלמת למונים אטומיים ולפעולות זיכוי וחיוב: לא צריך לקרוא, לחשב, לכתוב ולקרוא שוב.

אם ה-WHERE מתאים לכמה שורות, מקבלים שורה אחת בחזרה לכל שורה שהושפעה:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (name, price) VALUES ('A', 10), ('B', 20), ('C', 30);

UPDATE products
SET price = price * 1.1
RETURNING id, name, price;

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

RETURNING עם DELETE

ב-DELETE, RETURNING נותנת את השורות כפי שהיו רגע לפני המחיקה. שימושי לארכוב, לתיעוד ביקורת, או פשוט לאישור של מה שהוסר:

CREATE TABLE sessions (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expires_at TEXT NOT NULL
);
INSERT INTO sessions (user_id, expires_at) VALUES
    (1, '2024-01-01'), (2, '2024-01-02'), (3, '2026-01-01');

DELETE FROM sessions
WHERE expires_at < '2025-01-01'
RETURNING id, user_id, expires_at;

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

RETURNING עם UPSERT

RETURNING עובדת גם עם INSERT ... ON CONFLICT ... DO UPDATE. השורה שחוזרת משקפת את הענף שרץ: ההכנסה החדשה או העדכון בעקבות ההתנגשות:

CREATE TABLE counters (
    name TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('visits', 1)
ON CONFLICT(name) DO UPDATE SET value = value + 1
RETURNING name, value;

הריצו את הפקודה פעמיים. בפעם הראשונה היא מכניסה ומחזירה ('visits', 1). בפעם השנייה ההתנגשות מופעלת, הערך גדל, ומקבלים בחזרה ('visits', 2). בכל מקרה, פקודה אחת ושורה אחת בפלט: אין צורך לשאול "האם זה הכניס או עדכן?" לפני שממשיכים.

זה הדפוס הנקי ביותר ב-SQLite ל"תנו לי את הערך הנוכחי, וצרו אותו אם צריך" בלי פניות חוזרות.

כמה דברים שכדאי לדעת

כמה פרטים שמפתיעים אנשים:

• RETURNING תמיד רואה את השורה אחרי השינוי ב-INSERT וב-UPDATE, ו_לפני_ השינוי ב-DELETE. אין תחביר לבקש את הצד השני.
• סדר השורות שחוזרות אינו מובטח. הוסיפו ORDER BY בצד הלקוח אם זה חשוב.
• אי אפשר לשים RETURNING בתוך תת-שאילתה. זו פסוקית ברמה העליונה של פקודת הכתיבה, לא ביטוי.
• RETURNING לא מחזירה נתונים ששונו על ידי טריגרים מסוג BEFORE: היא מחזירה את הערכים שבאמת נכתבו. טריגרים מסוג AFTER רצים בין הכתיבה לבין החזרת השורה.
• עמודות מחושבות וערכי DEFAULT נראים בתוצאה. זו בדיוק הסיבה ש-RETURNING * היא דרך מהירה לבדוק מה מסד הנתונים מילא בשבילכם.

הבא: ייבוא נתוני CSV

RETURNING מצוינת כשכותבים שורה אחת או כמה בכל פעם ורוצים לראות את התוצאה מיד. כשטוענים אלפי שורות מקובץ, משתמשים במקום זה בכלי ייבוא ה-CSV של SQLite, וזה העמוד הבא.