policy-db-migrator/src/main/docker/db-migrator-pg

   1 #!/usr/bin/env sh
   2
   3 # ============LICENSE_START=======================================================
   4 # ONAP
   5 # ================================================================================
   6 # Copyright (C) 2022 Nordix Foundation.
   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 # A number of environment variables needs to be initialized
  38 # prior to running db-migrator.
  39 # These vaiables specifiy the connection details for the database
  40 # to be upgraded/downgraded.
  41 #
  42 # The repository of upgrade/downgrade scripts is located in the
  43 # /home/policy/sql directory.
  44 # Two additional scripts have been provided to prepare the directories/files
  45 # needed to perform the upgrade/downgrade.
  46 #
  47 # At any given time the following invariant must be preserved in any given
  48 # $POLICY_HOME/etc/db/migration/<schema-name>/sql directory
  49 #
  50 #     There is only upgrade scripts, or only downgrade scripts, or none.
  51 #
  52 # #####################################################################
  53
  54 source ${POLICY_HOME}/etc/profile.d/env.sh
  55
  56 METADATA_DB=migration
  57 METADATA_TABLE=schema_versions
  58 MIGRATION_DIR=${POLICY_HOME}/etc/db/migration
  59 ZERO_VERSION="0"
  60 BASE_VERSION="0800"
  61 UPGRADE_SQL_SUFFIX=".upgrade.sql"
  62 DOWNGRADE_SQL_SUFFIX=".downgrade.sql"
  63
  64 SQL_QUOTES="SET SESSION SQL_MODE=ANSI_QUOTES;"
  65
  66 #####################################################
  67 # usage
  68 #####################################################
  69
  70 function usage() {
  71     echo
  72     echo -e "syntax: $(basename "$0") "
  73     echo -e "\t -s <schema-name> "
  74     echo -e "\t [-b <migration-dir>] "
  75     echo -e "\t [-f <from-version>]"
  76     echo -e "\t [-t <target-version>]"
  77     echo -e "\t -o <operations> "
  78     echo
  79     echo -e "\t where <operations>=upgrade|downgrade|auto|version|erase|report"
  80     echo
  81     echo
  82     echo -e "Configuration Options:"
  83     echo -e "\t -s|--schema|--database:  schema to operate on ('ALL' to apply on all)"
  84     echo -e "\t -b|--basedir: overrides base DB migration directory"
  85     echo -e "\t -f|--from: overrides current release version for operations"
  86     echo -e "\t -t|--target: overrides target release to upgrade/downgrade"
  87     echo
  88     echo -e "Operations:"
  89     echo -e "\t upgrade: upgrade operation"
  90     echo -e "\t downgrade: performs a downgrade operation"
  91     echo -e "\t auto: autonomous operation, determines upgrade or downgrade"
  92     echo -e "\t version: returns current version, and in conjunction if '-f' sets the current version"
  93     echo -e "\t erase: erase all data related <schema> (use with care)"
  94     echo -e "\t report: migration detailed report on an schema"
  95     echo -e "\t ok: is the migration status valid"
  96     echo
  97     echo
  98 }
  99
 100 #####################################################
 101 # current_schema
 102 #####################################################
 103
 104 function current_schema() {
 105     if [ "${DEBUG}" = "y" ]; then
 106         echo "-- current_schema --"
 107         set -x
 108     fi
 109
 110     local rc
 111     local query="SELECT count(table_name) from information_schema.tables where table_schema='${SCHEMA}'"
 112
 113     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
 114     TABLES_IN_SCHEMA=$(${PSQL} -X -A --tuples-only --quiet --command "${query}")
 115     if [ ${TABLES_IN_SCHEMA} -gt 0 ] && [ "${BASE_VERSION}" \> "${CURRENT_RELEASE}" ]; then
 116         set_current_release "${BASE_VERSION}"
 117         return $?
 118     fi
 119
 120     return 0
 121 }
 122
 123 #####################################################
 124 # ensure global metadata
 125 #####################################################
 126
 127 function ensure_metadata() {
 128     if [ "${DEBUG}" = "y" ]; then
 129         echo "-- ensure_metadata --"
 130         set -x
 131     fi
 132
 133     local sql rc
 134
 135     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 136     sql="SELECT 'CREATE DATABASE migration' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'migration');"
 137     ${PSQL} --command "${sql}"
 138     rc=$?
 139     if [ ${rc} -ne 0 ]; then
 140         return ${rc}
 141     fi
 142
 143     sql="CREATE TABLE IF NOT EXISTS ${METADATA_TABLE} "
 144     sql=${sql}"(name VARCHAR(60) NOT NULL, version VARCHAR(20), "
 145     sql=${sql}"PRIMARY KEY(name));"
 146     ${PSQL} --command "${sql}"
 147     return $?
 148 }
 149
 150 #####################################################
 151 # ensure metadata on a per schema basis
 152 #####################################################
 153
 154 function ensure_metadata_schema() {
 155     if [ "${DEBUG}" = "y" ]; then
 156         echo "-- ensure_metadata_schema --"
 157         set -x
 158     fi
 159
 160     local sql rc
 161
 162     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 163     sql="CREATE TABLE IF NOT EXISTS ${METADATA_HISTORY} "
 164     sql=${sql}"(ID bigserial, script VARCHAR(80) NOT NULL, "
 165     sql=${sql}"operation VARCHAR(10), from_version VARCHAR(20), to_version VARCHAR(20), tag VARCHAR(20), "
 166     sql=${sql}"success VARCHAR(1), atTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(ID));"
 167     ${PSQL} --command "${sql}"
 168     rc=$?
 169     if [ ${rc} -ne 0 ]; then
 170         return ${rc}
 171     fi
 172
 173     sql="SELECT 'CREATE DATABASE migration' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'policyadmin');"
 174     ${PSQL} --command "${sql}"
 175     return $?
 176 }
 177
 178 #####################################################
 179 # target_release
 180 #####################################################
 181
 182 function target_release() {
 183     if [ "${DEBUG}" = "y" ]; then
 184         echo "-- target_release --"
 185         set -x
 186     fi
 187
 188     local sql sqlName upgradeSqls downgradeSqls
 189
 190     TARGET_UPGRADE_RELEASE=${ZERO_VERSION}
 191     TARGET_DOWNGRADE_RELEASE=${ZERO_VERSION}
 192
 193     upgradeSqls=$(ls -v -r "${UPGRADE_DIR}"/*/upgrade/*.sql 2>/dev/null)
 194     for sql in ${upgradeSqls}; do
 195         TARGET_UPGRADE_RELEASE="$(basename $(dirname $(dirname $sql)))"
 196         break
 197     done
 198
 199     # default unless overriden
 200     TARGET_DOWNGRADE_RELEASE="${ZERO_VERSION}"
 201 }
 202
 203 #####################################################
 204 # is_upgrade
 205 #####################################################
 206
 207 function is_upgrade() {
 208     if [ "${DEBUG}" = "y" ]; then
 209         echo "-- is_upgrade --"
 210         set -x
 211     fi
 212
 213     local upgradeSqls
 214
 215     upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*/upgrade/*.sql 2>/dev/null)
 216     if [ -z "${upgradeSqls}" ]; then
 217         return 1
 218     else
 219         return 0
 220     fi
 221 }
 222
 223 #####################################################
 224 # is_downgrade
 225 #####################################################
 226
 227 function is_downgrade() {
 228     if [ "${DEBUG}" = "y" ]; then
 229         echo "-- is_downgrade --"
 230         set -x
 231     fi
 232
 233     local downgradeSqls
 234
 235     downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*/downgrade/*.sql 2>/dev/null)
 236     if [ -z "${downgradeSqls}" ]; then
 237         return 1
 238     else
 239         return 0
 240     fi
 241 }
 242
 243 #####################################################
 244 # set_current_release
 245 #####################################################
 246
 247 function set_current_release() {
 248     if [ "${DEBUG}" = "y" ]; then
 249         echo "-- set_current_release --"
 250         set -x
 251     fi
 252
 253     CURRENT_RELEASE="${1}"
 254
 255     local sql
 256     sql="INSERT INTO ${METADATA_TABLE} (name, version) "
 257     sql=${sql}"VALUES('${SCHEMA}', '${CURRENT_RELEASE}') "
 258     sql=${sql}"ON CONFLICT (name) DO UPDATE SET version='${CURRENT_RELEASE}';"
 259
 260     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 261     ${PSQL} --command "${sql}"
 262     return $?
 263 }
 264
 265 #####################################################
 266 # current_release
 267 #####################################################
 268
 269 function current_release() {
 270     if [ "${DEBUG}" = "y" ]; then
 271         echo "-- current_release --"
 272         set -x
 273     fi
 274
 275     local rc
 276     local query="SELECT version FROM schema_versions WHERE name='${SQL_DB}'"
 277
 278     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 279     CURRENT_RELEASE=$(${PSQL} -X -A -t --command "${query}")
 280
 281     if [ -z "${CURRENT_RELEASE}" ]; then
 282         set_current_release "${ZERO_VERSION}"
 283         return $?
 284     fi
 285
 286     return 0
 287 }
 288
 289 #####################################################
 290 # previous_release
 291 #####################################################
 292
 293 function previous_release() {
 294     if [ "${DEBUG}" = "y" ]; then
 295         echo "-- previous_release --"
 296         set -x
 297     fi
 298     local current_release="${1}"
 299     local current_release_int_val previous_release_int_val previous_release
 300
 301     if [ $current_release == $ZERO_VERSION ] || [ $current_release == $BASE_VERSION ]; then
 302         PREVIOUS_RELEASE=$ZERO_VERSION
 303     else
 304         current_release_int_val=$(echo $current_release | awk '{$0=int($0)}1')
 305         let previous_release_int_val=$current_release_int_val-100
 306         if [ $previous_release_int_val -lt 1000 ]; then
 307             previous_release="0"$previous_release_int_val
 308         else
 309             previous_release=$previous_release_int_val
 310         fi
 311         PREVIOUS_RELEASE=$previous_release
 312     fi
 313
 314     return 0
 315 }
 316
 317 #####################################################
 318 # last_operation_status
 319 #####################################################
 320
 321 function last_operation_status() {
 322     if [ "${DEBUG}" = "y" ]; then
 323         echo "-- last_operation_status --"
 324         set -x
 325     fi
 326
 327     local query rc row
 328
 329     query="SELECT script, operation, from_version, to_version, success FROM  "
 330     query=${query}"${METADATA_HISTORY} WHERE id="
 331     query=${query}"(SELECT MAX(id) from ${METADATA_HISTORY}) "
 332     query=${query}"AND success::integer=0"
 333
 334     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 335     row=$(${PSQL} --tuples-only --quiet --command "${query}")
 336
 337     if [ "${row}" != "" ]; then
 338         LAST_SCRIPT=$(echo $row | awk '{print $1}')
 339         LAST_OPERATION=$(echo $row | awk '{print $2}')
 340         LAST_FROM_VERSION=$(echo $row | awk '{print $3}')
 341         LAST_TO_VERSION=$(echo $row | awk '{print $4}')
 342         LAST_STATUS=$(echo $row | awk '{print $5}')
 343         rc=0
 344     else
 345         rc=1
 346     fi
 347
 348     return $rc
 349 }
 350
 351 #####################################################
 352 # execute sql script history
 353 #####################################################
 354
 355 function track_script() {
 356     if [ "${DEBUG}" = "y" ]; then
 357         echo "-- track_script $* --"
 358         set -x
 359     fi
 360
 361     local script="${1}" operation="${2}" success="${3}" from_version="${4}" to_version="${5}" tag="${6}"
 362     if [ $operation == "downgrade" ]; then
 363         to_version=${TARGET_DOWNGRADE_RELEASE}
 364     fi
 365
 366     local sql="INSERT INTO ${METADATA_HISTORY}(script,operation,from_version,to_version,tag,success,atTime) "
 367     sql=${sql}"VALUES ('${script}','${operation}','${from_version}','${to_version}','${tag}','${success}',now()) "
 368
 369     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 370     ${PSQL} --command "${sql}"
 371     return $?
 372 }
 373
 374 #####################################################
 375 # execute sql script
 376 #####################################################
 377
 378 function run_script() {
 379     if [ "${DEBUG}" == "y" ]; then
 380         echo "-- run_script $* --"
 381         set -x
 382     fi
 383
 384     local operation="${1}" script="${2}" scriptPath="${3}" fromVersion="${4}" toVersion="${5}" tag="${6}"
 385
 386     echo
 387     echo "> ${operation} ${script}"
 388
 389     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
 390     ${PSQL} <"${scriptPath}"
 391     local rc=$?
 392     if [ ${rc} -ne 0 ]; then
 393         success="0"
 394     else
 395         success="1"
 396     fi
 397
 398     track_script "${script}" "${operation}" "${success}" "${fromVersion}" "${toVersion}" "${tag}"
 399
 400     return ${rc}
 401 }
 402
 403 #####################################################
 404 # upgrade
 405 #####################################################
 406
 407 function upgrade() {
 408     if [ "${DEBUG}" = "y" ]; then
 409         echo "-- upgrade --"
 410         set -x
 411     fi
 412
 413     local sqlName sqlFile schemaVersion upgradeSqls rc tagDate tag
 414     tagDate=$(date +%d%m%y%H%M%S)
 415
 416     echo "upgrade: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
 417
 418     if [ ${CURRENT_RELEASE} \< ${TARGET_UPGRADE_RELEASE} ]; then
 419         upgradeSqls=$(ls -v "${UPGRADE_DIR}"/*/upgrade/*.sql 2>/dev/null)
 420         for sqlFile in ${upgradeSqls}; do
 421             sqlName=$(basename "${sqlFile}")
 422             schemaVersion="$(basename $(dirname $(dirname $sqlFile)))"
 423             previous_release $schemaVersion
 424             if [ "${schemaVersion}" -gt "${CURRENT_RELEASE}" ] &&
 425                 [ "${schemaVersion}" -le "${TARGET_UPGRADE_RELEASE}" ]; then
 426                 tag=${tagDate}"${schemaVersion}u"
 427                 if [ $RETRY -eq 1 ] &&
 428                     # Compare the numeric portion of the filename because shell doesn't support string comparison
 429                     [ $(echo ${sqlName} | awk -F- '{print $1}') -${COMPARE} $(echo ${LAST_SCRIPT} | awk -F- '{print $1}') ]; then
 430                     rc=1
 431                     echo "rc=1"
 432                 else
 433                     rc=0
 434                     echo "rc=0"
 435                 fi
 436                 if [ ${rc} -eq 0 ]; then
 437                     run_script "upgrade" "${sqlName}" "${sqlFile}" "${PREVIOUS_RELEASE}" "${schemaVersion}" "${tag}"
 438                     rc=$?
 439                     if [ ${rc} -ne 0 ]; then
 440                         echo "${SCHEMA}: upgrade aborted at ${schemaVersion} by script ${sqlName}"
 441                         set_current_release "${schemaVersion}"
 442                         return ${rc}
 443                     fi
 444                 fi
 445             fi
 446         done
 447
 448         set_current_release "${TARGET_UPGRADE_RELEASE}"
 449     fi
 450
 451     return 0
 452 }
 453
 454 #####################################################
 455 # downgrade
 456 #####################################################
 457
 458 function downgrade() {
 459     if [ "${DEBUG}" = "y" ]; then
 460         echo "-- downgrade --"
 461         set -x
 462     fi
 463
 464     local sqlName sqlFile schemaVersion downgradeSqls rc tagDate tag
 465     tagDate=$(date +%d%m%y%H%M%S)
 466
 467     echo "downgrade: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
 468
 469     if [ ${CURRENT_RELEASE} \> ${TARGET_DOWNGRADE_RELEASE} ]; then
 470         downgradeSqls=$(ls -v -r "${DOWNGRADE_DIR}"/*/downgrade/*.sql 2>/dev/null)
 471         for sqlFile in ${downgradeSqls}; do
 472             sqlName=$(basename "${sqlFile}")
 473             schemaVersion="$(basename $(dirname $(dirname $sqlFile)))"
 474             if [ "${schemaVersion}" -le "${CURRENT_RELEASE}" ] &&
 475                 [ "${schemaVersion}" -gt "${TARGET_DOWNGRADE_RELEASE}" ]; then
 476                 tag=${tagDate}"${schemaVersion}d"
 477                 if [ $RETRY -eq 1 ] &&
 478                     # Compare the numeric portion of the filename because shell doesn't support string comparison
 479                     [ $(echo ${sqlName} | awk -F- '{print $1}') -${COMPARE} $(echo ${LAST_SCRIPT} | awk -F- '{print $1}') ]; then
 480                     rc=1
 481                 else
 482                     rc=0
 483                 fi
 484                 if [ ${rc} -eq 0 ]; then
 485                     run_script "downgrade" "${sqlName}" "${sqlFile}" "${schemaVersion}" "${PREVIOUS_RELEASE}" ${tag}
 486                     rc=$?
 487                     if [ ${rc} -ne 0 ]; then
 488                         echo "${SCHEMA}: downgrade aborted at ${schemaVersion} by script ${sqlName}"
 489                         set_current_release "${schemaVersion}"
 490                         return ${rc}
 491                     fi
 492                 fi
 493             fi
 494         done
 495
 496         set_current_release "${TARGET_DOWNGRADE_RELEASE}"
 497     fi
 498
 499     return 0
 500 }
 501
 502 #####################################################
 503 # erase
 504 #####################################################
 505
 506 function erase() {
 507     if [ "${DEBUG}" = "y" ]; then
 508         echo "-- erase --"
 509         set -x
 510     fi
 511
 512     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 513     local updateMetadata="UPDATE ${METADATA_TABLE} SET version='${ZERO_VERSION}';"
 514     ${PSQL} --command "${updateMetadata}"
 515
 516     local deleteHistory="DELETE FROM ${METADATA_HISTORY};"
 517     ${PSQL} --command "${deleteHistory}"
 518
 519     local dropDB="DROP DATABASE IF EXISTS ${SCHEMA_DB}"
 520     ${PSQL} --command "${dropDB}"
 521 }
 522
 523 #####################################################
 524 # report
 525 #####################################################
 526
 527 function report() {
 528     if [ "${DEBUG}" = "y" ]; then
 529         echo "-- report --"
 530         set -x
 531     fi
 532
 533     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${METADATA_DB}"
 534     local versionSql="SELECT * FROM ${METADATA_TABLE} WHERE name='${SCHEMA}';"
 535     ${PSQL} --command "${versionSql}"
 536
 537     local historySql="SELECT * FROM ${METADATA_HISTORY} ORDER BY id, atTime ASC;"
 538     ${PSQL} --command "${historySql}"
 539
 540     okay
 541 }
 542
 543 function okay() {
 544     if [ "${DEBUG}" = "y" ]; then
 545         echo "-- okay --"
 546         set -x
 547     fi
 548
 549     local rc=0
 550     if is_upgrade; then
 551         if [ "${CURRENT_RELEASE}" = "${TARGET_UPGRADE_RELEASE}" ]; then
 552             echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
 553         else
 554             echo "${SCHEMA}: upgrade available: ${CURRENT_RELEASE} -> ${TARGET_UPGRADE_RELEASE}"
 555             rc=1
 556         fi
 557     else
 558         if [ "${CURRENT_RELEASE}" = "${TARGET_DOWNGRADE_RELEASE}" ]; then
 559             echo "${SCHEMA}: OK @ ${CURRENT_RELEASE}"
 560         else
 561             echo "${SCHEMA}: downgrade available: ${CURRENT_RELEASE} -> ${TARGET_DOWNGRADE_RELEASE}"
 562             rc=1
 563         fi
 564     fi
 565
 566     return ${rc}
 567 }
 568
 569 #####################################################
 570 # MAIN
 571 #####################################################
 572
 573 if [ "${DEBUG}" = "y" ]; then
 574     echo "-- $0 $* --"
 575     set -x
 576 fi
 577 until [ -z "$1" ]; do
 578     case $1 in
 579     -s | --schema | --database)
 580         shift
 581         SCHEMA=$1
 582         ;;
 583     -b | --basedir)
 584         shift
 585         MIGRATION_DIR=$1
 586         ;;
 587     -t | --target)
 588         shift
 589         INPUT_TARGET_RELEASE=$1
 590         ;;
 591     -f | --from)
 592         shift
 593         INPUT_FROM_RELEASE=$1
 594         ;;
 595     -o | --operation)
 596         shift
 597         OPERATION=$1
 598         ;;
 599     *)
 600         usage
 601         exit 1
 602         ;;
 603     esac
 604     shift
 605 done
 606
 607 case ${OPERATION} in
 608     upgrade) ;;
 609     downgrade) ;;
 610     auto) ;;
 611     version) ;;
 612     erase) ;;
 613     report) ;;
 614     ok) ;;
 615     *)
 616         echo "error: invalid operation provided"
 617         usage
 618         exit 1
 619         ;;
 620 esac
 621
 622 if [ -z "${SCHEMA}" ]; then
 623     echo "error: a database name must be provided"
 624     usage
 625     exit 2
 626 fi
 627
 628 source "${POLICY_HOME}"/etc/profile.d/env.sh
 629
 630 if [ -z "${SQL_HOST}" ] || [ -z "${SQL_USER}" ] || [ -z "${SQL_PASSWORD}" ]; then
 631     echo "error: no database has been set up"
 632     exit 4
 633 fi
 634
 635 PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB}"
 636 if ! ${PSQL} --command '\l'; then
 637     echo "error: No DB connectivity to ${SQL_HOST} for ${SQL_USER}"
 638     exit 5
 639 fi
 640
 641 if [ "${SCHEMA}" = "ALL" ]; then
 642     SCHEMA="*"
 643 fi
 644
 645 SCHEMA_S=$(ls -d "${MIGRATION_DIR}"/${SCHEMA}/ 2>/dev/null)
 646 if [ -z "${SCHEMA_S}" ]; then
 647     echo "error: no databases available"
 648     exit 0
 649 fi
 650
 651 if ! ensure_metadata; then
 652     echo "error: migration metadata not accessible"
 653     exit 7
 654 fi
 655
 656 rc=0
 657 for dbPath in ${SCHEMA_S}; do
 658     SCHEMA=$(basename "${dbPath}")
 659     SCHEMA_DB="'${SCHEMA}'"
 660     UPGRADE_DIR="${MIGRATION_DIR}"/"${SCHEMA}"/postgres
 661     DOWNGRADE_DIR=${UPGRADE_DIR}
 662     METADATA_HISTORY="${SCHEMA}_schema_changelog"
 663     TARGET_RELEASE=${INPUT_TARGET_RELEASE}
 664
 665     if is_upgrade && is_downgrade; then
 666         echo "${SCHEMA}: failure: invalid configuration: ${UPGRADE_SQL_SUFFIX} and " \
 667             "${DOWNGRADE_SQL_SUFFIX} exist under ${DOWNGRADE_DIR}"
 668         rc=1
 669         continue
 670     fi
 671
 672     if [ "${operation}" = "auto" ]; then
 673         if is_upgrade; then
 674             operation=upgrade
 675         else
 676             operation=downgrade
 677         fi
 678     fi
 679
 680     if ! ensure_metadata_schema; then
 681         echo "${SCHEMA}: failure: metadata not accessible for this schema"
 682         continue
 683     fi
 684
 685     if [ -z "${TARGET_RELEASE}" ]; then
 686         target_release
 687     else
 688         # user asked to override
 689         TARGET_UPGRADE_RELEASE="${TARGET_RELEASE}"
 690         TARGET_DOWNGRADE_RELEASE="${TARGET_RELEASE}"
 691     fi
 692
 693     if [ -z "${CURRENT_RELEASE}" ]; then
 694         if ! current_release; then
 695             echo "${SCHEMA}: failure: cannot obtain current release"
 696             continue
 697         fi
 698     else
 699         if ! set_current_release "${CURRENT_RELEASE}"; then
 700             echo "${SCHEMA}: failure: cannot set current release"
 701             continue
 702         fi
 703     fi
 704
 705     PSQL="psql -h ${SQL_HOST} -U ${SQL_USER} -d ${SQL_DB};"
 706     # Check if the schema has already been installed
 707     current_schema
 708     RETRY=0
 709     COMPARE=""
 710     last_operation_status
 711     last_status=$?
 712     if [ $last_status -eq 0 ]; then
 713         echo "Partial $LAST_OPERATION detected"
 714         RETRY=1
 715         if [ "${OPERATION}" == 'downgrade' ] && [ "${LAST_OPERATION}" == 'downgrade' ]; then
 716             CURRENT_RELEASE="${LAST_FROM_VERSION}"
 717             # greater than
 718             COMPARE="gt"
 719             echo "Downgrade will attempt to resume at $LAST_SCRIPT"
 720         elif [ "${OPERATION}" == 'downgrade' ] && [ "${LAST_OPERATION}" == 'upgrade' ]; then
 721             CURRENT_RELEASE="${LAST_TO_VERSION}"
 722             # greater than or equal to
 723             COMPARE="ge"
 724             echo "Downgrade will attempt to resume before $LAST_SCRIPT"
 725         elif [ "${OPERATION}" == 'upgrade' ] && [ "${LAST_OPERATION}" == 'upgrade' ]; then
 726             CURRENT_RELEASE="${LAST_FROM_VERSION}"
 727             # less than
 728             COMPARE="lt"
 729             echo "Upgrade will attempt to resume at $LAST_SCRIPT"
 730         else
 731             CURRENT_RELEASE="${LAST_TO_VERSION}"
 732             # less than or equal
 733             COMPARE="le"
 734             echo "Upgrade will attempt to resume after $LAST_SCRIPT"
 735         fi
 736     fi
 737
 738     if [ -n "${INPUT_FROM_RELEASE}" ]; then
 739         if [ "${CURRENT_RELEASE}" \> "${INPUT_FROM_RELEASE}" ] || [ "${CURRENT_RELEASE}" \< "${INPUT_FROM_RELEASE}" ]; then
 740             echo "${SCHEMA}: On version ${CURRENT_RELEASE} cannot ${OPERATION} from ${INPUT_FROM_RELEASE}"
 741             continue
 742         else
 743             CURRENT_RELEASE=${INPUT_FROM_RELEASE}
 744         fi
 745     fi
 746
 747     case ${OPERATION} in
 748     upgrade)
 749         if upgrade; then
 750             echo "${SCHEMA}: OK: upgrade (${CURRENT_RELEASE})"
 751         else
 752             rc=1
 753             echo "${SCHEMA}: failure: upgrade to release ${TARGET_UPGRADE_RELEASE} (${CURRENT_RELEASE})"
 754         fi
 755         ;;
 756     downgrade)
 757         if downgrade; then
 758             echo "${SCHEMA}: OK: downgrade (${CURRENT_RELEASE})"
 759         else
 760             rc=1
 761             echo "${SCHEMA}: failure: downgrade to release ${TARGET_DOWNGRADE_RELEASE} (${CURRENT_RELEASE})"
 762         fi
 763         ;;
 764     version)
 765         echo "${SCHEMA}: ${CURRENT_RELEASE}"
 766         ;;
 767     erase)
 768         erase
 769         ;;
 770     report)
 771         report
 772         ;;
 773     ok)
 774         okay
 775         ;;
 776     esac
 777
 778 done
 779 exit $rc