database_utils/migrate_vim_db.sh

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