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