osm_openvim/vim_db.py

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