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