#!/usr/bin/env bash
# Reproduce covering index / automatic index / WITHOUT ROWID EQP for SQLite kernel article 12.
# Environment: Linux/WSL2, sqlite3 3.45+ (recorded with 3.53.2).
set -euo pipefail
DIR="$(cd "$(dirname "$0")" && pwd)"
DB="$DIR/sk-cover.db"
rm -f "$DB" "$DB"-*

sqlite3 "$DB" <<'SQL'
CREATE TABLE orders(id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL, note TEXT);
CREATE INDEX idx_orders_user ON orders(user_id);
INSERT INTO orders(user_id, amount, note)
SELECT (abs(random()) % 500), (abs(random()) % 10000) / 100.0, 'n' || value
FROM generate_series(1, 20000);
SQL

echo "=== version ==="
sqlite3 --version

echo "=== covering: SELECT user_id (the indexed column itself) ==="
sqlite3 "$DB" "EXPLAIN QUERY PLAN SELECT user_id FROM orders WHERE user_id=10;"

echo "=== non-covering: SELECT amount (needs rowid lookup back into the table) ==="
sqlite3 "$DB" "EXPLAIN QUERY PLAN SELECT amount FROM orders WHERE user_id=10;"

echo "=== covering: SELECT id, user_id (id is the rowid alias, already inside the index leaf) ==="
sqlite3 "$DB" "EXPLAIN QUERY PLAN SELECT id, user_id FROM orders WHERE user_id=10;"

echo "=== automatic index: JOIN with no index on either side's join column ==="
sqlite3 "$DB" <<'SQL'
CREATE TABLE t1(x INTEGER, y INTEGER);
CREATE TABLE t2(x INTEGER, z INTEGER);
INSERT INTO t1 SELECT value, value FROM generate_series(1,500);
INSERT INTO t2 SELECT value, value FROM generate_series(1,500);
EXPLAIN QUERY PLAN SELECT t1.y, t2.z FROM t1 JOIN t2 ON t1.x = t2.x;
SQL

echo "=== automatic_index default value ==="
sqlite3 "$DB" "PRAGMA automatic_index;"

echo "=== same JOIN with automatic_index disabled: degrades to double SCAN ==="
sqlite3 "$DB" "PRAGMA automatic_index=OFF; EXPLAIN QUERY PLAN SELECT t1.y, t2.z FROM t1 JOIN t2 ON t1.x = t2.x;"

echo "=== WITHOUT ROWID table: primary key lookup needs no separate covering label ==="
sqlite3 "$DB" <<'SQL'
CREATE TABLE wr(k INTEGER PRIMARY KEY, v TEXT) WITHOUT ROWID;
INSERT INTO wr SELECT value, 'v'||value FROM generate_series(1,100);
EXPLAIN QUERY PLAN SELECT v FROM wr WHERE k=5;
SQL