aboutsummaryrefslogtreecommitdiffstats
path: root/policy-db-migrator/src
diff options
context:
space:
mode:
authorktimoney <kevin.timoney@est.tech>2021-07-16 15:58:40 +0100
committerktimoney <kevin.timoney@est.tech>2021-07-20 08:28:31 +0100
commit939ccae374e93cbc204eaf3f47150c27107e6e6b (patch)
treef86a41ee4d908bea7d0926fbdefa6aca79110381 /policy-db-migrator/src
parent39f68e0f3e0a0442b2ddf4593c33a2e483fc1822 (diff)
Update db-migrator to include rollback functionality
Issue-ID: POLICY-3490 Change-Id: Ibd1898819a5cb70f25bf69d71f2975dd2845389e Signed-off-by: ktimoney <kevin.timoney@est.tech>
Diffstat (limited to 'policy-db-migrator/src')
-rw-r--r--policy-db-migrator/src/main/docker/db-migrator109
1 files changed, 86 insertions, 23 deletions
diff --git a/policy-db-migrator/src/main/docker/db-migrator b/policy-db-migrator/src/main/docker/db-migrator
index 9065220a..7eced0e5 100644
--- a/policy-db-migrator/src/main/docker/db-migrator
+++ b/policy-db-migrator/src/main/docker/db-migrator
@@ -58,6 +58,7 @@ METADATA_DB=migration
METADATA_TABLE=${METADATA_DB}.db_metadata_versions
MIGRATION_DIR=${POLICY_HOME}/etc/db/migration
ZERO_VERSION="0"
+BASE_VERSION="0800"
UPGRADE_SQL_SUFFIX=".upgrade.sql"
DOWNGRADE_SQL_SUFFIX=".downgrade.sql"
@@ -98,6 +99,29 @@ function usage() {
}
#####################################################
+# 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}'"
+
+ TABLES_IN_SCHEMA=$(${MYSQL} --skip-column-names --silent --execute "${query}")
+ if [ ${TABLES_IN_SCHEMA} -gt 0 ] && [ "${BASE_VERSION}" \> "${CURRENT_RELEASE}" ]; then
+ set_current_release "${BASE_VERSION}"
+ return $?
+ fi
+
+ return 0
+}
+
+#####################################################
# ensure global metadata
#####################################################
@@ -139,10 +163,10 @@ function ensure_metadata_schema
local sql rc
sql="CREATE TABLE IF NOT EXISTS ${METADATA_HISTORY} "
- sql=${sql}"(script VARCHAR(80) NOT NULL, "
- sql=${sql}"operation VARCHAR(10), from_version VARCHAR(20), to_version VARCHAR(20), success VARCHAR(1), "
- sql=${sql}"atTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, "
- sql=${sql}"PRIMARY KEY(to_version, script, operation));"
+ sql=${sql}"(ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, 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 ON UPDATE CURRENT_TIMESTAMP, "
+ sql=${sql}"PRIMARY KEY(ID));"
${MYSQL} --execute "${sql}"
rc=$?
if [ ${rc} -ne 0 ]; then
@@ -271,6 +295,35 @@ function current_release
}
#####################################################
+# 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
+}
+
+#####################################################
# execute sql script history
#####################################################
@@ -281,15 +334,13 @@ function track_script
set -x
fi
- local script="${1}" operation="${2}" success="${3}" to_version="${4}"
+ 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
- from_version=${CURRENT_RELEASE}
- local sql="INSERT INTO ${METADATA_HISTORY}(script,operation,from_version,to_version,success,atTime) "
- sql=${sql}"VALUES ('${script}','${operation}','${from_version}','${to_version}','${success}',now()) "
- sql=${sql}"ON DUPLICATE KEY UPDATE operation=values(operation),from_version=values(from_version),"
- sql=${sql}"success=values(success), atTime=values(atTime);"
+
+ 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()) "
${MYSQL} --execute "${sql}"
return $?
@@ -307,7 +358,7 @@ function run_script
set -x
fi
- local operation="${1}" script="${2}" scriptPath="${3}" schemaVersion="${4}"
+ local operation="${1}" script="${2}" scriptPath="${3}" fromVersion="${4}" toVersion="${5}" tag="${6}"
echo
echo "> ${operation} ${script}"
@@ -320,7 +371,7 @@ function run_script
success="1"
fi
- track_script "${script}" "${operation}" "${success}" "${schemaVersion}"
+ track_script "${script}" "${operation}" "${success}" "${fromVersion}" "${toVersion}" "${tag}"
return ${rc}
}
@@ -336,9 +387,8 @@ function upgrade
set -x
fi
- local sqlName sqlFile schemaVersion upgradeSqls rc
-
- ${MYSQL} --execute "USE ${SCHEMA_DB}"
+ local sqlName sqlFile schemaVersion upgradeSqls rc tagDate tag
+ tagDate=$(date +%d%m%y%H%M%S)
echo "upgrade: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
@@ -347,9 +397,11 @@ function upgrade
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
- run_script "upgrade" "${sqlName}" "${sqlFile}" "${schemaVersion}"
+ tag=${tagDate}"${schemaVersion}u"
+ run_script "upgrade" "${sqlName}" "${sqlFile}" "${PREVIOUS_RELEASE}" "${schemaVersion}" "${tag}"
rc=$?
if [ ${rc} -ne 0 ]; then
echo "${SCHEMA}: upgrade aborted at ${schemaVersion} by script ${sqlName}"
@@ -376,9 +428,8 @@ function downgrade
set -x
fi
- local sqlName sqlFile schemaVersion downgradeSqls rc
-
- ${MYSQL} --execute "USE ${SCHEMA_DB}"
+ local sqlName sqlFile schemaVersion downgradeSqls rc tagDate tag
+ tagDate=$(date +%d%m%y%H%M%S)
echo "downgrade: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
@@ -389,7 +440,8 @@ function downgrade
schemaVersion="$(basename $(dirname $(dirname $sqlFile)))"
if [ "${schemaVersion}" -le "${CURRENT_RELEASE}" ] && \
[ "${schemaVersion}" -gt "${TARGET_DOWNGRADE_RELEASE}" ]; then
- run_script "downgrade" "${sqlName}" "${sqlFile}" "${schemaVersion}"
+ tag=${tagDate}"${schemaVersion}d"
+ run_script "downgrade" "${sqlName}" "${sqlFile}" "${schemaVersion}" "${PREVIOUS_RELEASE}" ${tag}
rc=$?
if [ ${rc} -ne 0 ]; then
echo "${SCHEMA}: downgrade aborted at ${schemaVersion} by script ${sqlName}"
@@ -440,7 +492,7 @@ function report
local versionSql="SELECT * FROM ${METADATA_TABLE} WHERE name='${SCHEMA}';"
${MYSQL} --execute "${versionSql}"
- local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY atTime ASC;"
+ local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY id, atTime ASC;"
${MYSQL} --execute "${historySql}"
okay
@@ -493,7 +545,7 @@ until [ -z "$1" ]; do
INPUT_TARGET_RELEASE=$1
;;
-f|--from) shift
- INPUT_CURRENT_RELEASE=$1
+ INPUT_FROM_RELEASE=$1
;;
-o|--operation) shift
OPERATION=$1
@@ -561,7 +613,6 @@ for dbPath in ${SCHEMA_S}; do
DOWNGRADE_DIR=${UPGRADE_DIR}
METADATA_HISTORY="${METADATA_DB}.\`${SCHEMA}_change_history\`"
TARGET_RELEASE=${INPUT_TARGET_RELEASE}
- CURRENT_RELEASE=${INPUT_CURRENT_RELEASE}
if is_upgrade && is_downgrade; then
echo "${SCHEMA}: failure: invalid configuration: ${UPGRADE_SQL_SUFFIX} and "\
@@ -603,6 +654,18 @@ for dbPath in ${SCHEMA_S}; do
fi
fi
+ # Check if the schema has already been installed
+ current_schema
+
+ 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})"