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=20
  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_NUM20   #0.5.17  => 20
 191 # TODO ... put next versions here
 192
 193 function upgrade_to_1(){
 194     # echo "    upgrade database from version 0.0 to version 0.1"
 195     echo "      CREATE TABLE \`schema_version\`"
 196     echo "CREATE TABLE \`schema_version\` (
 197         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 198         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 199         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 200         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 201         \`date\` DATE NULL,
 202         PRIMARY KEY (\`version_int\`)
 203         )
 204         COMMENT='database schema control version'
 205         COLLATE='utf8_general_ci'
 206         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 207     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 208          VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
 209     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 210     echo "ALTER TABLE \`nets\`
 211          ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 212 }
 213 function downgrade_from_1(){
 214     # echo "    downgrade database from version 0.1 to version 0.0"
 215     echo "      ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
 216     echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 217     echo "      DROP TABLE \`schema_version\`"
 218     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 219 }
 220 function upgrade_to_2(){
 221     # echo "    upgrade database from version 0.1 to version 0.2"
 222     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
 223     for table in of_ports_pci_correspondence resources_port ports
 224     do
 225         echo "ALTER TABLE \`${table}\`
 226             ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 227         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 228         [ $table == of_ports_pci_correspondence ] ||
 229             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
 230     done
 231     echo "      UPDATE procedure UpdateSwitchPort"
 232     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 233     delimiter //
 234     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 235     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 236     BEGIN
 237         #DELETES switch_port entry before writing, because if not it fails for key constrains
 238         UPDATE ports
 239         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 240         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 241         INNER JOIN numas on RP.numa_id=numas.id
 242         INNER JOIN hosts on numas.host_id=hosts.uuid
 243         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 244         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 245         #write switch_port into resources_port and ports
 246         UPDATE ports
 247         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 248         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 249         INNER JOIN numas on RP.numa_id=numas.id
 250         INNER JOIN hosts on numas.host_id=hosts.uuid
 251         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 252         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;
 253     END//
 254     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 255     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 256          VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 257 }
 258 function upgrade_to_3(){
 259     # echo "    upgrade database from version 0.2 to version 0.3"
 260     echo "     change size of source_name at table resources_port"
 261     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
 262     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 263     echo "delimiter //
 264     CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 265     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 266     BEGIN
 267         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 268         FROM
 269         (
 270            SELECT id as port_id, Mbps, pci, switch_port, mac
 271            FROM resources_port
 272                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 273         ) as A
 274         INNER JOIN
 275         (
 276            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 277                 FROM resources_port
 278                 WHERE numa_id = Numa AND status = 'ok'
 279                 GROUP BY root_id
 280         ) as B
 281         ON A.port_id = B.root_id
 282         LEFT JOIN
 283         (
 284            SELECT root_id,  COUNT(id) as usedSRIOV
 285                 FROM resources_port
 286                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 287                 GROUP BY root_id
 288         ) as C
 289         ON A.port_id = C.root_id
 290         ORDER BY Mbps_free, availableSRIOV, pci;
 291     END//
 292     delimiter ;"| $DBCMD || !  ! echo "ERROR. Aborted!" || exit -1
 293     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
 294 }
 295
 296 function upgrade_to_4(){
 297     # echo "    upgrade database from version 0.3 to version 0.4"
 298     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 299     echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 300     echo "ALTER TABLE ports          DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 301     echo "     change table 'ports'"
 302     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
 303     echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 304     echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 305     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
 306 }
 307
 308 function upgrade_to_X(){
 309     #TODO, this change of foreign key does not work
 310     # echo "    upgrade database from version 0.X to version 0.X"
 311     echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
 312           DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 313     echo "ALTER TABLE instances
 314         ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
 315         ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 316 }
 317
 318 function downgrade_from_2(){
 319     # echo "    downgrade database from version 0.2 to version 0.1"
 320     echo "      UPDATE procedure UpdateSwitchPort"
 321     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 322     delimiter //
 323     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 324     BEGIN
 325     UPDATE
 326         resources_port INNER JOIN (
 327             SELECT resources_port.id,KK.switch_port
 328             FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 329                 INNER JOIN hosts on numas.host_id=hosts.uuid
 330                 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 331             ) as TABLA
 332         ON  resources_port.root_id=TABLA.id
 333     SET resources_port.switch_port=TABLA.switch_port
 334     WHERE resources_port.root_id=TABLA.id;
 335     END//
 336     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 337     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 338     for table in of_ports_pci_correspondence resources_port ports
 339     do
 340         [ $table == of_ports_pci_correspondence ] ||
 341             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 342         echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 343         switch_port_size=12
 344         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 345         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 346     done
 347     echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 348 }
 349 function downgrade_from_3(){
 350     # echo "    downgrade database from version 0.3 to version 0.2"
 351     echo "     change back size of source_name at table resources_port"
 352     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
 353     echo "      DROP PROCEDURE GetAllAvailablePorts"
 354     echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 355     echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 356 }
 357 function downgrade_from_4(){
 358     # echo "    downgrade database from version 0.4 to version 0.3"
 359     echo "     adding back unique index VLAN at 'resources_port','ports'"
 360     echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 361     echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 362     echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 363     echo "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 364     echo "     change back table 'ports'"
 365     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 366     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
 367     echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 368 }
 369
 370
 371 function upgrade_to_5(){
 372     # echo "    upgrade database from version 0.4 to version 0.5"
 373     echo "     add 'ip_address' to ports'"
 374     echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 375     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
 376 }
 377 function downgrade_from_5(){
 378     # echo "    downgrade database from version 0.5 to version 0.4"
 379     echo "     removing 'ip_address' from 'ports'"
 380     echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 381     echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 382 }
 383
 384 function upgrade_to_6(){
 385     # echo "    upgrade database from version 0.5 to version 0.6"
 386     echo "      Change enalarge name, description to 255 at all database"
 387     for table in flavors images instances tenants
 388     do
 389          name_length=255
 390          [[ $table == tenants ]] || name_length=64
 391          echo -en "        $table               \r"
 392          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
 393     done
 394     echo -en "        hosts               \r"
 395     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
 396     echo -en "        nets                \r"
 397     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
 398     echo -en "        instances           \r"
 399     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 400     echo -en "        ports               \r"
 401     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
 402     echo -en "        of_flows            \r"
 403     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
 404     echo -en "        of_ports_pci_cor... \r"
 405     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
 406     echo -en "        resources_port      \r"
 407     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
 408     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
 409 }
 410 function downgrade_from_6(){
 411     # echo "    downgrade database from version 0.6 to version 0.5"
 412     echo "      Change back name,description to shorter length at all database"
 413     for table in flavors images instances tenants
 414     do
 415          name_length=50
 416          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 417          echo -en "        $table               \r"
 418          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
 419     done
 420     echo -en "        hosts               \r"
 421     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
 422     echo -en "        nets                \r"
 423     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
 424     echo -en "        instances           \r"
 425     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 426     echo -en "        ports               \r"
 427     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
 428     echo -en "        of_flows            \r"
 429     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
 430     echo -en "        of_ports_pci_cor... \r"
 431     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
 432     echo -en "        resources_port      \r"
 433     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
 434     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 435 }
 436
 437 function upgrade_to_7(){
 438     # echo "    upgrade database from version 0.6 to version 0.7"
 439     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 440     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
 441     echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 442     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
 443     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
 444 }
 445 function downgrade_from_7(){
 446     # echo "    downgrade database from version 0.7 to version 0.6"
 447     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 448     echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 449     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
 450     echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 451 }
 452
 453 function upgrade_to_8(){
 454     # echo "    upgrade database from version 0.7 to version 0.8"
 455     echo "     add column 'checksum' to 'images'"
 456     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 457     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
 458 }
 459 function downgrade_from_8(){
 460     # echo "    downgrade database from version 0.8 to version 0.7"
 461     echo "     remove column 'checksum' from 'images'"
 462     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 463     echo "DELETE FROM schema_version WHERE version_int = '8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 464 }
 465
 466 function upgrade_to_9(){
 467     # echo "    upgrade database from version 0.8 to version 0.9"
 468     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
 469     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
 470     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 471     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
 472 }
 473 function downgrade_from_9(){
 474     # echo "    downgrade database from version 0.9 to version 0.8"
 475     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 476     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
 477     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 478     echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 479 }
 480
 481 function upgrade_to_10(){
 482     # echo "    upgrade database from version 0.9 to version 0.10"
 483     echo "     change types at 'ports'"
 484     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
 485     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
 486 }
 487 function downgrade_from_10(){
 488     # echo "    downgrade database from version 0.10 to version 0.9"
 489     echo "     change back types at 'ports'"
 490     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
 491     echo "DELETE FROM schema_version WHERE version_int = '10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 492 }
 493
 494 function upgrade_to_11(){
 495     # echo "    upgrade database from version 0.10 to version 0.11"
 496     echo "    Add gateway_ip colum to 'nets'"
 497     echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 498     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
 499 }
 500 function downgrade_from_11(){
 501     # echo "    downgrade database from version 0.11 to version 0.10"
 502     echo "    Delete gateway_ip colum from 'nets'"
 503     echo "ALTER TABLE nets DROP COLUMN gateway_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 504     echo "DELETE FROM schema_version WHERE version_int = '11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 505 }
 506 function upgrade_to_12(){
 507     # echo "    upgrade database from version 0.11 to version 0.12"
 508     echo "    Create of_controller table "
 509     echo "CREATE TABLE ofcs (
 510         uuid VARCHAR(36) NOT NULL,
 511         name VARCHAR(255) NOT NULL,
 512         dpid VARCHAR(64) NOT NULL,
 513         ip VARCHAR(64) NOT NULL,
 514         port INT(5) NOT NULL,
 515         type VARCHAR(64) NOT NULL,
 516         version VARCHAR(12) NULL DEFAULT NULL,
 517         user VARCHAR(64) NULL DEFAULT NULL,
 518         password VARCHAR(64) NULL DEFAULT NULL,
 519         PRIMARY KEY (uuid)
 520 )
 521 COLLATE='utf8_general_ci'
 522 ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 523     echo "    Modify user_at for uuids table"
 524     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
 525     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
 526 }
 527
 528 function downgrade_from_12(){
 529     # echo "    downgrade database from version 0.12 to version 0.11"
 530     echo "    Delete ofcs table"
 531     echo "DROP TABLE ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 532     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
 533     echo "DELETE FROM schema_version WHERE version_int = '12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 534 }
 535
 536 function upgrade_to_13(){
 537     # echo "    upgrade database from version 0.12 to version 0.13"
 538     echo "    Create of_port_mapings table "
 539     echo "CREATE TABLE of_port_mappings (
 540         uuid VARCHAR(36) NOT NULL,
 541         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 542         region VARCHAR(64) NULL DEFAULT NULL,
 543         compute_node VARCHAR(64) NULL DEFAULT NULL,
 544         pci VARCHAR(50) NULL DEFAULT NULL,
 545         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 546         switch_port VARCHAR(64) NULL DEFAULT NULL,
 547         switch_mac CHAR(18) NULL DEFAULT NULL,
 548         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 549         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 550         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 551         INDEX FK_of_port_mappings_ofcs (ofc_id),
 552         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 553     COLLATE='utf8_general_ci'
 554     ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 555     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
 556 }
 557
 558 function downgrade_from_13(){
 559     # echo "    downgrade database from version 0.13 to version 0.12"
 560     echo "    Delete of_port_mappings table"
 561     echo "DROP TABLE of_port_mappings;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 562     echo "DELETE FROM schema_version WHERE version_int = '13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 563 }
 564
 565 function upgrade_to_14(){
 566     # echo "    upgrade database from version 0.13 to version 0.14"
 567     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 568     echo "ALTER TABLE ports
 569         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 570         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 571         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 572     echo "ALTER TABLE resources_port
 573         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 574         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 575         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 576     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
 577 }
 578
 579 function downgrade_from_14(){
 580     # echo "    downgrade database from version 0.14 to version 0.13"
 581     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 582     echo "ALTER TABLE ports
 583         DROP COLUMN switch_mac,
 584         DROP COLUMN ofc_id,
 585         DROP FOREIGN KEY FK_port_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 586     echo "ALTER TABLE resources_port
 587         DROP COLUMN switch_mac,
 588         DROP COLUMN ofc_id,
 589         DROP FOREIGN KEY FK_resource_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 590     echo "DELETE FROM schema_version WHERE version_int = '14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 591 }
 592
 593 function upgrade_to_15(){
 594     # echo "    upgrade database from version 0.14 to version 0.15"
 595     echo "    Add ofc_id colum to 'of_flows'"
 596     echo "ALTER TABLE of_flows
 597         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id,
 598         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
 599     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
 600 }
 601
 602 function downgrade_from_15(){
 603     # echo "    downgrade database from version 0.15 to version 0.14"
 604     echo "    Delete ofc_id to 'of_flows'"
 605     echo "ALTER TABLE of_flows
 606         DROP COLUMN ofc_id,
 607         DROP FOREIGN KEY FK_of_flows_ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 608     echo "DELETE FROM schema_version WHERE version_int = '15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 609 }
 610
 611
 612 function upgrade_to_16(){
 613     # echo "    upgrade database from version 0.15 to version 0.16"
 614     echo "    Add last_error and status colum to 'ofcs'"
 615     echo "ALTER TABLE ofcs
 616         ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password,
 617         ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 618     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
 619 }
 620
 621 function downgrade_from_16(){
 622     # echo "    downgrade database from version 0.16 to version 0.15"
 623     echo "    Delete last_error and status colum to 'ofcs'"
 624     echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;  " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 625     echo "DELETE FROM schema_version WHERE version_int = '16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 626 }
 627
 628 function upgrade_to_17(){
 629     # echo "    upgrade database from version 0.16 to version 0.17"
 630     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 631     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 632           "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 633     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 634          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 635     echo "    Add nets_with_same_vlan to table ofcs"
 636     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
 637     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
 638 }
 639
 640 function downgrade_from_17(){
 641     # echo "    downgrade database from version 0.17 to version 0.16"
 642     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 643     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 644          "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 645     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 646          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 647     echo "    Remove nets_with_same_vlan from table ofcs"
 648     echo "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 649     echo "DELETE FROM schema_version WHERE version_int = '17';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 650 }
 651
 652 function upgrade_to_18(){
 653     echo "    Add 'region' at 'nets' and change unique index vlan+region"
 654     echo "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, " \
 655             "DROP INDEX type_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 656     echo "    Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
 657     echo "UPDATE nets set region='__OVS__' where provider like 'OVS%';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 658     echo "UPDATE nets set region='__DATA__' where type='data' or type='ptp';" | $DBCMD || ! echo "ERROR. Aborted!" ||
 659          exit -1
 660     echo "    Create new index region_vlan at nets"
 661         echo "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);" \
 662              | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 663     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 664             "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"\
 665          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 666 }
 667
 668 function downgrade_from_18(){
 669     echo "    Delete 'region' at 'nets' and change back unique index vlan+type"
 670     echo "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 671     echo "    Create back index type_vlan at nets"
 672         echo "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 673     echo "DELETE FROM schema_version WHERE version_int = '18';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 674 }
 675
 676 function upgrade_to_19(){
 677     echo "    Add 'keyfile' to 'hosts'"
 678     echo "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;" \
 679             | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 680     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 681             "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"\
 682          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 683 }
 684
 685 function downgrade_from_19(){
 686     echo "    Delete 'keyfile' from 'hosts'"
 687     echo "ALTER TABLE hosts DROP COLUMN keyfile;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 688     echo "DELETE FROM schema_version WHERE version_int = '19';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 689 }
 690
 691 function upgrade_to_20(){
 692     echo "    Add 'image_size' to 'instance_devices'"
 693     echo "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 694     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');"\
 695          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 696
 697
 698 }
 699
 700 function downgrade_from_20(){
 701     echo "    Delete 'image_size' from 'instance_devices'"
 702     echo "ALTER TABLE instance_devices DROP COLUMN image_size;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 703     echo "DELETE FROM schema_version WHERE version_int = '20';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 704 }
 705
 706 #TODO ... put funtions here
 707
 708 # echo "db version = "${DATABASE_VER_NUM}
 709 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo "    current database version '$DATABASE_VER_NUM' is ok"
 710 #UPGRADE DATABASE step by step
 711 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 712 do
 713     echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 714     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 715     upgrade_to_${DATABASE_VER_NUM}
 716     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 717     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 718     #$FILE_ || exit -1  # if fail return
 719 done
 720
 721 #DOWNGRADE DATABASE step by step
 722 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 723 do
 724     echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 725     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 726     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 727     #$FILE_ || exit -1  # if fail return
 728     downgrade_from_${DATABASE_VER_NUM}
 729     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 730 done
 731
 732 #echo done
 733