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