From 7b81c1105558fdad28cd2555fa24cb06453e6ad6 Mon Sep 17 00:00:00 2001
From: Nathan Perry <np@nathanperry.dev>
Date: Wed, 30 Nov 2022 00:40:37 -0500
Subject: nix: move init script to file

---
 nix/dbinit.sh | 49 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 49 insertions(+)
 create mode 100755 nix/dbinit.sh

(limited to 'nix/dbinit.sh')

diff --git a/nix/dbinit.sh b/nix/dbinit.sh
new file mode 100755
index 0000000..9ca7481
--- /dev/null
+++ b/nix/dbinit.sh
@@ -0,0 +1,49 @@
+#!/usr/bin/env bash
+
+set -euo pipefail
+
+PSQL=$1
+DB=$2
+DB_USER=$3
+
+echo 'creating role, database, assigning owner...'
+
+"$PSQL" <<EOF
+DO \$\$
+BEGIN
+CREATE ROLE $DB_USER WITH LOGIN;
+EXCEPTION WHEN duplicate_object THEN RAISE NOTICE '%, skipping', SQLERRM USING ERRCODE = SQLSTATE;
+END
+\$$;
+
+SELECT 'CREATE DATABASE $DB'
+WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$DB')\gexec
+
+ALTER DATABASE $DB OWNER TO $DB_USER;
+EOF
+
+echo 'echo creating pgcrypto extension...'
+
+"$PSQL" -c "CREATE EXTENSION IF NOT EXISTS pgcrypto;" "$DB"
+
+readarray -t TABLES    < <("$PSQL" -qAt -c "SELECT tablename      FROM pg_tables                    WHERE schemaname      = 'public';" "$DB")
+readarray -t SEQUENCES < <("$PSQL" -qAt -c "SELECT sequence_name  FROM information_schema.sequences WHERE sequence_schema = 'public';" "$DB")
+readarray -t VIEWS     < <("$PSQL" -qAt -c "SELECT table_name     FROM information_schema.views     WHERE table_schema    = 'public';" "$DB")
+
+cat <<EOF >&2
+patching owner:
+    tables:     ${TABLES[*]}
+    sequences:  ${SEQUENCES[*]}
+    views:      ${VIEWS[*]}
+EOF
+
+STMT=""
+
+for tbl in "${TABLES[@]}" "${SEQUENCES[@]}" "${VIEWS[@]}"; do
+    STMT+="ALTER TABLE \"$tbl\" OWNER TO $DB_USER;"
+    STMT+=$'\n'
+done
+
+"$PSQL" -c "$STMT" "$DB"
+
+echo "success"
-- 
cgit v1.3.1