#!/bin/sh
 
# Abort on any errors
# set -e
 
# Script created 2010-01-15 by Tommy Evans (hostbunch.com) to dump a production database and import it into a test database
 
#############################################
###### Configure the below variables ########
#############################################
# The domain in which to copy files from.
export SRCDOMAIN="domain.com"
 
# The domain in which to copy files to.
export DESTDOMAIN="test.domain.com"
 
#The source SQL database
export SRCSQL="livesql.domain.com"
export SRCUSER="dbuser"
export SRCPSSWD="userpsswd"
export SRCDB="sitedb"
 
#The destination SQL database
export DESTSQL="testsql.domain.com"
export DESTUSER="testuser"
export DESTPSSWD="testpsswd"
export DESTDB="testdb"
 
## Modify the below for any custom SQL you wish to add. It will go at the bottom of this script
export CUSTOMSQL1="UPDATE config SET conf_value = 'http://${DESTDOMAIN}' WHERE conf_name = 'o_base_url';"
export CUSTOMSQL2="UPDATE config SET conf_value = 'http://${DESTDOMAIN}' WHERE conf_name = 'o_base_url_alt';"
export CUSTOMSQL3="TRUNCATE search_cache;"
export CUSTOMSQL4="TRUNCATE search_matches;"
export CUSTOMSQL5="TRUNCATE search_words;"
#############################################
###### End of configured variables ########
#############################################
 
echo "Delete all listed folders in ${DESTDOMAIN}"
#You can change these folders as you please
cd ${HOME}
mv ${DESTDOMAIN}/config.php ${DESTDOMAIN}/config2.php
mv ${DESTDOMAIN}/.htaccess ${DESTDOMAIN}/keep.htaccess
rm -dfr ${DESTDOMAIN}/attachments
rm -dfr ${DESTDOMAIN}/calendar
rm -dfr ${DESTDOMAIN}/cron
rm -dfr ${DESTDOMAIN}/images
rm -dfr ${DESTDOMAIN}/img
rm -dfr ${DESTDOMAIN}/include
rm -dfr ${DESTDOMAIN}/lang
rm -dfr ${DESTDOMAIN}/log
rm -dfr ${DESTDOMAIN}/plugins
rm -dfr ${DESTDOMAIN}/style
rm -dfr ${DESTDOMAIN}/uploads
rm -dfr ${DESTDOMAIN}/cache
 
echo "Copy listed folders from ${SRCDOMAIN} to ${DESTDOMAIN}"
cp -fR ${SRCDOMAIN}/attachments ${DESTDOMAIN}/attachments
cp -fR ${SRCDOMAIN}/calendar ${DESTDOMAIN}/calendar
cp -fR ${SRCDOMAIN}/cron ${DESTDOMAIN}/cron
cp -fR ${SRCDOMAIN}/images ${DESTDOMAIN}/images
cp -fR ${SRCDOMAIN}/img ${DESTDOMAIN}/img
cp -fR ${SRCDOMAIN}/include ${DESTDOMAIN}/include
cp -fR ${SRCDOMAIN}/lang ${DESTDOMAIN}/lang
cp -fR ${SRCDOMAIN}/log ${DESTDOMAIN}/log
cp -fR ${SRCDOMAIN}/plugins ${DESTDOMAIN}/plugins
cp -fR ${SRCDOMAIN}/style ${DESTDOMAIN}/style
cp -fR ${SRCDOMAIN}/uploads ${DESTDOMAIN}/uploads
 
echo "Create cache folder in ${DESTDOMAIN}"
cd ${HOME}/${DESTDOMAIN}
mkdir cache
chmod 0755 cache
 
echo "Copy all root files from ${SRCDOMAIN} to ${DESTDOMAIN}"
cd ${HOME}
cp -uf ${SRCDOMAIN}/* ${DESTDOMAIN}/
cp -f ${SRCDOMAIN}/.htaccess ${DESTDOMAIN}/.htaccess
 
#remove unneeded files
echo "Removing unneeded root files in ${DESTDOMAIN}"
rm -f ${DESTDOMAIN}/0
rm -f ${DESTDOMAIN}/core
rm -f ${DESTDOMAIN}/Web.config
 
#replace config.php and .htaccess
echo "Renaming saved config.php and .htaccess in ${DESTDOMAIN}"
mv -f ${DESTDOMAIN}/config2.php ${DESTDOMAIN}/config.php
#mv -fv ${DESTDOMAIN}/keep.htaccess ${DESTDOMAIN}/.htaccess
 
#clean up ${SRCDB} before the dump
echo "Cleaning up ${SRCSQL}"
mysqlcheck --auto-repair -s -u${SRCUSER} -p${SRCPSSWD} -h ${SRCSQL} -B ${SRCDB}
 
echo "Dumping ${SRCSQL} to ${DESTSQL}"
#dump ${SRCDB} to ${DESTDB} and a few extra steps to ensure subject on index work
mysqldump -u${SRCUSER} -p${SRCPSSWD} -h ${SRCSQL} --opt -f -n ${SRCDB} | mysql -u${DESTUSER} -p${DESTPSSWD} -h ${DESTSQL} -D ${DESTDB}
 
echo "Updating ${DESTSQL} with new URL info and truncating search tables"
 
### Custom SQL is here
mysql -f -n -u${DESTUSER} -p${DESTPSSWD} -h ${DESTSQL} -D ${DESTDB} -e "${CUSTOMSQL1}${CUSTOMSQL2}${CUSTOMSQL3}${CUSTOMSQL4}${CUSTOMSQL5}"