database_utils/migrate_mano_db.sh

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