From: tierno Date: Fri, 21 Dec 2018 10:19:38 +0000 (+0000) Subject: bug 608 Database migration recovering with docker relaunch X-Git-Tag: v5.0.3~7 X-Git-Url: https://osm.etsi.org/gitweb/?p=osm%2FRO.git;a=commitdiff_plain;h=fc7df372047d3e16f8e3779544d175175e33bef3 bug 608 Database migration recovering with docker relaunch Change-Id: I4005e048b71284c154495a90d8b1256a09e6ff98 Signed-off-by: tierno --- diff --git a/database_utils/init_mano_db.sh b/database_utils/init_mano_db.sh index be7d3eea..147ea389 100755 --- a/database_utils/init_mano_db.sh +++ b/database_utils/init_mano_db.sh @@ -161,7 +161,8 @@ DBHOST_="" && [ -n "$DBHOST" ] && DBHOST_="-h$DBHOST" DBPORT_="-P$DBPORT" echo " loading ${DIRNAME}/mano_db_structure.sql" -sed -e "s/{{mano_db}}/$DBNAME/" ${DIRNAME}/mano_db_structure.sql | mysql $DEF_EXTRA_FILE_PARAM +sed -e "s/{{mano_db}}/$DBNAME/" ${DIRNAME}/mano_db_structure.sql | mysql $DEF_EXTRA_FILE_PARAM || + ! echo "ERROR at init $DBNAME" || exit 1 echo " migrage database version" # echo "${DIRNAME}/migrate_mano_db.sh $DBHOST_ $DBPORT_ $DBUSER_ $DBPASS_ -d$DBNAME $QUIET_MODE $DB_VERSION" diff --git a/database_utils/mano_db_structure.sql b/database_utils/mano_db_structure.sql index 2bedc389..747ffe27 100644 --- a/database_utils/mano_db_structure.sql +++ b/database_utils/mano_db_structure.sql @@ -19,11 +19,11 @@ * contact with: nfvlabs@tid.es **/ --- MySQL dump 10.13 Distrib 5.7.20, for Linux (x86_64) +-- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64) -- -- Host: localhost Database: {{mano_db}} -- ------------------------------------------------------ --- Server version 5.7.20-0ubuntu0.16.04.1 +-- Server version 5.7.24 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -42,7 +42,7 @@ /*!40000 DROP DATABASE IF EXISTS `{{mano_db}}`*/; -CREATE DATABASE /*!32312 IF NOT EXISTS*/ `{{mano_db}}` /*!40100 DEFAULT CHARACTER SET latin1 */; +CREATE DATABASE /*!32312 IF NOT EXISTS*/ `{{mano_db}}` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `{{mano_db}}`; @@ -228,6 +228,37 @@ CREATE TABLE `instance_actions` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Contains client actions over instances'; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `instance_classifications` +-- + +DROP TABLE IF EXISTS `instance_classifications`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `instance_interfaces` -- @@ -271,6 +302,7 @@ DROP TABLE IF EXISTS `instance_nets`; CREATE TABLE `instance_nets` ( `uuid` varchar(36) NOT NULL, `vim_net_id` varchar(128) DEFAULT NULL, + `vim_name` varchar(255) DEFAULT NULL, `instance_scenario_id` varchar(36) DEFAULT NULL, `sce_net_id` varchar(36) DEFAULT NULL, `net_id` varchar(36) DEFAULT NULL, @@ -328,6 +360,99 @@ CREATE TABLE `instance_scenarios` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Instances of scenarios defined by the user'; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `instance_sfis` +-- + +DROP TABLE IF EXISTS `instance_sfis`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `instance_sfps` +-- + +DROP TABLE IF EXISTS `instance_sfps`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `instance_sfs` +-- + +DROP TABLE IF EXISTS `instance_sfs`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `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 +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `instance_vms` -- @@ -340,6 +465,7 @@ CREATE TABLE `instance_vms` ( `instance_vnf_id` varchar(36) NOT NULL, `vm_id` varchar(36) DEFAULT NULL, `vim_vm_id` varchar(128) DEFAULT NULL, + `vim_name` varchar(255) DEFAULT NULL, `status` enum('ACTIVE:NoMgmtIP','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, @@ -384,6 +510,39 @@ CREATE TABLE `instance_vnfs` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Instances of VNFs as part of a scenario'; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `instance_wim_nets` +-- + +DROP TABLE IF EXISTS `instance_wim_nets`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `instance_wim_nets` ( + `uuid` varchar(36) NOT NULL, + `wim_internal_id` varchar(128) DEFAULT NULL COMMENT 'Internal ID used by the WIM to refer to the network', + `instance_scenario_id` varchar(36) DEFAULT NULL, + `sce_net_id` varchar(36) DEFAULT NULL, + `wim_id` varchar(36) DEFAULT NULL, + `wim_account_id` varchar(36) NOT NULL, + `status` enum('ACTIVE','INACTIVE','DOWN','BUILD','ERROR','WIM_ERROR','DELETED','SCHEDULED_CREATION','SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD', + `error_msg` varchar(1024) DEFAULT NULL, + `wim_info` text, + `multipoint` enum('true','false') NOT NULL DEFAULT 'false', + `created` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'Created or already exists at WIM', + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_instance_wim_nets_instance_scenarios` (`instance_scenario_id`), + KEY `FK_instance_wim_nets_sce_nets` (`sce_net_id`), + KEY `FK_instance_wim_nets_wims` (`wim_id`), + KEY `FK_instance_wim_nets_wim_accounts` (`wim_account_id`), + CONSTRAINT `FK_instance_wim_nets_instance_scenarios` FOREIGN KEY (`instance_scenario_id`) REFERENCES `instance_scenarios` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_instance_wim_nets_sce_nets` FOREIGN KEY (`sce_net_id`) REFERENCES `sce_nets` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE, + CONSTRAINT `FK_instance_wim_nets_wim_accounts` FOREIGN KEY (`wim_account_id`) REFERENCES `wim_accounts` (`uuid`), + CONSTRAINT `FK_instance_wim_nets_wims` FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Instances of wim networks'; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `interfaces` -- @@ -474,6 +633,7 @@ DROP TABLE IF EXISTS `nets`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `nets` ( `uuid` varchar(36) NOT NULL, + `osm_id` varchar(255) DEFAULT NULL, `vnf_id` varchar(36) NOT NULL, `name` varchar(255) NOT NULL, `type` enum('bridge','data','ptp') NOT NULL DEFAULT 'data' COMMENT 'Type of network', @@ -507,6 +667,58 @@ CREATE TABLE `nfvo_tenants` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Scenarios defined by the user'; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `sce_classifier_matches` +-- + +DROP TABLE IF EXISTS `sce_classifier_matches`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `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 DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_classifiers_classifier_match` (`sce_classifier_id`), + CONSTRAINT `FK_sce_classifiers_classifier_match` FOREIGN KEY (`sce_classifier_id`) REFERENCES `sce_classifiers` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sce_classifiers` +-- + +DROP TABLE IF EXISTS `sce_classifiers`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sce_classifiers` ( + `uuid` varchar(36) NOT NULL, + `tenant_id` varchar(36) 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 DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_sce_vnffgs_classifier` (`sce_vnffg_id`), + KEY `FK_sce_rsps_classifier` (`sce_rsp_id`), + KEY `FK_sce_vnfs_classifier` (`sce_vnf_id`), + KEY `FK_interfaces_classifier` (`interface_id`), + CONSTRAINT `FK_interfaces_classifier` FOREIGN KEY (`interface_id`) REFERENCES `interfaces` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_sce_rsps_classifier` FOREIGN KEY (`sce_rsp_id`) REFERENCES `sce_rsps` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_sce_vnffgs_classifier` FOREIGN KEY (`sce_vnffg_id`) REFERENCES `sce_vnffgs` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_sce_vnfs_classifier` FOREIGN KEY (`sce_vnf_id`) REFERENCES `sce_vnfs` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `sce_interfaces` -- @@ -541,12 +753,14 @@ DROP TABLE IF EXISTS `sce_nets`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sce_nets` ( `uuid` varchar(36) NOT NULL, + `osm_id` varchar(255) DEFAULT NULL, `name` varchar(255) NOT NULL, `scenario_id` varchar(36) DEFAULT NULL COMMENT 'NULL if net is matched to several scenarios', `type` enum('bridge','data','ptp') NOT NULL DEFAULT 'data' COMMENT 'Type of network', `multipoint` enum('true','false') NOT NULL DEFAULT 'true', `external` enum('true','false') NOT NULL DEFAULT 'false' COMMENT 'If external, net is already present at VIM', `description` varchar(255) DEFAULT NULL, + `vim_network_name` varchar(255) DEFAULT NULL, `created_at` double NOT NULL, `modified_at` double DEFAULT NULL, `graph` varchar(2000) DEFAULT NULL, @@ -556,6 +770,74 @@ CREATE TABLE `sce_nets` ( ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='Networks in a scenario definition. It only considers networks among VNFs. Networks among internal VMs are only considered in tble ''nets''.'; /*!40101 SET character_set_client = @saved_cs_client */; +-- +-- Table structure for table `sce_rsp_hops` +-- + +DROP TABLE IF EXISTS `sce_rsp_hops`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sce_rsp_hops` ( + `uuid` varchar(36) NOT NULL, + `if_order` int(11) NOT NULL DEFAULT '0', + `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 DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_interfaces_rsp_hop` (`interface_id`), + KEY `FK_sce_vnfs_rsp_hop` (`sce_vnf_id`), + KEY `FK_sce_rsps_rsp_hop` (`sce_rsp_id`), + CONSTRAINT `FK_interfaces_rsp_hop` FOREIGN KEY (`interface_id`) REFERENCES `interfaces` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_sce_rsps_rsp_hop` FOREIGN KEY (`sce_rsp_id`) REFERENCES `sce_rsps` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_sce_vnfs_rsp_hop` FOREIGN KEY (`sce_vnf_id`) REFERENCES `sce_vnfs` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sce_rsps` +-- + +DROP TABLE IF EXISTS `sce_rsps`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sce_rsps` ( + `uuid` varchar(36) NOT NULL, + `tenant_id` varchar(36) DEFAULT NULL, + `name` varchar(255) NOT NULL, + `sce_vnffg_id` varchar(36) NOT NULL, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_sce_vnffgs_rsp` (`sce_vnffg_id`), + CONSTRAINT `FK_sce_vnffgs_rsp` FOREIGN KEY (`sce_vnffg_id`) REFERENCES `sce_vnffgs` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `sce_vnffgs` +-- + +DROP TABLE IF EXISTS `sce_vnffgs`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `sce_vnffgs` ( + `uuid` varchar(36) NOT NULL, + `tenant_id` varchar(36) DEFAULT NULL, + `name` varchar(255) NOT NULL, + `description` varchar(255) DEFAULT NULL, + `vendor` varchar(255) DEFAULT NULL, + `scenario_id` varchar(36) NOT NULL, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`uuid`), + KEY `FK_scenarios_sce_vnffg` (`scenario_id`), + KEY `FK_scenarios_vnffg` (`tenant_id`), + CONSTRAINT `FK_scenarios_vnffg` FOREIGN KEY (`tenant_id`) REFERENCES `scenarios` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; + -- -- Table structure for table `sce_vnfs` -- @@ -565,7 +847,7 @@ DROP TABLE IF EXISTS `sce_vnfs`; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `sce_vnfs` ( `uuid` varchar(36) NOT NULL, - `member_vnf_index` smallint(6) DEFAULT NULL, + `member_vnf_index` varchar(255) DEFAULT NULL, `name` varchar(255) NOT NULL, `scenario_id` varchar(36) NOT NULL, `vnf_id` varchar(36) NOT NULL, @@ -668,19 +950,21 @@ CREATE TABLE `uuids` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `vim_actions` +-- Table structure for table `vim_wim_actions` -- -DROP TABLE IF EXISTS `vim_actions`; +DROP TABLE IF EXISTS `vim_wim_actions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; -CREATE TABLE `vim_actions` ( +CREATE TABLE `vim_wim_actions` ( `instance_action_id` varchar(36) NOT NULL, `task_index` int(6) NOT NULL, - `datacenter_vim_id` varchar(36) NOT NULL, + `datacenter_vim_id` varchar(36) DEFAULT NULL, `vim_id` varchar(64) DEFAULT NULL, + `wim_account_id` varchar(36) DEFAULT NULL, + `wim_internal_id` varchar(64) 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` enum('datacenters_flavors','datacenter_images','instance_nets','instance_vms','instance_interfaces','instance_wim_nets') NOT NULL COMMENT 'table where the item is stored', `item_id` varchar(36) DEFAULT NULL COMMENT 'uuid of the entry in the table', `status` enum('SCHEDULED','BUILD','DONE','FAILED','SUPERSEDED') NOT NULL DEFAULT 'SCHEDULED', `extra` text COMMENT 'json with params:, depends_on: for the task', @@ -690,8 +974,11 @@ CREATE TABLE `vim_actions` ( PRIMARY KEY (`task_index`,`instance_action_id`), KEY `FK_actions_instance_actions` (`instance_action_id`), KEY `FK_actions_vims` (`datacenter_vim_id`), + KEY `item_type_id` (`item`,`item_id`), + KEY `FK_actions_wims` (`wim_account_id`), CONSTRAINT `FK_actions_instance_actions` FOREIGN KEY (`instance_action_id`) REFERENCES `instance_actions` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_actions_vims` FOREIGN KEY (`datacenter_vim_id`) REFERENCES `datacenter_tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE + CONSTRAINT `FK_actions_vims` FOREIGN KEY (`datacenter_vim_id`) REFERENCES `datacenter_tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_actions_wims` FOREIGN KEY (`wim_account_id`) REFERENCES `wim_accounts` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table with the individual VIM actions.'; /*!40101 SET character_set_client = @saved_cs_client */; @@ -705,11 +992,13 @@ DROP TABLE IF EXISTS `vms`; CREATE TABLE `vms` ( `uuid` varchar(36) NOT NULL, `osm_id` varchar(255) DEFAULT NULL, + `pdu_type` varchar(255) DEFAULT NULL, `name` varchar(255) NOT NULL, `vnf_id` varchar(36) NOT NULL, `count` smallint(6) NOT NULL DEFAULT '1', `flavor_id` varchar(36) NOT NULL COMMENT 'Link to flavor table', `image_id` varchar(36) NOT NULL COMMENT 'Link to image table', + `image_list` text COMMENT 'Alternative images', `image_path` varchar(100) DEFAULT NULL COMMENT 'Path where the image of the VM is located', `boot_data` text, `description` varchar(255) DEFAULT NULL, @@ -757,7 +1046,104 @@ CREATE TABLE `vnfs` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Dumping routines for database '{{mano_db}}' +-- Table structure for table `wim_accounts` +-- + +DROP TABLE IF EXISTS `wim_accounts`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `wim_accounts` ( + `uuid` varchar(36) NOT NULL, + `name` varchar(255) DEFAULT NULL, + `wim_id` varchar(36) NOT NULL, + `created` enum('true','false') NOT NULL DEFAULT 'false', + `user` varchar(64) DEFAULT NULL, + `password` varchar(64) DEFAULT NULL, + `config` varchar(4000) DEFAULT NULL, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`uuid`), + UNIQUE KEY `wim_name` (`wim_id`,`name`), + KEY `FK_wim_accounts_wims` (`wim_id`), + CONSTRAINT `FK_wim_accounts_wims` FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='WIM accounts by the user'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `wim_nfvo_tenants` +-- + +DROP TABLE IF EXISTS `wim_nfvo_tenants`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `wim_nfvo_tenants` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `nfvo_tenant_id` varchar(36) NOT NULL, + `wim_id` varchar(36) NOT NULL, + `wim_account_id` varchar(36) NOT NULL, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `wim_nfvo_tenant` (`wim_id`,`nfvo_tenant_id`), + KEY `FK_wims_nfvo_tenants` (`wim_id`), + KEY `FK_wim_accounts_nfvo_tenants` (`wim_account_id`), + KEY `FK_nfvo_tenants_wim_accounts` (`nfvo_tenant_id`), + CONSTRAINT `FK_nfvo_tenants_wim_accounts` FOREIGN KEY (`nfvo_tenant_id`) REFERENCES `nfvo_tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_wim_accounts_nfvo_tenants` FOREIGN KEY (`wim_account_id`) REFERENCES `wim_accounts` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_wims_nfvo_tenants` FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='WIM accounts mapping to NFVO tenants'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `wim_port_mappings` +-- + +DROP TABLE IF EXISTS `wim_port_mappings`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `wim_port_mappings` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `wim_id` varchar(36) NOT NULL, + `datacenter_id` varchar(36) NOT NULL, + `pop_switch_dpid` varchar(64) NOT NULL, + `pop_switch_port` varchar(64) NOT NULL, + `wan_service_endpoint_id` varchar(256) NOT NULL COMMENT 'this field contains a unique identifier used to check the mapping_info consistency', + `wan_service_mapping_info` text, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `unique_datacenter_port_mapping` (`datacenter_id`,`pop_switch_dpid`,`pop_switch_port`), + UNIQUE KEY `unique_wim_port_mapping` (`wim_id`,`wan_service_endpoint_id`), + KEY `FK_wims_wim_physical_connections` (`wim_id`), + KEY `FK_datacenters_wim_port_mappings` (`datacenter_id`), + CONSTRAINT `FK_datacenters_wim_port_mappings` FOREIGN KEY (`datacenter_id`) REFERENCES `datacenters` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, + CONSTRAINT `FK_wims_wim_port_mappings` FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='WIM port mappings managed by the WIM.'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Table structure for table `wims` +-- + +DROP TABLE IF EXISTS `wims`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `wims` ( + `uuid` varchar(36) NOT NULL, + `name` varchar(255) NOT NULL, + `description` varchar(255) DEFAULT NULL, + `type` varchar(36) NOT NULL DEFAULT 'odl', + `wim_url` varchar(150) NOT NULL, + `config` varchar(4000) DEFAULT NULL, + `created_at` double NOT NULL, + `modified_at` double DEFAULT NULL, + PRIMARY KEY (`uuid`), + UNIQUE KEY `name` (`name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='WIMs managed by the NFVO.'; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- +-- Dumping routines for database 'mano_db' -- /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -769,17 +1155,17 @@ CREATE TABLE `vnfs` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2017-11-23 10:24:39 +-- Dump completed on 2018-12-10 9:58:03 --- MySQL dump 10.13 Distrib 5.7.20, for Linux (x86_64) +-- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64) -- -- Host: localhost Database: {{mano_db}} -- ------------------------------------------------------ --- Server version 5.7.20-0ubuntu0.16.04.1 +-- Server version 5.7.24 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -798,7 +1184,44 @@ CREATE TABLE `vnfs` ( LOCK TABLES `schema_version` WRITE; /*!40000 ALTER TABLE `schema_version` DISABLE KEYS */; -INSERT INTO `schema_version` VALUES (1,'0.1','0.2.2','insert schema_version','2015-05-08'),(2,'0.2','0.2.5','new tables images,flavors','2015-07-13'),(3,'0.3','0.3.3','alter vim_tenant tables','2015-07-28'),(4,'0.4','0.3.5','enlarge graph field at sce_vnfs/nets','2015-10-20'),(5,'0.5','0.4.1','Add mac address for bridge interfaces','2015-12-14'),(6,'0.6','0.4.2','Adding VIM status info','2015-12-22'),(7,'0.7','0.4.3','Changing created_at time at database','2016-01-25'),(8,'0.8','0.4.32','Enlarging name at database','2016-02-01'),(9,'0.9','0.4.33','Add ACTIVE:NoMgmtIP to instance_vms table','2016-02-05'),(10,'0.10','0.4.36','tenant management of vnfs,scenarios','2016-03-08'),(11,'0.11','0.4.43','remove unique name at scenarios,instance_scenarios','2016-07-18'),(12,'0.12','0.4.46','create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to interfaces and sce_interfaces','2016-08-29'),(13,'0.13','0.4.47','insert cloud-config at scenarios,instance_scenarios','2016-08-30'),(14,'0.14','0.4.57','remove unique index vim_net_id, instance_scenario_id','2016-09-26'),(15,'0.15','0.4.59','add columns universal_name and checksum at table images, add unique index universal_name_checksum, and change location to allow NULL; change column image_path in table vms to allow NULL','2016-09-27'),(16,'0.16','0.5.2','enlarge vim_tenant_name and id. New config at datacenter_tenants','2016-10-11'),(17,'0.17','0.5.3','Extra description json format of additional devices in datacenter_flavors','2016-12-20'),(18,'0.18','0.5.4','Add columns \'floating_ip\' and \'port_security\' at tables \'interfaces\' and \'instance_interfaces\'','2017-01-09'),(19,'0.19','0.5.5','Extra Boot-data content at VNFC (vms)','2017-01-11'),(20,'0.20','0.5.9','Added columns to store dataplane connectivity info','2017-03-13'),(21,'0.21','0.5.15','Edit instance_nets to allow instance_scenario_id=None and enlarge column dns_address at table ip_profiles','2017-06-02'),(22,'0.22','0.5.16','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-06-02'),(23,'0.23','0.5.20','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-08-29'),(24,'0.24','0.5.21','Added vnfd fields','2017-08-29'),(25,'0.25','0.5.22','Added osm_id to vnfs,scenarios','2017-09-01'),(26,'0.26','0.5.23','Several changes','2017-09-09'),(27,'0.27','0.5.25','Added encrypted_RO_priv_key,RO_pub_key to table nfvo_tenants','2017-09-29'); +INSERT INTO `schema_version` VALUES +(0,'0.0','0.0.0','Database in init process','2015-05-08'), +(1,'0.1','0.2.2','insert schema_version','2015-05-08'), +(2,'0.2','0.2.5','new tables images,flavors','2015-07-13'), +(3,'0.3','0.3.3','alter vim_tenant tables','2015-07-28'), +(4,'0.4','0.3.5','enlarge graph field at sce_vnfs/nets','2015-10-20'), +(5,'0.5','0.4.1','Add mac address for bridge interfaces','2015-12-14'), +(6,'0.6','0.4.2','Adding VIM status info','2015-12-22'), +(7,'0.7','0.4.3','Changing created_at time at database','2016-01-25'), +(8,'0.8','0.4.32','Enlarging name at database','2016-02-01'), +(9,'0.9','0.4.33','Add ACTIVE:NoMgmtIP to instance_vms table','2016-02-05'), +(10,'0.10','0.4.36','tenant management of vnfs,scenarios','2016-03-08'), +(11,'0.11','0.4.43','remove unique name at scenarios,instance_scenarios','2016-07-18'), +(12,'0.12','0.4.46','create ip_profiles table, with foreign keys to all nets tables, and add ip_address column to interfaces and sce_interfaces','2016-08-29'), +(13,'0.13','0.4.47','insert cloud-config at scenarios,instance_scenarios','2016-08-30'), +(14,'0.14','0.4.57','remove unique index vim_net_id, instance_scenario_id','2016-09-26'), +(15,'0.15','0.4.59','add columns universal_name and checksum at table images, add unique index universal_name_checksum, and change location to allow NULL; change column image_path in table vms to allow NULL','2016-09-27'), +(16,'0.16','0.5.2','enlarge vim_tenant_name and id. New config at datacenter_tenants','2016-10-11'), +(17,'0.17','0.5.3','Extra description json format of additional devices in datacenter_flavors','2016-12-20'), +(18,'0.18','0.5.4','Add columns \'floating_ip\' and \'port_security\' at tables \'interfaces\' and \'instance_interfaces\'','2017-01-09'), +(19,'0.19','0.5.5','Extra Boot-data content at VNFC (vms)','2017-01-11'), +(20,'0.20','0.5.9','Added columns to store dataplane connectivity info','2017-03-13'), +(21,'0.21','0.5.15','Edit instance_nets to allow instance_scenario_id=None and enlarge column dns_address at table ip_profiles','2017-06-02'), +(22,'0.22','0.5.16','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-06-02'), +(23,'0.23','0.5.20','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-08-29'), +(24,'0.24','0.5.21','Added vnfd fields','2017-08-29'), +(25,'0.25','0.5.22','Added osm_id to vnfs,scenarios','2017-09-01'), +(26,'0.26','0.5.23','Several changes','2017-09-09'), +(27,'0.27','0.5.25','Added encrypted_RO_priv_key,RO_pub_key to table nfvo_tenants','2017-09-29'), +(28,'0.28','0.5.28','Adding VNFFG-related tables','2017-11-20'), +(29,'0.29','0.5.59','Change member_vnf_index to str accordingly to the model','2018-04-11'), +(30,'0.30','0.5.60','Add image_list to vms','2018-04-24'), +(31,'0.31','0.5.61','Add vim_network_name to sce_nets','2018-05-03'), +(32,'0.32','0.5.70','Add vim_name to instance vms','2018-06-28'), +(33,'0.33','0.5.82','Add pdu information to vms','2018-11-13'), +(34,'0.34','0.6.00','Added WIM tables','2018-09-10'), +(35,'0.35','0.6.02','Adding ingress and egress ports for RSPs','2018-12-11'), +(36,'0.36','0.6.03','Allow vm without image_id for PDUs','2018-12-19'); /*!40000 ALTER TABLE `schema_version` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -811,4 +1234,4 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2017-11-23 10:24:39 +-- Dump completed on 2018-12-10 9:58:03 diff --git a/database_utils/migrate_mano_db.sh b/database_utils/migrate_mano_db.sh index 3970a7ca..2d3f1bcf 100755 --- a/database_utils/migrate_mano_db.sh +++ b/database_utils/migrate_mano_db.sh @@ -33,6 +33,8 @@ DBHOST="" DBPORT="3306" DBNAME="mano_db" QUIET_MODE="" +BACKUP_DIR="" +BACKUP_FILE="" #TODO update it with the last database version LAST_DB_VERSION=36 @@ -51,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" @@ -72,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 ;; @@ -143,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 @@ -151,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 @@ -1360,19 +1349,31 @@ function downgrade_from_36(){ #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 " Aborted! Rollback database OK" || - echo " Aborted! 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 @@ -1381,27 +1382,86 @@ function sql() # send a sql command 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 diff --git a/scripts/RO-start.sh b/scripts/RO-start.sh index 53e189c0..9297559a 100755 --- a/scripts/RO-start.sh +++ b/scripts/RO-start.sh @@ -17,7 +17,12 @@ function is_db_created() { db_version=$6 # minimun database version if mysqlshow -h"$db_host" -P"$db_port" -u"$db_user" -p"$db_pswd" | grep -v Wildcard | grep -q -e "$db_name" ; then - if echo "SELECT * FROM schema_version WHERE version='$db_version'" | + if echo "SELECT * FROM schema_version WHERE version='0'" | + mysql -h"$db_host" -P"$db_port" -u"$db_user" -p"$db_pswd" "$db_name" | + grep -q -e "init" ; then + echo " DB $db_name exists BUT failed in previous init" + return 1 + elif echo "SELECT * FROM schema_version WHERE version='$db_version'" | mysql -h"$db_host" -P"$db_port" -u"$db_user" -p"$db_pswd" "$db_name" | grep -q -e "$db_version" ; then echo " DB $db_name exists and inited" @@ -103,7 +108,7 @@ then else echo " migrate database version" ${RO_PATH}/database_utils/migrate_mano_db.sh -u "$RO_DB_USER" -p "$RO_DB_PASSWORD" -h "$RO_DB_HOST" \ - -P "$RO_DB_PORT" -d "$RO_DB_NAME" + -P "$RO_DB_PORT" -d "$RO_DB_NAME" -b /var/log/osm fi OVIM_PATH=`python -c 'import lib_osm_openvim; print(lib_osm_openvim.__path__[0])'` @@ -125,7 +130,7 @@ then else echo " migrate database version" ${OVIM_PATH}/database_utils/migrate_vim_db.sh -u "$RO_DB_OVIM_USER" -p "$RO_DB_OVIM_PASSWORD" -h "$RO_DB_OVIM_HOST"\ - -P "$RO_DB_OVIM_PORT" -d "$RO_DB_OVIM_NAME" + -P "$RO_DB_OVIM_PORT" -d "$RO_DB_OVIM_NAME" -b /var/log/osm fi