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