#!/bin/bash

psql_args=( "$@" )

run_sql()
{
    psql "${psql_args[@]}" "$@"
}

run_sql <<EOF
DROP TABLE IF EXISTS refs;
DROP TABLE IF EXISTS targets;

CREATE TABLE targets (
  id serial PRIMARY KEY,
  name text UNIQUE NOT NULL
);

INSERT INTO targets (name) SELECT 'foo' || x::text FROM generate_series(0, 2) s (x);

CREATE TABLE refs (
  id serial PRIMARY KEY,
  t_id int NOT NULL
);

INSERT INTO refs (t_id) SELECT id FROM targets;
EOF

make_test_sql()
{
    fkcols=$1
    kcols=$2
    shift 2
    rules=( "$@" )
    cnames=( {z..a} )

    cat <<EOF
\\echo
\\echo ======================================
EOF

    for i in ${!rules[*]}
    do
	cname=${cnames[$i]}
	cat <<EOF
ALTER TABLE refs ADD CONSTRAINT ${cname} FOREIGN KEY ($fkcols) REFERENCES targets ($kcols) ON DELETE ${rules[$i]};
EOF
    done

    cat <<EOF
\\echo
\\echo This test will show that ON DELETE ${rules[0]} is in effect due to constraint ${cnames[0]}.
\\echo
\\d refs

\\echo -------------
\\echo Content of refs table before deletion of target:
SELECT * FROM refs;

\\echo -------------
\\echo Attempting to delete a target:
DELETE FROM targets WHERE name = 'foo1' RETURNING *;

\\echo -------------
\\echo Content of refs table after deletion of target:
SELECT * FROM refs;
EOF
}

make_test_sql t_id id "NO ACTION" "SET NULL" "CASCADE" | run_sql -e --single-transaction 2>&1
make_test_sql t_id id "SET NULL" "NO ACTION" "CASCADE" | run_sql -e --single-transaction 2>&1
make_test_sql t_id id "CASCADE" "SET NULL" "NO ACTION" | run_sql -e --single-transaction 2>&1