database_utils/vim_db_structure.sql

   1 /**
   2 * Copyright 2017 Telefónica Investigación y Desarrollo, S.A.U.
   3 * This file is part of openvim
   4 * All Rights Reserved.
   5 *
   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
   9 *
  10 *         http://www.apache.org/licenses/LICENSE-2.0
  11 *
  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
  16 * under the License.
  17 *
  18 * For those usages not covered by the Apache License, Version 2.0 please
  19 * contact with: nfvlabs@tid.es
  20 **/
  21
  22 -- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
  23 --
  24 -- Host: localhost    Database: {{vim_db}}
  25 -- ------------------------------------------------------
  26 -- Server version       5.7.24
  27
  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 */;
  38
  39 --
  40 -- Current Database: `{{vim_db}}`
  41 --
  42
  43 /*!40000 DROP DATABASE IF EXISTS `{{vim_db}}`*/;
  44
  45 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `{{vim_db}}` /*!40100 DEFAULT CHARACTER SET utf8 */;
  46
  47 USE `{{vim_db}}`;
  48
  49 --
  50 -- Table structure for table `flavors`
  51 --
  52
  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,
  66   PRIMARY KEY (`uuid`)
  67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='flavors with extra vnfcd info';
  68 /*!40101 SET character_set_client = @saved_cs_client */;
  69
  70 --
  71 -- Table structure for table `host_ranking`
  72 --
  73
  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,
  84   PRIMARY KEY (`id`),
  85   UNIQUE KEY `family_manufacturer_version` (`family`,`manufacturer`,`version`)
  86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  87 /*!40101 SET character_set_client = @saved_cs_client */;
  88
  89 --
  90 -- Table structure for table `hosts`
  91 --
  92
  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 */;
 116
 117 --
 118 -- Table structure for table `images`
 119 --
 120
 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 */;
 140
 141 --
 142 -- Table structure for table `instance_devices`
 143 --
 144
 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,
 157   PRIMARY KEY (`id`),
 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 */;
 164
 165 --
 166 -- Table structure for table `instances`
 167 --
 168
 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 */;
 200
 201 --
 202 -- Table structure for table `logs`
 203 --
 204
 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,
 216   PRIMARY KEY (`id`)
 217 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 218 /*!40101 SET character_set_client = @saved_cs_client */;
 219
 220 --
 221 -- Table structure for table `nets`
 222 --
 223
 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,
 247   `links` text,
 248   `routes` text,
 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 */;
 256
 257 --
 258 -- Table structure for table `numas`
 259 --
 260
 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',
 272   PRIMARY KEY (`id`),
 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 */;
 277
 278 --
 279 -- Table structure for table `of_flows`
 280 --
 281
 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,
 296   PRIMARY KEY (`id`),
 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 */;
 304
 305 --
 306 -- Table structure for table `of_port_mappings`
 307 --
 308
 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 */;
 328
 329 --
 330 -- Table structure for table `of_ports_pci_correspondence`
 331 --
 332
 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,
 342   PRIMARY KEY (`id`)
 343 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 344 /*!40101 SET character_set_client = @saved_cs_client */;
 345
 346 --
 347 -- Table structure for table `ofcs`
 348 --
 349
 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',
 366   PRIMARY KEY (`uuid`)
 367 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 368 /*!40101 SET character_set_client = @saved_cs_client */;
 369
 370 --
 371 -- Table structure for table `ports`
 372 --
 373
 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 */;
 408
 409 --
 410 -- Table structure for table `resources_core`
 411 --
 412
 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',
 425   PRIMARY KEY (`id`),
 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 */;
 432
 433 --
 434 -- Table structure for table `resources_mem`
 435 --
 436
 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',
 445   PRIMARY KEY (`id`),
 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 */;
 452
 453 --
 454 -- Table structure for table `resources_port`
 455 --
 456
 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',
 476   PRIMARY KEY (`id`),
 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 */;
 488
 489 --
 490 -- Table structure for table `schema_version`
 491 --
 492
 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 */;
 505
 506 --
 507 -- Table structure for table `tenants`
 508 --
 509
 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 */;
 523
 524 --
 525 -- Table structure for table `tenants_flavors`
 526 --
 527
 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,
 535   PRIMARY KEY (`id`),
 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 */;
 542
 543 --
 544 -- Table structure for table `tenants_images`
 545 --
 546
 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,
 554   PRIMARY KEY (`id`),
 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 */;
 561
 562 --
 563 -- Table structure for table `uuids`
 564 --
 565
 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',
 574   PRIMARY KEY (`uuid`)
 575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to avoid UUID repetitions';
 576 /*!40101 SET character_set_client = @saved_cs_client */;
 577
 578 --
 579 -- Dumping routines for database '{{vim_db}}'
 580 --
 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              = '' */ ;
 590 DELIMITER ;;
 591 CREATE PROCEDURE `GetAllAvailablePorts`(IN Numa INT)
 592     SQL SECURITY INVOKER
 593     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 594 BEGIN
 595         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 596         FROM
 597         (
 598            SELECT id as port_id, Mbps, pci, switch_port, mac
 599            FROM resources_port
 600                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 601         ) as A
 602         INNER JOIN
 603         (
 604            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 605                 FROM resources_port
 606                 WHERE numa_id = Numa AND status = 'ok'
 607                 GROUP BY root_id
 608         ) as B
 609         ON A.port_id = B.root_id
 610         LEFT JOIN
 611         (
 612            SELECT root_id,  COUNT(id) as usedSRIOV
 613                 FROM resources_port
 614                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 615                 GROUP BY root_id
 616         ) as C
 617         ON A.port_id = C.root_id
 618         ORDER BY Mbps_free, availableSRIOV, pci;
 619     END ;;
 620 DELIMITER ;
 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              = '' */ ;
 634 DELIMITER ;;
 635 CREATE PROCEDURE `GetAvailablePorts`(IN `Numa` INT)
 636     DETERMINISTIC
 637     SQL SECURITY INVOKER
 638 BEGIN
 639 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 640 FROM
 641         (
 642            SELECT id as port_id, Mbps, pci, switch_port, mac
 643            FROM resources_port
 644                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND switch_port is not Null AND instance_id IS NULL
 645         ) as A
 646         INNER JOIN
 647         (
 648            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 649                 FROM resources_port
 650                 WHERE numa_id = Numa AND status = 'ok'
 651                 GROUP BY root_id
 652         ) as B
 653         ON A.port_id = B.root_id
 654         LEFT JOIN
 655         (
 656            SELECT root_id,  COUNT(id) as usedSRIOV
 657                 FROM resources_port
 658                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL AND switch_port is not Null
 659                 GROUP BY root_id
 660         ) as C
 661         ON A.port_id = C.root_id
 662
 663 ORDER BY Mbps_free, availableSRIOV, pci
 664 ;
 665 END ;;
 666 DELIMITER ;
 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              = '' */ ;
 680 DELIMITER ;;
 681 CREATE PROCEDURE `GetHostByMemCpu`(IN `Needed_mem` INT, IN `Needed_cpus` INT)
 682     SQL SECURITY INVOKER
 683     COMMENT 'Obtain those hosts with the available free Memory(Non HugePages) and CPUS (Non isolated)'
 684 BEGIN
 685
 686 SELECT *
 687 FROM hosts as H
 688 LEFT JOIN (
 689         SELECT sum(ram) as used_ram, sum(vcpus) as used_cpus, host_id
 690         FROM instances
 691         GROUP BY 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)
 695
 696 ;
 697 END ;;
 698 DELIMITER ;
 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              = '' */ ;
 712 DELIMITER ;;
 713 CREATE PROCEDURE `GetIfaces`()
 714     SQL SECURITY INVOKER
 715     COMMENT 'Used for the http get ports'
 716 BEGIN
 717
 718 SELECT *, 'ACTIVE' as status,'true' as admin_state_up FROM
 719 (
 720         (
 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
 723         )
 724         UNION
 725         (
 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
 729         )
 730         UNION
 731         (
 732                 SELECT uuid as id, name, Null as device_id, net_id, Null as tenant_id
 733                 FROM external_ports
 734         )
 735 ) as B
 736 ;
 737 END ;;
 738 DELIMITER ;
 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              = '' */ ;
 752 DELIMITER ;;
 753 CREATE PROCEDURE `GetNextAutoIncrement`()
 754     SQL SECURITY INVOKER
 755 BEGIN
 756 SELECT table_name, AUTO_INCREMENT
 757 FROM information_schema.tables
 758 WHERE table_name = 'resources_port'
 759 AND table_schema = DATABASE( ) ;
 760 END ;;
 761 DELIMITER ;
 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              = '' */ ;
 775 DELIMITER ;;
 776 CREATE PROCEDURE `GetNumaByCore`(IN `Needed_cores` SMALLINT)
 777     SQL SECURITY INVOKER
 778     COMMENT 'Obtain Numas with a concrete number of available cores, with bot'
 779 BEGIN
 780
 781 SELECT numa_id, host_id, numa_socket, freecores FROM
 782 (
 783     SELECT numa_id, COUNT(core_id) as freecores FROM
 784     (
 785         SELECT numa_id, core_id, COUNT(thread_id) AS freethreads
 786                   FROM resources_core
 787                   WHERE instance_id IS NULL AND status = 'ok'
 788                   GROUP BY numa_id, core_id
 789     ) AS FREECORES_TABLE
 790     WHERE FREECORES_TABLE.freethreads = 2
 791     GROUP BY numa_id
 792 ) AS NBCORES_TABLE
 793 INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
 794 INNER JOIN hosts ON numas.host_id = hosts.uuid
 795
 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
 798 ;
 799
 800 END ;;
 801 DELIMITER ;
 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              = '' */ ;
 815 DELIMITER ;;
 816 CREATE PROCEDURE `GetNumaByMemory`(IN `Needed_mem` SMALLINT)
 817     DETERMINISTIC
 818     SQL SECURITY INVOKER
 819     COMMENT 'Obtain numas with a free quantity of memory, passed by parameter'
 820 BEGIN
 821 SELECT * FROM
 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
 823     FROM numas
 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'
 827     GROUP BY numas.id
 828 ) AS COMBINED
 829
 830 WHERE COMBINED.freemem >= Needed_mem
 831 ORDER BY COMBINED.freemem
 832 ;
 833 END ;;
 834 DELIMITER ;
 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              = '' */ ;
 848 DELIMITER ;;
 849 CREATE PROCEDURE `GetNumaByPort`(IN `Needed_speed` SMALLINT, IN `Needed_ports` SMALLINT)
 850     SQL SECURITY INVOKER
 851     COMMENT 'Busca Numas con N puertos fisicos LIBRES de X velocidad'
 852 BEGIN
 853
 854 SELECT numa_id, COUNT(id) AS number_ports
 855 FROM
 856 (
 857         SELECT root_id AS id, status, numa_id, Mbps, SUM(Mbps_used) AS Consumed
 858         FROM resources_port
 859         GROUP BY root_id
 860 ) AS P
 861 WHERE status = 'ok' AND switch_port is not Null AND Consumed = 0 AND Mbps >= Needed_speed
 862 GROUP BY numa_id
 863 HAVING number_ports  >= Needed_ports
 864 ;
 865
 866 END ;;
 867 DELIMITER ;
 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              = '' */ ;
 881 DELIMITER ;;
 882 CREATE PROCEDURE `GetNumaByThread`(IN `Needed_threads` SMALLINT)
 883     SQL SECURITY INVOKER
 884 BEGIN
 885
 886 SELECT numa_id, host_id, numa_socket, freethreads
 887 FROM
 888 (
 889         SELECT numa_id, COUNT(thread_id) AS freethreads
 890         FROM resources_core
 891         WHERE instance_id IS NULL AND status = 'ok'
 892         GROUP BY numa_id
 893 ) AS NBCORES_TABLE
 894 INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
 895 INNER JOIN hosts ON numas.host_id = hosts.uuid
 896
 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
 899 ;
 900
 901 END ;;
 902 DELIMITER ;
 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              = '' */ ;
 916 DELIMITER ;;
 917 CREATE PROCEDURE `GetPortsFromNuma`(IN `Numa` INT)
 918     NO SQL
 919     SQL SECURITY INVOKER
 920 BEGIN
 921 SELECT Mbps, pci, status, Mbps_consumed
 922 FROM
 923 (
 924    SELECT id, Mbps, pci, status
 925    FROM resources_port
 926         WHERE numa_id = Numa AND id=root_id AND status='ok' AND switch_port is not Null
 927 ) as A
 928 INNER JOIN
 929 (
 930    SELECT root_id, sum(Mbps_used) as Mbps_consumed
 931         FROM resources_port
 932         WHERE numa_id = Numa
 933         GROUP BY root_id
 934 ) as B
 935 ON A.id = B.root_id
 936 ;
 937 END ;;
 938 DELIMITER ;
 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              = '' */ ;
 952 DELIMITER ;;
 953 CREATE PROCEDURE `UpdateSwitchPort`()
 954     MODIFIES SQL DATA
 955     SQL SECURITY INVOKER
 956     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 957 BEGIN
 958
 959         UPDATE 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;
 966
 967         UPDATE ports
 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;
 974     END ;;
 975 DELIMITER ;
 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 */;
 981
 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 */;
 989
 990 -- Dump completed on 2018-12-10 14:58:11
 991
 992
 993 -- MySQL dump 10.13  Distrib 5.7.24, for Linux (x86_64)
 994 --
 995 -- Host: localhost    Database: {{vim_db}}
 996 -- ------------------------------------------------------
 997 -- Server version       5.7.24
 998
 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 */;
1009
1010 --
1011 -- Dumping data for table `schema_version`
1012 --
1013
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 */;
1042 UNLOCK TABLES;
1043 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1044
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 */;
1052
1053 -- Dump completed on 2018-12-10 14:58:11
1054