summaryrefslogtreecommitdiffstats
path: root/policy-management/src/main/server-gen/bin/db-migrator
diff options
context:
space:
mode:
authorJorge Hernandez <jh1730@att.com>2017-08-14 13:34:53 -0500
committerJorge Hernandez <jh1730@att.com>2017-08-14 13:43:07 -0500
commit483b87c8bee3c6911aa537b9c823a2c8d24bec80 (patch)
tree32b8422350a6060b9f26bc12d79b7fc3e0684653 /policy-management/src/main/server-gen/bin/db-migrator
parent155b76589040d98d4a693d9d83dadb4f454cff5e (diff)
Support of DB-enabled features
Isolation and data migration support overall and on a per feature basis. policy@drools:/opt/app/policy$ policy.sh status [drools-pdp-controllers] L []: Policy Management (no pidfile) is NOT running 1 cron jobs installed. [features] name version status ---- ------- ------ eelf 1.1.0 disabled healthcheck 1.1.0 disabled session-persistence 1.1.0 enabled [migration] session-persistence: upgrade out-of-sync: 0 -> 201702 policy@drools:/opt/app/policy$ features enable session-persistence session-persistence: upgrade out-of-sync: 0 -> 201702 name version status ---- ------- ------ eelf 1.1.0 disabled healthcheck 1.1.0 disabled session-persistence 1.1.0 enabled policy@drools:/opt/app/policy$ db-migrator -s session-persistence -o upgrade upgrade: 0 -> 201702 > upgrade 201701-blah.upgrade.sql -------------- create table blah (a varchar(15), b varchar(20)) -------------- > upgrade 201702-blah2.upgrade.sql -------------- create table blah2 (a varchar(15), b varchar(20)) -------------- session-persistence: OK: upgrade (201702) policy@drools:/opt/app/policy$ db-migrator -s ALL -o report +---------------------+---------+ | name | version | +---------------------+---------+ | session-persistence | 201702 | +---------------------+---------+ +--------------------------+-----------+---------+---------------------+ | script | operation | success | atTime | +--------------------------+-----------+---------+---------------------+ | 201701-blah.upgrade.sql | upgrade | 1 | 2017-08-14 16:01:32 | | 201702-blah2.upgrade.sql | upgrade | 1 | 2017-08-14 16:01:32 | +--------------------------+-----------+---------+---------------------+ policy@drools:/opt/app/policy$ features disable session-persistence name version status ---- ------- ------ eelf 1.1.0 disabled healthcheck 1.1.0 disabled session-persistence 1.1.0 disabled policy@drools:/opt/app/policy$ db-migrator -s ALL -o downgrade downgrade: 201702 -> 0 > downgrade 201702-blah2.downgrade.sql -------------- drop table if exists blah2 -------------- > downgrade 201701-blah.downgrade.sql -------------- drop table if exists blah -------------- session-persistence: OK: downgrade (0) policy@drools:/opt/app/policy$ db-migrator -s ALL -o report +---------------------+---------+ | name | version | +---------------------+---------+ | session-persistence | 0 | +---------------------+---------+ +----------------------------+-----------+---------+---------------------+ | script | operation | success | atTime | +----------------------------+-----------+---------+---------------------+ | 201701-blah.upgrade.sql | upgrade | 1 | 2017-08-14 16:01:32 | | 201702-blah2.upgrade.sql | upgrade | 1 | 2017-08-14 16:01:32 | | 201701-blah.downgrade.sql | downgrade | 1 | 2017-08-14 16:13:49 | | 201702-blah2.downgrade.sql | downgrade | 1 | 2017-08-14 16:13:49 | +----------------------------+-----------+---------+---------------------+ session-persistence: OK @ 0 Change-Id: Ie185f5d7a8463cb349ac452d8c2b4b05928b3e56 Issue-ID: POLICY-96 Signed-off-by: Jorge Hernandez <jh1730@att.com>
Diffstat (limited to 'policy-management/src/main/server-gen/bin/db-migrator')
-rw-r--r--policy-management/src/main/server-gen/bin/db-migrator625
1 files changed, 625 insertions, 0 deletions
diff --git a/policy-management/src/main/server-gen/bin/db-migrator b/policy-management/src/main/server-gen/bin/db-migrator
new file mode 100644
index 00000000..328ad0b5
--- /dev/null
+++ b/policy-management/src/main/server-gen/bin/db-migrator
@@ -0,0 +1,625 @@
+#!/bin/bash
+#
+# ============LICENSE_START=======================================================
+# ONAP
+# ================================================================================
+# Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+# ================================================================================
+# 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.
+# Other tooling aware of when migrations are needed are in charge to populate
+# the migrations directory accordingly.
+#
+# One of these tools is the 'features' when a feature with DB requirements
+# is 'enabled', the upgrade scripts will be made present in the migration directory.
+# When a features is 'disabled' downgrade scripts will be made available in the
+# migration directory.
+#
+# The 'policy' tool via its operations 'status' or 'start' will signal the
+# need to perform upgrade or downgrade for a given schema.
+#
+# 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.
+#
+# #####################################################################
+
+METADATA_DB=migration
+METADATA_TABLE="${METADATA_DB}".metadata_versions
+MIGRATION_DIR="${POLICY_HOME}"/etc/db/migration
+
+ZERO_VERSION="0"
+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
+}
+
+#####################################################
+# ensure global metadata
+#####################################################
+
+function ensure_metadata
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local sql rc
+
+ sql="CREATE DATABASE IF NOT EXISTS ${METADATA_DB};"
+ ${MYSQL} --execute "${sql}"
+ rc=$?
+ if [[ ${rc} != 0 ]]; then
+ return ${rc}
+ fi
+
+ sql="CREATE TABLE IF NOT EXISTS ${METADATA_TABLE} "
+ sql+="(name VARCHAR(60) NOT NULL, version VARCHAR(20), PRIMARY KEY(name));"
+ ${MYSQL} --execute "${sql}"
+ return $?
+}
+
+
+#####################################################
+# ensure metadata on a per schema basis
+#####################################################
+
+function ensure_metadata_schema
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local sql rc
+
+ sql="CREATE TABLE IF NOT EXISTS ${METADATA_HISTORY} "
+ sql+="(script VARCHAR(80) NOT NULL, operation VARCHAR(10), success VARCHAR(1), "
+ sql+="atTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, "
+ sql+="PRIMARY KEY(script));"
+ ${MYSQL} --execute "${sql}"
+ rc=$?
+ if [[ ${rc} != 0 ]]; then
+ return ${rc}
+ fi
+
+ sql="CREATE DATABASE IF NOT EXISTS ${SCHEMA_DB};"
+ ${MYSQL} --execute "${sql}"
+ return $?
+}
+
+
+#####################################################
+# target_release
+#####################################################
+
+function target_release
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ 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_SUFFIX}" 2> /dev/null)
+ for sql in ${upgradeSqls}; do
+ sqlName=$(basename "${sql}")
+ TARGET_UPGRADE_RELEASE="${sqlName%-*}"
+ break
+ done
+
+ # default unless overriden
+ TARGET_DOWNGRADE_RELEASE="${ZERO_VERSION}"
+}
+
+#####################################################
+# is_upgrade
+#####################################################
+
+function is_upgrade
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local upgradeSqls
+
+ upgradeSqls=$(ls "${UPGRADE_DIR}"/*"${UPGRADE_SQL_SUFFIX}" 2> /dev/null)
+ if [[ -z ${upgradeSqls} ]]; then
+ return 1
+ else
+ return 0
+ fi
+}
+
+
+#####################################################
+# is_downgrade
+#####################################################
+
+function is_downgrade
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local downgradeSqls
+
+ downgradeSqls=$(ls "${DOWNGRADE_DIR}"/*"${DOWNGRADE_SQL_SUFFIX}" 2> /dev/null)
+ if [[ -z ${downgradeSqls} ]]; then
+ return 1
+ else
+ return 0
+ fi
+}
+
+
+#####################################################
+# current_release
+#####################################################
+
+function current_release
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local rc
+ local query="SELECT version FROM ${METADATA_TABLE} WHERE name='${SCHEMA}'"
+
+ CURRENT_RELEASE=$(${MYSQL} --skip-column-names --silent --execute "${query}")
+ if [[ -z ${CURRENT_RELEASE} ]]; then
+ set_current_release "${ZERO_VERSION}"
+ return $?
+ fi
+
+ return 0
+}
+
+
+#####################################################
+# set_current_release
+#####################################################
+
+function set_current_release
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} $* --"
+ set -x
+ fi
+
+ CURRENT_RELEASE="${1}"
+
+ local sql="INSERT INTO ${METADATA_TABLE} (name, version) "
+ sql+="VALUES('${SCHEMA}', '${CURRENT_RELEASE}') "
+ sql+="ON DUPLICATE KEY UPDATE version='${CURRENT_RELEASE}';"
+
+ ${MYSQL} --execute "${sql}"
+ return $?
+}
+
+
+#####################################################
+# execute sql script history
+#####################################################
+
+function track_script
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} $* --"
+ set -x
+ fi
+
+ local script="${1}" operation="${2}" success="${3}"
+ local sql="INSERT INTO ${METADATA_HISTORY}(script,operation,success,atTime) "
+ sql+="VALUES ('${script}','${operation}','${success}',now()) "
+ sql+="ON DUPLICATE KEY UPDATE operation=values(operation), success=values(success), atTime=values(atTime);"
+
+ ${MYSQL} --execute "${sql}"
+ return $?
+}
+
+
+#####################################################
+# execute sql script
+#####################################################
+
+function run_script
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} $* --"
+ set -x
+ fi
+
+ local operation="${1}" script="${2}" scriptPath="${3}"
+
+ echo
+ echo "> ${operation} ${script}"
+
+ ${MYSQL} --verbose < "${scriptPath}"
+ local rc=$?
+ if [[ ${rc} != 0 ]]; then
+ success="0"
+ else
+ success="1"
+ fi
+
+ track_script "${script}" "${operation}" "${success}"
+
+ return ${rc}
+}
+
+#####################################################
+# upgrade
+#####################################################
+
+function upgrade
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local sqlName sqlFile schemaVersion upgradeSqls rc
+
+ ${MYSQL} --execute "USE ${SCHEMA_DB}"
+
+ echo "upgrade: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
+
+ if [[ ${CURRENT_RELEASE} < ${TARGET_UPGRADE_RELEASE} ]]; then
+ upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*"${UPGRADE_SQL_SUFFIX}" 2> /dev/null)
+ for sqlFile in ${upgradeSqls}; do
+ sqlName=$(basename "${sqlFile}")
+ schemaVersion="${sqlName%-*}"
+ if [ "${schemaVersion}" -gt "${CURRENT_RELEASE}" ] && \
+ [ "${schemaVersion}" -le "${TARGET_UPGRADE_RELEASE}" ]; then
+ run_script "upgrade" "${sqlName}" "${sqlFile}"
+ rc=$?
+ if [[ ${rc} != 0 ]]; then
+ echo "${SCHEMA}: upgrade aborted at ${schemaVersion} by script ${sqlName}"
+ set_current_release "${schemaVersion}"
+ return ${rc}
+ fi
+ fi
+ done
+
+ set_current_release "${TARGET_UPGRADE_RELEASE}"
+ fi
+
+ return 0
+}
+
+#####################################################
+# downgrade
+#####################################################
+
+function downgrade
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local sqlName sqlFile schemaVersion downgradeSqls rc
+
+ ${MYSQL} --execute "USE ${SCHEMA_DB}"
+
+ echo "downgrade: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
+
+ if [[ ${CURRENT_RELEASE} > ${TARGET_DOWNGRADE_RELEASE} ]]; then
+ downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*"${DOWNGRADE_SQL_SUFFIX}" 2> /dev/null)
+ for sqlFile in ${downgradeSqls}; do
+ sqlName=$(basename "${sqlFile}")
+ schemaVersion="${sqlName%-*}"
+ if [ "${schemaVersion}" -le "${CURRENT_RELEASE}" ] && \
+ [ "${schemaVersion}" -gt "${TARGET_DOWNGRADE_RELEASE}" ]; then
+ run_script "downgrade" "${sqlName}" "${sqlFile}"
+ rc=$?
+ if [[ ${rc} != 0 ]]; then
+ echo "${SCHEMA}: downgrade aborted at ${schemaVersion} by script ${sqlName}"
+ set_current_release "${schemaVersion}"
+ return ${rc}
+ fi
+ fi
+ done
+
+ set_current_release "${TARGET_DOWNGRADE_RELEASE}"
+ fi
+
+ return 0
+}
+
+#####################################################
+# erase
+#####################################################
+
+function erase
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local updateMetadata="UPDATE ${METADATA_TABLE} SET version='${ZERO_VERSION}';"
+ ${MYSQL} --execute "${updateMetadata}"
+
+ local deleteHistory="DELETE FROM ${METADATA_HISTORY};"
+ ${MYSQL} --execute "${deleteHistory}"
+
+ local dropDB="DROP DATABASE IF EXISTS ${SCHEMA_DB}";
+ ${MYSQL} --execute "${dropDB}"
+}
+
+#####################################################
+# report
+#####################################################
+
+function report
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ set -x
+ fi
+
+ local versionSql="SELECT * FROM ${METADATA_TABLE} WHERE name='${SCHEMA}';"
+ ${MYSQL} --execute "${versionSql}"
+
+ local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY atTime ASC;"
+ ${MYSQL} --execute "${historySql}"
+
+ okay
+}
+
+function okay
+{
+ if [[ ${DEBUG} == y ]]; then
+ echo "-- ${FUNCNAME[0]} --"
+ 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
+ TARGET_RELEASE=$1
+ ;;
+ -f|--from) shift
+ CURRENT_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/base.conf
+
+if [[ -z ${SQL_HOST} ]] || [[ -z ${SQL_USER} ]] || [[ -z ${SQL_PASSWORD} ]]; then
+ echo "error: no database has been set up"
+ exit 4
+fi
+
+MYSQL="mysql -u${SQL_USER} -p${SQL_PASSWORD} -h ${SQL_HOST}";
+if ! ${MYSQL} -h"${SQL_HOST}" --execute "show databases;" > /dev/null 2>&1; 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}"/sql
+ DOWNGRADE_DIR=${UPGRADE_DIR}
+ METADATA_HISTORY="${METADATA_DB}.\`${SCHEMA}_history\`"
+
+ 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
+
+ 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}