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 DEFAULT_DBPASS="vimpw"
  31 DBHOST=""
  32 DBPORT="3306"
  33 DBNAME="vim_db"
  34 QUIET_MODE=""
  35 #TODO update it with the last database version
  36 LAST_DB_VERSION=21
  37
  38 # Detect paths
  39 MYSQL=$(which mysql)
  40 AWK=$(which awk)
  41 GREP=$(which grep)
  42
  43 function usage(){
  44     echo -e "Usage: $0 OPTIONS [version]"
  45     echo -e "  Upgrades/Downgrades openvim database preserving the content."\
  46             "If [version]  is not provided, it is upgraded to the last version"
  47     echo -e "  OPTIONS"
  48     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  49     echo -e "     -p PASS  database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
  50     echo -e "     -P PORT  database port. '$DBPORT' by default"
  51     echo -e "     -h HOST  database host. 'localhost' by default"
  52     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  53     echo -e "     -q --quiet: Do not prompt for credentials and exit if cannot access to database"
  54     echo -e "     --help   shows this help"
  55 }
  56
  57 while getopts ":u:p:P:h:d:q-:" o; do
  58     case "${o}" in
  59         u)
  60             DBUSER="$OPTARG"
  61             ;;
  62         p)
  63             DBPASS="$OPTARG"
  64             ;;
  65         P)
  66             DBPORT="$OPTARG"
  67             ;;
  68         d)
  69             DBNAME="$OPTARG"
  70             ;;
  71         h)
  72             DBHOST="$OPTARG"
  73             ;;
  74         q)
  75             export QUIET_MODE=yes
  76             ;;
  77         -)
  78             [ "${OPTARG}" == "help" ] && usage && exit 0
  79             [ "${OPTARG}" == "quiet" ] && export QUIET_MODE=yes && continue
  80             echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
  81             exit 1
  82             ;;
  83         \?)
  84             echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
  85             exit 1
  86             ;;
  87         :)
  88             echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
  89             exit 1
  90             ;;
  91         *)
  92             usage >&2
  93             exit 1
  94             ;;
  95     esac
  96 done
  97 shift $((OPTIND-1))
  98
  99 DB_VERSION=$1
 100
 101 if [ -n "$DB_VERSION" ] ; then
 102     # check it is a number and an allowed one
 103     [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev/null ||
 104         ! echo "parameter 'version' requires a integer value" >&2 || exit 1
 105     if [ "$DB_VERSION" -lt 0 ] || [ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
 106         echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
 107              "If you need an upper version, get a newer version of this script '$0'" >&2
 108         exit 1
 109     fi
 110 else
 111     DB_VERSION="$LAST_DB_VERSION"
 112 fi
 113
 114 # Creating temporary file
 115 TEMPFILE="$(mktemp -q --tmpdir "migratevimdb.XXXXXX")"
 116 trap 'rm -f "$TEMPFILE"' EXIT
 117 chmod 0600 "$TEMPFILE"
 118 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 119 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 120
 121 # Check and ask for database user password
 122 FIRST_TRY="yes"
 123 while ! DB_ERROR=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
 124 do
 125     # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
 126     [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS="$DEFAULT_DBPASS" &&
 127         echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
 128         continue
 129     echo "$DB_ERROR"
 130     [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
 131     echo -e "Provide database name and credentials (Ctrl+c to abort):"
 132     read -e -p "    mysql database name($DBNAME): " KK
 133     [ -n "$KK" ] && DBNAME="$KK"
 134     read -e -p "    mysql user($DBUSER): " KK
 135     [ -n "$KK" ] && DBUSER="$KK"
 136     read -e -s -p "    mysql password: " DBPASS
 137     echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 138     FIRST_TRY=""
 139     echo
 140 done
 141
 142 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
 143 #echo DBCMD $DBCMD
 144
 145 #GET DATABASE VERSION
 146 # check that the database seems a openvim database
 147 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
 148 then
 149     echo "    database $DBNAME does not seem to be an openvim database" >&2
 150     exit -1;
 151 fi
 152
 153 if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1
 154 then
 155     DATABASE_VER="0.0"
 156     DATABASE_VER_NUM=0
 157 else
 158     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2`
 159     DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2`
 160     [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] &&
 161         echo "    Error can not get database version ($DATABASE_VER?)" >&2 && exit -1
 162     #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}"
 163 fi
 164
 165 [ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ] &&
 166     echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
 167     exit 1
 168
 169 #GET DATABASE TARGET VERSION
 170 #DB_VERSION=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 #[ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM=12  #0.5.5   => 12
 183 #[ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM=13  #0.5.6   => 13
 184 #[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14  #0.5.7   => 14
 185 #[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15  #0.5.8   => 15
 186 #[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16  #0.5.9   => 16
 187 #[ $OPENVIM_VER_NUM -ge 5010 ] && DATABASE_TARGET_VER_NUM=17  #0.5.10  => 17
 188 #[ $OPENVIM_VER_NUM -ge 5013 ] && DATABASE_TARGET_VER_NUM=18  #0.5.13  => 18
 189 #[ $OPENVIM_VER_NUM -ge 5015 ] && DATABASE_TARGET_VER_NUM=19  #0.5.15  => 19
 190 #[ $OPENVIM_VER_NUM -ge 5017 ] && DATABASE_TARGET_VER_NUM=20   #0.5.17  => 20
 191 #[ $OPENVIM_VER_NUM -ge 5018 ] && DATABASE_TARGET_VER_NUM=21   #0.5.18  => 21
 192 # TODO ... put next versions here
 193
 194 function upgrade_to_1(){
 195     # echo "    upgrade database from version 0.0 to version 0.1"
 196     echo "      CREATE TABLE \`schema_version\`"
 197     echo "CREATE TABLE \`schema_version\` (
 198         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 199         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 200         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 201         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 202         \`date\` DATE NULL,
 203         PRIMARY KEY (\`version_int\`)
 204         )
 205         COMMENT='database schema control version'
 206         COLLATE='utf8_general_ci'
 207         ENGINE=InnoDB;" | $DBCMD  || ! echo "ERROR. Aborted!" || exit -1
 208     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 209          VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');" | $DBCMD
 210     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 211     echo "ALTER TABLE \`nets\`
 212          ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 213 }
 214 function downgrade_from_1(){
 215     # echo "    downgrade database from version 0.1 to version 0.0"
 216     echo "      ALTER TABLE \`nets\` DROP COLUMN \`last_error\`"
 217     echo "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 218     echo "      DROP TABLE \`schema_version\`"
 219     echo "DROP TABLE \`schema_version\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 220 }
 221 function upgrade_to_2(){
 222     # echo "    upgrade database from version 0.1 to version 0.2"
 223     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` ADD COLUMN \`switch_dpid\`"
 224     for table in of_ports_pci_correspondence resources_port ports
 225     do
 226         echo "ALTER TABLE \`${table}\`
 227             ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 228         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 229         [ $table == of_ports_pci_correspondence ] ||
 230             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
 231     done
 232     echo "      UPDATE procedure UpdateSwitchPort"
 233     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 234     delimiter //
 235     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 236     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 237     BEGIN
 238         #DELETES switch_port entry before writing, because if not it fails for key constrains
 239         UPDATE ports
 240         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 241         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 242         INNER JOIN numas on RP.numa_id=numas.id
 243         INNER JOIN hosts on numas.host_id=hosts.uuid
 244         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 245         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 246         #write switch_port into resources_port and ports
 247         UPDATE ports
 248         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 249         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 250         INNER JOIN numas on RP.numa_id=numas.id
 251         INNER JOIN hosts on numas.host_id=hosts.uuid
 252         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 253         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;
 254     END//
 255     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 256     echo "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`)
 257          VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 258 }
 259 function upgrade_to_3(){
 260     # echo "    upgrade database from version 0.2 to version 0.3"
 261     echo "     change size of source_name at table resources_port"
 262     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
 263     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 264     echo "delimiter //
 265     CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 266     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 267     BEGIN
 268         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 269         FROM
 270         (
 271            SELECT id as port_id, Mbps, pci, switch_port, mac
 272            FROM resources_port
 273                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 274         ) as A
 275         INNER JOIN
 276         (
 277            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 278                 FROM resources_port
 279                 WHERE numa_id = Numa AND status = 'ok'
 280                 GROUP BY root_id
 281         ) as B
 282         ON A.port_id = B.root_id
 283         LEFT JOIN
 284         (
 285            SELECT root_id,  COUNT(id) as usedSRIOV
 286                 FROM resources_port
 287                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 288                 GROUP BY root_id
 289         ) as C
 290         ON A.port_id = C.root_id
 291         ORDER BY Mbps_free, availableSRIOV, pci;
 292     END//
 293     delimiter ;"| $DBCMD || !  ! echo "ERROR. Aborted!" || exit -1
 294     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
 295 }
 296
 297 function upgrade_to_4(){
 298     # echo "    upgrade database from version 0.3 to version 0.4"
 299     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 300     echo "ALTER TABLE resources_port DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 301     echo "ALTER TABLE ports          DROP INDEX vlan_switch_port;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 302     echo "     change table 'ports'"
 303     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
 304     echo "ALTER TABLE ports DROP COLUMN vlan_changed;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 305     echo "ALTER TABLE resources_port DROP COLUMN vlan;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 306     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
 307 }
 308
 309 function upgrade_to_X(){
 310     #TODO, this change of foreign key does not work
 311     # echo "    upgrade database from version 0.X to version 0.X"
 312     echo "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors,
 313           DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 314     echo "ALTER TABLE instances
 315         ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) REFERENCES tenants_flavors (flavor_id, tenant_id),
 316         ADD CONSTRAINT FK_instances_images FOREIGN KEY (image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 317 }
 318
 319 function downgrade_from_2(){
 320     # echo "    downgrade database from version 0.2 to version 0.1"
 321     echo "      UPDATE procedure UpdateSwitchPort"
 322     echo "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 323     delimiter //
 324     CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 325     BEGIN
 326     UPDATE
 327         resources_port INNER JOIN (
 328             SELECT resources_port.id,KK.switch_port
 329             FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 330                 INNER JOIN hosts on numas.host_id=hosts.uuid
 331                 INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 332             ) as TABLA
 333         ON  resources_port.root_id=TABLA.id
 334     SET resources_port.switch_port=TABLA.switch_port
 335     WHERE resources_port.root_id=TABLA.id;
 336     END//
 337     delimiter ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 338     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 339     for table in of_ports_pci_correspondence resources_port ports
 340     do
 341         [ $table == of_ports_pci_correspondence ] ||
 342             echo "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 343         echo "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 344         switch_port_size=12
 345         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 346         echo "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 347     done
 348     echo "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 349 }
 350 function downgrade_from_3(){
 351     # echo "    downgrade database from version 0.3 to version 0.2"
 352     echo "     change back size of source_name at table resources_port"
 353     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
 354     echo "      DROP PROCEDURE GetAllAvailablePorts"
 355     echo "DROP PROCEDURE GetAllAvailablePorts;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 356     echo "DELETE FROM schema_version WHERE version_int = '3';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 357 }
 358 function downgrade_from_4(){
 359     # echo "    downgrade database from version 0.4 to version 0.3"
 360     echo "     adding back unique index VLAN at 'resources_port','ports'"
 361     echo "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 362     echo "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 363     echo "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 364     echo "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 365     echo "     change back table 'ports'"
 366     echo "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 367     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
 368     echo "DELETE FROM schema_version WHERE version_int = '4';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 369 }
 370
 371
 372 function upgrade_to_5(){
 373     # echo "    upgrade database from version 0.4 to version 0.5"
 374     echo "     add 'ip_address' to ports'"
 375     echo "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 376     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
 377 }
 378 function downgrade_from_5(){
 379     # echo "    downgrade database from version 0.5 to version 0.4"
 380     echo "     removing 'ip_address' from 'ports'"
 381     echo "ALTER TABLE ports DROP COLUMN ip_address;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 382     echo "DELETE FROM schema_version WHERE version_int = '5';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 383 }
 384
 385 function upgrade_to_6(){
 386     # echo "    upgrade database from version 0.5 to version 0.6"
 387     echo "      Change enalarge name, description to 255 at all database"
 388     for table in flavors images instances tenants
 389     do
 390          name_length=255
 391          [[ $table == tenants ]] || name_length=64
 392          echo -en "        $table               \r"
 393          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
 394     done
 395     echo -en "        hosts               \r"
 396     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
 397     echo -en "        nets                \r"
 398     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
 399     echo -en "        instances           \r"
 400     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 401     echo -en "        ports               \r"
 402     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
 403     echo -en "        of_flows            \r"
 404     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
 405     echo -en "        of_ports_pci_cor... \r"
 406     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
 407     echo -en "        resources_port      \r"
 408     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
 409     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
 410 }
 411 function downgrade_from_6(){
 412     # echo "    downgrade database from version 0.6 to version 0.5"
 413     echo "      Change back name,description to shorter length at all database"
 414     for table in flavors images instances tenants
 415     do
 416          name_length=50
 417          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 418          echo -en "        $table               \r"
 419          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
 420     done
 421     echo -en "        hosts               \r"
 422     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
 423     echo -en "        nets                \r"
 424     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
 425     echo -en "        instances           \r"
 426     echo "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 427     echo -en "        ports               \r"
 428     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
 429     echo -en "        of_flows            \r"
 430     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
 431     echo -en "        of_ports_pci_cor... \r"
 432     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
 433     echo -en "        resources_port      \r"
 434     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
 435     echo "DELETE FROM schema_version WHERE version_int='6';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 436 }
 437
 438 function upgrade_to_7(){
 439     # echo "    upgrade database from version 0.6 to version 0.7"
 440     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 441     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
 442     echo "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 443     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
 444     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
 445 }
 446 function downgrade_from_7(){
 447     # echo "    downgrade database from version 0.7 to version 0.6"
 448     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 449     echo "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 450     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
 451     echo "DELETE FROM schema_version WHERE version_int = '7';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 452 }
 453
 454 function upgrade_to_8(){
 455     # echo "    upgrade database from version 0.7 to version 0.8"
 456     echo "     add column 'checksum' to 'images'"
 457     echo "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 458     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
 459 }
 460 function downgrade_from_8(){
 461     # echo "    downgrade database from version 0.8 to version 0.7"
 462     echo "     remove column 'checksum' from 'images'"
 463     echo "ALTER TABLE images DROP COLUMN checksum;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 464     echo "DELETE FROM schema_version WHERE version_int = '8';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 465 }
 466
 467 function upgrade_to_9(){
 468     # echo "    upgrade database from version 0.8 to version 0.9"
 469     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'"
 470     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
 471     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 472     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
 473 }
 474 function downgrade_from_9(){
 475     # echo "    downgrade database from version 0.9 to version 0.8"
 476     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 477     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
 478     echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 479     echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 480 }
 481
 482 function upgrade_to_10(){
 483     # echo "    upgrade database from version 0.9 to version 0.10"
 484     echo "     change types at 'ports'"
 485     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
 486     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
 487 }
 488 function downgrade_from_10(){
 489     # echo "    downgrade database from version 0.10 to version 0.9"
 490     echo "     change back types at 'ports'"
 491     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
 492     echo "DELETE FROM schema_version WHERE version_int = '10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 493 }
 494
 495 function upgrade_to_11(){
 496     # echo "    upgrade database from version 0.10 to version 0.11"
 497     echo "    Add gateway_ip colum to 'nets'"
 498     echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 499     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
 500 }
 501 function downgrade_from_11(){
 502     # echo "    downgrade database from version 0.11 to version 0.10"
 503     echo "    Delete gateway_ip colum from 'nets'"
 504     echo "ALTER TABLE nets DROP COLUMN gateway_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 505     echo "DELETE FROM schema_version WHERE version_int = '11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 506 }
 507 function upgrade_to_12(){
 508     # echo "    upgrade database from version 0.11 to version 0.12"
 509     echo "    Create of_controller table "
 510     echo "CREATE TABLE ofcs (
 511         uuid VARCHAR(36) NOT NULL,
 512         name VARCHAR(255) NOT NULL,
 513         dpid VARCHAR(64) NOT NULL,
 514         ip VARCHAR(64) NOT NULL,
 515         port INT(5) NOT NULL,
 516         type VARCHAR(64) NOT NULL,
 517         version VARCHAR(12) NULL DEFAULT NULL,
 518         user VARCHAR(64) NULL DEFAULT NULL,
 519         password VARCHAR(64) NULL DEFAULT NULL,
 520         PRIMARY KEY (uuid)
 521 )
 522 COLLATE='utf8_general_ci'
 523 ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 524     echo "    Modify user_at for uuids table"
 525     echo "ALTER TABLE uuids  CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 526     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 527 }
 528
 529 function downgrade_from_12(){
 530     # echo "    downgrade database from version 0.12 to version 0.11"
 531     echo "    Delete ofcs table"
 532     echo "DROP TABLE ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 533     echo "ALTER TABLE uuids  CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', 'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 534     echo "DELETE FROM schema_version WHERE version_int = '12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 535 }
 536
 537 function upgrade_to_13(){
 538     # echo "    upgrade database from version 0.12 to version 0.13"
 539     echo "    Create of_port_mapings table "
 540     echo "CREATE TABLE of_port_mappings (
 541         uuid VARCHAR(36) NOT NULL,
 542         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 543         region VARCHAR(64) NULL DEFAULT NULL,
 544         compute_node VARCHAR(64) NULL DEFAULT NULL,
 545         pci VARCHAR(50) NULL DEFAULT NULL,
 546         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 547         switch_port VARCHAR(64) NULL DEFAULT NULL,
 548         switch_mac CHAR(18) NULL DEFAULT NULL,
 549         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 550         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 551         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 552         INDEX FK_of_port_mappings_ofcs (ofc_id),
 553         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE CASCADE)
 554     COLLATE='utf8_general_ci'
 555     ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 556     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 557 }
 558
 559 function downgrade_from_13(){
 560     # echo "    downgrade database from version 0.13 to version 0.12"
 561     echo "    Delete of_port_mappings table"
 562     echo "DROP TABLE of_port_mappings;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 563     echo "DELETE FROM schema_version WHERE version_int = '13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 564 }
 565
 566 function upgrade_to_14(){
 567     # echo "    upgrade database from version 0.13 to version 0.14"
 568     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 569     echo "ALTER TABLE ports
 570         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 571         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 572         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 573     echo "ALTER TABLE resources_port
 574         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 575         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 576         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 577     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 578 }
 579
 580 function downgrade_from_14(){
 581     # echo "    downgrade database from version 0.14 to version 0.13"
 582     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 583     echo "ALTER TABLE ports
 584         DROP COLUMN switch_mac,
 585         DROP COLUMN ofc_id,
 586         DROP FOREIGN KEY FK_port_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 587     echo "ALTER TABLE resources_port
 588         DROP COLUMN switch_mac,
 589         DROP COLUMN ofc_id,
 590         DROP FOREIGN KEY FK_resource_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 591     echo "DELETE FROM schema_version WHERE version_int = '14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 592 }
 593
 594 function upgrade_to_15(){
 595     # echo "    upgrade database from version 0.14 to version 0.15"
 596     echo "    Add ofc_id colum to 'of_flows'"
 597     echo "ALTER TABLE of_flows
 598         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id,
 599         ADD CONSTRAINT FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 600     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 601 }
 602
 603 function downgrade_from_15(){
 604     # echo "    downgrade database from version 0.15 to version 0.14"
 605     echo "    Delete ofc_id to 'of_flows'"
 606     echo "ALTER TABLE of_flows
 607         DROP COLUMN ofc_id,
 608         DROP FOREIGN KEY FK_of_flows_ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 609     echo "DELETE FROM schema_version WHERE version_int = '15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 610 }
 611
 612
 613 function upgrade_to_16(){
 614     # echo "    upgrade database from version 0.15 to version 0.16"
 615     echo "    Add last_error and status colum to 'ofcs'"
 616     echo "ALTER TABLE ofcs
 617         ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password,
 618         ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 619     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 620 }
 621
 622 function downgrade_from_16(){
 623     # echo "    downgrade database from version 0.16 to version 0.15"
 624     echo "    Delete last_error and status colum to 'ofcs'"
 625     echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;  " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 626     echo "DELETE FROM schema_version WHERE version_int = '16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 627 }
 628
 629 function upgrade_to_17(){
 630     # echo "    upgrade database from version 0.16 to version 0.17"
 631     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 632     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 633           "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 634     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 635          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 636     echo "    Add nets_with_same_vlan to table ofcs"
 637     echo "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 638     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 639 }
 640
 641 function downgrade_from_17(){
 642     # echo "    downgrade database from version 0.17 to version 0.16"
 643     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 644     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 645          "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 646     echo "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 647          "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 648     echo "    Remove nets_with_same_vlan from table ofcs"
 649     echo "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 650     echo "DELETE FROM schema_version WHERE version_int = '17';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 651 }
 652
 653 function upgrade_to_18(){
 654     echo "    Add 'region' at 'nets' and change unique index vlan+region"
 655     echo "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, " \
 656             "DROP INDEX type_vlan;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 657     echo "    Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
 658     echo "UPDATE nets set region='__OVS__' where provider like 'OVS%';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 659     echo "UPDATE nets set region='__DATA__' where type='data' or type='ptp';" | $DBCMD || ! echo "ERROR. Aborted!" ||
 660          exit -1
 661     echo "    Create new index region_vlan at nets"
 662         echo "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);" \
 663              | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 664     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 665             "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"\
 666          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 667 }
 668
 669 function downgrade_from_18(){
 670     echo "    Delete 'region' at 'nets' and change back unique index vlan+type"
 671     echo "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 672     echo "    Create back index type_vlan at nets"
 673         echo "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 674     echo "DELETE FROM schema_version WHERE version_int = '18';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 675 }
 676
 677 function upgrade_to_19(){
 678     echo "    Add 'keyfile' to 'hosts'"
 679     echo "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;" \
 680             | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 681     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 682             "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"\
 683          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 684 }
 685
 686 function downgrade_from_19(){
 687     echo "    Delete 'keyfile' from 'hosts'"
 688     echo "ALTER TABLE hosts DROP COLUMN keyfile;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 689     echo "DELETE FROM schema_version WHERE version_int = '19';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 690 }
 691
 692 function upgrade_to_20(){
 693     echo "    Add 'image_size' to 'instance_devices'"
 694     echo "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 695     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"\
 696          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 697
 698
 699 }
 700
 701 function downgrade_from_20(){
 702     echo "    Delete 'image_size' from 'instance_devices'"
 703     echo "ALTER TABLE instance_devices DROP COLUMN image_size;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 704     echo "DELETE FROM schema_version WHERE version_int = '20';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 705 }
 706
 707 function upgrade_to_21(){
 708     echo "    Add 'routes', 'links' and 'dns' to 'nets'"
 709     echo "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip,
 710     ADD COLUMN links TEXT(2000)  NULL AFTER dns,
 711     ADD COLUMN routes TEXT(2000)  NULL AFTER links;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 712     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"\
 713          | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 714 }
 715
 716 function downgrade_from_21(){
 717     echo "    Delete 'routes', 'links' and 'dns' to 'nets'"
 718     echo "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 719     echo "DELETE FROM schema_version WHERE version_int = '21';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1
 720 }
 721
 722
 723 #TODO ... put funtions here
 724
 725 # echo "db version = "${DATABASE_VER_NUM}
 726 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo "    current database version '$DATABASE_VER_NUM' is ok"
 727 #UPGRADE DATABASE step by step
 728 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 729 do
 730     echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 731     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 732     upgrade_to_${DATABASE_VER_NUM}
 733     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 734     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 735     #$FILE_ || exit -1  # if fail return
 736 done
 737
 738 #DOWNGRADE DATABASE step by step
 739 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 740 do
 741     echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 742     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 743     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 744     #$FILE_ || exit -1  # if fail return
 745     downgrade_from_${DATABASE_VER_NUM}
 746     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 747 done
 748
 749 #echo done
 750