diff options
| -rwxr-xr-x | csit/config/db/db.sh | 2 | ||||
| -rw-r--r-- | policy-db-migrator/src/main/docker/db-migrator | 109 |
2 files changed, 87 insertions, 24 deletions
diff --git a/csit/config/db/db.sh b/csit/config/db/db.sh index e6cd5617..1afaebc7 100755 --- a/csit/config/db/db.sh +++ b/csit/config/db/db.sh @@ -13,7 +13,7 @@ # See the License for the specific language governing permissions and # limitations under the License. -for db in support onap_sdk log migration operationshistory10 pooling policyadmin operationshistory controlloop +for db in migration pooling policyadmin operationshistory do mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" --execute "CREATE DATABASE IF NOT EXISTS ${db};" mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" --execute "GRANT ALL PRIVILEGES ON \`${db}\`.* TO '${MYSQL_USER}'@'%' ;" 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})" |