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