DB migrator fixes after smoke test

Issue-ID: POLICY-3999
Change-Id: I65d95cbfab4b49245bb68f2ca5067fda9437b531
Signed-off-by: adheli.tavares <adheli.tavares@est.tech>

1 files changed, 332 insertions, 0 deletions

diff --git a/policy-db-migrator/smoke-test/mariadb-tests.sh b/policy-db-migrator/smoke-test/mariadb-tests.sh
new file mode 100755
index 00000000..a5f1e31f
--- /dev/null
+++ b/policy-db-migrator/smoke-test/mariadb-tests.sh
@@ -0,0 +1,332 @@
+#!/bin/sh
+# ============LICENSE_START====================================================
+#  Copyright (C) 2022 Nordix Foundation.
+# =============================================================================
+# Licensed under the Apache License, Version 2.0 (the "License");
+# you may not use this file except in compliance with the License.
+# You may obtain a copy of the License at
+#
+#      http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+# SPDX-License-Identifier: Apache-2.0
+# ============LICENSE_END======================================================
+# shellcheck disable=SC2039
+# shellcheck disable=SC2086
+# shellcheck disable=SC2012
+echo "Start of test $(date +%F-%T)"
+
+export POLICY_HOME=/opt/app/policy
+export SQL_USER=policy_user
+export SQL_PASSWORD=policy_user
+export SQL_HOST=mariadb
+export MYSQL_ROOT_PASSWORD=secret
+export SCHEMA=policyadmin
+export SCRIPT_DIRECTORY=sql
+
+# Test variables
+TOTAL_COUNT=0
+HONOLULU_UPGRADE_COUNT=$(ls /home/policy/sql/0800/upgrade/*.sql | wc -l)
+HONOLULU_DOWNGRADE_COUNT=$(ls /home/policy/sql/0800/downgrade/*.sql | wc -l)
+ISTANBUL_UPGRADE_COUNT=$(ls /home/policy/sql/0900/upgrade/*.sql | wc -l)
+ISTANBUL_DOWNGRADE_COUNT=$(ls /home/policy/sql/0900/downgrade/*.sql | wc -l)
+JAKARTA_UPGRADE_COUNT=$(ls /home/policy/sql/1000/upgrade/*.sql | wc -l)
+JAKARTA_DOWNGRADE_COUNT=$(ls /home/policy/sql/1000/downgrade/*.sql | wc -l)
+
+NEW_SQL_EXECUTIONS=0
+START_VERSION=""
+PREVIOUS_SQL_EXECUTIONS=0
+END_VERSION=""
+RECENT_SQL_EXECUTIONS=0
+END_STATUS=0
+TEST_STATUS="FAIL"
+TEST_MSG=""
+TESTS=0
+PASSED=0
+FAILED=0
+
+# SQL statements
+PDPSTATISTICS="CREATE TABLE IF NOT EXISTS pdpstatistics ("
+PDPSTATISTICS=${PDPSTATISTICS}"PDPGROUPNAME VARCHAR(120) NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"PDPSUBGROUPNAME VARCHAR(120) NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDFAILCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"POLICYEXECUTEDSUCCESSCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS}"POLICYUNDEPLOYCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS2}"POLICYUNDEPLOYFAILCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS2}"POLICYUNDEPLOYSUCCESSCOUNT BIGINT DEFAULT NULL NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"timeStamp datetime NOT NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS2}"timeStamp datetime DEFAULT NULL NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS2}"ID BIGINT NOT NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"name VARCHAR(120) NOT NULL, "
+PDPSTATISTICS2=${PDPSTATISTICS2}"name VARCHAR(120) NOT NULL, "
+PDPSTATISTICS=${PDPSTATISTICS}"version VARCHAR(20) NOT NULL,"
+PDPSTATISTICS2=${PDPSTATISTICS2}"version VARCHAR(20) NOT NULL,"
+PDPSTATISTICS=${PDPSTATISTICS}"CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (timeStamp, name, version));"
+PDPSTATISTICS2=${PDPSTATISTICS2}"CONSTRAINT PK_PDPSTATISTICS PRIMARY KEY (ID, name, version));"
+
+IDX_TSIDX1="CREATE INDEX IDX_TSIDX1 ON pdpstatistics(timeStamp, name, version);"
+INSERT="INSERT INTO pdpstatistics(PDPGROUPNAME,PDPSUBGROUPNAME,POLICYDEPLOYCOUNT,POLICYDEPLOYFAILCOUNT,POLICYDEPLOYSUCCESSCOUNT,"
+INSERT1="${INSERT}""POLICYEXECUTEDCOUNT,POLICYEXECUTEDFAILCOUNT,POLICYEXECUTEDSUCCESSCOUNT,timeStamp,name,version)"
+INSERT2="${INSERT}""POLICYEXECUTEDCOUNT,POLICYEXECUTEDFAILCOUNT,POLICYEXECUTEDSUCCESSCOUNT,POLICYUNDEPLOYCOUNT,"
+INSERT2="${INSERT2}""POLICYUNDEPLOYFAILCOUNT,POLICYUNDEPLOYSUCCESSCOUNT,timeStamp,ID,name,version)"
+SQL1="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test1', '1.0')"
+SQL2="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test1', '1.0')"
+SQL3="${INSERT2}"" values('groupname', 'subgroup',1,1,1,1,1,1,1,1,1,now(),3,'test1', '1.0')"
+SQL4="${INSERT2}"" values('groupname', 'subgroup',1,1,1,1,1,1,1,1,1,now(),4,'test1', '1.0')"
+SQL5="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test2', '1.0')"
+SQL6="${INSERT1}"" values('groupname', 'subgroup',1,1,1,1,1,1,now(),'test2', '1.0')"
+
+run_sql() {
+  local user="${1}" password="${2}" schema="${3}" sql="${4}"
+  MYSQL="mysql -u${user} -p${password} -h ${SQL_HOST} ${schema}"
+  ${MYSQL} --execute "${sql}"
+  return $?
+}
+
+start_test() {
+  echo ""
+  echo "############################################################################################################"
+  echo ""
+  let TESTS=$TESTS+1
+  reportStatus=$(/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report | tail -2)
+  echo "Starting test $TESTS"
+  echo "$reportStatus"
+  START_VERSION=$(echo $reportStatus | cut -f13 -d' ')
+  PREVIOUS_SQL_EXECUTIONS=$(echo $reportStatus | cut -f1 -d' ')
+
+  if [ "${START_VERSION}" == "" ]; then
+    START_VERSION="0"
+    PREVIOUS_SQL_EXECUTIONS=0
+  fi
+}
+
+end_test() {
+  reportStatus=$(/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o report | tail -2)
+  echo "Ending test $TESTS"
+  echo "$reportStatus"
+  END_VERSION=$(echo $reportStatus | cut -f13 -d' ')
+  RECENT_SQL_EXECUTIONS=$(echo $reportStatus | cut -f1 -d' ')
+  END_STATUS=$(echo $reportStatus | cut -f7 -d' ')
+}
+
+check_results() {
+  local status="${1}" operation="${2}" startVer="${3}" endVer="${4}" newRecords="${5}" filesRan="${6}"
+
+  echo ""
+  echo "Test summary: status:$status, operation:$operation, from:$startVer, to:$endVer, new executions:$newRecords, sqlFiles:$filesRan"
+  # Convert to number
+  startVer=$(echo ${startVer} | awk '{$0=int($0)}1')
+  endVer=$(echo ${endVer} | awk '{$0=int($0)}1')
+
+  if [ ${startVer} -eq ${endVer} ] && [ $newRecords -eq $filesRan ] && [ $newRecords -eq 0 ]; then
+    TEST_MSG="No ${operation} required"
+    TEST_STATUS="PASS"
+    let PASSED=$PASSED+1
+  elif [ $status -eq 1 ] && [ "${operation}" == "upgrade" ] && [ ${startVer} -le ${endVer} ] && [ $newRecords -eq $filesRan ]; then
+    TEST_MSG="Successful ${operation}"
+    TEST_STATUS="PASS"
+    let PASSED=$PASSED+1
+  elif [ $status -eq 1 ] && [ "${operation}" == "downgrade" ] && [ ${startVer} -ge ${endVer} ] && [ $newRecords -eq $filesRan ]; then
+    TEST_MSG="Successful ${operation}"
+    TEST_STATUS="PASS"
+    let PASSED=$PASSED+1
+  else
+    TEST_MSG="Errors occurred during ${operation}"
+    TEST_STATUS="FAIL"
+    let FAILED=$FAILED+1
+  fi
+  echo ""
+  echo "*** Test $TESTS: $TEST_STATUS , $TEST_MSG, current version: $END_VERSION ***"
+  echo ""
+}
+
+# Test 1 - Upgrade to Istanbul
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+start_test
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -t 0900
+end_test
+let TOTAL_COUNT=$HONOLULU_UPGRADE_COUNT+$ISTANBUL_UPGRADE_COUNT
+check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" $RECENT_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test 2 - downgrade to 0800
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $ISTANBUL_DOWNGRADE_COUNT
+
+sleep 5
+
+# Test 3 - upgrade to 0900
+start_test
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $ISTANBUL_UPGRADE_COUNT
+
+sleep 5
+
+# Test4 - run upgrade on db where tables already exist and migration schema is empty
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+run_sql "root" "${MYSQL_ROOT_PASSWORD}" "${SCHEMA}" "DROP DATABASE IF EXISTS migration;"
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -t 0900
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS
+check_results $END_STATUS 'upgrade' "0800" "${END_VERSION}" $NEW_SQL_EXECUTIONS $ISTANBUL_UPGRADE_COUNT
+
+sleep 5
+
+# Test5  - upgrade after failed downgrade
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS2}"
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${IDX_TSIDX1}"
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+# (files run before error * 2) + 1 to run the file again
+let TOTAL_COUNT=11
+check_results $END_STATUS 'upgrade' "0800" "0900" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test6 - Downgrade after failed downgrade
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS2}"
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${IDX_TSIDX1}"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+# +1 to run the file again
+let TOTAL_COUNT=$ISTANBUL_DOWNGRADE_COUNT+1
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test7  - downgrade after failed upgrade
+start_test
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS}"
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+# (files run before error * 2) + 1 to run the file again
+let TOTAL_COUNT=3
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test 8 - Upgrade after failed upgrade
+start_test
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "DROP table pdpstatistics;"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${PDPSTATISTICS}"
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -t 0900
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+# +1 to run the file again
+let TOTAL_COUNT=$ISTANBUL_UPGRADE_COUNT+1
+check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test 9 - Upgrade when pdpstatistics contains data
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+start_test
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL1}"
+sleep 1
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL2}"
+sleep 1
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade -f 0800 -t 0900
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL3}"
+sleep 1
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL4}"
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+# +1 to run the file again
+let TOTAL_COUNT=$ISTANBUL_UPGRADE_COUNT
+check_results $END_STATUS 'upgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test 10 - downgrade to 0800 with records in pdpstatistics
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0900 -t 0800
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL5}"
+sleep 1
+run_sql "${SQL_USER}" "${SQL_PASSWORD}" "${SCHEMA}" "${SQL6}"
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $ISTANBUL_DOWNGRADE_COUNT
+
+sleep 5
+
+# Test 11 - downgrade from 0800
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 0800
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "${END_VERSION}" $NEW_SQL_EXECUTIONS $HONOLULU_DOWNGRADE_COUNT
+
+sleep 5
+
+# Test 12 - Full upgrade
+start_test
+/opt/app/policy/bin/prepare_upgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o upgrade
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+let TOTAL_COUNT=$HONOLULU_UPGRADE_COUNT+$ISTANBUL_UPGRADE_COUNT+$JAKARTA_UPGRADE_COUNT
+check_results $END_STATUS 'upgrade' "0" "${END_VERSION}" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+sleep 5
+
+# Test 13 - Full downgrade
+start_test
+/opt/app/policy/bin/prepare_downgrade.sh ${SQL_DB}
+/opt/app/policy/bin/db-migrator -s ${SQL_DB} -o downgrade -f 1000 -t 0
+end_test
+let NEW_SQL_EXECUTIONS=$RECENT_SQL_EXECUTIONS-$PREVIOUS_SQL_EXECUTIONS
+let TOTAL_COUNT=$HONOLULU_DOWNGRADE_COUNT+$ISTANBUL_DOWNGRADE_COUNT+$JAKARTA_DOWNGRADE_COUNT
+check_results $END_STATUS 'downgrade' "${START_VERSION}" "0" $NEW_SQL_EXECUTIONS $TOTAL_COUNT
+
+echo
+echo "-----------------------------------------------------------------------"
+echo "Number of Tests: $TESTS, Tests Passed: $PASSED, Tests Failed: $FAILED"
+echo "-----------------------------------------------------------------------"
+
+echo "End of test $(date +%F-%T)"
+
+nc -lk -p 6824
+
+exit 0