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