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 DBHOST="localhost"
  31 DBPORT="3306"
  32 DBNAME="vim_db"
  33
  34 # Detect paths
  35 MYSQL=$(which mysql)
  36 AWK=$(which awk)
  37 GREP=$(which grep)
  38 DIRNAME=`dirname $0`
  39
  40 function usage(){
  41     echo -e "Usage: $0 OPTIONS  [{openvim_version}]"
  42     echo -e "  Upgrades/Downgrades openvim database preserving the content"
  43     echo -e "   if openvim_version is not provided it tries to get from openvimd.py using relative path"
  44     echo -e "  OPTIONS"
  45     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  46     echo -e "     -p PASS  database password. 'No password' or 'vimpw' by default. Prompts if DB access fails"
  47     echo -e "     -P PORT  database port. '$DBPORT' by default"
  48     echo -e "     -h HOST  database host. '$DBHOST' by default"
  49     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  50     echo -e "     --help   shows this help"
  51 }
  52
  53 while getopts ":u:p:P:h:d:-:" o; do
  54     case "${o}" in
  55         u)
  56             DBUSER="$OPTARG"
  57             ;;
  58         p)
  59             DBPASS="$OPTARG"
  60             ;;
  61         P)
  62             DBPORT="$OPTARG"
  63             ;;
  64         d)
  65             DBNAME="$OPTARG"
  66             ;;
  67         h)
  68             DBHOST="$OPTARG"
  69             ;;
  70         -)
  71             [ "${OPTARG}" == "help" ] && usage && exit 0
  72             echo "Invalid option: --$OPTARG" >&2 && usage  >&2
  73             exit 1
  74             ;;
  75         \?)
  76             echo "Invalid option: -$OPTARG" >&2 && usage  >&2
  77             exit 1
  78             ;;
  79         :)
  80             echo "Option -$OPTARG requires an argument." >&2 && usage  >&2
  81             exit 1
  82             ;;
  83         *)
  84             usage >&2
  85             exit -1
  86             ;;
  87     esac
  88 done
  89 shift $((OPTIND-1))
  90
  91
  92 #GET OPENVIM VERSION
  93 OPENVIM_VER="$1"
  94 if [ -z "$OPENVIM_VER" ]
  95 then
  96     OPENVIM_VER=`ovim -v`
  97     OPENVIM_VER=${OPENVIM_VER%%-r*}
  98     OPENVIM_VER=${OPENVIM_VER##*version }
  99     echo "    Detected openvim version $OPENVIM_VER"
 100 fi
 101 VERSION_1=`echo $OPENVIM_VER | cut -f 1 -d"."`
 102 VERSION_2=`echo $OPENVIM_VER | cut -f 2 -d"."`
 103 VERSION_3=`echo $OPENVIM_VER | cut -f 3 -d"."`
 104 if ! [ "$VERSION_1" -ge 0 -a "$VERSION_2" -ge 0 -a "$VERSION_3" -ge 0 ] 2>/dev/null
 105 then
 106     [ -n "$1" ] && echo "Invalid openvim version '$1', expected 'X.X.X'" >&2
 107     [ -z "$1" ] && echo "Can not get openvim version" >&2
 108     exit -1
 109 fi
 110 OPENVIM_VER_NUM=`printf "%d%03d%03d" ${VERSION_1} ${VERSION_2} ${VERSION_3}`
 111
 112 #Creating temporary file
 113 TEMPFILE="$(mktemp -q --tmpdir "migratemanodb.XXXXXX")"
 114 trap 'rm -f "$TEMPFILE"' EXIT
 115 chmod 0600 "$TEMPFILE"
 116
 117 #if password is missing, before prompting for it try without password and with "manopw"
 118 DBHOST_="-h$DBHOST"
 119 DBPORT_="-P$DBPORT"
 120 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 121 if [ -z "${DBPASS}" ]
 122 then
 123     password_ok=""
 124     echo -e "[client]\nuser='${DBUSER}'\npassword='vimpw'" > "$TEMPFILE"
 125     mysql --defaults-extra-file="$TEMPFILE" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev/null 2>&1 && DBPASS="vimpw"
 126     echo -e "[client]\nuser='${DBUSER}'\npassword=''" > "$TEMPFILE"
 127     mysql --defaults-extra-file="$TEMPFILE" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev/null 2>&1 && DBPASS=""
 128 fi
 129 echo -e "[client]\nuser='${DBUSER}'\npassword='${DBPASS}'" > "$TEMPFILE"
 130
 131 #check and ask for database user password
 132 while ! mysql "$DEF_EXTRA_FILE_PARAM" $DBHOST_ $DBPORT_ $DBNAME -e "quit" >/dev/null 2>&1
 133 do
 134         [ -n "$logintry" ] && echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
 135         [ -z "$logintry" ] &&  echo -e "\nProvide database name and credentials"
 136         read -e -p "mysql database name($DBNAME): " KK
 137         [ -n "$KK" ] && DBNAME="$KK"
 138         read -e -p "mysql user($DBUSER): " KK
 139         [ -n "$KK" ] && DBUSER="$KK"
 140         read -e -s -p "mysql password: " DBPASS
 141         echo -e "[client]\nuser='${DBUSER}'\npassword='${DBPASS}'" > "$TEMPFILE"
 142         logintry="yes"
 143         echo
 144 done
 145
 146 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBHOST_ $DBPORT_ $DBNAME"
 147 #echo DBCMD $DBCMD
 148
 149 #GET DATABASE VERSION
 150 #check that the database seems a openvim database
 151 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
 152 then
 153     echo "    database $DBNAME does not seem to be an openvim database" >&2
 154     exit -1;
 155 fi
 156
 157 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 158 then
 159     DATABASE_VER="0.0"
 160     DATABASE_VER_NUM=0
 161 else
 162     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 163     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 164     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 165     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 166 fi
 167
 168
 169 #GET DATABASE TARGET VERSION
 170 DATABASE_TARGET_VER_NUM=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 #TODO ... put next versions here
 189
 190 function upgrade_to_1(){
 191     echo "    upgrade database from version 0.0 to version 0.1"
 192     echo "      CREATE TABLE \`schema_version\`"
 193     echo "CREATE TABLE \`schema_version\` (
 194         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 195         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 196         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 197         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 198         \`date\` DATE NULL,
 199         PRIMARY KEY (\`version_int\`)
 200         )
 201         COMMENT='database schema control version'
 202         COLLATE='utf8_general_ci'
 203         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 204     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 205          VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
 206     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 207     echo "ALTER TABLE \`nets\`
 208          ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 209 }
 210 function downgrade_from_1(){
 211     echo "    downgrade database from version 0.1 to version 0.0"
 212     echo "      ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
 213     echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 214     echo "      DROP TABLE \`schema_version\`"
 215     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 216 }
 217 function upgrade_to_2(){
 218     echo "    upgrade database from version 0.1 to version 0.2"
 219     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
 220     for table in of_ports_pci_correspondence resources_port ports
 221     do
 222         echo "ALTER TABLE \`${table}\`
 223             ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 224         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 225         [ $table == of_ports_pci_correspondence ] ||
 226             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
 227     done
 228     echo "      UPDATE procedure UpdateSwitchPort"
 229     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 230     delimiter //
 231     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 232     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 233     BEGIN
 234         #DELETES switch_port entry before writing, because if not it fails for key constrains
 235         UPDATE ports
 236         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 237         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 238         INNER JOIN numas on RP.numa_id=numas.id
 239         INNER JOIN hosts on numas.host_id=hosts.uuid
 240         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 241         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 242         #write switch_port into resources_port and ports
 243         UPDATE ports
 244         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 245         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 246         INNER JOIN numas on RP.numa_id=numas.id
 247         INNER JOIN hosts on numas.host_id=hosts.uuid
 248         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 249         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;
 250     END//
 251     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 252     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 253          VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 254 }
 255 function upgrade_to_3(){
 256     echo "    upgrade database from version 0.2 to version 0.3"
 257     echo "     change size of source_name at table resources_port"
 258     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
 259     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 260     echo "delimiter //
 261     CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 262     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 263     BEGIN
 264         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 265         FROM
 266         (
 267            SELECT id as port_id, Mbps, pci, switch_port, mac
 268            FROM resources_port
 269                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 270         ) as A
 271         INNER JOIN
 272         (
 273            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 274                 FROM resources_port
 275                 WHERE numa_id = Numa AND status = 'ok'
 276                 GROUP BY root_id
 277         ) as B
 278         ON A.port_id = B.root_id
 279         LEFT JOIN
 280         (
 281            SELECT root_id,  COUNT(id) as usedSRIOV
 282                 FROM resources_port
 283                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 284                 GROUP BY root_id
 285         ) as C
 286         ON A.port_id = C.root_id
 287         ORDER BY Mbps_free, availableSRIOV, pci;
 288     END//
 289     delimiter ;"| $DBCMD || !  ! echo "ERROR. Aborted!" || exit -1
 290     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
 291 }
 292
 293 function upgrade_to_4(){
 294     echo "    upgrade database from version 0.3 to version 0.4"
 295     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 296     echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 297     echo "ALTER TABLE ports          DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 298     echo "     change table 'ports'"
 299     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
 300     echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 301     echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 302     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
 303 }
 304
 305 function upgrade_to_X(){
 306     #TODO, this change of foreign key does not work
 307     echo "    upgrade database from version 0.X to version 0.X"
 308     echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
 309           DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 310     echo "ALTER TABLE instances
 311         ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
 312         ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 313 }
 314
 315 function downgrade_from_2(){
 316     echo "    downgrade database from version 0.2 to version 0.1"
 317     echo "      UPDATE procedure UpdateSwitchPort"
 318     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 319     delimiter //
 320     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 321     BEGIN
 322     UPDATE
 323         resources_port INNER JOIN (
 324             SELECT resources_port.id,KK.switch_port
 325             FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 326                 INNER JOIN hosts on numas.host_id=hosts.uuid
 327                 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 328             ) as TABLA
 329         ON  resources_port.root_id=TABLA.id
 330     SET resources_port.switch_port=TABLA.switch_port
 331     WHERE resources_port.root_id=TABLA.id;
 332     END//
 333     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 334     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 335     for table in of_ports_pci_correspondence resources_port ports
 336     do
 337         [ $table == of_ports_pci_correspondence ] ||
 338             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 339         echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 340         switch_port_size=12
 341         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 342         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 343     done
 344     echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 345 }
 346 function downgrade_from_3(){
 347     echo "    downgrade database from version 0.3 to version 0.2"
 348     echo "     change back size of source_name at table resources_port"
 349     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
 350     echo "      DROP PROCEDURE GetAllAvailablePorts"
 351     echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 352     echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 353 }
 354 function downgrade_from_4(){
 355     echo "    downgrade database from version 0.4 to version 0.3"
 356     echo "     adding back unique index VLAN at 'resources_port','ports'"
 357     echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 358     echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 359     echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 360     echo "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 361     echo "     change back table 'ports'"
 362     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 363     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
 364     echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 365 }
 366
 367
 368 function upgrade_to_5(){
 369     echo "    upgrade database from version 0.4 to version 0.5"
 370     echo "     add 'ip_address' to ports'"
 371     echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 372     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
 373 }
 374 function downgrade_from_5(){
 375     echo "    downgrade database from version 0.5 to version 0.4"
 376     echo "     removing 'ip_address' from 'ports'"
 377     echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 378     echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 379 }
 380
 381 function upgrade_to_6(){
 382     echo "    upgrade database from version 0.5 to version 0.6"
 383     echo "      Change enalarge name, description to 255 at all database"
 384     for table in flavors images instances tenants
 385     do
 386          name_length=255
 387          [[ $table == tenants ]] || name_length=64
 388          echo -en "        $table               \r"
 389          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
 390     done
 391     echo -en "        hosts               \r"
 392     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
 393     echo -en "        nets                \r"
 394     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
 395     echo -en "        instances           \r"
 396     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 397     echo -en "        ports               \r"
 398     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
 399     echo -en "        of_flows            \r"
 400     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
 401     echo -en "        of_ports_pci_cor... \r"
 402     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
 403     echo -en "        resources_port      \r"
 404     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
 405     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
 406 }
 407 function downgrade_from_6(){
 408     echo "    downgrade database from version 0.6 to version 0.5"
 409     echo "      Change back name,description to shorter length at all database"
 410     for table in flavors images instances tenants
 411     do
 412          name_length=50
 413          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 414          echo -en "        $table               \r"
 415          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
 416     done
 417     echo -en "        hosts               \r"
 418     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
 419     echo -en "        nets                \r"
 420     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
 421     echo -en "        instances           \r"
 422     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 423     echo -en "        ports               \r"
 424     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
 425     echo -en "        of_flows            \r"
 426     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
 427     echo -en "        of_ports_pci_cor... \r"
 428     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
 429     echo -en "        resources_port      \r"
 430     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
 431     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 432 }
 433
 434 function upgrade_to_7(){
 435     echo "    upgrade database from version 0.6 to version 0.7"
 436     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 437     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
 438     echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 439     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
 440     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
 441 }
 442 function downgrade_from_7(){
 443     echo "    downgrade database from version 0.7 to version 0.6"
 444     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 445     echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 446     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
 447     echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 448 }
 449
 450 function upgrade_to_8(){
 451     echo "    upgrade database from version 0.7 to version 0.8"
 452     echo "     add column 'checksum' to 'images'"
 453     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 454     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
 455 }
 456 function downgrade_from_8(){
 457     echo "    downgrade database from version 0.8 to version 0.7"
 458     echo "     remove column 'checksum' from 'images'"
 459     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 460     echo "DELETE FROM schema_version WHERE version_int = '8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 461 }
 462
 463 function upgrade_to_9(){
 464     echo "    upgrade database from version 0.8 to version 0.9"
 465     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
 466     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
 467     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 468     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
 469 }
 470 function downgrade_from_9(){
 471     echo "    downgrade database from version 0.9 to version 0.8"
 472     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 473     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
 474     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 475     echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 476 }
 477
 478 function upgrade_to_10(){
 479     echo "    upgrade database from version 0.9 to version 0.10"
 480     echo "     change types at 'ports'"
 481     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
 482     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
 483 }
 484 function downgrade_from_10(){
 485     echo "    downgrade database from version 0.10 to version 0.9"
 486     echo "     change back types at 'ports'"
 487     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
 488     echo "DELETE FROM schema_version WHERE version_int = '10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 489 }
 490
 491 function upgrade_to_11(){
 492     echo "    upgrade database from version 0.10 to version 0.11"
 493     echo "    Add gateway_ip colum to 'nets'"
 494     echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 495     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
 496 }
 497 function downgrade_from_11(){
 498     echo "    downgrade database from version 0.11 to version 0.10"
 499     echo "    Delete gateway_ip colum from 'nets'"
 500     echo "ALTER TABLE nets DROP COLUMN gateway_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 501     echo "DELETE FROM schema_version WHERE version_int = '11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 502 }
 503 function upgrade_to_12(){
 504     echo "    upgrade database from version 0.11 to version 0.12"
 505     echo "    Create of_controller table "
 506     echo "CREATE TABLE ofcs (
 507         uuid VARCHAR(36) NOT NULL,
 508         name VARCHAR(255) NOT NULL,
 509         dpid VARCHAR(64) NOT NULL,
 510         ip VARCHAR(64) NOT NULL,
 511         port INT(5) NOT NULL,
 512         type VARCHAR(64) NOT NULL,
 513         version VARCHAR(12) NULL DEFAULT NULL,
 514         user VARCHAR(64) NULL DEFAULT NULL,
 515         password VARCHAR(64) NULL DEFAULT NULL,
 516         PRIMARY KEY (uuid)
 517 )
 518 COLLATE='utf8_general_ci'
 519 ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 520     echo "    Modify user_at for uuids table"
 521     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
 522     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
 523 }
 524
 525 function downgrade_from_12(){
 526     echo "    downgrade database from version 0.12 to version 0.11"
 527     echo "    Delete ofcs table"
 528     echo "DROP TABLE ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 529     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
 530     echo "DELETE FROM schema_version WHERE version_int = '12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 531 }
 532
 533 function upgrade_to_13(){
 534     echo "    upgrade database from version 0.12 to version 0.13"
 535     echo "    Create of_port_mapings table "
 536     echo "CREATE TABLE of_port_mappings (
 537         uuid VARCHAR(36) NOT NULL,
 538         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 539         region VARCHAR(64) NULL DEFAULT NULL,
 540         compute_node VARCHAR(64) NULL DEFAULT NULL,
 541         pci VARCHAR(50) NULL DEFAULT NULL,
 542         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 543         switch_port VARCHAR(64) NULL DEFAULT NULL,
 544         switch_mac CHAR(18) NULL DEFAULT NULL,
 545         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 546         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 547         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 548         INDEX FK_of_port_mappings_ofcs (ofc_id),
 549         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 550     COLLATE='utf8_general_ci'
 551     ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 552     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
 553 }
 554
 555 function downgrade_from_13(){
 556     echo "    downgrade database from version 0.13 to version 0.12"
 557     echo "    Delete of_port_mappings table"
 558     echo "DROP TABLE of_port_mappings;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 559     echo "DELETE FROM schema_version WHERE version_int = '13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 560 }
 561
 562 function upgrade_to_14(){
 563     echo "    upgrade database from version 0.13 to version 0.14"
 564     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 565     echo "ALTER TABLE ports
 566         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 567         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 568         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 569     echo "ALTER TABLE resources_port
 570         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 571         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 572         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 573     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
 574 }
 575
 576 function downgrade_from_14(){
 577     echo "    downgrade database from version 0.14 to version 0.13"
 578     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 579     echo "ALTER TABLE ports
 580         DROP COLUMN switch_mac,
 581         DROP COLUMN ofc_id,
 582         DROP FOREIGN KEY FK_port_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 583     echo "ALTER TABLE resources_port
 584         DROP COLUMN switch_mac,
 585         DROP COLUMN ofc_id,
 586         DROP FOREIGN KEY FK_resource_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 587     echo "DELETE FROM schema_version WHERE version_int = '14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 588 }
 589
 590 function upgrade_to_15(){
 591     echo "    upgrade database from version 0.14 to version 0.15"
 592     echo "    Add ofc_id colum to 'of_flows'"
 593     echo "ALTER TABLE of_flows
 594         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id,
 595         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
 596     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
 597 }
 598
 599 function downgrade_from_15(){
 600     echo "    downgrade database from version 0.15 to version 0.14"
 601     echo "    Delete ofc_id to 'of_flows'"
 602     echo "ALTER TABLE of_flows
 603         DROP COLUMN ofc_id,
 604         DROP FOREIGN KEY FK_of_flows_ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 605     echo "DELETE FROM schema_version WHERE version_int = '15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 606 }
 607
 608
 609 function upgrade_to_16(){
 610     echo "    upgrade database from version 0.15 to version 0.16"
 611     echo "    Add last_error and status colum to 'ofcs'"
 612     echo "ALTER TABLE ofcs
 613         ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password,
 614         ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 615     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
 616 }
 617
 618 function downgrade_from_16(){
 619     echo "    downgrade database from version 0.16 to version 0.15"
 620     echo "    Delete last_error and status colum to 'ofcs'"
 621     echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;  " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 622     echo "DELETE FROM schema_version WHERE version_int = '16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 623 }
 624
 625 function upgrade_to_17(){
 626     echo "    upgrade database from version 0.16 to version 0.17"
 627     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 628     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 629           "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 630     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 631          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 632     echo "    Add nets_with_same_vlan to table ofcs"
 633     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
 634     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
 635 }
 636
 637 function downgrade_from_17(){
 638     echo "    downgrade database from version 0.17 to version 0.16"
 639     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 640     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 641          "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 642     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 643          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 644     echo "    Remove nets_with_same_vlan from table ofcs"
 645     echo "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 646     echo "DELETE FROM schema_version WHERE version_int = '17';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 647 }
 648
 649 #TODO ... put funtions here
 650
 651 echo "db version = "${DATABASE_VER_NUM}
 652 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo "    current database version $DATABASE_VER is ok"
 653 #UPGRADE DATABASE step by step
 654 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
 655 do
 656     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 657
 658     upgrade_to_${DATABASE_VER_NUM}
 659     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 660     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 661     #$FILE_ || exit -1  # if fail return
 662 done
 663
 664 #DOWNGRADE DATABASE step by step
 665 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
 666 do
 667     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 668     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 669     #$FILE_ || exit -1  # if fail return
 670     downgrade_from_${DATABASE_VER_NUM}
 671     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 672 done
 673
 674 #echo done
 675