database_utils/migrate_mano_db.sh

   1 #!/bin/bash
   2
   3 ##
   4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
   5 # This file is part of openmano
   6 # All Rights Reserved.
   7 #
   8 # Licensed under the Apache License, Version 2.0 (the "License"); you may
   9 # not use this file except in compliance with the License. You may obtain
  10 # a copy of the License at
  11 #
  12 #         http://www.apache.org/licenses/LICENSE-2.0
  13 #
  14 # Unless required by applicable law or agreed to in writing, software
  15 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
  16 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  17 # License for the specific language governing permissions and limitations
  18 # under the License.
  19 #
  20 # For those usages not covered by the Apache License, Version 2.0 please
  21 # contact with: nfvlabs@tid.es
  22 ##
  23
  24 #
  25 #Upgrade/Downgrade openmano database preserving the content
  26 #
  27
  28 DBUSER="mano"
  29 DBPASS=""
  30 DEFAULT_DBPASS="manopw"
  31 DBHOST=""
  32 DBPORT="3306"
  33 DBNAME="mano_db"
  34 QUIET_MODE=""
  35 #TODO update it with the last database version
  36 LAST_DB_VERSION=24
  37
  38 # Detect paths
  39 MYSQL=$(which mysql)
  40 AWK=$(which awk)
  41 GREP=$(which grep)
  42
  43 function usage(){
  44     echo -e "Usage: $0 OPTIONS [version]"
  45     echo -e "  Upgrades/Downgrades openmano database preserving the content."\
  46             "If [version]  is not provided, it is upgraded to the last version"
  47     echo -e "  OPTIONS"
  48     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  49     echo -e "     -p PASS  database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
  50     echo -e "     -P PORT  database port. '$DBPORT' by default"
  51     echo -e "     -h HOST  database host. 'localhost' by default"
  52     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  53     echo -e "     -q --quiet: Do not prompt for credentials and exit if cannot access to database"
  54     echo -e "     --help   shows this help"
  55 }
  56
  57 while getopts ":u:p:P:h:d:q-:" o; do
  58     case "${o}" in
  59         u)
  60             DBUSER="$OPTARG"
  61             ;;
  62         p)
  63             DBPASS="$OPTARG"
  64             ;;
  65         P)
  66             DBPORT="$OPTARG"
  67             ;;
  68         d)
  69             DBNAME="$OPTARG"
  70             ;;
  71         h)
  72             DBHOST="$OPTARG"
  73             ;;
  74         q)
  75             export QUIET_MODE=yes
  76             ;;
  77         -)
  78             [ "${OPTARG}" == "help" ] && usage && exit 0
  79             [ "${OPTARG}" == "quiet" ] && export QUIET_MODE=yes && continue
  80             echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
  81             exit 1
  82             ;;
  83         \?)
  84             echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
  85             exit 1
  86             ;;
  87         :)
  88             echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
  89             exit 1
  90             ;;
  91         *)
  92             usage >&2
  93             exit 1
  94             ;;
  95     esac
  96 done
  97 shift $((OPTIND-1))
  98
  99 DB_VERSION=$1
 100
 101 if [ -n "$DB_VERSION" ] ; then
 102     # check it is a number and an allowed one
 103     [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev/null ||
 104         ! echo "parameter 'version' requires a integer value" >&2 || exit 1
 105     if [ "$DB_VERSION" -lt 0 ] || [ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
 106         echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
 107              "If you need an upper version, get a newer version of this script '$0'" >&2
 108         exit 1
 109     fi
 110 else
 111     DB_VERSION="$LAST_DB_VERSION"
 112 fi
 113
 114 # Creating temporary file
 115 TEMPFILE="$(mktemp -q --tmpdir "migratemanodb.XXXXXX")"
 116 trap 'rm -f "$TEMPFILE"' EXIT
 117 chmod 0600 "$TEMPFILE"
 118 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 119 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 120
 121 # Check and ask for database user password
 122 FIRST_TRY="yes"
 123 while ! DB_ERROR=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
 124 do
 125     # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
 126     [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS="$DEFAULT_DBPASS" &&
 127         echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
 128         continue
 129     echo "$DB_ERROR"
 130     [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
 131     echo -e "Provide database name and credentials (Ctrl+c to abort):"
 132     read -e -p "    mysql database name($DBNAME): " KK
 133     [ -n "$KK" ] && DBNAME="$KK"
 134     read -e -p "    mysql user($DBUSER): " KK
 135     [ -n "$KK" ] && DBUSER="$KK"
 136     read -e -s -p "    mysql password: " DBPASS
 137     echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 138     FIRST_TRY=""
 139     echo
 140 done
 141
 142 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
 143 #echo DBCMD $DBCMD
 144
 145 #GET DATABASE VERSION
 146 #check that the database seems a openmano database
 147 if ! echo -e "show create table vnfs;\nshow create table scenarios" | $DBCMD >/dev/null 2>&1
 148 then
 149     echo "    database $DBNAME does not seem to be an openmano database" >&2
 150     exit -1;
 151 fi
 152
 153 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 154 then
 155     DATABASE_VER="0.0"
 156     DATABASE_VER_NUM=0
 157 else
 158     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 159     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 160     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] &&
 161         echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 162     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 163 fi
 164
 165 [ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ] &&
 166     echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
 167     exit 1
 168
 169 #GET DATABASE TARGET VERSION
 170 #DB_VERSION=0
 171 #[ $OPENMANO_VER_NUM -ge 2002 ] && DB_VERSION=1   #0.2.2 =>  1
 172 #[ $OPENMANO_VER_NUM -ge 2005 ] && DB_VERSION=2   #0.2.5 =>  2
 173 #[ $OPENMANO_VER_NUM -ge 3003 ] && DB_VERSION=3   #0.3.3 =>  3
 174 #[ $OPENMANO_VER_NUM -ge 3005 ] && DB_VERSION=4   #0.3.5 =>  4
 175 #[ $OPENMANO_VER_NUM -ge 4001 ] && DB_VERSION=5   #0.4.1 =>  5
 176 #[ $OPENMANO_VER_NUM -ge 4002 ] && DB_VERSION=6   #0.4.2 =>  6
 177 #[ $OPENMANO_VER_NUM -ge 4003 ] && DB_VERSION=7   #0.4.3 =>  7
 178 #[ $OPENMANO_VER_NUM -ge 4032 ] && DB_VERSION=8   #0.4.32=>  8
 179 #[ $OPENMANO_VER_NUM -ge 4033 ] && DB_VERSION=9   #0.4.33=>  9
 180 #[ $OPENMANO_VER_NUM -ge 4036 ] && DB_VERSION=10  #0.4.36=>  10
 181 #[ $OPENMANO_VER_NUM -ge 4043 ] && DB_VERSION=11  #0.4.43=>  11
 182 #[ $OPENMANO_VER_NUM -ge 4046 ] && DB_VERSION=12  #0.4.46=>  12
 183 #[ $OPENMANO_VER_NUM -ge 4047 ] && DB_VERSION=13  #0.4.47=>  13
 184 #[ $OPENMANO_VER_NUM -ge 4057 ] && DB_VERSION=14  #0.4.57=>  14
 185 #[ $OPENMANO_VER_NUM -ge 4059 ] && DB_VERSION=15  #0.4.59=>  15
 186 #[ $OPENMANO_VER_NUM -ge 5002 ] && DB_VERSION=16  #0.5.2 =>  16
 187 #[ $OPENMANO_VER_NUM -ge 5003 ] && DB_VERSION=17  #0.5.3 =>  17
 188 #[ $OPENMANO_VER_NUM -ge 5004 ] && DB_VERSION=18  #0.5.4 =>  18
 189 #[ $OPENMANO_VER_NUM -ge 5005 ] && DB_VERSION=19  #0.5.5 =>  19
 190 #[ $OPENMANO_VER_NUM -ge 5009 ] && DB_VERSION=20  #0.5.9 =>  20
 191 #[ $OPENMANO_VER_NUM -ge 5015 ] && DB_VERSION=21  #0.5.15 =>  21
 192 #[ $OPENMANO_VER_NUM -ge 5016 ] && DB_VERSION=22  #0.5.16 =>  22
 193 #[ $OPENMANO_VER_NUM -ge 5020 ] && DB_VERSION=23  #0.5.20 =>  23
 194 #[ $OPENMANO_VER_NUM -ge 5021 ] && DB_VERSION=24  #0.5.21 =>  24
 195 #TODO ... put next versions here
 196
 197 function upgrade_to_1(){
 198     # echo "    upgrade database from version 0.0 to version 0.1"
 199     echo "      CREATE TABLE \`schema_version\`"
 200     echo "CREATE TABLE \`schema_version\` (
 201         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 202         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 203         \`openmano_ver\` VARCHAR(20) NOT NULL COMMENT 'openmano version',
 204         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 205         \`date\` DATE NULL,
 206         PRIMARY KEY (\`version_int\`)
 207         )
 208         COMMENT='database schema control version'
 209         COLLATE='utf8_general_ci'
 210         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 211     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openmano_ver\`, \`comments\`, \`date\`)
 212          VALUES (1, '0.1', '0.2.2', 'insert schema_version', '2015-05-08');" | $DBCMD
 213 }
 214 function downgrade_from_1(){
 215     # echo "    downgrade database from version 0.1 to version 0.0"
 216     echo "      DROP TABLE \`schema_version\`"
 217     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 218 }
 219 function upgrade_to_2(){
 220     # echo "    upgrade database from version 0.1 to version 0.2"
 221     echo "      Add columns user/passwd to table 'vim_tenants'"
 222     echo "ALTER TABLE vim_tenants ADD COLUMN user VARCHAR(36) NULL COMMENT 'Credentials for vim' AFTER created,
 223         ADD COLUMN passwd VARCHAR(50) NULL COMMENT 'Credentials for vim' AFTER user;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 224     echo "      Add table 'images' and 'datacenters_images'"
 225     echo "CREATE TABLE images (
 226         uuid VARCHAR(36) NOT NULL,
 227         name VARCHAR(50) NOT NULL,
 228         location VARCHAR(200) NOT NULL,
 229         description VARCHAR(100) NULL,
 230         metadata VARCHAR(400) NULL,
 231         PRIMARY KEY (uuid),
 232         UNIQUE INDEX location (location)  )
 233         COLLATE='utf8_general_ci'
 234         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 235     echo "CREATE TABLE datacenters_images (
 236         id INT NOT NULL AUTO_INCREMENT,
 237         image_id VARCHAR(36) NOT NULL,
 238         datacenter_id VARCHAR(36) NOT NULL,
 239         vim_id VARCHAR(36) NOT NULL,
 240         PRIMARY KEY (id),
 241         CONSTRAINT FK__images FOREIGN KEY (image_id) REFERENCES images (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 242         CONSTRAINT FK__datacenters_i FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 243         COLLATE='utf8_general_ci'
 244         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 245     echo "      migrate data from table 'vms' into 'images'"
 246     echo "INSERT INTO images (uuid, name, location) SELECT DISTINCT vim_image_id, vim_image_id, image_path FROM vms;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 247     echo "INSERT INTO datacenters_images (image_id, datacenter_id, vim_id)
 248           SELECT DISTINCT vim_image_id, datacenters.uuid, vim_image_id FROM vms JOIN datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 249     echo "      Add table 'flavors' and 'datacenter_flavors'"
 250     echo "CREATE TABLE flavors (
 251         uuid VARCHAR(36) NOT NULL,
 252         name VARCHAR(50) NOT NULL,
 253         description VARCHAR(100) NULL,
 254         disk SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 255         ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 256         vcpus SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 257         extended VARCHAR(2000) NULL DEFAULT NULL COMMENT 'Extra description json format of needed resources and pining, orginized in sets per numa',
 258         PRIMARY KEY (uuid)  )
 259         COLLATE='utf8_general_ci'
 260         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 261     echo "CREATE TABLE datacenters_flavors (
 262         id INT NOT NULL AUTO_INCREMENT,
 263         flavor_id VARCHAR(36) NOT NULL,
 264         datacenter_id VARCHAR(36) NOT NULL,
 265         vim_id VARCHAR(36) NOT NULL,
 266         PRIMARY KEY (id),
 267         CONSTRAINT FK__flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 268         CONSTRAINT FK__datacenters_f FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 269         COLLATE='utf8_general_ci'
 270         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 271     echo "      migrate data from table 'vms' into 'flavors'"
 272     echo "INSERT INTO flavors (uuid, name) SELECT DISTINCT vim_flavor_id, vim_flavor_id FROM vms;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 273     echo "INSERT INTO datacenters_flavors (flavor_id, datacenter_id, vim_id)
 274           SELECT DISTINCT vim_flavor_id, datacenters.uuid, vim_flavor_id FROM vms JOIN datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 275     echo "ALTER TABLE vms ALTER vim_flavor_id DROP DEFAULT, ALTER vim_image_id DROP DEFAULT;
 276           ALTER TABLE vms CHANGE COLUMN vim_flavor_id flavor_id VARCHAR(36) NOT NULL COMMENT 'Link to flavor table' AFTER vnf_id,
 277           CHANGE COLUMN vim_image_id image_id VARCHAR(36) NOT NULL COMMENT 'Link to image table' AFTER flavor_id,
 278           ADD CONSTRAINT FK_vms_images  FOREIGN KEY (image_id) REFERENCES  images (uuid),
 279           ADD CONSTRAINT FK_vms_flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid);
 280          " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 281     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 282
 283 }
 284
 285 function downgrade_from_2(){
 286     # echo "    downgrade database from version 0.2 to version 0.1"
 287     echo "       migrate back data from 'datacenters_images' 'datacenters_flavors' into 'vms'"
 288     echo "ALTER TABLE vms ALTER image_id DROP DEFAULT, ALTER flavor_id DROP DEFAULT;
 289           ALTER TABLE vms CHANGE COLUMN flavor_id vim_flavor_id VARCHAR(36) NOT NULL COMMENT 'Flavor ID in the VIM DB' AFTER vnf_id,
 290           CHANGE COLUMN image_id vim_image_id VARCHAR(36) NOT NULL COMMENT 'Image ID in the VIM DB' AFTER vim_flavor_id,
 291           DROP FOREIGN KEY FK_vms_flavors, DROP INDEX FK_vms_flavors,
 292           DROP FOREIGN KEY FK_vms_images, DROP INDEX FK_vms_images;
 293          " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 294 #    echo "UPDATE v SET v.vim_image_id=di.vim_id
 295 #          FROM  vms as v INNER JOIN images as i ON v.vim_image_id=i.uuid
 296 #          INNER JOIN datacenters_images as di ON i.uuid=di.image_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 297     echo "      Delete columns 'user/passwd' from 'vim_tenants'"
 298     echo "ALTER TABLE vim_tenants DROP COLUMN user, DROP COLUMN passwd; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 299     echo "        delete tables 'datacenter_images', 'images'"
 300     echo "DROP TABLE \`datacenters_images\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 301     echo "DROP TABLE \`images\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 302     echo "        delete tables 'datacenter_flavors', 'flavors'"
 303     echo "DROP TABLE \`datacenters_flavors\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 304     echo "DROP TABLE \`flavors\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 305     echo "DELETE FROM schema_version WHERE version_int='2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 306 }
 307
 308 function upgrade_to_3(){
 309     # echo "    upgrade database from version 0.2 to version 0.3"
 310     echo "      Change table 'logs', 'uuids"
 311     echo "ALTER TABLE logs CHANGE COLUMN related related VARCHAR(36) NOT NULL COMMENT 'Relevant element for the log' AFTER nfvo_tenant_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 312     echo "ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(36) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' AFTER created_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 313     echo "      Add column created to table 'datacenters_images' and 'datacenters_flavors'"
 314     for table in datacenters_images datacenters_flavors
 315     do
 316         echo "ALTER TABLE $table ADD COLUMN created ENUM('true','false') NOT NULL DEFAULT 'false'
 317             COMMENT 'Indicates if it has been created by openmano, or already existed' AFTER vim_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 318     done
 319     echo "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(2000) NULL DEFAULT NULL AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 320     echo "      Allow null to column 'vim_interface_id' in 'instance_interfaces'"
 321     echo "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; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 322     echo "      Add column config to table 'datacenters'"
 323     echo "ALTER TABLE datacenters ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL COMMENT 'extra config information in json' AFTER vim_url_admin;
 324         " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 325     echo "      Add column datacenter_id to table 'vim_tenants'"
 326     echo "ALTER TABLE vim_tenants ADD COLUMN datacenter_id VARCHAR(36) NULL COMMENT 'Datacenter of this tenant' AFTER uuid,
 327         DROP INDEX name, DROP INDEX vim_tenant_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 328     echo "ALTER TABLE vim_tenants CHANGE COLUMN name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL COMMENT 'tenant name at VIM' AFTER datacenter_id,
 329         CHANGE COLUMN vim_tenant_id vim_tenant_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'Tenant ID at VIM' AFTER vim_tenant_name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 330     echo "UPDATE vim_tenants as vt LEFT JOIN tenants_datacenters as td ON vt.uuid=td.vim_tenant_id
 331         SET vt.datacenter_id=td.datacenter_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 332     echo "DELETE FROM vim_tenants WHERE datacenter_id is NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 333     echo "ALTER TABLE vim_tenants ALTER datacenter_id DROP DEFAULT;
 334         ALTER TABLE vim_tenants
 335         CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL COMMENT 'Datacenter of this tenant' AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 336     echo "ALTER TABLE vim_tenants ADD CONSTRAINT FK_vim_tenants_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid)
 337         ON UPDATE CASCADE ON DELETE CASCADE;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 338
 339     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 340 }
 341
 342
 343 function downgrade_from_3(){
 344     # echo "    downgrade database from version 0.3 to version 0.2"
 345     echo "      Change back table 'logs', 'uuids'"
 346     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 347     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 348     echo "      Delete column created from table 'datacenters_images' and 'datacenters_flavors'"
 349     for table in datacenters_images datacenters_flavors
 350     do
 351         echo "ALTER TABLE $table DROP COLUMN created;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 352     done
 353     echo "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(400) NULL DEFAULT NULL AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 354     echo "      Deny back null to column 'vim_interface_id' in 'instance_interfaces'"
 355     echo "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; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 356     echo "       Delete column config to table 'datacenters'"
 357     echo "ALTER TABLE datacenters DROP COLUMN config;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 358     echo "       Delete column datacenter_id to table 'vim_tenants'"
 359     echo "ALTER TABLE vim_tenants DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_vim_tenants_datacenters;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 360     echo "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name name VARCHAR(36) NULL DEFAULT NULL COMMENT '' AFTER uuid"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 361     echo "ALTER TABLE vim_tenants ALTER name DROP DEFAULT;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 362     echo "ALTER TABLE vim_tenants CHANGE COLUMN name name VARCHAR(36) NOT NULL AFTER uuid"| $DBCMD || ! echo "Warning changing column name at vim_tenants!"
 363     echo "ALTER TABLE vim_tenants ADD UNIQUE INDEX name (name);" | $DBCMD || ! echo "Warning add unique index name at vim_tenants!"
 364     echo "ALTER TABLE vim_tenants ALTER vim_tenant_id DROP DEFAULT;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 365     echo "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;"| $DBCMD || ! echo "Warning changing column vim_tenant_id at vim_tenants!"
 366     echo "ALTER TABLE vim_tenants ADD UNIQUE INDEX vim_tenant_id (vim_tenant_id);" | $DBCMD || ! echo "Warning add unique index vim_tenant_id at vim_tenants!"
 367     echo "DELETE FROM schema_version WHERE version_int='3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 368 }
 369
 370 function upgrade_to_4(){
 371     # echo "    upgrade database from version 0.3 to version 0.4"
 372     echo "      Enlarge graph field at tables 'sce_vnfs', 'sce_nets'"
 373     for table in sce_vnfs sce_nets
 374     do
 375         echo "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 376     done
 377     echo "ALTER TABLE datacenters CHANGE COLUMN type type VARCHAR(36) NOT NULL DEFAULT 'openvim' AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 378     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 379 }
 380
 381 function downgrade_from_4(){
 382     # echo "    downgrade database from version 0.4 to version 0.3"
 383     echo "      Shorten back graph field at tables 'sce_vnfs', 'sce_nets'"
 384     for table in sce_vnfs sce_nets
 385     do
 386         echo "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 387     done
 388     echo "ALTER TABLE datacenters CHANGE COLUMN type type ENUM('openvim','openstack') NOT NULL DEFAULT 'openvim' AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 389     echo "DELETE FROM schema_version WHERE version_int='4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 390 }
 391
 392 function upgrade_to_5(){
 393     # echo "    upgrade database from version 0.4 to version 0.5"
 394     echo "      Add 'mac' field for bridge interfaces in table 'interfaces'"
 395     echo "ALTER TABLE interfaces ADD COLUMN mac CHAR(18) NULL DEFAULT NULL AFTER model;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 396     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 397 }
 398 function downgrade_from_5(){
 399     # echo "    downgrade database from version 0.5 to version 0.4"
 400     echo "      Remove 'mac' field for bridge interfaces in table 'interfaces'"
 401     echo "ALTER TABLE interfaces DROP COLUMN mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 402     echo "DELETE FROM schema_version WHERE version_int='5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 403 }
 404
 405 function upgrade_to_6(){
 406     # echo "    upgrade database from version 0.5 to version 0.6"
 407     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 408     echo "ALTER TABLE vnfs ADD COLUMN descriptor TEXT NULL DEFAULT NULL COMMENT 'Original text descriptor used for create the VNF' AFTER class;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 409     echo "ALTER TABLE scenarios ADD COLUMN descriptor TEXT NULL DEFAULT NULL COMMENT 'Original text descriptor used for create the scenario' AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 410     echo "      Add 'last_error', 'vim_info' to 'instance_vms', 'instance_nets'"
 411     echo "ALTER TABLE instance_vms  ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 412     echo "ALTER TABLE instance_vms  ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 413     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 414     echo "ALTER TABLE instance_nets ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 415     echo "ALTER TABLE instance_nets ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 416     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 417     echo "      Add 'mac_address', 'ip_address', 'vim_info' to 'instance_interfaces'"
 418     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 419     echo "      Add 'sce_vnf_id','datacenter_id','vim_tenant_id' field to 'instance_vnfs'"
 420     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 421     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 422     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 423     echo "      Add 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field to 'instance_nets'"
 424     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 425     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 426     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 427     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 428     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 429 }
 430 function downgrade_from_6(){
 431     # echo "    downgrade database from version 0.6 to version 0.5"
 432     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 433     echo "ALTER TABLE vnfs      DROP COLUMN descriptor;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 434     echo "ALTER TABLE scenarios DROP COLUMN descriptor;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 435     echo "      Remove 'last_error', 'vim_info' from 'instance_vms', 'instance_nets'"
 436     echo "ALTER TABLE instance_vms  DROP COLUMN error_msg, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 437     echo "ALTER TABLE instance_vms  CHANGE COLUMN status status ENUM('ACTIVE','PAUSED','INACTIVE','CREATING','ERROR','DELETING') NOT NULL DEFAULT 'CREATING' AFTER vim_vm_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 438     echo "ALTER TABLE instance_nets DROP COLUMN error_msg, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 439     echo "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'BUILD' AFTER instance_scenario_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 440     echo "      Remove 'mac_address', 'ip_address', 'vim_info' from 'instance_interfaces'"
 441     echo "ALTER TABLE instance_interfaces DROP COLUMN mac_address, DROP COLUMN ip_address, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 442     echo "      Remove 'sce_vnf_id','datacenter_id','vim_tenant_id' field from 'instance_vnfs'"
 443     echo "ALTER TABLE instance_vnfs DROP COLUMN sce_vnf_id, DROP FOREIGN KEY FK_instance_vnfs_sce_vnfs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 444     echo "ALTER TABLE instance_vnfs DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_vnfs_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 445     echo "ALTER TABLE instance_vnfs DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_vnfs_datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 446     echo "      Remove 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field from 'instance_nets'"
 447     echo "ALTER TABLE instance_nets DROP COLUMN sce_net_id, DROP FOREIGN KEY FK_instance_nets_sce_nets;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 448     echo "ALTER TABLE instance_nets DROP COLUMN net_id, DROP FOREIGN KEY FK_instance_nets_nets;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 449     echo "ALTER TABLE instance_nets DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_nets_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 450     echo "ALTER TABLE instance_nets DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_nets_datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 451     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 452 }
 453
 454 function upgrade_to_7(){
 455     # echo "    upgrade database from version 0.6 to version 0.7"
 456     echo "      Change created_at, modified_at from timestamp to unix float at all database"
 457     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
 458     do
 459          echo -en "        $table               \r"
 460          echo "ALTER TABLE $table ADD COLUMN created_at_ DOUBLE NOT NULL after created_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 461          echo "UPDATE $table SET created_at_=unix_timestamp(created_at);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 462          echo "ALTER TABLE $table DROP COLUMN created_at, CHANGE COLUMN created_at_ created_at DOUBLE NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 463          [[ $table == uuids ]] || echo "ALTER TABLE $table CHANGE COLUMN modified_at modified_at DOUBLE NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 464     done
 465
 466     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 467     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 468 }
 469 function downgrade_from_7(){
 470     # echo "    downgrade database from version 0.7 to version 0.6"
 471     echo "      Change back created_at, modified_at from unix float to timestamp at all database"
 472     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
 473     do
 474          echo -en "        $table               \r"
 475          echo "ALTER TABLE $table ADD COLUMN created_at_ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP after created_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 476          echo "UPDATE $table SET created_at_=from_unixtime(created_at);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 477          echo "ALTER TABLE $table DROP COLUMN created_at, CHANGE COLUMN created_at_ created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 478          [[ $table == uuids ]] || echo "ALTER TABLE $table CHANGE COLUMN modified_at modified_at TIMESTAMP NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 479     done
 480     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 481     echo "DELETE FROM schema_version WHERE version_int='7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 482 }
 483
 484 function upgrade_to_8(){
 485     # echo "    upgrade database from version 0.7 to version 0.8"
 486     echo "      Change enalarge name, description to 255 at all database"
 487     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 488     do
 489          echo -en "        $table               \r"
 490          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR(255) NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 491          echo "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 492     done
 493     echo -en "        interfaces           \r"
 494     echo "ALTER TABLE interfaces CHANGE COLUMN internal_name internal_name VARCHAR(255) NOT NULL, CHANGE COLUMN external_name external_name VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 495     echo "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 496     echo -en "        vim_tenants          \r"
 497     echo "ALTER TABLE vim_tenants CHANGE COLUMN user user VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN passwd passwd VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 498     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 499 }
 500 function downgrade_from_8(){
 501     # echo "    downgrade database from version 0.8 to version 0.7"
 502     echo "      Change back name,description to shorter length at all database"
 503     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 504     do
 505          name_length=50
 506          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 507          echo -en "        $table               \r"
 508          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 509          echo "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 510     done
 511     echo -en "        interfaces           \r"
 512     echo "ALTER TABLE interfaces CHANGE COLUMN internal_name internal_name VARCHAR(25) NOT NULL, CHANGE COLUMN external_name external_name VARCHAR(25) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 513     echo -en "        vim_tenants          \r"
 514     echo "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 515     echo "ALTER TABLE vim_tenants CHANGE COLUMN user user VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN passwd passwd VARCHAR(50) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 516     echo "DELETE FROM schema_version WHERE version_int='8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 517 }
 518 function upgrade_to_9(){
 519     # echo "    upgrade database from version 0.8 to version 0.9"
 520     echo "      Add more status to 'instance_vms'"
 521     echo "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 522     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 523 }
 524 function downgrade_from_9(){
 525     # echo "    downgrade database from version 0.9 to version 0.8"
 526     echo "      Add more status to 'instance_vms'"
 527     echo "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 528     echo "DELETE FROM schema_version WHERE version_int='9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 529 }
 530 function upgrade_to_10(){
 531     # echo "    upgrade database from version 0.9 to version 0.10"
 532     echo "      add tenant to 'vnfs'"
 533     echo "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;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 534     echo "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 535     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 536     echo "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 537     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 538     echo "      rename 'vim_tenants' table to 'datacenter_tenants'"
 539     echo "RENAME TABLE vim_tenants TO datacenter_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 540     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 541     do
 542         NULL="NOT NULL"
 543         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 544         echo "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 545         echo "ALTER TABLE ${table} ALTER vim_tenant_id DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 546         echo "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); " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 547     done
 548     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 549 }
 550
 551 function downgrade_from_10(){
 552     # echo "    downgrade database from version 0.10 to version 0.9"
 553     echo "      remove tenant from 'vnfs'"
 554     echo "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;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 555     echo "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 556     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 557     echo "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 558     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 559     echo "      rename back 'datacenter_tenants' table to 'vim_tenants'"
 560     echo "RENAME TABLE datacenter_tenants TO vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 561     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 562     do
 563         echo "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_datacenter_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 564         NULL="NOT NULL"
 565         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 566         echo "ALTER TABLE ${table} ALTER datacenter_tenant_id DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 567         echo "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); " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 568     done
 569     echo "DELETE FROM schema_version WHERE version_int='10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 570 }
 571
 572 function upgrade_to_11(){
 573     # echo "    upgrade database from version 0.10 to version 0.11"
 574     echo "      remove unique name at 'scenarios', 'instance_scenarios'"
 575     echo "ALTER TABLE scenarios DROP INDEX name;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 576     echo "ALTER TABLE instance_scenarios DROP INDEX name;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 577     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 578 }
 579 function downgrade_from_11(){
 580     # echo "    downgrade database from version 0.11 to version 0.10"
 581     echo "      add unique name at 'scenarios', 'instance_scenarios'"
 582     echo "ALTER TABLE scenarios ADD UNIQUE INDEX name (name);"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 583     echo "ALTER TABLE instance_scenarios ADD UNIQUE INDEX name (name);"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 584     echo "DELETE FROM schema_version WHERE version_int='11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 585 }
 586
 587 function upgrade_to_12(){
 588     # echo "    upgrade database from version 0.11 to version 0.12"
 589     echo "      create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to 'interfaces' and 'sce_interfaces'"
 590     echo "CREATE TABLE IF NOT EXISTS ip_profiles (
 591         id INT(11) NOT NULL AUTO_INCREMENT,
 592         net_id VARCHAR(36) NULL DEFAULT NULL,
 593         sce_net_id VARCHAR(36) NULL DEFAULT NULL,
 594         instance_net_id VARCHAR(36) NULL DEFAULT NULL,
 595         ip_version ENUM('IPv4','IPv6') NOT NULL DEFAULT 'IPv4',
 596         subnet_address VARCHAR(64) NULL DEFAULT NULL,
 597         gateway_address VARCHAR(64) NULL DEFAULT NULL,
 598         dns_address VARCHAR(64) NULL DEFAULT NULL,
 599         dhcp_enabled ENUM('true','false') NOT NULL DEFAULT 'true',
 600         dhcp_start_address VARCHAR(64) NULL DEFAULT NULL,
 601         dhcp_count INT(11) NULL DEFAULT NULL,
 602         PRIMARY KEY (id),
 603         CONSTRAINT FK_ipprofiles_nets FOREIGN KEY (net_id) REFERENCES nets (uuid) ON DELETE CASCADE,
 604         CONSTRAINT FK_ipprofiles_scenets FOREIGN KEY (sce_net_id) REFERENCES sce_nets (uuid) ON DELETE CASCADE,
 605         CONSTRAINT FK_ipprofiles_instancenets FOREIGN KEY (instance_net_id) REFERENCES instance_nets (uuid) ON DELETE CASCADE  )
 606         COMMENT='Table containing the IP parameters of a network, either a net, a sce_net or and instance_net.'
 607         COLLATE='utf8_general_ci'
 608         ENGINE=InnoDB;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 609     echo "ALTER TABLE interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 610     echo "ALTER TABLE sce_interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER interface_id;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 611     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 612 }
 613 function downgrade_from_12(){
 614     # echo "    downgrade database from version 0.12 to version 0.11"
 615     echo "      delete ip_profiles table, and remove ip_address column in 'interfaces' and 'sce_interfaces'"
 616     echo "DROP TABLE ip_profiles;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 617     echo "ALTER TABLE interfaces DROP COLUMN ip_address;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 618     echo "ALTER TABLE sce_interfaces DROP COLUMN ip_address;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 619     echo "DELETE FROM schema_version WHERE version_int='12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 620 }
 621
 622 function upgrade_to_13(){
 623     # echo "    upgrade database from version 0.12 to version 0.13"
 624     echo "      add cloud_config at 'scenarios', 'instance_scenarios'"
 625     echo "ALTER TABLE scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER descriptor;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 626     echo "ALTER TABLE instance_scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER modified_at;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 627     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 628 }
 629 function downgrade_from_13(){
 630     # echo "    downgrade database from version 0.13 to version 0.12"
 631     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 632     echo "ALTER TABLE scenarios DROP COLUMN cloud_config;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 633     echo "ALTER TABLE instance_scenarios DROP COLUMN cloud_config;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 634     echo "DELETE FROM schema_version WHERE version_int='13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 635 }
 636
 637 function upgrade_to_14(){
 638     # echo "    upgrade database from version 0.13 to version 0.14"
 639     echo "      remove unique index vim_net_id, instance_scenario_id at table 'instance_nets'"
 640     echo "ALTER TABLE instance_nets DROP INDEX vim_net_id_instance_scenario_id;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 641     echo "ALTER TABLE instance_nets CHANGE COLUMN external created ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'Created or already exists at VIM' AFTER multipoint;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 642     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 643 }
 644 function downgrade_from_14(){
 645     # echo "    downgrade database from version 0.14 to version 0.13"
 646     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 647     echo "ALTER TABLE instance_nets ADD UNIQUE INDEX vim_net_id_instance_scenario_id (vim_net_id, instance_scenario_id);"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 648     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 649     echo "DELETE FROM schema_version WHERE version_int='14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 650 }
 651
 652 function upgrade_to_15(){
 653     # echo "    upgrade database from version 0.14 to version 0.15"
 654     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"
 655     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL DEFAULT NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 656     echo "ALTER TABLE images ALTER location DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 657     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 658     echo "ALTER TABLE vms ALTER image_path DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 659     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 660     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 661 }
 662 function downgrade_from_15(){
 663     # echo "    downgrade database from version 0.15 to version 0.14"
 664     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"
 665     echo "ALTER TABLE images DROP INDEX universal_name_checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 666     echo "ALTER TABLE images ALTER location DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 667     echo "ALTER TABLE images CHANGE COLUMN location location VARCHAR(200) NOT NULL AFTER checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 668     echo "ALTER TABLE images DROP COLUMN universal_name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 669     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 670     echo "ALTER TABLE vms ALTER image_path DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 671     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 672     echo "DELETE FROM schema_version WHERE version_int='15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 673 }
 674
 675 function upgrade_to_16(){
 676     # echo "    upgrade database from version 0.15 to version 0.16"
 677     echo "      add column 'config' at table 'datacenter_tenants', enlarge 'vim_tenant_name/id'"
 678     echo "ALTER TABLE datacenter_tenants ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL AFTER passwd;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 679     echo "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(256) NULL DEFAULT NULL AFTER datacenter_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 680     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 681     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 682 }
 683 function downgrade_from_16(){
 684     # echo "    downgrade database from version 0.16 to version 0.15"
 685     echo "      remove column 'config' at table 'datacenter_tenants', restoring lenght 'vim_tenant_name/id'"
 686     echo "ALTER TABLE datacenter_tenants DROP COLUMN config;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 687     echo "ALTER TABLE datacenter_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL AFTER datacenter_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 688     echo "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;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 689     echo "DELETE FROM schema_version WHERE version_int='16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 690 }
 691
 692 function upgrade_to_17(){
 693     # echo "    upgrade database from version 0.16 to version 0.17"
 694     echo "      add column 'extended' at table 'datacenter_flavors'"
 695     echo "ALTER TABLE datacenters_flavors ADD extended varchar(2000) NULL COMMENT 'Extra description json format of additional devices';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 696     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 697 }
 698 function downgrade_from_17(){
 699     # echo "    downgrade database from version 0.17 to version 0.16"
 700     echo "      remove column 'extended' from table 'datacenter_flavors'"
 701     echo "ALTER TABLE datacenters_flavors DROP COLUMN extended;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 702     echo "DELETE FROM schema_version WHERE version_int='17';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 703 }
 704
 705 function upgrade_to_18(){
 706     # echo "    upgrade database from version 0.17 to version 0.18"
 707     echo "      add columns 'floating_ip' and 'port_security' at tables 'interfaces' and 'instance_interfaces'"
 708     echo "ALTER TABLE interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 709     echo "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';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 710     echo "ALTER TABLE instance_interfaces ADD floating_ip BOOL DEFAULT 0 NOT NULL COMMENT 'Indicates if a floating_ip must be associated to this interface';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 711     echo "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';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 712     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 713 }
 714 function downgrade_from_18(){
 715     # echo "    downgrade database from version 0.18 to version 0.17"
 716     echo "      remove columns 'floating_ip' and 'port_security' from tables 'interfaces' and 'instance_interfaces'"
 717     echo "ALTER TABLE interfaces DROP COLUMN floating_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 718     echo "ALTER TABLE interfaces DROP COLUMN port_security;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 719     echo "ALTER TABLE instance_interfaces DROP COLUMN floating_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 720     echo "ALTER TABLE instance_interfaces DROP COLUMN port_security;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 721     echo "DELETE FROM schema_version WHERE version_int='18';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 722 }
 723
 724 function upgrade_to_19(){
 725     # echo "    upgrade database from version 0.18 to version 0.19"
 726     echo "      add column 'boot_data' at table 'vms'"
 727     echo "ALTER TABLE vms ADD COLUMN boot_data TEXT NULL DEFAULT NULL AFTER image_path;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 728     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 729 }
 730 function downgrade_from_19(){
 731     # echo "    downgrade database from version 0.19 to version 0.18"
 732     echo "      remove column 'boot_data' from table 'vms'"
 733     echo "ALTER TABLE vms DROP COLUMN boot_data;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 734     echo "DELETE FROM schema_version WHERE version_int='19';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 735 }
 736
 737 function upgrade_to_20(){
 738     # echo "    upgrade database from version 0.19 to version 0.20"
 739     echo "      add column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
 740     echo "ALTER TABLE instance_nets ADD sdn_net_id varchar(36) DEFAULT NULL NULL COMMENT 'Network id in ovim';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 741     echo "ALTER TABLE instance_interfaces ADD sdn_port_id varchar(36) DEFAULT NULL NULL COMMENT 'Port id in ovim';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 742     echo "ALTER TABLE instance_interfaces ADD compute_node varchar(100) DEFAULT NULL NULL COMMENT 'Compute node id used to specify the SDN port mapping';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 743     echo "ALTER TABLE instance_interfaces ADD pci varchar(12) DEFAULT NULL NULL COMMENT 'PCI of the physical port in the host';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 744     echo "ALTER TABLE instance_interfaces ADD vlan SMALLINT UNSIGNED DEFAULT NULL NULL COMMENT 'VLAN tag used by the port';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 745     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 746 }
 747 function downgrade_from_20(){
 748     # echo "    downgrade database from version 0.20 to version 0.19"
 749     echo "      remove column 'sdn_net_id' at table 'instance_nets' and columns 'sdn_port_id', 'compute_node', 'pci' and 'vlan' to table 'instance_interfaces'"
 750     echo "ALTER TABLE instance_nets DROP COLUMN sdn_net_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 751     echo "ALTER TABLE instance_interfaces DROP COLUMN vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 752     echo "ALTER TABLE instance_interfaces DROP COLUMN pci;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 753     echo "ALTER TABLE instance_interfaces DROP COLUMN compute_node;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 754     echo "ALTER TABLE instance_interfaces DROP COLUMN sdn_port_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 755     echo "DELETE FROM schema_version WHERE version_int='20';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 756 }
 757
 758 function upgrade_to_21(){
 759     # echo "    upgrade database from version 0.20 to version 0.21"
 760     echo "      edit 'instance_nets' to allow instance_scenario_id=None"
 761     echo "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 762     echo "      enlarge column 'dns_address' at table 'ip_profiles'"
 763     echo "ALTER TABLE ip_profiles MODIFY dns_address varchar(255) DEFAULT NULL NULL "\
 764          "comment 'dns ip list separated by semicolon';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 765     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 766 }
 767 function downgrade_from_21(){
 768     # echo "    downgrade database from version 0.21 to version 0.20"
 769     echo "      edit 'instance_nets' to disallow instance_scenario_id=None"
 770     #Delete all lines with a instance_scenario_id=NULL in order to disable this option
 771     echo "DELETE FROM instance_nets WHERE instance_scenario_id IS NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 772     echo "ALTER TABLE instance_nets MODIFY COLUMN instance_scenario_id varchar(36) NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 773     echo "      shorten column 'dns_address' at table 'ip_profiles'"
 774     echo "ALTER TABLE ip_profiles MODIFY dns_address varchar(64) DEFAULT NULL NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 775     echo "DELETE FROM schema_version WHERE version_int='21';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 776 }
 777
 778 function upgrade_to_22(){
 779     # echo "    upgrade database from version 0.21 to version 0.22"
 780     echo "      Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
 781     echo "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 782     echo "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');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 783 }
 784 function downgrade_from_22(){
 785     # echo "    downgrade database from version 0.22 to version 0.21"
 786     echo "      Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
 787     echo "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 788     echo "DELETE FROM schema_version WHERE version_int='22';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 789 }
 790
 791 function upgrade_to_23(){
 792     # echo "    upgrade database from version 0.22 to version 0.23"
 793     echo "      add column 'availability_zone' at table 'vms'"
 794     echo "ALTER TABLE mano_db.vms ADD COLUMN availability_zone VARCHAR(255) NULL AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 795     echo "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (23, '0.23', '0.5.20', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-08-29');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 796 }
 797 function downgrade_from_23(){
 798     # echo "    downgrade database from version 0.23 to version 0.22"
 799     echo "      remove column 'availability_zone' from table 'vms'"
 800     echo "ALTER TABLE mano_db.vms DROP COLUMN availability_zone;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 801     echo "DELETE FROM schema_version WHERE version_int='23';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 802 }
 803
 804 function upgrade_to_24(){
 805     # echo "    upgrade database from version 0.23 to version 0.24"
 806     echo "      Add 'count' to table 'vms'"
 807     echo "ALTER TABLE vms ADD COLUMN count SMALLINT NOT NULL DEFAULT '1' AFTER vnf_id;" | $DBCMD ||
 808         ! echo "ERROR. Aborted!" || exit -1
 809     echo "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\
 810          "VALUES (24, '0.24', '0.5.21', 'Added vnfd fields', '2017-08-29');" | $DBCMD ||
 811          ! echo "ERROR. Aborted!" || exit -1
 812 }
 813 function downgrade_from_24(){
 814     # echo "    downgrade database from version 0.24 to version 0.23"
 815     echo "      Remove 'count' from table 'vms'"
 816     echo "ALTER TABLE vms DROP COLUMN count;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 817     echo "DELETE FROM schema_version WHERE version_int='24';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 818 }
 819
 820 function upgrade_to_X(){
 821     echo "      change 'datacenter_nets'"
 822     echo "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);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 823 }
 824 function downgrade_from_X(){
 825     echo "      Change back 'datacenter_nets'"
 826     echo "ALTER TABLE datacenter_nets DROP COLUMN vim_tenant_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 827 }
 828 #TODO ... put funtions here
 829
 830 # echo "db version = "${DATABASE_VER_NUM}
 831 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo "    current database version '$DATABASE_VER_NUM' is ok"
 832 #UPGRADE DATABASE step by step
 833 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 834 do
 835     echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 836     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 837     upgrade_to_${DATABASE_VER_NUM}
 838     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 839     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 840     #$FILE_ || exit -1  # if fail return
 841 done
 842
 843 #DOWNGRADE DATABASE step by step
 844 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 845 do
 846     echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 847     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 848     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 849     #$FILE_ || exit -1  # if fail return
 850     downgrade_from_${DATABASE_VER_NUM}
 851     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 852 done
 853
 854 #echo done
 855