database_utils/migrate_vim_db.sh

   1 #!/bin/bash
   2
   3 ##
   4 # Copyright 2015 Telefonica Investigacion 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 DBUTILS="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
  28
  29 DBUSER="vim"
  30 DBPASS=""
  31 DEFAULT_DBPASS="vimpw"
  32 DBHOST=""
  33 DBPORT="3306"
  34 DBNAME="vim_db"
  35 QUIET_MODE=""
  36 BACKUP_DIR=""
  37 BACKUP_FILE=""
  38 #TODO update it with the last database version
  39 LAST_DB_VERSION=23
  40
  41 # Detect paths
  42 MYSQL=$(which mysql)
  43 AWK=$(which awk)
  44 GREP=$(which grep)
  45
  46 function usage(){
  47     echo -e "Usage: $0 OPTIONS [version]"
  48     echo -e "  Upgrades/Downgrades openvim database preserving the content."\
  49             "If [version]  is not provided, it is upgraded to the last version"
  50     echo -e "  OPTIONS"
  51     echo -e "     -u USER  database user. '$DBUSER' by default. Prompts if DB access fails"
  52     echo -e "     -p PASS  database password. If missing it tries without and '$DEFAULT_DBPASS' password before prompting"
  53     echo -e "     -P PORT  database port. '$DBPORT' by default"
  54     echo -e "     -h HOST  database host. 'localhost' by default"
  55     echo -e "     -d NAME  database name. '$DBNAME' by default.  Prompts if DB access fails"
  56     echo -e "     -b DIR   backup folder where to create rollback backup file"
  57     echo -e "     -q --quiet: Do not prompt for credentials and exit if cannot access to database"
  58     echo -e "     --help   shows this help"
  59 }
  60
  61 while getopts ":u:p:b:P:h:d:q-:" o; do
  62     case "${o}" in
  63         u)
  64             DBUSER="$OPTARG"
  65             ;;
  66         p)
  67             DBPASS="$OPTARG"
  68             ;;
  69         P)
  70             DBPORT="$OPTARG"
  71             ;;
  72         d)
  73             DBNAME="$OPTARG"
  74             ;;
  75         h)
  76             DBHOST="$OPTARG"
  77             ;;
  78         b)
  79             BACKUP_DIR="$OPTARG"
  80             ;;
  81         q)
  82             export QUIET_MODE=yes
  83             ;;
  84         -)
  85             [ "${OPTARG}" == "help" ] && usage && exit 0
  86             [ "${OPTARG}" == "quiet" ] && export QUIET_MODE=yes && continue
  87             echo "Invalid option: '--$OPTARG'. Type --help for more information" >&2
  88             exit 1
  89             ;;
  90         \?)
  91             echo "Invalid option: '-$OPTARG'. Type --help for more information" >&2
  92             exit 1
  93             ;;
  94         :)
  95             echo "Option '-$OPTARG' requires an argument. Type --help for more information" >&2
  96             exit 1
  97             ;;
  98         *)
  99             usage >&2
 100             exit 1
 101             ;;
 102     esac
 103 done
 104 shift $((OPTIND-1))
 105
 106 DB_VERSION=$1
 107
 108 if [ -n "$DB_VERSION" ] ; then
 109     # check it is a number and an allowed one
 110     [ "$DB_VERSION" -eq "$DB_VERSION" ] 2>/dev/null ||
 111         ! echo "parameter 'version' requires a integer value" >&2 || exit 1
 112     if [ "$DB_VERSION" -lt 0 ] || [ "$DB_VERSION" -gt "$LAST_DB_VERSION" ] ; then
 113         echo "parameter 'version' requires a valid database version between '0' and '$LAST_DB_VERSION'"\
 114              "If you need an upper version, get a newer version of this script '$0'" >&2
 115         exit 1
 116     fi
 117 else
 118     DB_VERSION="$LAST_DB_VERSION"
 119 fi
 120
 121 # Creating temporary file
 122 TEMPFILE="$(mktemp -q --tmpdir "migratevimdb.XXXXXX")"
 123 trap 'rm -f "$TEMPFILE"' EXIT
 124 chmod 0600 "$TEMPFILE"
 125 DEF_EXTRA_FILE_PARAM="--defaults-extra-file=$TEMPFILE"
 126 echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 127
 128 # Check and ask for database user password
 129 FIRST_TRY="yes"
 130 while ! DB_ERROR=`mysql "$DEF_EXTRA_FILE_PARAM" $DBNAME -e "quit" 2>&1 >/dev/null`
 131 do
 132     # if password is not provided, try silently with $DEFAULT_DBPASS before exit or prompt for credentials
 133     [[ -n "$FIRST_TRY" ]] && [[ -z "$DBPASS" ]] && DBPASS="$DEFAULT_DBPASS" &&
 134         echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE" &&
 135         continue
 136     echo "$DB_ERROR"
 137     [[ -n "$QUIET_MODE" ]] && echo -e "Invalid database credentials!!!" >&2 && exit 1
 138     echo -e "Provide database name and credentials (Ctrl+c to abort):"
 139     read -e -p "    mysql database name($DBNAME): " KK
 140     [ -n "$KK" ] && DBNAME="$KK"
 141     read -e -p "    mysql user($DBUSER): " KK
 142     [ -n "$KK" ] && DBUSER="$KK"
 143     read -e -s -p "    mysql password: " DBPASS
 144     echo -e "[client]\n user='${DBUSER}'\n password='$DBPASS'\n host='$DBHOST'\n port='$DBPORT'" > "$TEMPFILE"
 145     FIRST_TRY=""
 146     echo
 147 done
 148
 149 DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME"
 150 #echo DBCMD $DBCMD
 151
 152 # check that the database seems a openvim database
 153 if ! echo -e "show create table instances;\nshow create table numas" | $DBCMD >/dev/null 2>&1
 154 then
 155     echo "    database $DBNAME does not seem to be an openvim database" >&2
 156     exit 1;
 157 fi
 158
 159 #GET DATABASE TARGET VERSION
 160 #DB_VERSION=0
 161 #[ $OPENVIM_VER_NUM -gt 1091 ] && DATABASE_TARGET_VER_NUM=1   #>0.1.91 =>  1
 162 #[ $OPENVIM_VER_NUM -ge 2003 ] && DATABASE_TARGET_VER_NUM=2   #0.2.03  =>  2
 163 #[ $OPENVIM_VER_NUM -ge 2005 ] && DATABASE_TARGET_VER_NUM=3   #0.2.5   =>  3
 164 #[ $OPENVIM_VER_NUM -ge 3001 ] && DATABASE_TARGET_VER_NUM=4   #0.3.1   =>  4
 165 #[ $OPENVIM_VER_NUM -ge 4001 ] && DATABASE_TARGET_VER_NUM=5   #0.4.1   =>  5
 166 #[ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6   #0.4.2   =>  6
 167 #[ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7   #0.4.5   =>  7
 168 #[ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM=8   #0.4.10  =>  8
 169 #[ $OPENVIM_VER_NUM -ge 5001 ] && DATABASE_TARGET_VER_NUM=9   #0.5.1   =>  9
 170 #[ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM=10  #0.5.2   => 10
 171 #[ $OPENVIM_VER_NUM -ge 5004 ] && DATABASE_TARGET_VER_NUM=11  #0.5.4   => 11
 172 #[ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM=12  #0.5.5   => 12
 173 #[ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM=13  #0.5.6   => 13
 174 #[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14  #0.5.7   => 14
 175 #[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15  #0.5.8   => 15
 176 #[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16  #0.5.9   => 16
 177 #[ $OPENVIM_VER_NUM -ge 5010 ] && DATABASE_TARGET_VER_NUM=17  #0.5.10  => 17
 178 #[ $OPENVIM_VER_NUM -ge 5013 ] && DATABASE_TARGET_VER_NUM=18  #0.5.13  => 18
 179 #[ $OPENVIM_VER_NUM -ge 5015 ] && DATABASE_TARGET_VER_NUM=19  #0.5.15  => 19
 180 #[ $OPENVIM_VER_NUM -ge 5017 ] && DATABASE_TARGET_VER_NUM=20   #0.5.17  => 20
 181 #[ $OPENVIM_VER_NUM -ge 5018 ] && DATABASE_TARGET_VER_NUM=21   #0.5.18  => 21
 182 #[ $OPENVIM_VER_NUM -ge 5021 ] && DATABASE_TARGET_VER_NUM=22   #0.5.21  => 22
 183 #[ $OPENVIM_VER_NUM -ge 5024 ] && DATABASE_TARGET_VER_NUM=23   #0.5.24  => 23
 184 # TODO ... put next versions here
 185
 186 function upgrade_to_1(){
 187     # echo "    upgrade database from version 0.0 to version 0.1"
 188     echo "      CREATE TABLE \`schema_version\`"
 189     sql "CREATE TABLE \`schema_version\` (
 190         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 191         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 192         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 193         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 194         \`date\` DATE NULL,
 195         PRIMARY KEY (\`version_int\`)
 196         )
 197         COMMENT='database schema control version'
 198         COLLATE='utf8_general_ci'
 199         ENGINE=InnoDB;"
 200     sql "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`) "\
 201             "VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');"
 202     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 203     sql "ALTER TABLE \`nets\` ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;"
 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     sql "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;"
 209     echo "      DROP TABLE \`schema_version\`"
 210     sql "DROP TABLE \`schema_version\`;"
 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         sql "ALTER TABLE \`${table}\` "\
 218             "ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; "
 219         sql "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;"
 220         [ $table == of_ports_pci_correspondence ] ||
 221             sql "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port "\
 222                 "(vlan, switch_port, switch_dpid);"
 223     done
 224     echo "      UPDATE procedure UpdateSwitchPort"
 225     sql "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 226         delimiter //
 227         CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 228         COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 229         BEGIN
 230             #DELETES switch_port entry before writing, because if not it fails for key constrains
 231             UPDATE ports
 232             RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 233             INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 234             INNER JOIN numas on RP.numa_id=numas.id
 235             INNER JOIN hosts on numas.host_id=hosts.uuid
 236             INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 237             SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 238             #write switch_port into resources_port and ports
 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=PC.switch_port, ports.switch_dpid=PC.switch_dpid, RP.switch_port=PC.switch_port, "\
 246                 "RP.switch_dpid=PC.switch_dpid;
 247         END//
 248         delimiter ;"
 249     sql "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`) "\
 250             "VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');"
 251 }
 252 function upgrade_to_3(){
 253     # echo "    upgrade database from version 0.2 to version 0.3"
 254     echo "     change size of source_name at table resources_port"
 255     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL AFTER port_id;"
 256     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 257     sql "delimiter //
 258         CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 259         COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 260         BEGIN
 261         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as
 262             availableSRIOV, switch_port, mac
 263         FROM
 264         (
 265            SELECT id as port_id, Mbps, pci, switch_port, mac
 266            FROM resources_port
 267             WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 268         ) as A
 269         INNER JOIN
 270         (
 271            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 272             FROM resources_port
 273             WHERE numa_id = Numa AND status = 'ok'
 274             GROUP BY root_id
 275         ) as B
 276         ON A.port_id = B.root_id
 277         LEFT JOIN
 278         (
 279            SELECT root_id,  COUNT(id) as usedSRIOV
 280             FROM resources_port
 281             WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 282             GROUP BY root_id
 283         ) as C
 284         ON A.port_id = C.root_id
 285         ORDER BY Mbps_free, availableSRIOV, pci;
 286         END//
 287         delimiter ;"
 288     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 289         "VALUES (3, '0.3', '0.2.5', 'New Procedure GetAllAvailablePorts', '2015-07-09');"
 290 }
 291
 292 function upgrade_to_4(){
 293     # echo "    upgrade database from version 0.3 to version 0.4"
 294     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 295     sql "ALTER TABLE resources_port DROP INDEX vlan_switch_port;"
 296     sql "ALTER TABLE ports          DROP INDEX vlan_switch_port;"
 297     echo "     change table 'ports'"
 298     sql "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL COMMENT 'driver model for bridge "\
 299         "ifaces; PF,VF,VFnotShared for data ifaces' AFTER mac;"
 300     sql "ALTER TABLE ports DROP COLUMN vlan_changed;"
 301     sql "ALTER TABLE resources_port DROP COLUMN vlan;"
 302     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 303         "VALUES (4, '0.4', '0.3.1', 'Remove unique index VLAN at resources_port', '2015-09-04');"
 304 }
 305
 306 function upgrade_to_X(){
 307     #TODO, this change of foreign key does not work
 308     # echo "    upgrade database from version 0.X to version 0.X"
 309     sql "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors, "\
 310         "DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"
 311     sql "ALTER TABLE instances ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) "\
 312         "REFERENCES tenants_flavors (flavor_id, tenant_id), ADD CONSTRAINT FK_instances_images FOREIGN KEY "\
 313         "(image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);"
 314 }
 315
 316 function downgrade_from_2(){
 317     # echo "    downgrade database from version 0.2 to version 0.1"
 318     echo "      UPDATE procedure UpdateSwitchPort"
 319     sql "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 320         delimiter //
 321         CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 322         BEGIN
 323         UPDATE
 324             resources_port INNER JOIN (
 325                 SELECT resources_port.id,KK.switch_port
 326                 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 327                     INNER JOIN hosts on numas.host_id=hosts.uuid
 328                     INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 329                 ) as TABLA
 330             ON  resources_port.root_id=TABLA.id
 331         SET resources_port.switch_port=TABLA.switch_port
 332         WHERE resources_port.root_id=TABLA.id;
 333         END//
 334         delimiter ;"
 335     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 336     for table in of_ports_pci_correspondence resources_port ports
 337     do
 338         [ $table == of_ports_pci_correspondence ] ||
 339             sql "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);"
 340         sql "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;"
 341         switch_port_size=12
 342         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 343         sql "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;"
 344     done
 345     sql "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';"
 346 }
 347 function downgrade_from_3(){
 348     # echo "    downgrade database from version 0.3 to version 0.2"
 349     echo "     change back size of source_name at table resources_port"
 350     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(20) NULL DEFAULT NULL AFTER port_id;"
 351     echo "      DROP PROCEDURE GetAllAvailablePorts"
 352     sql "DROP PROCEDURE GetAllAvailablePorts;"
 353     sql "DELETE FROM schema_version WHERE version_int = '3';"
 354 }
 355 function downgrade_from_4(){
 356     # echo "    downgrade database from version 0.4 to version 0.3"
 357     echo "     adding back unique index VLAN at 'resources_port','ports'"
 358     sql "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"
 359     sql "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"
 360     sql "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
 361     sql "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
 362     echo "     change back table 'ports'"
 363     sql "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"
 364     sql "ALTER TABLE ports ADD COLUMN vlan_changed SMALLINT(5) NULL DEFAULT NULL COMMENT '!=NULL when original vlan "\
 365         "have been changed to match a pmp net with all ports in the same vlan' AFTER switch_port;"
 366     sql "DELETE FROM schema_version WHERE version_int = '4';"
 367 }
 368
 369
 370 function upgrade_to_5(){
 371     # echo "    upgrade database from version 0.4 to version 0.5"
 372     echo "     add 'ip_address' to ports'"
 373     sql "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"
 374     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 375         "VALUES (5, '0.5', '0.4.1', 'Add ip_address to ports', '2015-09-04');"
 376 }
 377 function downgrade_from_5(){
 378     # echo "    downgrade database from version 0.5 to version 0.4"
 379     echo "     removing 'ip_address' from 'ports'"
 380     sql "ALTER TABLE ports DROP COLUMN ip_address;"
 381     sql "DELETE FROM schema_version WHERE version_int = '5';"
 382 }
 383
 384 function upgrade_to_6(){
 385     # echo "    upgrade database from version 0.5 to version 0.6"
 386     echo "      Enlarge name, description to 255 at all database"
 387     for table in flavors images instances tenants
 388     do
 389         name_length=255
 390         [[ $table == tenants ]] || name_length=64
 391         echo -en "        $table               \r"
 392         sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
 393             "description VARCHAR(255) NULL DEFAULT NULL;"
 394     done
 395     echo -en "        hosts               \r"
 396     sql "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(64) "\
 397         "NOT NULL, CHANGE COLUMN user user VARCHAR(64) NOT NULL, CHANGE COLUMN password password VARCHAR(64) NULL "\
 398         "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN features "\
 399         "features VARCHAR(255) NULL DEFAULT NULL;"
 400     echo -en "        nets                \r"
 401     sql "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN last_error last_error "\
 402         "VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
 403     echo -en "        instances           \r"
 404     sql "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;"
 405     echo -en "        ports               \r"
 406     sql "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN switch_port switch_port "\
 407         "VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;"
 408     echo -en "        of_flows            \r"
 409     sql "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN net_id net_id VARCHAR(36) "\
 410         "NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(255) NULL DEFAULT NULL;"
 411     echo -en "        of_ports_pci_cor... \r"
 412     sql "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
 413         "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 414         "VARCHAR(64) NULL DEFAULT NULL;"
 415     echo -en "        resources_port      \r"
 416     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
 417         "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 418         "VARCHAR(64) NULL DEFAULT NULL;"
 419     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
 420         "(6, '0.6', '0.4.2', 'Enlarging name at database', '2016-02-01');"
 421 }
 422 function downgrade_from_6(){
 423     # echo "    downgrade database from version 0.6 to version 0.5"
 424     echo "      Change back name,description to shorter length at all database"
 425     for table in flavors images instances tenants
 426     do
 427          name_length=50
 428          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 429          echo -en "        $table               \r"
 430          sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
 431             "description VARCHAR(100) NULL DEFAULT NULL;"
 432     done
 433     echo -en "        hosts               \r"
 434     sql "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(36) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(36) "\
 435         "NOT NULL, CHANGE COLUMN user user VARCHAR(36) NOT NULL, CHANGE COLUMN password password VARCHAR(36) NULL "\
 436         "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL, CHANGE COLUMN features "\
 437         "features VARCHAR(50) NULL DEFAULT NULL;"
 438     echo -en "        nets                \r"
 439     sql "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(50) NOT NULL, CHANGE COLUMN last_error last_error "\
 440         "VARCHAR(200) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
 441     echo -en "        instances           \r"
 442     sql "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;"
 443     echo -en "        ports               \r"
 444     sql "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(25) NULL DEFAULT NULL, CHANGE COLUMN switch_port "\
 445         "switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;"
 446     echo -en "        of_flows            \r"
 447     sql "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN net_id net_id "\
 448         "VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(100) NULL DEFAULT NULL;"
 449     echo -en "        of_ports_pci_cor... \r"
 450     sql "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(50) NULL DEFAULT NULL, CHANGE "\
 451         "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 452         "VARCHAR(23) NULL DEFAULT NULL;"
 453     echo -en "        resources_port      \r"
 454     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL, CHANGE "\
 455         "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 456         "VARCHAR(23) NULL DEFAULT NULL;"
 457     sql "DELETE FROM schema_version WHERE version_int='6';"
 458 }
 459
 460 function upgrade_to_7(){
 461     # echo "    upgrade database from version 0.6 to version 0.7"
 462     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 463     sql "ALTER TABLE nets ADD COLUMN cidr VARCHAR(64) NULL DEFAULT NULL AFTER bind, ADD COLUMN enable_dhcp "\
 464         "ENUM('true','false') NOT NULL DEFAULT 'false' after cidr, ADD COLUMN dhcp_first_ip VARCHAR(64) NULL "\
 465         "DEFAULT NULL AFTER enable_dhcp, ADD COLUMN dhcp_last_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_first_ip;"
 466     sql "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;"
 467     sql "ALTER TABLE nets ADD COLUMN bind_net VARCHAR(36) NULL DEFAULT NULL COMMENT 'To connect with other net' "\
 468         "AFTER provider, ADD COLUMN bind_type VARCHAR(36)  NULL DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove' "\
 469         "after bind_net;"
 470     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 471         "VALUES (7, '0.7', '0.4.5', 'Add bind_net to net table', '2016-02-12');"
 472 }
 473 function downgrade_from_7(){
 474     # echo "    downgrade database from version 0.7 to version 0.6"
 475     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 476     sql "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;"
 477     sql "ALTER TABLE nets DROP COLUMN cidr, DROP COLUMN enable_dhcp, DROP COLUMN bind_net, DROP COLUMN bind_type, "\
 478         "DROP COLUMN dhcp_first_ip, DROP COLUMN dhcp_last_ip;"
 479     sql "DELETE FROM schema_version WHERE version_int = '7';"
 480 }
 481
 482 function upgrade_to_8(){
 483     # echo "    upgrade database from version 0.7 to version 0.8"
 484     echo "     add column 'checksum' to 'images'"
 485     sql "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;"
 486     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 487         "VALUES (8, '0.8', '0.4.10', 'add column checksum to images', '2016-09-30');"
 488 }
 489 function downgrade_from_8(){
 490     # echo "    downgrade database from version 0.8 to version 0.7"
 491     echo "     remove column 'checksum' from 'images'"
 492     sql "ALTER TABLE images DROP COLUMN checksum;"
 493     sql "DELETE FROM schema_version WHERE version_int = '8';"
 494 }
 495
 496 function upgrade_to_9(){
 497     # echo "    upgrade database from version 0.8 to version 0.9"
 498     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column "\
 499         "'name' to 255 in table 'flavors'"
 500     sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
 501         "VARCHAR(255) NOT NULL AFTER path;"
 502     sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;"
 503     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 504         "VALUES (9, '0.9', '0.5.1','increase length of columns path and name to 255 in table images, "\
 505         "and change length of column name to 255 in table flavors', '2017-01-10');"
 506 }
 507 function downgrade_from_9(){
 508     # echo "    downgrade database from version 0.9 to version 0.8"
 509     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 510     sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
 511         "VARCHAR(64) NOT NULL AFTER path;"
 512     sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;"
 513     sql "DELETE FROM schema_version WHERE version_int = '9';"
 514 }
 515
 516 function upgrade_to_10(){
 517     # echo "    upgrade database from version 0.9 to version 0.10"
 518     echo "     change types at 'ports'"
 519     sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs',"\
 520         "'controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;"
 521     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 522         "VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"
 523 }
 524 function downgrade_from_10(){
 525     # echo "    downgrade database from version 0.10 to version 0.9"
 526     echo "     change back types at 'ports'"
 527     sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') "\
 528         "NOT NULL DEFAULT 'instance:bridge' AFTER status;"
 529     sql "DELETE FROM schema_version WHERE version_int = '10';"
 530 }
 531
 532 function upgrade_to_11(){
 533     # echo "    upgrade database from version 0.10 to version 0.11"
 534     echo "    Add gateway_ip colum to 'nets'"
 535     sql "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;"
 536     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 537         "VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"
 538 }
 539 function downgrade_from_11(){
 540     # echo "    downgrade database from version 0.11 to version 0.10"
 541     echo "    Delete gateway_ip colum from 'nets'"
 542     sql "ALTER TABLE nets DROP COLUMN gateway_ip;"
 543     sql "DELETE FROM schema_version WHERE version_int = '11';"
 544 }
 545 function upgrade_to_12(){
 546     # echo "    upgrade database from version 0.11 to version 0.12"
 547     echo "    Create of_controller table "
 548     sql "CREATE TABLE ofcs (
 549         uuid VARCHAR(36) NOT NULL,
 550         name VARCHAR(255) NOT NULL,
 551         dpid VARCHAR(64) NOT NULL,
 552         ip VARCHAR(64) NOT NULL,
 553         port INT(5) NOT NULL,
 554         type VARCHAR(64) NOT NULL,
 555         version VARCHAR(12) NULL DEFAULT NULL,
 556         user VARCHAR(64) NULL DEFAULT NULL,
 557         password VARCHAR(64) NULL DEFAULT NULL,
 558         PRIMARY KEY (uuid)
 559         )
 560         COLLATE='utf8_general_ci'
 561         ENGINE=InnoDB;"
 562     echo "    Modify user_at for uuids table"
 563     sql "ALTER TABLE uuids  CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL "\
 564         "COMMENT 'Table that uses this UUID' ;"
 565     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 566         "VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"
 567 }
 568
 569 function downgrade_from_12(){
 570     # echo "    downgrade database from version 0.12 to version 0.11"
 571     echo "    Delete ofcs table"
 572     sql "DROP TABLE ofcs;"
 573     sql "ALTER TABLE uuids  CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', "\
 574         "'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"
 575     sql "DELETE FROM schema_version WHERE version_int = '12';"
 576 }
 577
 578 function upgrade_to_13(){
 579     # echo "    upgrade database from version 0.12 to version 0.13"
 580     echo "    Create of_port_mapings table "
 581     sql "CREATE TABLE of_port_mappings (
 582         uuid VARCHAR(36) NOT NULL,
 583         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 584         region VARCHAR(64) NULL DEFAULT NULL,
 585         compute_node VARCHAR(64) NULL DEFAULT NULL,
 586         pci VARCHAR(50) NULL DEFAULT NULL,
 587         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 588         switch_port VARCHAR(64) NULL DEFAULT NULL,
 589         switch_mac CHAR(18) NULL DEFAULT NULL,
 590         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 591         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 592         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 593         INDEX FK_of_port_mappings_ofcs (ofc_id),
 594         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE
 595         ON DELETE CASCADE)
 596         COLLATE='utf8_general_ci'
 597         ENGINE=InnoDB;"
 598     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 599         "VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"
 600 }
 601
 602 function downgrade_from_13(){
 603     # echo "    downgrade database from version 0.13 to version 0.12"
 604     echo "    Delete of_port_mappings table"
 605     sql "DROP TABLE of_port_mappings;"
 606     sql "DELETE FROM schema_version WHERE version_int = '13';"
 607 }
 608
 609 function upgrade_to_14(){
 610     # echo "    upgrade database from version 0.13 to version 0.14"
 611     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 612     sql "ALTER TABLE ports
 613         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 614         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 615         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
 616     sql "ALTER TABLE resources_port
 617         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 618         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 619         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
 620     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 621         "VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"
 622 }
 623
 624 function downgrade_from_14(){
 625     # echo "    downgrade database from version 0.14 to version 0.13"
 626     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 627     sql "ALTER TABLE ports DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_port_ofc_id;"
 628     sql "ALTER TABLE resources_port DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_resource_ofc_id;"
 629     sql "DELETE FROM schema_version WHERE version_int = '14';"
 630 }
 631
 632 function upgrade_to_15(){
 633     # echo "    upgrade database from version 0.14 to version 0.15"
 634     echo "    Add ofc_id colum to 'of_flows'"
 635     sql "ALTER TABLE of_flows ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT "\
 636         "FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 637     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 638         "VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"
 639 }
 640
 641 function downgrade_from_15(){
 642     # echo "    downgrade database from version 0.15 to version 0.14"
 643     echo "    Delete ofc_id to 'of_flows'"
 644     sql "ALTER TABLE of_flows DROP COLUMN ofc_id, DROP FOREIGN KEY FK_of_flows_ofcs;"
 645     sql "DELETE FROM schema_version WHERE version_int = '15';"
 646 }
 647
 648
 649 function upgrade_to_16(){
 650     # echo "    upgrade database from version 0.15 to version 0.16"
 651     echo "    Add last_error and status colum to 'ofcs'"
 652     sql "ALTER TABLE ofcs ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password, "\
 653             "ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"
 654     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 655         "VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"
 656 }
 657
 658 function downgrade_from_16(){
 659     # echo "    downgrade database from version 0.16 to version 0.15"
 660     echo "    Delete last_error and status colum to 'ofcs'"
 661     sql "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;"
 662     sql "DELETE FROM schema_version WHERE version_int = '16';"
 663 }
 664
 665 function upgrade_to_17(){
 666     # echo "    upgrade database from version 0.16 to version 0.17"
 667     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 668     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 669         "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);"
 670     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 671         "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);"
 672     echo "    Add nets_with_same_vlan to table ofcs"
 673     sql "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;"
 674     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 675         "VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"
 676 }
 677
 678 function downgrade_from_17(){
 679     # echo "    downgrade database from version 0.17 to version 0.16"
 680     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 681     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 682         "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);"
 683     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 684         "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);"
 685     echo "    Remove nets_with_same_vlan from table ofcs"
 686     sql "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;"
 687     sql "DELETE FROM schema_version WHERE version_int = '17';"
 688 }
 689
 690 function upgrade_to_18(){
 691     echo "    Add 'region' at 'nets' and change unique index vlan+region"
 692     sql "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, DROP INDEX type_vlan;"
 693     echo "    Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
 694     sql "UPDATE nets set region='__OVS__' where provider like 'OVS%';"
 695     sql "UPDATE nets set region='__DATA__' where type='data' or type='ptp';"
 696     echo "    Create new index region_vlan at nets"
 697         sql "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);"
 698     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 699         "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"
 700 }
 701
 702 function downgrade_from_18(){
 703     echo "    Delete 'region' at 'nets' and change back unique index vlan+type"
 704     sql "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;"
 705     echo "    Create back index type_vlan at nets"
 706         sql "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);"
 707     sql "DELETE FROM schema_version WHERE version_int = '18';"
 708 }
 709
 710 function upgrade_to_19(){
 711     echo "    Add 'keyfile' to 'hosts'"
 712     sql "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;"
 713     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 714         "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"
 715 }
 716
 717 function downgrade_from_19(){
 718     echo "    Delete 'keyfile' from 'hosts'"
 719     sql "ALTER TABLE hosts DROP COLUMN keyfile;"
 720     sql "DELETE FROM schema_version WHERE version_int = '19';"
 721 }
 722
 723 function upgrade_to_20(){
 724     echo "    Add 'image_size' to 'instance_devices'"
 725     sql "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;"
 726     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 727         "VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"
 728 }
 729
 730 function downgrade_from_20(){
 731     echo "    Delete 'image_size' from 'instance_devices'"
 732     sql "ALTER TABLE instance_devices DROP COLUMN image_size;"
 733     sql "DELETE FROM schema_version WHERE version_int = '20';"
 734 }
 735
 736 function upgrade_to_21(){
 737     echo "    Add 'routes', 'links' and 'dns' to 'nets'"
 738     sql "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip, "\
 739         "ADD COLUMN links TEXT(2000)  NULL AFTER dns, "\
 740         "ADD COLUMN routes TEXT(2000)  NULL AFTER links;"
 741     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 742         "VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"
 743 }
 744
 745 function downgrade_from_21(){
 746     echo "    Delete 'routes', 'links' and 'dns' to 'nets'"
 747     sql "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;"
 748     sql "DELETE FROM schema_version WHERE version_int = '21';"
 749 }
 750
 751 function upgrade_to_22(){
 752     echo "    Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
 753     sql "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 754     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 755         "VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"
 756 }
 757
 758 function downgrade_from_22(){
 759     echo "    Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
 760     sql "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 761     sql "DELETE FROM schema_version WHERE version_int = '22';"
 762 }
 763
 764 function upgrade_to_23(){
 765     echo "    Add 'hypervisor' and 'os_type' column to 'instances' table"
 766     sql "ALTER TABLE instances ADD COLUMN hypervisor enum('kvm','xen-unik','xenhvm') NOT NULL DEFAULT 'kvm' AFTER flavor_id;"
 767     sql "ALTER TABLE instances ADD COLUMN os_image_type VARCHAR(24) NOT NULL DEFAULT 'other' AFTER hypervisor;"
 768     echo "    Add 'hypervisors' column to 'hosts' table"
 769     sql "ALTER TABLE hosts ADD COLUMN hypervisors VARCHAR(255) NOT NULL DEFAULT 'kvm' AFTER features;"
 770     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 771         "VALUES (23, '0.23', '0.5.24', 'Add hypervisor, os_type to instances and add hypervisors to hosts', '2018-03-20');"
 772 }
 773
 774 function downgrade_from_23(){
 775     echo "    Remove 'hypervisor' and 'os_type' column from 'instances' table"
 776     sql "ALTER TABLE instances DROP COLUMN hypervisor;"
 777     sql "ALTER TABLE instances DROP COLUMN os_image_type;"
 778     echo "    Remove 'hypervisors' column from 'hosts' table"
 779     sql "ALTER TABLE hosts DROP COLUMN hypervisors;"
 780     sql "DELETE FROM schema_version WHERE version_int = '23';"
 781 }
 782
 783 # TODO ... put functions here
 784
 785
 786 function del_schema_version_process()
 787 {
 788     echo "DELETE FROM schema_version WHERE version_int='0';" | $DBCMD ||
 789         ! echo "    ERROR writing on schema_version" >&2 || exit 1
 790 }
 791
 792 function set_schema_version_process()
 793 {
 794     echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
 795         "(0, '0.0', '0.0.0', 'migration from $DATABASE_VER_NUM to $DB_VERSION backup: $BACKUP_FILE',"\
 796         "'$(date +%Y-%m-%d)');" | $DBCMD ||
 797         ! echo  "    Cannot set database at migration process writing into schema_version" >&2 || exit 1
 798
 799 }
 800
 801 function rollback_db()
 802 {
 803     if echo $DATABASE_PROCESS | grep -q init ; then   # Empty database. No backup needed
 804         echo "    Aborted! Rollback database not needed" && exit 1
 805     else   # migration a non empty database or Recovering a migration process
 806         cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM && echo "    Aborted! Rollback database OK" &&
 807             del_schema_version_process && rm -f "$BACKUP_FILE" && exit 1
 808         echo "    Aborted! Rollback database FAIL" && exit 1
 809     fi
 810 }
 811
 812 function sql()    # send a sql command
 813 {
 814     echo "$*" | $DBCMD || ! echo "    ERROR with command '$*'" || rollback_db
 815     return 0
 816 }
 817
 818 function migrate()
 819 {
 820     #UPGRADE DATABASE step by step
 821     while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 822     do
 823         echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 824         DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 825         upgrade_to_${DATABASE_VER_NUM}
 826         #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 827         #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 828         #$FILE_ || exit -1  # if fail return
 829     done
 830
 831     #DOWNGRADE DATABASE step by step
 832     while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 833     do
 834         echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 835         #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 836         #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 837         #$FILE_ || exit -1  # if fail return
 838         downgrade_from_${DATABASE_VER_NUM}
 839         DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 840     done
 841 }
 842
 843
 844 # check if current database is ok
 845 function check_migration_needed()
 846 {
 847     DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2` ||
 848     ! echo "    ERROR cannot read from schema_version" || exit 1
 849
 850     if [[ -z "$DATABASE_VER_NUM" ]] || [[ "$DATABASE_VER_NUM" -lt 0 ]] || [[ "$DATABASE_VER_NUM" -gt 100 ]] ; then
 851         echo "    Error can not get database version ($DATABASE_VER_NUM?)" >&2
 852         exit 1
 853     fi
 854
 855     [[ $DB_VERSION -eq $DATABASE_VER_NUM ]] && echo "    current database version '$DATABASE_VER_NUM' is ok" && return 1
 856     [[ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ]] &&
 857         echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
 858         exit 1
 859     return 0
 860 }
 861
 862 DATABASE_PROCESS=`echo "select comments from schema_version where version_int=0;" | $DBCMD | tail -n+2` ||
 863     ! echo "    ERROR cannot read from schema_version" || exit 1
 864 if [[ -z "$DATABASE_PROCESS" ]] ; then  # migration a non empty database
 865     check_migration_needed || exit 0
 866     # Create a backup database content
 867     [[ -n "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q  "${BACKUP_DIR}/backupdb.XXXXXX.sql")"
 868     [[ -z "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql")"
 869     mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $BACKUP_FILE ||
 870         ! echo "Cannot create Backup file '$BACKUP_FILE'" >&2 || exit 1
 871     echo "    Backup file '$BACKUP_FILE' created"
 872     # Set schema version
 873     set_schema_version_process
 874     migrate
 875     del_schema_version_process
 876     rm -f "$BACKUP_FILE"
 877 elif echo $DATABASE_PROCESS | grep -q init ; then   # Empty database. No backup needed
 878     echo "    Migrating an empty database"
 879     if check_migration_needed ; then
 880         migrate
 881     fi
 882     del_schema_version_process
 883
 884 else  # Recover Migration process
 885     BACKUP_FILE=${DATABASE_PROCESS##*backup: }
 886     [[ -f "$BACKUP_FILE" ]] || ! echo "Previous migration process fail and cannot recover backup file '$BACKUP_FILE'" >&2 ||
 887         exit 1
 888     echo "    Previous migration was killed. Restoring database from rollback file'$BACKUP_FILE'"
 889     cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM || ! echo "    Cannot load backup file '$BACKUP_FILE'" >&2 || exit 1
 890     if check_migration_needed ; then
 891         set_schema_version_process
 892         migrate
 893     fi
 894     del_schema_version_process
 895     rm -f "$BACKUP_FILE"
 896 fi
 897 exit 0
 898
 899 #echo done
 900