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