RO/osm_ro/database_utils/migrate_mano_db.sh

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