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