#!/bin/bash
# Reproduces every factual claim in
# https://www.heatware.net/postgresql/how-to-drop-a-constraint/
#
# Creates a throwaway PostgreSQL cluster in a temp directory on port 55494, builds two
# small tables, asserts each documented error message and result, 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.
# Runtime: about 10 seconds.
# Usage: bash verify-how-to-drop-a-constraint.sh

set -u

PORT=55494
PGDATA_TMP="$(mktemp -d -t hwdrc)"
# The socket directory must be short: PostgreSQL caps the Unix socket path at 103 bytes.
SOCK="$(mktemp -d /tmp/hwdc.XXXXXX)"

cleanup() {
  pg_ctl -D "$PGDATA_TMP/data" stop -m immediate >/dev/null 2>&1
  rm -rf "$PGDATA_TMP" "$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 <label> <needle> <haystack>
contains(){ case "$3" in *"$2"*) pass "$1";; *) fail "$1" "text containing: $2" "$3";; esac; }

q()  { psql -h 127.0.0.1 -p $PORT -U postgres -d hwcons -X -At -c "$1" 2>&1; }
qv() { psql -h 127.0.0.1 -p $PORT -U postgres -d hwcons -X    -c "$1" 2>&1; }

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

initdb -D "$PGDATA_TMP/data" -U postgres --no-locale -E UTF8 >"$PGDATA_TMP/initdb.log" 2>&1 \
  || { echo "SKIP (initdb failed; see $PGDATA_TMP/initdb.log)"; echo "PASS=0 FAIL=0 SKIP=1"; exit 0; }
pg_ctl -D "$PGDATA_TMP/data" -o "-p $PORT -k $SOCK -c listen_addresses=127.0.0.1" \
       -l "$PGDATA_TMP/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
psql -h 127.0.0.1 -p $PORT -U postgres -X -At -c 'SELECT 1' >/dev/null 2>&1 \
  || { echo "SKIP (cluster did not start; see $PGDATA_TMP/pg.log)"; echo "PASS=0 FAIL=0 SKIP=1"; exit 0; }

echo "server: $(psql -h 127.0.0.1 -p $PORT -U postgres -X -At -c 'SELECT version();')"
psql -h 127.0.0.1 -p $PORT -U postgres -X -At -c "CREATE DATABASE hwcons;" >/dev/null

# Each build() makes a fresh schema and puts it first on search_path, so no test ever has to
# destroy the previous one. Nothing in this script drops a table.
GEN=0
build() {
  GEN=$((GEN+1))
  q "CREATE SCHEMA gen$GEN;
     SET search_path = gen$GEN;
     ALTER DATABASE hwcons SET search_path = gen$GEN;
     CREATE TABLE authors (
       id        bigint PRIMARY KEY,
       email     text UNIQUE,
       full_name text NOT NULL,
       rating    int CHECK (rating BETWEEN 1 AND 5)
     );
     CREATE TABLE books (
       id        bigint PRIMARY KEY,
       author_id bigint REFERENCES authors(id),
       isbn      text
     );" >/dev/null
}
build

# ---------------------------------------------------------------- default names & pg_constraint
names=$(q "SELECT string_agg(conname, ',' ORDER BY conname) FROM pg_constraint WHERE conrelid='authors'::regclass;")
check "default constraint names on authors" "authors_email_key,authors_pkey,authors_rating_check" "$names"
types=$(q "SELECT string_agg(contype::text, ',' ORDER BY contype::text) FROM pg_constraint WHERE conrelid='authors'::regclass;")
check "contype codes are c (check), p (primary key), u (unique)" "c,p,u" "$types"
fk=$(q "SELECT conname FROM pg_constraint WHERE conrelid='books'::regclass AND contype='f';")
check "the foreign key lives on books, not on authors" "books_author_id_fkey" "$fk"
def=$(q "SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname='authors_rating_check';")
check "pg_get_constraintdef renders the CHECK expression" "CHECK (((rating >= 1) AND (rating <= 5)))" "$def"

# ---------------------------------------------------------------- an explicit name is not the default
q "ALTER TABLE books ADD CONSTRAINT my_own_name UNIQUE (isbn);" >/dev/null
custom=$(q "SELECT count(*) FROM pg_constraint WHERE conname='my_own_name' AND conrelid='books'::regclass;")
check "a constraint created with an explicit name does not get the default name" "1" "$custom"
guess=$(qv "ALTER TABLE books DROP CONSTRAINT books_isbn_key;")
contains "guessing the default name for an explicitly named constraint errors" \
         'constraint "books_isbn_key" of relation "books" does not exist' "$guess"
q "ALTER TABLE books DROP CONSTRAINT my_own_name;" >/dev/null

# ---------------------------------------------------------------- IF EXISTS
e1=$(qv "ALTER TABLE authors DROP CONSTRAINT no_such_constraint;")
contains "DROP CONSTRAINT on a missing name is an ERROR" \
         'ERROR:  constraint "no_such_constraint" of relation "authors" does not exist' "$e1"
e2=$(qv "ALTER TABLE authors DROP CONSTRAINT IF EXISTS no_such_constraint;")
contains "IF EXISTS downgrades it to a NOTICE" \
         'NOTICE:  constraint "no_such_constraint" of relation "authors" does not exist, skipping' "$e2"
contains "IF EXISTS still reports ALTER TABLE" "ALTER TABLE" "$e2"

# ---------------------------------------------------------------- dependent FK blocks the PK drop
e3=$(qv "ALTER TABLE authors DROP CONSTRAINT authors_pkey;")
contains "dropping a referenced PK errors" \
         'ERROR:  cannot drop constraint authors_pkey on table authors because other objects depend on it' "$e3"
contains "the DETAIL names the dependent foreign key" \
         'DETAIL:  constraint books_author_id_fkey on table books depends on index authors_pkey' "$e3"
contains "the HINT suggests CASCADE" 'HINT:  Use DROP ... CASCADE to drop the dependent objects too.' "$e3"

# ---------------------------------------------------------------- CASCADE silently removes the FK
e4=$(qv "ALTER TABLE authors DROP CONSTRAINT authors_pkey CASCADE;")
contains "CASCADE reports the collateral drop as a NOTICE only" \
         'NOTICE:  drop cascades to constraint books_author_id_fkey on table books' "$e4"
left=$(q "SELECT count(*) FROM pg_constraint WHERE conname='books_author_id_fkey' AND conrelid='books'::regclass;")
check "after CASCADE the foreign key on books is gone" "0" "$left"
build

# ---------------------------------------------------------------- transactional rollback
q "BEGIN; ALTER TABLE authors DROP CONSTRAINT authors_rating_check; ROLLBACK;" >/dev/null
back=$(q "SELECT count(*) FROM pg_constraint WHERE conname='authors_rating_check' AND conrelid='authors'::regclass;")
check "ROLLBACK restores a dropped constraint" "1" "$back"

# ---------------------------------------------------------------- constraint vs index
e5=$(qv "DROP INDEX authors_email_key;")
contains "you cannot DROP INDEX an index owned by a constraint" \
         'ERROR:  cannot drop index authors_email_key because constraint authors_email_key on table authors requires it' "$e5"
contains "the HINT points at DROP CONSTRAINT" \
         'HINT:  You can drop constraint authors_email_key on table authors instead.' "$e5"
q "ALTER TABLE authors DROP CONSTRAINT authors_email_key;" >/dev/null
idx=$(q "SELECT string_agg(indexname, ',' ORDER BY indexname) FROM pg_indexes WHERE tablename='authors' AND schemaname=current_schema();")
check "dropping the UNIQUE constraint also removes its index" "authors_pkey" "$idx"

q "CREATE UNIQUE INDEX books_isbn_uniq ON books(isbn);" >/dev/null
nc=$(q "SELECT count(*) FROM pg_constraint WHERE conname='books_isbn_uniq' AND conrelid='books'::regclass;")
check "a plain CREATE UNIQUE INDEX has no pg_constraint row" "0" "$nc"
e6=$(qv "ALTER TABLE books DROP CONSTRAINT books_isbn_uniq;")
contains "DROP CONSTRAINT cannot remove a plain unique index" \
         'ERROR:  constraint "books_isbn_uniq" of relation "books" does not exist' "$e6"
e7=$(qv "DROP INDEX books_isbn_uniq;")
contains "DROP INDEX removes it" "DROP INDEX" "$e7"

# ---------------------------------------------------------------- NOT NULL
nn=$(q "SELECT count(*) FROM pg_constraint WHERE conrelid='authors'::regclass AND conname LIKE '%full_name%';")
check "NOT NULL has no pg_constraint row on this server" "0" "$nn"
e8=$(qv "ALTER TABLE authors DROP CONSTRAINT authors_full_name_not_null;")
contains "DROP CONSTRAINT cannot remove a NOT NULL" \
         'ERROR:  constraint "authors_full_name_not_null" of relation "authors" does not exist' "$e8"
q "ALTER TABLE authors ALTER COLUMN full_name DROP NOT NULL;" >/dev/null
an=$(q "SELECT attnotnull FROM pg_attribute WHERE attrelid='authors'::regclass AND attname='full_name';")
check "ALTER COLUMN ... DROP NOT NULL clears pg_attribute.attnotnull" "f" "$an"
q "ALTER TABLE authors ADD CONSTRAINT authors_full_name_present CHECK (full_name IS NOT NULL);" >/dev/null
e9=$(qv "ALTER TABLE authors DROP CONSTRAINT authors_full_name_present;")
contains "a hand-written CHECK (col IS NOT NULL) IS droppable by name" "ALTER TABLE" "$e9"

# ---------------------------------------------------------------- lock level
lock=$(q "BEGIN; ALTER TABLE authors DROP CONSTRAINT authors_rating_check;
          SELECT mode FROM pg_locks WHERE relation='authors'::regclass AND pid=pg_backend_pid() AND mode='AccessExclusiveLock';
          ROLLBACK;" | grep -c '^AccessExclusiveLock$')
check "DROP CONSTRAINT holds an ACCESS EXCLUSIVE lock on the table" "1" "$lock"

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