diff options
Diffstat (limited to 'policy-db-migrator/smoke-test/mariadb-tests.sh')
| -rwxr-xr-x | policy-db-migrator/smoke-test/mariadb-tests.sh | 332 |
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 |