332 lines
11 KiB
Bash
Executable File
332 lines
11 KiB
Bash
Executable File
#!/usr/bin/env bash
|
|
set -euo pipefail
|
|
|
|
root=$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)
|
|
validator=$root/scripts/ci-validate-migrations.mjs
|
|
fixture_root=$(mktemp -d)
|
|
trap 'rm -rf "$fixture_root"' EXIT
|
|
|
|
new_repo() {
|
|
local name=$1
|
|
local repo=$fixture_root/$name
|
|
|
|
mkdir -p "$repo/apps/api/migrations"
|
|
git -C "$repo" init -q
|
|
git -C "$repo" config user.name 'CI Migration Test'
|
|
git -C "$repo" config user.email 'ci-migration-test@example.invalid'
|
|
printf 'CREATE TABLE accounts (id bigint PRIMARY KEY);\n' > \
|
|
"$repo/apps/api/migrations/0001_init.sql"
|
|
git -C "$repo" add apps/api/migrations/0001_init.sql
|
|
git -C "$repo" commit -qm baseline
|
|
mkdir -p "$repo/deploy/ci"
|
|
git -C "$repo" rev-parse HEAD > \
|
|
"$repo/deploy/ci/production-migration-base"
|
|
printf '%s\n' "$repo"
|
|
}
|
|
|
|
commit_all() {
|
|
local repo=$1
|
|
local message=$2
|
|
git -C "$repo" add -A
|
|
git -C "$repo" commit -qm "$message"
|
|
}
|
|
|
|
expect_pass() {
|
|
local repo=$1
|
|
local base=$2
|
|
local immutable_base=${3:-}
|
|
(
|
|
cd "$repo"
|
|
if [[ -n $immutable_base ]]; then
|
|
node "$validator" "$base" "$immutable_base"
|
|
else
|
|
node "$validator" "$base"
|
|
fi
|
|
)
|
|
}
|
|
|
|
expect_fail() {
|
|
local repo=$1
|
|
local base=$2
|
|
local expected=$3
|
|
local immutable_base=${4:-}
|
|
local output status
|
|
|
|
if [[ -n $immutable_base ]]; then
|
|
set +e
|
|
output=$(cd "$repo" && node "$validator" "$base" "$immutable_base" 2>&1)
|
|
status=$?
|
|
set -e
|
|
else
|
|
set +e
|
|
output=$(cd "$repo" && node "$validator" "$base" 2>&1)
|
|
status=$?
|
|
set -e
|
|
fi
|
|
if [[ $status -eq 0 ]]; then
|
|
printf 'migration validator unexpectedly passed: %s\n' "$expected" >&2
|
|
exit 1
|
|
fi
|
|
grep -Fq "$expected" <<<"$output" || {
|
|
printf 'missing expected migration failure %q in:\n%s\n' "$expected" "$output" >&2
|
|
exit 1
|
|
}
|
|
}
|
|
|
|
repo=$(new_repo safe)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_expand_accounts.sql" <<'SQL'
|
|
-- A comment mentioning DROP TABLE must not trigger the gate.
|
|
ALTER TABLE accounts ADD COLUMN display_name text;
|
|
CREATE TABLE audit_events (id bigint PRIMARY KEY, note text);
|
|
INSERT INTO audit_events (id, note) VALUES (1, 'DROP TABLE is documentation');
|
|
UPDATE accounts SET display_name = 'ready' WHERE display_name IS NULL;
|
|
SQL
|
|
commit_all "$repo" safe
|
|
expect_pass "$repo" "$base"
|
|
|
|
repo=$(new_repo drop)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'DROP\nTABLE accounts;\n' >"$repo/apps/api/migrations/0002_drop.sql"
|
|
commit_all "$repo" drop
|
|
expect_fail "$repo" "$base" 'destructive DROP operation'
|
|
|
|
repo=$(new_repo delete)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'DELETE FROM accounts;\n' >"$repo/apps/api/migrations/0002_delete.sql"
|
|
commit_all "$repo" delete
|
|
expect_fail "$repo" "$base" 'DELETE FROM operation'
|
|
|
|
repo=$(new_repo incompatible_alter)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_alter.sql" <<'SQL'
|
|
ALTER TABLE accounts ADD COLUMN external_id text NOT NULL;
|
|
ALTER TABLE accounts ALTER COLUMN id TYPE numeric;
|
|
SQL
|
|
commit_all "$repo" alter
|
|
expect_fail "$repo" "$base" 'non-null column addition'
|
|
expect_fail "$repo" "$base" 'column type change'
|
|
|
|
repo=$(new_repo dynamic_sql)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_dynamic.sql" <<'SQL'
|
|
DO $$
|
|
BEGIN
|
|
EXECUTE 'DROP ' || 'TABLE accounts';
|
|
END
|
|
$$;
|
|
SQL
|
|
commit_all "$repo" dynamic
|
|
expect_fail "$repo" "$base" 'dynamic SQL execution'
|
|
|
|
repo=$(new_repo escape_string)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_escape.sql" <<'SQL'
|
|
SELECT E'foo\'bar'; DROP TABLE accounts; --';
|
|
SQL
|
|
commit_all "$repo" escape
|
|
expect_fail "$repo" "$base" 'PostgreSQL escape/unicode strings are not allowed'
|
|
|
|
repo=$(new_repo string_semantics_override)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'SET standard_conforming_strings = off;\n' > \
|
|
"$repo/apps/api/migrations/0002_string_mode.sql"
|
|
commit_all "$repo" string_mode
|
|
expect_fail "$repo" "$base" 'SQL string semantics override'
|
|
|
|
repo=$(new_repo transaction_control)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_commit.sql" <<'SQL'
|
|
ALTER TABLE accounts ADD COLUMN display_name text;
|
|
COMMIT;
|
|
UPDATE accounts SET display_name = 'partially committed';
|
|
SQL
|
|
commit_all "$repo" transaction_control
|
|
expect_fail "$repo" "$base" 'transaction control operation'
|
|
|
|
repo=$(new_repo bare_end_transaction)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_end.sql" <<'SQL'
|
|
ALTER TABLE accounts ADD COLUMN display_name text;
|
|
END;
|
|
UPDATE accounts SET display_name = 'partially committed';
|
|
SQL
|
|
commit_all "$repo" bare_end_transaction
|
|
expect_fail "$repo" "$base" 'transaction control operation'
|
|
|
|
repo=$(new_repo plpgsql_block_end)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_function.sql" <<'SQL'
|
|
CREATE FUNCTION account_count() RETURNS bigint
|
|
LANGUAGE plpgsql AS $$
|
|
BEGIN
|
|
RETURN (SELECT count(*) FROM accounts);
|
|
END;
|
|
$$;
|
|
SQL
|
|
commit_all "$repo" plpgsql_block_end
|
|
expect_fail "$repo" "$base" 'procedural SQL body'
|
|
|
|
repo=$(new_repo ordinary_string_procedural_body)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_do_string.sql" <<'SQL'
|
|
DO LANGUAGE plpgsql 'BEGIN DROP TABLE accounts; END';
|
|
SQL
|
|
commit_all "$repo" ordinary_string_procedural_body
|
|
expect_fail "$repo" "$base" 'procedural SQL body'
|
|
|
|
repo=$(new_repo ordinary_string_boundary)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_string_boundary.sql" <<'SQL'
|
|
SELECT '\'; DROP TABLE accounts; --';
|
|
SQL
|
|
commit_all "$repo" ordinary_string
|
|
expect_fail "$repo" "$base" 'destructive DROP operation'
|
|
|
|
repo=$(new_repo unicode_string)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf "SELECT U&'d\\0061ta';\n" >"$repo/apps/api/migrations/0002_unicode.sql"
|
|
commit_all "$repo" unicode
|
|
expect_fail "$repo" "$base" 'PostgreSQL escape/unicode strings are not allowed'
|
|
|
|
repo=$(new_repo merge_delete)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
cat >"$repo/apps/api/migrations/0002_merge.sql" <<'SQL'
|
|
MERGE INTO accounts USING stale_accounts ON accounts.id = stale_accounts.id
|
|
WHEN MATCHED THEN DELETE;
|
|
SQL
|
|
commit_all "$repo" merge
|
|
expect_fail "$repo" "$base" 'MERGE operation'
|
|
|
|
repo=$(new_repo replace_view)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'CREATE OR REPLACE VIEW active_accounts AS SELECT id FROM accounts;\n' > \
|
|
"$repo/apps/api/migrations/0002_replace.sql"
|
|
commit_all "$repo" replace
|
|
expect_fail "$repo" "$base" 'CREATE OR REPLACE operation'
|
|
|
|
repo=$(new_repo rename_function)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'ALTER FUNCTION calculate_total() RENAME TO calculate_total_legacy;\n' > \
|
|
"$repo/apps/api/migrations/0002_rename.sql"
|
|
commit_all "$repo" rename
|
|
expect_fail "$repo" "$base" 'object or column rename'
|
|
|
|
repo=$(new_repo set_schema)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'ALTER TABLE accounts SET SCHEMA archive;\n' > \
|
|
"$repo/apps/api/migrations/0002_schema.sql"
|
|
commit_all "$repo" schema
|
|
expect_fail "$repo" "$base" 'SET SCHEMA operation'
|
|
|
|
repo=$(new_repo drop_domain)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'DROP DOMAIN account_code CASCADE;\n' > \
|
|
"$repo/apps/api/migrations/0002_drop_domain.sql"
|
|
commit_all "$repo" drop_domain
|
|
expect_fail "$repo" "$base" 'destructive DROP operation'
|
|
|
|
repo=$(new_repo immutable)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf '\n-- retroactive edit\n' >>"$repo/apps/api/migrations/0001_init.sql"
|
|
commit_all "$repo" edit
|
|
expect_fail "$repo" "$base" 'applied migrations are immutable (status M)'
|
|
|
|
repo=$(new_repo immutable_after_baseline)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'ALTER TABLE accounts ADD COLUMN nickname text;\n' > \
|
|
"$repo/apps/api/migrations/0002_nickname.sql"
|
|
commit_all "$repo" add
|
|
immutable_base=$(git -C "$repo" rev-parse HEAD)
|
|
printf '\nUPDATE accounts SET nickname = NULL;\n' >> \
|
|
"$repo/apps/api/migrations/0002_nickname.sql"
|
|
commit_all "$repo" modify
|
|
output=$(
|
|
cd "$repo"
|
|
node "$validator" "$base" "$immutable_base" 2>&1 || true
|
|
)
|
|
grep -Fq 'migration present on main is immutable (status M)' <<<"$output" || {
|
|
printf 'stale production baseline did not preserve main migration immutability:\n%s\n' \
|
|
"$output" >&2
|
|
exit 1
|
|
}
|
|
|
|
repo=$(new_repo filename)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/next.SQL"
|
|
commit_all "$repo" filename
|
|
expect_fail "$repo" "$base" 'new migration must use NNNN_lowercase_name.sql'
|
|
|
|
repo=$(new_repo ordering)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/0000_late.sql"
|
|
commit_all "$repo" ordering
|
|
expect_fail "$repo" "$base" 'new migration name must sort after 0001_init.sql'
|
|
|
|
repo=$(new_repo stale_baseline)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'ALTER TABLE accounts ADD COLUMN nickname text;\n' > \
|
|
"$repo/apps/api/migrations/0002_nickname.sql"
|
|
commit_all "$repo" main_migration
|
|
immutable_base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'ordinary code change\n' >"$repo/README.md"
|
|
commit_all "$repo" code_only
|
|
expect_pass "$repo" "$base" "$immutable_base"
|
|
|
|
repo=$(new_repo baseline_advance)
|
|
production_base=$(git -C "$repo" rev-parse HEAD)
|
|
mkdir -p "$repo/deploy/ci"
|
|
printf '%s\n' "$production_base" >"$repo/deploy/ci/production-migration-base"
|
|
commit_all "$repo" baseline_file
|
|
immutable_base=$(git -C "$repo" rev-parse HEAD)
|
|
git -C "$repo" switch -q -c release-baseline-update "$immutable_base"
|
|
printf '%s\n' "$immutable_base" >"$repo/deploy/ci/production-migration-base"
|
|
commit_all "$repo" valid_baseline_advance
|
|
expect_pass "$repo" "$immutable_base" "$immutable_base"
|
|
|
|
git -C "$repo" switch -q -c malicious-baseline "$immutable_base"
|
|
printf 'DROP TABLE accounts;\n' >"$repo/apps/api/migrations/0002_drop.sql"
|
|
commit_all "$repo" hidden_destructive_migration
|
|
hidden_sha=$(git -C "$repo" rev-parse HEAD)
|
|
printf '%s\n' "$hidden_sha" >"$repo/deploy/ci/production-migration-base"
|
|
commit_all "$repo" hide_migration_in_baseline
|
|
expect_fail "$repo" "$hidden_sha" \
|
|
'production migration baseline must belong to the immutable event base history' \
|
|
"$immutable_base"
|
|
|
|
repo=$(new_repo nested)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
mkdir -p "$repo/apps/api/migrations/archive"
|
|
printf 'SELECT 1;\n' >"$repo/apps/api/migrations/archive/9999_hidden.sql"
|
|
commit_all "$repo" nested
|
|
expect_fail "$repo" "$base" 'new migration must use NNNN_lowercase_name.sql'
|
|
|
|
repo=$(new_repo symlink)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf 'SELECT 1;\n' >"$repo/outside.sql"
|
|
ln -s ../../../outside.sql "$repo/apps/api/migrations/0002_symlink.sql"
|
|
commit_all "$repo" symlink
|
|
expect_fail "$repo" "$base" 'new migration must be a mode 100644 regular file'
|
|
|
|
repo=$(new_repo baseline_symlink)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
printf '%s\n' "$base" >"$repo/baseline-target"
|
|
rm "$repo/deploy/ci/production-migration-base"
|
|
ln -s ../../baseline-target "$repo/deploy/ci/production-migration-base"
|
|
commit_all "$repo" baseline_symlink
|
|
expect_fail "$repo" "$base" \
|
|
'production migration baseline must be a mode 100644 regular file'
|
|
|
|
repo=$(new_repo ancestry)
|
|
base=$(git -C "$repo" rev-parse HEAD)
|
|
git -C "$repo" switch -q --orphan unrelated
|
|
printf 'unrelated\n' >"$repo/unrelated"
|
|
git -C "$repo" add unrelated
|
|
git -C "$repo" commit -qm unrelated
|
|
expect_fail "$repo" "$base" 'production migration baseline is not an ancestor of HEAD'
|
|
|
|
repo=$(new_repo invalid_sha)
|
|
expect_fail "$repo" deadbeef 'must be a full lowercase commit SHA'
|
|
|
|
echo 'migration_tests=PASS'
|