Modified db-migrator to allow configuration of database

- Updated the Dockerfile to include postgres sql
and to copy postgres scripts
- Added db-migrator-pg script for postgres installation
- Updated prepare upgrade/downgrade scripts

Issue-ID: POLICY-2715
Change-Id: Ib8d3523e8445ad1528fe225c96acdac5ca61535d
Signed-off-by: Wayne Dunican <wayne.dunican@est.tech>

4 files changed, 806 insertions, 14 deletions

diff --git a/policy-db-migrator/src/main/docker/Dockerfile b/policy-db-migrator/src/main/docker/Dockerfile
index 85e7e319..0fbbdb3c 100644
--- a/policy-db-migrator/src/main/docker/Dockerfile
+++ b/policy-db-migrator/src/main/docker/Dockerfile
@@ -1,7 +1,7 @@
 #-------------------------------------------------------------------------------
 # Dockerfile
 # ============LICENSE_START=======================================================
-#  Copyright (C) 2021 Nordix Foundation.
+#  Copyright (C) 2021-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.
@@ -28,16 +28,19 @@ ENV POLICY_PROFILE /opt/app/policy/etc/profile.d
 ENV POLICY_BIN /opt/app/policy/bin
 
 RUN apk update && \
-    apk add --no-cache mariadb-client \
+    apk add --no-cache mariadb-client && \
+    apk add postgresql-client \
     net-tools netcat-openbsd sudo less vim && \
     mkdir -p $POLICY_PROFILE $POLICY_BIN && \
     chown -R policy:policy $POLICY_ETC $POLICY_BIN
 
 COPY --chown=policy:policy ./env.sh $POLICY_PROFILE/
 COPY --chown=policy:policy ./db-migrator $POLICY_BIN/
+COPY --chown=policy:policy ./db-migrator-pg $POLICY_BIN/
 COPY --chown=policy:policy ./prepare_upgrade.sh $POLICY_BIN/
 COPY --chown=policy:policy ./prepare_downgrade.sh $POLICY_BIN/
 COPY --chown=policy:policy ./config/policyadmin/sql /home/policy/sql
+COPY --chown=policy:policy ./config/policyadmin/postgres /home/policy/postgres
 
 WORKDIR $POLICY_BIN
 USER policy:policy
diff --git a/policy-db-migrator/src/main/docker/db-migrator-pg b/policy-db-migrator/src/main/docker/db-migrator-pg
new file mode 100755
index 00000000..a0ca2265
--- /dev/null
+++ b/policy-db-migrator/src/main/docker/db-migrator-pg
@@ -0,0 +1,789 @@
+#!/usr/bin/env sh
+
+# ============LICENSE_START=======================================================
+# ONAP
+# ================================================================================
+# 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.
+# ============LICENSE_END=========================================================
+
+# #####################################################################
+#
+# Upgrade/Downgrade SQL File Name Format:
+#
+#     <VERSION>-<pdp|feature-name>[-description](.upgrade|.downgrade).sql
+#
+# This tool operates on a migration working directory at
+#
+#    $POLICY_HOME/etc/db/migration
+#
+# Upgrade/Downgrade files for each schema (aka database) names to be maintained
+# by this tool are located at
+#
+#    $POLICY_HOME/etc/db/migration/<schema-name>/sql
+#
+# The nature of the migration directories is dynamic.
+# A number of environment variables needs to be initialized
+# prior to running db-migrator.
+# These vaiables specifiy the connection details for the database
+# to be upgraded/downgraded.
+#
+# The repository of upgrade/downgrade scripts is located in the
+# /home/policy/sql directory.
+# Two additional scripts have been provided to prepare the directories/files
+# needed to perform the upgrade/downgrade.
+#
+# At any given time the following invariant must be preserved in any given
+# $POLICY_HOME/etc/db/migration/<schema-name>/sql directory
+#
+#     There is only upgrade scripts, or only downgrade scripts, or none.
+#
+# #####################################################################
+
+source ${POLICY_HOME}/etc/profile.d/env.sh
+
+METADATA_DB=migration
+METADATA_TABLE=schema_versions
+MIGRATION_DIR=${POLICY_HOME}/etc/db/migration
+ZERO_VERSION="0"
+BASE_VERSION="0800"
+UPGRADE_SQL_SUFFIX=".upgrade.sql"
+DOWNGRADE_SQL_SUFFIX=".downgrade.sql"
+
+SQL_QUOTES="SET SESSION SQL_MODE=ANSI_QUOTES;"
+
+#####################################################
+# usage
+#####################################################
+
+function usage() {
+    echo
+    echo -e "syntax: $(basename "$0") "
+    echo -e "\t -s <schema-name> "
+    echo -e "\t [-b <migration-dir>] "
+    echo -e "\t [-f <from-version>]"
+    echo -e "\t [-t <target-version>]"
+    echo -e "\t -o <operations> "
+    echo
+    echo -e "\t where <operations>=upgrade|downgrade|auto|version|erase|report"
+    echo
+    echo
+    echo -e "Configuration Options:"
+    echo -e "\t -s|--schema|--database:  schema to operate on ('ALL' to apply on all)"
+    echo -e "\t -b|--basedir: overrides base DB migration directory"
+    echo -e "\t -f|--from: overrides current release version for operations"
+    echo -e "\t -t|--target: overrides target release to upgrade/downgrade"
+    echo
+    echo -e "Operations:"
+    echo -e "\t upgrade: upgrade operation"
+    echo -e "\t downgrade: performs a downgrade operation"
+    echo -e "\t auto: autonomous operation, determines upgrade or downgrade"
+    echo -e "\t version: returns current version, and in conjunction if '-f' sets the current version"
+    echo -e "\t erase: erase all data related <schema> (use with care)"
+    echo -e "\t report: migration detailed report on an schema"
+    echo -e "\t ok: is the migration status valid"
+    echo
+    echo
+}
+
+#####################################################
+# current_schema
+#####################################################
+
+function current_schema
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- current_schema --"
+        set -x
+    fi
+
+    local rc
+    local query="SELECT count(table_name) from information_schema.tables where table_schema='${SCHEMA}'"
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
+    TABLES_IN_SCHEMA=$(${PSQL} -X -A --tuples-only --quiet --command "${query}")
+    if [ ${TABLES_IN_SCHEMA} -gt 0 ] && [ "${BASE_VERSION}" \> "${CURRENT_RELEASE}" ]; then
+        set_current_release "${BASE_VERSION}"
+        return $?
+    fi
+
+    return 0
+}
+
+#####################################################
+# ensure global metadata
+#####################################################
+
+function ensure_metadata
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- ensure_metadata --"
+        set -x
+    fi
+
+    local sql rc
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    sql="SELECT 'CREATE DATABASE migration' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'migration');"
+    ${PSQL} --command "${sql}"
+    rc=$?
+    if [ ${rc} -ne 0 ]; then
+        return ${rc}
+    fi
+
+    sql="CREATE TABLE IF NOT EXISTS ${METADATA_TABLE} "
+    sql=${sql}"(name VARCHAR(60) NOT NULL, version VARCHAR(20), "
+    sql=${sql}"PRIMARY KEY(name));"
+    ${PSQL} --command "${sql}"
+    return $?
+}
+
+
+#####################################################
+# ensure metadata on a per schema basis
+#####################################################
+
+function ensure_metadata_schema
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- ensure_metadata_schema --"
+        set -x
+    fi
+
+    local sql rc
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    sql="CREATE TABLE IF NOT EXISTS ${METADATA_HISTORY} "
+    sql=${sql}"(ID bigserial, script VARCHAR(80) NOT NULL, "
+    sql=${sql}"operation VARCHAR(10), from_version VARCHAR(20), to_version VARCHAR(20), tag VARCHAR(20), "
+    sql=${sql}"success VARCHAR(1), atTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(ID));"
+    ${PSQL} --command "${sql}"
+    rc=$?
+    if [ ${rc} -ne 0 ]; then
+        return ${rc}
+    fi
+
+    sql="SELECT 'CREATE DATABASE migration' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'policyadmin');"
+    ${PSQL} --command "${sql}"
+    return $?
+}
+
+
+#####################################################
+# target_release
+#####################################################
+
+function target_release
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- target_release --"
+        set -x
+    fi
+
+    local sql sqlName upgradeSqls downgradeSqls
+
+    TARGET_UPGRADE_RELEASE=${ZERO_VERSION}
+    TARGET_DOWNGRADE_RELEASE=${ZERO_VERSION}
+
+    upgradeSqls=$(ls -v -r "${UPGRADE_DIR}"/*/upgrade/*.sql 2> /dev/null)
+    for sql in ${upgradeSqls}; do
+        TARGET_UPGRADE_RELEASE="$(basename $(dirname $(dirname $sql)))"
+        break
+    done
+
+    # default unless overriden
+    TARGET_DOWNGRADE_RELEASE="${ZERO_VERSION}"
+}
+
+#####################################################
+# is_upgrade
+#####################################################
+
+function is_upgrade
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- is_upgrade --"
+        set -x
+    fi
+
+    local upgradeSqls
+
+    upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*/upgrade/*.sql 2> /dev/null)
+    if [ -z "${upgradeSqls}" ]; then
+        return 1
+    else
+        return 0
+    fi
+}
+
+
+#####################################################
+# is_downgrade
+#####################################################
+
+function is_downgrade
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- is_downgrade --"
+        set -x
+    fi
+
+    local downgradeSqls
+
+    downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*/downgrade/*.sql 2> /dev/null)
+    if [ -z "${downgradeSqls}" ]; then
+        return 1
+    else
+        return 0
+    fi
+}
+
+
+#####################################################
+# set_current_release
+#####################################################
+
+function set_current_release
+{
+        if [ "${DEBUG}" = "y" ]; then
+                echo "-- set_current_release --"
+                set -x
+        fi
+
+        CURRENT_RELEASE="${1}"
+
+        local sql
+        sql="INSERT INTO ${METADATA_TABLE} (name, version) "
+        sql=${sql}"VALUES('${SCHEMA}', '${CURRENT_RELEASE}') "
+        sql=${sql}"ON CONFLICT (name) DO UPDATE SET version='${CURRENT_RELEASE}';"
+
+        PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}";
+        ${PSQL} --command "${sql}"
+        return $?
+}
+
+#####################################################
+# current_release
+#####################################################
+
+function current_release
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- current_release --"
+        set -x
+    fi
+
+    local rc
+    local query="SELECT version FROM schema_versions WHERE name='${SQL_DB}'"
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    CURRENT_RELEASE=$(${PSQL} -X -A -t --command "${query}")
+
+    if [ -z "${CURRENT_RELEASE}" ]; then
+        set_current_release "${ZERO_VERSION}"
+        return $?
+    fi
+
+    return 0
+}
+
+#####################################################
+# previous_release
+#####################################################
+
+function previous_release
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- previous_release --"
+        set -x
+    fi
+    local current_release="${1}"
+    local current_release_int_val previous_release_int_val previous_release
+
+    if [ $current_release == $ZERO_VERSION ] || [ $current_release == $BASE_VERSION ]; then
+         PREVIOUS_RELEASE=$ZERO_VERSION
+    else
+         current_release_int_val=$(echo $current_release  | awk '{$0=int($0)}1')
+         let previous_release_int_val=$current_release_int_val-100
+         if [ $previous_release_int_val -lt 1000 ]; then
+                 previous_release="0"$previous_release_int_val
+         else
+                 previous_release=$previous_release_int_val
+         fi
+         PREVIOUS_RELEASE=$previous_release
+    fi
+
+    return 0
+}
+
+
+#####################################################
+# last_operation_status
+#####################################################
+
+function last_operation_status
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- last_operation_status --"
+        set -x
+    fi
+
+    local query rc row
+
+    query="SELECT script, operation, from_version, to_version, success FROM  "
+    query=${query}"${METADATA_HISTORY} WHERE id="
+    query=${query}"(SELECT MAX(id) from ${METADATA_HISTORY}) "
+    query=${query}"AND success::integer=0"
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    row=$(${PSQL} --tuples-only --quiet --command "${query}")
+
+    if [ "${row}" != "" ]; then
+        LAST_SCRIPT=$(echo $row | awk '{print $1}')
+        LAST_OPERATION=$(echo $row | awk '{print $2}')
+        LAST_FROM_VERSION=$(echo $row | awk '{print $3}')
+        LAST_TO_VERSION=$(echo $row | awk '{print $4}')
+        LAST_STATUS=$(echo $row | awk '{print $5}')
+        rc=0
+    else
+        rc=1
+    fi
+
+    return $rc;
+}
+
+#####################################################
+# execute sql script history
+#####################################################
+
+function track_script
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- track_script $* --"
+        set -x
+    fi
+
+    local script="${1}" operation="${2}" success="${3}" from_version="${4}" to_version="${5}" tag="${6}"
+    if [ $operation == "downgrade" ]; then
+       to_version=${TARGET_DOWNGRADE_RELEASE}
+    fi
+
+    local sql="INSERT INTO ${METADATA_HISTORY}(script,operation,from_version,to_version,tag,success,atTime) "
+    sql=${sql}"VALUES ('${script}','${operation}','${from_version}','${to_version}','${tag}','${success}',now()) "
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}";
+    ${PSQL} --command "${sql}"
+    return $?
+}
+
+
+#####################################################
+# execute sql script
+#####################################################
+
+function run_script
+{
+    if [ "${DEBUG}" == "y" ]; then
+        echo "-- run_script $* --"
+        set -x
+    fi
+
+    local operation="${1}" script="${2}" scriptPath="${3}" fromVersion="${4}" toVersion="${5}" tag="${6}"
+
+    echo
+    echo "> ${operation} ${script}"
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
+    ${PSQL} ${SCHEMA} < "${scriptPath}"
+    local rc=$?
+    if [ ${rc} -ne 0 ]; then
+        success="0"
+    else
+        success="1"
+    fi
+
+    track_script "${script}" "${operation}" "${success}" "${fromVersion}" "${toVersion}" "${tag}"
+
+    return ${rc}
+}
+
+#####################################################
+# upgrade
+#####################################################
+
+function upgrade
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- upgrade --"
+        set -x
+    fi
+
+    local sqlName sqlFile schemaVersion upgradeSqls rc tagDate tag
+    tagDate=$(date +%d%m%y%H%M%S)
+
+    echo "upgrade: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
+
+    if [ ${CURRENT_RELEASE} \< ${TARGET_UPGRADE_RELEASE} ]; then
+	upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*/upgrade/*.sql 2> /dev/null)
+        for sqlFile in ${upgradeSqls}; do
+            sqlName=$(basename "${sqlFile}")
+            schemaVersion="$(basename $(dirname $(dirname $sqlFile)))"
+            previous_release $schemaVersion
+            if [ "${schemaVersion}" -gt "${CURRENT_RELEASE}" ] && \
+                [ "${schemaVersion}" -le "${TARGET_UPGRADE_RELEASE}" ]; then
+                tag=${tagDate}"${schemaVersion}u"
+                if [ $RETRY -eq 1 ] && \
+                    # Compare the numeric portion of the filename because shell doesn't support string comparison
+                    [ $(echo ${sqlName}| awk -F- '{print $1}') -${COMPARE} $(echo ${LAST_SCRIPT}| awk -F- '{print $1}') ]; then
+                     rc=1
+		     echo "rc=1"
+                else
+                     rc=0
+		     echo "rc=0"
+                fi
+                if [ ${rc} -eq 0 ]; then
+                    run_script "upgrade" "${sqlName}" "${sqlFile}" "${PREVIOUS_RELEASE}" "${schemaVersion}" "${tag}"
+                    rc=$?
+                    if [ ${rc} -ne 0 ]; then
+                        echo "${SCHEMA}: upgrade aborted at ${schemaVersion} by script ${sqlName}"
+                        set_current_release "${schemaVersion}"
+                        return ${rc}
+                    fi
+                fi
+            fi
+        done
+
+        set_current_release "${TARGET_UPGRADE_RELEASE}"
+    fi
+
+    return 0
+}
+
+#####################################################
+# downgrade
+#####################################################
+
+function downgrade
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- downgrade --"
+        set -x
+    fi
+
+    local sqlName sqlFile schemaVersion downgradeSqls rc tagDate tag
+    tagDate=$(date +%d%m%y%H%M%S)
+
+    echo "downgrade: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
+
+    if [ ${CURRENT_RELEASE} \> ${TARGET_DOWNGRADE_RELEASE} ]; then
+        downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*/downgrade/*.sql 2> /dev/null)
+        for sqlFile in ${downgradeSqls}; do
+            sqlName=$(basename "${sqlFile}")
+            schemaVersion="$(basename $(dirname $(dirname $sqlFile)))"
+            if [ "${schemaVersion}" -le "${CURRENT_RELEASE}" ] && \
+                [ "${schemaVersion}" -gt "${TARGET_DOWNGRADE_RELEASE}" ]; then
+                tag=${tagDate}"${schemaVersion}d"
+                if [ $RETRY -eq 1 ] && \
+                    # Compare the numeric portion of the filename because shell doesn't support string comparison
+                    [ $(echo ${sqlName}| awk -F- '{print $1}') -${COMPARE} $(echo ${LAST_SCRIPT}| awk -F- '{print $1}') ]; then
+                     rc=1
+                else
+                     rc=0
+                fi
+                if [ ${rc} -eq 0 ]; then
+                    run_script "downgrade" "${sqlName}" "${sqlFile}" "${schemaVersion}" "${PREVIOUS_RELEASE}" ${tag}
+                    rc=$?
+                    if [ ${rc} -ne 0 ]; then
+                        echo "${SCHEMA}: downgrade aborted at ${schemaVersion} by script ${sqlName}"
+                        set_current_release "${schemaVersion}"
+                        return ${rc}
+                    fi
+                fi
+            fi
+        done
+
+        set_current_release "${TARGET_DOWNGRADE_RELEASE}"
+    fi
+
+    return 0
+}
+
+#####################################################
+# erase
+#####################################################
+
+function erase
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- erase --"
+        set -x
+    fi
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    local updateMetadata="UPDATE ${METADATA_TABLE} SET version='${ZERO_VERSION}';"
+    ${PSQL} --command "${updateMetadata}"
+
+    local deleteHistory="DELETE FROM ${METADATA_HISTORY};"
+    ${PSQL} --command "${deleteHistory}"
+
+    local dropDB="DROP DATABASE IF EXISTS ${SCHEMA_DB}";
+    ${PSQL} --command "${dropDB}"
+}
+
+#####################################################
+# report
+#####################################################
+
+function report
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- report --"
+        set -x
+    fi
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
+    local versionSql="SELECT * FROM ${METADATA_TABLE} WHERE name='${SCHEMA}';"
+    ${PSQL} --command "${versionSql}"
+
+    local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY id, atTime ASC;"
+    ${PSQL} --command "${historySql}"
+
+    okay
+}
+
+function okay
+{
+    if [ "${DEBUG}" = "y" ]; then
+        echo "-- okay --"
+        set -x
+    fi
+
+    local rc=0
+    if is_upgrade; then
+        if [ "${CURRENT_RELEASE}" = "${TARGET_UPGRADE_RELEASE}" ]; then
+            echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
+        else
+            echo "${SCHEMA}: upgrade available: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
+            rc=1
+        fi
+    else
+        if [ "${CURRENT_RELEASE}" = "${TARGET_DOWNGRADE_RELEASE}" ]; then
+            echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
+        else
+            echo "${SCHEMA}: downgrade available: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
+            rc=1
+        fi
+    fi
+
+    return ${rc}
+}
+
+#####################################################
+# MAIN
+#####################################################
+
+if [ "${DEBUG}" = "y" ]; then
+    echo "-- $0 $* --"
+    set -x
+fi
+until [ -z "$1" ]; do
+    case $1 in
+        -s|--schema|--database)    shift
+             SCHEMA=$1
+             ;;
+        -b|--basedir)   shift
+             MIGRATION_DIR=$1
+             ;;
+        -t|--target)    shift
+             INPUT_TARGET_RELEASE=$1
+             ;;
+        -f|--from)      shift
+             INPUT_FROM_RELEASE=$1
+             ;;
+        -o|--operation) shift
+             OPERATION=$1
+             ;;
+        *)              usage
+             exit 1
+             ;;
+    esac
+    shift
+done
+
+case ${OPERATION} in
+    upgrade)    ;;
+    downgrade)  ;;
+    auto)       ;;
+    version)    ;;
+    erase)      ;;
+    report)     ;;
+    ok)         ;;
+    *)          echo "error: invalid operation provided"
+                usage
+                exit 1
+                ;;
+esac
+
+if [ -z "${SCHEMA}" ]; then
+    echo "error: a database name must be provided"
+    usage
+    exit 2
+fi
+
+source "${POLICY_HOME}"/etc/profile.d/env.sh
+
+if [ -z "${SQL_HOST}" ] || [ -z "${SQL_USER}" ] || [ -z "${SQL_PASSWORD}" ]; then
+    echo "error: no database has been set up"
+    exit 4
+fi
+
+PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}";
+if ! ${PSQL} --command '\l'; then
+    echo "error: No DB connectivity to ${SQL_HOST} for ${SQL_USER}"
+    exit 5
+fi
+
+if [ "${SCHEMA}" = "ALL" ]; then
+    SCHEMA="*"
+fi
+
+SCHEMA_S=$(ls -d "${MIGRATION_DIR}"/${SCHEMA}/ 2> /dev/null)
+if [ -z "${SCHEMA_S}" ]; then
+    echo "error: no databases available"
+    exit 0
+fi
+
+if ! ensure_metadata; then
+    echo "error: migration metadata not accessible"
+    exit 7
+fi
+
+rc=0
+for dbPath in ${SCHEMA_S}; do
+    SCHEMA=$(basename "${dbPath}")
+    SCHEMA_DB="'${SCHEMA}'"
+    UPGRADE_DIR="${MIGRATION_DIR}"/"${SCHEMA}"/postgres
+    DOWNGRADE_DIR=${UPGRADE_DIR}
+    METADATA_HISTORY="${SCHEMA}_schema_changelog"
+    TARGET_RELEASE=${INPUT_TARGET_RELEASE}
+
+    if is_upgrade && is_downgrade; then
+        echo "${SCHEMA}: failure: invalid configuration: ${UPGRADE_SQL_SUFFIX} and "\
+            "${DOWNGRADE_SQL_SUFFIX} exist under ${DOWNGRADE_DIR}"
+        rc=1
+        continue
+    fi
+
+    if [ "${operation}" = "auto" ]; then
+        if is_upgrade; then
+            operation=upgrade
+        else
+            operation=downgrade
+        fi
+    fi
+
+    if ! ensure_metadata_schema; then
+        echo "${SCHEMA}: failure: metadata not accessible for this schema"
+        continue
+    fi
+
+    if [ -z "${TARGET_RELEASE}" ]; then
+        target_release
+    else
+        # user asked to override
+        TARGET_UPGRADE_RELEASE="${TARGET_RELEASE}"
+        TARGET_DOWNGRADE_RELEASE="${TARGET_RELEASE}"
+    fi
+
+    if [ -z "${CURRENT_RELEASE}" ]; then
+        if ! current_release; then
+            echo "${SCHEMA}: failure: cannot obtain current release"
+            continue
+        fi
+    else
+        if ! set_current_release "${CURRENT_RELEASE}"; then
+            echo "${SCHEMA}: failure: cannot set current release"
+            continue
+        fi
+    fi
+
+    PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}";
+    # Check if the schema has already been installed
+    current_schema
+    RETRY=0
+    COMPARE=""
+    last_operation_status
+    last_status=$?
+    if [ $last_status -eq 0 ]; then
+         echo "Partial $LAST_OPERATION detected"
+         RETRY=1
+         if [ "${OPERATION}" == 'downgrade' ] && [ "${LAST_OPERATION}" == 'downgrade' ]; then
+              CURRENT_RELEASE="${LAST_FROM_VERSION}"
+              # greater than
+              COMPARE="gt"
+              echo "Downgrade will attempt to resume at $LAST_SCRIPT"
+         elif [ "${OPERATION}" == 'downgrade' ] && [ "${LAST_OPERATION}" == 'upgrade' ]; then
+              CURRENT_RELEASE="${LAST_TO_VERSION}"
+              # greater than or equal to
+              COMPARE="ge"
+              echo "Downgrade will attempt to resume before $LAST_SCRIPT"
+         elif [ "${OPERATION}" == 'upgrade' ] && [ "${LAST_OPERATION}" == 'upgrade' ]; then
+              CURRENT_RELEASE="${LAST_FROM_VERSION}"
+              # less than
+              COMPARE="lt"
+              echo "Upgrade will attempt to resume at $LAST_SCRIPT"
+         else
+              CURRENT_RELEASE="${LAST_TO_VERSION}"
+              # less than or equal
+              COMPARE="le"
+              echo "Upgrade will attempt to resume after $LAST_SCRIPT"
+         fi
+    fi
+
+    if [ -n "${INPUT_FROM_RELEASE}" ]; then
+        if [ "${CURRENT_RELEASE}" \> "${INPUT_FROM_RELEASE}" ] || [ "${CURRENT_RELEASE}" \< "${INPUT_FROM_RELEASE}" ]; then
+            echo "${SCHEMA}: On version ${CURRENT_RELEASE} cannot ${OPERATION} from ${INPUT_FROM_RELEASE}"
+            continue
+        else
+            CURRENT_RELEASE=${INPUT_FROM_RELEASE}
+        fi
+    fi
+
+    case ${OPERATION} in
+        upgrade)     if upgrade; then
+                         echo "${SCHEMA}: OK: upgrade (${CURRENT_RELEASE})"
+                     else
+                         rc=1
+                         echo "${SCHEMA}: failure: upgrade to release ${TARGET_UPGRADE_RELEASE} (${CURRENT_RELEASE})"
+                     fi
+                     ;;
+        downgrade)   if downgrade; then
+                         echo "${SCHEMA}: OK: downgrade (${CURRENT_RELEASE})"
+                     else
+                        rc=1
+                        echo "${SCHEMA}: failure: downgrade to release ${TARGET_DOWNGRADE_RELEASE} (${CURRENT_RELEASE})"
+                     fi
+                     ;;
+        version)     echo "${SCHEMA}: ${CURRENT_RELEASE}"
+                     ;;
+        erase)       erase
+                     ;;
+        report)      report
+                     ;;
+        ok)          okay
+                     ;;
+        esac
+
+done
+exit $rc
diff --git a/policy-db-migrator/src/main/docker/prepare_downgrade.sh b/policy-db-migrator/src/main/docker/prepare_downgrade.sh
index 004873c5..a7fdbbb3 100755
--- a/policy-db-migrator/src/main/docker/prepare_downgrade.sh
+++ b/policy-db-migrator/src/main/docker/prepare_downgrade.sh
@@ -1,6 +1,6 @@
 #!/bin/sh
 # ============LICENSE_START=======================================================
-#  Copyright (C) 2021 Nordix Foundation.
+#  Copyright (C) 2021-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.
@@ -29,14 +29,14 @@ export operation=downgrade
 cd $POLICY_HOME
 
 # Create schema directory for upgrade
-mkdir -p $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/
+mkdir -p $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/
 # Remove any files from previous operations
-rm -rf $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/* 2>/dev/null
+rm -rf $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/* 2>/dev/null
 # Copy files to downgrade directories
-cd /home/policy/sql && find . -type f  -not -path '*/upgrade/*' -not -path '*/upgrade'  -print0  \
-   | cpio --null -pud $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/
+cd /home/policy/${SCRIPT_DIRECTORY} && find . -type f  -not -path '*/upgrade/*' -not -path '*/upgrade'  -print0  \
+   | cpio --null -pud $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/
 
-releases=$(find $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/*/downgrade -type d | sort -u | rev | cut -f2 -d/ | rev)
+releases=$(find $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/*/downgrade -type d | sort -u | rev | cut -f2 -d/ | rev)
 for release in $releases
 do
         echo "Preparing $operation release version: $release"
diff --git a/policy-db-migrator/src/main/docker/prepare_upgrade.sh b/policy-db-migrator/src/main/docker/prepare_upgrade.sh
index b8a6a6d3..b5de226d 100755
--- a/policy-db-migrator/src/main/docker/prepare_upgrade.sh
+++ b/policy-db-migrator/src/main/docker/prepare_upgrade.sh
@@ -1,6 +1,6 @@
 #!/bin/sh
 # ============LICENSE_START=======================================================
-#  Copyright (C) 2021 Nordix Foundation.
+#  Copyright (C) 2021-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.
@@ -29,14 +29,14 @@ export operation=upgrade
 cd $POLICY_HOME
 
 # Create schema directory for upgrade
-mkdir -p $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/
+mkdir -p $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/
 # Remove any files from previous operations
-rm -rf $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/* 2>/dev/null
+rm -rf $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/* 2>/dev/null
 # Copy files to upgrade directories
-cd /home/policy/sql && find . -type f  -not -path '*/downgrade/*' -not -path '*/downgrade'  -print0  \
-   | cpio --null -pud $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/
+cd /home/policy/${SCRIPT_DIRECTORY} && find . -type f  -not -path '*/downgrade/*' -not -path '*/downgrade'  -print0  \
+   | cpio --null -pud $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/
 
-releases=$(find $POLICY_HOME/etc/db/migration/${SCHEMA}/sql/*/upgrade -type d | sort -u | rev | cut -f2 -d/ | rev)
+releases=$(find $POLICY_HOME/etc/db/migration/${SCHEMA}/${SCRIPT_DIRECTORY}/*/upgrade -type d | sort -u | rev | cut -f2 -d/ | rev)
 for release in $releases
 do
         echo "Preparing $operation release version: $release"