RO/osm_ro/database_utils/migrations/up/34_add_wim_tables.sql

   1 /**
   2 * Licensed under the Apache License, Version 2.0 (the "License"); you may
   3 * not use this file except in compliance with the License. You may obtain
   4 * a copy of the License at
   5 *
   6 *         http://www.apache.org/licenses/LICENSE-2.0
   7 *
   8 * Unless required by applicable law or agreed to in writing, software
   9 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
  10 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  11 * License for the specific language governing permissions and limitations
  12 * under the License.
  13 **/
  14 --
  15 -- Setup database structure required for integrating OSM with
  16 -- Wide Are Network Infrastructure Managers
  17 --
  18
  19 DROP TABLE IF EXISTS wims;
  20 CREATE TABLE wims (
  21   `uuid` varchar(36) NOT NULL,
  22   `name` varchar(255) NOT NULL,
  23   `description` varchar(255) DEFAULT NULL,
  24   `type` varchar(36) NOT NULL DEFAULT 'odl',
  25   `wim_url` varchar(150) NOT NULL,
  26   `config` varchar(4000) DEFAULT NULL,
  27   `created_at` double NOT NULL,
  28   `modified_at` double DEFAULT NULL,
  29   PRIMARY KEY (`uuid`),
  30   UNIQUE KEY `name` (`name`)
  31 )
  32 ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
  33 COMMENT='WIMs managed by the NFVO.';
  34
  35 DROP TABLE IF EXISTS wim_accounts;
  36 CREATE TABLE wim_accounts (
  37   `uuid` varchar(36) NOT NULL,
  38   `name` varchar(255) DEFAULT NULL,
  39   `wim_id` varchar(36) NOT NULL,
  40   `created` enum('true','false') NOT NULL DEFAULT 'false',
  41   `user` varchar(64) DEFAULT NULL,
  42   `password` varchar(64) DEFAULT NULL,
  43   `config` varchar(4000) DEFAULT NULL,
  44   `created_at` double NOT NULL,
  45   `modified_at` double DEFAULT NULL,
  46   PRIMARY KEY (`uuid`),
  47   UNIQUE KEY `wim_name` (`wim_id`,`name`),
  48   KEY `FK_wim_accounts_wims` (`wim_id`),
  49   CONSTRAINT `FK_wim_accounts_wims` FOREIGN KEY (`wim_id`)
  50     REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
  51 )
  52 ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
  53 COMMENT='WIM accounts by the user';
  54
  55 DROP TABLE IF EXISTS `wim_nfvo_tenants`;
  56 CREATE TABLE `wim_nfvo_tenants` (
  57   `id` integer NOT NULL AUTO_INCREMENT,
  58   `nfvo_tenant_id` varchar(36) NOT NULL,
  59   `wim_id` varchar(36) NOT NULL,
  60   `wim_account_id` varchar(36) NOT NULL,
  61   `created_at` double NOT NULL,
  62   `modified_at` double DEFAULT NULL,
  63   PRIMARY KEY (`id`),
  64   UNIQUE KEY `wim_nfvo_tenant` (`wim_id`,`nfvo_tenant_id`),
  65   KEY `FK_wims_nfvo_tenants` (`wim_id`),
  66   KEY `FK_wim_accounts_nfvo_tenants` (`wim_account_id`),
  67   KEY `FK_nfvo_tenants_wim_accounts` (`nfvo_tenant_id`),
  68   CONSTRAINT `FK_wims_nfvo_tenants` FOREIGN KEY (`wim_id`)
  69     REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
  70   CONSTRAINT `FK_wim_accounts_nfvo_tenants` FOREIGN KEY (`wim_account_id`)
  71     REFERENCES `wim_accounts` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
  72   CONSTRAINT `FK_nfvo_tenants_wim_accounts` FOREIGN KEY (`nfvo_tenant_id`)
  73     REFERENCES `nfvo_tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
  74 )
  75 ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
  76 COMMENT='WIM accounts mapping to NFVO tenants';
  77
  78 DROP TABLE IF EXISTS `instance_wim_nets`;
  79 CREATE TABLE `instance_wim_nets` (
  80   `uuid` varchar(36) NOT NULL,
  81   `wim_internal_id` varchar(128) DEFAULT NULL
  82     COMMENT 'Internal ID used by the WIM to refer to the network',
  83   `instance_scenario_id` varchar(36) DEFAULT NULL,
  84   `sce_net_id` varchar(36) DEFAULT NULL,
  85   `wim_id` varchar(36) DEFAULT NULL,
  86   `wim_account_id` varchar(36) NOT NULL,
  87   `status` enum(
  88     'ACTIVE',
  89     'INACTIVE',
  90     'DOWN',
  91     'BUILD',
  92     'ERROR',
  93     'WIM_ERROR',
  94     'DELETED',
  95     'SCHEDULED_CREATION',
  96     'SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
  97   `error_msg` varchar(1024) DEFAULT NULL,
  98   `wim_info` text,
  99   `multipoint` enum('true','false') NOT NULL DEFAULT 'false',
 100   `created` enum('true','false') NOT NULL DEFAULT 'false'
 101       COMMENT 'Created or already exists at WIM',
 102   `created_at` double NOT NULL,
 103   `modified_at` double DEFAULT NULL,
 104   PRIMARY KEY (`uuid`),
 105   KEY `FK_instance_wim_nets_instance_scenarios` (`instance_scenario_id`),
 106   KEY `FK_instance_wim_nets_sce_nets` (`sce_net_id`),
 107   KEY `FK_instance_wim_nets_wims` (`wim_id`),
 108   KEY `FK_instance_wim_nets_wim_accounts` (`wim_account_id`),
 109   CONSTRAINT `FK_instance_wim_nets_wim_accounts`
 110     FOREIGN KEY (`wim_account_id`) REFERENCES `wim_accounts` (`uuid`),
 111   CONSTRAINT `FK_instance_wim_nets_wims`
 112     FOREIGN KEY (`wim_id`) REFERENCES `wims` (`uuid`),
 113   CONSTRAINT `FK_instance_wim_nets_instance_scenarios`
 114     FOREIGN KEY (`instance_scenario_id`) REFERENCES `instance_scenarios` (`uuid`)
 115     ON DELETE CASCADE ON UPDATE CASCADE,
 116   CONSTRAINT `FK_instance_wim_nets_sce_nets`
 117     FOREIGN KEY (`sce_net_id`) REFERENCES `sce_nets` (`uuid`)
 118     ON DELETE SET NULL ON UPDATE CASCADE
 119 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
 120   COMMENT='Instances of wim networks';
 121
 122 ALTER TABLE `vim_actions`
 123   RENAME TO `vim_wim_actions`;
 124 ALTER TABLE `vim_wim_actions`
 125   ADD `wim_account_id` varchar(36) DEFAULT NULL AFTER `vim_id`,
 126   ADD `wim_internal_id` varchar(64) DEFAULT NULL AFTER `wim_account_id`,
 127   MODIFY `datacenter_vim_id` varchar(36) DEFAULT NULL,
 128   MODIFY `item` enum(
 129     'datacenters_flavors',
 130     'datacenter_images',
 131     'instance_nets',
 132     'instance_vms',
 133     'instance_interfaces',
 134     'instance_sfis',
 135     'instance_sfs',
 136     'instance_classifications',
 137     'instance_sfps',
 138     'instance_wim_nets') NOT NULL
 139   COMMENT 'table where the item is stored';
 140 ALTER TABLE `vim_wim_actions`
 141   ADD INDEX `item_type_id` (`item`, `item_id`);
 142 ALTER TABLE `vim_wim_actions`
 143   ADD INDEX `FK_actions_wims` (`wim_account_id`);
 144 ALTER TABLE `vim_wim_actions`
 145   ADD CONSTRAINT `FK_actions_wims` FOREIGN KEY (`wim_account_id`)
 146   REFERENCES `wim_accounts` (`uuid`)
 147   ON UPDATE CASCADE ON DELETE CASCADE;
 148
 149 DROP TABLE IF EXISTS `wim_port_mappings`;
 150 CREATE TABLE `wim_port_mappings` (
 151   `id` integer NOT NULL AUTO_INCREMENT,
 152   `wim_id` varchar(36) NOT NULL,
 153   `datacenter_id` varchar(36) NOT NULL,
 154   `pop_switch_dpid` varchar(64) NOT NULL,
 155   `pop_switch_port` varchar(64) NOT NULL,
 156   `wan_service_endpoint_id` varchar(256) NOT NULL
 157       COMMENT 'In case the WIM plugin relies on the wan_service_mapping_info'
 158       COMMENT 'this field contains a unique identifier used to check the mapping_info consistency',
 159       /* In other words: wan_service_endpoint_id = f(wan_service_mapping_info)
 160        * where f is a injective function'
 161        */
 162   `wan_service_mapping_info` text,
 163   `created_at` double NOT NULL,
 164   `modified_at` double DEFAULT NULL,
 165   PRIMARY KEY (`id`),
 166   UNIQUE KEY `unique_datacenter_port_mapping`
 167     (`datacenter_id`, `pop_switch_dpid`, `pop_switch_port`),
 168   UNIQUE KEY `unique_wim_port_mapping`
 169     (`wim_id`, `wan_service_endpoint_id`),
 170   KEY `FK_wims_wim_physical_connections` (`wim_id`),
 171   KEY `FK_datacenters_wim_port_mappings` (`datacenter_id`),
 172   CONSTRAINT `FK_wims_wim_port_mappings` FOREIGN KEY (`wim_id`)
 173     REFERENCES `wims` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
 174   CONSTRAINT `FK_datacenters_wim_port_mappings` FOREIGN KEY (`datacenter_id`)
 175     REFERENCES `datacenters` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 176 )
 177 ENGINE=InnoDB DEFAULT CHARSET=utf8
 178 COMMENT='WIM port mappings managed by the WIM.';
 179
 180 -- Update Schema with DB version
 181 INSERT INTO schema_version
 182 VALUES (34, '0.34', '0.6.00', 'Added WIM tables', '2018-09-10');