sf-tooldata

#!/usr/bin/env bash

#
# Download data of all or of specific sobjects of the Tooling API as csv files
#
# USAGE
#
#     sf-tooldata                           # query all data from default org and save in default folder
#     sf-tooldata -o ORG                    # query from ORG
#     sf-tooldata -d DIR                    # save data in DIR folder [default: .data]
#     sf-tooldata -s OBJECT1 -s OBJECT2     # query objects OBJECT1 and OBJECT2 and save in DIR
#     sf-tooldata -v OBJECT                 # query Object OBJECT and open in Visidata
#

yesno() {
  echo ""
  read -p "$1 [Y/n] " response
  [[ $response == "n" || $response == "N" ]] && exit 1
}

setTargetOrg() {
  if [[ -z "$org" ]]; then
    org=$(cat .sf/config.json 2>/dev/null | jq -r '."target-org" | select( . != null )')
  fi
  if [[ -z "$org" ]]; then
    org=$(cat ~/.sf/config.json 2>/dev/null | jq -r '."target-org" | select( . != null )')
  fi
  if [[ -z "$org" ]]; then
    echo -e >&2 "\n $(tput setaf 1)Missing target org$(tput sgr 0)\n\n   Either provide with $(tput setaf 0)-o$(tput sgr 0) option, or set via $(tput setaf 0)sf config set target-org <org>$(tput sgr 0)"
    exit 1
  fi
}

org=""
target=".data"
object="all"
visidata=false
listfile=$(mktemp)

while [ $# -gt 0 ]; do
  case $1 in
    -o)
      shift
      org=$1
      ;;
    -d)
      shift
      target=$1
      ;;
    -s)
      shift
      if [[ $object == "all" ]]; then
        object=$1
      else
        object="${object},${1}"
      fi
      ;;
    -v)
      shift
      object=$1
      visidata=true
      ;;
    -h|--help)
      awk '/^[^ #]/{c=1}c==0{print $0}' $0 | sed -n '/^#/p' | sed 1d | sed 's/^#/ /g' \
        | perl -pe "s/ #(.*)$/$(tput setaf 0)\1$(tput sgr 0)/" \
        | perl -pe "s/(USAGE|EXAMPLES)/$(tput setaf 0)\1$(tput sgr 0)/"
      exit 1
      ;;
    *)
      ;;
  esac
  shift
done
setTargetOrg

objects=$(echo ${object} | perl -pe "s/,/, /g" | perl -pe "s/(\w+)/$(tput setaf 4)\1$(tput sgr0)/g")
yesno " Fetch ${objects} data of $(tput setaf 2)Tooling API$(tput sgr0) from $(tput setaf 1)${org}$(tput sgr0) ?"

if [ $visidata == true ]; then
  echo ""
  target=$(mktemp)
  fields=$(sf sobject describe -t -s ${object} -o ${org} | jq -r '.fields[] | select(.name == "FullName" or .name == "Metadata" | not) | .name' | paste -sd, -)
  sf data query -q "SELECT ${fields} FROM ${object}" -t -o ${org} -r csv > "${target}"
  vd "${target}"
  exit 0
fi

if [[ $object == "all" ]]; then
  orgdata=$(sf org display -o ${org} --json)
  instanceUrl=$(jq -r '.result.instanceUrl' <<< "${orgdata}")
  accessToken=$(jq -r '.result.accessToken' <<< "${orgdata}")
  curl -s "${instanceUrl}/services/data/v59.0/tooling/sobjects/" -H "Authorization: Bearer ${accessToken}" | jq -r '.sobjects[].name' > ${listfile}
else
  echo ${object} | tr -d ' ' | tr ',' '\n' > ${listfile}
fi

total=$(cat ${listfile} | wc -l | tr -d ' ')
mkdir -p ${target}
c=0
while read obj; do
  c=$((c+1))
  echo -e "\n $(tput setaf 8)[${c}/${total}]$(tput sgr0) $(tput setaf 4)${obj}$(tput sgr0):"
  [[ $DEBUG == "1" ]] && echo -e "\n   $(tput setaf 0)sf sobject describe -t -s ${obj} -o ${org} | jq -r '.fields[] | select(.name == \"FullName\" or .name == \"Metadata\" | not) | .name' | paste -sd, -$(tput sgr0)"
  fields=$(sf sobject describe -t -s ${obj} -o ${org} 2>/dev/null | jq -r '.fields[] | select(.name == "FullName" or .name == "Metadata" | not) | .name' | paste -sd, -)
  if [[ -z "$fields" ]]; then
    echo -e " ${obj} is $(tput setaf 1)not readable$(tput sgr0)"
  else
    [[ $DEBUG == "1" ]] && echo -e "   $(tput setaf 0)sf data query -q \"SELECT ${fields} FROM ${obj}\" -t -o ${org} -r csv$(tput sgr0)\n"
    sf data query -q "SELECT ${fields} FROM ${obj}" -t -o ${org} -r csv > ${target}/${obj}.csv
    wc -l ${target}/${obj}.csv | awk '{if($1 == "0" || $1 == "1"){ print " " $2 ": \033[31mempty" } else { print " " $2 ": \033[32m" $1-1 " records" } }'
    awk 'NR == 1; NR > 1 { print $0 | "sort" }' ${target}/${obj}.csv > ${target}/temp; mv ${target}/temp ${target}/${obj}.csv
    find ${target}/${obj}.csv -type f -size -2c -delete
  fi
done < <(cat ${listfile})