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=22
  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 #[ $OPENVIM_VER_NUM -ge 5021 ] && DATABASE_TARGET_VER_NUM=22   #0.5.21  => 22
 193 # TODO ... put next versions here
 194
 195 function upgrade_to_1(){
 196     # echo "    upgrade database from version 0.0 to version 0.1"
 197     echo "      CREATE TABLE \`schema_version\`"
 198     sql "CREATE TABLE \`schema_version\` (
 199         \`version_int\` INT NOT NULL COMMENT 'version as a number. Must not contain gaps',
 200         \`version\` VARCHAR(20) NOT NULL COMMENT 'version as a text',
 201         \`openvim_ver\` VARCHAR(20) NOT NULL COMMENT 'openvim version',
 202         \`comments\` VARCHAR(2000) NULL COMMENT 'changes to database',
 203         \`date\` DATE NULL,
 204         PRIMARY KEY (\`version_int\`)
 205         )
 206         COMMENT='database schema control version'
 207         COLLATE='utf8_general_ci'
 208         ENGINE=InnoDB;"
 209     sql "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`) "\
 210             "VALUES (1, '0.1', '0.2.00', 'insert schema_version; alter nets with last_error column', '2015-05-05');"
 211     echo "      ALTER TABLE \`nets\`, ADD COLUMN \`last_error\`"
 212     sql "ALTER TABLE \`nets\` ADD COLUMN \`last_error\` VARCHAR(200) NULL AFTER \`status\`;"
 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     sql "ALTER TABLE \`nets\` DROP COLUMN \`last_error\`;"
 218     echo "      DROP TABLE \`schema_version\`"
 219     sql "DROP TABLE \`schema_version\`;"
 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         sql "ALTER TABLE \`${table}\` "\
 227             "ADD COLUMN \`switch_dpid\` CHAR(23) NULL DEFAULT NULL AFTER \`switch_port\`; "
 228         sql "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL;"
 229         [ $table == of_ports_pci_correspondence ] ||
 230             sql "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port "\
 231                 "(vlan, switch_port, switch_dpid);"
 232     done
 233     echo "      UPDATE procedure UpdateSwitchPort"
 234     sql "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 235         delimiter //
 236         CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 237         COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 238         BEGIN
 239             #DELETES switch_port entry before writing, because if not it fails for key constrains
 240             UPDATE ports
 241             RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 242             INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 243             INNER JOIN numas on RP.numa_id=numas.id
 244             INNER JOIN hosts on numas.host_id=hosts.uuid
 245             INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 246             SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 247             #write switch_port into resources_port and ports
 248             UPDATE ports
 249             RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 250             INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 251             INNER JOIN numas on RP.numa_id=numas.id
 252             INNER JOIN hosts on numas.host_id=hosts.uuid
 253             INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 254             SET ports.switch_port=PC.switch_port, ports.switch_dpid=PC.switch_dpid, RP.switch_port=PC.switch_port, "\
 255                 "RP.switch_dpid=PC.switch_dpid;
 256         END//
 257         delimiter ;"
 258     sql "INSERT INTO \`schema_version\` (\`version_int\`, \`version\`, \`openvim_ver\`, \`comments\`, \`date\`) "\
 259             "VALUES (2, '0.2', '0.2.03', 'update Procedure UpdateSwitchPort', '2015-05-06');"
 260 }
 261 function upgrade_to_3(){
 262     # echo "    upgrade database from version 0.2 to version 0.3"
 263     echo "     change size of source_name at table resources_port"
 264     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL AFTER port_id;"
 265     echo "     CREATE PROCEDURE GetAllAvailablePorts"
 266     sql "delimiter //
 267         CREATE PROCEDURE GetAllAvailablePorts(IN Numa INT) CONTAINS SQL SQL SECURITY INVOKER
 268         COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 269         BEGIN
 270         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as
 271             availableSRIOV, switch_port, mac
 272         FROM
 273         (
 274            SELECT id as port_id, Mbps, pci, switch_port, mac
 275            FROM resources_port
 276             WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 277         ) as A
 278         INNER JOIN
 279         (
 280            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 281             FROM resources_port
 282             WHERE numa_id = Numa AND status = 'ok'
 283             GROUP BY root_id
 284         ) as B
 285         ON A.port_id = B.root_id
 286         LEFT JOIN
 287         (
 288            SELECT root_id,  COUNT(id) as usedSRIOV
 289             FROM resources_port
 290             WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 291             GROUP BY root_id
 292         ) as C
 293         ON A.port_id = C.root_id
 294         ORDER BY Mbps_free, availableSRIOV, pci;
 295         END//
 296         delimiter ;"
 297     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 298         "VALUES (3, '0.3', '0.2.5', 'New Procedure GetAllAvailablePorts', '2015-07-09');"
 299 }
 300
 301 function upgrade_to_4(){
 302     # echo "    upgrade database from version 0.3 to version 0.4"
 303     echo "     remove unique VLAN index at 'resources_port', 'ports'"
 304     sql "ALTER TABLE resources_port DROP INDEX vlan_switch_port;"
 305     sql "ALTER TABLE ports          DROP INDEX vlan_switch_port;"
 306     echo "     change table 'ports'"
 307     sql "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL COMMENT 'driver model for bridge "\
 308         "ifaces; PF,VF,VFnotShared for data ifaces' AFTER mac;"
 309     sql "ALTER TABLE ports DROP COLUMN vlan_changed;"
 310     sql "ALTER TABLE resources_port DROP COLUMN vlan;"
 311     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 312         "VALUES (4, '0.4', '0.3.1', 'Remove unique index VLAN at resources_port', '2015-09-04');"
 313 }
 314
 315 function upgrade_to_X(){
 316     #TODO, this change of foreign key does not work
 317     # echo "    upgrade database from version 0.X to version 0.X"
 318     sql "ALTER TABLE instances DROP FOREIGN KEY FK_instances_flavors, DROP INDEX FK_instances_flavors, "\
 319         "DROP FOREIGN KEY FK_instances_images, DROP INDEX FK_instances_flavors,;"
 320     sql "ALTER TABLE instances ADD CONSTRAINT FK_instances_flavors FOREIGN KEY (flavor_id, tenant_id) "\
 321         "REFERENCES tenants_flavors (flavor_id, tenant_id), ADD CONSTRAINT FK_instances_images FOREIGN KEY "\
 322         "(image_id, tenant_id) REFERENCES tenants_images (image_id, tenant_id);"
 323 }
 324
 325 function downgrade_from_2(){
 326     # echo "    downgrade database from version 0.2 to version 0.1"
 327     echo "      UPDATE procedure UpdateSwitchPort"
 328     sql "DROP PROCEDURE IF EXISTS UpdateSwitchPort;
 329         delimiter //
 330         CREATE PROCEDURE UpdateSwitchPort() MODIFIES SQL DATA SQL SECURITY INVOKER
 331         BEGIN
 332         UPDATE
 333             resources_port INNER JOIN (
 334                 SELECT resources_port.id,KK.switch_port
 335                 FROM resources_port INNER JOIN numas on resources_port.numa_id=numas.id
 336                     INNER JOIN hosts on numas.host_id=hosts.uuid
 337                     INNER JOIN of_ports_pci_correspondence as KK on hosts.ip_name=KK.ip_name and resources_port.pci=KK.pci
 338                 ) as TABLA
 339             ON  resources_port.root_id=TABLA.id
 340         SET resources_port.switch_port=TABLA.switch_port
 341         WHERE resources_port.root_id=TABLA.id;
 342         END//
 343         delimiter ;"
 344     echo "      ALTER TABLE \`of_ports_pci_correspondence\` \`resources_port\` \`ports\` DROP COLUMN \`switch_dpid\`"
 345     for table in of_ports_pci_correspondence resources_port ports
 346     do
 347         [ $table == of_ports_pci_correspondence ] ||
 348             sql "ALTER TABLE ${table} DROP INDEX vlan_switch_port, ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port);"
 349         sql "ALTER TABLE \`${table}\` DROP COLUMN \`switch_dpid\`;"
 350         switch_port_size=12
 351         [ $table == of_ports_pci_correspondence ] && switch_port_size=50
 352         sql "ALTER TABLE ${table} CHANGE COLUMN switch_port switch_port VARCHAR(${switch_port_size}) NULL DEFAULT NULL;"
 353     done
 354     sql "DELETE FROM \`schema_version\` WHERE \`version_int\` = '2';"
 355 }
 356 function downgrade_from_3(){
 357     # echo "    downgrade database from version 0.3 to version 0.2"
 358     echo "     change back size of source_name at table resources_port"
 359     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(20) NULL DEFAULT NULL AFTER port_id;"
 360     echo "      DROP PROCEDURE GetAllAvailablePorts"
 361     sql "DROP PROCEDURE GetAllAvailablePorts;"
 362     sql "DELETE FROM schema_version WHERE version_int = '3';"
 363 }
 364 function downgrade_from_4(){
 365     # echo "    downgrade database from version 0.4 to version 0.3"
 366     echo "     adding back unique index VLAN at 'resources_port','ports'"
 367     sql "ALTER TABLE resources_port ADD COLUMN vlan SMALLINT(5) UNSIGNED NULL DEFAULT NULL  AFTER Mbps_used;"
 368     sql "UPDATE resources_port SET vlan= 99+id-root_id WHERE id != root_id;"
 369     sql "ALTER TABLE resources_port ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
 370     sql "ALTER TABLE    ports ADD UNIQUE INDEX vlan_switch_port (vlan, switch_port, switch_dpid);"
 371     echo "     change back table 'ports'"
 372     sql "ALTER TABLE ports CHANGE COLUMN model model VARCHAR(12) NULL DEFAULT NULL AFTER mac;"
 373     sql "ALTER TABLE ports ADD COLUMN vlan_changed SMALLINT(5) NULL DEFAULT NULL COMMENT '!=NULL when original vlan "\
 374         "have been changed to match a pmp net with all ports in the same vlan' AFTER switch_port;"
 375     sql "DELETE FROM schema_version WHERE version_int = '4';"
 376 }
 377
 378
 379 function upgrade_to_5(){
 380     # echo "    upgrade database from version 0.4 to version 0.5"
 381     echo "     add 'ip_address' to ports'"
 382     sql "ALTER TABLE ports ADD COLUMN ip_address VARCHAR(64) NULL DEFAULT NULL AFTER mac;"
 383     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 384         "VALUES (5, '0.5', '0.4.1', 'Add ip_address to ports', '2015-09-04');"
 385 }
 386 function downgrade_from_5(){
 387     # echo "    downgrade database from version 0.5 to version 0.4"
 388     echo "     removing 'ip_address' from 'ports'"
 389     sql "ALTER TABLE ports DROP COLUMN ip_address;"
 390     sql "DELETE FROM schema_version WHERE version_int = '5';"
 391 }
 392
 393 function upgrade_to_6(){
 394     # echo "    upgrade database from version 0.5 to version 0.6"
 395     echo "      Enlarge name, description to 255 at all database"
 396     for table in flavors images instances tenants
 397     do
 398         name_length=255
 399         [[ $table == tenants ]] || name_length=64
 400         echo -en "        $table               \r"
 401         sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
 402             "description VARCHAR(255) NULL DEFAULT NULL;"
 403     done
 404     echo -en "        hosts               \r"
 405     sql "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(64) "\
 406         "NOT NULL, CHANGE COLUMN user user VARCHAR(64) NOT NULL, CHANGE COLUMN password password VARCHAR(64) NULL "\
 407         "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN features "\
 408         "features VARCHAR(255) NULL DEFAULT NULL;"
 409     echo -en "        nets                \r"
 410     sql "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(255) NOT NULL, CHANGE COLUMN last_error last_error "\
 411         "VARCHAR(255) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
 412     echo -en "        instances           \r"
 413     sql "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(255) NULL DEFAULT NULL;"
 414     echo -en "        ports               \r"
 415     sql "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN switch_port switch_port "\
 416         "VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(64) NULL DEFAULT NULL;"
 417     echo -en "        of_flows            \r"
 418     sql "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(64) NOT NULL, CHANGE COLUMN net_id net_id VARCHAR(36) "\
 419         "NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(255) NULL DEFAULT NULL;"
 420     echo -en "        of_ports_pci_cor... \r"
 421     sql "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
 422         "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 423         "VARCHAR(64) NULL DEFAULT NULL;"
 424     echo -en "        resources_port      \r"
 425     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(64) NULL DEFAULT NULL, CHANGE "\
 426         "COLUMN switch_port switch_port VARCHAR(64) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 427         "VARCHAR(64) NULL DEFAULT NULL;"
 428     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
 429         "(6, '0.6', '0.4.2', 'Enlarging name at database', '2016-02-01');"
 430 }
 431 function downgrade_from_6(){
 432     # echo "    downgrade database from version 0.6 to version 0.5"
 433     echo "      Change back name,description to shorter length at all database"
 434     for table in flavors images instances tenants
 435     do
 436          name_length=50
 437          [[ $table == flavors ]] || [[ $table == images ]] || name_length=36
 438          echo -en "        $table               \r"
 439          sql "ALTER TABLE $table CHANGE COLUMN name name VARCHAR($name_length) NOT NULL, CHANGE COLUMN description "\
 440             "description VARCHAR(100) NULL DEFAULT NULL;"
 441     done
 442     echo -en "        hosts               \r"
 443     sql "ALTER TABLE hosts CHANGE COLUMN name name VARCHAR(36) NOT NULL, CHANGE COLUMN ip_name ip_name VARCHAR(36) "\
 444         "NOT NULL, CHANGE COLUMN user user VARCHAR(36) NOT NULL, CHANGE COLUMN password password VARCHAR(36) NULL "\
 445         "DEFAULT NULL, CHANGE COLUMN description description VARCHAR(100) NULL DEFAULT NULL, CHANGE COLUMN features "\
 446         "features VARCHAR(50) NULL DEFAULT NULL;"
 447     echo -en "        nets                \r"
 448     sql "ALTER TABLE nets CHANGE COLUMN name name VARCHAR(50) NOT NULL, CHANGE COLUMN last_error last_error "\
 449         "VARCHAR(200) NULL DEFAULT NULL, CHANGE COLUMN bind bind VARCHAR(36) NULL DEFAULT NULL;"
 450     echo -en "        instances           \r"
 451     sql "ALTER TABLE instances CHANGE COLUMN last_error last_error VARCHAR(200) NULL DEFAULT NULL;"
 452     echo -en "        ports               \r"
 453     sql "ALTER TABLE ports CHANGE COLUMN name name VARCHAR(25) NULL DEFAULT NULL, CHANGE COLUMN switch_port "\
 454         "switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid VARCHAR(23) NULL DEFAULT NULL;"
 455     echo -en "        of_flows            \r"
 456     sql "ALTER TABLE of_flows CHANGE COLUMN name name VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN net_id net_id "\
 457         "VARCHAR(50) NULL DEFAULT NULL, CHANGE COLUMN actions actions VARCHAR(100) NULL DEFAULT NULL;"
 458     echo -en "        of_ports_pci_cor... \r"
 459     sql "ALTER TABLE of_ports_pci_correspondence CHANGE COLUMN ip_name ip_name VARCHAR(50) NULL DEFAULT NULL, CHANGE "\
 460         "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 461         "VARCHAR(23) NULL DEFAULT NULL;"
 462     echo -en "        resources_port      \r"
 463     sql "ALTER TABLE resources_port CHANGE COLUMN source_name source_name VARCHAR(24) NULL DEFAULT NULL, CHANGE "\
 464         "COLUMN switch_port switch_port VARCHAR(24) NULL DEFAULT NULL, CHANGE COLUMN switch_dpid switch_dpid "\
 465         "VARCHAR(23) NULL DEFAULT NULL;"
 466     sql "DELETE FROM schema_version WHERE version_int='6';"
 467 }
 468
 469 function upgrade_to_7(){
 470     # echo "    upgrade database from version 0.6 to version 0.7"
 471     echo "     add 'bind_net','bind_type','cidr','enable_dhcp' to 'nets'"
 472     sql "ALTER TABLE nets ADD COLUMN cidr VARCHAR(64) NULL DEFAULT NULL AFTER bind, ADD COLUMN enable_dhcp "\
 473         "ENUM('true','false') NOT NULL DEFAULT 'false' after cidr, ADD COLUMN dhcp_first_ip VARCHAR(64) NULL "\
 474         "DEFAULT NULL AFTER enable_dhcp, ADD COLUMN dhcp_last_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_first_ip;"
 475     sql "ALTER TABLE nets CHANGE COLUMN bind provider VARCHAR(36) NULL DEFAULT NULL AFTER vlan;"
 476     sql "ALTER TABLE nets ADD COLUMN bind_net VARCHAR(36) NULL DEFAULT NULL COMMENT 'To connect with other net' "\
 477         "AFTER provider, ADD COLUMN bind_type VARCHAR(36)  NULL DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove' "\
 478         "after bind_net;"
 479     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 480         "VALUES (7, '0.7', '0.4.5', 'Add bind_net to net table', '2016-02-12');"
 481 }
 482 function downgrade_from_7(){
 483     # echo "    downgrade database from version 0.7 to version 0.6"
 484     echo "     removing 'bind_net','bind_type','cidr','enable_dhcp' from 'nets'"
 485     sql "ALTER TABLE nets CHANGE COLUMN provider bind NULL DEFAULT NULL AFTER vlan;"
 486     sql "ALTER TABLE nets DROP COLUMN cidr, DROP COLUMN enable_dhcp, DROP COLUMN bind_net, DROP COLUMN bind_type, "\
 487         "DROP COLUMN dhcp_first_ip, DROP COLUMN dhcp_last_ip;"
 488     sql "DELETE FROM schema_version WHERE version_int = '7';"
 489 }
 490
 491 function upgrade_to_8(){
 492     # echo "    upgrade database from version 0.7 to version 0.8"
 493     echo "     add column 'checksum' to 'images'"
 494     sql "ALTER TABLE images ADD COLUMN checksum VARCHAR(32) NULL AFTER name;"
 495     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 496         "VALUES (8, '0.8', '0.4.10', 'add column checksum to images', '2016-09-30');"
 497 }
 498 function downgrade_from_8(){
 499     # echo "    downgrade database from version 0.8 to version 0.7"
 500     echo "     remove column 'checksum' from 'images'"
 501     sql "ALTER TABLE images DROP COLUMN checksum;"
 502     sql "DELETE FROM schema_version WHERE version_int = '8';"
 503 }
 504
 505 function upgrade_to_9(){
 506     # echo "    upgrade database from version 0.8 to version 0.9"
 507     echo "     change length of columns 'path' and 'name' to 255 in table 'images', and change length of column "\
 508         "'name' to 255 in table 'flavors'"
 509     sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
 510         "VARCHAR(255) NOT NULL AFTER path;"
 511     sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;"
 512     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 513         "VALUES (9, '0.9', '0.5.1','increase length of columns path and name to 255 in table images, "\
 514         "and change length of column name to 255 in table flavors', '2017-01-10');"
 515 }
 516 function downgrade_from_9(){
 517     # echo "    downgrade database from version 0.9 to version 0.8"
 518     echo "     change length of columns 'path' and 'name' to 100 and 64 in table 'images'"
 519     sql "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name "\
 520         "VARCHAR(64) NOT NULL AFTER path;"
 521     sql "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;"
 522     sql "DELETE FROM schema_version WHERE version_int = '9';"
 523 }
 524
 525 function upgrade_to_10(){
 526     # echo "    upgrade database from version 0.9 to version 0.10"
 527     echo "     change types at 'ports'"
 528     sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs',"\
 529         "'controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;"
 530     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 531         "VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"
 532 }
 533 function downgrade_from_10(){
 534     # echo "    downgrade database from version 0.10 to version 0.9"
 535     echo "     change back types at 'ports'"
 536     sql "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') "\
 537         "NOT NULL DEFAULT 'instance:bridge' AFTER status;"
 538     sql "DELETE FROM schema_version WHERE version_int = '10';"
 539 }
 540
 541 function upgrade_to_11(){
 542     # echo "    upgrade database from version 0.10 to version 0.11"
 543     echo "    Add gateway_ip colum to 'nets'"
 544     sql "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;"
 545     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 546         "VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"
 547 }
 548 function downgrade_from_11(){
 549     # echo "    downgrade database from version 0.11 to version 0.10"
 550     echo "    Delete gateway_ip colum from 'nets'"
 551     sql "ALTER TABLE nets DROP COLUMN gateway_ip;"
 552     sql "DELETE FROM schema_version WHERE version_int = '11';"
 553 }
 554 function upgrade_to_12(){
 555     # echo "    upgrade database from version 0.11 to version 0.12"
 556     echo "    Create of_controller table "
 557     sql "CREATE TABLE ofcs (
 558         uuid VARCHAR(36) NOT NULL,
 559         name VARCHAR(255) NOT NULL,
 560         dpid VARCHAR(64) NOT NULL,
 561         ip VARCHAR(64) NOT NULL,
 562         port INT(5) NOT NULL,
 563         type VARCHAR(64) NOT NULL,
 564         version VARCHAR(12) NULL DEFAULT NULL,
 565         user VARCHAR(64) NULL DEFAULT NULL,
 566         password VARCHAR(64) NULL DEFAULT NULL,
 567         PRIMARY KEY (uuid)
 568         )
 569         COLLATE='utf8_general_ci'
 570         ENGINE=InnoDB;"
 571     echo "    Modify user_at for uuids table"
 572     sql "ALTER TABLE uuids  CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL "\
 573         "COMMENT 'Table that uses this UUID' ;"
 574     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 575         "VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"
 576 }
 577
 578 function downgrade_from_12(){
 579     # echo "    downgrade database from version 0.12 to version 0.11"
 580     echo "    Delete ofcs table"
 581     sql "DROP TABLE ofcs;"
 582     sql "ALTER TABLE uuids  CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', "\
 583         "'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"
 584     sql "DELETE FROM schema_version WHERE version_int = '12';"
 585 }
 586
 587 function upgrade_to_13(){
 588     # echo "    upgrade database from version 0.12 to version 0.13"
 589     echo "    Create of_port_mapings table "
 590     sql "CREATE TABLE of_port_mappings (
 591         uuid VARCHAR(36) NOT NULL,
 592         ofc_id VARCHAR(36) NULL DEFAULT NULL,
 593         region VARCHAR(64) NULL DEFAULT NULL,
 594         compute_node VARCHAR(64) NULL DEFAULT NULL,
 595         pci VARCHAR(50) NULL DEFAULT NULL,
 596         switch_dpid VARCHAR(64) NULL DEFAULT NULL,
 597         switch_port VARCHAR(64) NULL DEFAULT NULL,
 598         switch_mac CHAR(18) NULL DEFAULT NULL,
 599         UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
 600         UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
 601         UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
 602         INDEX FK_of_port_mappings_ofcs (ofc_id),
 603         CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE
 604         ON DELETE CASCADE)
 605         COLLATE='utf8_general_ci'
 606         ENGINE=InnoDB;"
 607     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 608         "VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"
 609 }
 610
 611 function downgrade_from_13(){
 612     # echo "    downgrade database from version 0.13 to version 0.12"
 613     echo "    Delete of_port_mappings table"
 614     sql "DROP TABLE of_port_mappings;"
 615     sql "DELETE FROM schema_version WHERE version_int = '13';"
 616 }
 617
 618 function upgrade_to_14(){
 619     # echo "    upgrade database from version 0.13 to version 0.14"
 620     echo "    Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
 621     sql "ALTER TABLE ports
 622         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 623         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 624         ADD CONSTRAINT  FK_port_ofc_id  FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
 625     sql "ALTER TABLE resources_port
 626         ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
 627         ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
 628         ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
 629     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 630         "VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"
 631 }
 632
 633 function downgrade_from_14(){
 634     # echo "    downgrade database from version 0.14 to version 0.13"
 635     echo "    Delete switch_mac, ofc_id colum to 'ports'"
 636     sql "ALTER TABLE ports DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_port_ofc_id;"
 637     sql "ALTER TABLE resources_port DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_resource_ofc_id;"
 638     sql "DELETE FROM schema_version WHERE version_int = '14';"
 639 }
 640
 641 function upgrade_to_15(){
 642     # echo "    upgrade database from version 0.14 to version 0.15"
 643     echo "    Add ofc_id colum to 'of_flows'"
 644     sql "ALTER TABLE of_flows ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT "\
 645         "FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
 646     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 647         "VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"
 648 }
 649
 650 function downgrade_from_15(){
 651     # echo "    downgrade database from version 0.15 to version 0.14"
 652     echo "    Delete ofc_id to 'of_flows'"
 653     sql "ALTER TABLE of_flows DROP COLUMN ofc_id, DROP FOREIGN KEY FK_of_flows_ofcs;"
 654     sql "DELETE FROM schema_version WHERE version_int = '15';"
 655 }
 656
 657
 658 function upgrade_to_16(){
 659     # echo "    upgrade database from version 0.15 to version 0.16"
 660     echo "    Add last_error and status colum to 'ofcs'"
 661     sql "ALTER TABLE ofcs ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password, "\
 662             "ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"
 663     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 664         "VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"
 665 }
 666
 667 function downgrade_from_16(){
 668     # echo "    downgrade database from version 0.16 to version 0.15"
 669     echo "    Delete last_error and status colum to 'ofcs'"
 670     sql "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;"
 671     sql "DELETE FROM schema_version WHERE version_int = '16';"
 672 }
 673
 674 function upgrade_to_17(){
 675     # echo "    upgrade database from version 0.16 to version 0.17"
 676     echo "    Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 677     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 678         "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);"
 679     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 680         "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);"
 681     echo "    Add nets_with_same_vlan to table ofcs"
 682     sql "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;"
 683     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 684         "VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"
 685 }
 686
 687 function downgrade_from_17(){
 688     # echo "    downgrade database from version 0.17 to version 0.16"
 689     echo "    Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
 690     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
 691         "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);"
 692     sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
 693         "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);"
 694     echo "    Remove nets_with_same_vlan from table ofcs"
 695     sql "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;"
 696     sql "DELETE FROM schema_version WHERE version_int = '17';"
 697 }
 698
 699 function upgrade_to_18(){
 700     echo "    Add 'region' at 'nets' and change unique index vlan+region"
 701     sql "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, DROP INDEX type_vlan;"
 702     echo "    Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
 703     sql "UPDATE nets set region='__OVS__' where provider like 'OVS%';"
 704     sql "UPDATE nets set region='__DATA__' where type='data' or type='ptp';"
 705     echo "    Create new index region_vlan at nets"
 706         sql "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);"
 707     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 708         "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"
 709 }
 710
 711 function downgrade_from_18(){
 712     echo "    Delete 'region' at 'nets' and change back unique index vlan+type"
 713     sql "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;"
 714     echo "    Create back index type_vlan at nets"
 715         sql "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);"
 716     sql "DELETE FROM schema_version WHERE version_int = '18';"
 717 }
 718
 719 function upgrade_to_19(){
 720     echo "    Add 'keyfile' to 'hosts'"
 721     sql "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;"
 722     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 723         "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"
 724 }
 725
 726 function downgrade_from_19(){
 727     echo "    Delete 'keyfile' from 'hosts'"
 728     sql "ALTER TABLE hosts DROP COLUMN keyfile;"
 729     sql "DELETE FROM schema_version WHERE version_int = '19';"
 730 }
 731
 732 function upgrade_to_20(){
 733     echo "    Add 'image_size' to 'instance_devices'"
 734     sql "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;"
 735     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 736         "VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"
 737 }
 738
 739 function downgrade_from_20(){
 740     echo "    Delete 'image_size' from 'instance_devices'"
 741     sql "ALTER TABLE instance_devices DROP COLUMN image_size;"
 742     sql "DELETE FROM schema_version WHERE version_int = '20';"
 743 }
 744
 745 function upgrade_to_21(){
 746     echo "    Add 'routes', 'links' and 'dns' to 'nets'"
 747     sql "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip, "\
 748         "ADD COLUMN links TEXT(2000)  NULL AFTER dns, "\
 749         "ADD COLUMN routes TEXT(2000)  NULL AFTER links;"
 750     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 751         "VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"
 752 }
 753
 754 function downgrade_from_21(){
 755     echo "    Delete 'routes', 'links' and 'dns' to 'nets'"
 756     sql "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;"
 757     sql "DELETE FROM schema_version WHERE version_int = '21';"
 758 }
 759
 760 function upgrade_to_22(){
 761     echo "    Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
 762     sql "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 763     sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
 764         "VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"
 765 }
 766
 767 function downgrade_from_22(){
 768     echo "    Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
 769     sql "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
 770     sql "DELETE FROM schema_version WHERE version_int = '22';"
 771 }
 772
 773 # TODO ... put functions here
 774
 775 # echo "db version = "${DATABASE_VER_NUM}
 776 [ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo "    current database version '$DATABASE_VER_NUM' is ok" && exit 0
 777
 778 # Create a backup database content
 779 TEMPFILE2="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql")"
 780 trap 'rm -f "$TEMPFILE2"' EXIT
 781 mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $TEMPFILE2
 782
 783 function rollback_db()
 784 {
 785     cat $TEMPFILE2 | mysql $DEF_EXTRA_FILE_PARAM && echo "    Aborted! Rollback database OK" ||
 786         echo "    Aborted! Rollback database FAIL"
 787     exit 1
 788 }
 789
 790 function sql()    # send a sql command
 791 {
 792     echo "$*" | $DBCMD || ! echo "    ERROR with command '$*'" || rollback_db
 793     return 0
 794 }
 795
 796 #UPGRADE DATABASE step by step
 797 while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
 798 do
 799     echo "    upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
 800     DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
 801     upgrade_to_${DATABASE_VER_NUM}
 802     #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
 803     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
 804     #$FILE_ || exit -1  # if fail return
 805 done
 806
 807 #DOWNGRADE DATABASE step by step
 808 while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
 809 do
 810     echo "    downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
 811     #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
 812     #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
 813     #$FILE_ || exit -1  # if fail return
 814     downgrade_from_${DATABASE_VER_NUM}
 815     DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
 816 done
 817
 818 #echo done
 819