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
TINY=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
mkto_queue
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
metadata_cache
pardot_log
pardot_log_messages
it_sugarupdates
it_sugarupdates_sugar_installation_1_c
tracker
tracker_perf
tracker_queries
tracker_sessions
tracker_tracker_queries
EOF

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

activities
activities_users
activity_logs
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
metadata_cache
mkto_queue
notes
notes_cstm
pardot_log
pardot_log_messages
prospect_lists_prospects
prospects
session_history
it_sugarinstallations
it_sugarinstallations_cstm
it_sugarupdates
it_sugarupdates_sugar_installation_1_c
tracker
tracker_perf
tracker_queries
tracker_sessions
tracker_tracker_queries
EOF

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

a_case_reviews
a_case_reviews_cases_c
accounts
accounts_accounts_1_c
accounts_accounts_c
accounts_bugs
accounts_cases
accounts_cases_1_c
accounts_classes
accounts_contacts
accounts_contracts_1_c
accounts_cstm
accounts_fin_subscriptions_1_c
accounts_lgl_contributor_1_c
accounts_opportunities
accounts_sales_orders_1_c
accounts_subinfos
accounts_threads
activities
activities_users
activity_logs
address_book
address_book_list_items
address_book_lists
bm2_billings_module2_products_c
bugs
bugs_bugs_1_c
bugs_cstm
bugs_supp_escalations_1_c
bugs_threads
bugs_users_1_c
bugs_voting
bugs_voting_bugs_c
c_contributithb_commits_c
calls
calls_contacts
calls_cstm
calls_leads
calls_users
campaign_log
campaign_trkrs
cases
cases_bugs
cases_cases_1_c
cases_cstm
cases_kbdocuments
cases_kbdocuments_1_c
cases_threads
cases_users
cases_users_1_c
category_tree
certification_histories
ci_email_log
class_locations
classes_locations
comments
contacts
contacts_bugs
contacts_cases
contacts_cstm
contacts_locations
contacts_sales_orders_1_c
contacts_users
contract_types
contracts
contracts_contacts
contracts_cstm
contracts_opportunities
contracts_products
contracts_ps_projectteam_1_c
contracts_quotes
cp_customerile_accounts_c
cp_customerprofile
cp_customerprofile_cstm
cron_remove_documents
dashboards
data_sets
dataset_attributes
distgroups
document_revisions
documents
documents_accounts
documents_bugs
documents_cases
documents_contacts
documents_cstm
documents_opportunities
documents_products
documents_quotes
documents_revenuelineitems
eapm
email_addr_bean_rel
email_addresses
email_cache
email_marketing
email_marketing_prospect_lists
email_templates
emailman
emailman_sent_
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
favorites
feeds
files
fin_addons
fin_addons_cstm
fin_billings
fin_billings_cstm
fin_netsuitesynclogs
fin_subscriptions
fin_subscriptions_cstm
fin_subscriptions_fin_addons_1_c
fin_subscriptions_opportunities_1_c
fin_subscriptions_sales_orders_1_c
fix_opps
folders_rel
folders_subscriptions
fonality_stats
fonuae_pbxsettings
forecast_manager_worksheets
forecast_schedule
forecast_tree
forecast_worksheets
forecasts
forums
forumtopics
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
githb_commis_itrequests_c
githb_commits
githb_commits_bugs_c
iframes
import_maps
inbound_email
inbound_email_autoreply
inbound_email_cache_ts
it_requests
it_requests_accounts_1_c
it_requests_bugs_1_c
it_requests_cases_1_c
it_requests_cstm
it_requests_it_requests_1_c
it_requests_users_1_c
it_sugarinstallations
it_sugarinstallations_accounts_1_c
it_sugarinstallations_cstm
it_sugarupdates
it_sugarupdates_sugar_installation_1_c
job_queue
kbcontents
kbdocument_revisions
kbdocuments
kbdocuments_cstm
kbdocuments_kbtags
kbdocuments_views_ratings
kbtags
leads
leads_archive
leads_cstm
leads_discrepancies
leads_leads
leads_locked
lgl_contribution
lgl_contribution_bugs_c
lgl_contribution_cstm
lgl_contributor
lgl_contributor_lgl_contribution_c
lgl_contributor_notes_c
linked_documents
manufacturers
meetings
meetings_contacts
meetings_leads
meetings_users
metadata_cache
mkto_queue
notes
notes_cstm
opportunities
opportunities_contacts
opportunities_cstm
opportunities_products
opportunities_threads
opps_opps
outbound_email
p_product_groductgroups_c
p_product_group_product_options
p_product_group_product_options_cstm
p_product_options
p_product_oucttemplates_c
pardot_log
pardot_log_messages
pd_pardot
posts
products_p_duct_options_c
producttemplates_contracts_1_c
producttemproductgroups_c
programs
programs_campaigns
project
project_cstm
project_relation
project_resources
project_task
project_threads
projects_accounts
projects_bugs
projects_cases
projects_contacts
projects_opportunities
projects_products
projects_quotes
projects_revenue_line_items
prospect_list_campaigns
prospect_lists
prospect_lists_prospects
prospects
ps_projectteam
ps_projectteam_cstm
ps_projectteam_tasks_1_c
ps_timesheets
pspt_project_team
query_calcs
query_columns
query_filters
query_groupbys
quotas
quotes
quotes_accounts
quotes_contacts
quotes_cstm
quotes_opportunities
record_list
report_cache
revenue_line_items
revenue_line_items_cstm
revenuelineitems_contracts_1_c
roles
roles_modules
roles_users
sales_orders
sales_orders_contracts_1_c
sales_orders_cstm
sales_orders_documents_1_c
sales_orders_opportunities_1_c
sales_orders_revenuelineitems_1_c
saved_reports
saved_search
session_active
session_history
sgr_addons_duct_options_c
sgr_salesstates
shippers
students_classes
students_courses
subinfos
subscriptions
subscriptions_distgroups
subscriptions_orders
subscriptions_portalusers
sugar_hb_stats
sugarfavorites
sugarfeed
supp_calendar
supp_calendar_users_1_c
supp_calendar_users_2_c
supp_calendar_users_c
supp_developertasks
supp_developertasks_bugs_c
supp_developertasks_cases_c
supp_developertasks_cstm
supp_developertasks_notes_1_c
supp_developertasks_users_c
supp_escalations
supp_escalations_cases_1_c
supp_escalations_cstm
supp_escalations_users_1_c
supp_hotfixes
supp_hotfixes_bugs_c
supp_hotfixes_cstm
supp_hotfixes_supp_developertasks_c
supp_roundrobin
supp_shifts
supp_shifts_users_c
supp_surveyresponse
supp_surveyresponse_cstm
supp_teams
supp_teams_supp_calendar_1_c
supp_teams_supp_calendar_c
supp_teams_supp_teams_c
supp_teams_users_c
tasks
tasks_cstm
tasks_notes_c
tasks_ps_timesheets_1_c
tasks_users_1_c
tasks_users_c
threads
timeperiods
tracker
tracker_perf
tracker_queries
tracker_sessions
tracker_tracker_queries
users_last_import
users_ps_projectteam_1_c
users_pspt_project_team_3_c
vcals
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/Import         [FULLER:${FULLER}]
    -t  Tiny (metadata only) Export/Import  [TINY:${TINY}]

Notes:
    The full (-f) export can be very large.  Use it carefully.

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
    elif $TINY; then
        if [[ "${SCHEMA_TINY}" =~ $RE ]]; then
            return 0
        elif [[ $1 =~ _audit ]]; 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:qvrftc"
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; TINY=false  ;;
        t) TINY=true; FULLER=false  ;;
        ?) 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
        elif $TINY; then
            CDIR=${CDIR}/${APPNAME}-tiny
        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
            if [[ $table == si_inf_* ]]; then
                continue
            fi
            TYPE="full"
            DUMPOPTS_TMP="$DUMPOPTS"
            if is_schema_only $table; then
                TYPE="schema"
                DUMPOPTS_TMP="$DUMPOPTS --no-data"
            fi
            FILE="${CDIR}/${TYPE}/${table}.sql.bz2"
            dolog "Exporting ${DBUSER}@${HOST}:${DATABASE}.${table} to ${FILE}"
            $DUMP ${MYSQL_AUTH} ${DUMPOPTS_TMP} ${DATABASE} ${table} | bzip2 -c > ${FILE}
        done

        say "Export done."
        ;;

    # Import Mode
    I)
        FIND_OPTS=""
        if $FULLER; then
            CDIR=${CDIR}/${APPNAME}-full
        elif $TINY; then
            CDIR=${CDIR}/${APPNAME}-tiny
        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.bz2"`; do
            dolog "Importing ${FILE} to ${DBUSER}@${HOST}:${DATABASE}.${table}"
            bzip2 -dc ${FILE} | $MYSQL ${MYSQL_AUTH} ${DATABASE}
        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
        elif $TINY; then
            ARCHIVE=${ARCHIVE}/${APPNAME}-tiny
        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