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