Pantheon - run db query on all sites on an upstream

query_all.sh

#!/usr/bin/env bash

# Example usage: 
#   echo "select distinct type, count(*) from node group by 1 order by 2 desc;" > node_counts.sql
#   ./query_all.sh node_counts.sql live | tee node_counts.txt

UPSTREAM='asdfasdf-asdf-asdf-asdf-asdfasdfasdf';

# Check command line arguments
if [[ -z $1 ]]; then
  echo; echo "Usage: $0 <query_file> [environment]";
  echo; echo;
  exit 1;
elif [[ ! -f $1 || ! -r $1 ]]; then
  echo; echo "The filename '$1' doesn't exist, isn't readable, or isn't an ordinary file.";
  echo; echo;
  exit 1;
fi

# Default to 'dev' environment if none was specified
if [[ -z $2 ]]; then
  echo; echo "No environment specified, assuming dev.."; echo;
  ENV='dev';
else
  ENV=$2;
fi

# We use a second "inner script" because we prefer to use GNU 'parallel' for speed if it's available, and I can't
# figure out how to run a multi-command subprocess with parallel without putting it in a second script.
INNER_SCRIPT='./query_site.sh';


# Check $INNER_SCRIPT exists and is executable
if [[ ! -f $INNER_SCRIPT || ! -x $INNER_SCRIPT ]]; then
  echo; echo "'$INNER_SCRIPT' isn't an executable script. Stopping.";
  echo; echo;
  exit 1;
fi


# ALL sites
SITES=$(terminus site:list --upstream=$UPSTREAM --fields=name --format=string | sort);

# Non-sandbox sites only
# SITES=$(terminus site:list --upstream=$UPSTREAM --fields=name --format=string --filter=plan_name!=Sandbox | sort);

# Check for GNU parallel
USE_PARALLEL=false;
PARALLEL_PATH=$(which parallel);   # 'parallel' command exists
# echo "'parallel' path: $PARALLEL_PATH";

if [[ ! -z $PARALLEL_PATH ]]; then
  PARALLEL_VERSION=$($PARALLEL_PATH --version);   # get version
  # echo "'parallel' version: $PARALLEL_VERSION";
  if [[ $PARALLEL_VERSION == *"GNU parallel"* ]]; then
    # echo "GNU parallel found: $PARALLEL_PATH";
    USE_PARALLEL=true;   # it really is GNU parallel.  we like it.
  fi
fi


if [[ "$USE_PARALLEL" == true ]]; then
  # With parallel
  parallel --delay 0.1 --tag "$INNER_SCRIPT {}.$ENV $1" ::: $SITES
else
  # We're going to use the inner script even without parallel 
  # because DRY and I can't be bothered to update the logic in 2 places.

  for SITE_NAME in $SITES; do
    echo "=== $SITE_NAME ===";
    $INNER_SCRIPT $SITE_NAME.$ENV $1;
    echo;echo;
  done
fi

query_site.sh

#!/usr/bin/env bash

if [[ -z $1 || -z $2 ]]; then
    echo; echo "Usage: $0 <site.env> <query_file>";
    echo; echo;
    exit 1;
fi;

# Split site and environment to support conditional $ENV checking
SITE_NAME=${1%.*};
# echo "SITE_NAME: $SITE_NAME";
ENV=${1##*.};
# echo "ENV: $ENV";


# BACKUP FRESHNESS SCRIPT: https://gist.github.com/weaver299/46257300e53fe50b2a0b929ab721860e

# # Check if backup is "fresh" "enough"... (only if [[ $ENV == 'live' ]] maybe?)
# ./backup_fresh.sh $SITE_NAME.$ENV;
# BACKUP_FRESH=$?; # capture exit code

# if [[ $BACKUP_FRESH -gt 0 ]]; then
#   # 0 means "fresh enough", else the script returns the age in seconds
#   echo "Backup is stale - $BACKUP_FRESH seconds old - Creating a new one...";
#   # echo "terminus backup:create $SITE_NAME.$ENV --keep-for=30 --element=db";
#   terminus backup:create $SITE_NAME.$ENV --keep-for=30 --element=db;
# fi


# Wake site  (only if [[ $ENV == 'live' ]] if you have no sandboxes?)
terminus env:wake $SITE_NAME.$ENV;

cat $2 | $(terminus connection:info --fields=mysql_command --format=string $SITE_NAME.$ENV);

This uses a second "inner script" because I prefer to use GNU parallel for speed if it's available, and I can't
figure out how to run a multi-command subprocess with parallel without putting it in a second script.

We're going to use the inner script even without parallel because DRY and I can't be bothered to update the
logic in 2 places.
 

The script as-published doesn't generate backups, but see the commented out code and optional backup_fresh.sh script to optionally generate one if the latest one is too old. backup_fresh.sh here: https://gist.github.com/weaver299/46257300e53fe50b2a0b929ab721860e
 

Read the code, don't just blindly run it. Standard "I'm not responsible for how you use this" disclaimer applies.