#!/bin/bash
# Script for optimized creation of a new "IDX_item_source_folder_id" index for webclient database table item. 
# Run before the upgrade to 13.0.2.x. No maintenance window needed, running outside the peak hours is recommended.

#vars
dbHost="${1}";
dbUser="${2}";
dbPass="${3}";
dbPort="${4}";
webcDbName="${5}";
mysqlConnString="-u${dbUser} -p${dbPass} -h${dbHost} -P${dbPort}";
webcDbBackupFile="/root/bck_db_wc_${webcDbName}`date +%Y%m%d-%H%M`.sql.gz";
maxRows=2000000; # maximal rows count for AlterLow method

#func
ItemRows() { # ( returns number of rows in webclient item table )
local rowsCount=$(echo "select count(*) from ${webcDbName}.item;" | mysql ${mysqlConnString} | tail -1);
local re='^[0-9]+$'
if ! [[ $rowsCount =~ $re ]] 
   then
     echo "error: Failed to get webclient item table rows count." >&2
     exit 1
   else
     echo "${rowsCount}"
     return 0
fi
}

### performs backup of the webclient database to the current directory
DbBackup() {
/usr/bin/mysqldump --single-transaction ${mysqlConnString} ${webcDbName} | gzip -c | cat > ${webcDbBackupFile}
}

### alter method for rowsCount < maxRows
AlterLow() {
mysql ${mysqlConnString} -ANe "USE ${webcDbName};CREATE TABLE item2 like item;CREATE INDEX IDX_item_source_folder_id ON item2 (source_folder_id,flag_update);INSERT INTO item2 SELECT * FROM item;RENAME TABLE item TO itembak01;RENAME TABLE item2 TO item;"
mysql ${mysqlConnString} -ANe "USE ${webcDbName};DROP TABLE itembak01;"
}

### alter method for rowsCount
AlterHigh() {
mysql ${mysqlConnString} -ANe "CREATE DATABASE IF NOT EXISTS ${webcDbName}2;CREATE TABLE ${webcDbName}2.item like ${webcDbName}.item;CREATE INDEX IDX_item_source_folder_id ON ${webcDbName}2.item (source_folder_id,flag_update);"
mysqldump ${mysqlConnString} --single-transaction --no-create-info ${webcDbName} item | mysql ${mysqlConnString} -D${webcDbName}2
mysql ${mysqlConnString} -ANe "INSERT INTO ${webcDbName}2.item SELECT * FROM ${webcDbName}.item where item_id > ( select max(item_id) from ${webcDbName}2.item );RENAME TABLE ${webcDbName}.item TO ${webcDbName}.itembak01;RENAME TABLE ${webcDbName}2.item TO ${webcDbName}.item;"
mysql ${mysqlConnString} -ANe "USE ${webcDbName};DROP TABLE itembak01;"
mysql ${mysqlConnString} -ANe "DROP DATABASE ${webcDbName}2;"
}

PrintUsage() {
echo -e "Script for optimized creation of a new \x27IDX_item_source_folder_id\x27 index for webclient database table item."
echo "Running is recommended before upgrade to IceWarp 13.0.2.x (upgdate 2) on bigger instances ( ~1000+ users )."
echo "Running this task before upgrade should not have any significant performance impact, it is recommended to run it outside the peak hours though."
echo
echo "Please specify parameters. 1: Database hostname / IP; 2: Database username; 3: Database password; 4: Database port; 5: Webclient database name;"
echo "Example: ./addwebcindex.sh 127.0.0.1 root rootpass 3306 webclient"
}

TestConn() {
mysql ${mysqlConnString} -e"quit"
}

#main
### print usage if any stdin variable is empty or help requested
if [[ "${1}" == "help" || "${1}" == "--help" ]]
  then
    PrintUsage
    exit 1
fi
if [[ -z ${1} || -z ${2} || -z ${3} || -z ${4} || -z ${5} ]];
  then
    PrintUsage
    exit 1
fi

### preflight check ( connection test, DB backup )
echo "Testing connection to the webclient database: ${webcDbName} on DB server: ${dbHost}, port:${dbPort} with user: ${dbUser}."
TestDBconn=$(TestConn)
if [ $? -eq 0 ]
  then
    echo "Test connection was sucesfull."
  else
    echo "Unable to proceed test connection to MySQL"  
	exit 1
fi

### check the new key is not yet created on the item table
echo "use ${webcDbName};show create table item\G" | mysql ${mysqlConnString} | grep "IDX_item_source_folder_id" > /dev/null 2>&1
if [[ $? -eq 0 ]]
  then
    echo "IDX_item_source_folder_id already created on ${webcDbName}.item. Exiting without changing the database."
    exit 1
fi

echo "Performing backup of ${webcDbName} database."
DbBackup;
sizeK=$(du -k ${webcDbBackupFile} | awk '{print $1}');
if [[ ${sizeK} -lt 5 ]]
  then
    echo "Backup file ${webcDbBackupFile} size lower than 5K ( ${sizeK}KB ), please check the DB connection paramaters. Exiting without chaging the database..";
    exit 1
  else
    echo "Backup file ${webcDbBackupFile} size ${sizeK}KB, OK"
fi

### get number of rows in webclient item table and use apropriate alter method
rowsCount=$(ItemRows);
if [[ ${rowsCount} -gt ${maxRows} ]]
  then
    echo "Webclient item table rows count = ${rowsCount} which is > 2M, altering using piped mysqldump."
    AlterHigh
  else
    echo "Webclient item table rows count = ${rowsCount} which is <= 2M, altering using table recreate."
    AlterLow
fi
echo "Job done."
exit 0

