2 * Copyright 2017 Telefónica Investigación y Desarrollo, S.A.U.
3 * This file is part of openvim
6 * Licensed under the Apache License, Version 2.0 (the "License"); you may
7 * not use this file except in compliance with the License. You may obtain
8 * a copy of the License at
10 * http://www.apache.org/licenses/LICENSE-2.0
12 * Unless required by applicable law or agreed to in writing, software
13 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
14 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
15 * License for the specific language governing permissions and limitations
18 * For those usages not covered by the Apache License, Version 2.0 please
19 * contact with: nfvlabs@tid.es
22 -- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64)
24 -- Host: localhost Database: {{vim_db}}
25 -- ------------------------------------------------------
26 -- Server version 5.7.24
28 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
29 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
30 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
31 /*!40101 SET NAMES utf8 */;
32 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
33 /*!40103 SET TIME_ZONE='+00:00' */;
34 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
35 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
36 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
37 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
40 -- Current Database: `{{vim_db}}`
43 /*!40000 DROP DATABASE IF EXISTS `{{vim_db}}`*/;
45 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `
{{vim_db
}}`
/*!40100 DEFAULT CHARACTER SET utf8 */;
50 -- Table structure for table `flavors`
53 DROP TABLE IF EXISTS `flavors`
;
54 /*!40101 SET @saved_cs_client = @@character_set_client */;
55 /*!40101 SET character_set_client = utf8 */;
56 CREATE TABLE `flavors`
(
57 `uuid`
varchar(36) NOT NULL,
58 `
name`
varchar(255) NOT NULL,
59 `description`
varchar(255) DEFAULT NULL,
60 `disk`
smallint(5) unsigned
DEFAULT NULL,
61 `ram`
mediumint(7) unsigned
DEFAULT NULL,
62 `vcpus`
smallint(5) unsigned
DEFAULT NULL,
63 `extended`
varchar(2000) DEFAULT NULL COMMENT 'Extra description yaml format of needed resources and pining, orginized in sets per numa',
64 `
public`
enum('yes','no') NOT NULL DEFAULT 'no',
65 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
67 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='flavors with extra vnfcd info';
68 /*!40101 SET character_set_client = @saved_cs_client */;
71 -- Table structure for table `host_ranking`
74 DROP TABLE IF EXISTS `host_ranking`
;
75 /*!40101 SET @saved_cs_client = @@character_set_client */;
76 /*!40101 SET character_set_client = utf8 */;
77 CREATE TABLE `host_ranking`
(
78 `
id`
int(10) NOT NULL AUTO_INCREMENT
,
79 `
family`
varchar(50) NOT NULL,
80 `manufacturer`
varchar(50) NOT NULL,
81 `
version`
varchar(50) NOT NULL,
82 `description`
varchar(50) DEFAULT NULL,
83 `ranking`
smallint(4) unsigned
NOT NULL,
85 UNIQUE KEY `family_manufacturer_version`
(`
family`
,`manufacturer`
,`
version`
)
86 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
87 /*!40101 SET character_set_client = @saved_cs_client */;
90 -- Table structure for table `hosts`
93 DROP TABLE IF EXISTS `hosts`
;
94 /*!40101 SET @saved_cs_client = @@character_set_client */;
95 /*!40101 SET character_set_client = utf8 */;
96 CREATE TABLE `hosts`
(
97 `uuid`
varchar(36) NOT NULL,
98 `
name`
varchar(255) NOT NULL,
99 `ip_name`
varchar(64) NOT NULL,
100 `description`
varchar(255) DEFAULT NULL,
101 `status`
enum('ok','error','notused') NOT NULL DEFAULT 'ok',
102 `ranking`
smallint(6) NOT NULL DEFAULT '0',
103 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
104 `features`
varchar(255) DEFAULT NULL,
105 `hypervisors`
varchar(255) NOT NULL DEFAULT 'kvm',
106 `
user`
varchar(64) NOT NULL,
107 `
password`
varchar(64) DEFAULT NULL,
108 `keyfile`
varchar(255) DEFAULT NULL,
109 `admin_state_up`
enum('true','false') NOT NULL DEFAULT 'true',
110 `RAM`
mediumint(8) unsigned
NOT NULL DEFAULT '0' COMMENT 'Host memory in MB not used as hugepages',
111 `cpus`
smallint(5) unsigned
NOT NULL DEFAULT '0' COMMENT 'Host threads(or cores) not isolated from OS',
112 PRIMARY KEY (`uuid`
),
113 UNIQUE KEY `ip_name`
(`ip_name`
)
114 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='hosts information';
115 /*!40101 SET character_set_client = @saved_cs_client */;
118 -- Table structure for table `images`
121 DROP TABLE IF EXISTS `images`
;
122 /*!40101 SET @saved_cs_client = @@character_set_client */;
123 /*!40101 SET character_set_client = utf8 */;
124 CREATE TABLE `images`
(
125 `uuid`
varchar(36) NOT NULL,
126 `
path`
varchar(255) NOT NULL,
127 `
name`
varchar(255) NOT NULL,
128 `checksum`
varchar(32) DEFAULT NULL,
129 `description`
varchar(255) DEFAULT NULL,
130 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
131 `modified_at`
timestamp NULL DEFAULT NULL,
132 `
public`
enum('yes','no') NOT NULL DEFAULT 'no',
133 `progress`
tinyint(3) unsigned
NOT NULL DEFAULT '100',
134 `status`
enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
135 `metadata`
varchar(2000) DEFAULT NULL COMMENT 'Metatdata in json text format',
136 PRIMARY KEY (`uuid`
),
137 UNIQUE KEY `
path`
(`
path`
)
138 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
139 /*!40101 SET character_set_client = @saved_cs_client */;
142 -- Table structure for table `instance_devices`
145 DROP TABLE IF EXISTS `instance_devices`
;
146 /*!40101 SET @saved_cs_client = @@character_set_client */;
147 /*!40101 SET character_set_client = utf8 */;
148 CREATE TABLE `instance_devices`
(
149 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
150 `
type`
enum('usb','disk','cdrom','xml') NOT NULL,
151 `
xml`
varchar(1000) DEFAULT NULL COMMENT 'libvirt XML format for aditional device',
152 `instance_id`
varchar(36) NOT NULL,
153 `image_id`
varchar(36) DEFAULT NULL COMMENT 'Used in case type is disk',
154 `vpci`
char(12) DEFAULT NULL COMMENT 'format XXXX:XX:XX.X',
155 `dev`
varchar(12) DEFAULT NULL,
156 `image_size`
int(11) DEFAULT NULL,
158 KEY `FK_instance_devices_instances`
(`instance_id`
),
159 KEY `FK_instance_devices_images`
(`image_id`
),
160 CONSTRAINT `FK_instance_devices_images`
FOREIGN KEY (`image_id`
) REFERENCES `tenants_images`
(`image_id`
),
161 CONSTRAINT `FK_instance_devices_instances`
FOREIGN KEY (`instance_id`
) REFERENCES `instances`
(`uuid`
) ON DELETE CASCADE
162 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
163 /*!40101 SET character_set_client = @saved_cs_client */;
166 -- Table structure for table `instances`
169 DROP TABLE IF EXISTS `instances`
;
170 /*!40101 SET @saved_cs_client = @@character_set_client */;
171 /*!40101 SET character_set_client = utf8 */;
172 CREATE TABLE `instances`
(
173 `uuid`
varchar(36) NOT NULL,
174 `flavor_id`
varchar(36) NOT NULL,
175 `hypervisor`
enum('kvm','xen-unik','xenhvm') NOT NULL DEFAULT 'kvm',
176 `os_image_type`
varchar(24) NOT NULL DEFAULT 'other',
177 `image_id`
varchar(36) NOT NULL,
178 `
name`
varchar(64) NOT NULL,
179 `description`
varchar(255) DEFAULT NULL,
180 `last_error`
varchar(255) DEFAULT NULL,
181 `progress`
tinyint(3) unsigned
NOT NULL DEFAULT '0',
182 `tenant_id`
varchar(36) NOT NULL,
183 `status`
enum('ACTIVE','PAUSED','INACTIVE','CREATING','ERROR','DELETING') NOT NULL DEFAULT 'ACTIVE',
184 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
185 `modified_at`
timestamp NULL DEFAULT NULL,
186 `host_id`
varchar(36) NOT NULL COMMENT 'HOST where is allocated',
187 `ram`
mediumint(8) unsigned
NOT NULL DEFAULT '0' COMMENT 'used non-hugepages memory in MB',
188 `vcpus`
smallint(5) unsigned
NOT NULL DEFAULT '0' COMMENT 'used non-isolated CPUs',
189 PRIMARY KEY (`uuid`
),
190 KEY `FK_instances_tenants`
(`tenant_id`
),
191 KEY `FK_instances_flavors`
(`flavor_id`
),
192 KEY `FK_instances_images`
(`image_id`
),
193 KEY `FK_instances_hosts`
(`host_id`
),
194 CONSTRAINT `FK_instances_flavors`
FOREIGN KEY (`flavor_id`
) REFERENCES `tenants_flavors`
(`flavor_id`
),
195 CONSTRAINT `FK_instances_hosts`
FOREIGN KEY (`host_id`
) REFERENCES `hosts`
(`uuid`
),
196 CONSTRAINT `FK_instances_images`
FOREIGN KEY (`image_id`
) REFERENCES `tenants_images`
(`image_id`
),
197 CONSTRAINT `FK_instances_tenants`
FOREIGN KEY (`tenant_id`
) REFERENCES `tenants`
(`uuid`
)
198 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='VM instances';
199 /*!40101 SET character_set_client = @saved_cs_client */;
202 -- Table structure for table `logs`
205 DROP TABLE IF EXISTS `logs`
;
206 /*!40101 SET @saved_cs_client = @@character_set_client */;
207 /*!40101 SET character_set_client = utf8 */;
208 CREATE TABLE `logs`
(
209 `
id`
int(10) unsigned
NOT NULL AUTO_INCREMENT
,
210 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
211 `tenant_id`
varchar(36) DEFAULT NULL,
212 `related`
enum('hosts','images','flavors','tenants','ports','instances','nets') DEFAULT NULL,
213 `uuid`
varchar(36) DEFAULT NULL COMMENT 'uuid of host, image, etc that log relates to',
214 `
level`
enum('panic','error','info','debug','verbose') NOT NULL,
215 `description`
varchar(200) NOT NULL,
217 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
218 /*!40101 SET character_set_client = @saved_cs_client */;
221 -- Table structure for table `nets`
224 DROP TABLE IF EXISTS `nets`
;
225 /*!40101 SET @saved_cs_client = @@character_set_client */;
226 /*!40101 SET character_set_client = utf8 */;
227 CREATE TABLE `nets`
(
228 `uuid`
varchar(36) NOT NULL,
229 `tenant_id`
varchar(36) DEFAULT NULL,
230 `
type`
enum('ptp','data','bridge_data','bridge_man') NOT NULL DEFAULT 'bridge_man',
231 `status`
enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
232 `last_error`
varchar(255) DEFAULT NULL,
233 `
name`
varchar(255) NOT NULL,
234 `shared`
enum('true','false') NOT NULL DEFAULT 'false',
235 `admin_state_up`
enum('true','false') NOT NULL DEFAULT 'true',
236 `region`
varchar(64) DEFAULT NULL,
237 `vlan`
smallint(6) DEFAULT NULL,
238 `provider`
varchar(36) DEFAULT NULL,
239 `bind_net`
varchar(36) DEFAULT NULL COMMENT 'To connect with other net',
240 `bind_type`
varchar(36) DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove',
241 `cidr`
varchar(64) DEFAULT NULL,
242 `enable_dhcp`
enum('true','false') NOT NULL DEFAULT 'false',
243 `dhcp_first_ip`
varchar(64) DEFAULT NULL,
244 `dhcp_last_ip`
varchar(64) DEFAULT NULL,
245 `gateway_ip`
varchar(64) DEFAULT NULL,
246 `dns`
varchar(255) DEFAULT NULL,
249 PRIMARY KEY (`uuid`
),
250 UNIQUE KEY `physical`
(`provider`
),
251 UNIQUE KEY `region_vlan`
(`region`
,`vlan`
),
252 KEY `FK_nets_tenants`
(`tenant_id`
),
253 CONSTRAINT `FK_nets_tenants`
FOREIGN KEY (`tenant_id`
) REFERENCES `tenants`
(`uuid`
)
254 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
255 /*!40101 SET character_set_client = @saved_cs_client */;
258 -- Table structure for table `numas`
261 DROP TABLE IF EXISTS `numas`
;
262 /*!40101 SET @saved_cs_client = @@character_set_client */;
263 /*!40101 SET character_set_client = utf8 */;
264 CREATE TABLE `numas`
(
265 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
266 `host_id`
varchar(36) NOT NULL,
267 `numa_socket`
tinyint(3) unsigned
NOT NULL DEFAULT '0',
268 `hugepages`
smallint(5) unsigned
NOT NULL DEFAULT '0' COMMENT 'Available memory for guest in GB',
269 `status`
enum('ok','error','notused') NOT NULL DEFAULT 'ok',
270 `memory`
smallint(5) unsigned
NOT NULL DEFAULT '0' COMMENT 'total memry in GB, not all available for guests',
271 `admin_state_up`
enum('true','false') NOT NULL DEFAULT 'true',
273 KEY `FK_numas_hosts`
(`host_id`
),
274 CONSTRAINT `FK_numas_hosts`
FOREIGN KEY (`host_id`
) REFERENCES `hosts`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE
275 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
276 /*!40101 SET character_set_client = @saved_cs_client */;
279 -- Table structure for table `of_flows`
282 DROP TABLE IF EXISTS `of_flows`
;
283 /*!40101 SET @saved_cs_client = @@character_set_client */;
284 /*!40101 SET character_set_client = utf8 */;
285 CREATE TABLE `of_flows`
(
286 `
id`
int(10) unsigned
NOT NULL AUTO_INCREMENT
,
287 `
name`
varchar(64) NOT NULL,
288 `net_id`
varchar(36) DEFAULT NULL,
289 `ofc_id`
varchar(36) DEFAULT NULL,
290 `priority`
int(10) unsigned
DEFAULT NULL,
291 `vlan_id`
smallint(5) unsigned
DEFAULT NULL,
292 `ingress_port`
varchar(10) DEFAULT NULL,
293 `src_mac`
varchar(50) DEFAULT NULL,
294 `dst_mac`
varchar(50) DEFAULT NULL,
295 `actions`
varchar(255) DEFAULT NULL,
297 UNIQUE KEY `
name`
(`
name`
),
298 KEY `FK_of_flows_nets`
(`net_id`
),
299 KEY `FK_of_flows_ofcs`
(`ofc_id`
),
300 CONSTRAINT `FK_of_flows_nets`
FOREIGN KEY (`net_id`
) REFERENCES `nets`
(`uuid`
) ON DELETE SET NULL ON UPDATE CASCADE,
301 CONSTRAINT `FK_of_flows_ofcs`
FOREIGN KEY (`ofc_id`
) REFERENCES `ofcs`
(`uuid`
) ON DELETE SET NULL ON UPDATE CASCADE
302 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
303 /*!40101 SET character_set_client = @saved_cs_client */;
306 -- Table structure for table `of_port_mappings`
309 DROP TABLE IF EXISTS `of_port_mappings`
;
310 /*!40101 SET @saved_cs_client = @@character_set_client */;
311 /*!40101 SET character_set_client = utf8 */;
312 CREATE TABLE `of_port_mappings`
(
313 `uuid`
varchar(36) NOT NULL,
314 `ofc_id`
varchar(36) DEFAULT NULL,
315 `region`
varchar(64) DEFAULT NULL,
316 `compute_node`
varchar(64) DEFAULT NULL,
317 `pci`
varchar(50) DEFAULT NULL,
318 `switch_dpid`
varchar(64) DEFAULT NULL,
319 `switch_port`
varchar(64) DEFAULT NULL,
320 `switch_mac`
char(18) DEFAULT NULL,
321 UNIQUE KEY `region_compute_node_pci`
(`region`
,`compute_node`
,`pci`
),
322 UNIQUE KEY `switch_dpid_switch_port`
(`switch_dpid`
,`switch_port`
,`pci`
),
323 UNIQUE KEY `switch_dpid_switch_mac`
(`switch_dpid`
,`switch_mac`
,`pci`
),
324 KEY `FK_of_port_mappings_ofcs`
(`ofc_id`
),
325 CONSTRAINT `FK_of_port_mappings_ofcs`
FOREIGN KEY (`ofc_id`
) REFERENCES `ofcs`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE
326 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
327 /*!40101 SET character_set_client = @saved_cs_client */;
330 -- Table structure for table `of_ports_pci_correspondence`
333 DROP TABLE IF EXISTS `of_ports_pci_correspondence`
;
334 /*!40101 SET @saved_cs_client = @@character_set_client */;
335 /*!40101 SET character_set_client = utf8 */;
336 CREATE TABLE `of_ports_pci_correspondence`
(
337 `
id`
int(10) NOT NULL AUTO_INCREMENT
,
338 `ip_name`
varchar(64) DEFAULT NULL,
339 `pci`
varchar(50) DEFAULT NULL,
340 `switch_port`
varchar(64) DEFAULT NULL,
341 `switch_dpid`
varchar(64) DEFAULT NULL,
343 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
344 /*!40101 SET character_set_client = @saved_cs_client */;
347 -- Table structure for table `ofcs`
350 DROP TABLE IF EXISTS `ofcs`
;
351 /*!40101 SET @saved_cs_client = @@character_set_client */;
352 /*!40101 SET character_set_client = utf8 */;
353 CREATE TABLE `ofcs`
(
354 `uuid`
varchar(36) NOT NULL,
355 `
name`
varchar(255) NOT NULL,
356 `dpid`
varchar(64) NOT NULL,
357 `ip`
varchar(64) NOT NULL,
358 `port`
int(5) NOT NULL,
359 `
type`
varchar(64) NOT NULL,
360 `
version`
varchar(12) DEFAULT NULL,
361 `
user`
varchar(64) DEFAULT NULL,
362 `
password`
varchar(64) DEFAULT NULL,
363 `last_error`
varchar(255) DEFAULT NULL,
364 `status`
enum('ACTIVE','INACTIVE','ERROR') DEFAULT 'ACTIVE',
365 `nets_with_same_vlan`
enum('true','false') NOT NULL DEFAULT 'false',
367 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
368 /*!40101 SET character_set_client = @saved_cs_client */;
371 -- Table structure for table `ports`
374 DROP TABLE IF EXISTS `ports`
;
375 /*!40101 SET @saved_cs_client = @@character_set_client */;
376 /*!40101 SET character_set_client = utf8 */;
377 CREATE TABLE `ports`
(
378 `uuid`
varchar(36) NOT NULL,
379 `
name`
varchar(64) NOT NULL,
380 `instance_id`
varchar(36) DEFAULT NULL,
381 `tenant_id`
varchar(36) DEFAULT NULL,
382 `net_id`
varchar(36) DEFAULT NULL,
383 `vpci`
char(12) DEFAULT NULL,
384 `Mbps`
mediumint(8) unsigned
DEFAULT NULL COMMENT 'In Mbits/s',
385 `admin_state_up`
enum('true','false') NOT NULL DEFAULT 'true',
386 `status`
enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
387 `
type`
enum('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge',
388 `vlan`
smallint(5) DEFAULT NULL COMMENT 'vlan of this SRIOV, or external port',
389 `switch_port`
varchar(64) DEFAULT NULL,
390 `switch_mac`
varchar(18) DEFAULT NULL,
391 `switch_dpid`
varchar(64) DEFAULT NULL,
392 `ofc_id`
varchar(36) DEFAULT NULL,
393 `mac`
char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
394 `ip_address`
varchar(64) DEFAULT NULL,
395 `model`
varchar(12) DEFAULT NULL COMMENT 'driver model for bridge ifaces; PF,VF,VFnotShared for data ifaces',
396 PRIMARY KEY (`uuid`
),
397 UNIQUE KEY `mac`
(`mac`
),
398 KEY `FK_instance_ifaces_instances`
(`instance_id`
),
399 KEY `FK_instance_ifaces_nets`
(`net_id`
),
400 KEY `FK_ports_tenants`
(`tenant_id`
),
401 KEY `FK_port_ofc_id`
(`ofc_id`
),
402 CONSTRAINT `FK_instance_ifaces_nets`
FOREIGN KEY (`net_id`
) REFERENCES `nets`
(`uuid`
),
403 CONSTRAINT `FK_port_ofc_id`
FOREIGN KEY (`ofc_id`
) REFERENCES `ofcs`
(`uuid`
),
404 CONSTRAINT `FK_ports_instances`
FOREIGN KEY (`instance_id`
) REFERENCES `instances`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE,
405 CONSTRAINT `FK_ports_tenants`
FOREIGN KEY (`tenant_id`
) REFERENCES `tenants`
(`uuid`
)
406 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Bridge interfaces used by instances';
407 /*!40101 SET character_set_client = @saved_cs_client */;
410 -- Table structure for table `resources_core`
413 DROP TABLE IF EXISTS `resources_core`
;
414 /*!40101 SET @saved_cs_client = @@character_set_client */;
415 /*!40101 SET character_set_client = utf8 */;
416 CREATE TABLE `resources_core`
(
417 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
418 `numa_id`
int(11) DEFAULT NULL,
419 `core_id`
smallint(5) unsigned
NOT NULL,
420 `thread_id`
smallint(5) unsigned
NOT NULL,
421 `instance_id`
varchar(36) DEFAULT NULL COMMENT 'instance that consume this resource',
422 `v_thread_id`
smallint(6) DEFAULT NULL COMMENT 'name used by virtual machine; -1 if this thread is not used because core is asigned completely',
423 `status`
enum('ok','error','notused','noteligible') NOT NULL DEFAULT 'ok' COMMENT '''error'': resource not available becasue an error at deployment; ''notused'': admin marked as not available, ''noteligible'': used by host and not available for guests',
424 `paired`
enum('Y','N') NOT NULL DEFAULT 'N',
426 KEY `FK_resources_core_instances`
(`instance_id`
),
427 KEY `FK_resources_core_numas`
(`numa_id`
),
428 CONSTRAINT `FK_resources_core_instances`
FOREIGN KEY (`instance_id`
) REFERENCES `instances`
(`uuid`
),
429 CONSTRAINT `FK_resources_core_numas`
FOREIGN KEY (`numa_id`
) REFERENCES `numas`
(`
id`
) ON DELETE CASCADE ON UPDATE CASCADE
430 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Contain an entry by thread (two entries per core) of all available cores. Threy will be free if instance_id is NULL';
431 /*!40101 SET character_set_client = @saved_cs_client */;
434 -- Table structure for table `resources_mem`
437 DROP TABLE IF EXISTS `resources_mem`
;
438 /*!40101 SET @saved_cs_client = @@character_set_client */;
439 /*!40101 SET character_set_client = utf8 */;
440 CREATE TABLE `resources_mem`
(
441 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
442 `numa_id`
int(11) NOT NULL DEFAULT '0',
443 `instance_id`
varchar(36) DEFAULT '0' COMMENT 'NULL is allowed in order to allow some memory not used',
444 `consumed`
int(3) unsigned
NOT NULL DEFAULT '0' COMMENT 'In GB',
446 KEY `FK_resources_mem_instances`
(`instance_id`
),
447 KEY `FK_resources_mem_numas`
(`numa_id`
),
448 CONSTRAINT `FK_resources_mem_instances`
FOREIGN KEY (`instance_id`
) REFERENCES `instances`
(`uuid`
) ON DELETE CASCADE,
449 CONSTRAINT `FK_resources_mem_numas`
FOREIGN KEY (`numa_id`
) REFERENCES `numas`
(`
id`
) ON UPDATE CASCADE
450 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Include the hugepages memory used by one instance (VM) in one host NUMA.';
451 /*!40101 SET character_set_client = @saved_cs_client */;
454 -- Table structure for table `resources_port`
457 DROP TABLE IF EXISTS `resources_port`
;
458 /*!40101 SET @saved_cs_client = @@character_set_client */;
459 /*!40101 SET character_set_client = utf8 */;
460 CREATE TABLE `resources_port`
(
461 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
462 `numa_id`
int(11) NOT NULL DEFAULT '0',
463 `instance_id`
varchar(36) DEFAULT NULL COMMENT 'Contain instance that use this resource completely. NULL if this resource is free or partially used (resources_port_SRIOV)',
464 `port_id`
varchar(36) DEFAULT NULL COMMENT 'When resource is used, this point to the ports table',
465 `source_name`
varchar(64) DEFAULT NULL,
466 `pci`
char(12) NOT NULL DEFAULT '0' COMMENT 'Host physical pci bus. Format XXXX:XX:XX.X',
467 `Mbps`
smallint(5) unsigned
DEFAULT '10' COMMENT 'Nominal Port speed ',
468 `root_id`
int(11) DEFAULT NULL COMMENT 'NULL for physical port entries; =id for SRIOV port',
469 `status`
enum('ok','error','notused') NOT NULL DEFAULT 'ok',
470 `Mbps_used`
smallint(5) unsigned
NOT NULL DEFAULT '0' COMMENT 'Speed bandwidth used when asigned',
471 `switch_port`
varchar(64) DEFAULT NULL,
472 `switch_mac`
varchar(18) DEFAULT NULL,
473 `switch_dpid`
varchar(64) DEFAULT NULL,
474 `ofc_id`
varchar(36) DEFAULT NULL,
475 `mac`
char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
477 UNIQUE KEY `mac`
(`mac`
),
478 UNIQUE KEY `port_id`
(`port_id`
),
479 KEY `FK_resources_port_numas`
(`numa_id`
),
480 KEY `FK_resources_port_instances`
(`instance_id`
),
481 KEY `FK_resource_ofc_id`
(`ofc_id`
),
482 CONSTRAINT `FK_resource_ofc_id`
FOREIGN KEY (`ofc_id`
) REFERENCES `ofcs`
(`uuid`
),
483 CONSTRAINT `FK_resources_port_instances`
FOREIGN KEY (`instance_id`
) REFERENCES `instances`
(`uuid`
),
484 CONSTRAINT `FK_resources_port_numas`
FOREIGN KEY (`numa_id`
) REFERENCES `numas`
(`
id`
) ON DELETE CASCADE ON UPDATE CASCADE,
485 CONSTRAINT `FK_resources_port_ports`
FOREIGN KEY (`port_id`
) REFERENCES `ports`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE
486 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Contain NIC ports SRIOV and availabes, and current use. Every port contain several entries, one per port (root_id=NULL) and all posible SRIOV (root_id=id of port)';
487 /*!40101 SET character_set_client = @saved_cs_client */;
490 -- Table structure for table `schema_version`
493 DROP TABLE IF EXISTS `schema_version`
;
494 /*!40101 SET @saved_cs_client = @@character_set_client */;
495 /*!40101 SET character_set_client = utf8 */;
496 CREATE TABLE `schema_version`
(
497 `version_int`
int(11) NOT NULL COMMENT 'version as a number. Must not contain gaps',
498 `
version`
varchar(20) NOT NULL COMMENT 'version as a text',
499 `openvim_ver`
varchar(20) NOT NULL COMMENT 'openvim version',
500 `
comments`
varchar(2000) DEFAULT NULL COMMENT 'changes to database',
501 `
date`
date DEFAULT NULL,
502 PRIMARY KEY (`version_int`
)
503 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='database schema control version';
504 /*!40101 SET character_set_client = @saved_cs_client */;
507 -- Table structure for table `tenants`
510 DROP TABLE IF EXISTS `tenants`
;
511 /*!40101 SET @saved_cs_client = @@character_set_client */;
512 /*!40101 SET character_set_client = utf8 */;
513 CREATE TABLE `tenants`
(
514 `uuid`
varchar(36) NOT NULL,
515 `
name`
varchar(255) NOT NULL,
516 `description`
varchar(255) DEFAULT NULL,
517 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
518 `enabled`
enum('true','false') NOT NULL DEFAULT 'true',
519 PRIMARY KEY (`uuid`
),
520 UNIQUE KEY `
name`
(`
name`
)
521 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='tenants information';
522 /*!40101 SET character_set_client = @saved_cs_client */;
525 -- Table structure for table `tenants_flavors`
528 DROP TABLE IF EXISTS `tenants_flavors`
;
529 /*!40101 SET @saved_cs_client = @@character_set_client */;
530 /*!40101 SET character_set_client = utf8 */;
531 CREATE TABLE `tenants_flavors`
(
532 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
533 `flavor_id`
varchar(36) NOT NULL,
534 `tenant_id`
varchar(36) NOT NULL,
536 KEY `FK__tenants`
(`tenant_id`
),
537 KEY `FK__flavors`
(`flavor_id`
),
538 CONSTRAINT `FK__flavors`
FOREIGN KEY (`flavor_id`
) REFERENCES `flavors`
(`uuid`
),
539 CONSTRAINT `FK__tenants`
FOREIGN KEY (`tenant_id`
) REFERENCES `tenants`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE
540 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
541 /*!40101 SET character_set_client = @saved_cs_client */;
544 -- Table structure for table `tenants_images`
547 DROP TABLE IF EXISTS `tenants_images`
;
548 /*!40101 SET @saved_cs_client = @@character_set_client */;
549 /*!40101 SET character_set_client = utf8 */;
550 CREATE TABLE `tenants_images`
(
551 `
id`
int(11) NOT NULL AUTO_INCREMENT
,
552 `image_id`
varchar(36) NOT NULL,
553 `tenant_id`
varchar(36) NOT NULL,
555 KEY `FK_tenants_images_tenants`
(`tenant_id`
),
556 KEY `FK_tenants_images_images`
(`image_id`
),
557 CONSTRAINT `FK_tenants_images_images`
FOREIGN KEY (`image_id`
) REFERENCES `images`
(`uuid`
),
558 CONSTRAINT `FK_tenants_images_tenants`
FOREIGN KEY (`tenant_id`
) REFERENCES `tenants`
(`uuid`
) ON DELETE CASCADE ON UPDATE CASCADE
559 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
;
560 /*!40101 SET character_set_client = @saved_cs_client */;
563 -- Table structure for table `uuids`
566 DROP TABLE IF EXISTS `uuids`
;
567 /*!40101 SET @saved_cs_client = @@character_set_client */;
568 /*!40101 SET character_set_client = utf8 */;
569 CREATE TABLE `uuids`
(
570 `uuid`
varchar(36) NOT NULL,
571 `root_uuid`
varchar(36) DEFAULT NULL COMMENT 'Some related UUIDs can be grouped by this field, so that they can be deleted at once',
572 `created_at`
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
573 `used_at`
varchar(64) DEFAULT NULL COMMENT 'Table that uses this UUID',
575 ) ENGINE
=InnoDB
DEFAULT CHARSET
=utf8
COMMENT='Used to avoid UUID repetitions';
576 /*!40101 SET character_set_client = @saved_cs_client */;
579 -- Dumping routines for database '{{vim_db}}'
581 /*!50003 DROP PROCEDURE IF EXISTS `GetAllAvailablePorts` */;
582 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
583 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
584 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
585 /*!50003 SET character_set_client = utf8 */ ;
586 /*!50003 SET character_set_results = utf8 */ ;
587 /*!50003 SET collation_connection = utf8_general_ci */ ;
588 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
589 /*!50003 SET sql_mode = '' */ ;
591 CREATE PROCEDURE `GetAllAvailablePorts`
(IN Numa
INT)
593 COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
595 SELECT port_id
, pci
, Mbps
, Mbps
- Mbps_consumed
as Mbps_free
, totalSRIOV
- coalesce(usedSRIOV
,0) as availableSRIOV
, switch_port
, mac
598 SELECT id as port_id
, Mbps
, pci
, switch_port
, mac
600 WHERE numa_id
= Numa
AND id=root_id
AND status
= 'ok' AND instance_id
IS NULL
604 SELECT root_id
, sum(Mbps_used
) as Mbps_consumed
, COUNT(id)-1 as totalSRIOV
606 WHERE numa_id
= Numa
AND status
= 'ok'
609 ON A.port_id
= B.root_id
612 SELECT root_id
, COUNT(id) as usedSRIOV
614 WHERE numa_id
= Numa
AND status
= 'ok' AND instance_id
IS NOT NULL
617 ON A.port_id
= C.root_id
618 ORDER BY Mbps_free
, availableSRIOV
, pci
;
621 /*!50003 SET sql_mode = @saved_sql_mode */ ;
622 /*!50003 SET character_set_client = @saved_cs_client */ ;
623 /*!50003 SET character_set_results = @saved_cs_results */ ;
624 /*!50003 SET collation_connection = @saved_col_connection */ ;
625 /*!50003 DROP PROCEDURE IF EXISTS `GetAvailablePorts` */;
626 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
627 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
628 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
629 /*!50003 SET character_set_client = utf8 */ ;
630 /*!50003 SET character_set_results = utf8 */ ;
631 /*!50003 SET collation_connection = utf8_general_ci */ ;
632 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
633 /*!50003 SET sql_mode = '' */ ;
635 CREATE PROCEDURE `GetAvailablePorts`
(IN `Numa`
INT)
639 SELECT port_id
, pci
, Mbps
, Mbps
- Mbps_consumed
as Mbps_free
, totalSRIOV
- coalesce(usedSRIOV
,0) as availableSRIOV
, switch_port
, mac
642 SELECT id as port_id
, Mbps
, pci
, switch_port
, mac
644 WHERE numa_id
= Numa
AND id=root_id
AND status
= 'ok' AND switch_port
is not Null AND instance_id
IS NULL
648 SELECT root_id
, sum(Mbps_used
) as Mbps_consumed
, COUNT(id)-1 as totalSRIOV
650 WHERE numa_id
= Numa
AND status
= 'ok'
653 ON A.port_id
= B.root_id
656 SELECT root_id
, COUNT(id) as usedSRIOV
658 WHERE numa_id
= Numa
AND status
= 'ok' AND instance_id
IS NOT NULL AND switch_port
is not Null
661 ON A.port_id
= C.root_id
663 ORDER BY Mbps_free
, availableSRIOV
, pci
667 /*!50003 SET sql_mode = @saved_sql_mode */ ;
668 /*!50003 SET character_set_client = @saved_cs_client */ ;
669 /*!50003 SET character_set_results = @saved_cs_results */ ;
670 /*!50003 SET collation_connection = @saved_col_connection */ ;
671 /*!50003 DROP PROCEDURE IF EXISTS `GetHostByMemCpu` */;
672 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
673 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
674 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
675 /*!50003 SET character_set_client = utf8 */ ;
676 /*!50003 SET character_set_results = utf8 */ ;
677 /*!50003 SET collation_connection = utf8_general_ci */ ;
678 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
679 /*!50003 SET sql_mode = '' */ ;
681 CREATE PROCEDURE `GetHostByMemCpu`
(IN `Needed_mem`
INT, IN `Needed_cpus`
INT)
683 COMMENT 'Obtain those hosts with the available free Memory(Non HugePages) and CPUS (Non isolated)'
689 SELECT sum(ram
) as used_ram
, sum(vcpus
) as used_cpus
, host_id
692 ) as U
ON U.host_id
= H.uuid
693 WHERE Needed_mem
<=H.RAM
-coalesce(U.used_ram
,0) AND Needed_cpus
<=H.cpus
-coalesce(U.used_cpus
,0) AND H.admin_state_up
= 'true'
694 ORDER BY RAM
-coalesce(U.used_ram
,0), cpus
-coalesce(U.used_cpus
,0)
699 /*!50003 SET sql_mode = @saved_sql_mode */ ;
700 /*!50003 SET character_set_client = @saved_cs_client */ ;
701 /*!50003 SET character_set_results = @saved_cs_results */ ;
702 /*!50003 SET collation_connection = @saved_col_connection */ ;
703 /*!50003 DROP PROCEDURE IF EXISTS `GetIfaces` */;
704 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
705 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
706 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
707 /*!50003 SET character_set_client = utf8 */ ;
708 /*!50003 SET character_set_results = utf8 */ ;
709 /*!50003 SET collation_connection = utf8_general_ci */ ;
710 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
711 /*!50003 SET sql_mode = '' */ ;
713 CREATE PROCEDURE `GetIfaces`
()
715 COMMENT 'Used for the http get ports'
718 SELECT *, 'ACTIVE' as status
,'true' as admin_state_up
FROM
721 SELECT ifa.uuid
as id, ifa.
name as name, instance_id
as device_id
, net_id
, tenant_id
722 FROM instance_ifaces
AS ifa
JOIN instances
AS i
on ifa.instance_id
=i.uuid
726 SELECT iface_uuid
as id, ifa.
name as name, instance_id
as device_id
, net_id
,tenant_id
727 FROM resources_port
AS ifa
JOIN instances
AS i
on ifa.instance_id
=i.uuid
728 WHERE iface_uuid
is not NULL
732 SELECT uuid
as id, name, Null as device_id
, net_id
, Null as tenant_id
739 /*!50003 SET sql_mode = @saved_sql_mode */ ;
740 /*!50003 SET character_set_client = @saved_cs_client */ ;
741 /*!50003 SET character_set_results = @saved_cs_results */ ;
742 /*!50003 SET collation_connection = @saved_col_connection */ ;
743 /*!50003 DROP PROCEDURE IF EXISTS `GetNextAutoIncrement` */;
744 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
745 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
746 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
747 /*!50003 SET character_set_client = utf8 */ ;
748 /*!50003 SET character_set_results = utf8 */ ;
749 /*!50003 SET collation_connection = utf8_general_ci */ ;
750 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
751 /*!50003 SET sql_mode = '' */ ;
753 CREATE PROCEDURE `GetNextAutoIncrement`
()
756 SELECT table_name, AUTO_INCREMENT
757 FROM information_schema.
tables
758 WHERE table_name = 'resources_port'
759 AND table_schema
= DATABASE( ) ;
762 /*!50003 SET sql_mode = @saved_sql_mode */ ;
763 /*!50003 SET character_set_client = @saved_cs_client */ ;
764 /*!50003 SET character_set_results = @saved_cs_results */ ;
765 /*!50003 SET collation_connection = @saved_col_connection */ ;
766 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByCore` */;
767 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
768 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
769 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
770 /*!50003 SET character_set_client = utf8 */ ;
771 /*!50003 SET character_set_results = utf8 */ ;
772 /*!50003 SET collation_connection = utf8_general_ci */ ;
773 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
774 /*!50003 SET sql_mode = '' */ ;
776 CREATE PROCEDURE `GetNumaByCore`
(IN `Needed_cores`
SMALLINT)
778 COMMENT 'Obtain Numas with a concrete number of available cores, with bot'
781 SELECT numa_id
, host_id
, numa_socket
, freecores
FROM
783 SELECT numa_id
, COUNT(core_id
) as freecores
FROM
785 SELECT numa_id
, core_id
, COUNT(thread_id
) AS freethreads
787 WHERE instance_id
IS NULL AND status
= 'ok'
788 GROUP BY numa_id
, core_id
790 WHERE FREECORES_TABLE.freethreads
= 2
793 INNER JOIN numas
ON numas.
id = NBCORES_TABLE.numa_id
794 INNER JOIN hosts
ON numas.host_id
= hosts.uuid
796 WHERE NBCORES_TABLE.freecores
>= Needed_cores
AND numas.status
= 'ok' AND numas.admin_state_up
= 'true' AND hosts.admin_state_up
= 'true'
797 ORDER BY NBCORES_TABLE.freecores
802 /*!50003 SET sql_mode = @saved_sql_mode */ ;
803 /*!50003 SET character_set_client = @saved_cs_client */ ;
804 /*!50003 SET character_set_results = @saved_cs_results */ ;
805 /*!50003 SET collation_connection = @saved_col_connection */ ;
806 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByMemory` */;
807 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
808 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
809 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
810 /*!50003 SET character_set_client = utf8 */ ;
811 /*!50003 SET character_set_results = utf8 */ ;
812 /*!50003 SET collation_connection = utf8_general_ci */ ;
813 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
814 /*!50003 SET sql_mode = '' */ ;
816 CREATE PROCEDURE `GetNumaByMemory`
(IN `Needed_mem`
SMALLINT)
819 COMMENT 'Obtain numas with a free quantity of memory, passed by parameter'
822 ( SELECT numas.
id as numa_id
, numas.host_id
, numas.numa_socket
, numas.hugepages
, numas.hugepages
- sum(coalesce(resources_mem.consumed
,0)) AS freemem
824 LEFT JOIN resources_mem
ON numas.
id = resources_mem.numa_id
825 JOIN hosts
ON numas.host_id
= hosts.uuid
826 WHERE numas.status
= 'ok' AND numas.admin_state_up
= 'true' AND hosts.admin_state_up
= 'true'
830 WHERE COMBINED.freemem
>= Needed_mem
831 ORDER BY COMBINED.freemem
835 /*!50003 SET sql_mode = @saved_sql_mode */ ;
836 /*!50003 SET character_set_client = @saved_cs_client */ ;
837 /*!50003 SET character_set_results = @saved_cs_results */ ;
838 /*!50003 SET collation_connection = @saved_col_connection */ ;
839 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByPort` */;
840 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
841 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
842 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
843 /*!50003 SET character_set_client = utf8 */ ;
844 /*!50003 SET character_set_results = utf8 */ ;
845 /*!50003 SET collation_connection = utf8_general_ci */ ;
846 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
847 /*!50003 SET sql_mode = '' */ ;
849 CREATE PROCEDURE `GetNumaByPort`
(IN `Needed_speed`
SMALLINT, IN `Needed_ports`
SMALLINT)
851 COMMENT 'Busca Numas con N puertos fisicos LIBRES de X velocidad'
854 SELECT numa_id
, COUNT(id) AS number_ports
857 SELECT root_id
AS id, status
, numa_id
, Mbps
, SUM(Mbps_used
) AS Consumed
861 WHERE status
= 'ok' AND switch_port
is not Null AND Consumed
= 0 AND Mbps
>= Needed_speed
863 HAVING number_ports
>= Needed_ports
868 /*!50003 SET sql_mode = @saved_sql_mode */ ;
869 /*!50003 SET character_set_client = @saved_cs_client */ ;
870 /*!50003 SET character_set_results = @saved_cs_results */ ;
871 /*!50003 SET collation_connection = @saved_col_connection */ ;
872 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByThread` */;
873 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
874 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
875 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
876 /*!50003 SET character_set_client = utf8 */ ;
877 /*!50003 SET character_set_results = utf8 */ ;
878 /*!50003 SET collation_connection = utf8_general_ci */ ;
879 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
880 /*!50003 SET sql_mode = '' */ ;
882 CREATE PROCEDURE `GetNumaByThread`
(IN `Needed_threads`
SMALLINT)
886 SELECT numa_id
, host_id
, numa_socket
, freethreads
889 SELECT numa_id
, COUNT(thread_id
) AS freethreads
891 WHERE instance_id
IS NULL AND status
= 'ok'
894 INNER JOIN numas
ON numas.
id = NBCORES_TABLE.numa_id
895 INNER JOIN hosts
ON numas.host_id
= hosts.uuid
897 WHERE NBCORES_TABLE.freethreads
>= Needed_threads
AND numas.status
= 'ok' AND numas.admin_state_up
= 'true' AND hosts.admin_state_up
= 'true'
898 ORDER BY NBCORES_TABLE.freethreads
903 /*!50003 SET sql_mode = @saved_sql_mode */ ;
904 /*!50003 SET character_set_client = @saved_cs_client */ ;
905 /*!50003 SET character_set_results = @saved_cs_results */ ;
906 /*!50003 SET collation_connection = @saved_col_connection */ ;
907 /*!50003 DROP PROCEDURE IF EXISTS `GetPortsFromNuma` */;
908 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
909 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
910 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
911 /*!50003 SET character_set_client = utf8 */ ;
912 /*!50003 SET character_set_results = utf8 */ ;
913 /*!50003 SET collation_connection = utf8_general_ci */ ;
914 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
915 /*!50003 SET sql_mode = '' */ ;
917 CREATE PROCEDURE `GetPortsFromNuma`
(IN `Numa`
INT)
921 SELECT Mbps
, pci
, status
, Mbps_consumed
924 SELECT id, Mbps
, pci
, status
926 WHERE numa_id
= Numa
AND id=root_id
AND status
='ok' AND switch_port
is not Null
930 SELECT root_id
, sum(Mbps_used
) as Mbps_consumed
939 /*!50003 SET sql_mode = @saved_sql_mode */ ;
940 /*!50003 SET character_set_client = @saved_cs_client */ ;
941 /*!50003 SET character_set_results = @saved_cs_results */ ;
942 /*!50003 SET collation_connection = @saved_col_connection */ ;
943 /*!50003 DROP PROCEDURE IF EXISTS `UpdateSwitchPort` */;
944 /*!50003 SET @saved_cs_client = @@character_set_client */ ;
945 /*!50003 SET @saved_cs_results = @@character_set_results */ ;
946 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
947 /*!50003 SET character_set_client = utf8 */ ;
948 /*!50003 SET character_set_results = utf8 */ ;
949 /*!50003 SET collation_connection = utf8_general_ci */ ;
950 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;
951 /*!50003 SET sql_mode = '' */ ;
953 CREATE PROCEDURE `UpdateSwitchPort`
()
956 COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
960 RIGHT JOIN resources_port
as RP
on ports.uuid
=RP.port_id
961 INNER JOIN resources_port
as RP2
on RP2.
id=RP.root_id
962 INNER JOIN numas
on RP.numa_id
=numas.
id
963 INNER JOIN hosts
on numas.host_id
=hosts.uuid
964 INNER JOIN of_ports_pci_correspondence
as PC
on hosts.ip_name
=PC.ip_name
and RP2.pci
=PC.pci
965 SET ports.switch_port
=null, ports.switch_dpid
=null, RP.switch_port
=null, RP.switch_dpid
=null;
968 RIGHT JOIN resources_port
as RP
on ports.uuid
=RP.port_id
969 INNER JOIN resources_port
as RP2
on RP2.
id=RP.root_id
970 INNER JOIN numas
on RP.numa_id
=numas.
id
971 INNER JOIN hosts
on numas.host_id
=hosts.uuid
972 INNER JOIN of_ports_pci_correspondence
as PC
on hosts.ip_name
=PC.ip_name
and RP2.pci
=PC.pci
973 SET ports.switch_port
=PC.switch_port
, ports.switch_dpid
=PC.switch_dpid
, RP.switch_port
=PC.switch_port
, RP.switch_dpid
=PC.switch_dpid
;
976 /*!50003 SET sql_mode = @saved_sql_mode */ ;
977 /*!50003 SET character_set_client = @saved_cs_client */ ;
978 /*!50003 SET character_set_results = @saved_cs_results */ ;
979 /*!50003 SET collation_connection = @saved_col_connection */ ;
980 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
982 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
983 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
984 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
985 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
986 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
987 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
988 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
990 -- Dump completed on 2018-12-10 14:58:11
993 -- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64)
995 -- Host: localhost Database: {{vim_db}}
996 -- ------------------------------------------------------
997 -- Server version 5.7.24
999 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
1000 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
1001 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
1002 /*!40101 SET NAMES utf8 */;
1003 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
1004 /*!40103 SET TIME_ZONE='+00:00' */;
1005 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
1006 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
1007 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
1008 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
1011 -- Dumping data for table `schema_version`
1014 LOCK TABLES `schema_version`
WRITE;
1015 /*!40000 ALTER TABLE `schema_version` DISABLE KEYS */;
1016 INSERT INTO `schema_version`
VALUES
1017 (0,'0.0','0.0.0','Database in init process','2015-05-05'),
1018 (1,'0.1','0.2.00','insert schema_version; alter nets with last_error column','2015-05-05'),
1019 (2,'0.2','0.2.03','update Procedure UpdateSwitchPort','2015-05-06'),
1020 (3,'0.3','0.2.5','New Procedure GetAllAvailablePorts','2015-07-09'),
1021 (4,'0.4','0.3.1','Remove unique index VLAN at resources_port','2015-09-04'),
1022 (5,'0.5','0.4.1','Add ip_address to ports','2015-09-04'),
1023 (6,'0.6','0.4.2','Enlarging name at database','2016-02-01'),
1024 (7,'0.7','0.4.4','Add bind_net to net table','2016-02-12'),
1025 (8,'0.8','0.4.10','add column checksum to images','2016-09-30'),
1026 (9,'0.9','0.5.1','increase length of columns path and name to 255 in table images, and change length of column name to 255 in table flavors','2017-01-10'),
1027 (10,'0.10','0.5.2','change ports type, adding instance:ovs','2017-02-01'),
1028 (11,'0.11','0.5.4','Add gateway_ip colum to nets','2017-02-13'),
1029 (12,'0.12','0.5.5','Add of_controller table','2017-02-17'),
1030 (13,'0.13','0.5.6','Add of_port_mapings table','2017-03-09'),
1031 (14,'0.14','0.5.7','Add switch_mac, ofc_id colum to ports and resources_port tables','2017-03-09'),
1032 (15,'0.15','0.5.8','Add ofc_id colum to of_flows','2017-03-15'),
1033 (16,'0.16','0.5.9','Add last_error and status colum to ofcs','2017-03-17'),
1034 (17,'0.17','0.5.10','Add pci to unique index dpid port/mac at of_port_mappings','2017-04-05'),
1035 (18,'0.18','0.5.13','Add region to nets, change vlan unique index','2017-05-03'),
1036 (19,'0.19','0.5.15','Add keyfile to hosts','2017-05-23'),
1037 (20,'0.20','0.5.17','Add image_size to instance_devices','2017-06-01'),
1038 (21,'0.21','0.5.18','Add routes, links and dns to inets','2017-06-21'),
1039 (22,'0.22','0.5.21','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-11-14'),
1040 (23,'0.23','0.5.24','Add hypervisor, os_type to instances and add hypervisors to hosts','2018-03-20');
1041 /*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
1043 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1045 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1046 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1047 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1048 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1049 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1050 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1051 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1053 -- Dump completed on 2018-12-10 14:58:11