database_utils/migrate_vim_db.sh

   1 #!/bin/bash
   2
   3 ##
   4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
   5 # This file is part of openvim
   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 openvim database preserving the content
  26 #
  27
  28 DBUSER="vim"
  29 DBPASS=""
  30 DEFAULT_DBPASS="vimpw"
  31 DBHOST=""
  32 DBPORT="3306"
  33 DBNAME="vim_db"
  34 QUIET_MODE=""
  35 #TODO update it with the last database version
  36 LAST_DB_VERSION=22
  37
  38 # Detect paths
  39 MYSQL=$(which mysql)
  40 AWK=$(which awk)
  41 GREP=$(which grep)
  42
  43 function usage(){
  44     echo -e "Usage: $0 OPTIONS [version]"
  45     echo -e "  Upgrades/Downgrades openvim database preserving the content."\
  46             "If [version]  is not provided, it is upgraded to the last version"
  47     echo -e "  OPTIONS"
  48     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  49     echo -e "     -p PASS  database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
  50     echo -e "     -P PORT  database port. '$DBPORT' by default"
  51     echo -e "     -h HOST  database host. 'localhost' by default"
  52     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  53     echo -e "     -q --quiet: Do not prompt for credentials and exit if cannot access to database"
  54     echo -e "     --help   shows this help"
  55 }
  56
  57 while getopts ":u:p:P:h:d:q-:" o; do
  58     case "${o}" in
  59         u)
  60             DBUSER="$OPTARG"
  61             ;;
  62         p)
  63             DBPASS="$OPTARG"
  64             ;;
  65         P)
  66             DBPORT="$OPTARG"
  67             ;;
  68         d)
  69             DBNAME="$OPTARG"
  70             ;;
  71         h)
  72             DBHOST="$OPTARG"
  73             ;;
  74         q)
  75             export QUIET_MODE=yes
  76             ;;
  77         -)
  78             [ "${OPTARG}" == "help" ] && usage && exit 0
  79             [ "${OPTARG}" == "quiet" ] && export QUIET_MODE=yes && continue
  80             echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
  81             exit 1
  82             ;;
  83         \?)
  84             echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
  85             exit 1
  86             ;;
  87         :)
  88             echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
  89             exit 1
  90             ;;
  91         *)
  92             usage >&2
  93             exit 1
  94             ;;
  95     esac
  96 done
  97 shift $((OPTIND-1))
  98
  99 DB_VERSION=$1
 100
 101 if [ -n "$DB_VERSION" ] ; then
 102     # check it is a number and an allowed one
 103     [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev/null ||
 104         ! echo "parameter 'version' requires a integer value" >&2 || exit 1
 105     if [ "$DB_VERSION" -lt 0 ] || [ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
 106         echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
 107              "If you need an upper version, get a newer version of this script '$0'" >&2
 108         exit 1
 109     fi
 110 else
 111     DB_VERSION="$LAST_DB_VERSION"
 112 fi
 113
 114 # Creating temporary file
 115 TEMPFILE="$(mktemp -q --tmpdir "migratevimdb.XXXXXX")"
 116 trap 'rm -f "$TEMPFILE"' EXIT
 117 chmod 0600 "$TEMPFILE"
 118 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 119 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 120
 121 # Check and ask for database user password
 122 FIRST_TRY="yes"
 123 while ! DB_ERROR=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
 124 do
 125     # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
 126     [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS="$DEFAULT_DBPASS" &&
 127         echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
 128         continue
 129     echo "$DB_ERROR"
 130     [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
 131     echo -e "Provide database name and credentials (Ctrl+c to abort):"
 132     read -e -p "    mysql database name($DBNAME): " KK
 133     [ -n "$KK" ] && DBNAME="$KK"
 134     read -e -p "    mysql user($DBUSER): " KK
 135     [ -n "$KK" ] && DBUSER="$KK"
 136     read -e -s -p "    mysql password: " DBPASS
 137     echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 138     FIRST_TRY=""
 139     echo
 140 done
 141
 142 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
 143 #echo DBCMD $DBCMD
 144
 145 #GET DATABASE VERSION
 146 # check that the database seems a openvim database
 147 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
 148 then
 149     echo "    database $DBNAME does not seem to be an openvim database" >&2
 150     exit -1;
 151 fi
 152
 153 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 154 then
 155     DATABASE_VER="0.0"
 156     DATABASE_VER_NUM=0
 157 else
 158     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 159     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 160     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] &&
 161         echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 162     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 163 fi
 164
 165 [ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ] &&
 166     echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
 167     exit 1
 168
 169 #GET DATABASE TARGET VERSION
 170 #DB_VERSION=0
 171 #[ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1   #>0.1.91 =>  1
 172 #[ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2   #0.2.03  =>  2
 173 #[ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3   #0.2.5   =>  3
 174 #[ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4   #0.3.1   =>  4
 175 #[ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5   #0.4.1   =>  5
 176 #[ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6   #0.4.2   =>  6
 177 #[ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7   #0.4.5   =>  7
 178 #[ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM=8   #0.4.10  =>  8
 179 #[ $OPENVIM_VER_NUM -ge 5001 ] && DATABASE_TARGET_VER_NUM=9   #0.5.1   =>  9
 180 #[ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM=10  #0.5.2   => 10
 181 #[ $OPENVIM_VER_NUM -ge 5004 ] && DATABASE_TARGET_VER_NUM=11  #0.5.4   => 11
 182 #[ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM=12  #0.5.5   => 12
 183 #[ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM=13  #0.5.6   => 13
 184 #[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14  #0.5.7   => 14
 185 #[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15  #0.5.8   => 15
 186 #[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16  #0.5.9   => 16
 187 #[ $OPENVIM_VER_NUM -ge 5010 ] && DATABASE_TARGET_VER_NUM=17  #0.5.10  => 17
 188 #[ $OPENVIM_VER_NUM -ge 5013 ] && DATABASE_TARGET_VER_NUM=18  #0.5.13  => 18
 189 #[ $OPENVIM_VER_NUM -ge 5015 ] && DATABASE_TARGET_VER_NUM=19  #0.5.15  => 19
 190 #[ $OPENVIM_VER_NUM -ge 5017 ] && DATABASE_TARGET_VER_NUM=20   #0.5.17  => 20
 191 #[ $OPENVIM_VER_NUM -ge 5018 ] && DATABASE_TARGET_VER_NUM=21   #0.5.18  => 21
 192 #[ $OPENVIM_VER_NUM -ge 5021 ] && DATABASE_TARGET_VER_NUM=22   #0.5.21  => 22
 193 # TODO ... put next versions here
 194
 195 function upgrade_to_1(){
 196     # echo "    upgrade database from version 0.0 to version 0.1"
 197     echo "      CREATE TABLE \`schema_version\`"
 198     echo "CREATE TABLE \`schema_version\` (
 199         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 200         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 201         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 202         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 203         \`date\` DATE NULL,
 204         PRIMARY KEY (\`version_int\`)
 205         )
 206         COMMENT='database schema control version'
 207         COLLATE='utf8_general_ci'
 208         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 209     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 210          VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
 211     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 212     echo "ALTER TABLE \`nets\`
 213          ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 214 }
 215 function downgrade_from_1(){
 216     # echo "    downgrade database from version 0.1 to version 0.0"
 217     echo "      ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
 218     echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 219     echo "      DROP TABLE \`schema_version\`"
 220     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 221 }
 222 function upgrade_to_2(){
 223     # echo "    upgrade database from version 0.1 to version 0.2"
 224     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
 225     for table in of_ports_pci_correspondence resources_port ports
 226     do
 227         echo "ALTER TABLE \`${table}\`
 228             ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 229         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 230         [ $table == of_ports_pci_correspondence ] ||
 231             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 232     done
 233     echo "      UPDATE procedure UpdateSwitchPort"
 234     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 235     delimiter //
 236     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 237     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 238     BEGIN
 239         #DELETES switch_port entry before writing, because if not it fails for key constrains
 240         UPDATE ports
 241         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 242         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 243         INNER JOIN numas on RP.numa_id=numas.id
 244         INNER JOIN hosts on numas.host_id=hosts.uuid
 245         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 246         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 247         #write switch_port into resources_port and ports
 248         UPDATE ports
 249         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 250         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 251         INNER JOIN numas on RP.numa_id=numas.id
 252         INNER JOIN hosts on numas.host_id=hosts.uuid
 253         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 254         SET ports.switch_port=PC.switch_port, ports.switch_dpid=PC.switch_dpid, RP.switch_port=PC.switch_port, RP.switch_dpid=PC.switch_dpid;
 255     END//
 256     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 257     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 258          VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 259 }
 260 function upgrade_to_3(){
 261     # echo "    upgrade database from version 0.2 to version 0.3"
 262     echo "     change size of source_name at table resources_port"
 263     echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL AFTER port_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 264     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 265     echo "delimiter //
 266     CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 267     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 268     BEGIN
 269         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 270         FROM
 271         (
 272            SELECT id as port_id, Mbps, pci, switch_port, mac
 273            FROM resources_port
 274                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 275         ) as A
 276         INNER JOIN
 277         (
 278            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 279                 FROM resources_port
 280                 WHERE numa_id = Numa AND status = 'ok'
 281                 GROUP BY root_id
 282         ) as B
 283         ON A.port_id = B.root_id
 284         LEFT JOIN
 285         (
 286            SELECT root_id,  COUNT(id) as usedSRIOV
 287                 FROM resources_port
 288                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 289                 GROUP BY root_id
 290         ) as C
 291         ON A.port_id = C.root_id
 292         ORDER BY Mbps_free, availableSRIOV, pci;
 293     END//
 294     delimiter ;"| $DBCMD || !  ! echo "ERROR. Aborted!" || exit -1
 295     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (3, '0.3', '0.2.5', 'New Procedure GetAllAvailablePorts', '2015-07-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 296 }
 297
 298 function upgrade_to_4(){
 299     # echo "    upgrade database from version 0.3 to version 0.4"
 300     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 301     echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 302     echo "ALTER TABLE ports          DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 303     echo "     change table 'ports'"
 304     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL COMMENT 'driver model for bridge ifaces; PF,VF,VFnotShared for data ifaces' AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 305     echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 306     echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 307     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (4, '0.4', '0.3.1', 'Remove unique index VLAN at resources_port', '2015-09-04');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 308 }
 309
 310 function upgrade_to_X(){
 311     #TODO, this change of foreign key does not work
 312     # echo "    upgrade database from version 0.X to version 0.X"
 313     echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
 314           DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 315     echo "ALTER TABLE instances
 316         ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
 317         ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 318 }
 319
 320 function downgrade_from_2(){
 321     # echo "    downgrade database from version 0.2 to version 0.1"
 322     echo "      UPDATE procedure UpdateSwitchPort"
 323     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 324     delimiter //
 325     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 326     BEGIN
 327     UPDATE
 328         resources_port INNER JOIN (
 329             SELECT resources_port.id,KK.switch_port
 330             FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 331                 INNER JOIN hosts on numas.host_id=hosts.uuid
 332                 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 333             ) as TABLA
 334         ON  resources_port.root_id=TABLA.id
 335     SET resources_port.switch_port=TABLA.switch_port
 336     WHERE resources_port.root_id=TABLA.id;
 337     END//
 338     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 339     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 340     for table in of_ports_pci_correspondence resources_port ports
 341     do
 342         [ $table == of_ports_pci_correspondence ] ||
 343             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 344         echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 345         switch_port_size=12
 346         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 347         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 348     done
 349     echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 350 }
 351 function downgrade_from_3(){
 352     # echo "    downgrade database from version 0.3 to version 0.2"
 353     echo "     change back size of source_name at table resources_port"
 354     echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(20) NULL DEFAULT NULL AFTER port_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 355     echo "      DROP PROCEDURE GetAllAvailablePorts"
 356     echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 357     echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 358 }
 359 function downgrade_from_4(){
 360     # echo "    downgrade database from version 0.4 to version 0.3"
 361     echo "     adding back unique index VLAN at 'resources_port','ports'"
 362     echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 363     echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 364     echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 365     echo "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 366     echo "     change back table 'ports'"
 367     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 368     echo "ALTER TABLE ports ADD COLUMN vlan_changed SMALLINT(5) NULL DEFAULT NULL COMMENT '!=NULL when original vlan have been changed to match a pmp net with all ports in the same vlan' AFTER switch_port;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 369     echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 370 }
 371
 372
 373 function upgrade_to_5(){
 374     # echo "    upgrade database from version 0.4 to version 0.5"
 375     echo "     add 'ip_address' to ports'"
 376     echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 377     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (5, '0.5', '0.4.1', 'Add ip_address to ports', '2015-09-04');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 378 }
 379 function downgrade_from_5(){
 380     # echo "    downgrade database from version 0.5 to version 0.4"
 381     echo "     removing 'ip_address' from 'ports'"
 382     echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 383     echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 384 }
 385
 386 function upgrade_to_6(){
 387     # echo "    upgrade database from version 0.5 to version 0.6"
 388     echo "      Change enalarge name, description to 255 at all database"
 389     for table in flavors images instances tenants
 390     do
 391          name_length=255
 392          [[ $table == tenants ]] || name_length=64
 393          echo -en "        $table               \r"
 394          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 395     done
 396     echo -en "        hosts               \r"
 397     echo "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(64) NOT NULL, CHANGE COLUMN user user VARCHAR(64) NOT NULL, CHANGE COLUMN password password VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN features features VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 398     echo -en "        nets                \r"
 399     echo "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 400     echo -en "        instances           \r"
 401     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 402     echo -en "        ports               \r"
 403     echo "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 404     echo -en "        of_flows            \r"
 405     echo "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN net_id net_id VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 406     echo -en "        of_ports_pci_cor... \r"
 407     echo "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 408     echo -en "        resources_port      \r"
 409     echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 410     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (6, '0.6', '0.4.2', 'Enlarging name at database', '2016-02-01');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 411 }
 412 function downgrade_from_6(){
 413     # echo "    downgrade database from version 0.6 to version 0.5"
 414     echo "      Change back name,description to shorter length at all database"
 415     for table in flavors images instances tenants
 416     do
 417          name_length=50
 418          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 419          echo -en "        $table               \r"
 420          echo "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 421     done
 422     echo -en "        hosts               \r"
 423     echo "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(36) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(36) NOT NULL, CHANGE COLUMN user user VARCHAR(36) NOT NULL, CHANGE COLUMN password password VARCHAR(36) NULL DEFAULT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL, CHANGE COLUMN features features VARCHAR(50) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 424     echo -en "        nets                \r"
 425     echo "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(50) NOT NULL, CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 426     echo -en "        instances           \r"
 427     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 428     echo -en "        ports               \r"
 429     echo "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(25) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 430     echo -en "        of_flows            \r"
 431     echo "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN net_id net_id VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(100) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 432     echo -en "        of_ports_pci_cor... \r"
 433     echo "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 434     echo -en "        resources_port      \r"
 435     echo "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 436     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 437 }
 438
 439 function upgrade_to_7(){
 440     # echo "    upgrade database from version 0.6 to version 0.7"
 441     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 442     echo "ALTER TABLE nets ADD COLUMN cidr VARCHAR(64) NULL DEFAULT NULL AFTER bind, ADD COLUMN enable_dhcp ENUM('true','false') NOT NULL DEFAULT 'false' after cidr, ADD COLUMN dhcp_first_ip VARCHAR(64) NULL DEFAULT NULL AFTER enable_dhcp, ADD COLUMN dhcp_last_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_first_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 443     echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 444     echo "ALTER TABLE nets ADD COLUMN bind_net VARCHAR(36) NULL DEFAULT NULL COMMENT 'To connect with other net' AFTER provider, ADD COLUMN bind_type VARCHAR(36)  NULL DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove' after bind_net;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 445     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (7, '0.7', '0.4.5', 'Add bind_net to net table', '2016-02-12');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 446 }
 447 function downgrade_from_7(){
 448     # echo "    downgrade database from version 0.7 to version 0.6"
 449     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 450     echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 451     echo "ALTER TABLE nets DROP COLUMN cidr, DROP COLUMN enable_dhcp, DROP COLUMN bind_net, DROP COLUMN bind_type, DROP COLUMN dhcp_first_ip, DROP COLUMN dhcp_last_ip;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 452     echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 453 }
 454
 455 function upgrade_to_8(){
 456     # echo "    upgrade database from version 0.7 to version 0.8"
 457     echo "     add column 'checksum' to 'images'"
 458     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 459     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (8, '0.8', '0.4.10', 'add column checksum to images', '2016-09-30');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 460 }
 461 function downgrade_from_8(){
 462     # echo "    downgrade database from version 0.8 to version 0.7"
 463     echo "     remove column 'checksum' from 'images'"
 464     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 465     echo "DELETE FROM schema_version WHERE version_int = '8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 466 }
 467
 468 function upgrade_to_9(){
 469     # echo "    upgrade database from version 0.8 to version 0.9"
 470     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
 471     echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER path;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 472     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 473     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (9, '0.9', '0.5.1', 'increase length of columns path and name to 255 in table images, and change length of column name to 255 in table flavors', '2017-01-10');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 474 }
 475 function downgrade_from_9(){
 476     # echo "    downgrade database from version 0.9 to version 0.8"
 477     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 478     echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER path;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 479     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 480     echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 481 }
 482
 483 function upgrade_to_10(){
 484     # echo "    upgrade database from version 0.9 to version 0.10"
 485     echo "     change types at 'ports'"
 486     echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 487     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 488 }
 489 function downgrade_from_10(){
 490     # echo "    downgrade database from version 0.10 to version 0.9"
 491     echo "     change back types at 'ports'"
 492     echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') NOT NULL DEFAULT 'instance:bridge' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 493     echo "DELETE FROM schema_version WHERE version_int = '10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 494 }
 495
 496 function upgrade_to_11(){
 497     # echo "    upgrade database from version 0.10 to version 0.11"
 498     echo "    Add gateway_ip colum to 'nets'"
 499     echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 500     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 501 }
 502 function downgrade_from_11(){
 503     # echo "    downgrade database from version 0.11 to version 0.10"
 504     echo "    Delete gateway_ip colum from 'nets'"
 505     echo "ALTER TABLE nets DROP COLUMN gateway_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 506     echo "DELETE FROM schema_version WHERE version_int = '11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 507 }
 508 function upgrade_to_12(){
 509     # echo "    upgrade database from version 0.11 to version 0.12"
 510     echo "    Create of_controller table "
 511     echo "CREATE TABLE ofcs (
 512         uuid VARCHAR(36) NOT NULL,
 513         name VARCHAR(255) NOT NULL,
 514         dpid VARCHAR(64) NOT NULL,
 515         ip VARCHAR(64) NOT NULL,
 516         port INT(5) NOT NULL,
 517         type VARCHAR(64) NOT NULL,
 518         version VARCHAR(12) NULL DEFAULT NULL,
 519         user VARCHAR(64) NULL DEFAULT NULL,
 520         password VARCHAR(64) NULL DEFAULT NULL,
 521         PRIMARY KEY (uuid)
 522 )
 523 COLLATE='utf8_general_ci'
 524 ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 525     echo "    Modify user_at for uuids table"
 526     echo "ALTER TABLE uuids  CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 527     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 528 }
 529
 530 function downgrade_from_12(){
 531     # echo "    downgrade database from version 0.12 to version 0.11"
 532     echo "    Delete ofcs table"
 533     echo "DROP TABLE ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 534     echo "ALTER TABLE uuids  CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', 'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 535     echo "DELETE FROM schema_version WHERE version_int = '12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 536 }
 537
 538 function upgrade_to_13(){
 539     # echo "    upgrade database from version 0.12 to version 0.13"
 540     echo "    Create of_port_mapings table "
 541     echo "CREATE TABLE of_port_mappings (
 542         uuid VARCHAR(36) NOT NULL,
 543         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 544         region VARCHAR(64) NULL DEFAULT NULL,
 545         compute_node VARCHAR(64) NULL DEFAULT NULL,
 546         pci VARCHAR(50) NULL DEFAULT NULL,
 547         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 548         switch_port VARCHAR(64) NULL DEFAULT NULL,
 549         switch_mac CHAR(18) NULL DEFAULT NULL,
 550         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 551         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 552         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 553         INDEX FK_of_port_mappings_ofcs (ofc_id),
 554         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 555     COLLATE='utf8_general_ci'
 556     ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 557     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 558 }
 559
 560 function downgrade_from_13(){
 561     # echo "    downgrade database from version 0.13 to version 0.12"
 562     echo "    Delete of_port_mappings table"
 563     echo "DROP TABLE of_port_mappings;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 564     echo "DELETE FROM schema_version WHERE version_int = '13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 565 }
 566
 567 function upgrade_to_14(){
 568     # echo "    upgrade database from version 0.13 to version 0.14"
 569     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 570     echo "ALTER TABLE ports
 571         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 572         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 573         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 574     echo "ALTER TABLE resources_port
 575         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 576         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 577         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 578     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 579 }
 580
 581 function downgrade_from_14(){
 582     # echo "    downgrade database from version 0.14 to version 0.13"
 583     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 584     echo "ALTER TABLE ports
 585         DROP COLUMN switch_mac,
 586         DROP COLUMN ofc_id,
 587         DROP FOREIGN KEY FK_port_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 588     echo "ALTER TABLE resources_port
 589         DROP COLUMN switch_mac,
 590         DROP COLUMN ofc_id,
 591         DROP FOREIGN KEY FK_resource_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 592     echo "DELETE FROM schema_version WHERE version_int = '14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 593 }
 594
 595 function upgrade_to_15(){
 596     # echo "    upgrade database from version 0.14 to version 0.15"
 597     echo "    Add ofc_id colum to 'of_flows'"
 598     echo "ALTER TABLE of_flows
 599         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id,
 600         ADD CONSTRAINT FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 601     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 602 }
 603
 604 function downgrade_from_15(){
 605     # echo "    downgrade database from version 0.15 to version 0.14"
 606     echo "    Delete ofc_id to 'of_flows'"
 607     echo "ALTER TABLE of_flows
 608         DROP COLUMN ofc_id,
 609         DROP FOREIGN KEY FK_of_flows_ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 610     echo "DELETE FROM schema_version WHERE version_int = '15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 611 }
 612
 613
 614 function upgrade_to_16(){
 615     # echo "    upgrade database from version 0.15 to version 0.16"
 616     echo "    Add last_error and status colum to 'ofcs'"
 617     echo "ALTER TABLE ofcs
 618         ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password,
 619         ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 620     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 621 }
 622
 623 function downgrade_from_16(){
 624     # echo "    downgrade database from version 0.16 to version 0.15"
 625     echo "    Delete last_error and status colum to 'ofcs'"
 626     echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;  " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 627     echo "DELETE FROM schema_version WHERE version_int = '16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 628 }
 629
 630 function upgrade_to_17(){
 631     # echo "    upgrade database from version 0.16 to version 0.17"
 632     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 633     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 634           "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 635     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 636          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 637     echo "    Add nets_with_same_vlan to table ofcs"
 638     echo "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 639     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 640 }
 641
 642 function downgrade_from_17(){
 643     # echo "    downgrade database from version 0.17 to version 0.16"
 644     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 645     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 646          "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 647     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 648          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 649     echo "    Remove nets_with_same_vlan from table ofcs"
 650     echo "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 651     echo "DELETE FROM schema_version WHERE version_int = '17';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 652 }
 653
 654 function upgrade_to_18(){
 655     echo "    Add 'region' at 'nets' and change unique index vlan+region"
 656     echo "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, " \
 657             "DROP INDEX type_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 658     echo "    Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
 659     echo "UPDATE nets set region='__OVS__' where provider like 'OVS%';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 660     echo "UPDATE nets set region='__DATA__' where type='data' or type='ptp';" | $DBCMD || ! echo "ERROR. Aborted!" ||
 661          exit -1
 662     echo "    Create new index region_vlan at nets"
 663         echo "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);" \
 664              | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 665     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 666             "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"\
 667          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 668 }
 669
 670 function downgrade_from_18(){
 671     echo "    Delete 'region' at 'nets' and change back unique index vlan+type"
 672     echo "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 673     echo "    Create back index type_vlan at nets"
 674         echo "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 675     echo "DELETE FROM schema_version WHERE version_int = '18';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 676 }
 677
 678 function upgrade_to_19(){
 679     echo "    Add 'keyfile' to 'hosts'"
 680     echo "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;" \
 681             | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 682     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 683             "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"\
 684          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 685 }
 686
 687 function downgrade_from_19(){
 688     echo "    Delete 'keyfile' from 'hosts'"
 689     echo "ALTER TABLE hosts DROP COLUMN keyfile;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 690     echo "DELETE FROM schema_version WHERE version_int = '19';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 691 }
 692
 693 function upgrade_to_20(){
 694     echo "    Add 'image_size' to 'instance_devices'"
 695     echo "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 696     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"\
 697          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 698
 699
 700 }
 701
 702 function downgrade_from_20(){
 703     echo "    Delete 'image_size' from 'instance_devices'"
 704     echo "ALTER TABLE instance_devices DROP COLUMN image_size;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 705     echo "DELETE FROM schema_version WHERE version_int = '20';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 706 }
 707
 708 function upgrade_to_21(){
 709     echo "    Add 'routes', 'links' and 'dns' to 'nets'"
 710     echo "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip,
 711     ADD COLUMN links TEXT(2000)  NULL AFTER dns,
 712     ADD COLUMN routes TEXT(2000)  NULL AFTER links;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 713     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"\
 714          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 715 }
 716
 717 function downgrade_from_21(){
 718     echo "    Delete 'routes', 'links' and 'dns' to 'nets'"
 719     echo "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 720     echo "DELETE FROM schema_version WHERE version_int = '21';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 721 }
 722
 723 function upgrade_to_22(){
 724     echo "    Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
 725     echo "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 726     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"\
 727          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 728 }
 729
 730 function downgrade_from_22(){
 731     echo "    Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
 732     echo "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 733     echo "DELETE FROM schema_version WHERE version_int = '22';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 734 }
 735
 736
 737 #TODO ... put funtions here
 738
 739 # echo "db version = "${DATABASE_VER_NUM}
 740 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo "    current database version '$DATABASE_VER_NUM' is ok"
 741 #UPGRADE DATABASE step by step
 742 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 743 do
 744     echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 745     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 746     upgrade_to_${DATABASE_VER_NUM}
 747     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 748     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 749     #$FILE_ || exit -1  # if fail return
 750 done
 751
 752 #DOWNGRADE DATABASE step by step
 753 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 754 do
 755     echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 756     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 757     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 758     #$FILE_ || exit -1  # if fail return
 759     downgrade_from_${DATABASE_VER_NUM}
 760     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 761 done
 762
 763 #echo done
 764