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[@]}