dibs.sh

#!/usr/bin/env bash
# Symlink as ~/bin/dibs for happy fun time excitement.

# Nothing pisses me off more than CTRL-c'ing during an import script
# And just having it fire up the next bloody file to import
trap 'echo Control-C trap caught; exit 1' 2 #traps Ctrl-C (signal 2)

# Exit if there is any errors
set -e

CONFIG_FILE="$HOME/.dibsrc"

# Don't edit this file, instead put custom defaults into CONFIG_FILE
SELF=`basename $0`
HOST="localhost"
DBUSER="mysql"
DBPASS="unspecified"
DATABASE="sugarinternal"
LOGIN_PATH="" # New in mySQL 5.6, see mysql_config_editor
PRE_IMPORT_DIR="pre_import"
POST_IMPORT_DIR="post_import"

CDIR="$HOME/archive"
VERBOSE=false
QUICK=false
REVERSE=false
FULLER=false
MODE="unspecified"
APP="SI"

ARCHIVE="$USER@si-dev:~jhoffmann/archive"
DUMP="mysqldump"
DUMPOPTS="-e --max-allowed-packet=2048M -Q --opt --skip-comments --skip-dump-date --single-transaction"
MYSQL="mysql"

if [ -r "${CONFIG_FILE}" ]; then source "${CONFIG_FILE}"; fi

# These options are meant to be set in CONFIG_FILE and don't have command line
# equivalents.
DIFFCMD="php -f $HOME/iAppsUtilityScripts/db_diff_generator/mysqldiff.php"
DIFFARGS="--drop-tables"
DIFF1HOST=$HOST
DIFF1DB=$DATABASE
DIFF1USER=$DBUSER
DIFF1PASS=$DBPASS

DIFF2HOST=$DIFF1HOST
DIFF2DB="pristine"
DIFF2USER=$DIFF1USER
DIFF2PASS=$DIFF1PASS

SYNC_CMD="rsync -rlDcz --delete-after -e ssh"
LAST_SYNC="$HOME/.dibs_last_sync"

# Load this twice
if [ -r "${CONFIG_FILE}" ]; then source "${CONFIG_FILE}"; fi

define()
{
    IFS='\n' read -r -d '' ${1} || true;
}

# A fuller dump for Cognos reporting
define SCHEMA_FULLER <<'EOF'

campaign_log
ci_email_log
email_cache
emails_email_addr_rel
emails_text
fts_queue
ganda_commentmeta
ganda_comments
ganda_links
ganda_options
ganda_postmeta
ganda_posts
ganda_term_relationships
ganda_term_taxonomy
ganda_terms
ganda_usermeta
ganda_users
job_queue
pardot_log
pardot_log_messages
round_robin_log
round_robin_tracker
sugar_updates
tracker
tracker_perf
tracker_queries
tracker_sessions
tracker_tracker_queries
EOF

# The blank is important, please do not remove it
define SCHEMA_ONLY <<'EOF'

calls
calls_cstm
campaign_log
ci_email_log
email_cache
emails
emails_accounts
emails_archive
emails_beans
emails_bugs
emails_cases
emails_contacts
emails_email_addr_rel
emails_leads
emails_opportunities
emails_project_tasks
emails_projects
emails_prospects
emails_quotes
emails_tasks
emails_text
emails_users
fts_queue
ganda_commentmeta
ganda_comments
ganda_links
ganda_options
ganda_postmeta
ganda_posts
ganda_term_relationships
ganda_term_taxonomy
ganda_terms
ganda_usermeta
ganda_users
job_queue
notes
notes_cstm
pardot_log
pardot_log_messages
prospect_lists_prospects
prospects
round_robin_log
round_robin_tracker
session_history
sugar_installations
sugar_installations_cstm
sugar_updates
tracker
tracker_perf
tracker_queries
tracker_sessions
tracker_tracker_queries
EOF

say()
{
    if [ -x /usr/bin/say ]; then
        /usr/bin/say -v Victoria "${1}"
    fi
}

dolog()
{
    if $VERBOSE; then
        echo `date "+%D %H:%M"` $1;
    fi
}
usage()
{
    cat <<EOF
Export/Import/Sync - iApps Team Databases - Project Diabetes

Usage: $SELF -m <EISDL> [options] [loglevel]

Arguments:
    -m  Mode                                [MODE:${MODE}]
    -h  Hostname                            [HOST:${HOST}]
    -b  Database                            [DATABASE:${DATABASE}]
    -u  User                                [DBUSER:${DBUSER}]
    -p  Password                            [DBPASS:xxxx]
    -d  Import/Export Directory             [CDIR:${CDIR}]
    -a  Application                         [APP:${APP}]

Flags:
    -v  Verbose Output                      [VERBOSE:${VERBOSE}]
    -q  Quick Import (updated files only)   [QUICK:${QUICK}]
    -r  Reverse Diff                        [REVERSE:${REVERSE}]
    -f  Full (almost) Export or Import      [FULLER:${FULLER}]

Notes:
    The full (-f) export can be very large.  Use it carefully.
    The full (-f) import includes the si_*view views where they are, by default,
       excluded

Configuration:
    Default values can be stored in $CONFIG_FILE, which will
    be source'd.

Examples:
    # Use rsync to fetch the latest archive to your home directory
    $SELF -m S -v

    # Import a local archive into the specific database (SI or D7)
    $SELF -m I -d ~/archive -h localhost -u mysql -p banana -a SI -b si-local -v -q

    # Use sed to update the log level in config_override.php
    $SELF -m L info

EOF

    say "Learn it.  Love it."
    exit $1
}

is_schema_only()
{
    # Hack for whole word searching
    RE="
$1
"
    if $FULLER; then
        # Fuller also includes all the audit tables
        if [[ "${SCHEMA_FULLER}" =~ $RE ]]; then
            return 0
        fi
    else
        if [[ "${SCHEMA_ONLY}" =~ $RE ]]; then
            return 0
        elif [[ $1 =~ _audit ]]; then
            return 0
        fi
    fi

    return -1
}

optspec="h:b:u:p:d:m:a:qvrfc"
while getopts "${optspec}" optchar; do
    case "${optchar}" in
        h) HOST="${OPTARG}" ;;
        b) DATABASE="${OPTARG}" ;;
        u) DBUSER="${OPTARG}" ;;
        p) DBPASS="${OPTARG}" ;;
        d) CDIR="${OPTARG}" ;;
        v) VERBOSE=true     ;;
        m) MODE="${OPTARG}" ;;
        a) APP="${OPTARG}"  ;;
        q) QUICK=true       ;;
        r) REVERSE=true     ;;
        f) FULLER=true      ;;
        ?) usage -1         ;;
    esac
done

if [ -n "${LOGIN_PATH}" ]; then
    MYSQL_AUTH="--login-path=${LOGIN_PATH}"
else
    MYSQL_AUTH="-h$HOST -u$DBUSER -p$DBPASS"
fi

# check which application to work with, defaults to SI
if [ $APP == "D7" ]; then
    APPNAME="drupal7"
elif [ $APP == "si7" ]; then
    APPNAME="si7"
else
    APPNAME="si2-production"
fi

# Preserve base path for pre and post dibs SQL scripts
BASEDIR=${CDIR}

case $MODE in
    # Export Mode
    E)
        if $FULLER; then
            CDIR=${CDIR}/${APPNAME}-full
        else
            CDIR=${CDIR}/${APPNAME}
        fi
        if [ ! -d "${CDIR}/full" -o ! -d "${CDIR}/schema" ]; then
            mkdir -p "${CDIR}/full"
            mkdir -p "${CDIR}/schema"
        else
            /bin/rm -f "${CDIR}/full/*.sql"
            /bin/rm -f "${CDIR}/schema/*.sql"
        fi

        TABLES=`$MYSQL $MYSQL_AUTH -NB $DATABASE -e "SHOW TABLES"`

        for table in $TABLES; do
            TYPE="full"
            DUMPOPTS_TMP="$DUMPOPTS"
            if is_schema_only $table; then
                TYPE="schema"
                DUMPOPTS_TMP="$DUMPOPTS --no-data"
            fi
            FILE="${CDIR}/${TYPE}/${table}.sql"
            dolog "Exporting ${DBUSER}@${HOST}:${DATABASE}.${table} to ${FILE}"
            $DUMP ${MYSQL_AUTH} ${DUMPOPTS_TMP} ${DATABASE} ${table} > ${FILE}
        done

        say "Export done."
        ;;

    # Import Mode
    I)
        FIND_OPTS=""
        if $FULLER; then
            CDIR=${CDIR}/${APPNAME}-full
        else
            CDIR=${CDIR}/${APPNAME}
        fi
        if $QUICK; then
            if [ -e ${LAST_SYNC} ]; then
                FIND_OPTS="-newer ${LAST_SYNC}"
            fi
        fi

        # Run any pre-import scripts
        pre_import_dirs=""
        if [ -d ${BASEDIR}/${PRE_IMPORT_DIR} ]; then
            pre_import_dirs="${pre_import_dirs} ${BASEDIR}/${PRE_IMPORT_DIR}"
        fi
        if [ -d ${BASEDIR}/${PRE_IMPORT_DIR}/${APP} ]; then
            pre_import_dirs="${pre_import_dirs} ${BASEDIR}/${PRE_IMPORT_DIR}/${APP}"
        fi
        if [ -n "${pre_import_dirs}" ]; then
            dolog "Running pre-import SQL scripts"
            for PREIMPORT in `find ${pre_import_dirs} -maxdepth 1 -type f -name "*.sql"`; do
                dolog "Running ${PREIMPORT} on ${DBUSER}@${HOST}:${DATABASE}"
                $MYSQL ${MYSQL_AUTH} ${DATABASE} < ${PREIMPORT}
            done
        fi

        # Do the dibs import
        dolog "Running main import"
        for FILE in `find "${CDIR}" ${FIND_OPTS} -type f -name "*.sql"`; do
            if [[ ! $FILE == */si_*view.sql ]] || $FULLER; then
                dolog "Importing ${FILE} to ${DBUSER}@${HOST}:${DATABASE}.${table}"
                $MYSQL ${MYSQL_AUTH} ${DATABASE} < ${FILE}
            else
                dolog "Skipping ${FILE}"
            fi
        done

        #if it's drupal7, need to make some modifications
        if [ $APP == "D7" ]; then
            dolog "Creating temp file to truncate cache tables and update cache variables"

            # create a temp sql file and get the tables to trunc
            FILE="${CDIR}/cache_updates.sql"
            echo "UPDATE drupal7_variable SET value = 'i:0;' WHERE name='cache' OR name='preprocess_js' OR name='preprocess_css';" > ${FILE}
            TABLES=`$MYSQL ${MYSQL_AUTH} -NB ${DATABASE} -e "SHOW TABLES LIKE '%cache%'"`
            for table in $TABLES; do
                echo "TRUNCATE TABLE ${table};" >> ${FILE}
            done

            # run it and then delete it
            dolog "Running the file and then deleting it"
            $MYSQL ${MYSQL_AUTH} ${DATABASE} < ${FILE}
            /bin/rm -f ${FILE}
        fi

        # Run any post-import scripts
        post_import_dirs=""
        if [ -d ${BASEDIR}/${POST_IMPORT_DIR} ]; then
            post_import_dirs="${post_import_dirs} ${BASEDIR}/${POST_IMPORT_DIR}"
        fi
        if [ -d ${BASEDIR}/${POST_IMPORT_DIR}/${APP} ]; then
            post_import_dirs="${post_import_dirs} ${BASEDIR}/${POST_IMPORT_DIR}/${APP}"
        fi
        if [ -n "${post_import_dirs}" ]; then
            dolog "Running post-import SQL scripts"
            for POSTIMPORT in `find ${post_import_dirs} -maxdepth 1 -type f -name "*.sql"`; do
                dolog "Running ${POSTIMPORT} on ${DBUSER}@${HOST}:${DATABASE}"
                $MYSQL ${MYSQL_AUTH} ${DATABASE} < ${POSTIMPORT}
            done
        fi

        say "Import done."
        ;;

    S)
        if $FULLER; then
            ARCHIVE=${ARCHIVE}/${APPNAME}-full
        else
            ARCHIVE=${ARCHIVE}/${APPNAME}
        fi
        dolog "Starting rsync, this could take a few minutes."
        START=`date "+%y%m%d%H%M.%S"`
        SYNC_OPTS=""
        if $VERBOSE; then
            SYNC_OPTS="-i"
        fi
        ${SYNC_CMD} ${SYNC_OPTS} --filter='- sync.sql' ${ARCHIVE} ${CDIR}
        dolog "Done!"

        # Record the time we started the sync by touching a file for find -newer
        touch -t ${START} ${LAST_SYNC}

        say "Sync done."
        ;;

    D)
        dolog "-- Starting data diff, this could take a few minutes."
        if $REVERSE; then
            ${DIFFCMD} -- ${DIFFARGS} \
                --host1 ${DIFF2HOST} --user1 "${DIFF2USER}" --pwd1 "${DIFF2PASS}" --database1 ${DIFF2DB} \
                --host2 ${DIFF1HOST} --user2 "${DIFF1USER}" --pwd2 "${DIFF1PASS}" --database2 ${DIFF1DB}
        else
            ${DIFFCMD} -- ${DIFFARGS} \
                --host1 ${DIFF1HOST} --user1 "${DIFF1USER}" --pwd1 "${DIFF1PASS}" --database1 ${DIFF1DB} \
                --host2 ${DIFF2HOST} --user2 "${DIFF2USER}" --pwd2 "${DIFF2PASS}" --database2 ${DIFF2DB}
        fi
        ;;

    L)
        OVERRIDE_FILE=config_override.php
        if [ -f $OVERRIDE_FILE ]; then
            shift $(($OPTIND - 1))
            ERROR_LEVEL=$1
            dolog "Setting error level to: ${ERROR_LEVEL}"
            cp $OVERRIDE_FILE ${OVERRIDE_FILE}.bak
            sed -i'' -re "s/(\['level'\] =).*/\1 '${ERROR_LEVEL}';/" $OVERRIDE_FILE
            if $VERBOSE; then diff -dU0 ${OVERRIDE_FILE}.bak $OVERRIDE_FILE | tail -3; fi
        else
            dolog "Unable to find ${OVERRIDE_FILE}."
        fi
        rm ${OVERRIDE_FILE}.bak
        ;;

    *)  usage -1 ;;
esac

exit 0