SQLite Crash Course

-- SQLite accepts this without error
CREATE TABLE demo (flag BOOLEAN, amount DECIMAL(10,2));
INSERT INTO demo VALUES ('yes', 'not a number'); -- no error!

-- Best practice: validate in application code
-- or use CHECK constraints (covered in Section 5)

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    active INTEGER NOT NULL DEFAULT 1 CHECK(active IN (0,1))
);

-- Store $19.99 as 1999 (cents) — safest for money
CREATE TABLE products (
    price_cents INTEGER NOT NULL  -- never REAL for money
);

CREATE TABLE IF NOT EXISTS posts (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    title       TEXT    NOT NULL,
    body        TEXT,
    author_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status      TEXT    NOT NULL DEFAULT 'draft' CHECK(status IN ('draft','published','archived')),
    view_count  INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at  TEXT
);

-- Rename the table
ALTER TABLE posts RENAME TO articles;

-- Rename a column (SQLite 3.25.0+)
ALTER TABLE articles RENAME COLUMN body TO content;

-- Add a new column (must allow NULL or have a DEFAULT)
ALTER TABLE articles ADD COLUMN excerpt TEXT;

-- Drop a column (SQLite 3.35.0+)
ALTER TABLE articles DROP COLUMN excerpt;

-- 1. Create new table with the desired schema
CREATE TABLE articles_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT
    -- new schema here
);

-- 2. Copy data
INSERT INTO articles_new SELECT id, title, content FROM articles;

-- 3. Drop old table and rename
DROP TABLE articles;
ALTER TABLE articles_new RENAME TO articles;

DROP TABLE IF EXISTS articles;

CREATE VIEW published_posts AS
SELECT id, title, created_at
FROM posts
WHERE status = 'published';

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

INSERT INTO users (email) VALUES ('alice@example.com');
-- id = 1

INSERT INTO users (email) VALUES ('bob@example.com');
-- id = 2

-- Get the last inserted id:
SELECT last_insert_rowid();

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

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id),
    tag_id  INTEGER NOT NULL REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL,     -- rejected if NULL
    name  TEXT               -- NULL allowed
);

CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    status     TEXT    NOT NULL DEFAULT 'pending',
    created_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    quantity   INTEGER NOT NULL DEFAULT 1
);

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

-- Table-level (composite unique)
CREATE TABLE memberships (
    user_id INTEGER NOT NULL,
    team_id INTEGER NOT NULL,
    UNIQUE(user_id, team_id)
);

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT    NOT NULL,
    price_cents INTEGER NOT NULL CHECK(price_cents >= 0),
    status      TEXT    NOT NULL CHECK(status IN ('active', 'inactive', 'discontinued')),
    stock       INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0),
    rating      REAL    CHECK(rating IS NULL OR (rating >= 0.0 AND rating <= 5.0))
);

PRAGMA foreign_keys = ON;  -- must be run after every connection open

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    title     TEXT NOT NULL
);

-- Equivalent explicit syntax:
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL,
    title     TEXT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Single-column index
CREATE INDEX idx_posts_author ON posts(author_id);

-- Composite index
CREATE INDEX idx_posts_status_created ON posts(status, created_at DESC);

-- Unique index (alternative to UNIQUE constraint)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index — only indexes rows matching the WHERE clause
CREATE INDEX idx_published_posts ON posts(created_at)
WHERE status = 'published';

-- Drop an index
DROP INDEX IF EXISTS idx_posts_author;

EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE author_id = 42 AND status = 'published';

-- Single row
INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice');

-- Multiple rows
INSERT INTO users (email, name) VALUES
    ('bob@example.com',   'Bob'),
    ('carol@example.com', 'Carol');

-- Insert or ignore on conflict
INSERT OR IGNORE INTO users (email, name) VALUES ('alice@example.com', 'Alice Again');

-- Insert or replace (deletes conflicting row, then inserts)
INSERT OR REPLACE INTO users (email, name) VALUES ('alice@example.com', 'Alice Updated');

-- Upsert (INSERT ... ON CONFLICT) — SQLite 3.24.0+
INSERT INTO users (email, name)
VALUES ('alice@example.com', 'Alice New Name')
ON CONFLICT(email) DO UPDATE SET
    name = excluded.name,
    updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now');

-- Insert from SELECT
INSERT INTO archive_posts SELECT * FROM posts WHERE status = 'archived';

-- Update specific rows
UPDATE posts SET status = 'published', updated_at = strftime('%Y-%m-%dT%H:%M:%fZ', 'now')
WHERE id = 42;

-- Update with subquery
UPDATE posts SET view_count = view_count + 1
WHERE id IN (SELECT id FROM posts WHERE author_id = 5);

-- UPDATE with RETURNING (SQLite 3.35.0+)
UPDATE posts SET view_count = view_count + 1
WHERE id = 42
RETURNING id, view_count;

-- Delete specific rows
DELETE FROM posts WHERE status = 'archived' AND created_at < '2023-01-01';

-- Delete all rows (faster than DELETE without WHERE for clearing a table)
DELETE FROM posts;  -- logs each row deletion

-- Even faster for clearing all rows:
-- DROP TABLE + CREATE TABLE, or use:
-- (no SQLite equivalent of TRUNCATE; DELETE is the standard way)

-- DELETE with RETURNING
DELETE FROM posts WHERE id = 42 RETURNING id, title;

SELECT id, title, status FROM posts WHERE author_id = 1 ORDER BY created_at DESC LIMIT 10;

-- Pagination
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;  -- page 3 of 20-per-page

-- Aliases
SELECT p.id, p.title, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.author_id;

SELECT
    author_id,
    COUNT(*)                     AS total_posts,
    COUNT(CASE WHEN status = 'published' THEN 1 END) AS published_count,
    AVG(view_count)              AS avg_views,
    MAX(created_at)              AS latest_post
FROM posts
GROUP BY author_id
HAVING COUNT(*) > 5
ORDER BY total_posts DESC;

WITH active_authors AS (
    SELECT DISTINCT author_id FROM posts WHERE status = 'published'
),
author_stats AS (
    SELECT author_id, COUNT(*) AS post_count
    FROM posts
    GROUP BY author_id
)
SELECT u.name, s.post_count
FROM users u
JOIN active_authors aa ON aa.author_id = u.id
JOIN author_stats s ON s.author_id = u.id
ORDER BY s.post_count DESC;

-- Generate a series of numbers 1–10
WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- Walk a self-referential hierarchy (categories with parent_id)
WITH RECURSIVE category_tree(id, name, depth) AS (
    SELECT id, name, 0 FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT depth, name FROM category_tree ORDER BY depth, name;

SELECT
    id,
    title,
    view_count,
    ROW_NUMBER()   OVER (PARTITION BY author_id ORDER BY view_count DESC) AS rank_by_author,
    SUM(view_count) OVER (PARTITION BY author_id) AS author_total_views,
    LAG(view_count) OVER (ORDER BY created_at)  AS prev_post_views
FROM posts;

SELECT p.title, u.name AS author
FROM posts p
INNER JOIN users u ON u.id = p.author_id;

-- All users, even those with no posts
SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.author_id = u.id
GROUP BY u.id;

SELECT a.name, b.name FROM users a CROSS JOIN users b WHERE a.id <> b.id;

-- Employees and their managers (same table)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

-- Posts with their tags (comma-aggregated)
SELECT p.title, GROUP_CONCAT(t.name, ', ') AS tags
FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id;

CREATE TABLE users         (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE);
CREATE TABLE user_profiles (
    user_id     INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  TEXT
);
-- user_id is both the PK and the FK, enforcing 1-to-1

-- Current date and time
SELECT date('now');                  -- '2025-06-11'
SELECT time('now');                  -- '14:32:00'
SELECT datetime('now');              -- '2025-06-11 14:32:00'
SELECT strftime('%Y-%m-%dT%H:%M:%fZ', 'now');  -- ISO 8601 with milliseconds

-- All 'now' functions return UTC. For local time:
SELECT datetime('now', 'localtime');

-- Modifiers
SELECT date('now', '+7 days');          -- one week from today
SELECT date('now', '-1 month');         -- one month ago
SELECT date('2025-01-15', 'start of month');   -- '2025-01-01'
SELECT date('2025-01-15', 'start of year');    -- '2025-01-01'

-- Unix timestamp (seconds since epoch)
SELECT strftime('%s', 'now');          -- '1718113920'
SELECT datetime(1718113920, 'unixepoch');  -- convert back to datetime

-- Formatting
SELECT strftime('%d/%m/%Y', '2025-06-11');   -- '11/06/2025'
SELECT strftime('%B %d, %Y', date('now'));   -- strftime has no %B — use substr instead

CREATE TABLE events (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT NOT NULL,
    -- Store as ISO 8601 UTC text: '2025-06-11T14:32:00.000Z'
    starts_at  TEXT NOT NULL,
    ends_at    TEXT,
    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

-- Querying date ranges works correctly with TEXT comparison (ISO 8601 sorts lexicographically)
SELECT * FROM events
WHERE starts_at >= '2025-06-01T00:00:00Z'
  AND starts_at <  '2025-07-01T00:00:00Z';

-- Date arithmetic
SELECT name, julianday(ends_at) - julianday(starts_at) AS duration_days FROM events;

-- Basic transaction
BEGIN;
    INSERT INTO orders (user_id, total_cents) VALUES (1, 4999);
    INSERT INTO order_items (order_id, product_id, qty) VALUES (last_insert_rowid(), 7, 2);
COMMIT;

-- Rollback on error (application-side, e.g. in a try/catch)
BEGIN;
    UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
    UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
    -- If anything goes wrong, call ROLLBACK instead of COMMIT
COMMIT;

-- Savepoints (nested transactions)
BEGIN;
    INSERT INTO users (email) VALUES ('a@example.com');
    SAVEPOINT sp1;
        INSERT INTO users (email) VALUES ('b@example.com');
    ROLLBACK TO sp1;  -- Undoes only the second insert
COMMIT;  -- Only the first insert is committed

BEGIN;             -- Deferred (default) — acquires lock on first read/write
BEGIN IMMEDIATE;   -- Acquires write-lock immediately; other writers blocked, readers allowed
BEGIN EXCLUSIVE;   -- Acquires exclusive lock; no other readers or writers

PRAGMA journal_mode = WAL;
-- Returns 'wal' on success. Run once; mode persists in the database file.

-- Recommended companion settings:
PRAGMA synchronous = NORMAL;    -- Safe with WAL; faster than FULL
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;     -- Wait up to 5s on SQLITE_BUSY instead of failing immediately

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;     -- 64MB page cache (negative = KB)
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;   -- 256MB memory-mapped I/O

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -32000;   -- 32MB
PRAGMA foreign_keys = ON;

-- Each connection on open:
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 10000;   -- Wait up to 10s for locks
PRAGMA foreign_keys = ON;

-- Use BEGIN IMMEDIATE for write transactions to avoid writer starvation:
BEGIN IMMEDIATE;
    -- write operations
COMMIT;

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -131072;      -- 128MB cache
PRAGMA mmap_size = 1073741824;    -- 1GB mmap
PRAGMA page_size = 4096;          -- set BEFORE creating tables; cannot change after
PRAGMA wal_autocheckpoint = 0;    -- Disable auto-checkpoint; checkpoint manually or via Litestream

-- Manual checkpoint modes:
PRAGMA wal_checkpoint;            -- PASSIVE: flush pages not blocking anyone
PRAGMA wal_checkpoint(FULL);      -- Wait for all readers to finish, then checkpoint
PRAGMA wal_checkpoint(RESTART);   -- FULL + resets WAL write position to start
PRAGMA wal_checkpoint(TRUNCATE);  -- RESTART + truncates WAL file to zero bytes

PRAGMA journal_mode = MEMORY;   -- Journal kept in RAM; fast but not crash-safe
PRAGMA journal_mode = OFF;      -- No journal; fastest, no rollback on crash (dangerous)
PRAGMA journal_mode = TRUNCATE; -- Journal file truncated (not deleted) on commit

-- Store JSON as TEXT
CREATE TABLE events (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL  -- JSON string
);

INSERT INTO events (payload) VALUES ('{"type":"click","x":100,"y":200,"tags":["ui","button"]}');

-- Extract fields
SELECT json_extract(payload, '$.type')       AS event_type,
       json_extract(payload, '$.x')          AS x,
       json_extract(payload, '$.tags[0]')    AS first_tag
FROM events;

-- Modify JSON
UPDATE events
SET payload = json_set(payload, '$.processed', true)
WHERE id = 1;

-- Query inside JSON array
SELECT * FROM events
WHERE json_extract(payload, '$.type') = 'click';

-- Expand a JSON array into rows
SELECT value FROM events, json_each(json_extract(payload, '$.tags'));

-- Build JSON from columns
SELECT json_object('id', id, 'type', json_extract(payload, '$.type')) FROM events;

-- Aggregate into JSON array
SELECT json_group_array(json_extract(payload, '$.type')) FROM events;

-- Create an FTS5 virtual table
CREATE VIRTUAL TABLE docs_fts USING fts5(
    title,
    body,
    content='docs',      -- content table (optional, for external content FTS)
    content_rowid='id'
);

-- Without content table (self-contained):
CREATE VIRTUAL TABLE notes_fts USING fts5(title, body);

INSERT INTO notes_fts VALUES ('SQLite Guide', 'SQLite is a serverless embedded database');
INSERT INTO notes_fts VALUES ('WAL Mode', 'Write-Ahead Logging improves concurrency');

-- Full-text query
SELECT * FROM notes_fts WHERE notes_fts MATCH 'serverless';

-- Phrase search
SELECT * FROM notes_fts WHERE notes_fts MATCH '"embedded database"';

-- Prefix search
SELECT * FROM notes_fts WHERE notes_fts MATCH 'serv*';

-- Boolean operators
SELECT * FROM notes_fts WHERE notes_fts MATCH 'SQLite AND WAL';
SELECT * FROM notes_fts WHERE notes_fts MATCH 'SQLite OR PostgreSQL';
SELECT * FROM notes_fts WHERE notes_fts MATCH 'database NOT NoSQL';

-- Column-scoped search
SELECT * FROM notes_fts WHERE notes_fts MATCH 'title:SQLite';

-- Ranked results (BM25 scoring)
SELECT title, rank FROM notes_fts
WHERE notes_fts MATCH 'database'
ORDER BY rank;  -- rank is negative; ORDER BY rank = best first

-- Highlighted snippets
SELECT snippet(notes_fts, 1, '<b>', '</b>', '...', 10) AS excerpt
FROM notes_fts
WHERE notes_fts MATCH 'database';

-- Enable WAL + safety settings (run on every new connection)
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

-- Canonical table template
CREATE TABLE IF NOT EXISTS things (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL,
    status     TEXT    NOT NULL DEFAULT 'active' CHECK(status IN ('active','inactive')),
    created_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now')),
    updated_at TEXT
);

-- Upsert
INSERT INTO things (id, name) VALUES (1, 'foo')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- Current UTC timestamp
SELECT strftime('%Y-%m-%dT%H:%M:%fZ', 'now');

-- Last inserted rowid
SELECT last_insert_rowid();

-- Table schema inspection
SELECT sql FROM sqlite_master WHERE type='table' AND name='things';
PRAGMA table_info(things);
PRAGMA foreign_key_list(things);
PRAGMA index_list(things);

-- Database info
SELECT * FROM sqlite_master;
PRAGMA database_list;
PRAGMA integrity_check;
PRAGMA quick_check;