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
6 * http://www.apache.org/licenses/LICENSE-2.0
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
15 -- Setup database structure required for integrating OSM with
16 -- Wide Are Network Infrastructure Managers
19 DROP TABLE IF EXISTS 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,
30 UNIQUE KEY `
name`
(`
name`
)
32 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
33 COMMENT='WIMs managed by the NFVO.';
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,
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
52 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
53 COMMENT='WIM accounts by the user';
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,
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
75 ENGINE
=InnoDB AUTO_INCREMENT
=86 DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
76 COMMENT='WIM accounts mapping to NFVO tenants';
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,
96 'SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
97 `error_msg`
varchar(1024) DEFAULT NULL,
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';
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,
129 'datacenters_flavors',
133 'instance_interfaces',
136 'instance_classifications',
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;
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'
162 `wan_service_mapping_info`
text,
163 `created_at`
double NOT NULL,
164 `modified_at`
double DEFAULT NULL,
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
177 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
178 COMMENT='WIM port mappings managed by the WIM.';
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');