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