osm_openvim/vim_db.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 ##
   5 # Copyright 2015 Telefonica Investigacion y Desarrollo, S.A.U.
   6 # This file is part of openvim
   7 # All Rights Reserved.
   8 #
   9 # Licensed under the Apache License, Version 2.0 (the "License"); you may
  10 # not use this file except in compliance with the License. You may obtain
  11 # a copy of the License at
  12 #
  13 #         http://www.apache.org/licenses/LICENSE-2.0
  14 #
  15 # Unless required by applicable law or agreed to in writing, software
  16 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
  17 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  18 # License for the specific language governing permissions and limitations
  19 # under the License.
  20 #
  21 # For those usages not covered by the Apache License, Version 2.0 please
  22 # contact with: nfvlabs@tid.es
  23 ##
  24
  25 """
  26 This module interact with the openvim database,
  27 It implements general table management and transactional writes, that is, or all is changed or nothing.
  28 It is threading safe using a Lock
  29 """
  30
  31 import MySQLdb as mdb
  32 import uuid as myUuid
  33 import auxiliary_functions as af
  34 import json
  35 import logging
  36 from netaddr import IPNetwork, IPAddress
  37 from threading import Lock
  38
  39 __author__ = "Alfonso Tierno"
  40 __date__ = "$10-jul-2014 12:07:15$"
  41
  42 HTTP_Bad_Request = 400
  43 HTTP_Unauthorized = 401
  44 HTTP_Not_Found = 404
  45 HTTP_Method_Not_Allowed = 405
  46 HTTP_Request_Timeout = 408
  47 HTTP_Conflict = 409
  48 HTTP_Service_Unavailable = 503
  49 HTTP_Internal_Server_Error = 500
  50
  51
  52 class vim_db():
  53     def __init__(self, vlan_range, logger_name=None, debug=None, lock=None):
  54         """vlan_range must be a tuple (vlan_ini, vlan_end) with available vlan values for networks
  55         every dataplane network contain a unique value, regardless of it is used or not
  56         """
  57         # initialization
  58         self.net_vlan_range = vlan_range
  59         self.vlan_config = {}
  60         self.host = None
  61         self.user = None
  62         self.passwd = None
  63         self.database = None
  64         self.con = None
  65         self.cur = None
  66         self.debug = debug
  67         self.lock = lock or Lock()
  68         if logger_name:
  69             self.logger_name = logger_name
  70         else:
  71             self.logger_name = 'openvim.db'
  72         self.logger = logging.getLogger(self.logger_name)
  73         if debug:
  74             self.logger.setLevel(getattr(logging, debug))
  75
  76     def connect(self, host=None, user=None, passwd=None, database=None):
  77         """Connect to the concrete data base.
  78         The first time a valid host, user, passwd and database must be provided,
  79         Following calls can skip this parameters
  80         """
  81         try:
  82             with self.lock:
  83                 if host:
  84                     self.host = host
  85                     self.con = None
  86                 if user:
  87                     self.user = user
  88                     self.con = None
  89                 if passwd:
  90                     self.passwd = passwd
  91                     self.con = None
  92                 if database:
  93                     self.database = database
  94                     self.con = None
  95                 if self.con:
  96                     # try to connect
  97                     try:
  98                         with self.con:
  99                             self.cur = self.con.cursor()
 100                             cmd = "SELECT version_int,version,openvim_ver FROM schema_version"
 101                             self.logger.debug("method connect: " + cmd)
 102                             self.cur.execute(cmd)
 103                             return 0
 104                     except Exception:
 105                         pass
 106                 self.con = mdb.connect(self.host, self.user, self.passwd, self.database)
 107                 self.logger.debug("connected to DB %s at %s@%s", self.database, self.user, self.host)
 108                 return 0
 109         except mdb.Error as e:
 110             self.logger.error("Cannot connect to DB %s at %s@%s Error %d: %s", self.database, self.user, self.host,
 111                               e.args[0], e.args[1])
 112             return -1
 113
 114     def get_db_version(self):
 115         """ Obtain the database schema version.
 116         Return: (negative, text) if error or version 0.0 where schema_version table is missing
 117                 (version_int, version_text) if ok
 118         """
 119         cmd = "SELECT version_int,version,openvim_ver FROM schema_version"
 120         for retry_ in range(0, 2):
 121             try:
 122                 with self.lock, self.con:
 123                     self.cur = self.con.cursor()
 124                     self.logger.debug(cmd)
 125                     self.cur.execute(cmd)
 126                     rows = self.cur.fetchall()
 127                 highest_version_int = 0
 128                 highest_version = ""
 129                 # print rows
 130                 for row in rows:  # look for the latest version
 131                     if row[0] > highest_version_int:
 132                         highest_version_int, highest_version = row[0:2]
 133                 return highest_version_int, highest_version
 134             except (mdb.Error, AttributeError) as e:
 135                 self.logger.error("get_db_version DB Exception %d: %s. Command %s", e.args[0], e.args[1], cmd)
 136                 r, c = self.format_error(e)
 137                 if r != -HTTP_Request_Timeout or retry_ == 1:
 138                     return r, c
 139
 140     def disconnect(self):
 141         """disconnect from the data base"""
 142         try:
 143             with self.lock:
 144                 if not self.con:
 145                     self.con.close()
 146                     self.con = None
 147         except mdb.Error as e:
 148             self.logger.error("while disconnecting from DB: Error: %s", str(e))
 149             return -1
 150         except AttributeError as e:  # self.con not defined
 151             if e[0][-5:] == "'con'":
 152                 return -1, "Database internal error, no connection."
 153             else:
 154                 raise
 155
 156     def format_error(self, e, func, cmd, command=None, extra=None):
 157         """Creates a text error base on the produced exception
 158             Params:
 159                 e: mdb exception
 160                 func: name of the function that makes the call, for logging purposes
 161                 cmd: database command that produce the exception
 162                 command: if the intention is update or delete
 163                 extra: extra information to add to some commands
 164             Return
 165                 HTTP error in negative, formatted error text
 166         """
 167
 168         self.logger.error("%s DB Exception %s. Command %s", func, str(e), cmd)
 169         if type(e[0]) is str:
 170             if e[0][-5:] == "'con'":
 171                 return -HTTP_Internal_Server_Error, "DB Exception, no connection."
 172             else:
 173                 return -HTTP_Internal_Server_Error, e.args[1]
 174         if e.args[0] == 2006 or e.args[0] == 2013:
 175             # MySQL server has gone away (((or)))    Exception 2013: Lost connection to MySQL server during query
 176             # reconnect
 177             self.connect()
 178             return -HTTP_Request_Timeout, "Database reconnection. Try Again"
 179         fk = e.args[1].find("foreign key constraint fails")
 180         if fk >= 0:
 181             if command == "update":
 182                 return -HTTP_Bad_Request, "tenant_id %s not found." % extra
 183             elif command == "delete":
 184                 return -HTTP_Bad_Request, "Resource is not free. There are %s that prevent its deletion." % extra
 185         de = e.args[1].find("Duplicate entry")
 186         fk = e.args[1].find("for key")
 187         uk = e.args[1].find("Unknown column")
 188         wc = e.args[1].find("in 'where clause'")
 189         fl = e.args[1].find("in 'field list'")
 190         # print de, fk, uk, wc,fl
 191         if de >= 0:
 192             if fk >= 0:  # error 1062
 193                 return -HTTP_Conflict, "Value %s already in use for %s" % (e.args[1][de + 15:fk], e.args[1][fk + 7:])
 194         if uk >= 0:
 195             if wc >= 0:
 196                 return -HTTP_Bad_Request, "Field %s cannot be used for filtering" % e.args[1][uk + 14:wc]
 197             if fl >= 0:
 198                 return -HTTP_Bad_Request, "Field %s does not exist" % e.args[1][uk + 14:wc]
 199         return -HTTP_Internal_Server_Error, "Database internal Error %d: %s" % (e.args[0], e.args[1])
 200
 201     @staticmethod
 202     def __data2db_format(data):
 203         """convert data to database format. If data is None it return the 'Null' text,
 204         otherwise it return the text surrounded by quotes ensuring internal quotes are escaped"""
 205         if data is None:
 206             return 'Null'
 207         out = str(data)
 208         if "'" not in out:
 209             return "'" + out + "'"
 210         elif '"' not in out:
 211             return '"' + out + '"'
 212         else:
 213             return json.dumps(out)
 214
 215     def __get_used_net_vlan(self, region=None):
 216         # get used from database if needed
 217         vlan_region = self.vlan_config[region]
 218         try:
 219             cmd = "SELECT vlan FROM nets WHERE vlan>='{}' and region{} ORDER BY vlan LIMIT 25".format(
 220                 vlan_region["lastused"], "='" + region + "'" if region else " is NULL")
 221             with self.con:
 222                 self.cur = self.con.cursor()
 223                 self.logger.debug(cmd)
 224                 self.cur.execute(cmd)
 225                 vlan_tuple = self.cur.fetchall()
 226                 # convert a tuple of tuples in a list of numbers
 227                 vlan_region["usedlist"] = []
 228                 for k in vlan_tuple:
 229                     vlan_region["usedlist"].append(k[0])
 230         except (mdb.Error, AttributeError) as e:
 231             return self.format_error(e, "get_free_net_vlan", cmd)
 232
 233     def get_free_net_vlan(self, region=None):
 234         """obtain a vlan not used in any net"""
 235         with self.lock:
 236             if region not in self.vlan_config:
 237                 self.vlan_config[region] = {
 238                     "usedlist": None,
 239                     "lastused": self.net_vlan_range[0] - 1
 240                 }
 241             vlan_region = self.vlan_config[region]
 242
 243             while True:
 244                 self.logger.debug("get_free_net_vlan() region[%s]=%s, net_vlan_range:%s-%s", str(region),
 245                                   str(vlan_region), str(self.net_vlan_range[0]), str(self.net_vlan_range[1]))
 246                 vlan_region["lastused"] += 1
 247                 if vlan_region["lastused"] == self.net_vlan_range[1]:
 248                     # start from the begining
 249                     vlan_region["lastused"] = self.net_vlan_range[0]
 250                     vlan_region["usedlist"] = None
 251                 if vlan_region["usedlist"] is None or \
 252                         (len(vlan_region["usedlist"]) == 25 and vlan_region["lastused"] >= vlan_region["usedlist"][-1]):
 253                     self.__get_used_net_vlan(region)
 254                     self.logger.debug("new net_vlan_usedlist %s", str(vlan_region["usedlist"]))
 255                 if vlan_region["lastused"] in vlan_region["usedlist"]:
 256                     continue
 257                 else:
 258                     return vlan_region["lastused"]
 259
 260     def get_table(self, **sql_dict):
 261         """ Obtain rows from a table.
 262         Atribure sql_dir: dictionary with the following key: value
 263             'SELECT': [list of fields to retrieve] (by default all)
 264             'FROM': string of table name (Mandatory)
 265             'WHERE': dict of key:values, translated to key=value AND ... (Optional)
 266             'WHERE_NOT': dict of key:values, translated to key!=value AND ... (Optional)
 267             'WHERE_OR': dict of key:values, translated to key=value OR ... (Optional)
 268             'WHERE_AND_OR: str 'AND' or 'OR'(by default) mark the priority to 'WHERE AND (WHERE_OR)' or (WHERE) OR
 269                 WHERE_OR' (Optional)
 270             'LIMIT': limit of number of rows (Optional)
 271             'DISTINCT': make a select distinct to remove repeated elements
 272         Return: a list with dictionarys at each row
 273         """
 274         # print sql_dict
 275         select_ = "SELECT "
 276         if sql_dict.get("DISTINCT"):
 277             select_ += "DISTINCT "
 278         select_ += ("*" if not sql_dict.get('SELECT') else ",".join(map(str, sql_dict['SELECT'])))
 279         # print 'select_', select_
 280         from_ = "FROM " + str(sql_dict['FROM'])
 281         # print 'from_', from_
 282
 283         where_and = None
 284         where_or = None
 285         w = sql_dict.get('WHERE')
 286         if w:
 287             where_and = " AND ".join(map(lambda x: str(x) + (" is Null" if w[x] is None else "='" + str(w[x]) + "'"),
 288                                          w.keys()))
 289         w = sql_dict.get('WHERE_LIKE')  # Unikernels extension -START-
 290         if w:
 291             where_and_like = " AND ".join(
 292                 map(lambda x: str(x) + (" is Null" if w[x] is None else " LIKE '" + str(w[x]) + "'"), w.keys()))
 293             if where_and:
 294                 where_and += " AND " + where_and_like
 295             else:
 296                 where_and = where_and_like  # Unikernels extension -END-
 297         w = sql_dict.get('WHERE_NOT')
 298         if w:
 299             where_and_not = " AND ".join(
 300                 map(lambda x: str(x) + (" is not Null" if w[x] is None else "!='" + str(w[x]) + "'"), w.keys()))
 301             if where_and:
 302                 where_and += " AND " + where_and_not
 303             else:
 304                 where_and = where_and_not
 305         w = sql_dict.get('WHERE_OR')
 306         if w:
 307             where_or = " OR ".join(map(lambda x: str(x) + (" is Null" if w[x] is None else "='" + str(w[x]) + "'"),
 308                                        w.keys()))
 309
 310         if where_and and where_or:
 311             if sql_dict.get("WHERE_AND_OR") == "AND":
 312                 where_ = "WHERE " + where_and + " AND (" + where_or + ")"
 313             else:
 314                 where_ = "WHERE (" + where_and + ") OR " + where_or
 315         elif where_and and where_or is None:
 316             where_ = "WHERE " + where_and
 317         elif where_and is None and where_or:
 318             where_ = "WHERE " + where_or
 319         else:
 320             where_ = ""
 321         # print 'where_', where_
 322         limit_ = "LIMIT " + str(sql_dict['LIMIT']) if sql_dict.get("LIMIT") else ""
 323         # print 'limit_', limit_
 324         cmd = " ".join((select_, from_, where_, limit_))
 325         for retry_ in range(0, 2):
 326             try:
 327                 with self.lock, self.con:
 328                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 329                     self.logger.debug(cmd)
 330                     self.cur.execute(cmd)
 331                     rows = self.cur.fetchall()
 332                     return self.cur.rowcount, rows
 333             except (mdb.Error, AttributeError) as e:
 334                 r, c = self.format_error(e, "get_table", cmd)
 335                 if r != -HTTP_Request_Timeout or retry_ == 1:
 336                     return r, c
 337
 338     def new_tenant(self, tenant_dict):
 339         """ Add one row into a table.
 340         Attribure
 341             tenant_dict: dictionary with the key: value to insert
 342         It checks presence of uuid and add one automatically otherwise
 343         Return: (result, uuid) where result can be 0 if error, or 1 if ok
 344         """
 345         for retry_ in range(0, 2):
 346             cmd = ""
 347             inserted = -1
 348             try:
 349                 # create uuid if not provided
 350                 if 'uuid' not in tenant_dict:
 351                     uuid = tenant_dict['uuid'] = str(myUuid.uuid1())  # create_uuid
 352                 else:
 353                     uuid = str(tenant_dict['uuid'])
 354                 # obtain tenant_id for logs
 355                 tenant_id = uuid
 356                 with self.lock, self.con:
 357                     self.cur = self.con.cursor()
 358                     # inserting new uuid
 359                     cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','tenants')" % uuid
 360                     self.logger.debug(cmd)
 361                     self.cur.execute(cmd)
 362                     # insert tenant
 363                     cmd = "INSERT INTO tenants (" + \
 364                           ",".join(map(str, tenant_dict.keys())) + ") VALUES(" + \
 365                           ",".join(
 366                               map(lambda x: "Null" if x is None else "'" + str(x) + "'", tenant_dict.values())) + ")"
 367                     self.logger.debug(cmd)
 368                     self.cur.execute(cmd)
 369                     inserted = self.cur.rowcount
 370                     # #inserting new log
 371                     # del tenant_dict['uuid'] # not interested for the log
 372                     # cmd = "INSERT INTO logs (related,level,tenant_id,uuid,description) VALUES
 373                     # ('tenants','debug','%s','%s',\"new tenant %s\")" % (uuid, tenant_id, str(tenant_dict))
 374                     # self.logger.debug(cmd)
 375                     # self.cur.execute(cmd)
 376                     # commit transaction
 377                     self.cur.close()
 378                 if inserted == 0:
 379                     return 0, uuid
 380                 with self.lock, self.con:
 381                     self.cur = self.con.cursor()
 382                     # adding public flavors
 383                     cmd = "INSERT INTO tenants_flavors(flavor_id,tenant_id) SELECT uuid as flavor_id,'" + tenant_id + \
 384                           "' FROM flavors WHERE public = 'yes'"
 385                     self.logger.debug(cmd)
 386                     self.cur.execute(cmd)
 387                     self.logger.debug("attached public flavors: %s", str(self.cur.rowcount))
 388                     # rows = self.cur.fetchall()
 389                     # for row in rows:
 390                     #    cmd = "INSERT INTO tenants_flavors(flavor_id,tenant_id) VALUES('%s','%s')"%(row[0], tenant_id)
 391                     #    self.cur.execute(cmd )
 392                     # adding public images
 393                     cmd = "INSERT INTO tenants_images(image_id,tenant_id) SELECT uuid as image_id,'" + tenant_id + \
 394                           "' FROM images WHERE public = 'yes'"
 395                     self.logger.debug(cmd)
 396                     self.cur.execute(cmd)
 397                     self.logger.debug("attached public images: %s", str(self.cur.rowcount))
 398                     return 1, uuid
 399             except (mdb.Error, AttributeError) as e:
 400                 if inserted == 1:
 401                     self.logger.warning("new_tenant DB Exception %d: %s. Command %s", e.args[0], e.args[1], cmd)
 402                     return 1, uuid
 403                 else:
 404                     r, c = self.format_error(e, "new_tenant", cmd)
 405                     if r != -HTTP_Request_Timeout or retry_ == 1:
 406                         return r, c
 407
 408     def new_row(self, table, INSERT, add_uuid=False, log=False):
 409         """ Add one row into a table.
 410         Atribure
 411             INSERT: dictionary with the key: value to insert
 412             table: table where to insert
 413             add_uuid: if True, it will crated an uuid key entry at INSERT if not provided
 414         It checks presence of uuid and add one automatically otherwise
 415         Return: (result, uuid) where result can be 0 if error, or 1 if ok
 416         """
 417         for retry_ in range(0, 2):
 418             cmd = ""
 419             try:
 420                 if add_uuid:
 421                     # create uuid if not provided
 422                     if 'uuid' not in INSERT:
 423                         uuid = INSERT['uuid'] = str(myUuid.uuid1())  # create_uuid
 424                     else:
 425                         uuid = str(INSERT['uuid'])
 426                 else:
 427                     uuid = None
 428                 with self.lock, self.con:
 429                     self.cur = self.con.cursor()
 430                     if add_uuid:
 431                         # inserting new uuid
 432                         cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','%s')" % (uuid, table)
 433                         self.logger.debug(cmd)
 434                         self.cur.execute(cmd)
 435                     # insertion
 436                     cmd = "INSERT INTO " + table + " (" + \
 437                           ",".join(map(str, INSERT.keys())) + ") VALUES(" + \
 438                           ",".join(map(lambda x: 'Null' if x is None else "'" + str(x) + "'", INSERT.values())) + ")"
 439                     self.logger.debug(cmd)
 440                     self.cur.execute(cmd)
 441                     nb_rows = self.cur.rowcount
 442                     # inserting new log
 443                     # if nb_rows > 0 and log:
 444                     #    if add_uuid: del INSERT['uuid']
 445                     #    #obtain tenant_id for logs
 446                     #    if 'tenant_id' in INSERT:
 447                     #        tenant_id = INSERT['tenant_id']
 448                     #        del INSERT['tenant_id']
 449                     #    elif table == 'tenants':
 450                     #        tenant_id = uuid
 451                     #    else:
 452                     #        tenant_id = None
 453                     #    if uuid is None: uuid_k = uuid_v = ""
 454                     #    else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
 455                     #    if tenant_id is None: tenant_k = tenant_v = ""
 456                     #    else: tenant_k=",tenant_id"; tenant_v=",'" + str(tenant_id) + "'"
 457                     #    cmd = "INSERT INTO logs (related,level%s%s,description) VALUES
 458                     #       ('%s','debug'%s%s,\"new %s %s\")" \
 459                     #        % (uuid_k, tenant_k, table, uuid_v, tenant_v, table[:-1], str(INSERT))
 460                     #    self.logger.debug(cmd)
 461                     #    self.cur.execute(cmd)
 462                     return nb_rows, uuid
 463
 464             except (mdb.Error, AttributeError) as e:
 465                 r, c = self.format_error(e, "new_row", cmd)
 466                 if r != -HTTP_Request_Timeout or retry_ == 1:
 467                     return r, c
 468
 469     @staticmethod
 470     def __remove_quotes(data):
 471         """remove single quotes ' of any string content of data dictionary"""
 472         for k, v in data.items():
 473             if type(v) == str:
 474                 if "'" in v:
 475                     data[k] = data[k].replace("'", "_")
 476
 477     def _update_rows_internal(self, table, UPDATE, WHERE=None):
 478         cmd = "UPDATE " + table + " SET " + \
 479               ",".join(map(lambda x: str(x) + '=' + self.__data2db_format(UPDATE[x]), UPDATE.keys()))
 480         if WHERE:
 481             cmd += " WHERE " + " and ".join(
 482                 map(lambda x: str(x) + (' is Null' if WHERE[x] is None else "='" + str(WHERE[x]) + "'"), WHERE.keys()))
 483         self.logger.debug(cmd)
 484         self.cur.execute(cmd)
 485         nb_rows = self.cur.rowcount
 486         return nb_rows, None
 487
 488     def update_rows(self, table, UPDATE, WHERE=None, log=False):
 489         """ Update one or several rows into a table.
 490         Atributes
 491             UPDATE: dictionary with the key-new_value pairs to change
 492             table: table to be modified
 493             WHERE: dictionary to filter target rows, key-value
 494             log:   if true, a log entry is added at logs table
 495         Return: (result, None) where result indicates the number of updated files
 496         """
 497         for retry_ in range(0, 2):
 498             cmd = ""
 499             try:
 500                 # gettting uuid
 501                 uuid = None
 502                 if WHERE:
 503                     uuid = WHERE.get('uuid')
 504
 505                 with self.lock, self.con:
 506                     self.cur = self.con.cursor()
 507                     cmd = "UPDATE " + table + " SET " + \
 508                           ",".join(map(lambda x: str(x) + '=' + self.__data2db_format(UPDATE[x]), UPDATE.keys()))
 509                     if WHERE:
 510                         cmd += " WHERE " + " and ".join(
 511                             map(lambda x: str(x) + (' is Null' if WHERE[x] is None else "='" + str(WHERE[x]) + "'"),
 512                                 WHERE.keys()))
 513                     self.logger.debug(cmd)
 514                     self.cur.execute(cmd)
 515                     nb_rows = self.cur.rowcount
 516                     # if nb_rows > 0 and log:
 517                     #    #inserting new log
 518                     #    if uuid is None: uuid_k = uuid_v = ""
 519                     #    else: uuid_k=",uuid"; uuid_v=",'" + str(uuid) + "'"
 520                     #    cmd = "INSERT INTO logs (related,level%s,description)
 521                     #        VALUES ('%s','debug'%s,\"updating %d entry %s\")" \
 522                     #        % (uuid_k, table, uuid_v, nb_rows, (str(UPDATE)).replace('"','-')  )
 523                     #    self.logger.debug(cmd)
 524                     #    self.cur.execute(cmd)
 525                     return nb_rows, uuid
 526             except (mdb.Error, AttributeError) as e:
 527                 r, c = self.format_error(e, "update_rows", cmd)
 528                 if r != -HTTP_Request_Timeout or retry_ == 1:
 529                     return r, c
 530
 531     def get_host(self, host_id):
 532         if af.check_valid_uuid(host_id):
 533             where_filter = "uuid='" + host_id + "'"
 534         else:
 535             where_filter = "name='" + host_id + "'"
 536         for retry_ in range(0, 2):
 537             cmd = ""
 538             try:
 539                 with self.lock, self.con:
 540                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 541                     # get HOST
 542                     cmd = "SELECT uuid, user, password, keyfile, name, ip_name, description, hypervisors, " \
 543                           "ranking, admin_state_up, DATE_FORMAT(created_at,'%Y-%m-%dT%H:%i:%s') as created_at " \
 544                           "FROM hosts WHERE " + where_filter  # Unikernels extension
 545                     self.logger.debug(cmd)
 546                     self.cur.execute(cmd)
 547                     if self.cur.rowcount == 0:
 548                         return 0, "host '" + str(host_id) + "'not found."
 549                     elif self.cur.rowcount > 1:
 550                         return 0, "host '" + str(host_id) + "' matches more than one result."
 551                     host = self.cur.fetchone()
 552                     host_id = host['uuid']
 553                     if host.get("password"):
 554                         host["password"] = "*****"
 555                     # get numa
 556                     cmd = "SELECT id, numa_socket, hugepages, memory, admin_state_up FROM numas " \
 557                           "WHERE host_id = '" + str(host_id) + "'"
 558                     self.logger.debug(cmd)
 559                     self.cur.execute(cmd)
 560                     host['numas'] = self.cur.fetchall()
 561                     for numa in host['numas']:
 562                         # print "SELECT core_id, instance_id, status, thread_id, v_thread_id FROM resources_core
 563                         # WHERE numa_id = '" + str(numa['id']) + "'"
 564                         # get cores
 565                         cmd = "SELECT core_id, instance_id, status, thread_id, v_thread_id FROM resources_core " \
 566                               "WHERE numa_id = '" + str(numa['id']) + "'"
 567                         self.logger.debug(cmd)
 568                         self.cur.execute(cmd)
 569                         numa['cores'] = self.cur.fetchall()
 570                         for core in numa['cores']:
 571                             if core['instance_id']is None:
 572                                 del core['instance_id'], core['v_thread_id']
 573                             if core['status'] == 'ok':
 574                                 del core['status']
 575                         # get used memory
 576                         cmd = "SELECT sum(consumed) as hugepages_consumed FROM resources_mem " \
 577                               "WHERE numa_id = '" + str(numa['id']) + "' GROUP BY numa_id"
 578                         self.logger.debug(cmd)
 579                         self.cur.execute(cmd)
 580                         used = self.cur.fetchone()
 581                         used_ = int(used['hugepages_consumed']) if used is not None else 0
 582                         numa['hugepages_consumed'] = used_
 583                         # get ports
 584                         # cmd = "CALL GetPortsFromNuma(%s)'" % str(numa['id'])
 585                         # self.cur.callproc('GetPortsFromNuma', (numa['id'],) )
 586                         # every time a Procedure is launched you need to close and open the cursor
 587                         # under Error 2014: Commands out of sync; you can't run this command now
 588                         # self.cur.close()
 589                         # self.cur = self.con.cursor(mdb.cursors.DictCursor)
 590                         cmd = "SELECT Mbps, pci, status, Mbps_used, instance_id, if(id=root_id,'PF','VF') as type_, " \
 591                               "switch_port, switch_dpid, switch_mac, mac, source_name " \
 592                               "FROM resources_port WHERE numa_id={} ORDER BY root_id, type_ DESC".format(numa['id'])
 593                         self.logger.debug(cmd)
 594                         self.cur.execute(cmd)
 595                         ifaces = self.cur.fetchall()
 596                         # The SQL query will ensure to have SRIOV interfaces from a port first
 597                         sriovs = []
 598                         Mpbs_consumed = 0
 599                         numa['interfaces'] = []
 600                         for iface in ifaces:
 601                             if not iface["instance_id"]:
 602                                 del iface["instance_id"]
 603                             if iface['status'] == 'ok':
 604                                 del iface['status']
 605                             Mpbs_consumed += int(iface["Mbps_used"])
 606                             del iface["Mbps_used"]
 607                             if iface["type_"] == 'PF':
 608                                 if not iface["switch_dpid"]:
 609                                     del iface["switch_dpid"]
 610                                 if not iface["switch_port"]:
 611                                     del iface["switch_port"]
 612                                 if not iface["switch_mac"]:
 613                                     del iface["switch_mac"]
 614                                 if sriovs:
 615                                     iface["sriovs"] = sriovs
 616                                 if Mpbs_consumed:
 617                                     iface["Mpbs_consumed"] = Mpbs_consumed
 618                                 del iface["type_"]
 619                                 numa['interfaces'].append(iface)
 620                                 sriovs = []
 621                                 Mpbs_consumed = 0
 622                             else:  # VF, SRIOV
 623                                 del iface["switch_port"]
 624                                 del iface["switch_dpid"]
 625                                 del iface["switch_mac"]
 626                                 del iface["type_"]
 627                                 del iface["Mbps"]
 628                                 sriovs.append(iface)
 629
 630                         # delete internal field
 631                         del numa['id']
 632                     return 1, host
 633             except (mdb.Error, AttributeError) as e:
 634                 r, c = self.format_error(e, "get_host", cmd)
 635                 if r != -HTTP_Request_Timeout or retry_ == 1:
 636                     return r, c
 637
 638     def new_uuid(self):
 639         max_retries = 10
 640         while max_retries > 0:
 641             uuid = str(myUuid.uuid1())
 642             if self.check_uuid(uuid)[0] == 0:
 643                 return uuid
 644             max_retries -= 1
 645         return uuid
 646
 647     def check_uuid(self, uuid):
 648         """check in the database if this uuid is already present"""
 649         try:
 650             cmd = "SELECT * FROM uuids where uuid='" + str(uuid) + "'"
 651             with self.lock, self.con:
 652                 self.cur = self.con.cursor(mdb.cursors.DictCursor)
 653                 self.logger.debug(cmd)
 654                 self.cur.execute(cmd)
 655                 rows = self.cur.fetchall()
 656                 return self.cur.rowcount, rows
 657         except (mdb.Error, AttributeError) as e:
 658             return self.format_error(e, "check_uuid", cmd)
 659
 660     def __get_next_ids(self):
 661         """get next auto increment index of all table in the database"""
 662         self.cur.execute("SELECT table_name,AUTO_INCREMENT FROM information_schema.tables "
 663                          "WHERE AUTO_INCREMENT IS NOT NULL AND table_schema = DATABASE()")
 664         rows = self.cur.fetchall()
 665         return self.cur.rowcount, dict(rows)
 666
 667     def edit_host(self, host_id, host_dict):
 668         # get next port index
 669         for retry_ in range(0, 2):
 670             cmd = ""
 671             try:
 672                 with self.lock, self.con:
 673                     self.cur = self.con.cursor()
 674
 675                     # update table host
 676                     numa_list = host_dict.pop('numas', ())
 677                     if host_dict:
 678                         self._update_rows_internal("hosts", host_dict, {"uuid": host_id})
 679
 680                     where = {"host_id": host_id}
 681                     for numa_dict in numa_list:
 682                         where["numa_socket"] = str(numa_dict.pop('numa_socket'))
 683                         interface_list = numa_dict.pop('interfaces', ())
 684                         if numa_dict:
 685                             self._update_rows_internal("numas", numa_dict, where)
 686                         for interface in interface_list:
 687                             source_name = str(interface.pop("source_name"))
 688                             if interface:
 689                                 # get interface id from resources_port
 690                                 cmd = "SELECT rp.id as id " \
 691                                       "FROM resources_port as rp join numas as n on n.id=rp.numa_id join " \
 692                                       "hosts as h on h.uuid=n.host_id " + \
 693                                       "WHERE host_id='{}' and rp.source_name='{}'".format(host_id, source_name)
 694                                 self.logger.debug(cmd)
 695                                 self.cur.execute(cmd)
 696                                 row = self.cur.fetchone()
 697                                 if self.cur.rowcount <= 0:
 698                                     return -HTTP_Bad_Request, "Interface source_name='{}s' from numa_socket='{}' " \
 699                                                               "not found".format(source_name, where["numa_socket"])
 700                                 interface_id = row[0]
 701                                 self._update_rows_internal("resources_port", interface, {"root_id": interface_id})
 702                 return self.get_host(host_id)
 703             except (mdb.Error, AttributeError) as e:
 704                 r, c = self.format_error(e, "edit_host", cmd)
 705                 if r != -HTTP_Request_Timeout or retry_ == 1:
 706                     return r, c
 707
 708     def new_host(self, host_dict):
 709         # get next port index
 710         for retry_ in range(0, 2):
 711             cmd = ""
 712             try:
 713                 with self.lock, self.con:
 714                     self.cur = self.con.cursor()
 715
 716                     result, next_ids = self.__get_next_ids()
 717                     # print "next_ids: " + str(next_ids)
 718                     if result <= 0:
 719                         return result, "Internal DataBase error getting next id of tables"
 720
 721                     # create uuid if not provided
 722                     if 'uuid' not in host_dict:
 723                         uuid = host_dict['uuid'] = str(myUuid.uuid1())  # create_uuid
 724                     else:  # check uuid is valid
 725                         uuid = str(host_dict['uuid'])
 726                     #    result, data = self.check_uuid(uuid)
 727                     #    if (result == 1):
 728                     #        return -1, "UUID '%s' already in use" % uuid
 729
 730                     # inserting new uuid
 731                     cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','hosts')" % uuid
 732                     self.logger.debug(cmd)
 733                     result = self.cur.execute(cmd)
 734
 735                     # insert in table host
 736                     numa_list = host_dict.pop('numas', [])
 737                     # get nonhupages and nonisolated cpus
 738                     host_dict['RAM'] = 0
 739                     host_dict['cpus'] = 0
 740                     for numa in numa_list:
 741                         mem_numa = numa.get('memory', 0) - numa.get('hugepages', 0)
 742                         if mem_numa > 0:
 743                             host_dict['RAM'] += mem_numa
 744                         for core in numa.get("cores", []):
 745                             if "status" in core and core["status"] == "noteligible":
 746                                 host_dict['cpus'] += 1
 747                     host_dict['RAM'] *= 1024  # from GB to MB
 748
 749                     keys = ",".join(host_dict.keys())
 750                     values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'", host_dict.values()))
 751                     cmd = "INSERT INTO hosts (" + keys + ") VALUES (" + values + ")"
 752                     self.logger.debug(cmd)
 753                     result = self.cur.execute(cmd)
 754                     # if result != 1: return -1, "Database Error while inserting at hosts table"
 755
 756                     # insert numas
 757                     nb_numas = nb_cores = nb_ifaces = 0
 758                     for numa_dict in numa_list:
 759                         nb_numas += 1
 760                         interface_list = numa_dict.pop('interfaces', [])
 761                         core_list = numa_dict.pop('cores', [])
 762                         numa_dict['id'] = next_ids['numas']
 763                         next_ids['numas'] += 1
 764                         numa_dict['host_id'] = uuid
 765                         keys = ",".join(numa_dict.keys())
 766                         values = ",".join(
 767                             map(lambda x: "Null" if x is None else "'" + str(x) + "'", numa_dict.values()))
 768                         cmd = "INSERT INTO numas (" + keys + ") VALUES (" + values + ")"
 769                         self.logger.debug(cmd)
 770                         self.cur.execute(cmd)
 771
 772                         # insert cores
 773                         for core_dict in core_list:
 774                             nb_cores += 1
 775                             core_dict['numa_id'] = numa_dict['id']
 776                             keys = ",".join(core_dict.keys())
 777                             values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'",
 778                                                   core_dict.values()))
 779                             cmd = "INSERT INTO resources_core (" + keys + ") VALUES (" + values + ")"
 780                             self.logger.debug(cmd)
 781                             self.cur.execute(cmd)
 782
 783                         # insert ports
 784                         for port_dict in interface_list:
 785                             nb_ifaces += 1
 786                             sriov_list = port_dict.pop('sriovs', [])
 787                             port_dict['numa_id'] = numa_dict['id']
 788                             port_dict['id'] = port_dict['root_id'] = next_ids['resources_port']
 789                             next_ids['resources_port'] += 1
 790                             switch_port = port_dict.get('switch_port', None)
 791                             switch_dpid = port_dict.get('switch_dpid', None)
 792                             keys = ",".join(port_dict.keys())
 793                             values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'",
 794                                                   port_dict.values()))
 795                             cmd = "INSERT INTO resources_port (" + keys + ") VALUES (" + values + ")"
 796                             self.logger.debug(cmd)
 797                             self.cur.execute(cmd)
 798
 799                             # insert sriovs into port table
 800                             for sriov_dict in sriov_list:
 801                                 sriov_dict['switch_port'] = switch_port
 802                                 sriov_dict['switch_dpid'] = switch_dpid
 803                                 sriov_dict['numa_id'] = port_dict['numa_id']
 804                                 sriov_dict['Mbps'] = port_dict['Mbps']
 805                                 sriov_dict['root_id'] = port_dict['id']
 806                                 sriov_dict['id'] = next_ids['resources_port']
 807                                 if "vlan" in sriov_dict:
 808                                     del sriov_dict["vlan"]
 809                                 next_ids['resources_port'] += 1
 810                                 keys = ",".join(sriov_dict.keys())
 811                                 values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'",
 812                                                       sriov_dict.values()))
 813                                 cmd = "INSERT INTO resources_port (" + keys + ") VALUES (" + values + ")"
 814                                 self.logger.debug(cmd)
 815                                 self.cur.execute(cmd)
 816
 817                     # inserting new log
 818                     # cmd = "INSERT INTO logs (related,level,uuid,description) VALUES ('hosts','debug','%s','new host:
 819                     #     %d numas, %d theads, %d ifaces')" % (uuid, nb_numas, nb_cores, nb_ifaces)
 820                     # self.logger.debug(cmd)
 821                     # result = self.cur.execute(cmd)
 822
 823                     # inseted ok
 824                 with self.lock, self.con:
 825                     self.cur = self.con.cursor()
 826                     self.logger.debug("callproc('UpdateSwitchPort', () )")
 827                     self.cur.callproc('UpdateSwitchPort', ())
 828
 829                 self.logger.debug("getting host '%s'", str(host_dict['uuid']))
 830                 return self.get_host(host_dict['uuid'])
 831             except (mdb.Error, AttributeError) as e:
 832                 r, c = self.format_error(e, "new_host", cmd)
 833                 if r != -HTTP_Request_Timeout or retry_ == 1:
 834                     return r, c
 835
 836     def new_flavor(self, flavor_dict, tenant_id):
 837         """Add new flavor into the database. Create uuid if not provided
 838         Atributes
 839             flavor_dict: flavor dictionary with the key: value to insert. Must be valid flavors columns
 840             tenant_id: if not 'any', it matches this flavor/tenant inserting at tenants_flavors table
 841         Return: (result, data) where result can be
 842             negative: error at inserting. data contain text
 843             1, inserted, data contain inserted uuid flavor
 844         """
 845         for retry_ in range(0, 2):
 846             cmd = ""
 847             try:
 848                 with self.lock, self.con:
 849                     self.cur = self.con.cursor()
 850
 851                     # create uuid if not provided
 852                     if 'uuid' not in flavor_dict:
 853                         uuid = flavor_dict['uuid'] = str(myUuid.uuid1())  # create_uuid
 854                     else:  # check uuid is valid
 855                         uuid = str(flavor_dict['uuid'])
 856                     #    result, data = self.check_uuid(uuid)
 857                     #    if (result == 1):
 858                     #        return -1, "UUID '%s' already in use" % uuid
 859
 860                     # inserting new uuid
 861                     cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','flavors')" % uuid
 862                     self.logger.debug(cmd)
 863                     self.cur.execute(cmd)
 864
 865                     # insert in table flavor
 866                     keys = ",".join(flavor_dict.keys())
 867                     values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'", flavor_dict.values()))
 868                     cmd = "INSERT INTO flavors (" + keys + ") VALUES (" + values + ")"
 869                     self.logger.debug(cmd)
 870                     self.cur.execute(cmd)
 871                     # if result != 1: return -1, "Database Error while inserting at flavors table"
 872
 873                     # insert tenants_flavors
 874                     if tenant_id != 'any':
 875                         cmd = "INSERT INTO tenants_flavors (tenant_id,flavor_id) VALUES ('%s','%s')" % (tenant_id, uuid)
 876                         self.logger.debug(cmd)
 877                         self.cur.execute(cmd)
 878
 879                     # inserting new log
 880                     # del flavor_dict['uuid']
 881                     # if 'extended' in flavor_dict: del flavor_dict['extended'] #remove two many information
 882                     # cmd = "INSERT INTO logs (related,level,uuid, tenant_id, description) VALUES
 883                     #    ('flavors','debug','%s','%s',\"new flavor: %s\")" \
 884                     #    % (uuid, tenant_id, str(flavor_dict))
 885                     # self.logger.debug(cmd)
 886                     # self.cur.execute(cmd)
 887
 888                     # inseted ok
 889                 return 1, uuid
 890             except (mdb.Error, AttributeError) as e:
 891                 r, c = self.format_error(e, "new_flavor", cmd, "update", tenant_id)
 892                 if r != -HTTP_Request_Timeout or retry_ == 1:
 893                     return r, c
 894
 895     def new_image(self, image_dict, tenant_id):
 896         """Add new image into the database. Create uuid if not provided
 897         Atributes
 898             image_dict: image dictionary with the key: value to insert. Must be valid images columns
 899             tenant_id: if not 'any', it matches this image/tenant inserting at tenants_images table
 900         Return: (result, data) where result can be
 901             negative: error at inserting. data contain text
 902             1, inserted, data contain inserted uuid image
 903         """
 904         for retry_ in range(0, 2):
 905             cmd = ""
 906             try:
 907                 with self.lock, self.con:
 908                     self.cur = self.con.cursor()
 909
 910                     # create uuid if not provided
 911                     if 'uuid' not in image_dict:
 912                         uuid = image_dict['uuid'] = str(myUuid.uuid1())  # create_uuid
 913                     else:  # check uuid is valid
 914                         uuid = str(image_dict['uuid'])
 915                     #    result, data = self.check_uuid(uuid)
 916                     #    if (result == 1):
 917                     #        return -1, "UUID '%s' already in use" % uuid
 918
 919                     # inserting new uuid
 920                     cmd = "INSERT INTO uuids (uuid, used_at) VALUES ('%s','images')" % uuid
 921                     self.logger.debug(cmd)
 922                     self.cur.execute(cmd)
 923
 924                     # insert in table image
 925                     keys = ",".join(image_dict.keys())
 926                     values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'", image_dict.values()))
 927                     cmd = "INSERT INTO images (" + keys + ") VALUES (" + values + ")"
 928                     self.logger.debug(cmd)
 929                     self.cur.execute(cmd)
 930                     # if result != 1: return -1, "Database Error while inserting at images table"
 931
 932                     # insert tenants_images
 933                     if tenant_id != 'any':
 934                         cmd = "INSERT INTO tenants_images (tenant_id,image_id) VALUES ('%s','%s')" % (tenant_id, uuid)
 935                         self.logger.debug(cmd)
 936                         self.cur.execute(cmd)
 937
 938                     # #inserting new log
 939                     # cmd = "INSERT INTO logs (related,level,uuid, tenant_id, description) VALUES
 940                     # ('images','debug','%s','%s',\"new image: %s path: %s\")" %
 941                     # (uuid, tenant_id, image_dict['name'], image_dict['path'])
 942                     # self.logger.debug(cmd)
 943                     # self.cur.execute(cmd)
 944
 945                     # inseted ok
 946                 return 1, uuid
 947             except (mdb.Error, AttributeError) as e:
 948                 r, c = self.format_error(e, "new_image", cmd, "update", tenant_id)
 949                 if r != -HTTP_Request_Timeout or retry_ == 1:
 950                     return r, c
 951
 952     def delete_image_flavor(self, item_type, item_id, tenant_id):
 953         """deletes an image or flavor from database
 954         item_type must be a 'image' or 'flavor'
 955         item_id is the uuid
 956         tenant_id is the asociated tenant, can be 'any' with means all
 957         If tenan_id is not any, it deletes from tenants_images/flavors,
 958         which means this image/flavor is used by this tenant, and if success,
 959         it tries to delete from images/flavors in case this is not public,
 960         that only will success if image is private and not used by other tenants
 961         If tenant_id is any, it tries to delete from both tables at the same transaction
 962         so that image/flavor is completely deleted from all tenants or nothing
 963         """
 964         for retry_ in range(0, 2):
 965             deleted = -1
 966             deleted_item = -1
 967             result = (-HTTP_Internal_Server_Error, "internal error")
 968             cmd = ""
 969             try:
 970                 with self.lock, self.con:
 971                     self.cur = self.con.cursor()
 972                     cmd = "DELETE FROM tenants_%ss WHERE %s_id = '%s'" % (item_type, item_type, item_id)
 973                     if tenant_id != 'any':
 974                         cmd += " AND tenant_id = '%s'" % tenant_id
 975                     self.logger.debug(cmd)
 976                     self.cur.execute(cmd)
 977                     deleted = self.cur.rowcount
 978                     if tenant_id == 'any':  # delete from images/flavors in the SAME transaction
 979                         cmd = "DELETE FROM %ss WHERE uuid = '%s'" % (item_type, item_id)
 980                         self.logger.debug(cmd)
 981                         self.cur.execute(cmd)
 982                         deleted = self.cur.rowcount
 983                         if deleted >= 1:
 984                             # delete uuid
 985                             cmd = "DELETE FROM uuids WHERE uuid = '%s'" % item_id
 986                             self.logger.debug(cmd)
 987                             self.cur.execute(cmd)
 988                             # #inserting new log
 989                             # cmd = "INSERT INTO logs (related,level,uuid,tenant_id,description) \
 990                             #       VALUES ('%ss','debug','%s','%s','delete %s completely')" % \
 991                             #       (item_type, item_id, tenant_id, item_type)
 992                             # self.logger.debug(cmd)
 993                             # self.cur.execute(cmd)
 994                             return deleted, "%s '%s' completely deleted" % (item_type, item_id)
 995                         return 0, "%s '%s' not found" % (item_type, item_id)
 996
 997                     if deleted == 1:
 998                         # #inserting new log
 999                         # cmd = "INSERT INTO logs (related,level,uuid,tenant_id,description) \
1000                         #        VALUES ('%ss','debug','%s','%s','delete %s reference for this tenant')" % \
1001                         #        (item_type, item_id, tenant_id, item_type)
1002                         # self.logger.debug(cmd)
1003                         # self.cur.execute(cmd)
1004
1005                         # commit transaction
1006                         self.cur.close()
1007                 # if tenant!=any  delete from images/flavors in OTHER transaction.
1008                 # If fails is because dependencies so that not return error
1009                 if deleted == 1:
1010                     with self.lock, self.con:
1011                         self.cur = self.con.cursor()
1012
1013                         # delete image/flavor if not public
1014                         cmd = "DELETE FROM %ss WHERE uuid = '%s' AND public = 'no'" % (item_type, item_id)
1015                         self.logger.debug(cmd)
1016                         self.cur.execute(cmd)
1017                         deleted_item = self.cur.rowcount
1018                         if deleted_item == 1:
1019                             # delete uuid
1020                             cmd = "DELETE FROM uuids WHERE uuid = '%s'" % item_id
1021                             self.logger.debug(cmd)
1022                             self.cur.execute(cmd)
1023                             # #inserting new log
1024                             # cmd = "INSERT INTO logs (related,level,uuid,tenant_id,description) \
1025                             #       VALUES ('%ss','debug','%s','%s','delete %s completely')" % \
1026                             #       (item_type, item_id, tenant_id, item_type)
1027                             # self.logger.debug(cmd)
1028                             # self.cur.execute(cmd)
1029             except (mdb.Error, AttributeError) as e:
1030                 # print "delete_%s DB Exception %d: %s" % (item_type, e.args[0], e.args[1])
1031                 if deleted < 0:
1032                     result = self.format_error(e, "delete_" + item_type, cmd, "delete", "servers")
1033             finally:
1034                 if deleted == 1:
1035                     return 1, "{} '{}' from tenant '{}' {}deleted".format(item_type, item_id, tenant_id,
1036                                                                           "completely " if deleted_item == 1 else "")
1037                 elif deleted == 0:
1038                     return 0, "{} '{}' from tenant '{}' not found".format(item_type, item_id, tenant_id)
1039                 else:
1040                     if result[0] != -HTTP_Request_Timeout or retry_ == 1:
1041                         return result
1042
1043     def delete_row(self, table, uuid):
1044         for retry_ in range(0, 2):
1045             cmd = ""
1046             try:
1047                 with self.lock, self.con:
1048                     # delete host
1049                     self.cur = self.con.cursor()
1050                     cmd = "DELETE FROM %s WHERE uuid = '%s'" % (table, uuid)
1051                     self.logger.debug(cmd)
1052                     self.cur.execute(cmd)
1053                     deleted = self.cur.rowcount
1054                     if deleted == 1:
1055                         # delete uuid
1056                         # if table == 'tenants':
1057                         #     tenant_str = uuid
1058                         # else:
1059                         #     tenant_str = 'Null'
1060                         self.cur = self.con.cursor()
1061                         cmd = "DELETE FROM uuids WHERE uuid = '%s'" % uuid
1062                         self.logger.debug(cmd)
1063                         self.cur.execute(cmd)
1064                         # #inserting new log
1065                         # cmd = "INSERT INTO logs (related,level,uuid,tenant_id,description) VALUES
1066                         # ('%s','debug','%s','%s','delete %s')" % (table, uuid, tenant_str, table[:-1])
1067                         # self.logger.debug(cmd)
1068                         # self.cur.execute(cmd)
1069                 return deleted, table[:-1] + " '%s' %s" % (uuid, "deleted" if deleted == 1 else "not found")
1070             except (mdb.Error, AttributeError) as e:
1071                 r, c = self.format_error(e, "delete_row", cmd, "delete",
1072                                          'instances' if table in ('hosts', 'tenants') else 'dependencies')
1073                 if r != -HTTP_Request_Timeout or retry_ == 1:
1074                     return r, c
1075
1076     def delete_row_by_key(self, table, key, value):
1077         for retry_ in range(0, 2):
1078             cmd = ""
1079             try:
1080                 with self.lock, self.con:
1081                     # delete host
1082                     self.cur = self.con.cursor()
1083                     cmd = "DELETE FROM %s" % (table)
1084                     if key:
1085                         if value:
1086                             cmd += " WHERE %s = '%s'" % (key, value)
1087                         else:
1088                             cmd += " WHERE %s is null" % (key)
1089                     else:  # delete all
1090                         pass
1091                     self.logger.debug(cmd)
1092                     self.cur.execute(cmd)
1093                     deleted = self.cur.rowcount
1094                     if deleted < 1:
1095                         return -1, 'Not found'
1096                         #  delete uuid
1097                     return 0, deleted
1098             except (mdb.Error, AttributeError) as e:
1099                 r, c = self.format_error(e, "delete_row_by_key", cmd, "delete",
1100                                          'instances' if table in ('hosts', 'tenants') else 'dependencies')
1101                 if r != -HTTP_Request_Timeout or retry_ == 1:
1102                     return r, c
1103
1104     def delete_row_by_dict(self, **sql_dict):
1105         """ Deletes rows from a table.
1106         Attribute sql_dir: dictionary with the following key: value
1107             'FROM': string of table name (Mandatory)
1108             'WHERE': dict of key:values, translated to key=value AND ... (Optional)
1109             'WHERE_NOT': dict of key:values, translated to key<>value AND ... (Optional)
1110             'WHERE_NOTNULL': (list or tuple of items that must not be null in a where ... (Optional)
1111             'LIMIT': limit of number of rows (Optional)
1112         Return: the (number of items deleted, descriptive test) if ok; (negative, descriptive text) if error
1113         """
1114         # print sql_dict
1115         from_ = "FROM " + str(sql_dict['FROM'])
1116         # print 'from_', from_
1117         if 'WHERE' in sql_dict and len(sql_dict['WHERE']) > 0:
1118             w = sql_dict['WHERE']
1119             where_ = "WHERE " + " AND ".join(map(lambda x: str(x) + (" is Null" if w[x] is None else "='" + str(w[x]) +
1120                                                                                                      "'"), w.keys()))
1121         else:
1122             where_ = ""
1123         if 'WHERE_NOT' in sql_dict and len(sql_dict['WHERE_NOT']) > 0:
1124             w = sql_dict['WHERE_NOT']
1125             where_2 = " AND ".join(map(lambda x: str(x) + (" is not Null" if w[x] is None else "<>'" + str(w[x]) + "'"),
1126                                        w.keys()))
1127             if len(where_) == 0:
1128                 where_ = "WHERE " + where_2
1129             else:
1130                 where_ = where_ + " AND " + where_2
1131         if 'WHERE_NOTNULL' in sql_dict and len(sql_dict['WHERE_NOTNULL']) > 0:
1132             w = sql_dict['WHERE_NOTNULL']
1133             where_2 = " AND ".join(map(lambda x: str(x) + " is not Null", w))
1134             if len(where_) == 0:
1135                 where_ = "WHERE " + where_2
1136             else:
1137                 where_ = where_ + " AND " + where_2
1138         # print 'where_', where_
1139         limit_ = "LIMIT " + str(sql_dict['LIMIT']) if 'LIMIT' in sql_dict else ""
1140         # print 'limit_', limit_
1141         cmd = " ".join(("DELETE", from_, where_, limit_))
1142         self.logger.debug(cmd)
1143         for retry_ in range(0, 2):
1144             try:
1145                 with self.lock, self.con:
1146                     # delete host
1147                     self.cur = self.con.cursor()
1148                     self.cur.execute(cmd)
1149                     deleted = self.cur.rowcount
1150                 return deleted, "%d deleted from %s" % (deleted, sql_dict['FROM'][:-1])
1151             except (mdb.Error, AttributeError) as e:
1152                 r, c = self.format_error(e, "delete_row_by_dict", cmd, "delete", 'dependencies')
1153                 if r != -HTTP_Request_Timeout or retry_ == 1:
1154                     return r, c
1155
1156     def get_instance(self, instance_id):
1157         for retry_ in range(0, 2):
1158             cmd = ""
1159             try:
1160                 with self.lock, self.con:
1161                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1162                     # get INSTANCE
1163                     cmd = "SELECT uuid, name, description, progress, host_id, flavor_id, image_id, status, " \
1164                           "hypervisor, os_image_type, last_error, tenant_id, ram, vcpus, created_at " \
1165                           "FROM instances WHERE uuid='{}'".format(instance_id)  # Unikernels extension
1166                     self.logger.debug(cmd)
1167                     self.cur.execute(cmd)
1168                     if self.cur.rowcount == 0:
1169                         return 0, "instance '" + str(instance_id) + "'not found."
1170                     instance = self.cur.fetchone()
1171                     # get networks
1172                     cmd = "SELECT uuid as iface_id, net_id, mac as mac_address, ip_address, name, Mbps as bandwidth, " \
1173                           "vpci, model FROM ports WHERE (type='instance:bridge' or type='instance:ovs') AND " \
1174                           "instance_id= '{}'".format(instance_id)
1175                     self.logger.debug(cmd)
1176                     self.cur.execute(cmd)
1177                     if self.cur.rowcount > 0:
1178                         instance['networks'] = self.cur.fetchall()
1179
1180                     # get extended
1181                     extended = {}
1182                     # get devices
1183                     cmd = "SELECT type,vpci,image_id,xml,dev,image_size FROM instance_devices " \
1184                           "WHERE instance_id = '%s' " % str(instance_id)
1185                     self.logger.debug(cmd)
1186                     self.cur.execute(cmd)
1187                     if self.cur.rowcount > 0:
1188                         extended['devices'] = self.cur.fetchall()
1189                     # get numas
1190                     numas = []
1191                     cmd = "SELECT id, numa_socket as source FROM numas WHERE host_id = '{}'".format(instance['host_id'])
1192                     self.logger.debug(cmd)
1193                     self.cur.execute(cmd)
1194                     host_numas = self.cur.fetchall()
1195                     # print 'host_numas', host_numas
1196                     for k in host_numas:
1197                         numa_id = str(k['id'])
1198                         numa_dict = {}
1199                         # get memory
1200                         cmd = "SELECT consumed FROM resources_mem WHERE instance_id = '{}' AND numa_id = '{}'".foramt(
1201                             instance_id, numa_id)
1202                         self.logger.debug(cmd)
1203                         self.cur.execute(cmd)
1204                         if self.cur.rowcount > 0:
1205                             mem_dict = self.cur.fetchone()
1206                             numa_dict['memory'] = mem_dict['consumed']
1207                         # get full cores
1208                         cursor2 = self.con.cursor()
1209                         cmd = "SELECT core_id, paired, MIN(v_thread_id) as v1, MAX(v_thread_id) as v2, " \
1210                               "COUNT(instance_id) as nb, MIN(thread_id) as t1, MAX(thread_id) as t2 " \
1211                               "FROM resources_core " \
1212                               "WHERE instance_id = '{}' AND numa_id = '{}' GROUP BY core_id,paired".format(instance_id,
1213                                                                                                            numa_id)
1214                         self.logger.debug(cmd)
1215                         cursor2.execute(cmd)
1216                         core_list = []
1217                         core_source = []
1218                         paired_list = []
1219                         paired_source = []
1220                         thread_list = []
1221                         thread_source = []
1222                         if cursor2.rowcount > 0:
1223                             cores = cursor2.fetchall()
1224                             for core in cores:
1225                                 if core[4] == 2:  # number of used threads from core
1226                                     if core[3] == core[2]:  # only one thread asigned to VM, so completely core
1227                                         core_list.append(core[2])
1228                                         core_source.append(core[5])
1229                                     elif core[1] == 'Y':
1230                                         paired_list.append(core[2:4])
1231                                         paired_source.append(core[5:7])
1232                                     else:
1233                                         thread_list.extend(core[2:4])
1234                                         thread_source.extend(core[5:7])
1235
1236                                 else:
1237                                     thread_list.append(core[2])
1238                                     thread_source.append(core[5])
1239                             if len(core_list) > 0:
1240                                 numa_dict['cores'] = len(core_list)
1241                                 numa_dict['cores-id'] = core_list
1242                                 numa_dict['cores-source'] = core_source
1243                             if len(paired_list) > 0:
1244                                 numa_dict['paired-threads'] = len(paired_list)
1245                                 numa_dict['paired-threads-id'] = paired_list
1246                                 numa_dict['paired-threads-source'] = paired_source
1247                             if len(thread_list) > 0:
1248                                 numa_dict['threads'] = len(thread_list)
1249                                 numa_dict['threads-id'] = thread_list
1250                                 numa_dict['threads-source'] = thread_source
1251
1252                         # get dedicated ports and SRIOV
1253                         cmd = "SELECT port_id as iface_id, p.vlan as vlan, p.mac as mac_address, net_id, " \
1254                               "if(model='PF','yes',if(model='VF','no','yes:sriov')) as dedicated, p.Mbps as bandwidth" \
1255                               ", name, vpci, pci as source " \
1256                               "FROM resources_port as rp join ports as p on port_id=uuid  " \
1257                               "WHERE p.instance_id = '{}' AND numa_id = '{}' and " \
1258                               "p.type='instance:data'".format(instance_id, numa_id)
1259                         self.logger.debug(cmd)
1260                         self.cur.execute(cmd)
1261                         if self.cur.rowcount > 0:
1262                             numa_dict['interfaces'] = self.cur.fetchall()
1263                             # print 'interfaces', numa_dict
1264
1265                         if len(numa_dict) > 0:
1266                             numa_dict['source'] = k['source']  # numa socket
1267                             numas.append(numa_dict)
1268
1269                     if len(numas) > 0:
1270                         extended['numas'] = numas
1271                     if len(extended) > 0:
1272                         instance['extended'] = extended
1273                     af.DeleteNone(instance)
1274                     return 1, instance
1275             except (mdb.Error, AttributeError) as e:
1276                 r, c = self.format_error(e, "get_instance", cmd)
1277                 if r != -HTTP_Request_Timeout or retry_ == 1:
1278                     return r, c
1279
1280     def get_numas(self, requirements, prefered_host_id=None, only_of_ports=True):
1281         """Obtain a valid NUMA/HOST for deployment a VM
1282         requirements: contain requirement regarding:
1283             requirements['ram']: Non huge page memory in MB; 0 to skip
1284             requirements['vcpus']: Non isolated cpus; 0 to skip
1285             requirements['numa']: Requiremets to be fixed in ONE Numa node
1286                 requirements['numa']['memory']: Huge page memory in GB at ; 0 for any
1287                 requirements['numa']['proc_req_type']: Type of processor, cores or threads
1288                 requirements['numa']['proc_req_nb']: Number of isolated cpus
1289                 requirements['numa']['port_list']: Physical NIC ports list ; [] for any
1290                 requirements['numa']['sriov_list']: Virtual function NIC ports list ; [] for any
1291         prefered_host_id: if not None return this host if it match
1292         only_of_ports: if True only those ports conected to the openflow (of) are valid,
1293             that is, with switch_port information filled; if False, all NIC ports are valid.
1294         Return a valid numa and host
1295         """
1296
1297         for retry_ in range(0, 2):
1298             cmd = ""
1299             try:
1300                 with self.lock, self.con:
1301                     # #Find numas of prefered host
1302                     # prefered_numas = ()
1303                     # if prefered_host_id is not None:
1304                     #     self.cur = self.con.cursor()
1305                     #     self.cur.execute("SELECT id FROM numas WHERE host_id='%s'" + prefered_host_id)
1306                     #     prefered_numas = self.cur.fetchall()
1307                     #     self.cur.close()
1308
1309                     # Find valid host for the ram and vcpus
1310                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1311                     cmd = "CALL GetHostByMemCpu(%s, %s)" % (str(requirements['ram']), str(requirements['vcpus']))
1312                     self.logger.debug(cmd)
1313                     self.cur.callproc('GetHostByMemCpu', (str(requirements['ram']), str(requirements['vcpus'])))
1314                     valid_hosts = self.cur.fetchall()
1315                     self.cur.close()
1316                     self.cur = self.con.cursor()
1317                     match_found = False
1318                     if len(valid_hosts) <= 0:
1319                         error_text = 'No room at data center. Cannot find a host with %s MB memory and %s cpus ' \
1320                                      'available' % (str(requirements['ram']), str(requirements['vcpus']))
1321                         # self.logger.debug(error_text)
1322                         return -1, error_text
1323
1324                     if 'hypervisor' not in requirements:  # Unikernels extension -END-
1325                         requirements['hypervisor'] = "kvm"
1326                     for valid_host in valid_hosts:
1327                         if 'hypervisors' not in valid_host:
1328                             valid_host['hypervisors'] = "kvm"
1329
1330                     valid_hosts = tuple(valid_host for valid_host in valid_hosts if
1331                                         requirements['hypervisor'] in valid_host['hypervisors'].split(","))
1332
1333                     if len(valid_hosts) <= 0:
1334                         error_text = 'No room at data center. Cannot find a host with %s hypervisor or not have ' \
1335                                      'enough resources available' % (str(requirements['hypervisor']))
1336                         # self.logger.debug(error_text)
1337                         return -1, error_text  # Unikernels extension -END-
1338
1339                     # elif req_numa is not None:
1340                     # Find valid numa nodes for memory requirements
1341                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1342                     cmd = "CALL GetNumaByMemory(%s)" % str(requirements['numa']['memory'])
1343                     self.logger.debug(cmd)
1344                     self.cur.callproc('GetNumaByMemory', (requirements['numa']['memory'],))
1345                     valid_for_memory = self.cur.fetchall()
1346                     self.cur.close()
1347                     self.cur = self.con.cursor()
1348                     if len(valid_for_memory) <= 0:
1349                         error_text = 'No room at data center. Cannot find a host with %s GB Hugepages memory' \
1350                                      ' available' % str(requirements['numa']['memory'])
1351                         # self.logger.debug(error_text)
1352                         return -1, error_text
1353
1354                     # Find valid numa nodes for processor requirements
1355                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1356                     if requirements['numa']['proc_req_type'] == 'threads':
1357                         cpu_requirement_text = 'cpu-threads'
1358                         cmd = "CALL GetNumaByThread(%s)" % str(requirements['numa']['proc_req_nb'])
1359                         self.logger.debug(cmd)
1360                         self.cur.callproc('GetNumaByThread', (requirements['numa']['proc_req_nb'],))
1361                     else:
1362                         cpu_requirement_text = 'cpu-cores'
1363                         cmd = "CALL GetNumaByCore(%s)" % str(requirements['numa']['proc_req_nb'])
1364                         self.logger.debug(cmd)
1365                         self.cur.callproc('GetNumaByCore', (requirements['numa']['proc_req_nb'],))
1366                     valid_for_processor = self.cur.fetchall()
1367                     self.cur.close()
1368                     self.cur = self.con.cursor()
1369                     if len(valid_for_processor) <= 0:
1370                         error_text = 'No room at data center. Cannot find a host with %s %s available' % (
1371                             str(requirements['numa']['proc_req_nb']), cpu_requirement_text)
1372                         # self.logger.debug(error_text)
1373                         return -1, error_text
1374
1375                     # Find the numa nodes that comply for memory and processor requirements
1376                     # sorting from less to more memory capacity
1377                     valid_numas = []
1378                     for m_numa in valid_for_memory:
1379                         numa_valid_for_processor = False
1380                         for p_numa in valid_for_processor:
1381                             if m_numa['numa_id'] == p_numa['numa_id']:
1382                                 numa_valid_for_processor = True
1383                                 break
1384                         numa_valid_for_host = False
1385                         prefered_numa = False
1386                         for p_host in valid_hosts:
1387                             if m_numa['host_id'] == p_host['uuid']:
1388                                 numa_valid_for_host = True
1389                                 if p_host['uuid'] == prefered_host_id:
1390                                     prefered_numa = True
1391                                 break
1392                         if numa_valid_for_host and numa_valid_for_processor:
1393                             if prefered_numa:
1394                                 valid_numas.insert(0, m_numa['numa_id'])
1395                             else:
1396                                 valid_numas.append(m_numa['numa_id'])
1397                     if len(valid_numas) <= 0:
1398                         error_text = "No room at data center. Cannot find a host with {} MB hugepages memory and {} " \
1399                                      "{} available in the same numa".format(requirements['numa']['memory'],
1400                                                                             requirements['numa']['proc_req_nb'],
1401                                                                             cpu_requirement_text)
1402                         # self.logger.debug(error_text)
1403                         return -1, error_text
1404
1405                     # print 'Valid numas list: '+str(valid_numas)
1406
1407                     # Find valid numa nodes for interfaces requirements
1408                     # For each valid numa we will obtain the number of available ports and check if these are valid
1409                     match_found = False
1410                     for numa_id in valid_numas:
1411                         # print 'Checking '+str(numa_id)
1412                         match_found = False
1413                         self.cur = self.con.cursor(mdb.cursors.DictCursor)
1414                         if only_of_ports:
1415                             cmd = "CALL GetAvailablePorts(%s)" % str(numa_id)
1416                             self.logger.debug(cmd)
1417                             self.cur.callproc('GetAvailablePorts', (numa_id,))
1418                         else:
1419                             cmd = "CALL GetAllAvailablePorts(%s)" % str(numa_id)
1420                             self.logger.debug(cmd)
1421                             self.cur.callproc('GetAllAvailablePorts', (numa_id,))
1422                         available_ports = self.cur.fetchall()
1423                         self.cur.close()
1424                         self.cur = self.con.cursor()
1425
1426                         # Set/reset reservations
1427                         for port in available_ports:
1428                             port['Mbps_reserved'] = 0
1429                             port['SRIOV_reserved'] = 0
1430
1431                         # Try to allocate physical ports
1432                         physical_ports_found = True
1433                         for iface in requirements['numa']['port_list']:
1434                             # print '\t\tchecking iface: '+str(iface)
1435                             portFound = False
1436                             for port in available_ports:
1437                                 # print '\t\t\tfor port: '+str(port)
1438                                 # If the port is not empty continue
1439                                 if port['Mbps_free'] != port['Mbps'] or port['Mbps_reserved'] != 0:
1440                                     # print '\t\t\t\t Not empty port'
1441                                     continue
1442                                 # If the port speed is not enough continue
1443                                 if port['Mbps'] < iface['bandwidth']:
1444                                     # print '\t\t\t\t Not enough speed'
1445                                     continue
1446
1447                                 # Otherwise this is a valid port
1448                                 port['Mbps_reserved'] = port['Mbps']
1449                                 port['SRIOV_reserved'] = 0
1450                                 iface['port_id'] = port['port_id']
1451                                 iface['vlan'] = None
1452                                 iface['mac'] = port['mac']
1453                                 iface['switch_port'] = port['switch_port']
1454                                 # print '\t\t\t\t Dedicated port found '+str(port['port_id'])
1455                                 portFound = True
1456                                 break
1457
1458                             # if all ports have been checked and no match has been found
1459                             # this is not a valid numa
1460                             if not portFound:
1461                                 # print '\t\t\t\t\tAll ports have been checked and no match has been found for
1462                                 # numa '+str(numa_id)+'\n\n'
1463                                 physical_ports_found = False
1464                                 break
1465
1466                         # if there is no match continue checking the following numa
1467                         if not physical_ports_found:
1468                             continue
1469
1470                         # Try to allocate SR-IOVs
1471                         sriov_ports_found = True
1472                         for iface in requirements['numa']['sriov_list']:
1473                             # print '\t\tchecking iface: '+str(iface)
1474                             portFound = False
1475                             for port in available_ports:
1476                                 # print '\t\t\tfor port: '+str(port)
1477                                 # If there are not available SR-IOVs continue
1478                                 if port['availableSRIOV'] - port['SRIOV_reserved'] <= 0:
1479                                     # print '\t\t\t\t Not enough SR-IOV'
1480                                     continue
1481                                 # If the port free speed is not enough continue
1482                                 if port['Mbps_free'] - port['Mbps_reserved'] < iface['bandwidth']:
1483                                     # print '\t\t\t\t Not enough speed'
1484                                     continue
1485
1486                                 # Otherwise this is a valid port
1487                                 port['Mbps_reserved'] += iface['bandwidth']
1488                                 port['SRIOV_reserved'] += 1
1489                                 # print '\t\t\t\t SR-IOV found '+str(port['port_id'])
1490                                 iface['port_id'] = port['port_id']
1491                                 iface['vlan'] = None
1492                                 iface['mac'] = port['mac']
1493                                 iface['switch_port'] = port['switch_port']
1494                                 portFound = True
1495                                 break
1496
1497                             # if all ports have been checked and no match has been found
1498                             # this is not a valid numa
1499                             if not portFound:
1500                                 # print '\t\t\t\t\tAll ports have been checked and no match has been found for numa
1501                                 # '+str(numa_id)+'\n\n'
1502                                 sriov_ports_found = False
1503                                 break
1504
1505                         # if there is no match continue checking the following numa
1506                         if not sriov_ports_found:
1507                             continue
1508
1509                         if sriov_ports_found and physical_ports_found:
1510                             match_found = True
1511                             break
1512
1513                     if not match_found:
1514                         error_text = 'No room at data center. Cannot find a host with the required hugepages, vcpus ' \
1515                                      'and interfaces'
1516                         # self.logger.debug(error_text)
1517                         return -1, error_text
1518
1519                     # self.logger.debug('Full match found in numa %s', str(numa_id))
1520
1521                 for numa in valid_for_processor:
1522                     if numa_id == numa['numa_id']:
1523                         host_id = numa['host_id']
1524                         break
1525                 return 0, {'numa_id': numa_id, 'host_id': host_id}
1526             except (mdb.Error, AttributeError) as e:
1527                 r, c = self.format_error(e, "get_numas", cmd)
1528                 if r != -HTTP_Request_Timeout or retry_ == 1:
1529                     return r, c
1530
1531     def new_instance(self, instance_dict, nets, ports_to_free):
1532         for retry_ in range(0, 2):
1533             cmd = ""
1534             try:
1535                 with self.lock, self.con:
1536                     self.cur = self.con.cursor()
1537
1538                     # create uuid if not provided
1539                     if 'uuid' not in instance_dict:
1540                         uuid = instance_dict['uuid'] = str(myUuid.uuid1())  # create_uuid
1541                     else:  # check uuid is valid
1542                         uuid = str(instance_dict['uuid'])
1543
1544                     # inserting new uuid
1545                     cmd = "INSERT INTO uuids (uuid, root_uuid, used_at) VALUES ('%s','%s', 'instances')" % (uuid, uuid)
1546                     self.logger.debug(cmd)
1547                     self.cur.execute(cmd)
1548
1549                     # insert in table instance
1550                     extended = instance_dict.pop('extended', None)
1551                     bridgedifaces = instance_dict.pop('bridged-ifaces', ())
1552
1553                     keys = ",".join(instance_dict.keys())
1554                     values = ",".join(
1555                         map(lambda x: "Null" if x is None else "'" + str(x) + "'", instance_dict.values()))
1556                     cmd = "INSERT INTO instances (" + keys + ") VALUES (" + values + ")"
1557                     self.logger.debug(cmd)
1558                     self.cur.execute(cmd)
1559                     # if result != 1: return -1, "Database Error while inserting at instances table"
1560
1561                     # insert resources
1562                     nb_bridge_ifaces = nb_cores = nb_ifaces = nb_numas = 0
1563                     # insert bridged_ifaces
1564
1565                     for iface in bridgedifaces:
1566                         # generate and insert a iface uuid
1567                         if 'enable_dhcp' in iface and iface['enable_dhcp']:
1568                             dhcp_first_ip = iface["dhcp_first_ip"]
1569                             del iface["dhcp_first_ip"]
1570                             dhcp_last_ip = iface["dhcp_last_ip"]
1571                             del iface["dhcp_last_ip"]
1572                             dhcp_cidr = iface["cidr"]
1573                             del iface["cidr"]
1574                             del iface["enable_dhcp"]
1575                             used_dhcp_ips = self._get_dhcp_ip_used_list(iface["net_id"])
1576                             if iface.get("ip_address"):
1577                                 if iface["ip_address"] in used_dhcp_ips:
1578                                     iface["ip_address"] = None
1579                             else:
1580                                 iface["ip_address"] = self.get_free_ip_from_range(dhcp_first_ip, dhcp_last_ip,
1581                                                                                   dhcp_cidr, used_dhcp_ips)
1582                             if 'links' in iface:
1583                                 del iface['links']
1584                             if 'dns' in iface:
1585                                 del iface['dns']
1586                             if 'routes' in iface:
1587                                 del iface['routes']
1588
1589                         iface['uuid'] = str(myUuid.uuid1())  # create_uuid
1590                         cmd = "INSERT INTO uuids (uuid, root_uuid, used_at) VALUES ('%s','%s', 'ports')" % (
1591                             iface['uuid'], uuid)
1592                         self.logger.debug(cmd)
1593                         self.cur.execute(cmd)
1594                         # insert iface
1595                         iface['instance_id'] = uuid
1596                         # iface['type'] = 'instance:bridge'
1597                         if 'name' not in iface:
1598                             iface['name'] = "br" + str(nb_bridge_ifaces)
1599                         iface['Mbps'] = iface.pop('bandwidth', None)
1600                         if 'mac_address' not in iface:
1601                             iface['mac'] = af.gen_random_mac()
1602                         else:
1603                             iface['mac'] = iface['mac_address']
1604                             del iface['mac_address']
1605
1606                         # iface['mac']=iface.pop('mac_address', None)  #for leaving mac generation to libvirt
1607                         keys = ",".join(iface.keys())
1608                         values = ",".join(map(lambda x: "Null" if x is None else "'" + str(x) + "'", iface.values()))
1609                         cmd = "INSERT INTO ports (" + keys + ") VALUES (" + values + ")"
1610                         self.logger.debug(cmd)
1611                         self.cur.execute(cmd)
1612                         nb_bridge_ifaces += 1
1613
1614                     if extended is not None:
1615                         if 'numas' not in extended or extended['numas'] is None:
1616                             extended['numas'] = ()
1617                         for numa in extended['numas']:
1618                             nb_numas += 1
1619                             # cores
1620                             if 'cores' not in numa or numa['cores'] is None:
1621                                 numa['cores'] = ()
1622                             for core in numa['cores']:
1623                                 nb_cores += 1
1624                                 cmd = "UPDATE resources_core SET instance_id='%s'%s%s WHERE id='%s'" \
1625                                       % (uuid,
1626                                          (",v_thread_id='" + str(core['vthread']) + "'") if 'vthread' in core else '',
1627                                          (",paired='" + core['paired'] + "'") if 'paired' in core else '', core['id'])
1628                                 self.logger.debug(cmd)
1629                                 self.cur.execute(cmd)
1630                             # interfaces
1631                             if 'interfaces' not in numa or numa['interfaces'] is None:
1632                                 numa['interfaces'] = ()
1633                             for iface in numa['interfaces']:
1634                                 # generate and insert an uuid; iface[id]=iface_uuid; iface[uuid]= net_id
1635                                 iface['id'] = str(myUuid.uuid1())  # create_uuid
1636                                 cmd = "INSERT INTO uuids (uuid, root_uuid, used_at) VALUES " \
1637                                       "('{}','{}', 'ports')".format(iface['id'], uuid)
1638                                 self.logger.debug(cmd)
1639                                 self.cur.execute(cmd)
1640                                 nb_ifaces += 1
1641                                 mbps_ = ("'" + str(iface['Mbps_used']) + "'") if 'Mbps_used' in iface and \
1642                                                                                  iface['Mbps_used'] is not None \
1643                                     else "Mbps"
1644                                 if iface["dedicated"] == "yes":
1645                                     iface_model = "PF"
1646                                 elif iface["dedicated"] == "yes:sriov":
1647                                     iface_model = "VFnotShared"
1648                                 elif iface["dedicated"] == "no":
1649                                     iface_model = "VF"
1650                                 # else error
1651                                 INSERT = (iface['mac_address'], iface['switch_port'], iface.get('vlan'),
1652                                           'instance:data', iface['Mbps_used'], iface['id'], uuid,
1653                                           instance_dict['tenant_id'], iface.get('name'), iface.get('vpci'),
1654                                           iface.get('uuid'), iface_model)
1655                                 cmd = "INSERT INTO ports (mac,switch_port,vlan,type,Mbps,uuid,instance_id,tenant_id," \
1656                                       "name,vpci,net_id, model)  VALUES (" + \
1657                                       ",".join(map(lambda x: 'Null' if x is None else "'" + str(x) + "'", INSERT)) + ")"
1658                                 self.logger.debug(cmd)
1659                                 self.cur.execute(cmd)
1660                                 if 'uuid' in iface:
1661                                     nets.append(iface['uuid'])
1662
1663                                 # discover if this port is not used by anyone
1664                                 cmd = "SELECT source_name, mac " \
1665                                       "FROM ( SELECT root_id, count(instance_id) as used FROM resources_port" \
1666                                       " WHERE root_id=(SELECT root_id from resources_port WHERE id='%s')" \
1667                                       " GROUP BY root_id ) AS A JOIN resources_port as B ON " \
1668                                       "A.root_id=B.id AND A.used=0" % iface['port_id']
1669                                 self.logger.debug(cmd)
1670                                 self.cur.execute(cmd)
1671                                 ports_to_free += self.cur.fetchall()
1672
1673                                 cmd = "UPDATE resources_port SET instance_id='%s', port_id='%s',Mbps_used=%s " \
1674                                       "WHERE id='%s'" % (uuid, iface['id'], mbps_, iface['port_id'])
1675                                 # if Mbps_used not suply, set the same value of 'Mpbs', that is the total
1676                                 self.logger.debug(cmd)
1677                                 self.cur.execute(cmd)
1678                             # memory
1679                             if 'memory' in numa and numa['memory'] is not None and numa['memory'] > 0:
1680                                 cmd = "INSERT INTO resources_mem (numa_id, instance_id, consumed) VALUES " \
1681                                       "('%s','%s','%s')" % (numa['numa_id'], uuid, numa['memory'])
1682                                 self.logger.debug(cmd)
1683                                 self.cur.execute(cmd)
1684                         if 'devices' not in extended or extended['devices'] is None:
1685                             extended['devices'] = ()
1686                         for device in extended['devices']:
1687                             if 'vpci' in device:
1688                                 vpci = "'" + device['vpci'] + "'"
1689                             else:
1690                                 vpci = 'Null'
1691                             if 'image_id' in device:
1692                                 image_id = "'" + device['image_id'] + "'"
1693                             else:
1694                                 image_id = 'Null'
1695                             if 'xml' in device:
1696                                 xml = "'" + device['xml'] + "'"
1697                             else:
1698                                 xml = 'Null'
1699                             if 'dev' in device:
1700                                 dev = "'" + device['dev'] + "'"
1701                             else:
1702                                 dev = 'Null'
1703                             if 'image_size' in device:
1704                                 size = device['image_size']
1705                             else:
1706                                 size = 0
1707                             cmd = "INSERT INTO instance_devices (type,instance_id,image_id,vpci,xml,dev,image_size) " \
1708                                   "VALUES ('%s','%s', %s, %s, %s, %s, %s)" % \
1709                                   (device['type'], uuid, image_id, vpci, xml, dev, str(size))
1710                             self.logger.debug(cmd)
1711                             self.cur.execute(cmd)
1712                     # #inserting new log
1713                     # cmd = "INSERT INTO logs (related,level,uuid,description) VALUES ('instances','debug','%s',
1714                     # 'new instance: %d numas, %d theads, %d ifaces %d bridge_ifaces')" %
1715                     # (uuid, nb_numas, nb_cores, nb_ifaces, nb_bridge_ifaces)
1716                     # self.logger.debug(cmd)
1717                     # self.cur.execute(cmd)
1718                     #
1719                     # inseted ok
1720                 return 1, uuid
1721             except (mdb.Error, AttributeError) as e:
1722                 r, c = self.format_error(e, "new_instance", cmd)
1723                 if r != -HTTP_Request_Timeout or retry_ == 1:
1724                     return r, c
1725
1726     @staticmethod
1727     def get_free_ip_from_range(first_ip, last_ip, cidr, ip_used_list):
1728         """
1729         Calculate a free IP from a range given
1730         :param first_ip: First dhcp ip range
1731         :param last_ip: Last dhcp ip range
1732         :param cidr: net cidr
1733         :param ip_used_list: contain all used ips to avoid ip collisions
1734         :return:
1735         """
1736         ip_tools = IPNetwork(cidr)
1737         cidr_len = ip_tools.prefixlen
1738         ips = IPNetwork(first_ip + '/' + str(cidr_len))
1739
1740         ip_used_list.append(str(ips[1]))  # gw ip
1741         ip_used_list.append(str(ips[-1]))  # broadcast ip
1742         ip_used_list.append(first_ip)
1743
1744         for vm_ip in ips:
1745             if str(vm_ip) not in ip_used_list and IPAddress(first_ip) <= IPAddress(vm_ip) <= IPAddress(last_ip):
1746                 return vm_ip
1747
1748         return None
1749
1750     def _get_dhcp_ip_used_list(self, net_id):
1751         """
1752         REtreive from DB all ips already used by the dhcp server for a given net
1753         :param net_id:
1754         :return:
1755         """
1756         WHERE = {'type': 'instance:ovs', 'net_id': net_id}
1757         for retry_ in range(0, 2):
1758             cmd = ""
1759             self.cur = self.con.cursor(mdb.cursors.DictCursor)
1760             select_ = "SELECT uuid, ip_address FROM ports "
1761
1762             if WHERE is None or len(WHERE) == 0:
1763                 where_ = ""
1764             else:
1765                 where_ = "WHERE " + " AND ".join(
1766                     map(lambda x: str(x) + (" is Null" if WHERE[x] is None else "='" + str(WHERE[x]) + "'"),
1767                         WHERE.keys()))
1768             limit_ = "LIMIT 100"
1769             cmd = " ".join((select_, where_, limit_))
1770             self.logger.debug(cmd)
1771             self.cur.execute(cmd)
1772             ports = self.cur.fetchall()
1773             ip_address_list = []
1774             for port in ports:
1775                 ip_address_list.append(port['ip_address'])
1776
1777             return ip_address_list
1778
1779     def delete_instance(self, instance_id, tenant_id, net_dataplane_list, ports_to_free, net_ovs_list,
1780                         logcause="requested by http"):
1781         for retry_ in range(0, 2):
1782             cmd = ""
1783             try:
1784                 with self.lock, self.con:
1785                     self.cur = self.con.cursor()
1786                     # get INSTANCE
1787                     cmd = "SELECT uuid FROM instances WHERE uuid='%s' AND tenant_id='%s'" % (instance_id, tenant_id)
1788                     self.logger.debug(cmd)
1789                     self.cur.execute(cmd)
1790                     if self.cur.rowcount == 0:
1791                         return 0, "instance %s not found in tenant %s" % (instance_id, tenant_id)
1792
1793                     # delete bridged ifaces, instace_devices, resources_mem; done by database: it is automatic by
1794                     # Database; FOREIGN KEY DELETE CASCADE
1795
1796                     # get nets afected
1797                     cmd = "SELECT DISTINCT net_id from ports WHERE instance_id = '%s' AND net_id is not Null AND " \
1798                           "type='instance:data'" % instance_id
1799                     self.logger.debug(cmd)
1800                     self.cur.execute(cmd)
1801                     net_list__ = self.cur.fetchall()
1802                     for net in net_list__:
1803                         net_dataplane_list.append(net[0])
1804
1805                     # get ovs manangement nets
1806                     cmd = "SELECT DISTINCT net_id, vlan, ip_address, mac FROM ports WHERE instance_id='{}' AND " \
1807                           "net_id is not Null AND type='instance:ovs'".format(instance_id)
1808                     self.logger.debug(cmd)
1809                     self.cur.execute(cmd)
1810                     net_ovs_list += self.cur.fetchall()
1811
1812                     # get dataplane interfaces releases by this VM; both PF and VF with no other VF
1813                     cmd = "SELECT source_name, mac FROM (SELECT root_id, count(instance_id) as used " \
1814                           "FROM resources_port WHERE instance_id='%s' GROUP BY root_id ) AS A" % instance_id \
1815                           + " JOIN (SELECT root_id, count(instance_id) as used FROM resources_port GROUP BY root_id) " \
1816                             "AS B ON A.root_id=B.root_id AND A.used=B.used JOIN resources_port as C ON A.root_id=C.id"
1817                     # cmd = "SELECT DISTINCT root_id FROM resources_port WHERE instance_id = '%s'" % instance_id
1818                     self.logger.debug(cmd)
1819                     self.cur.execute(cmd)
1820                     ports_to_free += self.cur.fetchall()
1821
1822                     # update resources port
1823                     cmd = "UPDATE resources_port SET instance_id=Null, port_id=Null, Mbps_used='0' " \
1824                           "WHERE instance_id = '%s'" % instance_id
1825                     self.logger.debug(cmd)
1826                     self.cur.execute(cmd)
1827
1828                     # #filter dataplane ports used by this VM that now are free
1829                     # for port in ports_list__:
1830                     #     cmd = "SELECT mac, count(instance_id) FROM resources_port WHERE root_id = '%s'" % port[0]
1831                     #     self.logger.debug(cmd)
1832                     #     self.cur.execute(cmd)
1833                     #     mac_list__ = self.cur.fetchone()
1834                     #     if mac_list__ and mac_list__[1]==0:
1835                     #         ports_to_free.append(mac_list__[0])
1836
1837                     # update resources core
1838                     cmd = "UPDATE resources_core SET instance_id=Null, v_thread_id=Null, paired='N' " \
1839                           "WHERE instance_id = '%s'" % instance_id
1840                     self.logger.debug(cmd)
1841                     self.cur.execute(cmd)
1842
1843                     # delete all related uuids
1844                     cmd = "DELETE FROM uuids WHERE root_uuid='%s'" % instance_id
1845                     self.logger.debug(cmd)
1846                     self.cur.execute(cmd)
1847
1848                     # #insert log
1849                     # cmd = "INSERT INTO logs (related,level,uuid,description) VALUES
1850                     # ('instances','debug','%s','delete instance %s')" % (instance_id, logcause)
1851                     # self.logger.debug(cmd)
1852                     # self.cur.execute(cmd)
1853
1854                     # delete instance
1855                     cmd = "DELETE FROM instances WHERE uuid='%s' AND tenant_id='%s'" % (instance_id, tenant_id)
1856                     self.cur.execute(cmd)
1857                     return 1, "instance %s from tenant %s DELETED" % (instance_id, tenant_id)
1858
1859             except (mdb.Error, AttributeError) as e:
1860                 r, c = self.format_error(e, "delete_instance", cmd)
1861                 if r != -HTTP_Request_Timeout or retry_ == 1:
1862                     return r, c
1863
1864     def get_ports(self, WHERE):
1865         """ Obtain ports using the WHERE filtering.
1866         Attributes:
1867             'where_': dict of key:values, translated to key=value AND ... (Optional)
1868         Return: a list with dictionarys at each row
1869         """
1870         for retry_ in range(0, 2):
1871             cmd = ""
1872             try:
1873                 with self.lock, self.con:
1874
1875                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1876                     select_ = "SELECT uuid,'ACTIVE' as status,admin_state_up,name,net_id,\
1877                         tenant_id,type,mac,vlan,switch_port,instance_id,Mbps FROM ports "
1878
1879                     if WHERE is None or len(WHERE) == 0:
1880                         where_ = ""
1881                     else:
1882                         where_ = "WHERE " + " AND ".join(
1883                             map(lambda x: str(x) + (" is Null" if WHERE[x] is None else "='" + str(WHERE[x]) + "'"),
1884                                 WHERE.keys()))
1885                     limit_ = "LIMIT 100"
1886                     cmd = " ".join((select_, where_, limit_))
1887                     # print "SELECT multiple de instance_ifaces, iface_uuid, external_ports" #print cmd
1888                     self.logger.debug(cmd)
1889                     self.cur.execute(cmd)
1890                     ports = self.cur.fetchall()
1891                     if self.cur.rowcount > 0:
1892                         af.DeleteNone(ports)
1893                     return self.cur.rowcount, ports
1894                     # return self.get_table(FROM=from_, SELECT=select_,WHERE=where_,LIMIT=100)
1895             except (mdb.Error, AttributeError) as e:
1896                 r, c = self.format_error(e, "get_ports", cmd)
1897                 if r != -HTTP_Request_Timeout or retry_ == 1:
1898                     return r, c
1899
1900     def check_target_net(self, net_id, tenant_id, port_type):
1901         """check if valid attachement of a port into a target net
1902         Attributes:
1903             net_id: target net uuid
1904             tenant_id: client where tenant belongs. Not used in this version
1905             port_type: string with the option 'instance:bridge', 'instance:data', 'external'
1906         Return:
1907             (0,net_dict) if ok,   where net_dict contain 'uuid','type','vlan', ...
1908             (negative,string-error) if error
1909         """
1910         for retry_ in range(0, 2):
1911             cmd = ""
1912             try:
1913                 with self.lock, self.con:
1914                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
1915                     cmd = "SELECT * FROM nets WHERE uuid='%s'" % net_id
1916                     self.logger.debug(cmd)
1917                     self.cur.execute(cmd)
1918                     if self.cur.rowcount == 0:
1919                         return -1, "network_id %s does not match any net" % net_id
1920                     net = self.cur.fetchone()
1921                     break
1922
1923             except (mdb.Error, AttributeError) as e:
1924                 r, c = self.format_error(e, "check_target_net", cmd)
1925                 if r != -HTTP_Request_Timeout or retry_ == 1:
1926                     return r, c
1927         # check permissions
1928         if tenant_id is not None and tenant_id is not "admin":
1929             if net['tenant_id'] == tenant_id and net['shared'] == 'false':
1930                 return -1, "needed admin privileges to attach to the net %s" % net_id
1931         # check types
1932         if (net['type'] in ('ptp', 'data') and port_type not in ('instance:data', 'external')) or \
1933                 (net['type'] in ('bridge_data', 'bridge_man') and port_type not in ('instance:bridge', 'instance:ovs')):
1934             return -1, "Cannot attach a port of type %s into a net of type %s" % (port_type, net['type'])
1935         if net['type'] == 'ptp':
1936             # look how many
1937             nb_ports, data = self.get_ports({'net_id': net_id})
1938             if nb_ports < 0:
1939                 return -1, data
1940             else:
1941                 if net['provider']:
1942                     nb_ports += 1
1943                 if nb_ports >= 2:
1944                     return -1, "net of type p2p already contain two ports attached. No room for another"
1945
1946         return 0, net
1947
1948
1949 if __name__ == "__main__":
1950     print("Hello World")