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