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 DBHOST="localhost"
  31 DBPORT="3306"
  32 DBNAME="mano_db"
  33
  34 # Detect paths
  35 MYSQL=$(which mysql)
  36 AWK=$(which awk)
  37 GREP=$(which grep)
  38 DIRNAME=`dirname $0`
  39
  40 function usage(){
  41     echo -e "Usage: $0 OPTIONS  [{openmano_version}]"
  42     echo -e "  Upgrades/Downgrades openmano database preserving the content"
  43     echo -e "   if openmano_version is not provided it tries to get from openmanod.py using relative path"
  44     echo -e "  OPTIONS"
  45     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  46     echo -e "     -p PASS  database password. 'No password' by default. Prompts if DB access fails"
  47     echo -e "     -P PORT  database port. '$DBPORT' by default"
  48     echo -e "     -h HOST  database host. '$DBHOST' by default"
  49     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  50     echo -e "     --help   shows this help"
  51 }
  52
  53 while getopts ":u:p:P:h:d:-:" o; do
  54     case "${o}" in
  55         u)
  56             DBUSER="$OPTARG"
  57             ;;
  58         p)
  59             DBPASS="$OPTARG"
  60             ;;
  61         P)
  62             DBPORT="$OPTARG"
  63             ;;
  64         d)
  65             DBNAME="$OPTARG"
  66             ;;
  67         h)
  68             DBHOST="$OPTARG"
  69             ;;
  70         -)
  71             [ "${OPTARG}" == "help" ] && usage && exit 0
  72             echo "Invalid option: --$OPTARG" >&2 && usage  >&2
  73             exit 1
  74             ;;
  75         \?)
  76             echo "Invalid option: -$OPTARG" >&2 && usage  >&2
  77             exit 1
  78             ;;
  79         :)
  80             echo "Option -$OPTARG requires an argument." >&2 && usage  >&2
  81             exit 1
  82             ;;
  83         *)
  84             usage >&2
  85             exit -1
  86             ;;
  87     esac
  88 done
  89 shift $((OPTIND-1))
  90
  91
  92 #GET OPENMANO VERSION
  93 OPENMANO_VER="$1"
  94 if [ -z "$OPENMANO_VER" ]
  95 then
  96     OPENMANO_VER=`${DIRNAME}/../openmanod.py -v`
  97     OPENMANO_VER=${OPENMANO_VER%%-r*}
  98     OPENMANO_VER=${OPENMANO_VER##*version }
  99     echo "    Detected openmano version $OPENMANO_VER"
 100 fi
 101 VERSION_1=`echo $OPENMANO_VER | cut -f 1 -d"."`
 102 VERSION_2=`echo $OPENMANO_VER | cut -f 2 -d"."`
 103 VERSION_3=`echo $OPENMANO_VER | cut -f 3 -d"."`
 104 if ! [ "$VERSION_1" -ge 0 -a "$VERSION_2" -ge 0 -a "$VERSION_3" -ge 0 ] 2>/dev/null
 105 then
 106     [ -n "$1" ] && echo "Invalid openmano version '$1', expected 'X.X.X'" >&2
 107     [ -z "$1" ] && echo "Can not get openmano version" >&2
 108     exit -1
 109 fi
 110 OPENMANO_VER_NUM=`printf "%d%03d%03d" ${VERSION_1} ${VERSION_2} ${VERSION_3}`
 111
 112 #Creating temporary file
 113 TEMPFILE="$(mktemp -q --tmpdir "migratemanodb.XXXXXX")"
 114 trap 'rm -f "$TEMPFILE"' EXIT
 115 chmod 0600 "$TEMPFILE"
 116 cat >"$TEMPFILE" <<EOF
 117 [client]
 118 user="${DBUSER}"
 119 password="${DBPASS}"
 120 EOF
 121 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 122
 123
 124 #check and ask for database user password
 125 DBUSER_="-u$DBUSER"
 126 [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
 127 DBHOST_="-h$DBHOST"
 128 DBPORT_="-P$DBPORT"
 129 while ! mysql $DEF_EXTRA_FILE_PARAM $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev/null 2>&1
 130 do
 131         [ -n "$logintry" ] &&  echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
 132         [ -z "$logintry" ] &&  echo -e "\nProvide database name and credentials"
 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         cat >"$TEMPFILE" <<EOF
 139 [client]
 140 user="${DBUSER}"
 141 password="${DBPASS}"
 142 EOF
 143         logintry="yes"
 144         echo
 145 done
 146
 147 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBHOST_ $DBPORT_ $DBNAME"
 148 #echo DBCMD $DBCMD
 149
 150 #GET DATABASE VERSION
 151 #check that the database seems a openmano database
 152 if ! echo -e "show create table vnfs;\nshow create table scenarios" | $DBCMD >/dev/null 2>&1
 153 then
 154     echo "    database $DBNAME does not seem to be an openmano database" >&2
 155     exit -1;
 156 fi
 157
 158 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 159 then
 160     DATABASE_VER="0.0"
 161     DATABASE_VER_NUM=0
 162 else
 163     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 164     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 165     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 166     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 167 fi
 168
 169
 170 #GET DATABASE TARGET VERSION
 171 DATABASE_TARGET_VER_NUM=0
 172 [ $OPENMANO_VER_NUM -ge 2002 ] && DATABASE_TARGET_VER_NUM=1   #0.2.2 =>  1
 173 [ $OPENMANO_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=2   #0.2.5 =>  2
 174 [ $OPENMANO_VER_NUM -ge 3003 ] && DATABASE_TARGET_VER_NUM=3   #0.3.3 =>  3
 175 [ $OPENMANO_VER_NUM -ge 3005 ] && DATABASE_TARGET_VER_NUM=4   #0.3.5 =>  4
 176 [ $OPENMANO_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5   #0.4.1 =>  5
 177 [ $OPENMANO_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6   #0.4.2 =>  6
 178 [ $OPENMANO_VER_NUM -ge 4003 ] && DATABASE_TARGET_VER_NUM=7   #0.4.3 =>  7
 179 [ $OPENMANO_VER_NUM -ge 4032 ] && DATABASE_TARGET_VER_NUM=8   #0.4.32=>  8
 180 [ $OPENMANO_VER_NUM -ge 4033 ] && DATABASE_TARGET_VER_NUM=9   #0.4.33=>  9
 181 [ $OPENMANO_VER_NUM -ge 4036 ] && DATABASE_TARGET_VER_NUM=10  #0.4.36=>  10
 182 [ $OPENMANO_VER_NUM -ge 4043 ] && DATABASE_TARGET_VER_NUM=11  #0.4.43=>  11
 183 [ $OPENMANO_VER_NUM -ge 4046 ] && DATABASE_TARGET_VER_NUM=12  #0.4.46=>  12
 184 [ $OPENMANO_VER_NUM -ge 4047 ] && DATABASE_TARGET_VER_NUM=13  #0.4.47=>  13
 185 [ $OPENMANO_VER_NUM -ge 4057 ] && DATABASE_TARGET_VER_NUM=14  #0.4.57=>  14
 186 [ $OPENMANO_VER_NUM -ge 4059 ] && DATABASE_TARGET_VER_NUM=15  #0.4.59=>  15
 187 #TODO ... put next versions here
 188
 189
 190 function upgrade_to_1(){
 191     echo "    upgrade database from version 0.0 to version 0.1"
 192     echo "      CREATE TABLE \`schema_version\`"
 193     echo "CREATE TABLE \`schema_version\` (
 194         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 195         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 196         \`openmano_ver\` VARCHAR(20) NOT NULL COMMENT 'openmano version',
 197         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 198         \`date\` DATE NULL,
 199         PRIMARY KEY (\`version_int\`)
 200         )
 201         COMMENT='database schema control version'
 202         COLLATE='utf8_general_ci'
 203         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 204     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openmano_ver\`, \`comments\`, \`date\`)
 205          VALUES (1, '0.1', '0.2.2', 'insert schema_version', '2015-05-08');" | $DBCMD
 206 }
 207 function downgrade_from_1(){
 208     echo "    downgrade database from version 0.1 to version 0.0"
 209     echo "      DROP TABLE \`schema_version\`"
 210     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 211 }
 212 function upgrade_to_2(){
 213     echo "    upgrade database from version 0.1 to version 0.2"
 214     echo "      Add columns user/passwd to table 'vim_tenants'"
 215     echo "ALTER TABLE vim_tenants ADD COLUMN user VARCHAR(36) NULL COMMENT 'Credentials for vim' AFTER created,
 216         ADD COLUMN passwd VARCHAR(50) NULL COMMENT 'Credentials for vim' AFTER user;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 217     echo "      Add table 'images' and 'datacenters_images'"
 218     echo "CREATE TABLE images (
 219         uuid VARCHAR(36) NOT NULL,
 220         name VARCHAR(50) NOT NULL,
 221         location VARCHAR(200) NOT NULL,
 222         description VARCHAR(100) NULL,
 223         metadata VARCHAR(400) NULL,
 224         PRIMARY KEY (uuid),
 225         UNIQUE INDEX location (location)  )
 226         COLLATE='utf8_general_ci'
 227         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 228     echo "CREATE TABLE datacenters_images (
 229         id INT NOT NULL AUTO_INCREMENT,
 230         image_id VARCHAR(36) NOT NULL,
 231         datacenter_id VARCHAR(36) NOT NULL,
 232         vim_id VARCHAR(36) NOT NULL,
 233         PRIMARY KEY (id),
 234         CONSTRAINT FK__images FOREIGN KEY (image_id) REFERENCES images (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 235         CONSTRAINT FK__datacenters_i FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 236         COLLATE='utf8_general_ci'
 237         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 238     echo "      migrate data from table 'vms' into 'images'"
 239     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
 240     echo "INSERT INTO datacenters_images (image_id, datacenter_id, vim_id)
 241           SELECT DISTINCT vim_image_id, datacenters.uuid, vim_image_id FROM vms JOIN datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 242     echo "      Add table 'flavors' and 'datacenter_flavors'"
 243     echo "CREATE TABLE flavors (
 244         uuid VARCHAR(36) NOT NULL,
 245         name VARCHAR(50) NOT NULL,
 246         description VARCHAR(100) NULL,
 247         disk SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 248         ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 249         vcpus SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
 250         extended VARCHAR(2000) NULL DEFAULT NULL COMMENT 'Extra description json format of needed resources and pining, orginized in sets per numa',
 251         PRIMARY KEY (uuid)  )
 252         COLLATE='utf8_general_ci'
 253         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 254     echo "CREATE TABLE datacenters_flavors (
 255         id INT NOT NULL AUTO_INCREMENT,
 256         flavor_id VARCHAR(36) NOT NULL,
 257         datacenter_id VARCHAR(36) NOT NULL,
 258         vim_id VARCHAR(36) NOT NULL,
 259         PRIMARY KEY (id),
 260         CONSTRAINT FK__flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid) ON UPDATE CASCADE ON DELETE CASCADE,
 261         CONSTRAINT FK__datacenters_f FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid) ON UPDATE CASCADE ON DELETE CASCADE  )
 262         COLLATE='utf8_general_ci'
 263         ENGINE=InnoDB;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 264     echo "      migrate data from table 'vms' into 'flavors'"
 265     echo "INSERT INTO flavors (uuid, name) SELECT DISTINCT vim_flavor_id, vim_flavor_id FROM vms;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 266     echo "INSERT INTO datacenters_flavors (flavor_id, datacenter_id, vim_id)
 267           SELECT DISTINCT vim_flavor_id, datacenters.uuid, vim_flavor_id FROM vms JOIN datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 268     echo "ALTER TABLE vms ALTER vim_flavor_id DROP DEFAULT, ALTER vim_image_id DROP DEFAULT;
 269           ALTER TABLE vms CHANGE COLUMN vim_flavor_id flavor_id VARCHAR(36) NOT NULL COMMENT 'Link to flavor table' AFTER vnf_id,
 270           CHANGE COLUMN vim_image_id image_id VARCHAR(36) NOT NULL COMMENT 'Link to image table' AFTER flavor_id,
 271           ADD CONSTRAINT FK_vms_images  FOREIGN KEY (image_id) REFERENCES  images (uuid),
 272           ADD CONSTRAINT FK_vms_flavors FOREIGN KEY (flavor_id) REFERENCES flavors (uuid);
 273          " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 274     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
 275
 276 }
 277
 278 function downgrade_from_2(){
 279     echo "    downgrade database from version 0.2 to version 0.1"
 280     echo "       migrate back data from 'datacenters_images' 'datacenters_flavors' into 'vms'"
 281     echo "ALTER TABLE vms ALTER image_id DROP DEFAULT, ALTER flavor_id DROP DEFAULT;
 282           ALTER TABLE vms CHANGE COLUMN flavor_id vim_flavor_id VARCHAR(36) NOT NULL COMMENT 'Flavor ID in the VIM DB' AFTER vnf_id,
 283           CHANGE COLUMN image_id vim_image_id VARCHAR(36) NOT NULL COMMENT 'Image ID in the VIM DB' AFTER vim_flavor_id,
 284           DROP FOREIGN KEY FK_vms_flavors, DROP INDEX FK_vms_flavors,
 285           DROP FOREIGN KEY FK_vms_images, DROP INDEX FK_vms_images;
 286          " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 287 #    echo "UPDATE v SET v.vim_image_id=di.vim_id
 288 #          FROM  vms as v INNER JOIN images as i ON v.vim_image_id=i.uuid
 289 #          INNER JOIN datacenters_images as di ON i.uuid=di.image_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 290     echo "      Delete columns 'user/passwd' from 'vim_tenants'"
 291     echo "ALTER TABLE vim_tenants DROP COLUMN user, DROP COLUMN passwd; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 292     echo "        delete tables 'datacenter_images', 'images'"
 293     echo "DROP TABLE \`datacenters_images\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 294     echo "DROP TABLE \`images\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 295     echo "        delete tables 'datacenter_flavors', 'flavors'"
 296     echo "DROP TABLE \`datacenters_flavors\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 297     echo "DROP TABLE \`flavors\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 298     echo "DELETE FROM schema_version WHERE version_int='2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 299 }
 300
 301 function upgrade_to_3(){
 302     echo "    upgrade database from version 0.2 to version 0.3"
 303     echo "      Change table 'logs', 'uuids"
 304     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
 305     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
 306     echo "      Add column created to table 'datacenters_images' and 'datacenters_flavors'"
 307     for table in datacenters_images datacenters_flavors
 308     do
 309         echo "ALTER TABLE $table ADD COLUMN created ENUM('true','false') NOT NULL DEFAULT 'false'
 310             COMMENT 'Indicates if it has been created by openmano, or already existed' AFTER vim_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 311     done
 312     echo "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(2000) NULL DEFAULT NULL AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 313     echo "      Allow null to column 'vim_interface_id' in 'instance_interfaces'"
 314     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
 315     echo "      Add column config to table 'datacenters'"
 316     echo "ALTER TABLE datacenters ADD COLUMN config VARCHAR(4000) NULL DEFAULT NULL COMMENT 'extra config information in json' AFTER vim_url_admin;
 317         " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 318     echo "      Add column datacenter_id to table 'vim_tenants'"
 319     echo "ALTER TABLE vim_tenants ADD COLUMN datacenter_id VARCHAR(36) NULL COMMENT 'Datacenter of this tenant' AFTER uuid,
 320         DROP INDEX name, DROP INDEX vim_tenant_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 321     echo "ALTER TABLE vim_tenants CHANGE COLUMN name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL COMMENT 'tenant name at VIM' AFTER datacenter_id,
 322         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
 323     echo "UPDATE vim_tenants as vt LEFT JOIN tenants_datacenters as td ON vt.uuid=td.vim_tenant_id
 324         SET vt.datacenter_id=td.datacenter_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 325     echo "DELETE FROM vim_tenants WHERE datacenter_id is NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 326     echo "ALTER TABLE vim_tenants ALTER datacenter_id DROP DEFAULT;
 327         ALTER TABLE vim_tenants
 328         CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL COMMENT 'Datacenter of this tenant' AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 329     echo "ALTER TABLE vim_tenants ADD CONSTRAINT FK_vim_tenants_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid)
 330         ON UPDATE CASCADE ON DELETE CASCADE;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 331
 332     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
 333 }
 334
 335
 336 function downgrade_from_3(){
 337     echo "    downgrade database from version 0.3 to version 0.2"
 338     echo "      Change back table 'logs', 'uuids'"
 339     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
 340     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
 341     echo "      Delete column created from table 'datacenters_images' and 'datacenters_flavors'"
 342     for table in datacenters_images datacenters_flavors
 343     do
 344         echo "ALTER TABLE $table DROP COLUMN created;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 345     done
 346     echo "ALTER TABLE images CHANGE COLUMN metadata metadata VARCHAR(400) NULL DEFAULT NULL AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 347     echo "      Deny back null to column 'vim_interface_id' in 'instance_interfaces'"
 348     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
 349     echo "       Delete column config to table 'datacenters'"
 350     echo "ALTER TABLE datacenters DROP COLUMN config;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 351     echo "       Delete column datacenter_id to table 'vim_tenants'"
 352     echo "ALTER TABLE vim_tenants DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_vim_tenants_datacenters;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 353     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
 354     echo "ALTER TABLE vim_tenants ALTER name DROP DEFAULT;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 355     echo "ALTER TABLE vim_tenants CHANGE COLUMN name name VARCHAR(36) NOT NULL AFTER uuid"| $DBCMD || ! echo "Warning changing column name at vim_tenants!"
 356     echo "ALTER TABLE vim_tenants ADD UNIQUE INDEX name (name);" | $DBCMD || ! echo "Warning add unique index name at vim_tenants!"
 357     echo "ALTER TABLE vim_tenants ALTER vim_tenant_id DROP DEFAULT;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 358     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!"
 359     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!"
 360     echo "DELETE FROM schema_version WHERE version_int='3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 361 }
 362
 363 function upgrade_to_4(){
 364     echo "    upgrade database from version 0.3 to version 0.4"
 365     echo "      Enlarge graph field at tables 'sce_vnfs', 'sce_nets'"
 366     for table in sce_vnfs sce_nets
 367     do
 368         echo "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 369     done
 370     echo "ALTER TABLE datacenters CHANGE COLUMN type type VARCHAR(36) NOT NULL DEFAULT 'openvim' AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 371     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
 372 }
 373
 374 function downgrade_from_4(){
 375     echo "    downgrade database from version 0.4 to version 0.3"
 376     echo "      Shorten back graph field at tables 'sce_vnfs', 'sce_nets'"
 377     for table in sce_vnfs sce_nets
 378     do
 379         echo "ALTER TABLE $table CHANGE COLUMN graph graph VARCHAR(2000) NULL DEFAULT NULL AFTER modified_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 380     done
 381     echo "ALTER TABLE datacenters CHANGE COLUMN type type ENUM('openvim','openstack') NOT NULL DEFAULT 'openvim' AFTER description;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 382     echo "DELETE FROM schema_version WHERE version_int='4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 383 }
 384
 385 function upgrade_to_5(){
 386     echo "    upgrade database from version 0.4 to version 0.5"
 387     echo "      Add 'mac' field for bridge interfaces in table 'interfaces'"
 388     echo "ALTER TABLE interfaces ADD COLUMN mac CHAR(18) NULL DEFAULT NULL AFTER model;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 389     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
 390 }
 391 function downgrade_from_5(){
 392     echo "    downgrade database from version 0.5 to version 0.4"
 393     echo "      Remove 'mac' field for bridge interfaces in table 'interfaces'"
 394     echo "ALTER TABLE interfaces DROP COLUMN mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 395     echo "DELETE FROM schema_version WHERE version_int='5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 396 }
 397
 398 function upgrade_to_6(){
 399     echo "    upgrade database from version 0.5 to version 0.6"
 400     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 401     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
 402     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
 403     echo "      Add 'last_error', 'vim_info' to 'instance_vms', 'instance_nets'"
 404     echo "ALTER TABLE instance_vms  ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 405     echo "ALTER TABLE instance_vms  ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 406     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
 407     echo "ALTER TABLE instance_nets ADD COLUMN error_msg VARCHAR(1024) NULL DEFAULT NULL AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 408     echo "ALTER TABLE instance_nets ADD COLUMN vim_info TEXT NULL DEFAULT NULL AFTER error_msg;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 409     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
 410     echo "      Add 'mac_address', 'ip_address', 'vim_info' to 'instance_interfaces'"
 411     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
 412     echo "      Add 'sce_vnf_id','datacenter_id','vim_tenant_id' field to 'instance_vnfs'"
 413     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
 414     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
 415     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
 416     echo "      Add 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field to 'instance_nets'"
 417     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
 418     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
 419     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
 420     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
 421     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
 422 }
 423 function downgrade_from_6(){
 424     echo "    downgrade database from version 0.6 to version 0.5"
 425     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 426     echo "ALTER TABLE vnfs      DROP COLUMN descriptor;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 427     echo "ALTER TABLE scenarios DROP COLUMN descriptor;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 428     echo "      Remove 'last_error', 'vim_info' from 'instance_vms', 'instance_nets'"
 429     echo "ALTER TABLE instance_vms  DROP COLUMN error_msg, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 430     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
 431     echo "ALTER TABLE instance_nets DROP COLUMN error_msg, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 432     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
 433     echo "      Remove 'mac_address', 'ip_address', 'vim_info' from 'instance_interfaces'"
 434     echo "ALTER TABLE instance_interfaces DROP COLUMN mac_address, DROP COLUMN ip_address, DROP COLUMN vim_info;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 435     echo "      Remove 'sce_vnf_id','datacenter_id','vim_tenant_id' field from 'instance_vnfs'"
 436     echo "ALTER TABLE instance_vnfs DROP COLUMN sce_vnf_id, DROP FOREIGN KEY FK_instance_vnfs_sce_vnfs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 437     echo "ALTER TABLE instance_vnfs DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_vnfs_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 438     echo "ALTER TABLE instance_vnfs DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_vnfs_datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 439     echo "      Remove 'sce_net_id','net_id','datacenter_id','vim_tenant_id' field from 'instance_nets'"
 440     echo "ALTER TABLE instance_nets DROP COLUMN sce_net_id, DROP FOREIGN KEY FK_instance_nets_sce_nets;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 441     echo "ALTER TABLE instance_nets DROP COLUMN net_id, DROP FOREIGN KEY FK_instance_nets_nets;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 442     echo "ALTER TABLE instance_nets DROP COLUMN vim_tenant_id, DROP FOREIGN KEY FK_instance_nets_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 443     echo "ALTER TABLE instance_nets DROP COLUMN datacenter_id, DROP FOREIGN KEY FK_instance_nets_datacenters;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 444     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 445 }
 446
 447 function upgrade_to_7(){
 448     echo "    upgrade database from version 0.6 to version 0.7"
 449     echo "      Change created_at, modified_at from timestamp to unix float at all database"
 450     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
 451     do
 452          echo -en "        $table               \r"
 453          echo "ALTER TABLE $table ADD COLUMN created_at_ DOUBLE NOT NULL after created_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 454          echo "UPDATE $table SET created_at_=unix_timestamp(created_at);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 455          echo "ALTER TABLE $table DROP COLUMN created_at, CHANGE COLUMN created_at_ created_at DOUBLE NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 456          [[ $table == uuids ]] || echo "ALTER TABLE $table CHANGE COLUMN modified_at modified_at DOUBLE NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 457     done
 458
 459     echo "      Add 'descriptor' field text to 'vnfd', 'scenarios'"
 460     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
 461 }
 462 function downgrade_from_7(){
 463     echo "    downgrade database from version 0.7 to version 0.6"
 464     echo "      Change back created_at, modified_at from unix float to timestamp at all database"
 465     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
 466     do
 467          echo -en "        $table               \r"
 468          echo "ALTER TABLE $table ADD COLUMN created_at_ TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP after created_at;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 469          echo "UPDATE $table SET created_at_=from_unixtime(created_at);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 470          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
 471          [[ $table == uuids ]] || echo "ALTER TABLE $table CHANGE COLUMN modified_at modified_at TIMESTAMP NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 472     done
 473     echo "      Remove 'descriptor' field from 'vnfd', 'scenarios' tables"
 474     echo "DELETE FROM schema_version WHERE version_int='7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 475 }
 476
 477 function upgrade_to_8(){
 478     echo "    upgrade database from version 0.7 to version 0.8"
 479     echo "      Change enalarge name, description to 255 at all database"
 480     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 481     do
 482          echo -en "        $table               \r"
 483          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR(255) NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 484          echo "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 485     done
 486     echo -en "        interfaces           \r"
 487     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
 488     echo "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 489     echo -en "        vim_tenants          \r"
 490     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
 491     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
 492 }
 493 function downgrade_from_8(){
 494     echo "    downgrade database from version 0.8 to version 0.7"
 495     echo "      Change back name,description to shorter length at all database"
 496     for table in datacenters datacenter_nets flavors images instance_scenarios nets nfvo_tenants scenarios sce_nets sce_vnfs vms vnfs
 497     do
 498          name_length=50
 499          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 500          echo -en "        $table               \r"
 501          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 502          echo "ALTER TABLE $table CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 503     done
 504     echo -en "        interfaces           \r"
 505     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
 506     echo -en "        vim_tenants          \r"
 507     echo "ALTER TABLE vim_tenants CHANGE COLUMN vim_tenant_name vim_tenant_name VARCHAR(36) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 508     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
 509     echo "DELETE FROM schema_version WHERE version_int='8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 510 }
 511 function upgrade_to_9(){
 512     echo "    upgrade database from version 0.8 to version 0.9"
 513     echo "      Add more status to 'instance_vms'"
 514     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
 515     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
 516 }
 517 function downgrade_from_9(){
 518     echo "    downgrade database from version 0.9 to version 0.8"
 519     echo "      Add more status to 'instance_vms'"
 520     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
 521     echo "DELETE FROM schema_version WHERE version_int='9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 522 }
 523 function upgrade_to_10(){
 524     echo "    upgrade database from version 0.9 to version 0.10"
 525     echo "      add tenant to 'vnfs'"
 526     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
 527     echo "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 528     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
 529     echo "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 530     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
 531     echo "      rename 'vim_tenants' table to 'datacenter_tenants'"
 532     echo "RENAME TABLE vim_tenants TO datacenter_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 533     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 534     do
 535         NULL="NOT NULL"
 536         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 537         echo "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 538         echo "ALTER TABLE ${table} ALTER vim_tenant_id DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 539         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
 540     done
 541     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
 542 }
 543
 544 function downgrade_from_10(){
 545     echo "    downgrade database from version 0.10 to version 0.9"
 546     echo "      remove tenant from 'vnfs'"
 547     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
 548     echo "ALTER TABLE scenarios DROP FOREIGN KEY FK_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 549     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
 550     echo "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_nfvo_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 551     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
 552     echo "      rename back 'datacenter_tenants' table to 'vim_tenants'"
 553     echo "RENAME TABLE datacenter_tenants TO vim_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 554     for table in tenants_datacenters instance_scenarios instance_vnfs instance_nets
 555     do
 556         echo "ALTER TABLE ${table} DROP FOREIGN KEY FK_${table}_datacenter_tenants;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 557         NULL="NOT NULL"
 558         [[ $table == instance_vnfs ]] && NULL="NULL DEFAULT NULL"
 559         echo "ALTER TABLE ${table} ALTER datacenter_tenant_id DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 560         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
 561     done
 562     echo "DELETE FROM schema_version WHERE version_int='10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 563 }
 564
 565 function upgrade_to_11(){
 566     echo "    upgrade database from version 0.10 to version 0.11"
 567     echo "      remove unique name at 'scenarios', 'instance_scenarios'"
 568     echo "ALTER TABLE scenarios DROP INDEX name;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 569     echo "ALTER TABLE instance_scenarios DROP INDEX name;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 570     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
 571 }
 572 function downgrade_from_11(){
 573     echo "    downgrade database from version 0.11 to version 0.10"
 574     echo "      add unique name at 'scenarios', 'instance_scenarios'"
 575     echo "ALTER TABLE scenarios ADD UNIQUE INDEX name (name);"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 576     echo "ALTER TABLE instance_scenarios ADD UNIQUE INDEX name (name);"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 577     echo "DELETE FROM schema_version WHERE version_int='11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 578 }
 579
 580 function upgrade_to_12(){
 581     echo "    upgrade database from version 0.11 to version 0.12"
 582     echo "      create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to 'interfaces' and 'sce_interfaces'"
 583     echo "CREATE TABLE IF NOT EXISTS ip_profiles (
 584         id INT(11) NOT NULL AUTO_INCREMENT,
 585         net_id VARCHAR(36) NULL DEFAULT NULL,
 586         sce_net_id VARCHAR(36) NULL DEFAULT NULL,
 587         instance_net_id VARCHAR(36) NULL DEFAULT NULL,
 588         ip_version ENUM('IPv4','IPv6') NOT NULL DEFAULT 'IPv4',
 589         subnet_address VARCHAR(64) NULL DEFAULT NULL,
 590         gateway_address VARCHAR(64) NULL DEFAULT NULL,
 591         dns_address VARCHAR(64) NULL DEFAULT NULL,
 592         dhcp_enabled ENUM('true','false') NOT NULL DEFAULT 'true',
 593         dhcp_start_address VARCHAR(64) NULL DEFAULT NULL,
 594         dhcp_count INT(11) NULL DEFAULT NULL,
 595         PRIMARY KEY (id),
 596         CONSTRAINT FK_ipprofiles_nets FOREIGN KEY (net_id) REFERENCES nets (uuid) ON DELETE CASCADE,
 597         CONSTRAINT FK_ipprofiles_scenets FOREIGN KEY (sce_net_id) REFERENCES sce_nets (uuid) ON DELETE CASCADE,
 598         CONSTRAINT FK_ipprofiles_instancenets FOREIGN KEY (instance_net_id) REFERENCES instance_nets (uuid) ON DELETE CASCADE  )
 599         COMMENT='Table containing the IP parameters of a network, either a net, a sce_net or and instance_net.'
 600         COLLATE='utf8_general_ci'
 601         ENGINE=InnoDB;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 602     echo "ALTER TABLE interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 603     echo "ALTER TABLE sce_interfaces ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER interface_id;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 604     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
 605 }
 606 function downgrade_from_12(){
 607     echo "    downgrade database from version 0.12 to version 0.11"
 608     echo "      delete ip_profiles table, and remove ip_address column in 'interfaces' and 'sce_interfaces'"
 609     echo "DROP TABLE ip_profiles;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 610     echo "ALTER TABLE interfaces DROP COLUMN ip_address;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 611     echo "ALTER TABLE sce_interfaces DROP COLUMN ip_address;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 612     echo "DELETE FROM schema_version WHERE version_int='12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 613 }
 614
 615 function upgrade_to_13(){
 616     echo "    upgrade database from version 0.12 to version 0.13"
 617     echo "      add cloud_config at 'scenarios', 'instance_scenarios'"
 618     echo "ALTER TABLE scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER descriptor;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 619     echo "ALTER TABLE instance_scenarios ADD COLUMN cloud_config MEDIUMTEXT NULL DEFAULT NULL AFTER modified_at;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 620     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
 621 }
 622 function downgrade_from_13(){
 623     echo "    downgrade database from version 0.13 to version 0.12"
 624     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 625     echo "ALTER TABLE scenarios DROP COLUMN cloud_config;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 626     echo "ALTER TABLE instance_scenarios DROP COLUMN cloud_config;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 627     echo "DELETE FROM schema_version WHERE version_int='13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 628 }
 629
 630 function upgrade_to_14(){
 631     echo "    upgrade database from version 0.13 to version 0.14"
 632     echo "      remove unique index vim_net_id, instance_scenario_id at table 'instance_nets'"
 633     echo "ALTER TABLE instance_nets DROP INDEX vim_net_id_instance_scenario_id;"  | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 634     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
 635     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
 636 }
 637 function downgrade_from_14(){
 638     echo "    downgrade database from version 0.14 to version 0.13"
 639     echo "      remove cloud_config at 'scenarios', 'instance_scenarios'"
 640     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
 641     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
 642     echo "DELETE FROM schema_version WHERE version_int='14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 643 }
 644
 645 function upgrade_to_15(){
 646     echo "    upgrade database from version 0.14 to version 0.15"
 647     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"
 648     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL DEFAULT NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 649     echo "ALTER TABLE images ALTER location DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 650     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
 651     echo "ALTER TABLE vms ALTER image_path DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 652     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
 653     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
 654 }
 655 function downgrade_from_15(){
 656     echo "    downgrade database from version 0.15 to version 0.14"
 657     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"
 658     echo "ALTER TABLE images DROP INDEX universal_name_checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 659     echo "ALTER TABLE images ALTER location DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 660     echo "ALTER TABLE images CHANGE COLUMN location location VARCHAR(200) NOT NULL AFTER checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 661     echo "ALTER TABLE images DROP COLUMN universal_name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 662     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 663     echo "ALTER TABLE vms ALTER image_path DROP DEFAULT;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 664     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
 665     echo "DELETE FROM schema_version WHERE version_int='15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 666 }
 667
 668 function upgrade_to_X(){
 669     echo "      change 'datacenter_nets'"
 670     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
 671 }
 672 function downgrade_from_X(){
 673     echo "      Change back 'datacenter_nets'"
 674     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
 675 }
 676 #TODO ... put funtions here
 677
 678
 679 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo "    current database version $DATABASE_VER is ok"
 680 #UPGRADE DATABASE step by step
 681 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
 682 do
 683     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 684     upgrade_to_${DATABASE_VER_NUM}
 685     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 686     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 687     #$FILE_ || exit -1  # if fail return
 688 done
 689
 690 #DOWNGRADE DATABASE step by step
 691 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
 692 do
 693     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 694     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 695     #$FILE_ || exit -1  # if fail return
 696     downgrade_from_${DATABASE_VER_NUM}
 697     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 698 done
 699
 700 #echo done
 701