database_utils/migrate_mano_db.sh

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