#!/usr/bin/env bash
# What one scan costs, on a catalog of a known size: TABLES tables in schema app, each with a
# primary key, a UNIQUE index, a CHECK constraint, a foreign key to the first table and a
# trigger, plus one breach (a disabled trigger) and one gap (a NOT VALID CHECK). Times REPS calls
# of promises_kept() (whole database) and of check_promises('app') inside one DO block, three
# times, and prints milliseconds per call. Results are recorded in bench/results.md.
#
# Runs against the server PGHOST/PGPORT point at, with pg_promise_guard installed; creates the
# database promise_guard_bench (refuses if it exists) and drops it at the end.
#
#   PGHOST=... PGPORT=... bash bench/scan_cost.sh
set -euo pipefail

TABLES=${TABLES:-1000}
REPS=${REPS:-20}
PSQL=${PSQL:-psql}
DB=promise_guard_bench
q() { $PSQL -X -v ON_ERROR_STOP=1 -tAq -d "$DB" "$@"; }

[ -z "$($PSQL -X -tA -d postgres -c "select 1 from pg_database where datname = '$DB'")" ] ||
    { echo "database $DB exists: not touching it" >&2; exit 2; }
trap '$PSQL -X -q -d postgres -c "drop database if exists $DB" >/dev/null 2>&1 || true' EXIT
$PSQL -X -q -d postgres -c "create database $DB"
q <<SQL
CREATE EXTENSION pg_promise_guard;
CREATE SCHEMA app;
CREATE FUNCTION app.touch() RETURNS trigger LANGUAGE plpgsql AS \$\$ BEGIN RETURN NEW; END \$\$;
CREATE TABLE app.t0 (id int PRIMARY KEY);
DO \$\$
BEGIN
    FOR i IN 1..$TABLES LOOP
        EXECUTE format('CREATE TABLE app.t%s (id int PRIMARY KEY, code text, amount int,
                            parent int REFERENCES app.t0, CONSTRAINT t%s_amount CHECK (amount >= 0))', i, i);
        EXECUTE format('CREATE UNIQUE INDEX t%s_code ON app.t%s (code)', i, i);
        EXECUTE format('CREATE TRIGGER t%s_touch BEFORE INSERT ON app.t%s FOR EACH ROW EXECUTE PROCEDURE app.touch()', i, i);
    END LOOP;
END
\$\$;
-- One of each: a breach and a gap, so the scan has something to report.
ALTER TABLE app.t1 DISABLE TRIGGER t1_touch;
ALTER TABLE app.t2 ADD CONSTRAINT t2_nv CHECK (amount < 1000000) NOT VALID;
ANALYZE;
SQL

echo "server: $(q -c 'select version()')"
echo "pg_promise_guard $(q -c "select extversion from pg_extension where extname = 'pg_promise_guard'")"
echo "catalog: $(q -c "select count(*) from pg_class") pg_class rows, $(q -c "select count(*) from pg_constraint") constraints, $(q -c "select count(*) from pg_trigger") triggers, $(q -c "select count(*) from pg_index") indexes"
echo "findings in app: $(q -c "select string_agg(kind || '/' || severity, ', ' order by kind) from promise_guard.check_promises('app')")"
# time WHAT: three timings of REPS calls of WHAT
time_it() {
    local what=$1 label=$2 i ms
    for i in 1 2 3; do
        ms=$(q 2>&1 <<SQL | sed -n 's/.*NOTICE: *//p'
DO \$\$
DECLARE t0 timestamptz := clock_timestamp();
BEGIN
    FOR i IN 1..$REPS LOOP
        -- The value is used: an unused column of a non-volatile call can be planned away.
        PERFORM s.x FROM (SELECT $what AS x) s;
    END LOOP;
    RAISE NOTICE '%', round((extract(epoch FROM clock_timestamp() - t0) * 1000 / $REPS)::numeric, 2);
END
\$\$;
SQL
)
        printf '%-50s rep %d: %8s ms per call\n' "$what $label" "$i" "$ms"
    done
}
time_it "promise_guard.check_promises('app')" "(lists every finding)"
time_it "promise_guard.promises_kept()" "(a breach present)"
# And once more with no breach left, in case the answer changes the cost.
q -c "ALTER TABLE app.t1 ENABLE TRIGGER t1_touch"
echo "findings in app after re-enabling the trigger: $(q -c "select coalesce(string_agg(kind || '/' || severity, ', '), 'none') from promise_guard.check_promises('app')")"
time_it "promise_guard.promises_kept()" "(no breach)"
