SQL Transactions

# SQL Transactions

Zerethon Official Sample `sql-transactions`, version 1.0.0, SQL Playground (SQLite 3).

- `setup/schema.sql` — accounts and transfers (made-up data), with constraints; runs first on every run
- `main.sql` — entry file: BEGIN/COMMIT, ROLLBACK, a SAVEPOINT, a constraint, an upsert
- `test_transactions.sql` — checks; set it as the entry file to run them (or Run ▾ → Run tests)

Every run starts from a new, empty in-memory database (Run is also Reset). An error stops the run
at that statement and names its line. There is no server and no real data: nothing you run here
reaches any other database.

This project is your own copy. Changing it never changes the official sample.

-- SQL Transactions 1.0.0 (Zerethon Official Sample). Each statement's result appears as its
-- own table. Every run starts again from setup/schema.sql.

-- 1. A transfer is two updates and a log row: all of them happen, or none.
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE owner = 'Lan';
UPDATE accounts SET balance = balance + 3000 WHERE owner = 'Minh';
INSERT INTO transfers (from_id, to_id, amount) VALUES (1, 2, 3000);
COMMIT;
SELECT owner, balance FROM accounts ORDER BY id;

-- 2. ROLLBACK undoes everything since BEGIN: the balances are as before.
BEGIN;
UPDATE accounts SET balance = 0 WHERE owner = 'Lan';
SELECT owner, balance FROM accounts WHERE owner = 'Lan'; -- 0 inside the transaction
ROLLBACK;
SELECT owner, balance FROM accounts WHERE owner = 'Lan'; -- back to 7000

-- 3. A SAVEPOINT undoes only part of a transaction.
BEGIN;
UPDATE accounts SET balance = balance + 500 WHERE owner = 'Nga';
SAVEPOINT bonus;
UPDATE accounts SET balance = balance + 99999 WHERE owner = 'Nga';
ROLLBACK TO bonus; -- keeps the +500, drops the +99999
COMMIT;
SELECT owner, balance FROM accounts WHERE owner = 'Nga';

-- 4. Constraints protect the data even without a transaction: this transfer would make
-- Minh's balance negative, so SQLite refuses it (and the run would stop with that error).
-- Try it: remove the "--" in front of the next line and press Run.
-- UPDATE accounts SET balance = balance - 999999 WHERE owner = 'Minh';

-- 5. Upsert: insert a row, or update it when it already exists.
INSERT INTO accounts (owner, balance) VALUES ('Nga', 100), ('Oanh', 100)
ON CONFLICT (owner) DO UPDATE SET balance = balance + excluded.balance;
SELECT owner, balance FROM accounts ORDER BY id;

-- SQL Transactions 1.0.0 (Zerethon Official Sample): schema and made-up data.
-- Runs before the entry file on every run, in a new in-memory SQLite database.
CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    owner TEXT NOT NULL UNIQUE,
    balance INTEGER NOT NULL CHECK (balance >= 0) -- in cents; never below zero
);

CREATE TABLE transfers (
    id INTEGER PRIMARY KEY,
    from_id INTEGER NOT NULL REFERENCES accounts (id),
    to_id INTEGER NOT NULL REFERENCES accounts (id),
    amount INTEGER NOT NULL CHECK (amount > 0)
);

INSERT INTO accounts (owner, balance) VALUES ('Lan', 10000), ('Minh', 2500), ('Nga', 0);

-- Checks for transactions and constraints. To run them, set this file as the entry file
-- (the ▶ button next to it in Files) and press Run, or Run ▾ → Run tests. A failed check
-- stops the run: the CHECK constraint on `ok` rejects a 0.
CREATE TEMP TABLE checks (name TEXT NOT NULL, ok INTEGER NOT NULL CHECK (ok = 1));

-- A committed transfer keeps the total amount of money.
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE owner = 'Lan';
UPDATE accounts SET balance = balance + 1000 WHERE owner = 'Nga';
COMMIT;
INSERT INTO checks VALUES ('a transfer keeps the total', (SELECT sum(balance) = 12500 FROM accounts));
INSERT INTO checks VALUES ('Nga received 1000', (SELECT balance = 1000 FROM accounts WHERE owner = 'Nga'));

-- A rolled-back change is gone.
BEGIN;
DELETE FROM accounts;
ROLLBACK;
INSERT INTO checks VALUES ('ROLLBACK restores deleted rows', (SELECT count(*) = 3 FROM accounts));

-- ROLLBACK TO a savepoint keeps what came before it.
BEGIN;
UPDATE accounts SET balance = balance + 1 WHERE owner = 'Minh';
SAVEPOINT s;
UPDATE accounts SET balance = balance + 1000 WHERE owner = 'Minh';
ROLLBACK TO s;
COMMIT;
INSERT INTO checks VALUES ('savepoint keeps the first change only', (SELECT balance = 2501 FROM accounts WHERE owner = 'Minh'));

-- The upsert updates an existing owner instead of failing on UNIQUE.
INSERT INTO accounts (owner, balance) VALUES ('Lan', 5) ON CONFLICT (owner) DO UPDATE SET balance = balance + excluded.balance;
INSERT INTO checks VALUES ('upsert adds to the existing row', (SELECT balance = 9005 FROM accounts WHERE owner = 'Lan'));
INSERT INTO checks VALUES ('upsert creates no duplicate', (SELECT count(*) = 3 FROM accounts));

SELECT 'All ' || count(*) || ' checks passed.' AS result FROM checks;