database_utils/migrate_vim_db.sh

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