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' 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 #check and ask for database user password
 113 DBUSER_="-u$DBUSER"
 114 [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
 115 DBHOST_="-h$DBHOST"
 116 DBPORT_="-P$DBPORT"
 117 while !  echo ";" | mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME >/dev/null 2>&1
 118 do
 119         [ -n "$logintry" ] &&  echo -e "\nInvalid database credentials!!!. Try again (Ctrl+c to abort)"
 120         [ -z "$logintry" ] &&  echo -e "\nProvide database name and credentials"
 121         read -e -p "mysql database name($DBNAME): " KK
 122         [ -n "$KK" ] && DBNAME="$KK"
 123         read -e -p "mysql user($DBUSER): " KK
 124         [ -n "$KK" ] && DBUSER="$KK" && DBUSER_="-u$DBUSER"
 125         read -e -s -p "mysql password: " DBPASS
 126         [ -n "$DBPASS" ] && DBPASS_="-p$DBPASS"
 127         [ -z "$DBPASS" ] && DBPASS_=""
 128         logintry="yes"
 129         echo
 130 done
 131
 132 DBCMD="mysql $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ $DBNAME"
 133 #echo DBCMD $DBCMD
 134
 135 #GET DATABASE VERSION
 136 #check that the database seems a openvim database
 137 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
 138 then
 139     echo "    database $DBNAME does not seem to be an openvim database" >&2
 140     exit -1;
 141 fi
 142
 143 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 144 then
 145     DATABASE_VER="0.0"
 146     DATABASE_VER_NUM=0
 147 else
 148     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 149     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 150     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 151     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 152 fi
 153
 154
 155 #GET DATABASE TARGET VERSION
 156 DATABASE_TARGET_VER_NUM=0
 157 [ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1   #>0.1.91 =>  1
 158 [ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2   #0.2.03  =>  2
 159 [ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3   #0.2.5   =>  3
 160 [ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4   #0.3.1   =>  4
 161 [ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5   #0.4.1   =>  5
 162 [ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6   #0.4.2   =>  6
 163 [ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7   #0.4.5   =>  7
 164 #TODO ... put next versions here
 165
 166
 167 function upgrade_to_1(){
 168     echo "    upgrade database from version 0.0 to version 0.1"
 169     echo "      CREATE TABLE \`schema_version\`"
 170     echo "CREATE TABLE \`schema_version\` (
 171         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 172         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 173         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 174         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 175         \`date\` DATE NULL,
 176         PRIMARY KEY (\`version_int\`)
 177         )
 178         COMMENT='database schema control version'
 179         COLLATE='utf8_general_ci'
 180         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 181     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 182          VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
 183     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 184     echo "ALTER TABLE \`nets\`
 185          ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 186 }
 187 function downgrade_from_1(){
 188     echo "    downgrade database from version 0.1 to version 0.0"
 189     echo "      ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
 190     echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 191     echo "      DROP TABLE \`schema_version\`"
 192     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 193 }
 194 function upgrade_to_2(){
 195     echo "    upgrade database from version 0.1 to version 0.2"
 196     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
 197     for table in of_ports_pci_correspondence resources_port ports
 198     do
 199         echo "ALTER TABLE \`${table}\`
 200             ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 201         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 202         [ $table == of_ports_pci_correspondence ] ||
 203             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
 204     done
 205     echo "      UPDATE procedure UpdateSwitchPort"
 206     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 207     delimiter //
 208     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 209     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 210     BEGIN
 211         #DELETES switch_port entry before writing, because if not it fails for key constrains
 212         UPDATE ports
 213         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 214         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 215         INNER JOIN numas on RP.numa_id=numas.id
 216         INNER JOIN hosts on numas.host_id=hosts.uuid
 217         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 218         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 219         #write switch_port into resources_port and ports
 220         UPDATE ports
 221         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 222         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 223         INNER JOIN numas on RP.numa_id=numas.id
 224         INNER JOIN hosts on numas.host_id=hosts.uuid
 225         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 226         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;
 227     END//
 228     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 229     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 230          VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 231 }
 232 function upgrade_to_3(){
 233     echo "    upgrade database from version 0.2 to version 0.3"
 234     echo "     change size of source_name at table resources_port"
 235     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
 236     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 237     echo "delimiter //
 238     CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 239     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 240     BEGIN
 241         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 242         FROM
 243         (
 244            SELECT id as port_id, Mbps, pci, switch_port, mac
 245            FROM resources_port
 246                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 247         ) as A
 248         INNER JOIN
 249         (
 250            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 251                 FROM resources_port
 252                 WHERE numa_id = Numa AND status = 'ok'
 253                 GROUP BY root_id
 254         ) as B
 255         ON A.port_id = B.root_id
 256         LEFT JOIN
 257         (
 258            SELECT root_id,  COUNT(id) as usedSRIOV
 259                 FROM resources_port
 260                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 261                 GROUP BY root_id
 262         ) as C
 263         ON A.port_id = C.root_id
 264         ORDER BY Mbps_free, availableSRIOV, pci;
 265     END//
 266     delimiter ;"| $DBCMD || !  ! echo "ERROR. Aborted!" || exit -1
 267     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
 268 }
 269
 270 function upgrade_to_4(){
 271     echo "    upgrade database from version 0.3 to version 0.4"
 272     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 273     echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 274     echo "ALTER TABLE ports          DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 275     echo "     change table 'ports'"
 276     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
 277     echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 278     echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 279     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
 280 }
 281
 282 function upgrade_to_X(){
 283     #TODO, this change of foreign key does not work
 284     echo "    upgrade database from version 0.X to version 0.X"
 285     echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
 286           DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 287     echo "ALTER TABLE instances
 288         ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
 289         ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 290 }
 291
 292 function downgrade_from_2(){
 293     echo "    downgrade database from version 0.2 to version 0.1"
 294     echo "      UPDATE procedure UpdateSwitchPort"
 295     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 296     delimiter //
 297     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 298     BEGIN
 299     UPDATE
 300         resources_port INNER JOIN (
 301             SELECT resources_port.id,KK.switch_port
 302             FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 303                 INNER JOIN hosts on numas.host_id=hosts.uuid
 304                 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 305             ) as TABLA
 306         ON  resources_port.root_id=TABLA.id
 307     SET resources_port.switch_port=TABLA.switch_port
 308     WHERE resources_port.root_id=TABLA.id;
 309     END//
 310     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 311     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 312     for table in of_ports_pci_correspondence resources_port ports
 313     do
 314         [ $table == of_ports_pci_correspondence ] ||
 315             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 316         echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 317         switch_port_size=12
 318         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 319         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 320     done
 321     echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 322 }
 323 function downgrade_from_3(){
 324     echo "    downgrade database from version 0.3 to version 0.2"
 325     echo "     change back size of source_name at table resources_port"
 326     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
 327     echo "      DROP PROCEDURE GetAllAvailablePorts"
 328     echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 329     echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 330 }
 331 function downgrade_from_4(){
 332     echo "    downgrade database from version 0.4 to version 0.3"
 333     echo "     adding back unique index VLAN at 'resources_port','ports'"
 334     echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 335     echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 336     echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 337     echo "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 338     echo "     change back table 'ports'"
 339     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 340     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
 341     echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 342 }
 343
 344
 345 function upgrade_to_5(){
 346     echo "    upgrade database from version 0.4 to version 0.5"
 347     echo "     add 'ip_address' to ports'"
 348     echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 349     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
 350 }
 351 function downgrade_from_5(){
 352     echo "    downgrade database from version 0.5 to version 0.4"
 353     echo "     removing 'ip_address' from 'ports'"
 354     echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 355     echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 356 }
 357
 358 function upgrade_to_6(){
 359     echo "    upgrade database from version 0.5 to version 0.6"
 360     echo "      Change enalarge name, description to 255 at all database"
 361     for table in flavors images instances tenants
 362     do
 363          name_length=255
 364          [[ $table == tenants ]] || name_length=64
 365          echo -en "        $table               \r"
 366          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
 367     done
 368     echo -en "        hosts               \r"
 369     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
 370     echo -en "        nets                \r"
 371     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
 372     echo -en "        instances           \r"
 373     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 374     echo -en "        ports               \r"
 375     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
 376     echo -en "        of_flows            \r"
 377     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
 378     echo -en "        of_ports_pci_cor... \r"
 379     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
 380     echo -en "        resources_port      \r"
 381     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
 382     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
 383 }
 384 function downgrade_from_6(){
 385     echo "    downgrade database from version 0.6 to version 0.5"
 386     echo "      Change back name,description to shorter length at all database"
 387     for table in flavors images instances tenants
 388     do
 389          name_length=50
 390          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 391          echo -en "        $table               \r"
 392          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
 393     done
 394     echo -en "        hosts               \r"
 395     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
 396     echo -en "        nets                \r"
 397     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
 398     echo -en "        instances           \r"
 399     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 400     echo -en "        ports               \r"
 401     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
 402     echo -en "        of_flows            \r"
 403     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
 404     echo -en "        of_ports_pci_cor... \r"
 405     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
 406     echo -en "        resources_port      \r"
 407     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
 408     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 409 }
 410 function upgrade_to_7(){
 411     echo "    upgrade database from version 0.6 to version 0.7"
 412     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 413     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
 414     echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 415     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
 416     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (7, '0.7', '0.4.4', 'Add bind_net to net table', '2016-02-12');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 417 }
 418 function downgrade_from_7(){
 419     echo "    downgrade database from version 0.7 to version 0.6"
 420     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 421     echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 422     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
 423     echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 424 }
 425 #TODO ... put funtions here
 426
 427
 428 [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo "    current database version $DATABASE_VER is ok"
 429 #UPGRADE DATABASE step by step
 430 while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ]
 431 do
 432     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 433     upgrade_to_${DATABASE_VER_NUM}
 434     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 435     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 436     #$FILE_ || exit -1  # if fail return
 437 done
 438
 439 #DOWNGRADE DATABASE step by step
 440 while [ $DATABASE_TARGET_VER_NUM -lt $DATABASE_VER_NUM ]
 441 do
 442     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 443     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 444     #$FILE_ || exit -1  # if fail return
 445     downgrade_from_${DATABASE_VER_NUM}
 446     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 447 done
 448
 449 #echo done
 450