Kolumny generowane w SQLite: VIRTUAL a STORED z przykładami

Kolumna generowana to kolumna wyliczana

Kolumna generowana to kolumna, której wartość pochodzi z wyrażenia, a nie z INSERT. Wzór deklarujesz raz w CREATE TABLE, a resztą zajmuje się SQLite. Nigdy do niej nie zapisujesz: próba zapisu kończy się błędem.

Najkrótszy możliwy przykład:

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 nigdy nie zostało wstawione, a mimo to pojawia się w każdym wierszu. SQLite wylicza je na nowo z price + tax przy każdym odczycie wiersza. Zmień którąkolwiek z tych kolumn, a total się dostosuje.

Fraza GENERATED ALWAYS AS jest wymagana. ALWAYS to formalność ze standardu SQL: w SQLite nie ma innej opcji.

VIRTUAL a STORED

Każda kolumna generowana występuje w jednym z dwóch wariantów. Domyślny to VIRTUAL:

CREATE TABLE orders (
    id        INTEGER PRIMARY KEY,
    qty       INTEGER NOT NULL,
    unit      REAL NOT NULL,
    -- wyliczana na nowo przy każdym SELECT
    subtotal  REAL GENERATED ALWAYS AS (qty * unit) VIRTUAL,
    -- wyliczana raz i zapisywana na dysku
    tax       REAL GENERATED ALWAYS AS (qty * unit * 0.08) STORED
);

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

Model myślowy:

• VIRTUAL: zero bajtów na dysku, koszt procesora przy każdym odczycie. Tania w dodaniu, tania w późniejszej zmianie.
• STORED: zajmuje miejsce na dysku, odczyt nic dodatkowo nie kosztuje. Opłaca się, gdy wyrażenie jest kosztowne albo kolumna jest znacznie częściej czytana niż zapisywana.

Jeśli nie podasz słowa kluczowego, dostaniesz VIRTUAL. To prawie zawsze właściwy wybór domyślny.

Po co to wszystko? Indeksowalne wartości pochodne

Najmocniejsza zaleta to możliwość założenia indeksu na kolumnie generowanej. Dzięki temu masz szybkie wyszukiwanie po wartościach pochodnych bez przepisywania każdego zapytania.

Załóżmy, że chcesz wyszukiwać e-maile bez rozróżniania wielkości liter:

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';

Indeks obejmuje wersję zapisaną małymi literami. Zapytanie filtrujące po email_lower korzysta z indeksu bezpośrednio. SQLite ma też indeksy na wyrażeniach (CREATE INDEX ... ON users(lower(email))), ale kolumna generowana sprawia, że wartość pochodna jest widoczna jako prawdziwa kolumna, którą możesz wybrać w SELECT, użyć w widokach i wykorzystać w kodzie aplikacji.

Wyciąganie wartości z JSON

Kolumny generowane świetnie sprawdzają się w połączeniu z JSON. Obsługa JSON w SQLite daje operator ->> do wyciągania wartości skalarnej. Opakuj go w kolumnę generowaną, a dostaniesz typowane, indeksowalne pole nad elastycznym obiektem.

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 i kind wyglądają dla zapytań jak zwykłe kolumny, ale dane są w payload. Zmień JSON, a kolumny się zaktualizują. Indeks na user_id sprawia, że wyszukiwanie jest szybkie.

Zasady i ograniczenia

Kilka rzeczy, które SQLite wymusza. Warto je znać, zanim na nie trafisz:

• Wyrażenie musi być deterministyczne. random(), datetime('now') i inne niedeterministyczne funkcje są niedozwolone. Wartość musi dać się odtworzyć z wiersza.
• Wyrażenie może odwoływać się tylko do kolumn tego samego wiersza. Bez podzapytań, agregatów i innych tabel.
• Nie możesz bezpośrednio wykonać INSERT ani UPDATE na kolumnie generowanej. INSERT INTO products (total) VALUES (5) to błąd.
• Kolumn STORED nie można dodać przez ALTER TABLE ... ADD COLUMN. Po fakcie można dodać tylko VIRTUAL.
• Kolumny generowane mogą mieć ograniczenia NOT NULL, CHECK, UNIQUE, a nawet FOREIGN KEY. Pod tym względem zachowują się jak każda inna kolumna.

Krótka demonstracja zasady zapisu:

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

Rozwiązanie: usuń kolumnę generowaną z listy w INSERT i pozwól SQLite ją wyliczyć.

Wybór między VIRTUAL a STORED

Decyzja zwykle sprowadza się do proporcji odczytów do zapisów i kosztu wyrażenia:

CREATE TABLE measurements (
    id          INTEGER PRIMARY KEY,
    celsius     REAL NOT NULL,
    -- Tania arytmetyka, można ją liczyć od nowa przy każdym odczycie.
    fahrenheit  REAL GENERATED ALWAYS AS (celsius * 9.0 / 5.0 + 32) VIRTUAL,
    -- Cięższe wyrażenie, często odczytywane, rzadko zapisywane → zapisz je.
    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;

Praktyczne zasady:

• Domyślnie wybieraj VIRTUAL. Nic nie kosztuje przy zapisie i nadaje się prawie do wszystkiego.
• Przejdź na STORED, gdy indeksujesz kolumnę w tabeli z dużą liczbą zapisów (indeks i tak potrzebuje zapisanej wartości) albo gdy wyrażenie jest naprawdę kosztowne.
• Nie zadręczaj się tym. Wariant jest częścią schematu, ale jeśli zmienisz zdanie, możesz usunąć kolumnę i utworzyć ją na nowo, przynajmniej w przypadku VIRTUAL.

Kolumny generowane a widoki

Kolumny generowane częściowo pokrywają się z widokami: jedne i drugie udostępniają wyliczone wartości bez ich przechowywania (no, czasami). Zwykle podział wygląda tak:

• Kolumna generowana należy do jednego wiersza i jednej tabeli. Używaj jej do wartości pochodnych w obrębie wiersza: formatowania e-maila, wyciągania pola JSON, liczenia sumy.
• Widok to zapisane zapytanie. Używaj go, gdy obliczenia wymagają złączeń, agregacji albo filtrowania wielu wierszy.

Możesz je łączyć. Widok może wybierać dane z tabeli z kolumnami generowanymi i dołączać dodatkowy kontekst. Kolumny generowane działają na warstwie przechowywania, a widoki na warstwie zapytań.

Dalej: ATTACH DATABASE

Kolumny generowane pozwalają jednej tabeli wyliczać własne wartości. Następna strona idzie w przeciwną stronę: łączy kilka baz SQLite naraz przez ATTACH DATABASE, żeby jedno zapytanie mogło obejmować wiele plików.