+function upgrade_to_13(){
+ # echo " upgrade database from version 0.12 to version 0.13"
+ echo " Create of_port_mapings table "
+ sql "CREATE TABLE of_port_mappings (
+ uuid VARCHAR(36) NOT NULL,
+ ofc_id VARCHAR(36) NULL DEFAULT NULL,
+ region VARCHAR(64) NULL DEFAULT NULL,
+ compute_node VARCHAR(64) NULL DEFAULT NULL,
+ pci VARCHAR(50) NULL DEFAULT NULL,
+ switch_dpid VARCHAR(64) NULL DEFAULT NULL,
+ switch_port VARCHAR(64) NULL DEFAULT NULL,
+ switch_mac CHAR(18) NULL DEFAULT NULL,
+ UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port),
+ UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac),
+ UNIQUE INDEX region_compute_node_pci (region, compute_node, pci),
+ INDEX FK_of_port_mappings_ofcs (ofc_id),
+ CONSTRAINT FK_of_port_mappings_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE
+ ON DELETE CASCADE)
+ COLLATE='utf8_general_ci'
+ ENGINE=InnoDB;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"
+}
+
+function downgrade_from_13(){
+ # echo " downgrade database from version 0.13 to version 0.12"
+ echo " Delete of_port_mappings table"
+ sql "DROP TABLE of_port_mappings;"
+ sql "DELETE FROM schema_version WHERE version_int = '13';"
+}
+
+function upgrade_to_14(){
+ # echo " upgrade database from version 0.13 to version 0.14"
+ echo " Add switch_mac, ofc_id colum to 'ports' and 'resources_port'"
+ sql "ALTER TABLE ports
+ ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
+ ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
+ ADD CONSTRAINT FK_port_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
+ sql "ALTER TABLE resources_port
+ ADD COLUMN switch_mac VARCHAR(18) NULL DEFAULT NULL AFTER switch_port,
+ ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER switch_dpid,
+ ADD CONSTRAINT FK_resource_ofc_id FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid);"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"
+}
+
+function downgrade_from_14(){
+ # echo " downgrade database from version 0.14 to version 0.13"
+ echo " Delete switch_mac, ofc_id colum to 'ports'"
+ sql "ALTER TABLE ports DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_port_ofc_id;"
+ sql "ALTER TABLE resources_port DROP COLUMN switch_mac, DROP COLUMN ofc_id, DROP FOREIGN KEY FK_resource_ofc_id;"
+ sql "DELETE FROM schema_version WHERE version_int = '14';"
+}
+
+function upgrade_to_15(){
+ # echo " upgrade database from version 0.14 to version 0.15"
+ echo " Add ofc_id colum to 'of_flows'"
+ sql "ALTER TABLE of_flows ADD COLUMN ofc_id VARCHAR(36) NULL DEFAULT NULL AFTER net_id, ADD CONSTRAINT "\
+ "FK_of_flows_ofcs FOREIGN KEY (ofc_id) REFERENCES ofcs (uuid) ON UPDATE CASCADE ON DELETE SET NULL;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"
+}
+
+function downgrade_from_15(){
+ # echo " downgrade database from version 0.15 to version 0.14"
+ echo " Delete ofc_id to 'of_flows'"
+ sql "ALTER TABLE of_flows DROP COLUMN ofc_id, DROP FOREIGN KEY FK_of_flows_ofcs;"
+ sql "DELETE FROM schema_version WHERE version_int = '15';"
+}
+
+
+function upgrade_to_16(){
+ # echo " upgrade database from version 0.15 to version 0.16"
+ echo " Add last_error and status colum to 'ofcs'"
+ sql "ALTER TABLE ofcs ADD COLUMN last_error VARCHAR(255) NULL DEFAULT NULL AFTER password, "\
+ "ADD COLUMN status ENUM('ACTIVE','INACTIVE','ERROR') NULL DEFAULT 'ACTIVE' AFTER last_error;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"
+}
+
+function downgrade_from_16(){
+ # echo " downgrade database from version 0.16 to version 0.15"
+ echo " Delete last_error and status colum to 'ofcs'"
+ sql "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status;"
+ sql "DELETE FROM schema_version WHERE version_int = '16';"
+}
+
+function upgrade_to_17(){
+ # echo " upgrade database from version 0.16 to version 0.17"
+ echo " Add pci to the unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port, pci);"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac, pci);"
+ echo " Add nets_with_same_vlan to table ofcs"
+ sql "ALTER TABLE ofcs ADD COLUMN nets_with_same_vlan ENUM('true','false') NOT NULL DEFAULT 'false' AFTER status;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (17, '0.17', '0.5.10', 'Add pci to unique index dpid port/mac at of_port_mappings', '2017-04-05');"
+}
+
+function downgrade_from_17(){
+ # echo " downgrade database from version 0.17 to version 0.16"
+ echo " Delete pci fromthe unique indexes switch_dpid_switch_port switch_dpid_switch_mac at of_port_mappings"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_port, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_port (switch_dpid, switch_port);"
+ sql "ALTER TABLE of_port_mappings DROP INDEX switch_dpid_switch_mac, "\
+ "ADD UNIQUE INDEX switch_dpid_switch_mac (switch_dpid, switch_mac);"
+ echo " Remove nets_with_same_vlan from table ofcs"
+ sql "ALTER TABLE ofcs DROP COLUMN nets_with_same_vlan;"
+ sql "DELETE FROM schema_version WHERE version_int = '17';"
+}
+
+function upgrade_to_18(){
+ echo " Add 'region' at 'nets' and change unique index vlan+region"
+ sql "ALTER TABLE nets ADD COLUMN region VARCHAR(64) NULL DEFAULT NULL AFTER admin_state_up, DROP INDEX type_vlan;"
+ echo " Fill 'region' with __OVS__/__DATA__ for OVS/openflow provider at nets"
+ sql "UPDATE nets set region='__OVS__' where provider like 'OVS%';"
+ sql "UPDATE nets set region='__DATA__' where type='data' or type='ptp';"
+ echo " Create new index region_vlan at nets"
+ sql "ALTER TABLE nets ADD UNIQUE INDEX region_vlan (region, vlan);"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (18, '0.18', '0.5.13', 'Add region to nets, change vlan unique index', '2017-05-03');"
+}
+
+function downgrade_from_18(){
+ echo " Delete 'region' at 'nets' and change back unique index vlan+type"
+ sql "ALTER TABLE nets DROP INDEX region_vlan, DROP COLUMN region;"
+ echo " Create back index type_vlan at nets"
+ sql "ALTER TABLE nets ADD UNIQUE INDEX type_vlan (type, vlan);"
+ sql "DELETE FROM schema_version WHERE version_int = '18';"
+}
+
+function upgrade_to_19(){
+ echo " Add 'keyfile' to 'hosts'"
+ sql "ALTER TABLE hosts ADD COLUMN keyfile VARCHAR(255) NULL DEFAULT NULL AFTER password;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (19, '0.19', '0.5.15', 'Add keyfile to hosts', '2017-05-23');"
+}
+
+function downgrade_from_19(){
+ echo " Delete 'keyfile' from 'hosts'"
+ sql "ALTER TABLE hosts DROP COLUMN keyfile;"
+ sql "DELETE FROM schema_version WHERE version_int = '19';"
+}
+
+function upgrade_to_20(){
+ echo " Add 'image_size' to 'instance_devices'"
+ sql "ALTER TABLE instance_devices ADD COLUMN image_size INT NULL DEFAULT NULL AFTER dev;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (20, '0.20', '0.5.17', 'Add image_size to instance_devices', '2017-06-01');"
+}
+
+function downgrade_from_20(){
+ echo " Delete 'image_size' from 'instance_devices'"
+ sql "ALTER TABLE instance_devices DROP COLUMN image_size;"
+ sql "DELETE FROM schema_version WHERE version_int = '20';"
+}
+
+function upgrade_to_21(){
+ echo " Add 'routes', 'links' and 'dns' to 'nets'"
+ sql "ALTER TABLE nets ADD COLUMN dns VARCHAR(255) NULL AFTER gateway_ip, "\
+ "ADD COLUMN links TEXT(2000) NULL AFTER dns, "\
+ "ADD COLUMN routes TEXT(2000) NULL AFTER links;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (21, '0.21', '0.5.18', 'Add routes, links and dns to inets', '2017-06-21');"
+}
+
+function downgrade_from_21(){
+ echo " Delete 'routes', 'links' and 'dns' to 'nets'"
+ sql "ALTER TABLE nets DROP COLUMN dns, DROP COLUMN links, DROP COLUMN routes;"
+ sql "DELETE FROM schema_version WHERE version_int = '21';"
+}
+
+function upgrade_to_22(){
+ echo " Changed type of ram in 'flavors' from SMALLINT to MEDIUMINT"
+ sql "ALTER TABLE flavors CHANGE COLUMN ram ram MEDIUMINT(7) UNSIGNED NULL DEFAULT NULL AFTER disk;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (22, '0.22', '0.5.21', 'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-11-14');"
+}
+
+function downgrade_from_22(){
+ echo " Changed type of ram in 'flavors' from MEDIUMINT to SMALLINT"
+ sql "ALTER TABLE flavors CHANGE COLUMN ram ram SMALLINT(5) UNSIGNED NULL DEFAULT NULL AFTER disk;"
+ sql "DELETE FROM schema_version WHERE version_int = '22';"
+}
+
+function upgrade_to_23(){
+ echo " Add 'hypervisor' and 'os_type' column to 'instances' table"
+ sql "ALTER TABLE instances ADD COLUMN hypervisor enum('kvm','xen-unik','xenhvm') NOT NULL DEFAULT 'kvm' AFTER flavor_id;"
+ sql "ALTER TABLE instances ADD COLUMN os_image_type VARCHAR(24) NOT NULL DEFAULT 'other' AFTER hypervisor;"
+ echo " Add 'hypervisors' column to 'hosts' table"
+ sql "ALTER TABLE hosts ADD COLUMN hypervisors VARCHAR(255) NOT NULL DEFAULT 'kvm' AFTER features;"
+ sql "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) "\
+ "VALUES (23, '0.23', '0.5.24', 'Add hypervisor, os_type to instances and add hypervisors to hosts', '2018-03-20');"
+}
+
+function downgrade_from_23(){
+ echo " Remove 'hypervisor' and 'os_type' column from 'instances' table"
+ sql "ALTER TABLE instances DROP COLUMN hypervisor;"
+ sql "ALTER TABLE instances DROP COLUMN os_image_type;"
+ echo " Remove 'hypervisors' column from 'hosts' table"
+ sql "ALTER TABLE hosts DROP COLUMN hypervisors;"
+ sql "DELETE FROM schema_version WHERE version_int = '23';"
+}
+
+# TODO ... put functions here
+
+
+function del_schema_version_process()
+{
+ echo "DELETE FROM schema_version WHERE version_int='0';" | $DBCMD ||
+ ! echo " ERROR writing on schema_version" >&2 || exit 1
+}
+
+function set_schema_version_process()
+{
+ echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES "\
+ "(0, '0.0', '0.0.0', 'migration from $DATABASE_VER_NUM to $DB_VERSION backup: $BACKUP_FILE',"\
+ "'$(date +%Y-%m-%d)');" | $DBCMD ||
+ ! echo " Cannot set database at migration process writing into schema_version" >&2 || exit 1
+
+}
+
+function rollback_db()
+{
+ if echo $DATABASE_PROCESS | grep -q init ; then # Empty database. No backup needed
+ echo " Aborted! Rollback database not needed" && exit 1
+ else # migration a non empty database or Recovering a migration process
+ cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM && echo " Aborted! Rollback database OK" &&
+ del_schema_version_process && rm -f "$BACKUP_FILE" && exit 1
+ echo " Aborted! Rollback database FAIL" && exit 1
+ fi
+}
+
+function sql() # send a sql command
+{
+ echo "$*" | $DBCMD || ! echo " ERROR with command '$*'" || rollback_db
+ return 0
+}
+
+function migrate()
+{
+ #UPGRADE DATABASE step by step
+ while [ $DB_VERSION -gt $DATABASE_VER_NUM ]
+ do
+ echo " upgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM+1))'"
+ DATABASE_VER_NUM=$((DATABASE_VER_NUM+1))
+ upgrade_to_${DATABASE_VER_NUM}
+ #FILE_="${DIRNAME}/upgrade_to_${DATABASE_VER_NUM}.sh"
+ #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to upgrade" >&2 && exit -1
+ #$FILE_ || exit -1 # if fail return
+ done
+
+ #DOWNGRADE DATABASE step by step
+ while [ $DB_VERSION -lt $DATABASE_VER_NUM ]
+ do
+ echo " downgrade database from version '$DATABASE_VER_NUM' to '$((DATABASE_VER_NUM-1))'"
+ #FILE_="${DIRNAME}/downgrade_from_${DATABASE_VER_NUM}.sh"
+ #[ ! -x "$FILE_" ] && echo "Error, can not find script '$FILE_' to downgrade" >&2 && exit -1
+ #$FILE_ || exit -1 # if fail return
+ downgrade_from_${DATABASE_VER_NUM}
+ DATABASE_VER_NUM=$((DATABASE_VER_NUM-1))
+ done
+}
+
+
+# check if current database is ok
+function check_migration_needed()
+{
+ DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2` ||
+ ! echo " ERROR cannot read from schema_version" || exit 1
+
+ if [[ -z "$DATABASE_VER_NUM" ]] || [[ "$DATABASE_VER_NUM" -lt 0 ]] || [[ "$DATABASE_VER_NUM" -gt 100 ]] ; then
+ echo " Error can not get database version ($DATABASE_VER_NUM?)" >&2
+ exit 1
+ fi
+
+ [[ $DB_VERSION -eq $DATABASE_VER_NUM ]] && echo " current database version '$DATABASE_VER_NUM' is ok" && return 1
+ [[ "$DATABASE_VER_NUM" -gt "$LAST_DB_VERSION" ]] &&
+ echo "Database has been upgraded with a newer version of this script. Use this version to downgrade" >&2 &&
+ exit 1
+ return 0
+}
+
+DATABASE_PROCESS=`echo "select comments from schema_version where version_int=0;" | $DBCMD | tail -n+2` ||
+ ! echo " ERROR cannot read from schema_version" || exit 1
+if [[ -z "$DATABASE_PROCESS" ]] ; then # migration a non empty database
+ check_migration_needed || exit 0
+ # Create a backup database content
+ [[ -n "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q "${BACKUP_DIR}/backupdb.XXXXXX.sql")"
+ [[ -z "$BACKUP_DIR" ]] && BACKUP_FILE="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql")"
+ mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $BACKUP_FILE ||
+ ! echo "Cannot create Backup file '$BACKUP_FILE'" >&2 || exit 1
+ echo " Backup file '$BACKUP_FILE' created"
+ # Set schema version
+ set_schema_version_process
+ migrate
+ del_schema_version_process
+ rm -f "$BACKUP_FILE"
+elif echo $DATABASE_PROCESS | grep -q init ; then # Empty database. No backup needed
+ echo " Migrating an empty database"
+ if check_migration_needed ; then
+ migrate
+ fi
+ del_schema_version_process
+
+else # Recover Migration process
+ BACKUP_FILE=${DATABASE_PROCESS##*backup: }
+ [[ -f "$BACKUP_FILE" ]] || ! echo "Previous migration process fail and cannot recover backup file '$BACKUP_FILE'" >&2 ||
+ exit 1
+ echo " Previous migration was killed. Restoring database from rollback file'$BACKUP_FILE'"
+ cat $BACKUP_FILE | mysql $DEF_EXTRA_FILE_PARAM || ! echo " Cannot load backup file '$BACKUP_FILE'" >&2 || exit 1
+ if check_migration_needed ; then
+ set_schema_version_process
+ migrate
+ fi
+ del_schema_version_process
+ rm -f "$BACKUP_FILE"
+fi
+exit 0