X-Git-Url: https://osm.etsi.org/gitweb/?a=blobdiff_plain;f=database_utils%2Fmigrate_vim_db.sh;h=1d2486797c047a0d5d9454bf8260c8c4982203a5;hb=6c6006528a473fff882151806251aa36c5fbf5b8;hp=a8a7741bfd7bfd6ca3f84a7e5dd24ce821166619;hpb=181f17585738e6724b781cb1b371e8ac3c394cc1;p=osm%2Fopenvim.git diff --git a/database_utils/migrate_vim_db.sh b/database_utils/migrate_vim_db.sh index a8a7741..1d24867 100755 --- a/database_utils/migrate_vim_db.sh +++ b/database_utils/migrate_vim_db.sh @@ -176,10 +176,16 @@ DATABASE_TARGET_VER_NUM=0 [ $OPENVIM_VER_NUM -ge 4002 ] && DATABASE_TARGET_VER_NUM=6 #0.4.2 => 6 [ $OPENVIM_VER_NUM -ge 4005 ] && DATABASE_TARGET_VER_NUM=7 #0.4.5 => 7 [ $OPENVIM_VER_NUM -ge 4010 ] && DATABASE_TARGET_VER_NUM=8 #0.4.10 => 8 -[ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM=9 #0.5.2 => 9 +[ $OPENVIM_VER_NUM -ge 5001 ] && DATABASE_TARGET_VER_NUM=9 #0.5.1 => 9 +[ $OPENVIM_VER_NUM -ge 5002 ] && DATABASE_TARGET_VER_NUM=10 #0.5.2 => 10 +[ $OPENVIM_VER_NUM -ge 5004 ] && DATABASE_TARGET_VER_NUM=11 #0.5.4 => 11 +[ $OPENVIM_VER_NUM -ge 5005 ] && DATABASE_TARGET_VER_NUM=12 #0.5.5 => 12 +[ $OPENVIM_VER_NUM -ge 5006 ] && DATABASE_TARGET_VER_NUM=13 #0.5.6 => 13 +[ $OPENVIM_VER_NUM -ge 5007 ] && DATABASE_TARGET_VER_NUM=14 #0.5.7 => 14 +[ $OPENVIM_VER_NUM -ge 5008 ] && DATABASE_TARGET_VER_NUM=15 #0.5.8 => 15 +[ $OPENVIM_VER_NUM -ge 5009 ] && DATABASE_TARGET_VER_NUM=16 #0.5.9 => 16 #TODO ... put next versions here - function upgrade_to_1(){ echo " upgrade database from version 0.0 to version 0.1" echo " CREATE TABLE \`schema_version\`" @@ -455,24 +461,174 @@ function downgrade_from_8(){ function upgrade_to_9(){ echo " upgrade database from version 0.8 to version 0.9" - echo " change types at 'ports'" - echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 - echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (9, '0.9', '0.5.2', 'add column checksum to images', '2016-09-30');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo " change length of columns 'path' and 'name' to 255 in table 'images', and change length of column 'name' to 255 in table 'flavors'" + echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(255) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER path;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(255) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (9, '0.9', '0.5.1', 'increase length of columns path and name to 255 in table images, and change length of column name to 255 in table flavors', '2017-01-10');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 } function downgrade_from_9(){ echo " downgrade database from version 0.9 to version 0.8" + echo " change length of columns 'path' and 'name' to 100 and 64 in table 'images'" + echo "ALTER TABLE images CHANGE COLUMN path path VARCHAR(100) NOT NULL AFTER uuid, CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER path;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "ALTER TABLE flavors CHANGE COLUMN name name VARCHAR(64) NOT NULL AFTER uuid;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function upgrade_to_10(){ + echo " upgrade database from version 0.9 to version 0.10" + echo " change types at 'ports'" + echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (10, '0.10', '0.5.2', 'change ports type, adding instance:ovs', '2017-02-01');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} +function downgrade_from_10(){ + echo " downgrade database from version 0.10 to version 0.9" echo " change back types at 'ports'" echo "ALTER TABLE ports CHANGE COLUMN type type ENUM('instance:bridge','instance:data','external') NOT NULL DEFAULT 'instance:bridge' AFTER status;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 - echo "DELETE FROM schema_version WHERE version_int = '9';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '10';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 } -#TODO ... put funtions here +function upgrade_to_11(){ + echo " upgrade database from version 0.10 to version 0.11" + echo " Add gateway_ip colum to 'nets'" + echo "ALTER TABLE nets ADD COLUMN gateway_ip VARCHAR(64) NULL DEFAULT NULL AFTER dhcp_last_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (11, '0.11', '0.5.4', 'Add gateway_ip colum to nets', '2017-02-13');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} +function downgrade_from_11(){ + echo " downgrade database from version 0.11 to version 0.10" + echo " Delete gateway_ip colum from 'nets'" + echo "ALTER TABLE nets DROP COLUMN gateway_ip;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '11';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} +function upgrade_to_12(){ + echo " upgrade database from version 0.11 to version 0.12" + echo " Create of_controller table " + echo "CREATE TABLE ofcs ( + uuid VARCHAR(36) NOT NULL, + name VARCHAR(255) NOT NULL, + dpid VARCHAR(64) NOT NULL, + ip VARCHAR(64) NOT NULL, + port INT(5) NOT NULL, + type VARCHAR(64) NOT NULL, + version VARCHAR(12) NULL DEFAULT NULL, + user VARCHAR(64) NULL DEFAULT NULL, + password VARCHAR(64) NULL DEFAULT NULL, + PRIMARY KEY (uuid) +) +COLLATE='utf8_general_ci' +ENGINE=InnoDB;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo " Modify user_at for uuids table" + echo "ALTER TABLE uuids CHANGE COLUMN used_at used_at VARCHAR(64) NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (12, '0.12', '0.5.5', 'Add of_controller table', '2017-02-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function downgrade_from_12(){ + echo " downgrade database from version 0.12 to version 0.11" + echo " Delete ofcs table" + echo "DROP TABLE ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "ALTER TABLE uuids CHANGE COLUMN used_at used_at ENUM('flavors', 'hosts', 'images', 'instances', 'nets', 'ports', 'tenants') NULL DEFAULT NULL COMMENT 'Table that uses this UUID' ;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '12';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function upgrade_to_13(){ + echo " upgrade database from version 0.12 to version 0.13" + echo " Create of_port_mapings table " + echo "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;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (13, '0.13', '0.5.6', 'Add of_port_mapings table', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} +function downgrade_from_13(){ + echo " downgrade database from version 0.13 to version 0.12" + echo " Delete of_port_mappings table" + echo "DROP TABLE of_port_mappings;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '13';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +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'" + echo "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);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "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);"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (14, '0.14', '0.5.7', 'Add switch_mac, ofc_id colum to ports and resources_port tables', '2017-03-09');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function downgrade_from_14(){ + echo " downgrade database from version 0.14 to version 0.13" + echo " Delete switch_mac, ofc_id colum to 'ports'" + echo "ALTER TABLE ports + DROP COLUMN switch_mac, + DROP COLUMN ofc_id, + DROP FOREIGN KEY FK_port_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "ALTER TABLE resources_port + DROP COLUMN switch_mac, + DROP COLUMN ofc_id, + DROP FOREIGN KEY FK_resource_ofc_id;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '14';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function upgrade_to_15(){ + echo " upgrade database from version 0.14 to version 0.15" + echo " Add ofc_id colum to 'of_flows'" + echo "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;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (15, '0.15', '0.5.8', 'Add ofc_id colum to of_flows', '2017-03-15');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function downgrade_from_15(){ + echo " downgrade database from version 0.15 to version 0.14" + echo " Delete ofc_id to 'of_flows'" + echo "ALTER TABLE of_flows + DROP COLUMN ofc_id, + DROP FOREIGN KEY FK_of_flows_ofcs;" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '15';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + + +function upgrade_to_16(){ + echo " upgrade database from version 0.15 to version 0.16" + echo " Add last_error and status colum to 'ofcs'" + echo "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;"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "INSERT INTO schema_version (version_int, version, openvim_ver, comments, date) VALUES (16, '0.16', '0.5.9', 'Add last_error and status colum to ofcs', '2017-03-17');"| $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} + +function downgrade_from_16(){ + echo " downgrade database from version 0.16 to version 0.15" + echo " Delete last_error and status colum to 'ofcs'" + echo "ALTER TABLE ofcs DROP COLUMN last_error, DROP COLUMN status; " | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 + echo "DELETE FROM schema_version WHERE version_int = '16';" | $DBCMD || ! echo "ERROR. Aborted!" || exit -1 +} +#TODO ... put funtions here + +echo "db version = "${DATABASE_VER_NUM} [ $DATABASE_TARGET_VER_NUM -eq $DATABASE_VER_NUM ] && echo " current database version $DATABASE_VER is ok" #UPGRADE DATABASE step by step while [ $DATABASE_TARGET_VER_NUM -gt $DATABASE_VER_NUM ] do 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