Keep Pantheon test environment up-to-date and squeeky clean

nightly_workflow.sh

#!/bin/bash

PSITE='your-site-name'
PUUID='aaaaaaaa-1111-bbbb-2222-cccccccccccc'
PEMAIL='your-email@example.com'
PPASS='your-password-here--i-know'

# Authenticate with Terminus
drush pauth $PEMAIL --password=$PPASS

# Update Pantheon aliases.
drush paliases

# Clone from the live environment to the test environment.
drush psite-clone $PUUID live test -y

# Sleep for an hour; the above command returns long before the clone completes.
# I wish this could be more deterministic, but alas...
sleep 3600

# Once cloned, clean out cache and other ephemeral data that's not needed.
`drush @pantheon.$PSITE.test sql-connect` -e "SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'pantheon' AND TABLE_NAME LIKE 'cache%' OR TABLE_NAME IN ('watchdog', 'accesslog', 'queue', 'sessions');" | sed 1d | `drush @pantheon.$PSITE.test sql-connect`

# Use simpleSAMLphp? Drop tables. They'll restore themselves when needed.
`drush @pantheon.$PSITE.test sql-connect` -e "SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'pantheon' AND TABLE_NAME LIKE 'simpleSAMLphp_%';" | sed 1d | `drush @pantheon.$PSITE.test sql-connect`