database_utils/migrate_mano_db.sh

   1 #!/bin/bash
   2
   3 ##
   4 # Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U.
   5 # This file is part of openmano
   6 # All Rights Reserved.
   7 #
   8 # Licensed under the Apache License, Version 2.0 (the "License"); you may
   9 # not use this file except in compliance with the License. You may obtain
  10 # 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, WITHOUT
  16 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  17 # License for the specific language governing permissions and limitations
  18 # under the License.
  19 #
  20 # For those usages not covered by the Apache License, Version 2.0 please
  21 # contact with: nfvlabs@tid.es
  22 ##
  23
  24 #
  25 #Upgrade/Downgrade openmano database preserving the content
  26 #
  27 DBUTILS="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
  28
  29 DBUSER="mano"
  30 DBPASS=""
  31 DEFAULT_DBPASS="manopw"
  32 DBHOST=""
  33 DBPORT="3306"
  34 DBNAME="mano_db"
  35 QUIET_MODE=""
  36 BACKUP_DIR=""
  37 BACKUP_FILE=""
  38 #TODO update it with the last database version
  39 LAST_DB_VERSION=38
  40
  41 # Detect paths
  42 MYSQL=$(which mysql)
  43 AWK=$(which awk)
  44 GREP=$(which grep)
  45
  46 function usage(){
  47     echo -e "Usage: $0 OPTIONS [version]"
  48     echo -e "  Upgrades/Downgrades openmano database preserving the content."\
  49             "If [version]  is not provided, it is upgraded to the last version"
  50     echo -e "  OPTIONS"
  51     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  52     echo -e "     -p PASS  database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
  53     echo -e "     -P PORT  database port. '$DBPORT' by default"
  54     echo -e "     -h HOST  database host. 'localhost' by default"
  55     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  56     echo -e "     -b DIR   backup folder where to create rollback backup file"
  57     echo -e "     -q --quiet: Do not prompt for credentials and exit if cannot access to database"
  58     echo -e "     --help   shows this help"
  59 }
  60
  61 while getopts ":u:p:b:P:h:d:q-:" o; do
  62     case "${o}" in
  63         u)
  64             DBUSER="$OPTARG"
  65             ;;
  66         p)
  67             DBPASS="$OPTARG"
  68             ;;
  69         P)
  70             DBPORT="$OPTARG"
  71             ;;
  72         d)
  73             DBNAME="$OPTARG"
  74             ;;
  75         h)
  76             DBHOST="$OPTARG"
  77             ;;
  78         b)
  79             BACKUP_DIR="$OPTARG"
  80             ;;
  81         q)
  82             export QUIET_MODE=yes
  83             ;;
  84         -)
  85             [ "${OPTARG}" == "help" ] && usage && exit 0
  86             [ "${OPTARG}" == "quiet" ] && export QUIET_MODE=yes && continue
  87             echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
  88             exit 1
  89             ;;
  90         \?)
  91             echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
  92             exit 1
  93             ;;
  94         :)
  95             echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
  96             exit 1
  97             ;;
  98         *)
  99             usage >&2
 100             exit 1
 101             ;;
 102     esac
 103 done
 104 shift $((OPTIND-1))
 105
 106 DB_VERSION=$1
 107
 108 if [ -n "$DB_VERSION" ] ; then
 109     # check it is a number and an allowed one
 110     [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev/null ||
 111         ! echo "parameter 'version' requires a integer value" >&2 || exit 1
 112     if [ "$DB_VERSION" -lt 0 ] || [ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
 113         echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
 114              "If you need an upper version, get a newer version of this script '$0'" >&2
 115         exit 1
 116     fi
 117 else
 118     DB_VERSION="$LAST_DB_VERSION"
 119 fi
 120
 121 # Creating temporary file
 122 TEMPFILE="$(mktemp -q --tmpdir "migratemanodb.XXXXXX")"
 123 trap 'rm -f "$TEMPFILE"' EXIT
 124 chmod 0600 "$TEMPFILE"
 125 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 126 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 127
 128 # Check and ask for database user password
 129 FIRST_TRY="yes"
 130 while ! DB_ERROR=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
 131 do
 132     # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
 133     [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS="$DEFAULT_DBPASS" &&
 134         echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
 135         continue
 136     echo "$DB_ERROR"
 137     [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
 138     echo -e "Provide database name and credentials (Ctrl+c to abort):"
 139     read -e -p "    mysql database name($DBNAME): " KK
 140     [ -n "$KK" ] && DBNAME="$KK"
 141     read -e -p "    mysql user($DBUSER): " KK
 142     [ -n "$KK" ] && DBUSER="$KK"
 143     read -e -s -p "    mysql password: " DBPASS
 144     echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 145     FIRST_TRY=""
 146     echo
 147 done
 148
 149 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
 150 #echo DBCMD $DBCMD
 151
 152 #check that the database seems a openmano database
 153 if ! echo -e "show create table vnfs;\nshow create table scenarios" | $DBCMD >/dev/null 2>&1
 154 then
 155     echo "    database $DBNAME does not seem to be an openmano database" >&2
 156     exit 1;
 157 fi
 158
 159 #GET DATABASE TARGET VERSION
 160 #DB_VERSION=0
 161 #[ $OPENMANO_VER_NUM -ge 2002 ] && DB_VERSION=1   #0.2.2 =>  1
 162 #[ $OPENMANO_VER_NUM -ge 2005 ] && DB_VERSION=2   #0.2.5 =>  2
 163 #[ $OPENMANO_VER_NUM -ge 3003 ] && DB_VERSION=3   #0.3.3 =>  3
 164 #[ $OPENMANO_VER_NUM -ge 3005 ] && DB_VERSION=4   #0.3.5 =>  4
 165 #[ $OPENMANO_VER_NUM -ge 4001 ] && DB_VERSION=5   #0.4.1 =>  5
 166 #[ $OPENMANO_VER_NUM -ge 4002 ] && DB_VERSION=6   #0.4.2 =>  6
 167 #[ $OPENMANO_VER_NUM -ge 4003 ] && DB_VERSION=7   #0.4.3 =>  7
 168 #[ $OPENMANO_VER_NUM -ge 4032 ] && DB_VERSION=8   #0.4.32=>  8
 169 #[ $OPENMANO_VER_NUM -ge 4033 ] && DB_VERSION=9   #0.4.33=>  9
 170 #[ $OPENMANO_VER_NUM -ge 4036 ] && DB_VERSION=10  #0.4.36=>  10
 171 #[ $OPENMANO_VER_NUM -ge 4043 ] && DB_VERSION=11  #0.4.43=>  11
 172 #[ $OPENMANO_VER_NUM -ge 4046 ] && DB_VERSION=12  #0.4.46=>  12
 173 #[ $OPENMANO_VER_NUM -ge 4047 ] && DB_VERSION=13  #0.4.47=>  13
 174 #[ $OPENMANO_VER_NUM -ge 4057 ] && DB_VERSION=14  #0.4.57=>  14
 175 #[ $OPENMANO_VER_NUM -ge 4059 ] && DB_VERSION=15  #0.4.59=>  15
 176 #[ $OPENMANO_VER_NUM -ge 5002 ] && DB_VERSION=16  #0.5.2 =>  16
 177 #[ $OPENMANO_VER_NUM -ge 5003 ] && DB_VERSION=17  #0.5.3 =>  17
 178 #[ $OPENMANO_VER_NUM -ge 5004 ] && DB_VERSION=18  #0.5.4 =>  18
 179 #[ $OPENMANO_VER_NUM -ge 5005 ] && DB_VERSION=19  #0.5.5 =>  19
 180 #[ $OPENMANO_VER_NUM -ge 5009 ] && DB_VERSION=20  #0.5.9 =>  20
 181 #[ $OPENMANO_VER_NUM -ge 5015 ] && DB_VERSION=21  #0.5.15 =>  21
 182 #[ $OPENMANO_VER_NUM -ge 5016 ] && DB_VERSION=22  #0.5.16 =>  22
 183 #[ $OPENMANO_VER_NUM -ge 5020 ] && DB_VERSION=23  #0.5.20 =>  23
 184 #[ $OPENMANO_VER_NUM -ge 5021 ] && DB_VERSION=24  #0.5.21 =>  24
 185 #[ $OPENMANO_VER_NUM -ge 5022 ] && DB_VERSION=25  #0.5.22 =>  25
 186 #[ $OPENMANO_VER_NUM -ge 5024 ] && DB_VERSION=26  #0.5.24 =>  26
 187 #[ $OPENMANO_VER_NUM -ge 5025 ] && DB_VERSION=27  #0.5.25 =>  27
 188 #[ $OPENMANO_VER_NUM -ge 5052 ] && DB_VERSION=28  #0.5.52 =>  28
 189 #[ $OPENMANO_VER_NUM -ge 5059 ] && DB_VERSION=29  #0.5.59 =>  29
 190 #[ $OPENMANO_VER_NUM -ge 5060 ] && DB_VERSION=30  #0.5.60 =>  30
 191 #[ $OPENMANO_VER_NUM -ge 5061 ] && DB_VERSION=31  #0.5.61 =>  31
 192 #[ $OPENMANO_VER_NUM -ge 5070 ] && DB_VERSION=32  #0.5.70 =>  32
 193 #[ $OPENMANO_VER_NUM -ge 5082 ] && DB_VERSION=33  #0.5.82 =>  33
 194 #[ $OPENMANO_VER_NUM -ge 6000 ] && DB_VERSION=34  #0.6.00 =>  34
 195 #[ $OPENMANO_VER_NUM -ge 6001 ] && DB_VERSION=35  #0.6.01 =>  35
 196 #[ $OPENMANO_VER_NUM -ge 6003 ] && DB_VERSION=36  #0.6.03 =>  36
 197 #[ $OPENMANO_VER_NUM -ge 6009 ] && DB_VERSION=37  #0.6.09 =>  37
 198 #[ $OPENMANO_VER_NUM -ge 6011 ] && DB_VERSION=38  #0.6.11 =>  38
 199 #TODO ... put next versions here
 200
 201 function upgrade_to_1(){
 202     # echo "    upgrade database from version 0.0 to version 0.1"
 203     echo "      CREATE TABLE \`schema_version\`"
 204     sql "CREATE TABLE \`schema_version\` (
 205         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 206         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 207         \`openmano_ver\` VARCHAR(20) NOT NULL COMMENT 'openmano version',
 208         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 209         \`date\` DATE NULL,
 210         PRIMARY KEY (\`version_int\`)
 211         )
 212         COMMENT='database schema control version'
 213         COLLATE='utf8_general_ci'
 214         ENGINE=InnoDB;"
 215     sql "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openmano_ver\`, \`comments\`, \`date\`)
 216          VALUES (1, '0.1', '0.2.2', 'insert schema_version', '2015-05-08');"
 217 }
 218 function downgrade_from_1(){
 219     # echo "    downgrade database from version 0.1 to version 0.0"
 220     echo "      DROP TABLE IF EXISTS \`schema_version\`"
 221     sql "DROP TABLE IF EXISTS \`schema_version\`;"
 222 }
 223 function upgrade_to_2(){
 224     # echo "    upgrade database from version 0.1 to version 0.2"
 225     echo "      Add columns user/passwd to table 'vim_tenants'"
 226     sql "ALTER TABLE vim_tenants ADD COLUMN user VARCHAR(36) NULL COMMENT 'Credentials for vim' AFTER created,
 227         ADD COLUMN passwd VARCHAR(50) NULL COMMENT 'Credentials for vim' AFTER user;"
 228     echo "      Add table 'images' and 'datacenters_images'"
 229     sql "CREATE TABLE images (
 230         uuid VARCHAR(36) NOT NULL,
 231         name VARCHAR(50) NOT NULL,
 232         location VARCHAR(200) NOT NULL,
 233         description VARCHAR(100) NULL,
 234         metadata VARCHAR(400) NULL,
 235         PRIMARY KEY (uuid),
 236         UNIQUE INDEX location (location)  )
 237         COLLATE='utf8_general_ci'
 238         ENGINE=InnoDB;"
 239     sql "CREATE TABLE datacenters_images (
 240         id INT NOT NULL AUTO_INCREMENT,
 241         image_id VARCHAR(36) NOT NULL,
 242         datacenter_id VARCHAR(36) NOT NULL,
 243         vim_id VARCHAR(36) NOT NULL,
 244         PRIMARY KEY (id),
 245         CONSTRAINT FK__images FOREIGN KEY (image_id) REFERENCES images (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 246         CONSTRAINT FK__datacenters_i FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 247         COLLATE='utf8_general_ci'
 248         ENGINE=InnoDB;"
 249     echo "      migrate data from table 'vms' into 'images'"
 250     sql "INSERT INTO images (uuid, name, location) SELECT DISTINCT vim_image_id, vim_image_id, image_path FROM vms;"
 251     sql "INSERT INTO datacenters_images (image_id, datacenter_id, vim_id)
 252           SELECT DISTINCT vim_image_id, datacenters.uuid, vim_image_id FROM vms JOIN datacenters;"
 253     echo "      Add table 'flavors' and 'datacenter_flavors'"
 254     sql "CREATE TABLE flavors (
 255         uuid VARCHAR(36) NOT NULL,
 256         name VARCHAR(50) NOT NULL,
 257         description VARCHAR(100) NULL,
 258         disk SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 259         ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 260         vcpus SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 261         extended VARCHAR(2000) NULL DEFAULT NULL COMMENT 'Extra description json format of needed resources and pining, orginized in sets per numa',
 262         PRIMARY KEY (uuid)  )
 263         COLLATE='utf8_general_ci'
 264         ENGINE=InnoDB;"
 265     sql "CREATE TABLE datacenters_flavors (
 266         id INT NOT NULL AUTO_INCREMENT,
 267         flavor_id VARCHAR(36) NOT NULL,
 268         datacenter_id VARCHAR(36) NOT NULL,
 269         vim_id VARCHAR(36) NOT NULL,
 270         PRIMARY KEY (id),
 271         CONSTRAINT FK__flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 272         CONSTRAINT FK__datacenters_f FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 273         COLLATE='utf8_general_ci'
 274         ENGINE=InnoDB;"
 275     echo "      migrate data from table 'vms' into 'flavors'"
 276     sql "INSERT INTO flavors (uuid, name) SELECT DISTINCT vim_flavor_id, vim_flavor_id FROM vms;"
 277     sql "INSERT INTO datacenters_flavors (flavor_id, datacenter_id, vim_id)
 278           SELECT DISTINCT vim_flavor_id, datacenters.uuid, vim_flavor_id FROM vms JOIN datacenters;"
 279     sql "ALTER TABLE vms ALTER vim_flavor_id DROP DEFAULT, ALTER vim_image_id DROP DEFAULT;
 280           ALTER TABLE vms CHANGE COLUMN vim_flavor_id flavor_id VARCHAR(36) NOT NULL COMMENT 'Link to flavor table' AFTER vnf_id,
 281           CHANGE COLUMN vim_image_id image_id VARCHAR(36) NOT NULL COMMENT 'Link to image table' AFTER flavor_id,
 282           ADD CONSTRAINT FK_vms_images  FOREIGN KEY (image_id) REFERENCES  images (uuid),
 283           ADD CONSTRAINT FK_vms_flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid);"
 284     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (2, '0.2', '0.2.5', 'new tables images,flavors', '2015-07-13');"
 285
 286 }
 287
 288 function downgrade_from_2(){
 289     # echo "    downgrade database from version 0.2 to version 0.1"
 290     echo "       migrate back data from 'datacenters_images' 'datacenters_flavors' into 'vms'"
 291     sql "ALTER TABLE vms ALTER image_id DROP DEFAULT, ALTER flavor_id DROP DEFAULT;
 292           ALTER TABLE vms CHANGE COLUMN flavor_id vim_flavor_id VARCHAR(36) NOT NULL COMMENT 'Flavor ID in the VIM DB' AFTER vnf_id,
 293           CHANGE COLUMN image_id vim_image_id VARCHAR(36) NOT NULL COMMENT 'Image ID in the VIM DB' AFTER vim_flavor_id,
 294           DROP FOREIGN KEY FK_vms_flavors, DROP INDEX FK_vms_flavors,
 295           DROP FOREIGN KEY FK_vms_images, DROP INDEX FK_vms_images;"
 296 #    echo "UPDATE v SET v.vim_image_id=di.vim_id
 297 #          FROM  vms as v INNER JOIN images as i ON v.vim_image_id=i.uuid
 298 #          INNER JOIN datacenters_images as di ON i.uuid=di.image_id;"
 299     echo "      Delete columns 'user/passwd' from 'vim_tenants'"
 300     sql "ALTER TABLE vim_tenants DROP COLUMN user, DROP COLUMN passwd; "
 301     echo "        delete tables 'datacenter_images', 'images'"
 302     sql "DROP TABLE IF EXISTS \`datacenters_images\`;"
 303     sql "DROP TABLE IF EXISTS \`images\`;"
 304     echo "        delete tables 'datacenter_flavors', 'flavors'"
 305     sql "DROP TABLE IF EXISTS \`datacenters_flavors\`;"
 306     sql "DROP TABLE IF EXISTS \`flavors\`;"
 307     sql "DELETE FROM schema_version WHERE version_int='2';"
 308 }
 309
 310 function upgrade_to_3(){
 311     # echo "    upgrade database from version 0.2 to version 0.3"
 312     echo "      Change table 'logs', 'uuids"
 313     sql "ALTER TABLE logs CHANGE COLUMN related related VARCHAR(36) NOT NULL COMMENT 'Relevant element for the log' AFTER nfvo_tenant_id;"
 314     sql "ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(36) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' AFTER created_at;"
 315     echo "      Add column created to table 'datacenters_images' and 'datacenters_flavors'"
 316     for table in datacenters_images datacenters_flavors
 317     do
 318         sql "ALTER TABLE $table ADD COLUMN created ENUM('true','false') NOT NULL DEFAULT 'false'
 319             COMMENT 'Indicates if it has been created by openmano, or already existed' AFTER vim_id;"
 320     done
 321     sql "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(2000) NULL DEFAULT NULL AFTER description;"
 322     echo "      Allow null to column 'vim_interface_id' in 'instance_interfaces'"
 323     sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'vim identity for that interface' AFTER interface_id; "
 324     echo "      Add column config to table 'datacenters'"
 325     sql "ALTER TABLE datacenters ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL COMMENT 'extra config information in json' AFTER vim_url_admin;
 326         "
 327     echo "      Add column datacenter_id to table 'vim_tenants'"
 328     sql "ALTER TABLE vim_tenants ADD COLUMN datacenter_id VARCHAR(36) NULL COMMENT 'Datacenter of this tenant' AFTER uuid,
 329         DROP INDEX name, DROP INDEX vim_tenant_id;"
 330     sql "ALTER TABLE vim_tenants CHANGE COLUMN name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL COMMENT 'tenant name at VIM' AFTER datacenter_id,
 331         CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;"
 332     echo "UPDATE vim_tenants as vt LEFT JOIN tenants_datacenters as td ON vt.uuid=td.vim_tenant_id
 333         SET vt.datacenter_id=td.datacenter_id;"
 334     sql "DELETE FROM vim_tenants WHERE datacenter_id is NULL;"
 335     sql "ALTER TABLE vim_tenants ALTER datacenter_id DROP DEFAULT;
 336         ALTER TABLE vim_tenants
 337         CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL COMMENT 'Datacenter of this tenant' AFTER uuid;"
 338     sql "ALTER TABLE vim_tenants ADD CONSTRAINT FK_vim_tenants_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid)
 339         ON UPDATE CASCADE ON DELETE CASCADE;"
 340
 341     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (3, '0.3', '0.3.3', 'alter vim_tenant tables', '2015-07-28');"
 342 }
 343
 344
 345 function downgrade_from_3(){
 346     # echo "    downgrade database from version 0.3 to version 0.2"
 347     echo "      Change back table 'logs', 'uuids'"
 348     sql "ALTER TABLE logs CHANGE COLUMN related related ENUM('nfvo_tenants','datacenters','vim_tenants','tenants_datacenters','vnfs','vms','interfaces','nets','scenarios','sce_vnfs','sce_interfaces','sce_nets','instance_scenarios','instance_vnfs','instance_vms','instance_nets','instance_interfaces') NOT NULL COMMENT 'Relevant element for the log' AFTER nfvo_tenant_id;"
 349     sql "ALTER TABLE uuids CHANGE COLUMN used_at used_at ENUM('nfvo_tenants','datacenters','vim_tenants','vnfs','vms','interfaces','nets','scenarios','sce_vnfs','sce_interfaces','sce_nets','instance_scenarios','instance_vnfs','instance_vms','instance_nets','instance_interfaces') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' AFTER created_at;"
 350     echo "      Delete column created from table 'datacenters_images' and 'datacenters_flavors'"
 351     for table in datacenters_images datacenters_flavors
 352     do
 353         sql "ALTER TABLE $table DROP COLUMN created;"
 354     done
 355     sql "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(400) NULL DEFAULT NULL AFTER description;"
 356     echo "      Deny back null to column 'vim_interface_id' in 'instance_interfaces'"
 357     sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(36) NOT NULL COMMENT 'vim identity for that interface' AFTER interface_id; "
 358     echo "       Delete column config to table 'datacenters'"
 359     sql "ALTER TABLE datacenters DROP COLUMN config;"
 360     echo "       Delete column datacenter_id to table 'vim_tenants'"
 361     sql "ALTER TABLE vim_tenants DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_vim_tenants_datacenters;"
 362     sql "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name name VARCHAR(36) NULL DEFAULT NULL COMMENT '' AFTER uuid"
 363     sql "ALTER TABLE vim_tenants ALTER name DROP DEFAULT;"
 364     sql "ALTER TABLE vim_tenants CHANGE COLUMN name name VARCHAR(36) NOT NULL AFTER uuid" || ! echo "Warning changing column name at vim_tenants!"
 365     sql "ALTER TABLE vim_tenants ADD UNIQUE INDEX name (name);" || ! echo "Warning add unique index name at vim_tenants!"
 366     sql "ALTER TABLE vim_tenants ALTER vim_tenant_id DROP DEFAULT;"
 367     sql "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(36) NOT NULL COMMENT 'Tenant ID in the VIM DB' AFTER name;" ||
 368         ! echo "Warning changing column vim_tenant_id at vim_tenants!"
 369     sql "ALTER TABLE vim_tenants ADD UNIQUE INDEX vim_tenant_id (vim_tenant_id);" ||
 370         ! echo "Warning add unique index vim_tenant_id at vim_tenants!"
 371     sql "DELETE FROM schema_version WHERE version_int='3';"
 372 }
 373
 374 function upgrade_to_4(){
 375     # echo "    upgrade database from version 0.3 to version 0.4"
 376     echo "      Enlarge graph field at tables 'sce_vnfs', 'sce_nets'"
 377     for table in sce_vnfs sce_nets
 378     do
 379         sql "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;"
 380     done
 381     sql "ALTER TABLE datacenters CHANGE COLUMN type type VARCHAR(36) NOT NULL DEFAULT 'openvim' AFTER description;"
 382     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (4, '0.4', '0.3.5', 'enlarge graph field at sce_vnfs/nets', '2015-10-20');"
 383 }
 384
 385 function downgrade_from_4(){
 386     # echo "    downgrade database from version 0.4 to version 0.3"
 387     echo "      Shorten back graph field at tables 'sce_vnfs', 'sce_nets'"
 388     for table in sce_vnfs sce_nets
 389     do
 390         sql "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;"
 391     done
 392     sql "ALTER TABLE datacenters CHANGE COLUMN type type ENUM('openvim','openstack') NOT NULL DEFAULT 'openvim' AFTER description;"
 393     sql "DELETE FROM schema_version WHERE version_int='4';"
 394 }
 395
 396 function upgrade_to_5(){
 397     # echo "    upgrade database from version 0.4 to version 0.5"
 398     echo "      Add 'mac' field for bridge interfaces in table 'interfaces'"
 399     sql "ALTER TABLE interfaces ADD COLUMN mac CHAR(18) NULL DEFAULT NULL AFTER model;"
 400     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (5, '0.5', '0.4.1', 'Add mac address for bridge interfaces', '2015-12-14');"
 401 }
 402 function downgrade_from_5(){
 403     # echo "    downgrade database from version 0.5 to version 0.4"
 404     echo "      Remove 'mac' field for bridge interfaces in table 'interfaces'"
 405     sql "ALTER TABLE interfaces DROP COLUMN mac;"
 406     sql "DELETE FROM schema_version WHERE version_int='5';"
 407 }
 408
 409 function upgrade_to_6(){
 410     # echo "    upgrade database from version 0.5 to version 0.6"
 411     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 412     sql "ALTER TABLE vnfs ADD COLUMN descriptor TEXT NULL DEFAULT NULL COMMENT 'Original text descriptor used for create the VNF' AFTER class;"
 413     sql "ALTER TABLE scenarios ADD COLUMN descriptor TEXT NULL DEFAULT NULL COMMENT 'Original text descriptor used for create the scenario' AFTER modified_at;"
 414     echo "      Add 'last_error', 'vim_info' to 'instance_vms', 'instance_nets'"
 415     sql "ALTER TABLE instance_vms  ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;"
 416     sql "ALTER TABLE instance_vms  ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;"
 417     sql "ALTER TABLE instance_vms  CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD' AFTER vim_vm_id;"
 418     sql "ALTER TABLE instance_nets ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;"
 419     sql "ALTER TABLE instance_nets ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;"
 420     sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR','VIM_ERROR','INACTIVE','DELETED') NOT NULL DEFAULT 'BUILD' AFTER instance_scenario_id;"
 421     echo "      Add 'mac_address', 'ip_address', 'vim_info' to 'instance_interfaces'"
 422     sql "ALTER TABLE instance_interfaces ADD COLUMN mac_address VARCHAR(32) NULL DEFAULT NULL AFTER vim_interface_id, ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac_address, ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER ip_address;"
 423     echo "      Add 'sce_vnf_id','datacenter_id','vim_tenant_id' field to 'instance_vnfs'"
 424     sql "ALTER TABLE instance_vnfs ADD COLUMN sce_vnf_id VARCHAR(36) NULL DEFAULT NULL AFTER vnf_id, ADD CONSTRAINT FK_instance_vnfs_sce_vnfs FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 425     sql "ALTER TABLE instance_vnfs ADD COLUMN vim_tenant_id VARCHAR(36) NULL DEFAULT NULL AFTER sce_vnf_id, ADD CONSTRAINT FK_instance_vnfs_vim_tenants FOREIGN KEY (vim_tenant_id) REFERENCES vim_tenants (uuid) ON UPDATE RESTRICT ON DELETE RESTRICT;"
 426     sql "ALTER TABLE instance_vnfs ADD COLUMN datacenter_id VARCHAR(36) NULL DEFAULT NULL AFTER vim_tenant_id, ADD CONSTRAINT FK_instance_vnfs_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE RESTRICT ON DELETE RESTRICT;"
 427     echo "      Add 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field to 'instance_nets'"
 428     sql "ALTER TABLE instance_nets ADD COLUMN sce_net_id VARCHAR(36) NULL DEFAULT NULL AFTER instance_scenario_id, ADD CONSTRAINT FK_instance_nets_sce_nets FOREIGN KEY (sce_net_id) REFERENCES sce_nets (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 429     sql "ALTER TABLE instance_nets ADD COLUMN net_id VARCHAR(36) NULL DEFAULT NULL AFTER sce_net_id, ADD CONSTRAINT FK_instance_nets_nets FOREIGN KEY (net_id) REFERENCES nets (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 430     sql "ALTER TABLE instance_nets ADD COLUMN vim_tenant_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT FK_instance_nets_vim_tenants FOREIGN KEY (vim_tenant_id) REFERENCES vim_tenants (uuid) ON UPDATE RESTRICT ON DELETE RESTRICT;"
 431     sql "ALTER TABLE instance_nets ADD COLUMN datacenter_id VARCHAR(36) NULL DEFAULT NULL AFTER vim_tenant_id, ADD CONSTRAINT FK_instance_nets_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE RESTRICT ON DELETE RESTRICT;"
 432     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (6, '0.6', '0.4.2', 'Adding VIM status info', '2015-12-22');"
 433 }
 434 function downgrade_from_6(){
 435     # echo "    downgrade database from version 0.6 to version 0.5"
 436     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 437     sql "ALTER TABLE vnfs      DROP COLUMN descriptor;"
 438     sql "ALTER TABLE scenarios DROP COLUMN descriptor;"
 439     echo "      Remove 'last_error', 'vim_info' from 'instance_vms', 'instance_nets'"
 440     sql "ALTER TABLE instance_vms  DROP COLUMN error_msg, DROP COLUMN vim_info;"
 441     sql "ALTER TABLE instance_vms  CHANGE COLUMN status status ENUM('ACTIVE','PAUSED','INACTIVE','CREATING','ERROR','DELETING') NOT NULL DEFAULT 'CREATING' AFTER vim_vm_id;"
 442     sql "ALTER TABLE instance_nets DROP COLUMN error_msg, DROP COLUMN vim_info;"
 443     sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'BUILD' AFTER instance_scenario_id;"
 444     echo "      Remove 'mac_address', 'ip_address', 'vim_info' from 'instance_interfaces'"
 445     sql "ALTER TABLE instance_interfaces DROP COLUMN mac_address, DROP COLUMN ip_address, DROP COLUMN vim_info;"
 446     echo "      Remove 'sce_vnf_id','datacenter_id','vim_tenant_id' field from 'instance_vnfs'"
 447     sql "ALTER TABLE instance_vnfs DROP COLUMN sce_vnf_id, DROP FOREIGN KEY FK_instance_vnfs_sce_vnfs;"
 448     sql "ALTER TABLE instance_vnfs DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_vnfs_vim_tenants;"
 449     sql "ALTER TABLE instance_vnfs DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_vnfs_datacenters;"
 450     echo "      Remove 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field from 'instance_nets'"
 451     sql "ALTER TABLE instance_nets DROP COLUMN sce_net_id, DROP FOREIGN KEY FK_instance_nets_sce_nets;"
 452     sql "ALTER TABLE instance_nets DROP COLUMN net_id, DROP FOREIGN KEY FK_instance_nets_nets;"
 453     sql "ALTER TABLE instance_nets DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_nets_vim_tenants;"
 454     sql "ALTER TABLE instance_nets DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_nets_datacenters;"
 455     sql "DELETE FROM schema_version WHERE version_int='6';"
 456 }
 457
 458 function upgrade_to_7(){
 459     # echo "    upgrade database from version 0.6 to version 0.7"
 460     echo "      Change created_at, modified_at from timestamp to unix float at all database"
 461     for table in datacenters datacenter_nets instance_nets instance_scenarios instance_vms instance_vnfs interfaces nets nfvo_tenants scenarios sce_interfaces sce_nets sce_vnfs tenants_datacenters vim_tenants vms vnfs uuids
 462     do
 463          echo -en "        $table               \r"
 464          sql "ALTER TABLE $table ADD COLUMN created_at_ DOUBLE NOT NULL after created_at;"
 465          echo "UPDATE $table SET created_at_=unix_timestamp(created_at);"
 466          sql "ALTER TABLE $table DROP COLUMN created_at, CHANGE COLUMN created_at_ created_at DOUBLE NOT NULL;"
 467          [[ $table == uuids ]] || sql "ALTER TABLE $table CHANGE COLUMN modified_at modified_at DOUBLE NULL DEFAULT NULL;"
 468     done
 469
 470     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 471     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (7, '0.7', '0.4.3', 'Changing created_at time at database', '2016-01-25');"
 472 }
 473 function downgrade_from_7(){
 474     # echo "    downgrade database from version 0.7 to version 0.6"
 475     echo "      Change back created_at, modified_at from unix float to timestamp at all database"
 476     for table in datacenters datacenter_nets instance_nets instance_scenarios instance_vms instance_vnfs interfaces nets nfvo_tenants scenarios sce_interfaces sce_nets sce_vnfs tenants_datacenters vim_tenants vms vnfs uuids
 477     do
 478          echo -en "        $table               \r"
 479          sql "ALTER TABLE $table ADD COLUMN created_at_ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP after created_at;"
 480          echo "UPDATE $table SET created_at_=from_unixtime(created_at);"
 481          sql "ALTER TABLE $table DROP COLUMN created_at, CHANGE COLUMN created_at_ created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;"
 482          [[ $table == uuids ]] || sql "ALTER TABLE $table CHANGE COLUMN modified_at modified_at TIMESTAMP NULL DEFAULT NULL;"
 483     done
 484     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 485     sql "DELETE FROM schema_version WHERE version_int='7';"
 486 }
 487
 488 function upgrade_to_8(){
 489     # echo "    upgrade database from version 0.7 to version 0.8"
 490     echo "      Change enalarge name, description to 255 at all database"
 491     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 492     do
 493          echo -en "        $table               \r"
 494          sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR(255) NOT NULL;"
 495          sql "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL;"
 496     done
 497     echo -en "        interfaces           \r"
 498     sql "ALTER TABLE interfaces CHANGE COLUMN internal_name internal_name VARCHAR(255) NOT NULL, CHANGE COLUMN external_name external_name VARCHAR(255) NULL DEFAULT NULL;"
 499     sql "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL;"
 500     echo -en "        vim_tenants          \r"
 501     sql "ALTER TABLE vim_tenants CHANGE COLUMN user user VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN passwd passwd VARCHAR(64) NULL DEFAULT NULL;"
 502     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (8, '0.8', '0.4.32', 'Enlarging name at database', '2016-02-01');"
 503 }
 504 function downgrade_from_8(){
 505     # echo "    downgrade database from version 0.8 to version 0.7"
 506     echo "      Change back name,description to shorter length at all database"
 507     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 508     do
 509          name_length=50
 510          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 511          echo -en "        $table               \r"
 512          sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL;"
 513          sql "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL;"
 514     done
 515     echo -en "        interfaces           \r"
 516     sql "ALTER TABLE interfaces CHANGE COLUMN internal_name internal_name VARCHAR(25) NOT NULL, CHANGE COLUMN external_name external_name VARCHAR(25) NULL DEFAULT NULL;"
 517     echo -en "        vim_tenants          \r"
 518     sql "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL;"
 519     sql "ALTER TABLE vim_tenants CHANGE COLUMN user user VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN passwd passwd VARCHAR(50) NULL DEFAULT NULL;"
 520     sql "DELETE FROM schema_version WHERE version_int='8';"
 521 }
 522 function upgrade_to_9(){
 523     # echo "    upgrade database from version 0.8 to version 0.9"
 524     echo "      Add more status to 'instance_vms'"
 525     sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';"
 526     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (9, '0.9', '0.4.33', 'Add ACTIVE:NoMgmtIP to instance_vms table', '2016-02-05');"
 527 }
 528 function downgrade_from_9(){
 529     # echo "    downgrade database from version 0.9 to version 0.8"
 530     echo "      Add more status to 'instance_vms'"
 531     sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';"
 532     sql "DELETE FROM schema_version WHERE version_int='9';"
 533 }
 534 function upgrade_to_10(){
 535     # echo "    upgrade database from version 0.9 to version 0.10"
 536     echo "      add tenant to 'vnfs'"
 537     sql "ALTER TABLE vnfs ADD COLUMN tenant_id VARCHAR(36) NULL DEFAULT NULL AFTER name, ADD CONSTRAINT FK_vnfs_nfvo_tenants FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid) ON UPDATE CASCADE ON DELETE SET NULL, CHANGE COLUMN public public ENUM('true','false') NOT NULL DEFAULT 'false' AFTER physical, DROP INDEX name, DROP INDEX path, DROP COLUMN path;"
 538     sql "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;"
 539     sql "ALTER TABLE scenarios CHANGE COLUMN nfvo_tenant_id tenant_id VARCHAR(36) NULL DEFAULT NULL after name, ADD CONSTRAINT FK_scenarios_nfvo_tenants FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid);"
 540     sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;"
 541     sql "ALTER TABLE instance_scenarios CHANGE COLUMN nfvo_tenant_id tenant_id VARCHAR(36) NULL DEFAULT NULL after name, ADD CONSTRAINT FK_instance_scenarios_nfvo_tenants FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid);"
 542     echo "      rename 'vim_tenants' table to 'datacenter_tenants'"
 543     echo "RENAME TABLE vim_tenants TO datacenter_tenants;"
 544     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 545     do
 546         NULL="NOT NULL"
 547         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 548         sql "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_vim_tenants;"
 549         sql "ALTER TABLE ${table} ALTER vim_tenant_id DROP DEFAULT;"
 550         sql "ALTER TABLE ${table} CHANGE COLUMN vim_tenant_id datacenter_tenant_id VARCHAR(36)  ${NULL} AFTER datacenter_id, ADD CONSTRAINT FK_${table}_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid); "
 551     done
 552     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (10, '0.10', '0.4.36', 'tenant management of vnfs,scenarios', '2016-03-08');"
 553 }
 554
 555 function downgrade_from_10(){
 556     # echo "    downgrade database from version 0.10 to version 0.9"
 557     echo "      remove tenant from 'vnfs'"
 558     sql "ALTER TABLE vnfs DROP COLUMN tenant_id, DROP FOREIGN KEY FK_vnfs_nfvo_tenants, ADD UNIQUE INDEX name (name), ADD COLUMN path VARCHAR(100) NULL DEFAULT NULL COMMENT 'Path where the YAML descriptor of the VNF can be found. NULL if it is a physical network function.' AFTER name, ADD UNIQUE INDEX path (path), CHANGE COLUMN public public ENUM('true','false') NOT NULL DEFAULT 'true' AFTER physical;"
 559     sql "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;"
 560     sql "ALTER TABLE scenarios CHANGE COLUMN tenant_id nfvo_tenant_id VARCHAR(36) NULL DEFAULT NULL after name, ADD CONSTRAINT FK_scenarios_nfvo_tenants FOREIGN KEY (nfvo_tenant_id) REFERENCES nfvo_tenants (uuid);"
 561     sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;"
 562     sql "ALTER TABLE instance_scenarios CHANGE COLUMN tenant_id nfvo_tenant_id VARCHAR(36) NULL DEFAULT NULL after name, ADD CONSTRAINT FK_instance_scenarios_nfvo_tenants FOREIGN KEY (nfvo_tenant_id) REFERENCES nfvo_tenants (uuid);"
 563     echo "      rename back 'datacenter_tenants' table to 'vim_tenants'"
 564     echo "RENAME TABLE datacenter_tenants TO vim_tenants;"
 565     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 566     do
 567         sql "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_datacenter_tenants;"
 568         NULL="NOT NULL"
 569         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 570         sql "ALTER TABLE ${table} ALTER datacenter_tenant_id DROP DEFAULT;"
 571         sql "ALTER TABLE ${table} CHANGE COLUMN datacenter_tenant_id vim_tenant_id VARCHAR(36) $NULL AFTER datacenter_id, ADD CONSTRAINT FK_${table}_vim_tenants FOREIGN KEY (vim_tenant_id) REFERENCES vim_tenants (uuid); "
 572     done
 573     sql "DELETE FROM schema_version WHERE version_int='10';"
 574 }
 575
 576 function upgrade_to_11(){
 577     # echo "    upgrade database from version 0.10 to version 0.11"
 578     echo "      remove unique name at 'scenarios', 'instance_scenarios'"
 579     sql "ALTER TABLE scenarios DROP INDEX name;"
 580     sql "ALTER TABLE instance_scenarios DROP INDEX name;"
 581     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (11, '0.11', '0.4.43', 'remove unique name at scenarios,instance_scenarios', '2016-07-18');"
 582 }
 583 function downgrade_from_11(){
 584     # echo "    downgrade database from version 0.11 to version 0.10"
 585     echo "      add unique name at 'scenarios', 'instance_scenarios'"
 586     sql "ALTER TABLE scenarios ADD UNIQUE INDEX name (name);"
 587     sql "ALTER TABLE instance_scenarios ADD UNIQUE INDEX name (name);"
 588     sql "DELETE FROM schema_version WHERE version_int='11';"
 589 }
 590
 591 function upgrade_to_12(){
 592     # echo "    upgrade database from version 0.11 to version 0.12"
 593     echo "      create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to 'interfaces' and 'sce_interfaces'"
 594     sql "CREATE TABLE IF NOT EXISTS ip_profiles (
 595         id INT(11) NOT NULL AUTO_INCREMENT,
 596         net_id VARCHAR(36) NULL DEFAULT NULL,
 597         sce_net_id VARCHAR(36) NULL DEFAULT NULL,
 598         instance_net_id VARCHAR(36) NULL DEFAULT NULL,
 599         ip_version ENUM('IPv4','IPv6') NOT NULL DEFAULT 'IPv4',
 600         subnet_address VARCHAR(64) NULL DEFAULT NULL,
 601         gateway_address VARCHAR(64) NULL DEFAULT NULL,
 602         dns_address VARCHAR(64) NULL DEFAULT NULL,
 603         dhcp_enabled ENUM('true','false') NOT NULL DEFAULT 'true',
 604         dhcp_start_address VARCHAR(64) NULL DEFAULT NULL,
 605         dhcp_count INT(11) NULL DEFAULT NULL,
 606         PRIMARY KEY (id),
 607         CONSTRAINT FK_ipprofiles_nets FOREIGN KEY (net_id) REFERENCES nets (uuid) ON DELETE CASCADE,
 608         CONSTRAINT FK_ipprofiles_scenets FOREIGN KEY (sce_net_id) REFERENCES sce_nets (uuid) ON DELETE CASCADE,
 609         CONSTRAINT FK_ipprofiles_instancenets FOREIGN KEY (instance_net_id) REFERENCES instance_nets (uuid) ON DELETE CASCADE  )
 610         COMMENT='Table containing the IP parameters of a network, either a net, a sce_net or and instance_net.'
 611         COLLATE='utf8_general_ci'
 612         ENGINE=InnoDB;"
 613     sql "ALTER TABLE interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"
 614     sql "ALTER TABLE sce_interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER interface_id;"
 615     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (12, '0.12', '0.4.46', 'create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to interfaces and sce_interfaces', '2016-08-29');"
 616 }
 617 function downgrade_from_12(){
 618     # echo "    downgrade database from version 0.12 to version 0.11"
 619     echo "      delete ip_profiles table, and remove ip_address column in 'interfaces' and 'sce_interfaces'"
 620     sql "DROP TABLE IF EXISTS ip_profiles;"
 621     sql "ALTER TABLE interfaces DROP COLUMN ip_address;"
 622     sql "ALTER TABLE sce_interfaces DROP COLUMN ip_address;"
 623     sql "DELETE FROM schema_version WHERE version_int='12';"
 624 }
 625
 626 function upgrade_to_13(){
 627     # echo "    upgrade database from version 0.12 to version 0.13"
 628     echo "      add cloud_config at 'scenarios', 'instance_scenarios'"
 629     sql "ALTER TABLE scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER descriptor;"
 630     sql "ALTER TABLE instance_scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER modified_at;"
 631     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (13, '0.13', '0.4.47', 'insert cloud-config at scenarios,instance_scenarios', '2016-08-30');"
 632 }
 633 function downgrade_from_13(){
 634     # echo "    downgrade database from version 0.13 to version 0.12"
 635     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 636     sql "ALTER TABLE scenarios DROP COLUMN cloud_config;"
 637     sql "ALTER TABLE instance_scenarios DROP COLUMN cloud_config;"
 638     sql "DELETE FROM schema_version WHERE version_int='13';"
 639 }
 640
 641 function upgrade_to_14(){
 642     # echo "    upgrade database from version 0.13 to version 0.14"
 643     echo "      remove unique index vim_net_id, instance_scenario_id at table 'instance_nets'"
 644     sql "ALTER TABLE instance_nets DROP INDEX vim_net_id_instance_scenario_id;"
 645     sql "ALTER TABLE instance_nets CHANGE COLUMN external created ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'Created or already exists at VIM' AFTER multipoint;"
 646     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (14, '0.14', '0.4.57', 'remove unique index vim_net_id, instance_scenario_id', '2016-09-26');"
 647 }
 648 function downgrade_from_14(){
 649     # echo "    downgrade database from version 0.14 to version 0.13"
 650     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 651     sql "ALTER TABLE instance_nets ADD UNIQUE INDEX vim_net_id_instance_scenario_id (vim_net_id, instance_scenario_id);"
 652     sql "ALTER TABLE instance_nets CHANGE COLUMN created external ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'If external, means that it already exists at VIM' AFTER multipoint;"
 653     sql "DELETE FROM schema_version WHERE version_int='14';"
 654 }
 655
 656 function upgrade_to_15(){
 657     # echo "    upgrade database from version 0.14 to version 0.15"
 658     echo "      add columns 'universal_name' and 'checksum' at table 'images', add unique index universal_name_checksum, and change location to allow NULL; change column 'image_path' in table 'vms' to allow NULL"
 659     sql "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL DEFAULT NULL AFTER name;"
 660     sql "ALTER TABLE images ALTER location DROP DEFAULT;"
 661     sql "ALTER TABLE images ADD COLUMN universal_name VARCHAR(255) NULL AFTER name, CHANGE COLUMN location location VARCHAR(200) NULL AFTER checksum, ADD UNIQUE INDEX universal_name_checksum (universal_name, checksum);"
 662     sql "ALTER TABLE vms ALTER image_path DROP DEFAULT;"
 663     sql "ALTER TABLE vms CHANGE COLUMN image_path image_path VARCHAR(100) NULL COMMENT 'Path where the image of the VM is located' AFTER image_id;"
 664     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (15, '0.15', '0.4.59', 'add columns universal_name and checksum at table images, add unique index universal_name_checksum, and change location to allow NULL; change column image_path in table vms to allow NULL', '2016-09-27');"
 665 }
 666 function downgrade_from_15(){
 667     # echo "    downgrade database from version 0.15 to version 0.14"
 668     echo "      remove columns 'universal_name' and 'checksum' from table 'images', remove index universal_name_checksum, change location NOT NULL; change column 'image_path' in table 'vms' to NOT NULL"
 669     sql "ALTER TABLE images DROP INDEX universal_name_checksum;"
 670     sql "ALTER TABLE images ALTER location DROP DEFAULT;"
 671     sql "ALTER TABLE images CHANGE COLUMN location location VARCHAR(200) NOT NULL AFTER checksum;"
 672     sql "ALTER TABLE images DROP COLUMN universal_name;"
 673     sql "ALTER TABLE images DROP COLUMN checksum;"
 674     sql "ALTER TABLE vms ALTER image_path DROP DEFAULT;"
 675     sql "ALTER TABLE vms CHANGE COLUMN image_path image_path VARCHAR(100) NOT NULL COMMENT 'Path where the image of the VM is located' AFTER image_id;"
 676     sql "DELETE FROM schema_version WHERE version_int='15';"
 677 }
 678
 679 function upgrade_to_16(){
 680     # echo "    upgrade database from version 0.15 to version 0.16"
 681     echo "      add column 'config' at table 'datacenter_tenants', enlarge 'vim_tenant_name/id'"
 682     sql "ALTER TABLE datacenter_tenants ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL AFTER passwd;"
 683     sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(256) NULL DEFAULT NULL AFTER datacenter_id;"
 684     sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(256) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;"
 685     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (16, '0.16', '0.5.2', 'enlarge vim_tenant_name and id. New config at datacenter_tenants', '2016-10-11');"
 686 }
 687 function downgrade_from_16(){
 688     # echo "    downgrade database from version 0.16 to version 0.15"
 689     echo "      remove column 'config' at table 'datacenter_tenants', restoring lenght 'vim_tenant_name/id'"
 690     sql "ALTER TABLE datacenter_tenants DROP COLUMN config;"
 691     sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL AFTER datacenter_id;"
 692     sql "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;"
 693     sql "DELETE FROM schema_version WHERE version_int='16';"
 694 }
 695
 696 function upgrade_to_17(){
 697     # echo "    upgrade database from version 0.16 to version 0.17"
 698     echo "      add column 'extended' at table 'datacenter_flavors'"
 699     sql "ALTER TABLE datacenters_flavors ADD extended varchar(2000) NULL COMMENT 'Extra description json format of additional devices';"
 700     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (17, '0.17', '0.5.3', 'Extra description json format of additional devices in datacenter_flavors', '2016-12-20');"
 701 }
 702 function downgrade_from_17(){
 703     # echo "    downgrade database from version 0.17 to version 0.16"
 704     echo "      remove column 'extended' from table 'datacenter_flavors'"
 705     sql "ALTER TABLE datacenters_flavors DROP COLUMN extended;"
 706     sql "DELETE FROM schema_version WHERE version_int='17';"
 707 }
 708
 709 function upgrade_to_18(){
 710     # echo "    upgrade database from version 0.17 to version 0.18"
 711     echo "      add columns 'floating_ip' and 'port_security' at tables 'interfaces' and 'instance_interfaces'"
 712     sql "ALTER TABLE interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';"
 713     sql "ALTER TABLE interfaces ADD port_security BOOL DEFAULT 1 NOT NULL COMMENT 'Indicates if port security must be enabled or disabled. By default it is enabled';"
 714     sql "ALTER TABLE instance_interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';"
 715     sql "ALTER TABLE instance_interfaces ADD port_security BOOL DEFAULT 1 NOT NULL COMMENT 'Indicates if port security must be enabled or disabled. By default it is enabled';"
 716     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (18, '0.18', '0.5.4', 'Add columns \'floating_ip\' and \'port_security\' at tables \'interfaces\' and \'instance_interfaces\'', '2017-01-09');"
 717 }
 718 function downgrade_from_18(){
 719     # echo "    downgrade database from version 0.18 to version 0.17"
 720     echo "      remove columns 'floating_ip' and 'port_security' from tables 'interfaces' and 'instance_interfaces'"
 721     sql "ALTER TABLE interfaces DROP COLUMN floating_ip;"
 722     sql "ALTER TABLE interfaces DROP COLUMN port_security;"
 723     sql "ALTER TABLE instance_interfaces DROP COLUMN floating_ip;"
 724     sql "ALTER TABLE instance_interfaces DROP COLUMN port_security;"
 725     sql "DELETE FROM schema_version WHERE version_int='18';"
 726 }
 727
 728 function upgrade_to_19(){
 729     # echo "    upgrade database from version 0.18 to version 0.19"
 730     echo "      add column 'boot_data' at table 'vms'"
 731     sql "ALTER TABLE vms ADD COLUMN boot_data TEXT NULL DEFAULT NULL AFTER image_path;"
 732     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (19, '0.19', '0.5.5', 'Extra Boot-data content at VNFC (vms)', '2017-01-11');"
 733 }
 734 function downgrade_from_19(){
 735     # echo "    downgrade database from version 0.19 to version 0.18"
 736     echo "      remove column 'boot_data' from table 'vms'"
 737     sql "ALTER TABLE vms DROP COLUMN boot_data;"
 738     sql "DELETE FROM schema_version WHERE version_int='19';"
 739 }
 740
 741 function upgrade_to_20(){
 742     # echo "    upgrade database from version 0.19 to version 0.20"
 743     echo "      add column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
 744     sql "ALTER TABLE instance_nets ADD sdn_net_id varchar(36) DEFAULT NULL NULL COMMENT 'Network id in ovim';"
 745     sql "ALTER TABLE instance_interfaces ADD sdn_port_id varchar(36) DEFAULT NULL NULL COMMENT 'Port id in ovim';"
 746     sql "ALTER TABLE instance_interfaces ADD compute_node varchar(100) DEFAULT NULL NULL COMMENT 'Compute node id used to specify the SDN port mapping';"
 747     sql "ALTER TABLE instance_interfaces ADD pci varchar(12) DEFAULT NULL NULL COMMENT 'PCI of the physical port in the host';"
 748     sql "ALTER TABLE instance_interfaces ADD vlan SMALLINT UNSIGNED DEFAULT NULL NULL COMMENT 'VLAN tag used by the port';"
 749     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (20, '0.20', '0.5.9', 'Added columns to store dataplane connectivity info', '2017-03-13');"
 750 }
 751 function downgrade_from_20(){
 752     # echo "    downgrade database from version 0.20 to version 0.19"
 753     echo "      remove column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
 754     sql "ALTER TABLE instance_nets DROP COLUMN sdn_net_id;"
 755     sql "ALTER TABLE instance_interfaces DROP COLUMN vlan;"
 756     sql "ALTER TABLE instance_interfaces DROP COLUMN pci;"
 757     sql "ALTER TABLE instance_interfaces DROP COLUMN compute_node;"
 758     sql "ALTER TABLE instance_interfaces DROP COLUMN sdn_port_id;"
 759     sql "DELETE FROM schema_version WHERE version_int='20';"
 760 }
 761
 762 function upgrade_to_21(){
 763     # echo "    upgrade database from version 0.20 to version 0.21"
 764     echo "      edit 'instance_nets' to allow instance_scenario_id=None"
 765     sql "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NULL;"
 766     echo "      enlarge column 'dns_address' at table 'ip_profiles'"
 767     sql "ALTER TABLE ip_profiles MODIFY dns_address varchar(255) DEFAULT NULL NULL "\
 768          "comment 'dns ip list separated by semicolon';"
 769     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (21, '0.21', '0.5.15', 'Edit instance_nets to allow instance_scenario_id=None and enlarge column dns_address at table ip_profiles', '2017-06-02');"
 770 }
 771 function downgrade_from_21(){
 772     # echo "    downgrade database from version 0.21 to version 0.20"
 773     echo "      edit 'instance_nets' to disallow instance_scenario_id=None"
 774     #Delete all lines with a instance_scenario_id=NULL in order to disable this option
 775     sql "DELETE FROM instance_nets WHERE instance_scenario_id IS NULL;"
 776     sql "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NOT NULL;"
 777     echo "      shorten column 'dns_address' at table 'ip_profiles'"
 778     sql "ALTER TABLE ip_profiles MODIFY dns_address varchar(64) DEFAULT NULL NULL;"
 779     sql "DELETE FROM schema_version WHERE version_int='21';"
 780 }
 781
 782 function upgrade_to_22(){
 783     # echo "    upgrade database from version 0.21 to version 0.22"
 784     echo "      Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
 785     sql "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 786     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (22, '0.22', '0.5.16', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-06-02');"
 787 }
 788 function downgrade_from_22(){
 789     # echo "    downgrade database from version 0.22 to version 0.21"
 790     echo "      Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
 791     sql "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 792     sql "DELETE FROM schema_version WHERE version_int='22';"
 793 }
 794
 795 function upgrade_to_23(){
 796     # echo "    upgrade database from version 0.22 to version 0.23"
 797     echo "      add column 'availability_zone' at table 'vms'"
 798     sql "ALTER TABLE mano_db.vms ADD COLUMN availability_zone VARCHAR(255) NULL AFTER modified_at;"
 799     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (23, '0.23', '0.5.20',"\
 800         "'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-08-29');"
 801 }
 802 function downgrade_from_23(){
 803     # echo "    downgrade database from version 0.23 to version 0.22"
 804     echo "      remove column 'availability_zone' from table 'vms'"
 805     sql "ALTER TABLE mano_db.vms DROP COLUMN availability_zone;"
 806     sql "DELETE FROM schema_version WHERE version_int='23';"
 807 }
 808
 809 function upgrade_to_24(){
 810     # echo "    upgrade database from version 0.23 to version 0.24"
 811     echo "      Add 'count' to table 'vms'"
 812
 813     sql "ALTER TABLE vms ADD COLUMN count SMALLINT NOT NULL DEFAULT '1' AFTER vnf_id;"
 814     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
 815          "VALUES (24, '0.24', '0.5.21', 'Added vnfd fields', '2017-08-29');"
 816 }
 817 function downgrade_from_24(){
 818     # echo "    downgrade database from version 0.24 to version 0.23"
 819     echo "      Remove 'count' from table 'vms'"
 820     sql "ALTER TABLE vms DROP COLUMN count;"
 821     sql "DELETE FROM schema_version WHERE version_int='24';"
 822 }
 823 function upgrade_to_25(){
 824     # echo "    upgrade database from version 0.24 to version 0.25"
 825     echo "      Add 'osm_id','short_name','vendor' to tables 'vnfs', 'scenarios'"
 826     for table in vnfs scenarios; do
 827         sql "ALTER TABLE $table ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid, "\
 828              "ADD UNIQUE INDEX osm_id_tenant_id (osm_id, tenant_id), "\
 829              "ADD COLUMN short_name VARCHAR(255) NULL AFTER name, "\
 830              "ADD COLUMN vendor VARCHAR(255) NULL AFTER description;"
 831     done
 832     sql "ALTER TABLE vnfs ADD COLUMN mgmt_access VARCHAR(2000) NULL AFTER vendor;"
 833     sql "ALTER TABLE vms ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
 834     sql "ALTER TABLE sce_vnfs ADD COLUMN member_vnf_index SMALLINT(6) NULL DEFAULT NULL AFTER uuid;"
 835     echo "      Add 'security_group' to table 'ip_profiles'"
 836     sql "ALTER TABLE ip_profiles ADD COLUMN security_group VARCHAR(255) NULL DEFAULT NULL AFTER dhcp_count;"
 837
 838     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
 839          "VALUES (25, '0.25', '0.5.22', 'Added osm_id to vnfs,scenarios', '2017-09-01');"
 840 }
 841 function downgrade_from_25(){
 842     # echo "    downgrade database from version 0.25 to version 0.24"
 843     echo "      Remove 'osm_id','short_name','vendor' from tables 'vnfs', 'scenarios'"
 844     for table in vnfs scenarios; do
 845         sql "ALTER TABLE $table DROP INDEX  osm_id_tenant_id, DROP COLUMN osm_id, "\
 846              "DROP COLUMN short_name, DROP COLUMN vendor;"
 847     done
 848     sql "ALTER TABLE vnfs DROP COLUMN mgmt_access;"
 849     sql "ALTER TABLE vms DROP COLUMN osm_id;"
 850     sql "ALTER TABLE sce_vnfs DROP COLUMN member_vnf_index;"
 851     echo "      Remove 'security_group' from table 'ip_profiles'"
 852     sql "ALTER TABLE ip_profiles DROP COLUMN security_group;"
 853
 854     sql "DELETE FROM schema_version WHERE version_int='25';"
 855 }
 856
 857 function upgrade_to_26(){
 858     echo "      Add name to table datacenter_tenants"
 859     sql "ALTER TABLE datacenter_tenants ADD COLUMN name VARCHAR(255) NULL AFTER uuid;"
 860     sql "UPDATE datacenter_tenants as dt join datacenters as d on dt.datacenter_id = d.uuid set dt.name=d.name;"
 861     echo "      Add 'SCHEDULED' to 'status' at tables 'instance_nets', 'instance_vms'"
 862     sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\
 863          "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') "\
 864          "NOT NULL DEFAULT 'BUILD';"
 865     sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','DOWN','BUILD','ERROR',"\
 866          "'VIM_ERROR','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD';"
 867     echo "      Enlarge pci at instance_interfaces to allow extended pci for SDN por mapping"
 868     sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(50) NULL DEFAULT NULL COMMENT 'PCI of the "\
 869         "physical port in the host' AFTER compute_node;"
 870
 871     for t in flavor image; do
 872         echo "      Change 'datacenters_${t}s' to point to datacenter_tenant, add status, vim_info"
 873         sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_vim_id VARCHAR(36) NULL DEFAULT NULL AFTER "\
 874             "datacenter_id, ADD COLUMN status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','DELETED',"\
 875             "'SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD' AFTER vim_id, ADD COLUMN vim_info "\
 876             "TEXT NULL AFTER status;"
 877         sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.datacenter_id=df.datacenter_id "\
 878             "set df.datacenter_vim_id=dt.uuid;"
 879         sql "DELETE FROM datacenters_${t}s WHERE datacenter_vim_id is NULL;"
 880         sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_vim_id datacenter_vim_id VARCHAR(36) NOT NULL;"
 881         sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK_datacenters_${t}s_datacenter_tenants FOREIGN KEY "\
 882             "(datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE;"
 883         sql "ALTER TABLE datacenters_${t}s DROP FOREIGN KEY FK__datacenters_${t:0:1};"
 884         sql "ALTER TABLE datacenters_${t}s DROP COLUMN datacenter_id;"
 885         done
 886
 887     echo "      Decoupling 'instance_interfaces' from scenarios/vnfs to allow scale actions"
 888     sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(128) NULL DEFAULT NULL;"
 889     sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NULL DEFAULT NULL;"
 890         sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids"
 891         sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\
 892             "REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 893
 894     echo "      Decoupling 'instance_vms' from scenarios/vnfs to allow scale actions"
 895     sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(128) NULL DEFAULT NULL;"
 896     sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NULL DEFAULT NULL;"
 897         sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;"
 898         sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\
 899             "REFERENCES vms (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 900
 901     echo "      Decoupling 'instance_nets' from scenarios/vnfs to allow scale actions"
 902     sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(128) NULL DEFAULT NULL;"
 903
 904     echo "      Decoupling 'instance_scenarios' from scenarios"
 905     sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NULL DEFAULT NULL;"
 906         sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;"
 907         sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\
 908             "REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 909
 910     echo "      Create table instance_actions, vim_actions"
 911     sql "CREATE TABLE IF NOT EXISTS instance_actions (
 912             uuid VARCHAR(36) NOT NULL,
 913             tenant_id VARCHAR(36) NULL DEFAULT NULL,
 914             instance_id VARCHAR(36) NULL DEFAULT NULL,
 915             description VARCHAR(64) NULL DEFAULT NULL COMMENT 'CREATE, DELETE, SCALE OUT/IN, ...',
 916             number_tasks SMALLINT(6) NOT NULL DEFAULT '1',
 917             number_done SMALLINT(6) NOT NULL DEFAULT '0',
 918             number_failed SMALLINT(6) NOT NULL DEFAULT '0',
 919             created_at DOUBLE NOT NULL,
 920             modified_at DOUBLE NULL DEFAULT NULL,
 921             PRIMARY KEY (uuid),
 922         INDEX FK_actions_tenants (tenant_id),
 923         CONSTRAINT FK_actions_tenant FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 924                 COMMENT='Contains client actions over instances'
 925         COLLATE='utf8_general_ci'
 926         ENGINE=InnoDB;"
 927
 928     sql "CREATE TABLE IF NOT EXISTS vim_actions (
 929             instance_action_id VARCHAR(36) NOT NULL,
 930             task_index INT(6) NOT NULL,
 931             datacenter_vim_id VARCHAR(36) NOT NULL,
 932             vim_id VARCHAR(64) NULL DEFAULT NULL,
 933             action VARCHAR(36) NOT NULL COMMENT 'CREATE,DELETE,START,STOP...',
 934             item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored',
 935             item_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'uuid of the entry in the table',
 936             status ENUM('SCHEDULED', 'BUILD', 'DONE', 'FAILED', 'SUPERSEDED') NOT NULL DEFAULT 'SCHEDULED',
 937             extra TEXT NULL DEFAULT NULL COMMENT 'json with params:, depends_on: for the task',
 938             error_msg VARCHAR(1024) NULL DEFAULT NULL,
 939             created_at DOUBLE NOT NULL,
 940             modified_at DOUBLE NULL DEFAULT NULL,
 941             PRIMARY KEY (task_index, instance_action_id),
 942         INDEX FK_actions_instance_actions (instance_action_id),
 943         CONSTRAINT FK_actions_instance_actions FOREIGN KEY (instance_action_id) REFERENCES instance_actions (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 944         INDEX FK_actions_vims (datacenter_vim_id),
 945         CONSTRAINT FK_actions_vims FOREIGN KEY (datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 946         COMMENT='Table with the individual VIM actions.'
 947         COLLATE='utf8_general_ci'
 948         ENGINE=InnoDB;"
 949
 950     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
 951          "VALUES (26, '0.26', '0.5.23', 'Several changes', '2017-09-09');"
 952 }
 953 function downgrade_from_26(){
 954     echo "      Remove name from table datacenter_tenants"
 955     sql "ALTER TABLE datacenter_tenants DROP COLUMN name;"
 956     echo "      Remove 'SCHEDULED' from the 'status' at tables 'instance_nets', 'instance_vms'"
 957     sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\
 958          "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';"
 959     sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR','VIM_ERROR',"\
 960          "'INACTIVE','DELETED') NOT NULL DEFAULT 'BUILD';"
 961     echo "      Shorten back pci at instance_interfaces to allow extended pci for SDN por mapping"
 962     sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(12) NULL DEFAULT NULL COMMENT 'PCI of the "\
 963         "physical port in the host' AFTER compute_node;"
 964
 965     for t in flavor image; do
 966         echo "      Restore back 'datacenters_${t}s'"
 967         sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_id VARCHAR(36) NULL DEFAULT NULL AFTER "\
 968             "${t}_id, DROP COLUMN status, DROP COLUMN vim_info ;"
 969         sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.uuid=df.datacenter_vim_id set "\
 970             "df.datacenter_id=dt.datacenter_id;"
 971         sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL;"
 972         sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK__datacenters_${t:0:1} FOREIGN KEY "\
 973             "(datacenter_id) REFERENCES datacenters (uuid), DROP FOREIGN KEY FK_datacenters_${t}s_datacenter_tenants, "\
 974             "DROP COLUMN datacenter_vim_id;"
 975     done
 976
 977     echo "      Restore back 'instance_interfaces' coupling to scenarios/vnfs"
 978     sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(36) NULL DEFAULT NULL;"
 979         sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids"
 980     sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NOT NULL;"
 981         sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\
 982             "REFERENCES interfaces (uuid);"
 983
 984     echo "      Restore back 'instance_vms' coupling to scenarios/vnfs"
 985     echo "      Decoupling 'instance vms' from scenarios/vnfs to allow scale actions"
 986     sql "UPDATE instance_vms SET vim_vm_id='' WHERE vim_vm_id is NULL;"
 987     sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(36) NOT NULL;"
 988         sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;"
 989     sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NOT NULL;"
 990         sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\
 991             "REFERENCES vms (uuid);"
 992
 993     echo "      Restore back 'instance_nets' coupling to scenarios/vnfs"
 994     sql "UPDATE instance_nets SET vim_net_id='' WHERE vim_net_id is NULL;"
 995     sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(36) NOT NULL;"
 996
 997     echo "      Restore back  'instance_scenarios' coupling to scenarios"
 998         sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;"
 999     sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NOT NULL;"
1000         sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\
1001             "REFERENCES scenarios (uuid);"
1002
1003     echo "      Delete table instance_actions"
1004     sql "DROP TABLE IF EXISTS vim_actions"
1005     sql "DROP TABLE IF EXISTS instance_actions"
1006     sql "DELETE FROM schema_version WHERE version_int='26';"
1007 }
1008
1009 function upgrade_to_27(){
1010     echo "      Added 'encrypted_RO_priv_key','RO_pub_key' to table 'nfvo_tenants'"
1011     sql "ALTER TABLE nfvo_tenants ADD COLUMN encrypted_RO_priv_key VARCHAR(2000) NULL AFTER description;"
1012     sql "ALTER TABLE nfvo_tenants ADD COLUMN RO_pub_key VARCHAR(510) NULL AFTER encrypted_RO_priv_key;"
1013
1014     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1015          "VALUES (27, '0.27', '0.5.25', 'Added encrypted_RO_priv_key,RO_pub_key to table nfvo_tenants', '2017-09-29');"
1016 }
1017 function downgrade_from_27(){
1018     echo "      Remove 'encrypted_RO_priv_key','RO_pub_key' from table 'nfvo_tenants'"
1019     sql "ALTER TABLE nfvo_tenants DROP COLUMN encrypted_RO_priv_key;"
1020     sql "ALTER TABLE nfvo_tenants DROP COLUMN RO_pub_key;"
1021     sql "DELETE FROM schema_version WHERE version_int='27';"
1022 }
1023 function upgrade_to_28(){
1024     echo "      [Adding necessary tables for VNFFG]"
1025     echo "      Adding sce_vnffgs"
1026     sql "CREATE TABLE IF NOT EXISTS sce_vnffgs (
1027             uuid VARCHAR(36) NOT NULL,
1028             tenant_id VARCHAR(36) NULL DEFAULT NULL,
1029             name VARCHAR(255) NOT NULL,
1030             description VARCHAR(255) NULL DEFAULT NULL,
1031             vendor VARCHAR(255) NULL DEFAULT NULL,
1032             scenario_id VARCHAR(36) NOT NULL,
1033             created_at DOUBLE NOT NULL,
1034             modified_at DOUBLE NULL DEFAULT NULL,
1035         PRIMARY KEY (uuid),
1036         INDEX FK_scenarios_sce_vnffg (scenario_id),
1037         CONSTRAINT FK_scenarios_vnffg FOREIGN KEY (tenant_id) REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
1038         COLLATE='utf8_general_ci'
1039         ENGINE=InnoDB;"
1040     echo "      Adding sce_rsps"
1041     sql "CREATE TABLE IF NOT EXISTS sce_rsps (
1042             uuid VARCHAR(36) NOT NULL,
1043             tenant_id VARCHAR(36) NULL DEFAULT NULL,
1044             name VARCHAR(255) NOT NULL,
1045             sce_vnffg_id VARCHAR(36) NOT NULL,
1046             created_at DOUBLE NOT NULL,
1047             modified_at DOUBLE NULL DEFAULT NULL,
1048         PRIMARY KEY (uuid),
1049         INDEX FK_sce_vnffgs_rsp (sce_vnffg_id),
1050         CONSTRAINT FK_sce_vnffgs_rsp FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
1051         COLLATE='utf8_general_ci'
1052         ENGINE=InnoDB;"
1053     echo "      Adding sce_rsp_hops"
1054     sql "CREATE TABLE IF NOT EXISTS sce_rsp_hops (
1055             uuid VARCHAR(36) NOT NULL,
1056             if_order INT DEFAULT 0 NOT NULL,
1057             interface_id VARCHAR(36) NOT NULL,
1058             sce_vnf_id VARCHAR(36) NOT NULL,
1059             sce_rsp_id VARCHAR(36) NOT NULL,
1060             created_at DOUBLE NOT NULL,
1061             modified_at DOUBLE NULL DEFAULT NULL,
1062         PRIMARY KEY (uuid),
1063         INDEX FK_interfaces_rsp_hop (interface_id),
1064         INDEX FK_sce_vnfs_rsp_hop (sce_vnf_id),
1065         INDEX FK_sce_rsps_rsp_hop (sce_rsp_id),
1066         CONSTRAINT FK_interfaces_rsp_hop FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
1067         CONSTRAINT FK_sce_vnfs_rsp_hop FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
1068         CONSTRAINT FK_sce_rsps_rsp_hop FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
1069         COLLATE='utf8_general_ci'
1070         ENGINE=InnoDB;"
1071     echo "      Adding sce_classifiers"
1072     sql "CREATE TABLE IF NOT EXISTS sce_classifiers (
1073             uuid VARCHAR(36) NOT NULL,
1074             tenant_id VARCHAR(36) NULL DEFAULT NULL,
1075             name VARCHAR(255) NOT NULL,
1076             sce_vnffg_id VARCHAR(36) NOT NULL,
1077             sce_rsp_id VARCHAR(36) NOT NULL,
1078             sce_vnf_id VARCHAR(36) NOT NULL,
1079             interface_id VARCHAR(36) NOT NULL,
1080             created_at DOUBLE NOT NULL,
1081             modified_at DOUBLE NULL DEFAULT NULL,
1082         PRIMARY KEY (uuid),
1083         INDEX FK_sce_vnffgs_classifier (sce_vnffg_id),
1084         INDEX FK_sce_rsps_classifier (sce_rsp_id),
1085         INDEX FK_sce_vnfs_classifier (sce_vnf_id),
1086         INDEX FK_interfaces_classifier (interface_id),
1087         CONSTRAINT FK_sce_vnffgs_classifier FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
1088         CONSTRAINT FK_sce_rsps_classifier FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
1089         CONSTRAINT FK_sce_vnfs_classifier FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
1090         CONSTRAINT FK_interfaces_classifier FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
1091         COLLATE='utf8_general_ci'
1092         ENGINE=InnoDB;"
1093     echo "      Adding sce_classifier_matches"
1094     sql "CREATE TABLE IF NOT EXISTS sce_classifier_matches (
1095             uuid VARCHAR(36) NOT NULL,
1096             ip_proto VARCHAR(2) NOT NULL,
1097             source_ip VARCHAR(16) NOT NULL,
1098             destination_ip VARCHAR(16) NOT NULL,
1099             source_port VARCHAR(5) NOT NULL,
1100             destination_port VARCHAR(5) NOT NULL,
1101             sce_classifier_id VARCHAR(36) NOT NULL,
1102             created_at DOUBLE NOT NULL,
1103             modified_at DOUBLE NULL DEFAULT NULL,
1104         PRIMARY KEY (uuid),
1105         INDEX FK_classifiers_classifier_match (sce_classifier_id),
1106         CONSTRAINT FK_sce_classifiers_classifier_match FOREIGN KEY (sce_classifier_id) REFERENCES sce_classifiers (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
1107         COLLATE='utf8_general_ci'
1108         ENGINE=InnoDB;"
1109
1110     echo "      [Adding necessary tables for VNFFG-SFC instance mapping]"
1111     echo "      Adding instance_sfis"
1112     sql "CREATE TABLE IF NOT EXISTS instance_sfis (
1113           uuid varchar(36) NOT NULL,
1114           instance_scenario_id varchar(36) NOT NULL,
1115           vim_sfi_id varchar(36) DEFAULT NULL,
1116           sce_rsp_hop_id varchar(36) DEFAULT NULL,
1117           datacenter_id varchar(36) DEFAULT NULL,
1118           datacenter_tenant_id varchar(36) DEFAULT NULL,
1119           status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
1120           error_msg varchar(1024) DEFAULT NULL,
1121           vim_info text,
1122           created_at double NOT NULL,
1123           modified_at double DEFAULT NULL,
1124           PRIMARY KEY (uuid),
1125       KEY FK_instance_sfis_instance_scenarios (instance_scenario_id),
1126       KEY FK_instance_sfis_sce_rsp_hops (sce_rsp_hop_id),
1127       KEY FK_instance_sfis_datacenters (datacenter_id),
1128       KEY FK_instance_sfis_datacenter_tenants (datacenter_tenant_id),
1129       CONSTRAINT FK_instance_sfis_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
1130       CONSTRAINT FK_instance_sfis_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
1131       CONSTRAINT FK_instance_sfis_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
1132       CONSTRAINT FK_instance_sfis_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
1133       COLLATE='utf8_general_ci'
1134       ENGINE=InnoDB;"
1135     echo "      Adding instance_sfs"
1136     sql "CREATE TABLE IF NOT EXISTS instance_sfs (
1137           uuid varchar(36) NOT NULL,
1138           instance_scenario_id varchar(36) NOT NULL,
1139           vim_sf_id varchar(36) DEFAULT NULL,
1140           sce_rsp_hop_id varchar(36) DEFAULT NULL,
1141           datacenter_id varchar(36) DEFAULT NULL,
1142           datacenter_tenant_id varchar(36) DEFAULT NULL,
1143           status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
1144           error_msg varchar(1024) DEFAULT NULL,
1145           vim_info text,
1146           created_at double NOT NULL,
1147           modified_at double DEFAULT NULL,
1148       PRIMARY KEY (uuid),
1149       KEY FK_instance_sfs_instance_scenarios (instance_scenario_id),
1150       KEY FK_instance_sfs_sce_rsp_hops (sce_rsp_hop_id),
1151       KEY FK_instance_sfs_datacenters (datacenter_id),
1152       KEY FK_instance_sfs_datacenter_tenants (datacenter_tenant_id),
1153       CONSTRAINT FK_instance_sfs_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
1154       CONSTRAINT FK_instance_sfs_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
1155       CONSTRAINT FK_instance_sfs_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
1156       CONSTRAINT FK_instance_sfs_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
1157       COLLATE='utf8_general_ci'
1158       ENGINE=InnoDB;"
1159     echo "      Adding instance_classifications"
1160     sql "CREATE TABLE IF NOT EXISTS instance_classifications (
1161           uuid varchar(36) NOT NULL,
1162           instance_scenario_id varchar(36) NOT NULL,
1163           vim_classification_id varchar(36) DEFAULT NULL,
1164           sce_classifier_match_id varchar(36) DEFAULT NULL,
1165           datacenter_id varchar(36) DEFAULT NULL,
1166           datacenter_tenant_id varchar(36) DEFAULT NULL,
1167           status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
1168           error_msg varchar(1024) DEFAULT NULL,
1169           vim_info text,
1170           created_at double NOT NULL,
1171           modified_at double DEFAULT NULL,
1172       PRIMARY KEY (uuid),
1173       KEY FK_instance_classifications_instance_scenarios (instance_scenario_id),
1174       KEY FK_instance_classifications_sce_classifier_matches (sce_classifier_match_id),
1175       KEY FK_instance_classifications_datacenters (datacenter_id),
1176       KEY FK_instance_classifications_datacenter_tenants (datacenter_tenant_id),
1177       CONSTRAINT FK_instance_classifications_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
1178       CONSTRAINT FK_instance_classifications_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
1179       CONSTRAINT FK_instance_classifications_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
1180       CONSTRAINT FK_instance_classifications_sce_classifier_matches FOREIGN KEY (sce_classifier_match_id) REFERENCES sce_classifier_matches (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
1181       COLLATE='utf8_general_ci'
1182       ENGINE=InnoDB;"
1183     echo "      Adding instance_sfps"
1184     sql "CREATE TABLE IF NOT EXISTS instance_sfps (
1185           uuid varchar(36) NOT NULL,
1186           instance_scenario_id varchar(36) NOT NULL,
1187           vim_sfp_id varchar(36) DEFAULT NULL,
1188           sce_rsp_id varchar(36) DEFAULT NULL,
1189           datacenter_id varchar(36) DEFAULT NULL,
1190           datacenter_tenant_id varchar(36) DEFAULT NULL,
1191           status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
1192           error_msg varchar(1024) DEFAULT NULL,
1193           vim_info text,
1194           created_at double NOT NULL,
1195           modified_at double DEFAULT NULL,
1196       PRIMARY KEY (uuid),
1197       KEY FK_instance_sfps_instance_scenarios (instance_scenario_id),
1198       KEY FK_instance_sfps_sce_rsps (sce_rsp_id),
1199       KEY FK_instance_sfps_datacenters (datacenter_id),
1200       KEY FK_instance_sfps_datacenter_tenants (datacenter_tenant_id),
1201       CONSTRAINT FK_instance_sfps_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid),
1202       CONSTRAINT FK_instance_sfps_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid),
1203       CONSTRAINT FK_instance_sfps_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE,
1204       CONSTRAINT FK_instance_sfps_sce_rsps FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON DELETE SET NULL ON UPDATE CASCADE)
1205       COLLATE='utf8_general_ci'
1206       ENGINE=InnoDB;"
1207
1208
1209     echo "      [Altering vim_actions table]"
1210     sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces','instance_sfis','instance_sfs','instance_classifications','instance_sfps') NOT NULL COMMENT 'table where the item is stored'"
1211
1212     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1213          "VALUES (28, '0.28', '0.5.28', 'Adding VNFFG-related tables', '2017-11-20');"
1214 }
1215 function downgrade_from_28(){
1216     echo "      [Undo adding the VNFFG tables]"
1217     echo "      Dropping instance_sfps"
1218     sql "DROP TABLE IF EXISTS instance_sfps;"
1219     echo "      Dropping sce_classifications"
1220     sql "DROP TABLE IF EXISTS instance_classifications;"
1221     echo "      Dropping instance_sfs"
1222     sql "DROP TABLE IF EXISTS instance_sfs;"
1223     echo "      Dropping instance_sfis"
1224     sql "DROP TABLE IF EXISTS instance_sfis;"
1225     echo "      Dropping sce_classifier_matches"
1226     echo "      [Undo adding the VNFFG-SFC instance mapping tables]"
1227     sql "DROP TABLE IF EXISTS sce_classifier_matches;"
1228     echo "      Dropping sce_classifiers"
1229     sql "DROP TABLE IF EXISTS sce_classifiers;"
1230     echo "      Dropping sce_rsp_hops"
1231     sql "DROP TABLE IF EXISTS sce_rsp_hops;"
1232     echo "      Dropping sce_rsps"
1233     sql "DROP TABLE IF EXISTS sce_rsps;"
1234     echo "      Dropping sce_vnffgs"
1235     sql "DROP TABLE IF EXISTS sce_vnffgs;"
1236     echo "      [Altering vim_actions table]"
1237     sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored'"
1238     sql "DELETE FROM schema_version WHERE version_int='28';"
1239 }
1240 function upgrade_to_29(){
1241     echo "      Change 'member_vnf_index' from int to str at 'sce_vnfs'"
1242     sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index VARCHAR(255) NULL DEFAULT NULL AFTER uuid;"
1243     echo "      Add osm_id to 'nets's and 'sce_nets'"
1244     sql "ALTER TABLE nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
1245     sql "ALTER TABLE sce_nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
1246     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1247          "VALUES (29, '0.29', '0.5.59', 'Change member_vnf_index to str accordingly to the model', '2018-04-11');"
1248 }
1249 function downgrade_from_29(){
1250     echo "      Change back 'member_vnf_index' from str to int at 'sce_vnfs'"
1251     sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index SMALLINT NULL DEFAULT NULL AFTER uuid;"
1252     echo "      Remove osm_id from 'nets's and 'sce_nets'"
1253     sql "ALTER TABLE nets DROP COLUMN osm_id;"
1254     sql "ALTER TABLE sce_nets DROP COLUMN osm_id;"
1255     sql "DELETE FROM schema_version WHERE version_int='29';"
1256 }
1257 function upgrade_to_30(){
1258     echo "      Add 'image_list' at 'vms' to allocate alternative images"
1259     sql "ALTER TABLE vms ADD COLUMN image_list TEXT NULL COMMENT 'Alternative images' AFTER image_id;"
1260     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1261          "VALUES (30, '0.30', '0.5.60', 'Add image_list to vms', '2018-04-24');"
1262 }
1263 function downgrade_from_30(){
1264     echo "      Remove back 'image_list' from 'vms' to allocate alternative images"
1265     sql "ALTER TABLE vms DROP COLUMN image_list;"
1266     sql "DELETE FROM schema_version WHERE version_int='30';"
1267 }
1268 function upgrade_to_31(){
1269     echo "      Add 'vim_network_name' at 'sce_nets'"
1270     sql "ALTER TABLE sce_nets ADD COLUMN vim_network_name VARCHAR(255) NULL DEFAULT NULL AFTER description;"
1271     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1272          "VALUES (31, '0.31', '0.5.61', 'Add vim_network_name to sce_nets', '2018-05-03');"
1273 }
1274 function downgrade_from_31(){
1275     echo "      Remove back 'vim_network_name' from 'sce_nets'"
1276     sql "ALTER TABLE sce_nets DROP COLUMN vim_network_name;"
1277     sql "DELETE FROM schema_version WHERE version_int='31';"
1278 }
1279 function upgrade_to_32(){
1280     echo "      Add 'vim_name' to 'instance_vms'"
1281     sql "ALTER TABLE instance_vms ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_vm_id;"
1282     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1283          "VALUES (32, '0.32', '0.5.70', 'Add vim_name to instance vms', '2018-06-28');"
1284 }
1285 function downgrade_from_32(){
1286     echo "      Remove back 'vim_name' from 'instance_vms'"
1287     sql "ALTER TABLE instance_vms DROP COLUMN vim_name;"
1288     sql "DELETE FROM schema_version WHERE version_int='32';"
1289 }
1290
1291 function upgrade_to_33(){
1292     echo "      Add PDU information to 'vms'"
1293     sql "ALTER TABLE vms ADD COLUMN pdu_type VARCHAR(255) NULL DEFAULT NULL AFTER osm_id;"
1294     sql "ALTER TABLE instance_nets ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_net_id;"
1295     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1296          "VALUES (33, '0.33', '0.5.82', 'Add pdu information to vms', '2018-11-13');"
1297 }
1298 function downgrade_from_33(){
1299     echo "      Remove back PDU information from 'vms'"
1300     sql "ALTER TABLE vms DROP COLUMN pdu_type;"
1301     sql "ALTER TABLE instance_nets DROP COLUMN vim_name;"
1302     sql "DELETE FROM schema_version WHERE version_int='33';"
1303 }
1304 function upgrade_to_X(){
1305     echo "      change 'datacenter_nets'"
1306     sql "ALTER TABLE datacenter_nets ADD COLUMN vim_tenant_id VARCHAR(36) NOT NULL AFTER datacenter_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id, vim_tenant_id);"
1307 }
1308 function downgrade_from_X(){
1309     echo "      Change back 'datacenter_nets'"
1310     sql "ALTER TABLE datacenter_nets DROP COLUMN vim_tenant_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id);"
1311 }
1312 function upgrade_to_34() {
1313     echo "      Create databases required for WIM features"
1314     script="$(find "${DBUTILS}/migrations/up" -iname "34*.sql" | tail -1)"
1315     sql "source ${script}"
1316 }
1317 function downgrade_from_34() {
1318     echo "      Drop databases required for WIM features"
1319     script="$(find "${DBUTILS}/migrations/down" -iname "34*.sql" | tail -1)"
1320     sql "source ${script}"
1321 }
1322 function upgrade_to_35(){
1323     echo "      Create databases required for WIM features"
1324     script="$(find "${DBUTILS}/migrations/up" -iname "35*.sql" | tail -1)"
1325     sql "source ${script}"
1326 }
1327 function downgrade_from_35(){
1328     echo "      Drop databases required for WIM features"
1329     script="$(find "${DBUTILS}/migrations/down" -iname "35*.sql" | tail -1)"
1330     sql "source ${script}"
1331 }
1332 function upgrade_to_36(){
1333     echo "      Allow null for image_id at 'vms'"
1334     sql "ALTER TABLE vms ALTER image_id DROP DEFAULT;"
1335     sql "ALTER TABLE vms CHANGE COLUMN image_id image_id VARCHAR(36) NULL COMMENT 'Link to image table' AFTER " \
1336         "flavor_id;"
1337     echo "      Enlarge config at 'wims' and 'wim_accounts'"
1338     sql "ALTER TABLE wims CHANGE COLUMN config config TEXT NULL DEFAULT NULL AFTER wim_url;"
1339     sql "ALTER TABLE wim_accounts CHANGE COLUMN config config TEXT NULL DEFAULT NULL AFTER password;"
1340     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
1341          "VALUES (36, '0.36', '0.6.03', 'Allow vm without image_id for PDUs', '2018-12-19');"
1342 }
1343 function downgrade_from_36(){
1344     echo "      Force back not null for image_id at 'vms'"
1345     sql "ALTER TABLE vms ALTER image_id DROP DEFAULT;"
1346     sql "ALTER TABLE vms CHANGE COLUMN image_id image_id VARCHAR(36) NOT NULL COMMENT 'Link to image table' AFTER " \
1347         "flavor_id;"
1348     # For downgrade do not restore wims/wim_accounts config to varchar 4000
1349     sql "DELETE FROM schema_version WHERE version_int='36';"
1350 }
1351 function upgrade_to_37(){
1352     echo "      Adding the enum tags for SFC"
1353     sql "ALTER TABLE vim_wim_actions " \
1354         "MODIFY COLUMN item " \
1355         "ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces'," \
1356             "'instance_sfis','instance_sfs','instance_classifications','instance_sfps','instance_wim_nets') " \
1357         "NOT NULL COMMENT 'table where the item is stored';"
1358     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) " \
1359          "VALUES (37, '0.37', '0.6.09', 'Adding the enum tags for SFC', '2019-02-07');"
1360 }
1361 function downgrade_from_37(){
1362     echo "      Adding the enum tags for SFC isn't going to be reversed"
1363     # It doesn't make sense to reverse to a bug state.
1364     sql "DELETE FROM schema_version WHERE version_int='37';"
1365 }
1366 function upgrade_to_38(){
1367     echo "      Change vim_wim_actions, add worker, related"
1368     sql "ALTER TABLE vim_wim_actions ADD COLUMN worker VARCHAR(64) NULL AFTER task_index, " \
1369             "ADD COLUMN related VARCHAR(36) NULL AFTER worker, " \
1370             "CHANGE COLUMN status status ENUM('SCHEDULED','BUILD','DONE','FAILED','SUPERSEDED','FINISHED') " \
1371             "NOT NULL DEFAULT 'SCHEDULED' AFTER item_id;"
1372         sql "UPDATE vim_wim_actions set related=item_id;"
1373         echo "      Change DONE to FINISHED when DELETE has been completed"
1374         sql "UPDATE vim_wim_actions as v1 join vim_wim_actions as v2 on (v1.action='CREATE' or v1.action='FIND') and " \
1375             "v2.action='DELETE' and (v2.status='SUPERSEDED' or v2.status='DONE') and v1.item_id=v2.item_id " \
1376         "SET v1.status='FINISHED', v2.status='FINISHED';"
1377     echo "      Add osm_id to instance_nets"
1378     sql "ALTER TABLE instance_nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;"
1379     echo "      Add related to instance_xxxx"
1380     for table in instance_classifications instance_nets instance_sfis instance_sfps instance_sfs \
1381         instance_vms
1382     do
1383         sql "ALTER TABLE $table ADD COLUMN related VARCHAR(36) NULL AFTER vim_info;"
1384         sql "UPDATE $table set related=uuid;"
1385     done
1386     sql "ALTER TABLE instance_wim_nets ADD COLUMN related VARCHAR(36) NULL AFTER wim_info;"
1387         sql "UPDATE instance_wim_nets set related=uuid;"
1388
1389     sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) " \
1390         "VALUES (38, '0.38', '0.6.11', 'Adding related to vim_wim_actions', '2019-03-07');"
1391
1392 }
1393 function downgrade_from_38(){
1394     echo "      Change vim_wim_actions, delete worker, related"
1395         sql "UPDATE vim_wim_actions SET status='DONE' WHERE status='FINISHED';"
1396     sql "ALTER TABLE vim_wim_actions DROP COLUMN worker, DROP COLUMN related, " \
1397             "CHANGE COLUMN status status ENUM('SCHEDULED','BUILD','DONE','FAILED','SUPERSEDED') " \
1398             "NOT NULL DEFAULT 'SCHEDULED' AFTER item_id;"
1399     echo "      Remove related from instance_xxxx"
1400     for table in instance_classifications instance_nets instance_wim_netsinstance_sfis instance_sfps instance_sfs \
1401         instance_vms
1402     do
1403         sql "ALTER TABLE $table DROP COLUMN related;"
1404     done
1405     echo "      Remove osm_id from instance_nets"
1406     sql "ALTER TABLE instance_nets DROP COLUMN osm_id;"
1407     sql "DELETE FROM schema_version WHERE version_int='38';"
1408 }
1409
1410 #TODO ... put functions here
1411
1412
1413 function del_schema_version_process()
1414 {
1415     echo "DELETE FROM schema_version WHERE version_int='0';" | $DBCMD ||
1416         ! echo "    ERROR writing on schema_version" >&2 || exit 1
1417 }
1418
1419 function set_schema_version_process()
1420 {
1421     echo "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES "\
1422         "(0, '0.0', '0.0.0', 'migration from $DATABASE_VER_NUM to $DB_VERSION backup: $BACKUP_FILE',"\
1423         "'$(date +%Y-%m-%d)');" | $DBCMD ||
1424         ! echo  "    Cannot set database at migration process writing into schema_version" >&2 || exit 1
1425
1426 }
1427
1428 function rollback_db()
1429 {
1430     if echo $DATABASE_PROCESS | grep -q init ; then   # Empty database. No backup needed
1431         echo "    Aborted! Rollback database not needed" && exit 1
1432     else   # migration a non empty database or Recovering a migration process
1433         cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM && echo "    Aborted! Rollback database OK" &&
1434             del_schema_version_process && rm -f "$BACKUP_FILE" && exit 1
1435         echo "    Aborted! Rollback database FAIL" && exit 1
1436     fi
1437 }
1438
1439 function sql()    # send a sql command
1440 {
1441     echo "$*" | $DBCMD || ! echo "    ERROR with command '$*'" || rollback_db
1442     return 0
1443 }
1444
1445 function migrate()
1446 {
1447     #UPGRADE DATABASE step by step
1448     while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
1449     do
1450         echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
1451         DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
1452         upgrade_to_${DATABASE_VER_NUM}
1453         #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
1454         #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
1455         #$FILE_ || exit -1  # if fail return
1456     done
1457
1458     #DOWNGRADE DATABASE step by step
1459     while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
1460     do
1461         echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
1462         #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
1463         #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
1464         #$FILE_ || exit -1  # if fail return
1465         downgrade_from_${DATABASE_VER_NUM}
1466         DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
1467     done
1468 }
1469
1470
1471 # check if current database is ok
1472 function check_migration_needed()
1473 {
1474     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2` ||
1475     ! echo "    ERROR cannot read from schema_version" || exit 1
1476
1477     if [[ -z "$DATABASE_VER_NUM" ]] || [[ "$DATABASE_VER_NUM" -lt 0 ]] || [[ "$DATABASE_VER_NUM" -gt 100 ]] ; then
1478         echo "    Error can not get database version ($DATABASE_VER_NUM?)" >&2
1479         exit 1
1480     fi
1481
1482     [[ $DB_VERSION -eq $DATABASE_VER_NUM ]] && echo "    current database version '$DATABASE_VER_NUM' is ok" && return 1
1483     [[ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ]] &&
1484         echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
1485         exit 1
1486     return 0
1487 }
1488
1489 DATABASE_PROCESS=`echo "select comments from schema_version where version_int=0;" | $DBCMD | tail -n+2` ||
1490     ! echo "    ERROR cannot read from schema_version" || exit 1
1491 if [[ -z "$DATABASE_PROCESS" ]] ; then  # migration a non empty database
1492     check_migration_needed || exit 0
1493     # Create a backup database content
1494     [[ -n "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q  "${BACKUP_DIR}/backupdb.XXXXXX.sql")"
1495     [[ -z "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql")"
1496     mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $BACKUP_FILE ||
1497         ! echo "Cannot create Backup file '$BACKUP_FILE'" >&2 || exit 1
1498     echo "    Backup file '$BACKUP_FILE' created"
1499     # Set schema version
1500     set_schema_version_process
1501     migrate
1502     del_schema_version_process
1503     rm -f "$BACKUP_FILE"
1504 elif echo $DATABASE_PROCESS | grep -q init ; then   # Empty database. No backup needed
1505     echo "    Migrating an empty database"
1506     if check_migration_needed ; then
1507         migrate
1508     fi
1509     del_schema_version_process
1510
1511 else  # Recover Migration process
1512     BACKUP_FILE=${DATABASE_PROCESS##*backup: }
1513     [[ -f "$BACKUP_FILE" ]] || ! echo "Previous migration process fail and cannot recover backup file '$BACKUP_FILE'" >&2 ||
1514         exit 1
1515     echo "    Previous migration was killed. Restoring database from rollback file'$BACKUP_FILE'"
1516     cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM || ! echo "    Cannot load backup file '$BACKUP_FILE'" >&2 || exit 1
1517     if check_migration_needed ; then
1518         set_schema_version_process
1519         migrate
1520     fi
1521     del_schema_version_process
1522     rm -f "$BACKUP_FILE"
1523 fi
1524 exit 0
1525
1526 #echo done
1527