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