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