policy-management/src/main/server-gen/bin/db-migrator

   1 #!/bin/bash
   2 #
   3 # ============LICENSE_START=======================================================
   4 # ONAP
   5 # ================================================================================
   6 # Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
   7 # ================================================================================
   8 # Licensed under the Apache License, Version 2.0 (the "License");
   9 # you may not use this file except in compliance with the License.
  10 # You may obtain a copy of the License at
  11 #
  12 #      http://www.apache.org/licenses/LICENSE-2.0
  13 #
  14 # Unless required by applicable law or agreed to in writing, software
  15 # distributed under the License is distributed on an "AS IS" BASIS,
  16 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  17 # See the License for the specific language governing permissions and
  18 # limitations under the License.
  19 # ============LICENSE_END=========================================================
  20
  21 # #####################################################################
  22 #
  23 # Upgrade/Downgrade SQL File Name Format:
  24 #
  25 #       <VERSION>-<pdp|feature-name>[-description](.upgrade|.downgrade).sql
  26 #
  27 # This tool operates on a migration working directory at
  28 #
  29 #       $POLICY_HOME/etc/db/migration
  30 #
  31 # Upgrade/Downgrade files for each schema (aka database) names to be maintained
  32 # by this tool are located at
  33 #
  34 #       $POLICY_HOME/etc/db/migration/<schema-name>/sql
  35 #
  36 # The nature of the migration directories is dynamic.
  37 # Other tooling aware of when migrations are needed are in charge to populate
  38 # the migrations directory accordingly.
  39 #
  40 # One of these tools is the 'features' when a feature with DB requirements
  41 # is 'enabled', the upgrade scripts will be made present in the migration directory.
  42 # When a features is 'disabled' downgrade scripts will be made available in the
  43 # migration directory.
  44 #
  45 # The 'policy' tool via its operations 'status' or 'start' will signal the
  46 # need to perform upgrade or downgrade for a given schema.
  47 #
  48 # At any given time the following invariant must be preserved in any given
  49 # $POLICY_HOME/etc/db/migration/<schema-name>/sql directory
  50 #
  51 #       There is only upgrade scripts, or only downgrade scripts, or none.
  52 #
  53 # #####################################################################
  54
  55 METADATA_DB=migration
  56 METADATA_TABLE="${METADATA_DB}".metadata_versions
  57 MIGRATION_DIR="${POLICY_HOME}"/etc/db/migration
  58
  59 ZERO_VERSION="0"
  60 UPGRADE_SQL_SUFFIX=".upgrade.sql"
  61 DOWNGRADE_SQL_SUFFIX=".downgrade.sql"
  62
  63 SQL_QUOTES="SET SESSION SQL_MODE=ANSI_QUOTES;"
  64
  65 #####################################################
  66 # usage
  67 #####################################################
  68
  69 function usage() {
  70         echo
  71         echo -e "syntax: $(basename "$0") "
  72         echo -e "\t -s <schema-name> "
  73         echo -e "\t [-b <migration-dir>] "
  74         echo -e "\t [-f <from-version>]"
  75         echo -e "\t [-t <target-version>]"
  76         echo -e "\t -o <operations> "
  77         echo
  78         echo -e "\t where <operations>=upgrade|downgrade|auto|version|erase|report"
  79         echo
  80         echo
  81         echo -e "Configuration Options:"
  82         echo -e "\t -s|--schema|--database:  schema to operate on ('ALL' to apply on all)"
  83         echo -e "\t -b|--basedir: overrides base DB migration directory"
  84         echo -e "\t -f|--from: overrides current release version for operations"
  85         echo -e "\t -t|--target: overrides target release to upgrade/downgrade"
  86         echo
  87         echo -e "Operations:"
  88         echo -e "\t upgrade: upgrade operation"
  89         echo -e "\t downgrade: performs a downgrade operation"
  90         echo -e "\t auto: autonomous operation, determines upgrade or downgrade"
  91         echo -e "\t version: returns current version, and in conjunction if '-f' sets the current version"
  92         echo -e "\t erase: erase all data related <schema> (use with care)"
  93         echo -e "\t report: migration detailed report on an schema"
  94         echo -e "\t ok: is the migration status valid"
  95         echo
  96         echo
  97 }
  98
  99 #####################################################
 100 # ensure global metadata
 101 #####################################################
 102
 103 function ensure_metadata
 104 {
 105         if [[ ${DEBUG} == y ]]; then
 106                 echo "-- ${FUNCNAME[0]} --"
 107                 set -x
 108         fi
 109
 110         local sql rc
 111
 112     sql="CREATE DATABASE IF NOT EXISTS ${METADATA_DB};"
 113         ${MYSQL} --execute "${sql}"
 114         rc=$?
 115         if [[ ${rc} != 0 ]]; then
 116                 return ${rc}
 117         fi
 118
 119     sql="CREATE TABLE IF NOT EXISTS ${METADATA_TABLE} "
 120     sql+="(name VARCHAR(60) NOT NULL, version VARCHAR(20), PRIMARY KEY(name));"
 121         ${MYSQL} --execute "${sql}"
 122         return $?
 123 }
 124
 125
 126 #####################################################
 127 # ensure metadata on a per schema basis
 128 #####################################################
 129
 130 function ensure_metadata_schema
 131 {
 132         if [[ ${DEBUG} == y ]]; then
 133                 echo "-- ${FUNCNAME[0]} --"
 134                 set -x
 135         fi
 136
 137         local sql rc
 138
 139         sql="CREATE TABLE IF NOT EXISTS ${METADATA_HISTORY} "
 140         sql+="(script VARCHAR(80) NOT NULL, operation VARCHAR(10), success VARCHAR(1), "
 141         sql+="atTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, "
 142         sql+="PRIMARY KEY(script));"
 143         ${MYSQL} --execute "${sql}"
 144         rc=$?
 145         if [[ ${rc} != 0 ]]; then
 146                 return ${rc}
 147         fi
 148
 149         sql="CREATE DATABASE IF NOT EXISTS ${SCHEMA_DB};"
 150         ${MYSQL} --execute "${sql}"
 151         return $?
 152 }
 153
 154
 155 #####################################################
 156 # target_release
 157 #####################################################
 158
 159 function target_release
 160 {
 161         if [[ ${DEBUG} == y ]]; then
 162                 echo "-- ${FUNCNAME[0]} --"
 163                 set -x
 164         fi
 165
 166         local sql sqlName upgradeSqls downgradeSqls
 167
 168         TARGET_UPGRADE_RELEASE=${ZERO_VERSION}
 169         TARGET_DOWNGRADE_RELEASE=${ZERO_VERSION}
 170
 171         upgradeSqls=$(ls -v -r "${UPGRADE_DIR}"/*"${UPGRADE_SQL_SUFFIX}" 2> /dev/null)
 172         for sql in ${upgradeSqls}; do
 173                 sqlName=$(basename "${sql}")
 174                 TARGET_UPGRADE_RELEASE="${sqlName%-*}"
 175                 break
 176         done
 177
 178         # default unless overriden
 179         TARGET_DOWNGRADE_RELEASE="${ZERO_VERSION}"
 180 }
 181
 182 #####################################################
 183 # is_upgrade
 184 #####################################################
 185
 186 function is_upgrade
 187 {
 188         if [[ ${DEBUG} == y ]]; then
 189                 echo "-- ${FUNCNAME[0]} --"
 190                 set -x
 191         fi
 192
 193         local upgradeSqls
 194
 195         upgradeSqls=$(ls "${UPGRADE_DIR}"/*"${UPGRADE_SQL_SUFFIX}" 2> /dev/null)
 196         if [[ -z ${upgradeSqls} ]]; then
 197                 return 1
 198         else
 199                 return 0
 200         fi
 201 }
 202
 203
 204 #####################################################
 205 # is_downgrade
 206 #####################################################
 207
 208 function is_downgrade
 209 {
 210         if [[ ${DEBUG} == y ]]; then
 211                 echo "-- ${FUNCNAME[0]} --"
 212                 set -x
 213         fi
 214
 215         local downgradeSqls
 216
 217         downgradeSqls=$(ls "${DOWNGRADE_DIR}"/*"${DOWNGRADE_SQL_SUFFIX}" 2> /dev/null)
 218         if [[ -z ${downgradeSqls} ]]; then
 219                 return 1
 220         else
 221                 return 0
 222         fi
 223 }
 224
 225
 226 #####################################################
 227 # current_release
 228 #####################################################
 229
 230 function current_release
 231 {
 232         if [[ ${DEBUG} == y ]]; then
 233                 echo "-- ${FUNCNAME[0]} --"
 234                 set -x
 235         fi
 236
 237         local rc
 238         local query="SELECT version FROM ${METADATA_TABLE} WHERE name='${SCHEMA}'"
 239
 240         CURRENT_RELEASE=$(${MYSQL} --skip-column-names --silent --execute "${query}")
 241         if [[ -z ${CURRENT_RELEASE} ]]; then
 242                 set_current_release "${ZERO_VERSION}"
 243                 return $?
 244         fi
 245
 246         return 0
 247 }
 248
 249
 250 #####################################################
 251 # set_current_release
 252 #####################################################
 253
 254 function set_current_release
 255 {
 256         if [[ ${DEBUG} == y ]]; then
 257                 echo "-- ${FUNCNAME[0]} $* --"
 258                 set -x
 259         fi
 260
 261         CURRENT_RELEASE="${1}"
 262
 263         local sql="INSERT INTO ${METADATA_TABLE} (name, version) "
 264         sql+="VALUES('${SCHEMA}', '${CURRENT_RELEASE}') "
 265         sql+="ON DUPLICATE KEY UPDATE version='${CURRENT_RELEASE}';"
 266
 267         ${MYSQL} --execute "${sql}"
 268         return $?
 269 }
 270
 271
 272 #####################################################
 273 # execute sql script history
 274 #####################################################
 275
 276 function track_script
 277 {
 278         if [[ ${DEBUG} == y ]]; then
 279                 echo "-- ${FUNCNAME[0]} $* --"
 280                 set -x
 281         fi
 282
 283         local script="${1}" operation="${2}" success="${3}"
 284         local sql="INSERT INTO ${METADATA_HISTORY}(script,operation,success,atTime) "
 285         sql+="VALUES ('${script}','${operation}','${success}',now()) "
 286         sql+="ON DUPLICATE KEY UPDATE operation=values(operation), success=values(success), atTime=values(atTime);"
 287
 288         ${MYSQL} --execute "${sql}"
 289         return $?
 290 }
 291
 292
 293 #####################################################
 294 # execute sql script
 295 #####################################################
 296
 297 function run_script
 298 {
 299         if [[ ${DEBUG} == y ]]; then
 300                 echo "-- ${FUNCNAME[0]} $* --"
 301                 set -x
 302         fi
 303
 304         local operation="${1}" script="${2}" scriptPath="${3}"
 305
 306         echo
 307         echo "> ${operation} ${script}"
 308
 309         ${MYSQL} --verbose < "${scriptPath}"
 310         local rc=$?
 311         if [[ ${rc} != 0 ]]; then
 312                 success="0"
 313         else
 314                 success="1"
 315         fi
 316
 317         track_script "${script}" "${operation}" "${success}"
 318
 319         return ${rc}
 320 }
 321
 322 #####################################################
 323 # upgrade
 324 #####################################################
 325
 326 function upgrade
 327 {
 328         if [[ ${DEBUG} == y ]]; then
 329                 echo "-- ${FUNCNAME[0]} --"
 330                 set -x
 331         fi
 332
 333         local sqlName sqlFile schemaVersion upgradeSqls rc
 334
 335         ${MYSQL} --execute "USE ${SCHEMA_DB}"
 336
 337         echo "upgrade: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
 338
 339         if [[ ${CURRENT_RELEASE} < ${TARGET_UPGRADE_RELEASE} ]]; then
 340                 upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*"${UPGRADE_SQL_SUFFIX}" 2> /dev/null)
 341             for sqlFile in ${upgradeSqls}; do
 342                 sqlName=$(basename "${sqlFile}")
 343                         schemaVersion="${sqlName%-*}"
 344                 if [ "${schemaVersion}" -gt "${CURRENT_RELEASE}" ] && \
 345                         [ "${schemaVersion}" -le "${TARGET_UPGRADE_RELEASE}" ]; then
 346                         run_script "upgrade" "${sqlName}" "${sqlFile}"
 347                         rc=$?
 348                                 if [[ ${rc} != 0 ]]; then
 349                                 echo "${SCHEMA}: upgrade aborted at ${schemaVersion} by script ${sqlName}"
 350                                 set_current_release "${schemaVersion}"
 351                                 return ${rc}
 352                         fi
 353                 fi
 354             done
 355
 356         set_current_release "${TARGET_UPGRADE_RELEASE}"
 357         fi
 358
 359         return 0
 360 }
 361
 362 #####################################################
 363 # downgrade
 364 #####################################################
 365
 366 function downgrade
 367 {
 368         if [[ ${DEBUG} == y ]]; then
 369                 echo "-- ${FUNCNAME[0]} --"
 370                 set -x
 371         fi
 372
 373         local sqlName sqlFile schemaVersion downgradeSqls rc
 374
 375         ${MYSQL} --execute "USE ${SCHEMA_DB}"
 376
 377         echo "downgrade: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
 378
 379         if [[ ${CURRENT_RELEASE} > ${TARGET_DOWNGRADE_RELEASE} ]]; then
 380                 downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*"${DOWNGRADE_SQL_SUFFIX}" 2> /dev/null)
 381                 for sqlFile in ${downgradeSqls}; do
 382                 sqlName=$(basename "${sqlFile}")
 383                         schemaVersion="${sqlName%-*}"
 384                 if [ "${schemaVersion}" -le "${CURRENT_RELEASE}" ] && \
 385                         [ "${schemaVersion}" -gt "${TARGET_DOWNGRADE_RELEASE}" ]; then
 386                         run_script "downgrade" "${sqlName}" "${sqlFile}"
 387                         rc=$?
 388                                 if [[ ${rc} != 0 ]]; then
 389                                 echo "${SCHEMA}: downgrade aborted at ${schemaVersion} by script ${sqlName}"
 390                                 set_current_release "${schemaVersion}"
 391                                 return ${rc}
 392                         fi
 393                 fi
 394             done
 395
 396         set_current_release "${TARGET_DOWNGRADE_RELEASE}"
 397         fi
 398
 399         return 0
 400 }
 401
 402 #####################################################
 403 # erase
 404 #####################################################
 405
 406 function erase
 407 {
 408         if [[ ${DEBUG} == y ]]; then
 409                 echo "-- ${FUNCNAME[0]} --"
 410                 set -x
 411         fi
 412
 413         local updateMetadata="UPDATE ${METADATA_TABLE} SET version='${ZERO_VERSION}';"
 414         ${MYSQL} --execute "${updateMetadata}"
 415
 416         local deleteHistory="DELETE FROM ${METADATA_HISTORY};"
 417         ${MYSQL} --execute "${deleteHistory}"
 418
 419         local dropDB="DROP DATABASE IF EXISTS ${SCHEMA_DB}";
 420         ${MYSQL} --execute "${dropDB}"
 421 }
 422
 423 #####################################################
 424 # report
 425 #####################################################
 426
 427 function report
 428 {
 429         if [[ ${DEBUG} == y ]]; then
 430                 echo "-- ${FUNCNAME[0]} --"
 431                 set -x
 432         fi
 433
 434         local versionSql="SELECT * FROM ${METADATA_TABLE} WHERE name='${SCHEMA}';"
 435         ${MYSQL} --execute "${versionSql}"
 436
 437         local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY atTime ASC;"
 438         ${MYSQL} --execute "${historySql}"
 439
 440         okay
 441 }
 442
 443 function okay
 444 {
 445         if [[ ${DEBUG} == y ]]; then
 446                 echo "-- ${FUNCNAME[0]} --"
 447                 set -x
 448         fi
 449
 450         local rc=0
 451         if is_upgrade; then
 452                 if [[ ${CURRENT_RELEASE} == "${TARGET_UPGRADE_RELEASE}" ]]; then
 453                         echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
 454                 else
 455                         echo "${SCHEMA}: upgrade available: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
 456                         rc=1
 457                 fi
 458         else
 459                 if [[ ${CURRENT_RELEASE} == "${TARGET_DOWNGRADE_RELEASE}" ]]; then
 460                         echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
 461                 else
 462                         echo "${SCHEMA}: downgrade available: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
 463                         rc=1
 464                 fi
 465         fi
 466
 467         return ${rc}
 468 }
 469
 470 #####################################################
 471 # MAIN
 472 #####################################################
 473
 474 if [[ ${DEBUG} == y ]]; then
 475         echo "-- $0 $* --"
 476         set -x
 477 fi
 478
 479 until [[ -z "$1" ]]; do
 480         case $1 in
 481                 -s|--schema|--database) shift
 482                                                                 SCHEMA=$1
 483                                                                 ;;
 484                 -b|--basedir)   shift
 485                                                 MIGRATION_DIR=$1
 486                                                 ;;
 487                 -t|--target)    shift
 488                                                 INPUT_TARGET_RELEASE=$1
 489                                                 ;;
 490                 -f|--from)      shift
 491                                                 INPUT_CURRENT_RELEASE=$1
 492                                                 ;;
 493                 -o|--operation) shift
 494                                                 OPERATION=$1
 495                                                 ;;
 496                 *)                              usage
 497                                                 exit 1
 498                                                 ;;
 499         esac
 500         shift
 501 done
 502
 503 case ${OPERATION} in
 504         upgrade)        ;;
 505         downgrade)      ;;
 506         auto)   ;;
 507         version)        ;;
 508         erase)          ;;
 509         report)         ;;
 510         ok)                     ;;
 511         *)                      echo "error: invalid operation provided"
 512                                 usage
 513                                 exit 1
 514                                 ;;
 515 esac
 516
 517 if [[ -z ${SCHEMA} ]]; then
 518         echo "error: a database name must be provided"
 519         usage
 520         exit 2
 521 fi
 522
 523 source "${POLICY_HOME}"/etc/profile.d/base.conf
 524
 525 if [[ -z ${SQL_HOST} ]] || [[ -z ${SQL_USER} ]] || [[ -z ${SQL_PASSWORD} ]]; then
 526         echo "error: no database has been set up"
 527         exit 4
 528 fi
 529
 530 MYSQL="mysql -u${SQL_USER} -p${SQL_PASSWORD} -h ${SQL_HOST}";
 531 if ! ${MYSQL} -h"${SQL_HOST}" --execute "show databases;" > /dev/null 2>&1; then
 532         echo "error: No DB connectivity to ${SQL_HOST} for ${SQL_USER}"
 533         exit 5
 534 fi
 535
 536 if [[ ${SCHEMA} == ALL ]]; then
 537         SCHEMA="*"
 538 fi
 539
 540 SCHEMA_S=$(ls -d "${MIGRATION_DIR}"/${SCHEMA}/ 2> /dev/null)
 541 if [[ -z ${SCHEMA_S} ]]; then
 542         echo "error: no databases available"
 543         exit 0
 544 fi
 545
 546 if ! ensure_metadata; then
 547         echo "error: migration metadata not accessible"
 548         exit 7
 549 fi
 550
 551 rc=0
 552 for dbPath in ${SCHEMA_S}; do
 553         SCHEMA=$(basename "${dbPath}")
 554         SCHEMA_DB="\`${SCHEMA}\`"
 555         UPGRADE_DIR="${MIGRATION_DIR}"/"${SCHEMA}"/sql
 556         DOWNGRADE_DIR=${UPGRADE_DIR}
 557         METADATA_HISTORY="${METADATA_DB}.\`${SCHEMA}_history\`"
 558         TARGET_RELEASE=${INPUT_TARGET_RELEASE}
 559         CURRENT_RELEASE=${INPUT_CURRENT_RELEASE}
 560
 561         if is_upgrade && is_downgrade; then
 562                 echo "${SCHEMA}: failure: invalid configuration: ${UPGRADE_SQL_SUFFIX} and "\
 563                         "${DOWNGRADE_SQL_SUFFIX} exist under ${DOWNGRADE_DIR}"
 564                 rc=1
 565                 continue
 566         fi
 567
 568         if [[ ${operation} == auto ]]; then
 569                 if is_upgrade; then
 570                         operation=upgrade
 571                 else
 572                         operation=downgrade
 573                 fi
 574         fi
 575
 576         if ! ensure_metadata_schema; then
 577                 echo "${SCHEMA}: failure: metadata not accessible for this schema"
 578                 continue
 579         fi
 580
 581         if [[ -z ${TARGET_RELEASE} ]]; then
 582                 target_release
 583         else
 584                 # user asked to override
 585                 TARGET_UPGRADE_RELEASE="${TARGET_RELEASE}"
 586                 TARGET_DOWNGRADE_RELEASE="${TARGET_RELEASE}"
 587         fi
 588
 589         if [[ -z ${CURRENT_RELEASE} ]]; then
 590                 if ! current_release; then
 591                         echo "${SCHEMA}: failure: cannot obtain current release"
 592                         continue
 593                 fi
 594         else
 595                 if ! set_current_release "${CURRENT_RELEASE}"; then
 596                         echo "${SCHEMA}: failure: cannot set current release"
 597                         continue
 598                 fi
 599         fi
 600
 601         case ${OPERATION} in
 602                 upgrade)        if upgrade; then
 603                                                 echo "${SCHEMA}: OK: upgrade (${CURRENT_RELEASE})"
 604                                         else
 605                                                 rc=1
 606                                                 echo "${SCHEMA}: failure: upgrade to release ${TARGET_UPGRADE_RELEASE} (${CURRENT_RELEASE})"
 607                                         fi
 608                                         ;;
 609                 downgrade)  if downgrade; then
 610                                                 echo "${SCHEMA}: OK: downgrade (${CURRENT_RELEASE})"
 611                                         else
 612                                                 rc=1
 613                                                 echo "${SCHEMA}: failure: downgrade to release ${TARGET_DOWNGRADE_RELEASE} (${CURRENT_RELEASE})"
 614                                         fi
 615                                         ;;
 616                 version)        echo "${SCHEMA}: ${CURRENT_RELEASE}"
 617                                         ;;
 618                 erase)          erase
 619                                         ;;
 620                 report)         report
 621                                         ;;
 622                 ok)             okay
 623                                         ;;
 624         esac
 625
 626 done
 627 exit ${rc}