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

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