RO/osm_ro/database_utils/migrate_mano_db.sh

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