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