2 -- Setup database structure required for integrating OSM with
3 -- Wide Are Network Infrastructure Managers
6 DROP TABLE IF EXISTS 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,
17 UNIQUE KEY `
name`
(`
name`
)
19 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
20 COMMENT='WIMs managed by the NFVO.';
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,
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
39 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
40 COMMENT='WIM accounts by the user';
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,
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
62 ENGINE
=InnoDB AUTO_INCREMENT
=86 DEFAULT CHARSET
=utf8 ROW_FORMAT
=COMPACT
63 COMMENT='WIM accounts mapping to NFVO tenants';
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,
83 'SCHEDULED_DELETION') NOT NULL DEFAULT 'BUILD',
84 `error_msg`
varchar(1024) DEFAULT NULL,
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,
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';
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,
116 'datacenters_flavors',
120 'instance_interfaces',
123 'instance_classifications',
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;
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'
149 `wan_service_mapping_info`
text,
150 `created_at`
double NOT NULL,
151 `modified_at`
double DEFAULT NULL,
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
164 ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
165 COMMENT='WIM port mappings managed by the WIM.';
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');