X-Git-Url: https://osm.etsi.org/gitweb/?a=blobdiff_plain;f=database_utils%2Fmigrate_mano_db.sh;h=096a21ad16a8fd03873cd522bdb1e5d114fe8c87;hb=fb5855ccaaffd7f9b5398505be9eed9b5b8e11d9;hp=1e3e5b6c1b296f1ab65b02d60b8da9cd444615d6;hpb=a92a0eaaf370c626b442863f4127cd11fc64754c;p=osm%2FRO.git diff --git a/database_utils/migrate_mano_db.sh b/database_utils/migrate_mano_db.sh index 1e3e5b6c..096a21ad 100755 --- a/database_utils/migrate_mano_db.sh +++ b/database_utils/migrate_mano_db.sh @@ -1,7 +1,7 @@ #!/bin/bash ## -# Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U. +# Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U. # This file is part of openmano # All Rights Reserved. # @@ -24,6 +24,7 @@ # #Upgrade/Downgrade openmano database preserving the content # +DBUTILS="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" DBUSER="mano" DBPASS="" @@ -32,9 +33,11 @@ DBHOST="" DBPORT="3306" DBNAME="mano_db" QUIET_MODE="" +BACKUP_DIR="" +BACKUP_FILE="" #TODO update it with the last database version -LAST_DB_VERSION=24 - +LAST_DB_VERSION=39 + # Detect paths MYSQL=$(which mysql) AWK=$(which awk) @@ -50,11 +53,12 @@ function usage(){ echo -e " -P PORT database port. '$DBPORT' by default" echo -e " -h HOST database host. 'localhost' by default" echo -e " -d NAME database name. '$DBNAME' by default. Prompts if DB access fails" + echo -e " -b DIR backup folder where to create rollback backup file" echo -e " -q --quiet: Do not prompt for credentials and exit if cannot access to database" echo -e " --help shows this help" } -while getopts ":u:p:P:h:d:q-:" o; do +while getopts ":u:p:b:P:h:d:q-:" o; do case "${o}" in u) DBUSER="$OPTARG" @@ -71,6 +75,9 @@ while getopts ":u:p:P:h:d:q-:" o; do h) DBHOST="$OPTARG" ;; + b) + BACKUP_DIR="$OPTARG" + ;; q) export QUIET_MODE=yes ;; @@ -142,7 +149,6 @@ done DBCMD="mysql $DEF_EXTRA_FILE_PARAM $DBNAME" #echo DBCMD $DBCMD -#GET DATABASE VERSION #check that the database seems a openmano database if ! echo -e "show create table vnfs;\nshow create table scenarios" | $DBCMD >/dev/null 2>&1 then @@ -150,22 +156,6 @@ then exit 1; fi -if ! echo 'show create table schema_version;' | $DBCMD >/dev/null 2>&1 -then - DATABASE_VER="0.0" - DATABASE_VER_NUM=0 -else - DATABASE_VER_NUM=`echo "select max(version_int) from schema_version;" | $DBCMD | tail -n+2` - DATABASE_VER=`echo "select version from schema_version where version_int='$DATABASE_VER_NUM';" | $DBCMD | tail -n+2` - [ "$DATABASE_VER_NUM" -lt 0 -o "$DATABASE_VER_NUM" -gt 100 ] && - echo " Error can not get database version ($DATABASE_VER?)" >&2 && exit 1 - #echo "_${DATABASE_VER_NUM}_${DATABASE_VER}" -fi - -[ "$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 - #GET DATABASE TARGET VERSION #DB_VERSION=0 #[ $OPENMANO_VER_NUM -ge 2002 ] && DB_VERSION=1 #0.2.2 => 1 @@ -192,6 +182,21 @@ fi #[ $OPENMANO_VER_NUM -ge 5016 ] && DB_VERSION=22 #0.5.16 => 22 #[ $OPENMANO_VER_NUM -ge 5020 ] && DB_VERSION=23 #0.5.20 => 23 #[ $OPENMANO_VER_NUM -ge 5021 ] && DB_VERSION=24 #0.5.21 => 24 +#[ $OPENMANO_VER_NUM -ge 5022 ] && DB_VERSION=25 #0.5.22 => 25 +#[ $OPENMANO_VER_NUM -ge 5024 ] && DB_VERSION=26 #0.5.24 => 26 +#[ $OPENMANO_VER_NUM -ge 5025 ] && DB_VERSION=27 #0.5.25 => 27 +#[ $OPENMANO_VER_NUM -ge 5052 ] && DB_VERSION=28 #0.5.52 => 28 +#[ $OPENMANO_VER_NUM -ge 5059 ] && DB_VERSION=29 #0.5.59 => 29 +#[ $OPENMANO_VER_NUM -ge 5060 ] && DB_VERSION=30 #0.5.60 => 30 +#[ $OPENMANO_VER_NUM -ge 5061 ] && DB_VERSION=31 #0.5.61 => 31 +#[ $OPENMANO_VER_NUM -ge 5070 ] && DB_VERSION=32 #0.5.70 => 32 +#[ $OPENMANO_VER_NUM -ge 5082 ] && DB_VERSION=33 #0.5.82 => 33 +#[ $OPENMANO_VER_NUM -ge 6000 ] && DB_VERSION=34 #0.6.00 => 34 +#[ $OPENMANO_VER_NUM -ge 6001 ] && DB_VERSION=35 #0.6.01 => 35 +#[ $OPENMANO_VER_NUM -ge 6003 ] && DB_VERSION=36 #0.6.03 => 36 +#[ $OPENMANO_VER_NUM -ge 6009 ] && DB_VERSION=37 #0.6.09 => 37 +#[ $OPENMANO_VER_NUM -ge 6011 ] && DB_VERSION=38 #0.6.11 => 38 +#[ $OPENMANO_VER_NUM -ge 6020 ] && DB_VERSION=39 #0.6.20 => 39 #TODO ... put next versions here function upgrade_to_1(){ @@ -213,8 +218,8 @@ function upgrade_to_1(){ } function downgrade_from_1(){ # echo " downgrade database from version 0.1 to version 0.0" - echo " DROP TABLE \`schema_version\`" - sql "DROP TABLE \`schema_version\`;" + echo " DROP TABLE IF EXISTS \`schema_version\`" + sql "DROP TABLE IF EXISTS \`schema_version\`;" } function upgrade_to_2(){ # echo " upgrade database from version 0.1 to version 0.2" @@ -295,11 +300,11 @@ function downgrade_from_2(){ echo " Delete columns 'user/passwd' from 'vim_tenants'" sql "ALTER TABLE vim_tenants DROP COLUMN user, DROP COLUMN passwd; " echo " delete tables 'datacenter_images', 'images'" - sql "DROP TABLE \`datacenters_images\`;" - sql "DROP TABLE \`images\`;" + sql "DROP TABLE IF EXISTS \`datacenters_images\`;" + sql "DROP TABLE IF EXISTS \`images\`;" echo " delete tables 'datacenter_flavors', 'flavors'" - sql "DROP TABLE \`datacenters_flavors\`;" - sql "DROP TABLE \`flavors\`;" + sql "DROP TABLE IF EXISTS \`datacenters_flavors\`;" + sql "DROP TABLE IF EXISTS \`flavors\`;" sql "DELETE FROM schema_version WHERE version_int='2';" } @@ -613,7 +618,7 @@ function upgrade_to_12(){ function downgrade_from_12(){ # echo " downgrade database from version 0.12 to version 0.11" echo " delete ip_profiles table, and remove ip_address column in 'interfaces' and 'sce_interfaces'" - sql "DROP TABLE ip_profiles;" + sql "DROP TABLE IF EXISTS ip_profiles;" sql "ALTER TABLE interfaces DROP COLUMN ip_address;" sql "ALTER TABLE sce_interfaces DROP COLUMN ip_address;" sql "DELETE FROM schema_version WHERE version_int='12';" @@ -791,20 +796,21 @@ function downgrade_from_22(){ function upgrade_to_23(){ # echo " upgrade database from version 0.22 to version 0.23" echo " add column 'availability_zone' at table 'vms'" - sql "ALTER TABLE mano_db.vms ADD COLUMN availability_zone VARCHAR(255) NULL AFTER modified_at;" + sql "ALTER TABLE vms ADD COLUMN availability_zone VARCHAR(255) NULL AFTER modified_at;" sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) VALUES (23, '0.23', '0.5.20',"\ "'Changed type of ram in flavors from SMALLINT to MEDIUMINT', '2017-08-29');" } function downgrade_from_23(){ # echo " downgrade database from version 0.23 to version 0.22" echo " remove column 'availability_zone' from table 'vms'" - sql "ALTER TABLE mano_db.vms DROP COLUMN availability_zone;" + sql "ALTER TABLE vms DROP COLUMN availability_zone;" sql "DELETE FROM schema_version WHERE version_int='23';" } function upgrade_to_24(){ # echo " upgrade database from version 0.23 to version 0.24" echo " Add 'count' to table 'vms'" + sql "ALTER TABLE vms ADD COLUMN count SMALLINT NOT NULL DEFAULT '1' AFTER vnf_id;" sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ "VALUES (24, '0.24', '0.5.21', 'Added vnfd fields', '2017-08-29');" @@ -815,7 +821,487 @@ function downgrade_from_24(){ sql "ALTER TABLE vms DROP COLUMN count;" sql "DELETE FROM schema_version WHERE version_int='24';" } +function upgrade_to_25(){ + # echo " upgrade database from version 0.24 to version 0.25" + echo " Add 'osm_id','short_name','vendor' to tables 'vnfs', 'scenarios'" + for table in vnfs scenarios; do + sql "ALTER TABLE $table ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid, "\ + "ADD UNIQUE INDEX osm_id_tenant_id (osm_id, tenant_id), "\ + "ADD COLUMN short_name VARCHAR(255) NULL AFTER name, "\ + "ADD COLUMN vendor VARCHAR(255) NULL AFTER description;" + done + sql "ALTER TABLE vnfs ADD COLUMN mgmt_access VARCHAR(2000) NULL AFTER vendor;" + sql "ALTER TABLE vms ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;" + sql "ALTER TABLE sce_vnfs ADD COLUMN member_vnf_index SMALLINT(6) NULL DEFAULT NULL AFTER uuid;" + echo " Add 'security_group' to table 'ip_profiles'" + sql "ALTER TABLE ip_profiles ADD COLUMN security_group VARCHAR(255) NULL DEFAULT NULL AFTER dhcp_count;" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (25, '0.25', '0.5.22', 'Added osm_id to vnfs,scenarios', '2017-09-01');" +} +function downgrade_from_25(){ + # echo " downgrade database from version 0.25 to version 0.24" + echo " Remove 'osm_id','short_name','vendor' from tables 'vnfs', 'scenarios'" + for table in vnfs scenarios; do + sql "ALTER TABLE $table DROP INDEX osm_id_tenant_id, DROP COLUMN osm_id, "\ + "DROP COLUMN short_name, DROP COLUMN vendor;" + done + sql "ALTER TABLE vnfs DROP COLUMN mgmt_access;" + sql "ALTER TABLE vms DROP COLUMN osm_id;" + sql "ALTER TABLE sce_vnfs DROP COLUMN member_vnf_index;" + echo " Remove 'security_group' from table 'ip_profiles'" + sql "ALTER TABLE ip_profiles DROP COLUMN security_group;" + + sql "DELETE FROM schema_version WHERE version_int='25';" +} + +function upgrade_to_26(){ + echo " Add name to table datacenter_tenants" + sql "ALTER TABLE datacenter_tenants ADD COLUMN name VARCHAR(255) NULL AFTER uuid;" + sql "UPDATE datacenter_tenants as dt join datacenters as d on dt.datacenter_id = d.uuid set dt.name=d.name;" + echo " Add 'SCHEDULED' to 'status' at tables 'instance_nets', 'instance_vms'" + sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\ + "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') "\ + "NOT NULL DEFAULT 'BUILD';" + sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','INACTIVE','DOWN','BUILD','ERROR',"\ + "'VIM_ERROR','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD';" + echo " Enlarge pci at instance_interfaces to allow extended pci for SDN por mapping" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(50) NULL DEFAULT NULL COMMENT 'PCI of the "\ + "physical port in the host' AFTER compute_node;" + + for t in flavor image; do + echo " Change 'datacenters_${t}s' to point to datacenter_tenant, add status, vim_info" + sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_vim_id VARCHAR(36) NULL DEFAULT NULL AFTER "\ + "datacenter_id, ADD COLUMN status ENUM('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','DELETED',"\ + "'SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD' AFTER vim_id, ADD COLUMN vim_info "\ + "TEXT NULL AFTER status;" + sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.datacenter_id=df.datacenter_id "\ + "set df.datacenter_vim_id=dt.uuid;" + sql "DELETE FROM datacenters_${t}s WHERE datacenter_vim_id is NULL;" + sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_vim_id datacenter_vim_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK_datacenters_${t}s_datacenter_tenants FOREIGN KEY "\ + "(datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE;" + sql "ALTER TABLE datacenters_${t}s DROP FOREIGN KEY FK__datacenters_${t:0:1};" + sql "ALTER TABLE datacenters_${t}s DROP COLUMN datacenter_id;" + done + + echo " Decoupling 'instance_interfaces' from scenarios/vnfs to allow scale actions" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(128) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids" + sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\ + "REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE SET NULL;" + + echo " Decoupling 'instance_vms' from scenarios/vnfs to allow scale actions" + sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(128) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;" + sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\ + "REFERENCES vms (uuid) ON UPDATE CASCADE ON DELETE SET NULL;" + + echo " Decoupling 'instance_nets' from scenarios/vnfs to allow scale actions" + sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(128) NULL DEFAULT NULL;" + + echo " Decoupling 'instance_scenarios' from scenarios" + sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;" + sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\ + "REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE SET NULL;" + + echo " Create table instance_actions, vim_actions" + sql "CREATE TABLE IF NOT EXISTS instance_actions ( + uuid VARCHAR(36) NOT NULL, + tenant_id VARCHAR(36) NULL DEFAULT NULL, + instance_id VARCHAR(36) NULL DEFAULT NULL, + description VARCHAR(64) NULL DEFAULT NULL COMMENT 'CREATE, DELETE, SCALE OUT/IN, ...', + number_tasks SMALLINT(6) NOT NULL DEFAULT '1', + number_done SMALLINT(6) NOT NULL DEFAULT '0', + number_failed SMALLINT(6) NOT NULL DEFAULT '0', + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_actions_tenants (tenant_id), + CONSTRAINT FK_actions_tenant FOREIGN KEY (tenant_id) REFERENCES nfvo_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COMMENT='Contains client actions over instances' + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + + sql "CREATE TABLE IF NOT EXISTS vim_actions ( + instance_action_id VARCHAR(36) NOT NULL, + task_index INT(6) NOT NULL, + datacenter_vim_id VARCHAR(36) NOT NULL, + vim_id VARCHAR(64) NULL DEFAULT NULL, + action VARCHAR(36) NOT NULL COMMENT 'CREATE,DELETE,START,STOP...', + item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored', + item_id VARCHAR(36) NULL DEFAULT NULL COMMENT 'uuid of the entry in the table', + status ENUM('SCHEDULED', 'BUILD', 'DONE', 'FAILED', 'SUPERSEDED') NOT NULL DEFAULT 'SCHEDULED', + extra TEXT NULL DEFAULT NULL COMMENT 'json with params:, depends_on: for the task', + error_msg VARCHAR(1024) NULL DEFAULT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (task_index, instance_action_id), + INDEX FK_actions_instance_actions (instance_action_id), + CONSTRAINT FK_actions_instance_actions FOREIGN KEY (instance_action_id) REFERENCES instance_actions (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + INDEX FK_actions_vims (datacenter_vim_id), + CONSTRAINT FK_actions_vims FOREIGN KEY (datacenter_vim_id) REFERENCES datacenter_tenants (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COMMENT='Table with the individual VIM actions.' + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (26, '0.26', '0.5.23', 'Several changes', '2017-09-09');" +} +function downgrade_from_26(){ + echo " Remove name from table datacenter_tenants" + sql "ALTER TABLE datacenter_tenants DROP COLUMN name;" + echo " Remove 'SCHEDULED' from the 'status' at tables 'instance_nets', 'instance_vms'" + sql "ALTER TABLE instance_vms CHANGE COLUMN status status ENUM('ACTIVE:NoMgmtIP','ACTIVE','INACTIVE','BUILD',"\ + "'ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED') NOT NULL DEFAULT 'BUILD';" + sql "ALTER TABLE instance_nets CHANGE COLUMN status status ENUM('ACTIVE','DOWN','BUILD','ERROR','VIM_ERROR',"\ + "'INACTIVE','DELETED') NOT NULL DEFAULT 'BUILD';" + echo " Shorten back pci at instance_interfaces to allow extended pci for SDN por mapping" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN pci pci VARCHAR(12) NULL DEFAULT NULL COMMENT 'PCI of the "\ + "physical port in the host' AFTER compute_node;" + + for t in flavor image; do + echo " Restore back 'datacenters_${t}s'" + sql "ALTER TABLE datacenters_${t}s ADD COLUMN datacenter_id VARCHAR(36) NULL DEFAULT NULL AFTER "\ + "${t}_id, DROP COLUMN status, DROP COLUMN vim_info ;" + sql "UPDATE datacenters_${t}s as df left join datacenter_tenants as dt on dt.uuid=df.datacenter_vim_id set "\ + "df.datacenter_id=dt.datacenter_id;" + sql "ALTER TABLE datacenters_${t}s CHANGE COLUMN datacenter_id datacenter_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE datacenters_${t}s ADD CONSTRAINT FK__datacenters_${t:0:1} FOREIGN KEY "\ + "(datacenter_id) REFERENCES datacenters (uuid), DROP FOREIGN KEY FK_datacenters_${t}s_datacenter_tenants, "\ + "DROP COLUMN datacenter_vim_id;" + done + + echo " Restore back 'instance_interfaces' coupling to scenarios/vnfs" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(36) NULL DEFAULT NULL;" + sql "ALTER TABLE instance_interfaces DROP FOREIGN KEY FK_instance_ids" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN interface_id interface_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE instance_interfaces ADD CONSTRAINT FK_instance_ids FOREIGN KEY (interface_id) "\ + "REFERENCES interfaces (uuid);" + + echo " Restore back 'instance_vms' coupling to scenarios/vnfs" + echo " Decoupling 'instance vms' from scenarios/vnfs to allow scale actions" + sql "UPDATE instance_vms SET vim_vm_id='' WHERE vim_vm_id is NULL;" + sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE instance_vms DROP FOREIGN KEY FK_instance_vms_vms;" + sql "ALTER TABLE instance_vms CHANGE COLUMN vm_id vm_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE instance_vms ADD CONSTRAINT FK_instance_vms_vms FOREIGN KEY (vm_id) "\ + "REFERENCES vms (uuid);" + + echo " Restore back 'instance_nets' coupling to scenarios/vnfs" + sql "UPDATE instance_nets SET vim_net_id='' WHERE vim_net_id is NULL;" + sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(36) NOT NULL;" + + echo " Restore back 'instance_scenarios' coupling to scenarios" + sql "ALTER TABLE instance_scenarios DROP FOREIGN KEY FK_instance_scenarios_scenarios;" + sql "ALTER TABLE instance_scenarios CHANGE COLUMN scenario_id scenario_id VARCHAR(36) NOT NULL;" + sql "ALTER TABLE instance_scenarios ADD CONSTRAINT FK_instance_scenarios_scenarios FOREIGN KEY (scenario_id) "\ + "REFERENCES scenarios (uuid);" + + echo " Delete table instance_actions" + sql "DROP TABLE IF EXISTS vim_actions" + sql "DROP TABLE IF EXISTS instance_actions" + sql "DELETE FROM schema_version WHERE version_int='26';" +} + +function upgrade_to_27(){ + echo " Added 'encrypted_RO_priv_key','RO_pub_key' to table 'nfvo_tenants'" + sql "ALTER TABLE nfvo_tenants ADD COLUMN encrypted_RO_priv_key VARCHAR(2000) NULL AFTER description;" + sql "ALTER TABLE nfvo_tenants ADD COLUMN RO_pub_key VARCHAR(510) NULL AFTER encrypted_RO_priv_key;" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (27, '0.27', '0.5.25', 'Added encrypted_RO_priv_key,RO_pub_key to table nfvo_tenants', '2017-09-29');" +} +function downgrade_from_27(){ + echo " Remove 'encrypted_RO_priv_key','RO_pub_key' from table 'nfvo_tenants'" + sql "ALTER TABLE nfvo_tenants DROP COLUMN encrypted_RO_priv_key;" + sql "ALTER TABLE nfvo_tenants DROP COLUMN RO_pub_key;" + sql "DELETE FROM schema_version WHERE version_int='27';" +} +function upgrade_to_28(){ + echo " [Adding necessary tables for VNFFG]" + echo " Adding sce_vnffgs" + sql "CREATE TABLE IF NOT EXISTS sce_vnffgs ( + uuid VARCHAR(36) NOT NULL, + tenant_id VARCHAR(36) NULL DEFAULT NULL, + name VARCHAR(255) NOT NULL, + description VARCHAR(255) NULL DEFAULT NULL, + vendor VARCHAR(255) NULL DEFAULT NULL, + scenario_id VARCHAR(36) NOT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_scenarios_sce_vnffg (scenario_id), + CONSTRAINT FK_scenarios_vnffg FOREIGN KEY (tenant_id) REFERENCES scenarios (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding sce_rsps" + sql "CREATE TABLE IF NOT EXISTS sce_rsps ( + uuid VARCHAR(36) NOT NULL, + tenant_id VARCHAR(36) NULL DEFAULT NULL, + name VARCHAR(255) NOT NULL, + sce_vnffg_id VARCHAR(36) NOT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_sce_vnffgs_rsp (sce_vnffg_id), + CONSTRAINT FK_sce_vnffgs_rsp FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding sce_rsp_hops" + sql "CREATE TABLE IF NOT EXISTS sce_rsp_hops ( + uuid VARCHAR(36) NOT NULL, + if_order INT DEFAULT 0 NOT NULL, + interface_id VARCHAR(36) NOT NULL, + sce_vnf_id VARCHAR(36) NOT NULL, + sce_rsp_id VARCHAR(36) NOT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_interfaces_rsp_hop (interface_id), + INDEX FK_sce_vnfs_rsp_hop (sce_vnf_id), + INDEX FK_sce_rsps_rsp_hop (sce_rsp_id), + CONSTRAINT FK_interfaces_rsp_hop FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT FK_sce_vnfs_rsp_hop FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT FK_sce_rsps_rsp_hop FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding sce_classifiers" + sql "CREATE TABLE IF NOT EXISTS sce_classifiers ( + uuid VARCHAR(36) NOT NULL, + tenant_id VARCHAR(36) NULL DEFAULT NULL, + name VARCHAR(255) NOT NULL, + sce_vnffg_id VARCHAR(36) NOT NULL, + sce_rsp_id VARCHAR(36) NOT NULL, + sce_vnf_id VARCHAR(36) NOT NULL, + interface_id VARCHAR(36) NOT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_sce_vnffgs_classifier (sce_vnffg_id), + INDEX FK_sce_rsps_classifier (sce_rsp_id), + INDEX FK_sce_vnfs_classifier (sce_vnf_id), + INDEX FK_interfaces_classifier (interface_id), + CONSTRAINT FK_sce_vnffgs_classifier FOREIGN KEY (sce_vnffg_id) REFERENCES sce_vnffgs (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT FK_sce_rsps_classifier FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT FK_sce_vnfs_classifier FOREIGN KEY (sce_vnf_id) REFERENCES sce_vnfs (uuid) ON UPDATE CASCADE ON DELETE CASCADE, + CONSTRAINT FK_interfaces_classifier FOREIGN KEY (interface_id) REFERENCES interfaces (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding sce_classifier_matches" + sql "CREATE TABLE IF NOT EXISTS sce_classifier_matches ( + uuid VARCHAR(36) NOT NULL, + ip_proto VARCHAR(2) NOT NULL, + source_ip VARCHAR(16) NOT NULL, + destination_ip VARCHAR(16) NOT NULL, + source_port VARCHAR(5) NOT NULL, + destination_port VARCHAR(5) NOT NULL, + sce_classifier_id VARCHAR(36) NOT NULL, + created_at DOUBLE NOT NULL, + modified_at DOUBLE NULL DEFAULT NULL, + PRIMARY KEY (uuid), + INDEX FK_classifiers_classifier_match (sce_classifier_id), + CONSTRAINT FK_sce_classifiers_classifier_match FOREIGN KEY (sce_classifier_id) REFERENCES sce_classifiers (uuid) ON UPDATE CASCADE ON DELETE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + + echo " [Adding necessary tables for VNFFG-SFC instance mapping]" + echo " Adding instance_sfis" + sql "CREATE TABLE IF NOT EXISTS instance_sfis ( + uuid varchar(36) NOT NULL, + instance_scenario_id varchar(36) NOT NULL, + vim_sfi_id varchar(36) DEFAULT NULL, + sce_rsp_hop_id varchar(36) DEFAULT NULL, + datacenter_id varchar(36) DEFAULT NULL, + datacenter_tenant_id varchar(36) DEFAULT NULL, + status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD', + error_msg varchar(1024) DEFAULT NULL, + vim_info text, + created_at double NOT NULL, + modified_at double DEFAULT NULL, + PRIMARY KEY (uuid), + KEY FK_instance_sfis_instance_scenarios (instance_scenario_id), + KEY FK_instance_sfis_sce_rsp_hops (sce_rsp_hop_id), + KEY FK_instance_sfis_datacenters (datacenter_id), + KEY FK_instance_sfis_datacenter_tenants (datacenter_tenant_id), + CONSTRAINT FK_instance_sfis_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid), + CONSTRAINT FK_instance_sfis_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid), + CONSTRAINT FK_instance_sfis_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_instance_sfis_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding instance_sfs" + sql "CREATE TABLE IF NOT EXISTS instance_sfs ( + uuid varchar(36) NOT NULL, + instance_scenario_id varchar(36) NOT NULL, + vim_sf_id varchar(36) DEFAULT NULL, + sce_rsp_hop_id varchar(36) DEFAULT NULL, + datacenter_id varchar(36) DEFAULT NULL, + datacenter_tenant_id varchar(36) DEFAULT NULL, + status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD', + error_msg varchar(1024) DEFAULT NULL, + vim_info text, + created_at double NOT NULL, + modified_at double DEFAULT NULL, + PRIMARY KEY (uuid), + KEY FK_instance_sfs_instance_scenarios (instance_scenario_id), + KEY FK_instance_sfs_sce_rsp_hops (sce_rsp_hop_id), + KEY FK_instance_sfs_datacenters (datacenter_id), + KEY FK_instance_sfs_datacenter_tenants (datacenter_tenant_id), + CONSTRAINT FK_instance_sfs_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid), + CONSTRAINT FK_instance_sfs_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid), + CONSTRAINT FK_instance_sfs_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_instance_sfs_sce_rsp_hops FOREIGN KEY (sce_rsp_hop_id) REFERENCES sce_rsp_hops (uuid) ON DELETE SET NULL ON UPDATE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding instance_classifications" + sql "CREATE TABLE IF NOT EXISTS instance_classifications ( + uuid varchar(36) NOT NULL, + instance_scenario_id varchar(36) NOT NULL, + vim_classification_id varchar(36) DEFAULT NULL, + sce_classifier_match_id varchar(36) DEFAULT NULL, + datacenter_id varchar(36) DEFAULT NULL, + datacenter_tenant_id varchar(36) DEFAULT NULL, + status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD', + error_msg varchar(1024) DEFAULT NULL, + vim_info text, + created_at double NOT NULL, + modified_at double DEFAULT NULL, + PRIMARY KEY (uuid), + KEY FK_instance_classifications_instance_scenarios (instance_scenario_id), + KEY FK_instance_classifications_sce_classifier_matches (sce_classifier_match_id), + KEY FK_instance_classifications_datacenters (datacenter_id), + KEY FK_instance_classifications_datacenter_tenants (datacenter_tenant_id), + CONSTRAINT FK_instance_classifications_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid), + CONSTRAINT FK_instance_classifications_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid), + CONSTRAINT FK_instance_classifications_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_instance_classifications_sce_classifier_matches FOREIGN KEY (sce_classifier_match_id) REFERENCES sce_classifier_matches (uuid) ON DELETE SET NULL ON UPDATE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + echo " Adding instance_sfps" + sql "CREATE TABLE IF NOT EXISTS instance_sfps ( + uuid varchar(36) NOT NULL, + instance_scenario_id varchar(36) NOT NULL, + vim_sfp_id varchar(36) DEFAULT NULL, + sce_rsp_id varchar(36) DEFAULT NULL, + datacenter_id varchar(36) DEFAULT NULL, + datacenter_tenant_id varchar(36) DEFAULT NULL, + status enum('ACTIVE','INACTIVE','BUILD','ERROR','VIM_ERROR','PAUSED','SUSPENDED','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD', + error_msg varchar(1024) DEFAULT NULL, + vim_info text, + created_at double NOT NULL, + modified_at double DEFAULT NULL, + PRIMARY KEY (uuid), + KEY FK_instance_sfps_instance_scenarios (instance_scenario_id), + KEY FK_instance_sfps_sce_rsps (sce_rsp_id), + KEY FK_instance_sfps_datacenters (datacenter_id), + KEY FK_instance_sfps_datacenter_tenants (datacenter_tenant_id), + CONSTRAINT FK_instance_sfps_datacenter_tenants FOREIGN KEY (datacenter_tenant_id) REFERENCES datacenter_tenants (uuid), + CONSTRAINT FK_instance_sfps_datacenters FOREIGN KEY (datacenter_id) REFERENCES datacenters (uuid), + CONSTRAINT FK_instance_sfps_instance_scenarios FOREIGN KEY (instance_scenario_id) REFERENCES instance_scenarios (uuid) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT FK_instance_sfps_sce_rsps FOREIGN KEY (sce_rsp_id) REFERENCES sce_rsps (uuid) ON DELETE SET NULL ON UPDATE CASCADE) + COLLATE='utf8_general_ci' + ENGINE=InnoDB;" + + + echo " [Altering vim_actions table]" + sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces','instance_sfis','instance_sfs','instance_classifications','instance_sfps') NOT NULL COMMENT 'table where the item is stored'" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (28, '0.28', '0.5.28', 'Adding VNFFG-related tables', '2017-11-20');" +} +function downgrade_from_28(){ + echo " [Undo adding the VNFFG tables]" + echo " Dropping instance_sfps" + sql "DROP TABLE IF EXISTS instance_sfps;" + echo " Dropping sce_classifications" + sql "DROP TABLE IF EXISTS instance_classifications;" + echo " Dropping instance_sfs" + sql "DROP TABLE IF EXISTS instance_sfs;" + echo " Dropping instance_sfis" + sql "DROP TABLE IF EXISTS instance_sfis;" + echo " Dropping sce_classifier_matches" + echo " [Undo adding the VNFFG-SFC instance mapping tables]" + sql "DROP TABLE IF EXISTS sce_classifier_matches;" + echo " Dropping sce_classifiers" + sql "DROP TABLE IF EXISTS sce_classifiers;" + echo " Dropping sce_rsp_hops" + sql "DROP TABLE IF EXISTS sce_rsp_hops;" + echo " Dropping sce_rsps" + sql "DROP TABLE IF EXISTS sce_rsps;" + echo " Dropping sce_vnffgs" + sql "DROP TABLE IF EXISTS sce_vnffgs;" + echo " [Altering vim_actions table]" + sql "ALTER TABLE vim_actions MODIFY COLUMN item ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces') NOT NULL COMMENT 'table where the item is stored'" + sql "DELETE FROM schema_version WHERE version_int='28';" +} +function upgrade_to_29(){ + echo " Change 'member_vnf_index' from int to str at 'sce_vnfs'" + sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index VARCHAR(255) NULL DEFAULT NULL AFTER uuid;" + echo " Add osm_id to 'nets's and 'sce_nets'" + sql "ALTER TABLE nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;" + sql "ALTER TABLE sce_nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (29, '0.29', '0.5.59', 'Change member_vnf_index to str accordingly to the model', '2018-04-11');" +} +function downgrade_from_29(){ + echo " Change back 'member_vnf_index' from str to int at 'sce_vnfs'" + sql "ALTER TABLE sce_vnfs CHANGE COLUMN member_vnf_index member_vnf_index SMALLINT NULL DEFAULT NULL AFTER uuid;" + echo " Remove osm_id from 'nets's and 'sce_nets'" + sql "ALTER TABLE nets DROP COLUMN osm_id;" + sql "ALTER TABLE sce_nets DROP COLUMN osm_id;" + sql "DELETE FROM schema_version WHERE version_int='29';" +} +function upgrade_to_30(){ + echo " Add 'image_list' at 'vms' to allocate alternative images" + sql "ALTER TABLE vms ADD COLUMN image_list TEXT NULL COMMENT 'Alternative images' AFTER image_id;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (30, '0.30', '0.5.60', 'Add image_list to vms', '2018-04-24');" +} +function downgrade_from_30(){ + echo " Remove back 'image_list' from 'vms' to allocate alternative images" + sql "ALTER TABLE vms DROP COLUMN image_list;" + sql "DELETE FROM schema_version WHERE version_int='30';" +} +function upgrade_to_31(){ + echo " Add 'vim_network_name' at 'sce_nets'" + sql "ALTER TABLE sce_nets ADD COLUMN vim_network_name VARCHAR(255) NULL DEFAULT NULL AFTER description;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (31, '0.31', '0.5.61', 'Add vim_network_name to sce_nets', '2018-05-03');" +} +function downgrade_from_31(){ + echo " Remove back 'vim_network_name' from 'sce_nets'" + sql "ALTER TABLE sce_nets DROP COLUMN vim_network_name;" + sql "DELETE FROM schema_version WHERE version_int='31';" +} +function upgrade_to_32(){ + echo " Add 'vim_name' to 'instance_vms'" + sql "ALTER TABLE instance_vms ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_vm_id;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (32, '0.32', '0.5.70', 'Add vim_name to instance vms', '2018-06-28');" +} +function downgrade_from_32(){ + echo " Remove back 'vim_name' from 'instance_vms'" + sql "ALTER TABLE instance_vms DROP COLUMN vim_name;" + sql "DELETE FROM schema_version WHERE version_int='32';" +} +function upgrade_to_33(){ + echo " Add PDU information to 'vms'" + sql "ALTER TABLE vms ADD COLUMN pdu_type VARCHAR(255) NULL DEFAULT NULL AFTER osm_id;" + sql "ALTER TABLE instance_nets ADD COLUMN vim_name VARCHAR(255) NULL DEFAULT NULL AFTER vim_net_id;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (33, '0.33', '0.5.82', 'Add pdu information to vms', '2018-11-13');" +} +function downgrade_from_33(){ + echo " Remove back PDU information from 'vms'" + sql "ALTER TABLE vms DROP COLUMN pdu_type;" + sql "ALTER TABLE instance_nets DROP COLUMN vim_name;" + sql "DELETE FROM schema_version WHERE version_int='33';" +} function upgrade_to_X(){ echo " change 'datacenter_nets'" sql "ALTER TABLE datacenter_nets ADD COLUMN vim_tenant_id VARCHAR(36) NOT NULL AFTER datacenter_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id, vim_tenant_id);" @@ -824,50 +1310,272 @@ function downgrade_from_X(){ echo " Change back 'datacenter_nets'" sql "ALTER TABLE datacenter_nets DROP COLUMN vim_tenant_id, DROP INDEX name_datacenter_id, ADD UNIQUE INDEX name_datacenter_id (name, datacenter_id);" } +function upgrade_to_34() { + echo " Create databases required for WIM features" + script="$(find "${DBUTILS}/migrations/up" -iname "34*.sql" | tail -1)" + sql "source ${script}" +} +function downgrade_from_34() { + echo " Drop databases required for WIM features" + script="$(find "${DBUTILS}/migrations/down" -iname "34*.sql" | tail -1)" + sql "source ${script}" +} +function upgrade_to_35(){ + echo " Create databases required for WIM features" + script="$(find "${DBUTILS}/migrations/up" -iname "35*.sql" | tail -1)" + sql "source ${script}" +} +function downgrade_from_35(){ + echo " Drop databases required for WIM features" + script="$(find "${DBUTILS}/migrations/down" -iname "35*.sql" | tail -1)" + sql "source ${script}" +} +function upgrade_to_36(){ + echo " Allow null for image_id at 'vms'" + sql "ALTER TABLE vms ALTER image_id DROP DEFAULT;" + sql "ALTER TABLE vms CHANGE COLUMN image_id image_id VARCHAR(36) NULL COMMENT 'Link to image table' AFTER " \ + "flavor_id;" + echo " Enlarge config at 'wims' and 'wim_accounts'" + sql "ALTER TABLE wims CHANGE COLUMN config config TEXT NULL DEFAULT NULL AFTER wim_url;" + sql "ALTER TABLE wim_accounts CHANGE COLUMN config config TEXT NULL DEFAULT NULL AFTER password;" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) "\ + "VALUES (36, '0.36', '0.6.03', 'Allow vm without image_id for PDUs', '2018-12-19');" +} +function downgrade_from_36(){ + echo " Force back not null for image_id at 'vms'" + sql "ALTER TABLE vms ALTER image_id DROP DEFAULT;" + sql "ALTER TABLE vms CHANGE COLUMN image_id image_id VARCHAR(36) NOT NULL COMMENT 'Link to image table' AFTER " \ + "flavor_id;" + # For downgrade do not restore wims/wim_accounts config to varchar 4000 + sql "DELETE FROM schema_version WHERE version_int='36';" +} +function upgrade_to_37(){ + echo " Adding the enum tags for SFC" + sql "ALTER TABLE vim_wim_actions " \ + "MODIFY COLUMN item " \ + "ENUM('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces'," \ + "'instance_sfis','instance_sfs','instance_classifications','instance_sfps','instance_wim_nets') " \ + "NOT NULL COMMENT 'table where the item is stored';" + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) " \ + "VALUES (37, '0.37', '0.6.09', 'Adding the enum tags for SFC', '2019-02-07');" +} +function downgrade_from_37(){ + echo " Adding the enum tags for SFC isn't going to be reversed" + # It doesn't make sense to reverse to a bug state. + sql "DELETE FROM schema_version WHERE version_int='37';" +} +function upgrade_to_38(){ + echo " Change vim_wim_actions, add worker, related" + sql "ALTER TABLE vim_wim_actions ADD COLUMN worker VARCHAR(64) NULL AFTER task_index, " \ + "ADD COLUMN related VARCHAR(36) NULL AFTER worker, " \ + "CHANGE COLUMN status status ENUM('SCHEDULED','BUILD','DONE','FAILED','SUPERSEDED','FINISHED') " \ + "NOT NULL DEFAULT 'SCHEDULED' AFTER item_id;" + sql "UPDATE vim_wim_actions set related=item_id;" + echo " Change DONE to FINISHED when DELETE has been completed" + sql "UPDATE vim_wim_actions as v1 join vim_wim_actions as v2 on (v1.action='CREATE' or v1.action='FIND') and " \ + "v2.action='DELETE' and (v2.status='SUPERSEDED' or v2.status='DONE') and v1.item_id=v2.item_id " \ + "SET v1.status='FINISHED', v2.status='FINISHED';" + echo " Add osm_id to instance_nets" + sql "ALTER TABLE instance_nets ADD COLUMN osm_id VARCHAR(255) NULL AFTER uuid;" + echo " Add related to instance_xxxx" + for table in instance_classifications instance_nets instance_sfis instance_sfps instance_sfs \ + instance_vms + do + sql "ALTER TABLE $table ADD COLUMN related VARCHAR(36) NULL AFTER vim_info;" + sql "UPDATE $table set related=uuid;" + done + sql "ALTER TABLE instance_wim_nets ADD COLUMN related VARCHAR(36) NULL AFTER wim_info;" + sql "UPDATE instance_wim_nets set related=uuid;" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) " \ + "VALUES (38, '0.38', '0.6.11', 'Adding related to vim_wim_actions', '2019-03-07');" + +} +function downgrade_from_38(){ + echo " Change vim_wim_actions, delete worker, related" + sql "UPDATE vim_wim_actions SET status='DONE' WHERE status='FINISHED';" + sql "ALTER TABLE vim_wim_actions DROP COLUMN worker, DROP COLUMN related, " \ + "CHANGE COLUMN status status ENUM('SCHEDULED','BUILD','DONE','FAILED','SUPERSEDED') " \ + "NOT NULL DEFAULT 'SCHEDULED' AFTER item_id;" + echo " Remove related from instance_xxxx" + for table in instance_classifications instance_nets instance_wim_nets instance_sfis instance_sfps instance_sfs \ + instance_vms + do + sql "ALTER TABLE $table DROP COLUMN related;" + done + echo " Remove osm_id from instance_nets" + sql "ALTER TABLE instance_nets DROP COLUMN osm_id;" + sql "DELETE FROM schema_version WHERE version_int='38';" +} + +function upgrade_to_39(){ + echo " Enlarge vim_id to 300 at all places" + sql "ALTER TABLE datacenters_flavors CHANGE COLUMN vim_id vim_id VARCHAR(300) NOT NULL AFTER datacenter_vim_id;" + sql "ALTER TABLE datacenters_images CHANGE COLUMN vim_id vim_id VARCHAR(300) NOT NULL AFTER datacenter_vim_id;" + sql "ALTER TABLE datacenter_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(300) NOT NULL AFTER name;" + sql "ALTER TABLE instance_classifications CHANGE COLUMN vim_classification_id vim_classification_id VARCHAR(300)" \ + " NULL DEFAULT NULL AFTER instance_scenario_id;" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(300) NULL DEFAULT " \ + " NULL AFTER interface_id;" + sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER osm_id;" + sql "ALTER TABLE instance_sfis CHANGE COLUMN vim_sfi_id vim_sfi_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_sfps CHANGE COLUMN vim_sfp_id vim_sfp_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_sfs CHANGE COLUMN vim_sf_id vim_sf_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER instance_vnf_id, DROP INDEX vim_vm_id;" + sql "ALTER TABLE instance_wim_nets CHANGE COLUMN wim_internal_id wim_internal_id VARCHAR(300) NULL DEFAULT NULL" \ + " COMMENT 'Internal ID used by the WIM to refer to the network' AFTER uuid;" + sql "ALTER TABLE vim_wim_actions CHANGE COLUMN vim_id vim_id VARCHAR(300) NULL DEFAULT NULL" \ + " AFTER datacenter_vim_id;" + + sql "INSERT INTO schema_version (version_int, version, openmano_ver, comments, date) " \ + "VALUES (39, '0.39', '0.6.20', 'Enlarge vim_id to 300 at all places', '2019-05-23');" +} +function downgrade_from_39(){ + echo " Set vim_id to original lenght at all places" + sql "ALTER TABLE datacenters_flavors CHANGE COLUMN vim_id vim_id VARCHAR(36) NOT NULL AFTER datacenter_vim_id;" + sql "ALTER TABLE datacenters_images CHANGE COLUMN vim_id vim_id VARCHAR(36) NOT NULL AFTER datacenter_vim_id;" + sql "ALTER TABLE datacenter_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(36) NOT NULL AFTER name;" + sql "ALTER TABLE instance_classifications CHANGE COLUMN vim_classification_id vim_classification_id VARCHAR(36)" \ + " NULL DEFAULT NULL AFTER instance_scenario_id;" + sql "ALTER TABLE instance_interfaces CHANGE COLUMN vim_interface_id vim_interface_id VARCHAR(128) NULL DEFAULT " \ + " NULL AFTER interface_id;" + sql "ALTER TABLE instance_nets CHANGE COLUMN vim_net_id vim_net_id VARCHAR(128) NULL DEFAULT NULL" \ + " AFTER osm_id;" + sql "ALTER TABLE instance_sfis CHANGE COLUMN vim_sfi_id vim_sfi_id VARCHAR(36) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_sfps CHANGE COLUMN vim_sfp_id vim_sfp_id VARCHAR(36) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_sfs CHANGE COLUMN vim_sf_id vim_sf_id VARCHAR(36) NULL DEFAULT NULL" \ + " AFTER instance_scenario_id;" + sql "ALTER TABLE instance_vms CHANGE COLUMN vim_vm_id vim_vm_id VARCHAR(36) NULL DEFAULT NULL" \ + " AFTER instance_vnf_id, ADD UNIQUE INDEX vim_vm_id (vim_vm_id);" + sql "ALTER TABLE instance_wim_nets CHANGE COLUMN wim_internal_id wim_internal_id VARCHAR(128) NULL DEFAULT NULL" \ + " COMMENT 'Internal ID used by the WIM to refer to the network' AFTER uuid;" + sql "ALTER TABLE vim_wim_actions CHANGE COLUMN vim_id vim_id VARCHAR(64) NULL DEFAULT NULL" \ + " AFTER datacenter_vim_id;" + + sql "DELETE FROM schema_version WHERE version_int='39';" +} #TODO ... put functions here -# echo "db version = "${DATABASE_VER_NUM} -[ $DB_VERSION -eq $DATABASE_VER_NUM ] && echo " current database version '$DATABASE_VER_NUM' is ok" && exit 0 -# Create a backup database content -TEMPFILE2="$(mktemp -q --tmpdir "backupdb.XXXXXX.sql")" -trap 'rm -f "$TEMPFILE2"' EXIT -mysqldump $DEF_EXTRA_FILE_PARAM --add-drop-table --add-drop-database --routines --databases $DBNAME > $TEMPFILE2 +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, openmano_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() { - cat $TEMPFILE2 | mysql $DEF_EXTRA_FILE_PARAM && echo " Rollback database OK" || - echo " Rollback database FAIL" - exit 1 + 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. Aborted!" || rollback_db + echo "$*" | $DBCMD || ! echo " ERROR with command '$*'" || rollback_db return 0 } -#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 +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 + #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 #echo done