execute sql scripts from github #sql #git #util

swbc-sql.sh

#!/bin/bash
set -e -o pipefail

# DESCRIPTION:
# * executes all scripts found in the directory identifed by GIT_URL
# * only loads files ending with .sql

# REQUIRED ENV VARS
# =================
# + GIT_TOKEN
# + GIT_USER
# + SQL_PASSWORD
# + SQL_USER

# Harcoded ENV VARS
# =================
# Update these to control what scripts are run and on which server.
# + GIT_URL: points to the repo directory with sql scripts
# + GIT_REF: git reference
# + SQL_SERVER

GIT_URL=https://api.github.com/repos/aspencapital/svc-loans/contents/src/sql/release/MARS-6595
GIT_REF=release/MARS-6595
SQL_SERVER="devserver.flanderscapital.com\qa2"

# gather the SQL script urls
URLS=(`curl --user ${GIT_USER}:${GIT_TOKEN} -H 'Accept: application/vnd.github.v3.raw' -sSL ${GIT_URL}?ref=${GIT_REF} | jq -r 'sort_by(.url) | .[] | select(.name | test("\\\\.sql$")) | .url'`)

for URL in ${URLS[@]}; do
    echo "Executing script ${URL}"
    curl --user ${GIT_USER}:${GIT_TOKEN} -H 'Accept: application/vnd.github.v3.raw' -sSL ${URL} | sqlcmd -S ${SQL_SERVER} -U ${SQL_USER} -P ${SQL_PASSWORD} -i /dev/stdin
done