database_utils/vim_db_structure.sql

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