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.20, for Linux (x86_64)
  23 --
  24 -- Host: localhost    Database: {{vim_db}}
  25 -- ------------------------------------------------------
  26 -- Server version       5.7.20-0ubuntu0.16.04.1
  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   `user` varchar(64) NOT NULL,
 106   `password` varchar(64) DEFAULT NULL,
 107   `keyfile` varchar(255) DEFAULT NULL,
 108   `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
 109   `RAM` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'Host memory in MB not used as hugepages',
 110   `cpus` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Host threads(or cores) not isolated from OS',
 111   PRIMARY KEY (`uuid`),
 112   UNIQUE KEY `ip_name` (`ip_name`)
 113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='hosts information';
 114 /*!40101 SET character_set_client = @saved_cs_client */;
 115
 116 --
 117 -- Table structure for table `images`
 118 --
 119
 120 DROP TABLE IF EXISTS `images`;
 121 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 122 /*!40101 SET character_set_client = utf8 */;
 123 CREATE TABLE `images` (
 124   `uuid` varchar(36) NOT NULL,
 125   `path` varchar(255) NOT NULL,
 126   `name` varchar(255) NOT NULL,
 127   `checksum` varchar(32) DEFAULT NULL,
 128   `description` varchar(255) DEFAULT NULL,
 129   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 130   `modified_at` timestamp NULL DEFAULT NULL,
 131   `public` enum('yes','no') NOT NULL DEFAULT 'no',
 132   `progress` tinyint(3) unsigned NOT NULL DEFAULT '100',
 133   `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
 134   `metadata` varchar(2000) DEFAULT NULL COMMENT 'Metatdata in json text format',
 135   PRIMARY KEY (`uuid`),
 136   UNIQUE KEY `path` (`path`)
 137 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 138 /*!40101 SET character_set_client = @saved_cs_client */;
 139
 140 --
 141 -- Table structure for table `instance_devices`
 142 --
 143
 144 DROP TABLE IF EXISTS `instance_devices`;
 145 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 146 /*!40101 SET character_set_client = utf8 */;
 147 CREATE TABLE `instance_devices` (
 148   `id` int(11) NOT NULL AUTO_INCREMENT,
 149   `type` enum('usb','disk','cdrom','xml') NOT NULL,
 150   `xml` varchar(1000) DEFAULT NULL COMMENT 'libvirt XML format for aditional device',
 151   `instance_id` varchar(36) NOT NULL,
 152   `image_id` varchar(36) DEFAULT NULL COMMENT 'Used in case type is disk',
 153   `vpci` char(12) DEFAULT NULL COMMENT 'format XXXX:XX:XX.X',
 154   `dev` varchar(12) DEFAULT NULL,
 155   `image_size` int(11) DEFAULT NULL,
 156   PRIMARY KEY (`id`),
 157   KEY `FK_instance_devices_instances` (`instance_id`),
 158   KEY `FK_instance_devices_images` (`image_id`),
 159   CONSTRAINT `FK_instance_devices_images` FOREIGN KEY (`image_id`) REFERENCES `tenants_images` (`image_id`),
 160   CONSTRAINT `FK_instance_devices_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE
 161 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 162 /*!40101 SET character_set_client = @saved_cs_client */;
 163
 164 --
 165 -- Table structure for table `instances`
 166 --
 167
 168 DROP TABLE IF EXISTS `instances`;
 169 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 170 /*!40101 SET character_set_client = utf8 */;
 171 CREATE TABLE `instances` (
 172   `uuid` varchar(36) NOT NULL,
 173   `flavor_id` varchar(36) NOT NULL,
 174   `image_id` varchar(36) NOT NULL,
 175   `name` varchar(64) NOT NULL,
 176   `description` varchar(255) DEFAULT NULL,
 177   `last_error` varchar(255) DEFAULT NULL,
 178   `progress` tinyint(3) unsigned NOT NULL DEFAULT '0',
 179   `tenant_id` varchar(36) NOT NULL,
 180   `status` enum('ACTIVE','PAUSED','INACTIVE','CREATING','ERROR','DELETING') NOT NULL DEFAULT 'ACTIVE',
 181   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 182   `modified_at` timestamp NULL DEFAULT NULL,
 183   `host_id` varchar(36) NOT NULL COMMENT 'HOST where is allocated',
 184   `ram` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT 'used non-hugepages memory in MB',
 185   `vcpus` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'used non-isolated CPUs',
 186   PRIMARY KEY (`uuid`),
 187   KEY `FK_instances_tenants` (`tenant_id`),
 188   KEY `FK_instances_flavors` (`flavor_id`),
 189   KEY `FK_instances_images` (`image_id`),
 190   KEY `FK_instances_hosts` (`host_id`),
 191   CONSTRAINT `FK_instances_flavors` FOREIGN KEY (`flavor_id`) REFERENCES `tenants_flavors` (`flavor_id`),
 192   CONSTRAINT `FK_instances_hosts` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`uuid`),
 193   CONSTRAINT `FK_instances_images` FOREIGN KEY (`image_id`) REFERENCES `tenants_images` (`image_id`),
 194   CONSTRAINT `FK_instances_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
 195 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='VM instances';
 196 /*!40101 SET character_set_client = @saved_cs_client */;
 197
 198 --
 199 -- Table structure for table `logs`
 200 --
 201
 202 DROP TABLE IF EXISTS `logs`;
 203 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 204 /*!40101 SET character_set_client = utf8 */;
 205 CREATE TABLE `logs` (
 206   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 207   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 208   `tenant_id` varchar(36) DEFAULT NULL,
 209   `related` enum('hosts','images','flavors','tenants','ports','instances','nets') DEFAULT NULL,
 210   `uuid` varchar(36) DEFAULT NULL COMMENT 'uuid of host, image, etc that log relates to',
 211   `level` enum('panic','error','info','debug','verbose') NOT NULL,
 212   `description` varchar(200) NOT NULL,
 213   PRIMARY KEY (`id`)
 214 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 215 /*!40101 SET character_set_client = @saved_cs_client */;
 216
 217 --
 218 -- Table structure for table `nets`
 219 --
 220
 221 DROP TABLE IF EXISTS `nets`;
 222 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 223 /*!40101 SET character_set_client = utf8 */;
 224 CREATE TABLE `nets` (
 225   `uuid` varchar(36) NOT NULL,
 226   `tenant_id` varchar(36) DEFAULT NULL,
 227   `type` enum('ptp','data','bridge_data','bridge_man') NOT NULL DEFAULT 'bridge_man',
 228   `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
 229   `last_error` varchar(255) DEFAULT NULL,
 230   `name` varchar(255) NOT NULL,
 231   `shared` enum('true','false') NOT NULL DEFAULT 'false',
 232   `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
 233   `region` varchar(64) DEFAULT NULL,
 234   `vlan` smallint(6) DEFAULT NULL,
 235   `provider` varchar(36) DEFAULT NULL,
 236   `bind_net` varchar(36) DEFAULT NULL COMMENT 'To connect with other net',
 237   `bind_type` varchar(36) DEFAULT NULL COMMENT 'VLAN:<tag> to insert/remove',
 238   `cidr` varchar(64) DEFAULT NULL,
 239   `enable_dhcp` enum('true','false') NOT NULL DEFAULT 'false',
 240   `dhcp_first_ip` varchar(64) DEFAULT NULL,
 241   `dhcp_last_ip` varchar(64) DEFAULT NULL,
 242   `gateway_ip` varchar(64) DEFAULT NULL,
 243   `dns` varchar(255) DEFAULT NULL,
 244   `links` text,
 245   `routes` text,
 246   PRIMARY KEY (`uuid`),
 247   UNIQUE KEY `physical` (`provider`),
 248   UNIQUE KEY `region_vlan` (`region`,`vlan`),
 249   KEY `FK_nets_tenants` (`tenant_id`),
 250   CONSTRAINT `FK_nets_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
 251 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 252 /*!40101 SET character_set_client = @saved_cs_client */;
 253
 254 --
 255 -- Table structure for table `numas`
 256 --
 257
 258 DROP TABLE IF EXISTS `numas`;
 259 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 260 /*!40101 SET character_set_client = utf8 */;
 261 CREATE TABLE `numas` (
 262   `id` int(11) NOT NULL AUTO_INCREMENT,
 263   `host_id` varchar(36) NOT NULL,
 264   `numa_socket` tinyint(3) unsigned NOT NULL DEFAULT '0',
 265   `hugepages` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Available memory for guest in GB',
 266   `status` enum('ok','error','notused') NOT NULL DEFAULT 'ok',
 267   `memory` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'total memry in GB, not all available for guests',
 268   `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
 269   PRIMARY KEY (`id`),
 270   KEY `FK_numas_hosts` (`host_id`),
 271   CONSTRAINT `FK_numas_hosts` FOREIGN KEY (`host_id`) REFERENCES `hosts` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 272 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 273 /*!40101 SET character_set_client = @saved_cs_client */;
 274
 275 --
 276 -- Table structure for table `of_flows`
 277 --
 278
 279 DROP TABLE IF EXISTS `of_flows`;
 280 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 281 /*!40101 SET character_set_client = utf8 */;
 282 CREATE TABLE `of_flows` (
 283   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 284   `name` varchar(64) NOT NULL,
 285   `net_id` varchar(36) DEFAULT NULL,
 286   `ofc_id` varchar(36) DEFAULT NULL,
 287   `priority` int(10) unsigned DEFAULT NULL,
 288   `vlan_id` smallint(5) unsigned DEFAULT NULL,
 289   `ingress_port` varchar(10) DEFAULT NULL,
 290   `src_mac` varchar(50) DEFAULT NULL,
 291   `dst_mac` varchar(50) DEFAULT NULL,
 292   `actions` varchar(255) DEFAULT NULL,
 293   PRIMARY KEY (`id`),
 294   UNIQUE KEY `name` (`name`),
 295   KEY `FK_of_flows_nets` (`net_id`),
 296   KEY `FK_of_flows_ofcs` (`ofc_id`),
 297   CONSTRAINT `FK_of_flows_nets` FOREIGN KEY (`net_id`) REFERENCES `nets` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE,
 298   CONSTRAINT `FK_of_flows_ofcs` FOREIGN KEY (`ofc_id`) REFERENCES `ofcs` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
 299 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 300 /*!40101 SET character_set_client = @saved_cs_client */;
 301
 302 --
 303 -- Table structure for table `of_port_mappings`
 304 --
 305
 306 DROP TABLE IF EXISTS `of_port_mappings`;
 307 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 308 /*!40101 SET character_set_client = utf8 */;
 309 CREATE TABLE `of_port_mappings` (
 310   `uuid` varchar(36) NOT NULL,
 311   `ofc_id` varchar(36) DEFAULT NULL,
 312   `region` varchar(64) DEFAULT NULL,
 313   `compute_node` varchar(64) DEFAULT NULL,
 314   `pci` varchar(50) DEFAULT NULL,
 315   `switch_dpid` varchar(64) DEFAULT NULL,
 316   `switch_port` varchar(64) DEFAULT NULL,
 317   `switch_mac` char(18) DEFAULT NULL,
 318   UNIQUE KEY `region_compute_node_pci` (`region`,`compute_node`,`pci`),
 319   UNIQUE KEY `switch_dpid_switch_port` (`switch_dpid`,`switch_port`,`pci`),
 320   UNIQUE KEY `switch_dpid_switch_mac` (`switch_dpid`,`switch_mac`,`pci`),
 321   KEY `FK_of_port_mappings_ofcs` (`ofc_id`),
 322   CONSTRAINT `FK_of_port_mappings_ofcs` FOREIGN KEY (`ofc_id`) REFERENCES `ofcs` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 323 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 324 /*!40101 SET character_set_client = @saved_cs_client */;
 325
 326 --
 327 -- Table structure for table `of_ports_pci_correspondence`
 328 --
 329
 330 DROP TABLE IF EXISTS `of_ports_pci_correspondence`;
 331 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 332 /*!40101 SET character_set_client = utf8 */;
 333 CREATE TABLE `of_ports_pci_correspondence` (
 334   `id` int(10) NOT NULL AUTO_INCREMENT,
 335   `ip_name` varchar(64) DEFAULT NULL,
 336   `pci` varchar(50) DEFAULT NULL,
 337   `switch_port` varchar(64) DEFAULT NULL,
 338   `switch_dpid` varchar(64) DEFAULT NULL,
 339   PRIMARY KEY (`id`)
 340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 341 /*!40101 SET character_set_client = @saved_cs_client */;
 342
 343 --
 344 -- Table structure for table `ofcs`
 345 --
 346
 347 DROP TABLE IF EXISTS `ofcs`;
 348 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 349 /*!40101 SET character_set_client = utf8 */;
 350 CREATE TABLE `ofcs` (
 351   `uuid` varchar(36) NOT NULL,
 352   `name` varchar(255) NOT NULL,
 353   `dpid` varchar(64) NOT NULL,
 354   `ip` varchar(64) NOT NULL,
 355   `port` int(5) NOT NULL,
 356   `type` varchar(64) NOT NULL,
 357   `version` varchar(12) DEFAULT NULL,
 358   `user` varchar(64) DEFAULT NULL,
 359   `password` varchar(64) DEFAULT NULL,
 360   `last_error` varchar(255) DEFAULT NULL,
 361   `status` enum('ACTIVE','INACTIVE','ERROR') DEFAULT 'ACTIVE',
 362   `nets_with_same_vlan` enum('true','false') NOT NULL DEFAULT 'false',
 363   PRIMARY KEY (`uuid`)
 364 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 365 /*!40101 SET character_set_client = @saved_cs_client */;
 366
 367 --
 368 -- Table structure for table `ports`
 369 --
 370
 371 DROP TABLE IF EXISTS `ports`;
 372 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 373 /*!40101 SET character_set_client = utf8 */;
 374 CREATE TABLE `ports` (
 375   `uuid` varchar(36) NOT NULL,
 376   `name` varchar(64) NOT NULL,
 377   `instance_id` varchar(36) DEFAULT NULL,
 378   `tenant_id` varchar(36) DEFAULT NULL,
 379   `net_id` varchar(36) DEFAULT NULL,
 380   `vpci` char(12) DEFAULT NULL,
 381   `Mbps` mediumint(8) unsigned DEFAULT NULL COMMENT 'In Mbits/s',
 382   `admin_state_up` enum('true','false') NOT NULL DEFAULT 'true',
 383   `status` enum('ACTIVE','DOWN','BUILD','ERROR') NOT NULL DEFAULT 'ACTIVE',
 384   `type` enum('instance:bridge','instance:data','external','instance:ovs','controller:ovs') NOT NULL DEFAULT 'instance:bridge',
 385   `vlan` smallint(5) DEFAULT NULL COMMENT 'vlan of this SRIOV, or external port',
 386   `switch_port` varchar(64) DEFAULT NULL,
 387   `switch_mac` varchar(18) DEFAULT NULL,
 388   `switch_dpid` varchar(64) DEFAULT NULL,
 389   `ofc_id` varchar(36) DEFAULT NULL,
 390   `mac` char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
 391   `ip_address` varchar(64) DEFAULT NULL,
 392   `model` varchar(12) DEFAULT NULL COMMENT 'driver model for bridge ifaces; PF,VF,VFnotShared for data ifaces',
 393   PRIMARY KEY (`uuid`),
 394   UNIQUE KEY `mac` (`mac`),
 395   KEY `FK_instance_ifaces_instances` (`instance_id`),
 396   KEY `FK_instance_ifaces_nets` (`net_id`),
 397   KEY `FK_ports_tenants` (`tenant_id`),
 398   KEY `FK_port_ofc_id` (`ofc_id`),
 399   CONSTRAINT `FK_instance_ifaces_nets` FOREIGN KEY (`net_id`) REFERENCES `nets` (`uuid`),
 400   CONSTRAINT `FK_port_ofc_id` FOREIGN KEY (`ofc_id`) REFERENCES `ofcs` (`uuid`),
 401   CONSTRAINT `FK_ports_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE,
 402   CONSTRAINT `FK_ports_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`)
 403 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Bridge interfaces used by instances';
 404 /*!40101 SET character_set_client = @saved_cs_client */;
 405
 406 --
 407 -- Table structure for table `resources_core`
 408 --
 409
 410 DROP TABLE IF EXISTS `resources_core`;
 411 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 412 /*!40101 SET character_set_client = utf8 */;
 413 CREATE TABLE `resources_core` (
 414   `id` int(11) NOT NULL AUTO_INCREMENT,
 415   `numa_id` int(11) DEFAULT NULL,
 416   `core_id` smallint(5) unsigned NOT NULL,
 417   `thread_id` smallint(5) unsigned NOT NULL,
 418   `instance_id` varchar(36) DEFAULT NULL COMMENT 'instance that consume this resource',
 419   `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',
 420   `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',
 421   `paired` enum('Y','N') NOT NULL DEFAULT 'N',
 422   PRIMARY KEY (`id`),
 423   KEY `FK_resources_core_instances` (`instance_id`),
 424   KEY `FK_resources_core_numas` (`numa_id`),
 425   CONSTRAINT `FK_resources_core_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`),
 426   CONSTRAINT `FK_resources_core_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 427 ) 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';
 428 /*!40101 SET character_set_client = @saved_cs_client */;
 429
 430 --
 431 -- Table structure for table `resources_mem`
 432 --
 433
 434 DROP TABLE IF EXISTS `resources_mem`;
 435 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 436 /*!40101 SET character_set_client = utf8 */;
 437 CREATE TABLE `resources_mem` (
 438   `id` int(11) NOT NULL AUTO_INCREMENT,
 439   `numa_id` int(11) NOT NULL DEFAULT '0',
 440   `instance_id` varchar(36) DEFAULT '0' COMMENT 'NULL is allowed in order to allow some memory not used',
 441   `consumed` int(3) unsigned NOT NULL DEFAULT '0' COMMENT 'In GB',
 442   PRIMARY KEY (`id`),
 443   KEY `FK_resources_mem_instances` (`instance_id`),
 444   KEY `FK_resources_mem_numas` (`numa_id`),
 445   CONSTRAINT `FK_resources_mem_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`) ON DELETE CASCADE,
 446   CONSTRAINT `FK_resources_mem_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON UPDATE CASCADE
 447 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Include the hugepages memory used by one instance (VM) in one host NUMA.';
 448 /*!40101 SET character_set_client = @saved_cs_client */;
 449
 450 --
 451 -- Table structure for table `resources_port`
 452 --
 453
 454 DROP TABLE IF EXISTS `resources_port`;
 455 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 456 /*!40101 SET character_set_client = utf8 */;
 457 CREATE TABLE `resources_port` (
 458   `id` int(11) NOT NULL AUTO_INCREMENT,
 459   `numa_id` int(11) NOT NULL DEFAULT '0',
 460   `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)',
 461   `port_id` varchar(36) DEFAULT NULL COMMENT 'When resource is used, this point to the ports table',
 462   `source_name` varchar(64) DEFAULT NULL,
 463   `pci` char(12) NOT NULL DEFAULT '0' COMMENT 'Host physical pci bus. Format XXXX:XX:XX.X',
 464   `Mbps` smallint(5) unsigned DEFAULT '10' COMMENT 'Nominal Port speed ',
 465   `root_id` int(11) DEFAULT NULL COMMENT 'NULL for physical port entries; =id for SRIOV port',
 466   `status` enum('ok','error','notused') NOT NULL DEFAULT 'ok',
 467   `Mbps_used` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Speed bandwidth used when asigned',
 468   `switch_port` varchar(64) DEFAULT NULL,
 469   `switch_mac` varchar(18) DEFAULT NULL,
 470   `switch_dpid` varchar(64) DEFAULT NULL,
 471   `ofc_id` varchar(36) DEFAULT NULL,
 472   `mac` char(18) DEFAULT NULL COMMENT 'mac address format XX:XX:XX:XX:XX:XX',
 473   PRIMARY KEY (`id`),
 474   UNIQUE KEY `mac` (`mac`),
 475   UNIQUE KEY `port_id` (`port_id`),
 476   KEY `FK_resources_port_numas` (`numa_id`),
 477   KEY `FK_resources_port_instances` (`instance_id`),
 478   KEY `FK_resource_ofc_id` (`ofc_id`),
 479   CONSTRAINT `FK_resource_ofc_id` FOREIGN KEY (`ofc_id`) REFERENCES `ofcs` (`uuid`),
 480   CONSTRAINT `FK_resources_port_instances` FOREIGN KEY (`instance_id`) REFERENCES `instances` (`uuid`),
 481   CONSTRAINT `FK_resources_port_numas` FOREIGN KEY (`numa_id`) REFERENCES `numas` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 482   CONSTRAINT `FK_resources_port_ports` FOREIGN KEY (`port_id`) REFERENCES `ports` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 483 ) 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)';
 484 /*!40101 SET character_set_client = @saved_cs_client */;
 485
 486 --
 487 -- Table structure for table `schema_version`
 488 --
 489
 490 DROP TABLE IF EXISTS `schema_version`;
 491 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 492 /*!40101 SET character_set_client = utf8 */;
 493 CREATE TABLE `schema_version` (
 494   `version_int` int(11) NOT NULL COMMENT 'version as a number. Must not contain gaps',
 495   `version` varchar(20) NOT NULL COMMENT 'version as a text',
 496   `openvim_ver` varchar(20) NOT NULL COMMENT 'openvim version',
 497   `comments` varchar(2000) DEFAULT NULL COMMENT 'changes to database',
 498   `date` date DEFAULT NULL,
 499   PRIMARY KEY (`version_int`)
 500 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='database schema control version';
 501 /*!40101 SET character_set_client = @saved_cs_client */;
 502
 503 --
 504 -- Table structure for table `tenants`
 505 --
 506
 507 DROP TABLE IF EXISTS `tenants`;
 508 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 509 /*!40101 SET character_set_client = utf8 */;
 510 CREATE TABLE `tenants` (
 511   `uuid` varchar(36) NOT NULL,
 512   `name` varchar(255) NOT NULL,
 513   `description` varchar(255) DEFAULT NULL,
 514   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 515   `enabled` enum('true','false') NOT NULL DEFAULT 'true',
 516   PRIMARY KEY (`uuid`),
 517   UNIQUE KEY `name` (`name`)
 518 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tenants information';
 519 /*!40101 SET character_set_client = @saved_cs_client */;
 520
 521 --
 522 -- Table structure for table `tenants_flavors`
 523 --
 524
 525 DROP TABLE IF EXISTS `tenants_flavors`;
 526 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 527 /*!40101 SET character_set_client = utf8 */;
 528 CREATE TABLE `tenants_flavors` (
 529   `id` int(11) NOT NULL AUTO_INCREMENT,
 530   `flavor_id` varchar(36) NOT NULL,
 531   `tenant_id` varchar(36) NOT NULL,
 532   PRIMARY KEY (`id`),
 533   KEY `FK__tenants` (`tenant_id`),
 534   KEY `FK__flavors` (`flavor_id`),
 535   CONSTRAINT `FK__flavors` FOREIGN KEY (`flavor_id`) REFERENCES `flavors` (`uuid`),
 536   CONSTRAINT `FK__tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 537 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 538 /*!40101 SET character_set_client = @saved_cs_client */;
 539
 540 --
 541 -- Table structure for table `tenants_images`
 542 --
 543
 544 DROP TABLE IF EXISTS `tenants_images`;
 545 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 546 /*!40101 SET character_set_client = utf8 */;
 547 CREATE TABLE `tenants_images` (
 548   `id` int(11) NOT NULL AUTO_INCREMENT,
 549   `image_id` varchar(36) NOT NULL,
 550   `tenant_id` varchar(36) NOT NULL,
 551   PRIMARY KEY (`id`),
 552   KEY `FK_tenants_images_tenants` (`tenant_id`),
 553   KEY `FK_tenants_images_images` (`image_id`),
 554   CONSTRAINT `FK_tenants_images_images` FOREIGN KEY (`image_id`) REFERENCES `images` (`uuid`),
 555   CONSTRAINT `FK_tenants_images_tenants` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`uuid`) ON DELETE CASCADE ON UPDATE CASCADE
 556 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 557 /*!40101 SET character_set_client = @saved_cs_client */;
 558
 559 --
 560 -- Table structure for table `uuids`
 561 --
 562
 563 DROP TABLE IF EXISTS `uuids`;
 564 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 565 /*!40101 SET character_set_client = utf8 */;
 566 CREATE TABLE `uuids` (
 567   `uuid` varchar(36) NOT NULL,
 568   `root_uuid` varchar(36) DEFAULT NULL COMMENT 'Some related UUIDs can be grouped by this field, so that they can be deleted at once',
 569   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 570   `used_at` varchar(64) DEFAULT NULL COMMENT 'Table that uses this UUID',
 571   PRIMARY KEY (`uuid`)
 572 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used to avoid UUID repetitions';
 573 /*!40101 SET character_set_client = @saved_cs_client */;
 574
 575 --
 576 -- Dumping routines for database '{{vim_db}}'
 577 --
 578 /*!50003 DROP PROCEDURE IF EXISTS `GetAllAvailablePorts` */;
 579 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 580 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 581 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 582 /*!50003 SET character_set_client  = utf8 */ ;
 583 /*!50003 SET character_set_results = utf8 */ ;
 584 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 585 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 586 /*!50003 SET sql_mode              = '' */ ;
 587 DELIMITER ;;
 588 CREATE PROCEDURE `GetAllAvailablePorts`(IN Numa INT)
 589     SQL SECURITY INVOKER
 590     COMMENT 'Obtain all -including those not connected to switch port- ports available for a numa'
 591 BEGIN
 592         SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 593         FROM
 594         (
 595            SELECT id as port_id, Mbps, pci, switch_port, mac
 596            FROM resources_port
 597                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND instance_id IS NULL
 598         ) as A
 599         INNER JOIN
 600         (
 601            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 602                 FROM resources_port
 603                 WHERE numa_id = Numa AND status = 'ok'
 604                 GROUP BY root_id
 605         ) as B
 606         ON A.port_id = B.root_id
 607         LEFT JOIN
 608         (
 609            SELECT root_id,  COUNT(id) as usedSRIOV
 610                 FROM resources_port
 611                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL
 612                 GROUP BY root_id
 613         ) as C
 614         ON A.port_id = C.root_id
 615         ORDER BY Mbps_free, availableSRIOV, pci;
 616     END ;;
 617 DELIMITER ;
 618 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 619 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 620 /*!50003 SET character_set_results = @saved_cs_results */ ;
 621 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 622 /*!50003 DROP PROCEDURE IF EXISTS `GetAvailablePorts` */;
 623 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 624 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 625 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 626 /*!50003 SET character_set_client  = utf8 */ ;
 627 /*!50003 SET character_set_results = utf8 */ ;
 628 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 629 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 630 /*!50003 SET sql_mode              = '' */ ;
 631 DELIMITER ;;
 632 CREATE PROCEDURE `GetAvailablePorts`(IN `Numa` INT)
 633     DETERMINISTIC
 634     SQL SECURITY INVOKER
 635 BEGIN
 636 SELECT port_id, pci, Mbps, Mbps - Mbps_consumed as Mbps_free, totalSRIOV - coalesce(usedSRIOV,0) as availableSRIOV, switch_port, mac
 637 FROM
 638         (
 639            SELECT id as port_id, Mbps, pci, switch_port, mac
 640            FROM resources_port
 641                 WHERE numa_id = Numa AND id=root_id AND status = 'ok' AND switch_port is not Null AND instance_id IS NULL
 642         ) as A
 643         INNER JOIN
 644         (
 645            SELECT root_id, sum(Mbps_used) as Mbps_consumed, COUNT(id)-1 as totalSRIOV
 646                 FROM resources_port
 647                 WHERE numa_id = Numa AND status = 'ok'
 648                 GROUP BY root_id
 649         ) as B
 650         ON A.port_id = B.root_id
 651         LEFT JOIN
 652         (
 653            SELECT root_id,  COUNT(id) as usedSRIOV
 654                 FROM resources_port
 655                 WHERE numa_id = Numa AND status = 'ok' AND instance_id IS NOT NULL AND switch_port is not Null
 656                 GROUP BY root_id
 657         ) as C
 658         ON A.port_id = C.root_id
 659
 660 ORDER BY Mbps_free, availableSRIOV, pci
 661 ;
 662 END ;;
 663 DELIMITER ;
 664 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 665 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 666 /*!50003 SET character_set_results = @saved_cs_results */ ;
 667 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 668 /*!50003 DROP PROCEDURE IF EXISTS `GetHostByMemCpu` */;
 669 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 670 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 671 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 672 /*!50003 SET character_set_client  = utf8 */ ;
 673 /*!50003 SET character_set_results = utf8 */ ;
 674 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 675 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 676 /*!50003 SET sql_mode              = '' */ ;
 677 DELIMITER ;;
 678 CREATE PROCEDURE `GetHostByMemCpu`(IN `Needed_mem` INT, IN `Needed_cpus` INT)
 679     SQL SECURITY INVOKER
 680     COMMENT 'Obtain those hosts with the available free Memory(Non HugePages) and CPUS (Non isolated)'
 681 BEGIN
 682
 683 SELECT *
 684 FROM hosts as H
 685 LEFT JOIN (
 686         SELECT sum(ram) as used_ram, sum(vcpus) as used_cpus, host_id
 687         FROM instances
 688         GROUP BY host_id
 689 ) as U ON U.host_id = H.uuid
 690 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'
 691 ORDER BY RAM-coalesce(U.used_ram,0), cpus-coalesce(U.used_cpus,0)
 692
 693 ;
 694 END ;;
 695 DELIMITER ;
 696 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 697 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 698 /*!50003 SET character_set_results = @saved_cs_results */ ;
 699 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 700 /*!50003 DROP PROCEDURE IF EXISTS `GetIfaces` */;
 701 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 702 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 703 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 704 /*!50003 SET character_set_client  = utf8 */ ;
 705 /*!50003 SET character_set_results = utf8 */ ;
 706 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 707 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 708 /*!50003 SET sql_mode              = '' */ ;
 709 DELIMITER ;;
 710 CREATE PROCEDURE `GetIfaces`()
 711     SQL SECURITY INVOKER
 712     COMMENT 'Used for the http get ports'
 713 BEGIN
 714
 715 SELECT *, 'ACTIVE' as status,'true' as admin_state_up FROM
 716 (
 717         (
 718                 SELECT ifa.uuid as id, ifa.name as name, instance_id as device_id, net_id, tenant_id
 719                 FROM instance_ifaces AS ifa JOIN instances AS i on ifa.instance_id=i.uuid
 720         )
 721         UNION
 722         (
 723                 SELECT iface_uuid as id, ifa.name as name, instance_id as device_id, net_id,tenant_id
 724                 FROM resources_port  AS ifa JOIN instances AS i on ifa.instance_id=i.uuid
 725                 WHERE iface_uuid is not NULL
 726         )
 727         UNION
 728         (
 729                 SELECT uuid as id, name, Null as device_id, net_id, Null as tenant_id
 730                 FROM external_ports
 731         )
 732 ) as B
 733 ;
 734 END ;;
 735 DELIMITER ;
 736 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 737 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 738 /*!50003 SET character_set_results = @saved_cs_results */ ;
 739 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 740 /*!50003 DROP PROCEDURE IF EXISTS `GetNextAutoIncrement` */;
 741 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 742 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 743 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 744 /*!50003 SET character_set_client  = utf8 */ ;
 745 /*!50003 SET character_set_results = utf8 */ ;
 746 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 747 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 748 /*!50003 SET sql_mode              = '' */ ;
 749 DELIMITER ;;
 750 CREATE PROCEDURE `GetNextAutoIncrement`()
 751     SQL SECURITY INVOKER
 752 BEGIN
 753 SELECT table_name, AUTO_INCREMENT
 754 FROM information_schema.tables
 755 WHERE table_name = 'resources_port'
 756 AND table_schema = DATABASE( ) ;
 757 END ;;
 758 DELIMITER ;
 759 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 760 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 761 /*!50003 SET character_set_results = @saved_cs_results */ ;
 762 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 763 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByCore` */;
 764 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 765 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 766 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 767 /*!50003 SET character_set_client  = utf8 */ ;
 768 /*!50003 SET character_set_results = utf8 */ ;
 769 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 770 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 771 /*!50003 SET sql_mode              = '' */ ;
 772 DELIMITER ;;
 773 CREATE PROCEDURE `GetNumaByCore`(IN `Needed_cores` SMALLINT)
 774     SQL SECURITY INVOKER
 775     COMMENT 'Obtain Numas with a concrete number of available cores, with bot'
 776 BEGIN
 777
 778 SELECT numa_id, host_id, numa_socket, freecores FROM
 779 (
 780     SELECT numa_id, COUNT(core_id) as freecores FROM
 781     (
 782         SELECT numa_id, core_id, COUNT(thread_id) AS freethreads
 783                   FROM resources_core
 784                   WHERE instance_id IS NULL AND status = 'ok'
 785                   GROUP BY numa_id, core_id
 786     ) AS FREECORES_TABLE
 787     WHERE FREECORES_TABLE.freethreads = 2
 788     GROUP BY numa_id
 789 ) AS NBCORES_TABLE
 790 INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
 791 INNER JOIN hosts ON numas.host_id = hosts.uuid
 792
 793 WHERE NBCORES_TABLE.freecores >= Needed_cores AND numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
 794 ORDER BY NBCORES_TABLE.freecores
 795 ;
 796
 797 END ;;
 798 DELIMITER ;
 799 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 800 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 801 /*!50003 SET character_set_results = @saved_cs_results */ ;
 802 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 803 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByMemory` */;
 804 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 805 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 806 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 807 /*!50003 SET character_set_client  = utf8 */ ;
 808 /*!50003 SET character_set_results = utf8 */ ;
 809 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 810 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 811 /*!50003 SET sql_mode              = '' */ ;
 812 DELIMITER ;;
 813 CREATE PROCEDURE `GetNumaByMemory`(IN `Needed_mem` SMALLINT)
 814     DETERMINISTIC
 815     SQL SECURITY INVOKER
 816     COMMENT 'Obtain numas with a free quantity of memory, passed by parameter'
 817 BEGIN
 818 SELECT * FROM
 819 (   SELECT numas.id as numa_id, numas.host_id, numas.numa_socket, numas.hugepages, numas.hugepages - sum(coalesce(resources_mem.consumed,0)) AS freemem
 820     FROM numas
 821          LEFT JOIN resources_mem ON numas.id = resources_mem.numa_id
 822     JOIN hosts ON numas.host_id = hosts.uuid
 823     WHERE numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
 824     GROUP BY numas.id
 825 ) AS COMBINED
 826
 827 WHERE COMBINED.freemem >= Needed_mem
 828 ORDER BY COMBINED.freemem
 829 ;
 830 END ;;
 831 DELIMITER ;
 832 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 833 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 834 /*!50003 SET character_set_results = @saved_cs_results */ ;
 835 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 836 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByPort` */;
 837 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 838 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 839 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 840 /*!50003 SET character_set_client  = utf8 */ ;
 841 /*!50003 SET character_set_results = utf8 */ ;
 842 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 843 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 844 /*!50003 SET sql_mode              = '' */ ;
 845 DELIMITER ;;
 846 CREATE PROCEDURE `GetNumaByPort`(IN `Needed_speed` SMALLINT, IN `Needed_ports` SMALLINT)
 847     SQL SECURITY INVOKER
 848     COMMENT 'Busca Numas con N puertos fisicos LIBRES de X velocidad'
 849 BEGIN
 850
 851 SELECT numa_id, COUNT(id) AS number_ports
 852 FROM
 853 (
 854         SELECT root_id AS id, status, numa_id, Mbps, SUM(Mbps_used) AS Consumed
 855         FROM resources_port
 856         GROUP BY root_id
 857 ) AS P
 858 WHERE status = 'ok' AND switch_port is not Null AND Consumed = 0 AND Mbps >= Needed_speed
 859 GROUP BY numa_id
 860 HAVING number_ports  >= Needed_ports
 861 ;
 862
 863 END ;;
 864 DELIMITER ;
 865 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 866 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 867 /*!50003 SET character_set_results = @saved_cs_results */ ;
 868 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 869 /*!50003 DROP PROCEDURE IF EXISTS `GetNumaByThread` */;
 870 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 871 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 872 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 873 /*!50003 SET character_set_client  = utf8 */ ;
 874 /*!50003 SET character_set_results = utf8 */ ;
 875 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 876 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 877 /*!50003 SET sql_mode              = '' */ ;
 878 DELIMITER ;;
 879 CREATE PROCEDURE `GetNumaByThread`(IN `Needed_threads` SMALLINT)
 880     SQL SECURITY INVOKER
 881 BEGIN
 882
 883 SELECT numa_id, host_id, numa_socket, freethreads
 884 FROM
 885 (
 886         SELECT numa_id, COUNT(thread_id) AS freethreads
 887         FROM resources_core
 888         WHERE instance_id IS NULL AND status = 'ok'
 889         GROUP BY numa_id
 890 ) AS NBCORES_TABLE
 891 INNER JOIN numas ON numas.id = NBCORES_TABLE.numa_id
 892 INNER JOIN hosts ON numas.host_id = hosts.uuid
 893
 894 WHERE NBCORES_TABLE.freethreads >= Needed_threads AND numas.status = 'ok' AND numas.admin_state_up = 'true' AND hosts.admin_state_up = 'true'
 895 ORDER BY NBCORES_TABLE.freethreads
 896 ;
 897
 898 END ;;
 899 DELIMITER ;
 900 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 901 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 902 /*!50003 SET character_set_results = @saved_cs_results */ ;
 903 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 904 /*!50003 DROP PROCEDURE IF EXISTS `GetPortsFromNuma` */;
 905 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 906 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 907 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 908 /*!50003 SET character_set_client  = utf8 */ ;
 909 /*!50003 SET character_set_results = utf8 */ ;
 910 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 911 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 912 /*!50003 SET sql_mode              = '' */ ;
 913 DELIMITER ;;
 914 CREATE PROCEDURE `GetPortsFromNuma`(IN `Numa` INT)
 915     NO SQL
 916     SQL SECURITY INVOKER
 917 BEGIN
 918 SELECT Mbps, pci, status, Mbps_consumed
 919 FROM
 920 (
 921    SELECT id, Mbps, pci, status
 922    FROM resources_port
 923         WHERE numa_id = Numa AND id=root_id AND status='ok' AND switch_port is not Null
 924 ) as A
 925 INNER JOIN
 926 (
 927    SELECT root_id, sum(Mbps_used) as Mbps_consumed
 928         FROM resources_port
 929         WHERE numa_id = Numa
 930         GROUP BY root_id
 931 ) as B
 932 ON A.id = B.root_id
 933 ;
 934 END ;;
 935 DELIMITER ;
 936 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 937 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 938 /*!50003 SET character_set_results = @saved_cs_results */ ;
 939 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 940 /*!50003 DROP PROCEDURE IF EXISTS `UpdateSwitchPort` */;
 941 /*!50003 SET @saved_cs_client      = @@character_set_client */ ;
 942 /*!50003 SET @saved_cs_results     = @@character_set_results */ ;
 943 /*!50003 SET @saved_col_connection = @@collation_connection */ ;
 944 /*!50003 SET character_set_client  = utf8 */ ;
 945 /*!50003 SET character_set_results = utf8 */ ;
 946 /*!50003 SET collation_connection  = utf8_general_ci */ ;
 947 /*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
 948 /*!50003 SET sql_mode              = '' */ ;
 949 DELIMITER ;;
 950 CREATE PROCEDURE `UpdateSwitchPort`()
 951     MODIFIES SQL DATA
 952     SQL SECURITY INVOKER
 953     COMMENT 'Load the openflow switch ports from of_ports_pci_correspondece into resoureces_port and ports'
 954 BEGIN
 955
 956         UPDATE ports
 957         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 958         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 959         INNER JOIN numas on RP.numa_id=numas.id
 960         INNER JOIN hosts on numas.host_id=hosts.uuid
 961         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 962         SET ports.switch_port=null, ports.switch_dpid=null, RP.switch_port=null, RP.switch_dpid=null;
 963
 964         UPDATE ports
 965         RIGHT JOIN resources_port as RP on ports.uuid=RP.port_id
 966         INNER JOIN resources_port as RP2 on RP2.id=RP.root_id
 967         INNER JOIN numas on RP.numa_id=numas.id
 968         INNER JOIN hosts on numas.host_id=hosts.uuid
 969         INNER JOIN of_ports_pci_correspondence as PC on hosts.ip_name=PC.ip_name and RP2.pci=PC.pci
 970         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;
 971     END ;;
 972 DELIMITER ;
 973 /*!50003 SET sql_mode              = @saved_sql_mode */ ;
 974 /*!50003 SET character_set_client  = @saved_cs_client */ ;
 975 /*!50003 SET character_set_results = @saved_cs_results */ ;
 976 /*!50003 SET collation_connection  = @saved_col_connection */ ;
 977 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 978
 979 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
 980 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
 981 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 982 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
 983 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
 984 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 985 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 986
 987 -- Dump completed on 2017-11-23 10:32:25
 988
 989
 990
 991
 992
 993 -- MySQL dump 10.13  Distrib 5.7.20, for Linux (x86_64)
 994 --
 995 -- Host: localhost    Database: {{vim_db}}
 996 -- ------------------------------------------------------
 997 -- Server version       5.7.20-0ubuntu0.16.04.1
 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 (1,'0.1','0.2.00','insert schema_version; alter nets with last_error column','2015-05-05'),(2,'0.2','0.2.03','update Procedure UpdateSwitchPort','2015-05-06'),(3,'0.3','0.2.5','New Procedure GetAllAvailablePorts','2015-07-09'),(4,'0.4','0.3.1','Remove unique index VLAN at resources_port','2015-09-04'),(5,'0.5','0.4.1','Add ip_address to ports','2015-09-04'),(6,'0.6','0.4.2','Enlarging name at database','2016-02-01'),(7,'0.7','0.4.4','Add bind_net to net table','2016-02-12'),(8,'0.8','0.4.10','add column checksum to images','2016-09-30'),(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'),(10,'0.10','0.5.2','change ports type, adding instance:ovs','2017-02-01'),(11,'0.11','0.5.4','Add gateway_ip colum to nets','2017-02-13'),(12,'0.12','0.5.5','Add of_controller table','2017-02-17'),(13,'0.13','0.5.6','Add of_port_mapings table','2017-03-09'),(14,'0.14','0.5.7','Add switch_mac, ofc_id colum to ports and resources_port tables','2017-03-09'),(15,'0.15','0.5.8','Add ofc_id colum to of_flows','2017-03-15'),(16,'0.16','0.5.9','Add last_error and status colum to ofcs','2017-03-17'),(17,'0.17','0.5.10','Add pci to unique index dpid port/mac at of_port_mappings','2017-04-05'),(18,'0.18','0.5.13','Add region to nets, change vlan unique index','2017-05-03'),(19,'0.19','0.5.15','Add keyfile to hosts','2017-05-23'),(20,'0.20','0.5.17','Add image_size to instance_devices','2017-06-01'),(21,'0.21','0.5.18','Add routes, links and dns to inets','2017-06-21'),(22,'0.22','0.5.21','Changed type of ram in flavors from SMALLINT to MEDIUMINT','2017-11-14');
1017 /*!40000 ALTER TABLE `schema_version` ENABLE KEYS */;
1018 UNLOCK TABLES;
1019 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
1020
1021 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
1022 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
1023 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
1024 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
1025 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
1026 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
1027 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
1028
1029 -- Dump completed on 2017-11-23 10:32:25