sf-data

#!/usr/bin/env bash

#
# Download data of all or of specific Salesforce objects as csv files
#
# USAGE
#
#    sf-data                                  # download all data
#    sf-data -o ORG                           # download from ORG
#    sf-data -d DIR                           # save data in DIR folder [default: .data]
#    sf-data -s OBJECT_1 -s OBJECT_2          # download OBJECT_1 and OBJECT_2
#    sf-data -v OBJECT                        # download OBJECT and open in Visidata
#    sf-data -r                               # refresh data in current directory
#    sf-data -l                               # only fetch objects that contained data during the last run
#
# EXAMPLES
#
#     sf-data -s Account -d .
#     sf-data -s Account -s Contact
#
# COMMANDS
#
#     sf sobject list -s all -o ORG
#
#     sf sobject describe -s OBJECT -o ORG | jq -r '.fields[].name' | paste -sd, -
#     sf data query -q "select FIELDS from OBJECT" -o ORG
#
#     obj="Account"; sf data query -q "select $(sf sobject describe -s $obj | jq -r '.fields[].name' | paste -sd, -) from $obj" -r csv > $obj.csv
#
#     rm /tmp/sf-data.objects                 # delete cached objects for bash-completion
#

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

show_help() {
  # shellcheck disable=SC2086
  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|COMMANDS)/$(tput setaf 0)\1$(tput sgr 0)/" \
    | perl -pe "s/\`(.+)\`/$(tput sgr 0 1)\1$(tput sgr 0)/"
  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=""
visidata=false
listfile=$(mktemp)
refresh=false
last=false
lastfile="/tmp/sf-data.lastobjects"

while [ $# -gt 0 ]; do
  case $1 in
    -o|--org)
      shift
      org=$1
      ;;
    -d|--destination)
      shift
      target=$1
      ;;
    -l|--last)
      last=true
      ;;
    -r|--refresh)
      refresh=true
      ;;
    -s|--sobject)
      shift
      if [[ $object == "" ]]; then
        object=$1
      else
        object="${object},${1}"
      fi
      ;;
    -v|--visidata)
      shift
      object=$1
      visidata=true
      ;;
    -h|--help)
      show_help
      ;;
    *)
      ;;
  esac
  shift
done
setTargetOrg

if [ $refresh == true ]; then
  object=$(fd . ${target} -e csv -x echo {/.} | tr '\n' ',')
  if [[ ${object} == "" ]];then
    echo -e >&2 "\n Folder $(tput setaf 4)${target}$(tput sgr 0) is empty"
    exit 1
  fi
  object=${object::-1}
fi

objects=$(echo ${object} | perl -pe "s/,/, /g" | perl -pe "s/(\w+)/$(tput setaf 4)\1$(tput sgr0)/g")
if [[ $object == "" ]] && [[ $last == "false" ]]; then
  yesno " Fetch $(tput setaf 4)all$(tput sgr0) data from $(tput setaf 1)${org}$(tput sgr0) ?"
elif [[ $object == "" ]] && [[ $last == "true" ]]; then
  yesno " Fetch $(tput setaf 4)all$(tput sgr0) data of $(tput setaf 4)last run$(tput sgr0) from $(tput setaf 1)${org}$(tput sgr0) ?"
else
  yesno " Fetch ${objects} data from $(tput setaf 1)${org}$(tput sgr0) ?"
fi

if [ $visidata == true ]; then
  echo ""
  target=$(mktemp)
  [[ $DEBUG == "1" ]] && echo -e "   $(tput setaf 0)sf data query -q \"SELECT `sf sobject describe -o ${org} -s ${object} | jq -r '.fields[].name' | paste -sd, -` FROM ${object}\" -o ${org}$(tput sgr0)\n"
  sf data query -q "SELECT `sf sobject describe -o ${org} -s ${object} | jq -r '.fields[].name' | paste -sd, -` FROM ${object}" -o ${org} -r csv > "${target}"
  vd "${target}"
  exit 0
fi

# this is an opinionated list of sobjects which are either not of interest or do not support queries
if [[ $object == "" ]] && [[ $last == "false" ]]; then
  sf sobject list -o "$org" -s all > "$listfile"
elif [[ $object == "" ]] && [[ $last == "true" ]]; then
  cat "$lastfile" > "$listfile"
else
  echo "$object" | tr -d ' ' | tr ',' '\n' > "$listfile"
fi
[[ $DEBUG == "1" ]] && echo -e "   $(tput setaf 0)${listfile}$(tput sgr0)\n"

total=$(cat "$listfile" | wc -l | tr -d ' ')
mkdir -p ${target}
c=0
startTime=$(date +%s)
:> $lastfile

while read obj; do
  c=$((c+1))
  echo -e "\n $(tput setaf 8)[${c}/${total}]$(tput sgr0) $(tput setaf 4)${obj}$(tput sgr0):"
  fields=$(sf sobject describe -s ${obj} -o ${org} | jq -r '.fields[].name' \
    | sed /MV_Minutes_Since_Modified__c/d \
    | sed /MV_Last_login_days__c/d \
    | sed /LastActivityDate/d \
    | sed /LastViewedDate/d \
    | sed /LastReferencedDate/d \
    | sed /MV_DaysSinceCreated__c/d \
    | paste -sd, -)
  [[ $DEBUG == "1" ]] && echo -e "\n   $(tput setaf 0)sf data query -q \"SELECT ${fields} FROM ${obj}\" -o ${org} -r csv$(tput sgr0)\n"
  sf data query -q "SELECT ${fields} FROM ${obj}" -o ${org} -r csv 2> /dev/null > ${target}/${obj}.csv
  dataSize=$(csvtk nrow ${target}/${obj}.csv)
  if [ $dataSize == '0' ]; then
    echo -e " ${target}/${obj}: $(tput setaf 1)empty$(tput sgr0)"
  else
    if [[ $dataSize == '1' ]]; then
      echo -e " ${target}/${obj}: $(tput setaf 2)1 record$(tput sgr0)"
    else
      echo -e " ${target}/${obj}: $(tput setaf 2)${dataSize} records$(tput sgr0)"
    fi
    echo "$obj" >> "$lastfile"
  fi
  find ${target}/${obj}.csv -type f -size -2c -delete
done < <(cat "$listfile")

endTime=$(date +%s)
duration=$(date -j -f %s $((endTime - startTime)) +%M:%S)
if [ ! "$duration" == "00:00" ]; then
  echo -e "\n $(tput setaf 0)download duration: ${duration} min$(tput sgr0)\n"
fi