X-Git-Url: https://osm.etsi.org/gitweb/?a=blobdiff_plain;f=RO%2Fosm_ro%2Fdatabase_utils%2Fmigrations%2Fup%2F34_add_wim_tables.sql;fp=RO%2Fosm_ro%2Fdatabase_utils%2Fmigrations%2Fup%2F34_add_wim_tables.sql;h=0000000000000000000000000000000000000000;hb=9f40121f66e644ddf700720d8d4bdf464f6dd414;hp=eb99b8b0f57bc8575e01ec1756786c43533c10e2;hpb=aed948dcfe3e1586c184fd31abacbd3b89eb2a2d;p=osm%2FRO.git diff --git a/RO/osm_ro/database_utils/migrations/up/34_add_wim_tables.sql b/RO/osm_ro/database_utils/migrations/up/34_add_wim_tables.sql deleted file mode 100644 index eb99b8b0..00000000 --- a/RO/osm_ro/database_utils/migrations/up/34_add_wim_tables.sql +++ /dev/null @@ -1,182 +0,0 @@ -/** -* Licensed under the Apache License, Version 2.0 (the "License"); you may -* not use this file except in compliance with the License. You may obtain -* a copy of the License at -* -* http://www.apache.org/licenses/LICENSE-2.0 -* -* Unless required by applicable law or agreed to in writing, software -* distributed under the License is distributed on an "AS IS" BASIS, WITHOUT -* WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the -* License for the specific language governing permissions and limitations -* under the License. -**/ --- --- Setup database structure required for integrating OSM with --- Wide Are Network Infrastructure Managers --- - -DROP TABLE IF EXISTS wims; -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.'; - -DROP TABLE IF EXISTS wim_accounts; -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'; - -DROP TABLE IF EXISTS `wim_nfvo_tenants`; -CREATE TABLE `wim_nfvo_tenants` ( - `id` integer 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_wims_nfvo_tenants` FOREIGN KEY (`wim_id`) - REFERENCES `wims` (`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_nfvo_tenants_wim_accounts` FOREIGN KEY (`nfvo_tenant_id`) - REFERENCES `nfvo_tenants` (`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'; - -DROP TABLE IF EXISTS `instance_wim_nets`; -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_wim_accounts` - FOREIGN KEY (`wim_account_id`) REFERENCES `wim_accounts` (`uuid`), - CONSTRAINT `FK_instance_wim_nets_wims` - FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`), - 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 -) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT - COMMENT='Instances of wim networks'; - -ALTER TABLE `vim_actions` - RENAME TO `vim_wim_actions`; -ALTER TABLE `vim_wim_actions` - ADD `wim_account_id` varchar(36) DEFAULT NULL AFTER `vim_id`, - ADD `wim_internal_id` varchar(64) DEFAULT NULL AFTER `wim_account_id`, - MODIFY `datacenter_vim_id` varchar(36) DEFAULT NULL, - MODIFY `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'; -ALTER TABLE `vim_wim_actions` - ADD INDEX `item_type_id` (`item`, `item_id`); -ALTER TABLE `vim_wim_actions` - ADD INDEX `FK_actions_wims` (`wim_account_id`); -ALTER TABLE `vim_wim_actions` - ADD CONSTRAINT `FK_actions_wims` FOREIGN KEY (`wim_account_id`) - REFERENCES `wim_accounts` (`uuid`) - ON UPDATE CASCADE ON DELETE CASCADE; - -DROP TABLE IF EXISTS `wim_port_mappings`; -CREATE TABLE `wim_port_mappings` ( - `id` integer 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 'In case the WIM plugin relies on the wan_service_mapping_info' - COMMENT 'this field contains a unique identifier used to check the mapping_info consistency', - /* In other words: wan_service_endpoint_id = f(wan_service_mapping_info) - * where f is a injective function' - */ - `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_wims_wim_port_mappings` FOREIGN KEY (`wim_id`) - REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE, - CONSTRAINT `FK_datacenters_wim_port_mappings` FOREIGN KEY (`datacenter_id`) - REFERENCES `datacenters` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE -) -ENGINE=InnoDB DEFAULT CHARSET=utf8 -COMMENT='WIM port mappings managed by the WIM.'; - --- Update Schema with DB version -INSERT INTO schema_version -VALUES (34, '0.34', '0.6.00', 'Added WIM tables', '2018-09-10');