#!/bin/bash
# Reproduces every factual claim in
# https://www.heatware.net/postgresql/show-all-tables-psql/
#
# Creates a throwaway PostgreSQL cluster in a temp directory on port 55611, builds two
# databases with tables in several schemas, asserts each claim, then stops the cluster and
# deletes it. It does not touch any existing PostgreSQL installation, database, data
# directory, or role.
#
# Requires: initdb, pg_ctl, psql on PATH (PostgreSQL 15+; \drg and some output formats differ
# on older majors).
# Runtime: about 20 seconds.
# Usage: bash verify-show-all-tables-psql.sh

set -u

PORT=55611
PGROOT="$(mktemp -d -t hwtbl)"
# The socket directory must be short: PostgreSQL caps the Unix socket path at 103 bytes.
SOCK="$(mktemp -d /tmp/hwtb.XXXXXX)"

cleanup() {
  pg_ctl -D "$PGROOT/data" stop -m immediate >/dev/null 2>&1
  rm -rf "$PGROOT" "$SOCK"
}
trap cleanup EXIT

PASSES=0; FAILS=0; SKIPS=0
pass() { PASSES=$((PASSES+1)); printf 'PASS %s\n' "$1"; }
fail() { FAILS=$((FAILS+1));   printf 'FAIL %s\n     expected: %s\n     actual:   %s\n' "$1" "$2" "$3"; }
skip() { SKIPS=$((SKIPS+1));   printf 'SKIP %s (needs %s)\n' "$1" "$2"; }
check(){ [ "$2" = "$3" ] && pass "$1" || fail "$1" "$2" "$3"; }
contains(){ case "$3" in *"$2"*) pass "$1";; *) fail "$1" "text containing: $2" "$3";; esac; }

for bin in initdb pg_ctl psql; do
  command -v "$bin" >/dev/null 2>&1 || { echo "SKIP all checks (needs $bin on PATH)"; echo "PASS=0 FAIL=0 SKIP=1"; exit 0; }
done

initdb -D "$PGROOT/data" -U postgres --no-locale -E UTF8 >"$PGROOT/initdb.log" 2>&1 \
  || { echo "SKIP all checks (initdb failed; see $PGROOT/initdb.log)"; echo "PASS=0 FAIL=0 SKIP=1"; exit 0; }
pg_ctl -D "$PGROOT/data" -o "-p $PORT -k $SOCK -c listen_addresses=127.0.0.1" \
       -l "$PGROOT/pg.log" start >/dev/null 2>&1
for _ in 1 2 3 4 5 6 7 8 9 10; do
  psql -h 127.0.0.1 -p $PORT -U postgres -X -At -c 'SELECT 1' >/dev/null 2>&1 && break; sleep 1
done

adm(){ psql -h 127.0.0.1 -p $PORT -U postgres -d postgres  -X -At -c "$1" 2>&1; }
q()  { psql -h 127.0.0.1 -p $PORT -U postgres -d hwtables  -X -At -c "$1" 2>&1; }
qv() { psql -h 127.0.0.1 -p $PORT -U postgres -d hwtables  -X    -c "$1" 2>&1; }
e()  { psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty   -X -At -c "$1" 2>&1; }
lim(){ psql -h 127.0.0.1 -p $PORT -U hw_limited -d hwtables -X -At -c "$1" 2>&1; }

adm "SELECT 1" >/dev/null 2>&1 || { echo "SKIP all checks (server did not start)"; echo "PASS=0 FAIL=0 SKIP=1"; exit 0; }

echo "server: $(adm 'SELECT version()')"
echo

adm "CREATE DATABASE hwtables" >/dev/null
adm "CREATE DATABASE hwempty"  >/dev/null

q "CREATE TABLE public.orders (id serial primary key, total numeric);
   CREATE TABLE public.customers (id serial primary key, name text);
   CREATE UNLOGGED TABLE public.scratch (id int);
   CREATE VIEW public.recent_orders AS SELECT * FROM public.orders;
   CREATE SCHEMA app;
   CREATE TABLE app.sessions (id serial primary key, token text);
   INSERT INTO public.orders SELECT g, g*1.5 FROM generate_series(1,5000) g;" >/dev/null

e "CREATE SCHEMA reporting;
   CREATE TABLE reporting.daily_totals (d date, total numeric);
   CREATE TABLE reporting.errors (id int);" >/dev/null

# ------------------------------------------------------------------ \dt basics
out=$(qv '\dt')
contains "\\dt lists public.orders"                  "orders"    "$out"
contains "\\dt lists public.customers"               "customers" "$out"
case "$out" in *recent_orders*) fail "\\dt excludes views" "no 'recent_orders'" "$out";;
              *) pass "\\dt excludes views";; esac
case "$out" in *sessions*) fail "\\dt excludes other schemas not on search_path" "no 'sessions'" "$out";;
              *) pass "\\dt excludes other schemas not on search_path";; esac

out=$(qv '\dt+')
contains "\\dt+ adds a Persistence column"     "Persistence"   "$out"
contains "\\dt+ reports scratch as unlogged"   "unlogged"      "$out"
contains "\\dt+ adds an Access method column"  "Access method" "$out"

# --------------------------------------------------- the empty-result case
out=$(psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty -X -c '\dt' 2>&1)
contains "\\dt prints 'Did not find any relations.' when tables are off search_path" \
         "Did not find any relations." "$out"
check "...but pg_tables finds them anyway" "2" \
      "$(e "SELECT count(*) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema');")"
check "the default search_path does not mention the schema" '"$user", public' "$(e 'SHOW search_path;')"

out=$(psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty -X -c '\dt reporting.*' 2>&1)
contains "\\dt reporting.* finds them" "daily_totals" "$out"

out=$(psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty -X -c 'SET search_path TO reporting' -c '\dt' 2>&1)
contains "SET search_path in a separate -c makes \\dt find them" "daily_totals" "$out"

out=$(psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty -X -c 'SET search_path TO reporting; \dt' 2>&1)
contains "a backslash command in the SAME -c as SQL is a syntax error" "syntax error" "$out"

# \dt uses pg_table_is_visible, which is what search_path controls
out=$(psql -h 127.0.0.1 -p $PORT -U postgres -d hwempty -X -E -c '\dt' 2>&1)
contains "\\dt filters on pg_table_is_visible()" "pg_table_is_visible" "$out"

# \dt *.* sweeps in the system catalogs
n=$(e "SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
       WHERE c.relkind IN ('r','p') AND n.nspname NOT LIKE 'pg_toast%';")
[ "${n:-0}" -gt 50 ] && pass "\\dt *.* would list 50+ relations on an empty DB (system catalogs)" \
                     || fail "\\dt *.* sweeps in system catalogs" ">50 relations" "$n"

# ------------------------------------------- information_schema vs pg_tables
q "CREATE ROLE hw_limited LOGIN;
   GRANT CONNECT ON DATABASE hwtables TO hw_limited;
   GRANT USAGE ON SCHEMA public TO hw_limited;
   GRANT SELECT ON public.orders TO hw_limited;" >/dev/null

check "information_schema.tables hides tables the role has no privilege on" "1" \
      "$(lim "SELECT count(*) FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE';")"
check "pg_tables shows all three to the same role" "3" \
      "$(lim "SELECT count(*) FROM pg_tables WHERE schemaname='public';")"
check "information_schema.tables includes views for a privileged role" "1" \
      "$(q "SELECT count(*) FROM information_schema.tables WHERE table_schema='public' AND table_type='VIEW';")"
check "pg_tables excludes views entirely" "0" \
      "$(q "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename='recent_orders';")"

# ------------------------------------------------------------------ pg_class
check "pg_class.relkind marks the view as 'v'" "v" "$(q "SELECT relkind FROM pg_class WHERE relname='recent_orders';")"
check "pg_class.relkind marks the table as 'r'" "r" "$(q "SELECT relkind FROM pg_class WHERE relname='orders';")"
check "pg_class.relpersistence marks scratch as 'u' (unlogged)" "u" "$(q "SELECT relpersistence FROM pg_class WHERE relname='scratch';")"

# ---------------------------------------------------------------- partitions
q "CREATE TABLE public.events (id bigint, created date) PARTITION BY RANGE (created);
   CREATE TABLE public.events_2026 PARTITION OF public.events
     FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');" >/dev/null

out=$(qv '\dt')
contains "\\dt shows the partitioned parent"    "partitioned table" "$out"
contains "\\dt also shows the child partition"  "events_2026"       "$out"
check "pg_tables also lists both parent and partition" "2" \
      "$(q "SELECT count(*) FROM pg_tables WHERE schemaname='public' AND tablename LIKE 'events%';")"
check "relispartition filters the child out" "1" \
      "$(q "SELECT count(*) FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
            WHERE n.nspname='public' AND c.relkind IN ('r','p')
              AND NOT c.relispartition AND c.relname LIKE 'events%';")"
check "pg_tables has no relispartition column to filter on" "0" \
      "$(q "SELECT count(*) FROM information_schema.columns
            WHERE table_schema='pg_catalog' AND table_name='pg_tables' AND column_name='relispartition';")"

# ------------------------------------------------------- cross-database claim
out=$(q "SELECT count(*) FROM hwempty.reporting.errors;")
contains "PostgreSQL has no cross-database query" "cross-database references are not implemented" "$out"

echo
echo "PASS=$PASSES FAIL=$FAILS SKIP=$SKIPS"
[ "$FAILS" -eq 0 ] || exit 1