migrate-data.sh
#!/bin/bash
# Migrate any data through a series of dashboard versions (the dashboard zips must be in order of migration)
# bash migrate-data.sh --hsql /path/to/data database_password version1.zip version2.zip version3.zip version4.zip
#
function log() {
echo "[+] $1"
}
function usage() {
echo "migrate-data.sh"
echo " A tool to migrate data across intermediary versions."
echo " The last parameters must be a list of dashboard zip files in order of the upgrade. E.g. 2019_01_31.zip 2019_07_31 2020_01_30.zip."
echo ""
echo "Usage:"
echo " migrate-data.sh --help"
echo " migrate-data.sh [--hsql|--mariadb] <path_to_data> <database_password> <first_zip> [other_zips...] <last_zip>"
echo ""
echo "Parameters:"
echo " path_to_data"
echo " Path to the data."
echo " If used with --hsql it must point to a folder containing the dashboard.* files."
echo " If used with --mariadb it must point to a *.sql sqldump file."
echo " database_password"
echo " The current database password."
echo " first_zip"
echo " The first zip of a dashboard version, should be the oldest"
echo " other_zips"
echo " The intermediate versions required between first_zip and last_zip. These should be in order."
echo " last_zip"
echo " The final version to upgrade to."
echo ""
echo "Options:"
echo " --hsql"
echo " Signal the data as being in HyperSQL. This will not migrate it to MariaDB."
echo " --mariadb"
echo " Signal the data as being in MariaDB."
echo ""
echo "Example:"
echo " ./migrate-data.sh --hsql ./data-backup SuperSecretPassword 2019_01_31.zip 2019_07_31.zip 2020_01_30.zip 2020_07_30.zip 2021_01_28.zip"
}
DB_TYPE=$1
if [[ "${DB_TYPE}" == "--help" ]]; then
usage
exit 0
fi
if [[ "${DB_TYPE}" == "" ]]; then
usage
log "No database type provided."
exit 1
fi
if [[ "${DB_TYPE}" != "--hsql" ]] && [[ "${DB_TYPE}" != "--mariadb" ]]; then
usage
log "Supported database types are --hsql and --mariadb."
exit 1
fi
shift
PATH_TO_DATA=$1
if [[ "${PATH_TO_DATA}" == "" ]]; then
usage
log "No path to data provided."
exit 1
fi
shift
DB_PASSWORD=$1
shift
ZIPS="$@"
if [[ ${#ALL_ZIPS[@]} < 2 ]];then
usage
log "You at least two versions!"
exit 1
fi
DB_FOLDER="data"
if [[ "${DB_TYPE}" == "--mariadb" ]]; then
DB_FOLDER="db"
fi
function run_sql() {
local DASHBOARD_PATH="$1"
local CURRENT_PW="$2"
local SQL="$3"
local MARIA_SQL="$4"
if [[ "${DB_TYPE}" == "--hsql" ]]; then
pushd $DASHBOARD_PATH/Dashboard/data > /dev/null 2>&1
java -jar sqltool.jar --inlineRc="url=jdbc:hsqldb:hsql://localhost:9910/dashboard;user=sa;password=${CURRENT_PW}" --sql="${SQL};commit;"
popd
fi
if [[ "${MARIA_SQL}" == "" ]]; then
MARIA_SQL="${SQL}"
fi
if [[ "${DB_TYPE}" == "--mariadb" ]]; then
pushd $DASHBOARD_PATH/Dashboard/db > /dev/null 2>&1
./bin/mysql --defaults-file=my.cnf -uroot -p${CURRENT_PW} --execute="${MARIA_SQL};"
popd
fi
}
function change_password_to_default() {
# We don't need to do any password change on mariadb
local DASHBOARD_PATH="$1"
if [[ "${DB_TYPE}" == "--hsql" ]]; then
log "Removing DB password temporarily"
run_sql "${DASHBOARD_PATH}" "${DB_PASSWORD}" "SET PASSWORD ''"
fi
}
function change_password_from_default() {
# We don't need to do any password change on mariadb
local DASHBOARD_PATH="$1"
if [[ "${DB_TYPE}" == "--hsql" ]]; then
log "Resetting the DB password back to what it was"
run_sql "${DASHBOARD_PATH}" "" "SET PASSWORD '${DB_PASSWORD}'"
fi
}
function unzip_dashboard() {
local ZIP_FILE=$1
local NEW_DIR=$2
unzip $ZIP_FILE -d $NEW_DIR > /dev/null 2>&1
chmod 777 $NEW_DIR/Dashboard/${DB_FOLDER}/*.sh
chmod 777 $NEW_DIR/Dashboard/tomcat/bin/*.sh
}
function migrate_from_to() {
local FROM_ZIP=$1
local TO_ZIP=$2
local FROM_VERSION=$(echo "${FROM_ZIP/pi./}" | tr '-' ' ' | awk '{ print $3 }') # extract the version
local TO_VERSION=$(echo "${TO_ZIP/pi./}" | tr '-' ' ' | awk '{ print $3 }') # extract the version
# We don't really care about the name, just that they're different
if [[ "$FROM_VERSION" == "$TO_VERSION" ]]; then
TO_VERSION="${TO_VERSION}_to"
fi
if [[ ! -d $FROM_VERSION ]]; then
log "$FROM_VERSION was not found."
exit 1
fi
unzip_dashboard $TO_ZIP $TO_VERSION
if [[ ! -d $TO_VERSION ]]; then
log "$FROM_VERSION was not found."
exit 1
fi
log "Migrating from $FROM_VERSION to $TO_VERSION"
$TO_VERSION/Dashboard/upgrade_dashboard.sh $FROM_VERSION/Dashboard/ > ~/logs/migration.$FROM_VERSION.to.$TO_VERSION.upgrade.output 2>&1
stop_dashboard $TO_VERSION
start_dashboard $TO_VERSION
stop_dashboard $TO_VERSION
log "Finished migrating from $FROM_VERSION to $TO_VERSION"
log "Removing $FROM_VERSION"
rm -rf $FROM_VERSION
}
function start_dashboard() {
LOCATION=$1
pushd $LOCATION/Dashboard > /dev/null 2>&1
./dashboard.sh all start > ~/logs/migration.$LOCATION.output 2>&1
log "Waiting for $LOCATION dashboard to start..."
sleep 5
tries=0
while [[ $(curl -s -o /dev/null -w"%{http_code}" http://localhost:8224/pi/) != "200" ]]; do
sleep 3
tries=$(($tries+1))
if [[ $tries -gt 10 ]]; then
log "Gave up starting dashboard at $LOCATION"
exit 1
fi
done
log "$LOCATION dashboard is started. Data is migrated"
popd > /dev/null 2>&1
}
function stop_dashboard() {
LOCATION=$1
pushd $LOCATION/Dashboard > /dev/null 2>&1
./dashboard.sh all stop > ~/logs/migration.$LOCATION.output 2>&1
log "Waiting for $LOCATION dashboard to stop..."
tries=0
while [[ $(ps aux | grep java | grep catalina) != "" ]]; do
sleep 5
kill $(ps aux | grep java | grep catalina | awk '{ print $2 }') # kill tomcat
kill $(ps aux | grep java | grep hsql | awk '{ print $2 }') # kill hypersql
kill $(ps aux | grep excel | awk '{ print $2 }') # kill excel reader
kill $(ps aux | grep renderer | awk '{ print $2 }') # kill renderer
tries=$(($tries+1))
if [[ $tries -gt 10 ]]; then
log "Gave up stopping dashboard at $LOCATION"
exit 1
fi
done
log "$LOCATION dashboard is stopped"
popd > /dev/null 2>&1
}
function prepare_initial_data() {
local DATA_PATH=$1
local DB_TYPE=$2
local DB_PASSWORD=$3
local INITIAL_ZIP=$4
local INITIAL_VERSION=$(echo "${INITIAL_ZIP/pi./}" | tr '-' ' ' | awk '{ print $3 }') # extract the version
log "Preparing initial data in $DATA_PATH by loading it in $INITIAL_VERSION"
unzip_dashboard $INITIAL_ZIP $INITIAL_VERSION
log "Copying licence.xml to $INITIAL_VERSION"
cp licence.xml $INITIAL_VERSION/Dashboard/tomcat/webapps/panLicenceManager/WEB-INF/classes/
if [[ "${DB_TYPE}" == "--hsql" ]]; then
rm -f $INITIAL_VERSION/Dashboard/data/dashboard.*
cp $DATA_PATH/dashboard.* $INITIAL_VERSION/Dashboard/data
fi
log "Starting the database"
$INITIAL_VERSION/Dashboard/${DB_FOLDER}/dbup-repos.sh
sleep 2
change_password_to_default "${LAST_DASHBOARD_PATH}"
if [[ "${DB_TYPE}" == "--mariadb" ]]; then
$INITIAL_VERSION/Dashboard/db/dbup-repos.sh
fi
log "Stopping the database"
$INITIAL_VERSION/Dashboard/${DB_FOLDER}/dbstop-repos.sh
start_dashboard $INITIAL_VERSION
stop_dashboard $INITIAL_VERSION
log "Finished preparing the initial dashboard ($INITIAL_VERSION) with the data from $DATA_PATH"
}
function migrate() {
local DATA_PATH="$1"
shift
local ALL_ZIPS=("$@")
# We need to place the data in the first dashboard and ensure it is migrated
prepare_initial_data $DATA_PATH $DB_TYPE $DB_PASSWORD ${ALL_ZIPS[0]}
# Now progress the data through the versions, starting from the initial to the very last one
local LAST_FROM_ZIP_INDEX=$((${#ALL_ZIPS[@]}-2))
for i in $(seq 0 $LAST_FROM_ZIP_INDEX); do
local NEXT_i=$(($i+1))
# Migrate from index to index+1
migrate_from_to ${ALL_ZIPS[$i]} ${ALL_ZIPS[$NEXT_i]}
done
local LAST_TO_ZIP_INDEX=$(($LAST_FROM_ZIP_INDEX+1))
local LAST_DASHBOARD_PATH=${ALL_ZIPS[$LAST_TO_ZIP_INDEX]}
pushd ${LAST_DASHBOARD_PATH}/Dashboard/${DB_FOLDER} > /dev/null 2>&1
log "Starting the database"
./dbup-repos.sh
sleep 2
log "Ensuring migration of IN filters"
run_sql "${LAST_DASHBOARD_PATH}" "" "UPDATE mis_user_restrictions SET VALUE = REPLACE(VALUE, ',', '::::') WHERE VALUE NOT LIKE '%::::%' AND VALUE LIKE '%,%';
UPDATE mis_filter_columns SET OPERAND_ONE = REPLACE(OPERAND_ONE, ',', '::::') WHERE OPERATOR_ID in (4, 13) and OPERAND_ONE NOT LIKE '%::::%' and OPERAND_ONE LIKE '%,%';
UPDATE mis_chart_columns SET OPERAND_ONE = REPLACE(OPERAND_ONE, ',', '::::') WHERE OPERATOR_ID in (4, 13) and OPERAND_ONE NOT LIKE '%::::%' and OPERAND_ONE LIKE '%,%';
UPDATE mis_category_objects SET DEFAULT_VALUE = REPLACE(DEFAULT_VALUE, ',', '::::') WHERE DEFAULT_VALUE NOT LIKE '%::::%' AND DEFAULT_VALUE LIKE '%,%'"
log "Changing the password back to what it was"
change_password "${LAST_DASHBOARD_PATH}" "" "${DB_PASSWORD}"
log "Stopping the database"
./dbstop-repos.sh
popd > /dev/null 2>&1
log "Done"
}
mkdir -p ~/logs
migrate $PATH_TO_DATA ${ZIPS[@]}
NEWS: We are pleased to share that our April 24 dashboard release is now available to download, see HERE for more details