database_utils/migrate_mano_db.sh

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