Created
June 16, 2020 17:07
-
-
Save squiddle/39b603e4c80f87ddf8fba8d8bd2178c3 to your computer and use it in GitHub Desktop.
Bash script that can do a select from the DB, do something with each result, modify the DB after each result and do all this in a single transaction
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/env bash | |
| set -euo pipefail | |
| IFS=$'\t\n' | |
| # | |
| # starts a transaction and selects some data on which something has to be done and if successful recorded in the DB again | |
| # | |
| declare SQL_LIST_CANDIDATES | |
| SQL_LIST_CANDIDATES="$(cat <<"EOF" | |
| SELECT id FROM data WHERE removeme = true; | |
| EOF | |
| )" | |
| declare -r SQL_LIST_CANDIDATES | |
| # | |
| # Prepared Statement to reduce SQL-Injection chance | |
| # Hint: this is still bash and it can still happen | |
| # | |
| declare SQL_PREPARE_RECORDTHETHING_STATEMENT | |
| SQL_PREPARE_RECORDTHETHING_STATEMENT="$(cat <<"EOF" | |
| PREPARE recordthething (int) AS | |
| DELETE | |
| FROM data | |
| WHERE id = $1 | |
| ; | |
| EOF | |
| )" | |
| declare -r SQL_PREPARE_RECORDTHETHING_STATEMENT | |
| function cleanup() { | |
| true | |
| } | |
| function dothething() { | |
| echo "dothething" "$@" | |
| } | |
| function main() { | |
| # uses PGHOST, PGPORT, PGPASSFILE, ... Environment Variables | |
| coproc COPROC (tee psql.dbg.in | psql --quiet --tuples-only --no-align --no-readline --field-separator=$'\t' --pset='footer=off' | tee psql.dbg.out) | |
| declare -r PSQL_PID=${COPROC_PID} | |
| declare -r PSQL_OUT=${COPROC[0]} | |
| declare -r PSQL_IN=${COPROC[1]} | |
| echo "START TRANSACTION;" >&"$PSQL_IN" | |
| echo "$SQL_PREPARE_DELETION_STATEMENT" >&"$PSQL_IN" | |
| echo "$SQL_LIST_CANDIDATES" >&"$PSQL_IN" | |
| echo "SELECT 'END_OF_CANDIDATES';" >&"$PSQL_IN" | |
| while IFS=$'\t' read -r -u "$PSQL_OUT" id; do | |
| if [[ 'END_OF_CANDIDATES' == "$id" ]]; then | |
| break; | |
| fi | |
| if [[ -z "$id" ]]; then | |
| echo 'invalid line; abort' >&2 | |
| exit 100 | |
| fi | |
| # due to set -e the script will abort hard on error | |
| # dothething could have done half of the work so consistency needs to be checked | |
| # after an exit | |
| dothething "$id" | |
| echo "EXECUTE recordthething(\$ID\$${id}\$ID\$);" >&"$PSQL_IN" | |
| done | |
| # finally commit the transaction if we reach this space | |
| echo "COMMIT;" >&"$PSQL_IN" | |
| echo "\\q" >&"$PSQL_IN" | |
| } | |
| trap cleanup EXIT | |
| main "$@" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment