nfvo_db.py

   1 # -*- coding: utf-8 -*-
   2
   3 ##
   4 # Copyright 2015 Telefónica Investigación y Desarrollo, S.A.U.
   5 # This file is part of openmano
   6 # All Rights Reserved.
   7 #
   8 # Licensed under the Apache License, Version 2.0 (the "License"); you may
   9 # not use this file except in compliance with the License. You may obtain
  10 # a copy of the License at
  11 #
  12 #         http://www.apache.org/licenses/LICENSE-2.0
  13 #
  14 # Unless required by applicable law or agreed to in writing, software
  15 # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
  16 # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  17 # License for the specific language governing permissions and limitations
  18 # under the License.
  19 #
  20 # For those usages not covered by the Apache License, Version 2.0 please
  21 # contact with: nfvlabs@tid.es
  22 ##
  23
  24 '''
  25 NFVO DB engine. It implements all the methods to interact with the Openmano Database
  26 '''
  27 __author__="Alfonso Tierno, Gerardo Garcia, Pablo Montes"
  28 __date__ ="$28-aug-2014 10:05:01$"
  29
  30 import db_base
  31 import MySQLdb as mdb
  32 import json
  33 #import yaml
  34 import time
  35
  36
  37 tables_with_createdat_field=["datacenters","instance_nets","instance_scenarios","instance_vms","instance_vnfs",
  38                            "interfaces","nets","nfvo_tenants","scenarios","sce_interfaces","sce_nets",
  39                            "sce_vnfs","tenants_datacenters","datacenter_tenants","vms","vnfs"]
  40
  41 class nfvo_db(db_base.db_base):
  42     def __init__(self, host=None, user=None, passwd=None, database=None, log_name='openmano.db', log_level="ERROR"):
  43         db_base.db_base.__init__(self, host, user, passwd, database, log_name, log_level)
  44         db_base.db_base.tables_with_created_field=tables_with_createdat_field
  45         return
  46
  47
  48     def new_vnf_as_a_whole(self,nfvo_tenant,vnf_name,vnf_descriptor,VNFCDict):
  49         self.logger.debug("Adding new vnf to the NFVO database")
  50         tries = 2
  51         while tries:
  52             created_time = time.time()
  53             try:
  54                 with self.con:
  55
  56                     myVNFDict = {}
  57                     myVNFDict["name"] = vnf_name
  58                     myVNFDict["descriptor"] = vnf_descriptor['vnf'].get('descriptor')
  59                     myVNFDict["public"] = vnf_descriptor['vnf'].get('public', "false")
  60                     myVNFDict["description"] = vnf_descriptor['vnf']['description']
  61                     myVNFDict["class"] = vnf_descriptor['vnf'].get('class',"MISC")
  62                     myVNFDict["tenant_id"] = vnf_descriptor['vnf'].get("tenant_id")
  63
  64                     vnf_id = self._new_row_internal('vnfs', myVNFDict, add_uuid=True, root_uuid=None, created_time=created_time)
  65                     #print "Adding new vms to the NFVO database"
  66                     #For each vm, we must create the appropriate vm in the NFVO database.
  67                     vmDict = {}
  68                     for _,vm in VNFCDict.iteritems():
  69                         #This code could make the name of the vms grow and grow.
  70                         #If we agree to follow this convention, we should check with a regex that the vnfc name is not including yet the vnf name
  71                         #vm['name'] = "%s-%s" % (vnf_name,vm['name'])
  72                         #print "VM name: %s. Description: %s" % (vm['name'], vm['description'])
  73                         vm["vnf_id"] = vnf_id
  74                         created_time += 0.00001
  75                         vm_id = self._new_row_internal('vms', vm, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
  76                         #print "Internal vm id in NFVO DB: %s" % vm_id
  77                         vmDict[vm['name']] = vm_id
  78
  79                     #Collect the data interfaces of each VM/VNFC under the 'numas' field
  80                     dataifacesDict = {}
  81                     for vm in vnf_descriptor['vnf']['VNFC']:
  82                         dataifacesDict[vm['name']] = {}
  83                         for numa in vm.get('numas', []):
  84                             for dataiface in numa.get('interfaces',[]):
  85                                 db_base._convert_bandwidth(dataiface)
  86                                 dataifacesDict[vm['name']][dataiface['name']] = {}
  87                                 dataifacesDict[vm['name']][dataiface['name']]['vpci'] = dataiface['vpci']
  88                                 dataifacesDict[vm['name']][dataiface['name']]['bw'] = dataiface['bandwidth']
  89                                 dataifacesDict[vm['name']][dataiface['name']]['model'] = "PF" if dataiface['dedicated']=="yes" else ("VF"  if dataiface['dedicated']=="no" else "VFnotShared")
  90
  91                     #Collect the bridge interfaces of each VM/VNFC under the 'bridge-ifaces' field
  92                     bridgeInterfacesDict = {}
  93                     for vm in vnf_descriptor['vnf']['VNFC']:
  94                         if 'bridge-ifaces' in  vm:
  95                             bridgeInterfacesDict[vm['name']] = {}
  96                             for bridgeiface in vm['bridge-ifaces']:
  97                                 db_base._convert_bandwidth(bridgeiface)
  98                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']] = {}
  99                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['vpci'] = bridgeiface.get('vpci',None)
 100                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['mac'] = bridgeiface.get('mac_address',None)
 101                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['bw'] = bridgeiface.get('bandwidth', None)
 102                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['model'] = bridgeiface.get('model', None)
 103
 104                     #For each internal connection, we add it to the interfaceDict and we  create the appropriate net in the NFVO database.
 105                     #print "Adding new nets (VNF internal nets) to the NFVO database (if any)"
 106                     internalconnList = []
 107                     if 'internal-connections' in vnf_descriptor['vnf']:
 108                         for net in vnf_descriptor['vnf']['internal-connections']:
 109                             #print "Net name: %s. Description: %s" % (net['name'], net['description'])
 110
 111                             myNetDict = {}
 112                             myNetDict["name"] = net['name']
 113                             myNetDict["description"] = net['description']
 114                             myNetDict["type"] = net['type']
 115                             myNetDict["vnf_id"] = vnf_id
 116
 117                             created_time += 0.00001
 118                             net_id = self._new_row_internal('nets', myNetDict, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 119
 120                             for element in net['elements']:
 121                                 ifaceItem = {}
 122                                 #ifaceItem["internal_name"] = "%s-%s-%s" % (net['name'],element['VNFC'], element['local_iface_name'])
 123                                 ifaceItem["internal_name"] = element['local_iface_name']
 124                                 #ifaceItem["vm_id"] = vmDict["%s-%s" % (vnf_name,element['VNFC'])]
 125                                 ifaceItem["vm_id"] = vmDict[element['VNFC']]
 126                                 ifaceItem["net_id"] = net_id
 127                                 ifaceItem["type"] = net['type']
 128                                 if ifaceItem ["type"] == "data":
 129                                     ifaceItem["vpci"] =  dataifacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['vpci']
 130                                     ifaceItem["bw"] =    dataifacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['bw']
 131                                     ifaceItem["model"] = dataifacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['model']
 132                                 else:
 133                                     ifaceItem["vpci"] =  bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['vpci']
 134                                     ifaceItem["mac"] =  bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['mac_address']
 135                                     ifaceItem["bw"] =    bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['bw']
 136                                     ifaceItem["model"] = bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]['model']
 137                                 internalconnList.append(ifaceItem)
 138                             #print "Internal net id in NFVO DB: %s" % net_id
 139
 140                     #print "Adding internal interfaces to the NFVO database (if any)"
 141                     for iface in internalconnList:
 142                         print "Iface name: %s" % iface['internal_name']
 143                         created_time += 0.00001
 144                         iface_id = self._new_row_internal('interfaces', iface, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 145                         #print "Iface id in NFVO DB: %s" % iface_id
 146
 147                     #print "Adding external interfaces to the NFVO database"
 148                     for iface in vnf_descriptor['vnf']['external-connections']:
 149                         myIfaceDict = {}
 150                         #myIfaceDict["internal_name"] = "%s-%s-%s" % (vnf_name,iface['VNFC'], iface['local_iface_name'])
 151                         myIfaceDict["internal_name"] = iface['local_iface_name']
 152                         #myIfaceDict["vm_id"] = vmDict["%s-%s" % (vnf_name,iface['VNFC'])]
 153                         myIfaceDict["vm_id"] = vmDict[iface['VNFC']]
 154                         myIfaceDict["external_name"] = iface['name']
 155                         myIfaceDict["type"] = iface['type']
 156                         if iface["type"] == "data":
 157                             myIfaceDict["vpci"]  = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['vpci']
 158                             myIfaceDict["bw"]    = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['bw']
 159                             myIfaceDict["model"] = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['model']
 160                         else:
 161                             myIfaceDict["vpci"]  = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['vpci']
 162                             myIfaceDict["bw"]    = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['bw']
 163                             myIfaceDict["model"] = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['model']
 164                             myIfaceDict["mac"] = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['mac']
 165                         print "Iface name: %s" % iface['name']
 166                         created_time += 0.00001
 167                         iface_id = self._new_row_internal('interfaces', myIfaceDict, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 168                         #print "Iface id in NFVO DB: %s" % iface_id
 169
 170                     return vnf_id
 171
 172             except (mdb.Error, AttributeError) as e:
 173                 self._format_error(e, tries)
 174             tries -= 1
 175
 176
 177     def new_scenario(self, scenario_dict):
 178         tries = 2
 179         while tries:
 180             created_time = time.time()
 181             try:
 182                 with self.con:
 183                     self.cur = self.con.cursor()
 184                     tenant_id = scenario_dict.get('tenant_id')
 185                     #scenario
 186                     INSERT_={'tenant_id': tenant_id,
 187                              'name': scenario_dict['name'],
 188                              'description': scenario_dict['description'],
 189                              'public': scenario_dict.get('public', "false")}
 190
 191                     scenario_uuid =  self._new_row_internal('scenarios', INSERT_, add_uuid=True, root_uuid=None, created_time=created_time)
 192                     #sce_nets
 193                     for net in scenario_dict['nets'].values():
 194                         net_dict={'scenario_id': scenario_uuid}
 195                         net_dict["name"] = net["name"]
 196                         net_dict["type"] = net["type"]
 197                         net_dict["description"] = net.get("description")
 198                         net_dict["external"] = net.get("external", False)
 199                         if "graph" in net:
 200                             #net["graph"]=yaml.safe_dump(net["graph"],default_flow_style=True,width=256)
 201                             #TODO, must be json because of the GUI, change to yaml
 202                             net_dict["graph"]=json.dumps(net["graph"])
 203                         created_time += 0.00001
 204                         net_uuid =  self._new_row_internal('sce_nets', net_dict, add_uuid=True, root_uuid=scenario_uuid, created_time=created_time)
 205                         net['uuid']=net_uuid
 206                     #sce_vnfs
 207                     for k,vnf in scenario_dict['vnfs'].items():
 208                         INSERT_={'scenario_id': scenario_uuid,
 209                                 'name': k,
 210                                 'vnf_id': vnf['uuid'],
 211                                 #'description': scenario_dict['name']
 212                                 'description': vnf['description']
 213                             }
 214                         if "graph" in vnf:
 215                             #INSERT_["graph"]=yaml.safe_dump(vnf["graph"],default_flow_style=True,width=256)
 216                             #TODO, must be json because of the GUI, change to yaml
 217                             INSERT_["graph"]=json.dumps(vnf["graph"])
 218                         created_time += 0.00001
 219                         scn_vnf_uuid =  self._new_row_internal('sce_vnfs', INSERT_, add_uuid=True, root_uuid=scenario_uuid, created_time=created_time)
 220                         vnf['scn_vnf_uuid']=scn_vnf_uuid
 221                         #sce_interfaces
 222                         for iface in vnf['ifaces'].values():
 223                             #print 'iface', iface
 224                             if 'net_key' not in iface:
 225                                 continue
 226                             iface['net_id'] = scenario_dict['nets'][ iface['net_key'] ]['uuid']
 227                             INSERT_={'sce_vnf_id': scn_vnf_uuid,
 228                                 'sce_net_id': iface['net_id'],
 229                                 'interface_id':  iface[ 'uuid' ]
 230                             }
 231                             created_time += 0.00001
 232                             iface_uuid =  self._new_row_internal('sce_interfaces', INSERT_, add_uuid=True, root_uuid=scenario_uuid, created_time=created_time)
 233
 234                     return scenario_uuid
 235
 236             except (mdb.Error, AttributeError) as e:
 237                 self._format_error(e, tries)
 238             tries -= 1
 239
 240     def edit_scenario(self, scenario_dict):
 241         tries = 2
 242         while tries:
 243             modified_time = time.time()
 244             item_changed=0
 245             try:
 246                 with self.con:
 247                     self.cur = self.con.cursor()
 248                     #check that scenario exist
 249                     tenant_id = scenario_dict.get('tenant_id')
 250                     scenario_uuid = scenario_dict['uuid']
 251
 252                     where_text = "uuid='{}'".format(scenario_uuid)
 253                     if not tenant_id and tenant_id != "any":
 254                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 255                     cmd = "SELECT * FROM scenarios WHERE "+ where_text
 256                     self.logger.debug(cmd)
 257                     self.cur.execute(cmd)
 258                     self.cur.fetchall()
 259                     if self.cur.rowcount==0:
 260                         raise db_base.db_base_Exception("No scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 261                     elif self.cur.rowcount>1:
 262                         raise db_base.db_base_Exception("More than one scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 263
 264                     #scenario
 265                     nodes = {}
 266                     topology = scenario_dict.pop("topology", None)
 267                     if topology != None and "nodes" in topology:
 268                         nodes = topology.get("nodes",{})
 269                     UPDATE_ = {}
 270                     if "name" in scenario_dict:        UPDATE_["name"] = scenario_dict["name"]
 271                     if "description" in scenario_dict: UPDATE_["description"] = scenario_dict["description"]
 272                     if len(UPDATE_)>0:
 273                         WHERE_={'tenant_id': tenant_id, 'uuid': scenario_uuid}
 274                         item_changed += self._update_rows('scenarios', UPDATE_, WHERE_, modified_time=modified_time)
 275                     #sce_nets
 276                     for node_id, node in nodes.items():
 277                         if "graph" in node:
 278                             #node["graph"] = yaml.safe_dump(node["graph"],default_flow_style=True,width=256)
 279                             #TODO, must be json because of the GUI, change to yaml
 280                             node["graph"] = json.dumps(node["graph"])
 281                         WHERE_={'scenario_id': scenario_uuid, 'uuid': node_id}
 282                         #Try to change at sce_nets(version 0 API backward compatibility and sce_vnfs)
 283                         item_changed += self._update_rows('sce_nets', node, WHERE_)
 284                         item_changed += self._update_rows('sce_vnfs', node, WHERE_, modified_time=modified_time)
 285                     return item_changed
 286
 287             except (mdb.Error, AttributeError) as e:
 288                 self._format_error(e, tries)
 289             tries -= 1
 290
 291 #     def get_instance_scenario(self, instance_scenario_id, tenant_id=None):
 292 #         '''Obtain the scenario instance information, filtering by one or serveral of the tenant, uuid or name
 293 #         instance_scenario_id is the uuid or the name if it is not a valid uuid format
 294 #         Only one scenario isntance must mutch the filtering or an error is returned
 295 #         '''
 296 #         print "1******************************************************************"
 297 #         try:
 298 #             with self.con:
 299 #                 self.cur = self.con.cursor(mdb.cursors.DictCursor)
 300 #                 #scenario table
 301 #                 where_list=[]
 302 #                 if tenant_id is not None: where_list.append( "tenant_id='" + tenant_id +"'" )
 303 #                 if db_base._check_valid_uuid(instance_scenario_id):
 304 #                     where_list.append( "uuid='" + instance_scenario_id +"'" )
 305 #                 else:
 306 #                     where_list.append( "name='" + instance_scenario_id +"'" )
 307 #                 where_text = " AND ".join(where_list)
 308 #                 self.cur.execute("SELECT * FROM instance_scenarios WHERE "+ where_text)
 309 #                 rows = self.cur.fetchall()
 310 #                 if self.cur.rowcount==0:
 311 #                     return -HTTP_Bad_Request, "No scenario instance found with this criteria " + where_text
 312 #                 elif self.cur.rowcount>1:
 313 #                     return -HTTP_Bad_Request, "More than one scenario instance found with this criteria " + where_text
 314 #                 instance_scenario_dict = rows[0]
 315 #
 316 #                 #instance_vnfs
 317 #                 self.cur.execute("SELECT uuid,vnf_id FROM instance_vnfs WHERE instance_scenario_id='"+ instance_scenario_dict['uuid'] + "'")
 318 #                 instance_scenario_dict['instance_vnfs'] = self.cur.fetchall()
 319 #                 for vnf in instance_scenario_dict['instance_vnfs']:
 320 #                     #instance_vms
 321 #                     self.cur.execute("SELECT uuid, vim_vm_id "+
 322 #                                 "FROM instance_vms  "+
 323 #                                 "WHERE instance_vnf_id='" + vnf['uuid'] +"'"
 324 #                                 )
 325 #                     vnf['instance_vms'] = self.cur.fetchall()
 326 #                 #instance_nets
 327 #                 self.cur.execute("SELECT uuid, vim_net_id FROM instance_nets WHERE instance_scenario_id='"+ instance_scenario_dict['uuid'] + "'")
 328 #                 instance_scenario_dict['instance_nets'] = self.cur.fetchall()
 329 #
 330 #                 #instance_interfaces
 331 #                 self.cur.execute("SELECT uuid, vim_interface_id, instance_vm_id, instance_net_id FROM instance_interfaces WHERE instance_scenario_id='"+ instance_scenario_dict['uuid'] + "'")
 332 #                 instance_scenario_dict['instance_interfaces'] = self.cur.fetchall()
 333 #
 334 #                 db_base._convert_datetime2str(instance_scenario_dict)
 335 #                 db_base._convert_str2boolean(instance_scenario_dict, ('public','shared','external') )
 336 #                 print "2******************************************************************"
 337 #                 return 1, instance_scenario_dict
 338 #         except (mdb.Error, AttributeError) as e:
 339 #             print "nfvo_db.get_instance_scenario DB Exception %d: %s" % (e.args[0], e.args[1])
 340 #             return self._format_error(e)
 341
 342     def get_scenario(self, scenario_id, tenant_id=None, datacenter_id=None):
 343         '''Obtain the scenario information, filtering by one or serveral of the tenant, uuid or name
 344         scenario_id is the uuid or the name if it is not a valid uuid format
 345         if datacenter_id is provided, it supply aditional vim_id fields with the matching vim uuid
 346         Only one scenario must mutch the filtering or an error is returned
 347         '''
 348         tries = 2
 349         while tries:
 350             try:
 351                 with self.con:
 352                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 353                     where_text = "uuid='{}'".format(scenario_id)
 354                     if not tenant_id and tenant_id != "any":
 355                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 356                     cmd = "SELECT * FROM scenarios WHERE " + where_text
 357                     self.logger.debug(cmd)
 358                     self.cur.execute(cmd)
 359                     rows = self.cur.fetchall()
 360                     if self.cur.rowcount==0:
 361                         raise db_base.db_base_Exception("No scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 362                     elif self.cur.rowcount>1:
 363                         raise db_base.db_base_Exception("More than one scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 364                     scenario_dict = rows[0]
 365                     #sce_vnfs
 366                     cmd = "SELECT uuid,name,vnf_id,description FROM sce_vnfs WHERE scenario_id='{}' ORDER BY created_at".format(scenario_dict['uuid'])
 367                     self.logger.debug(cmd)
 368                     self.cur.execute(cmd)
 369                     scenario_dict['vnfs'] = self.cur.fetchall()
 370                     for vnf in scenario_dict['vnfs']:
 371                         #sce_interfaces
 372                         cmd = "SELECT uuid,sce_net_id,interface_id FROM sce_interfaces WHERE sce_vnf_id='{}' ORDER BY created_at".format(vnf['uuid'])
 373                         self.logger.debug(cmd)
 374                         self.cur.execute(cmd)
 375                         vnf['interfaces'] = self.cur.fetchall()
 376                         #vms
 377                         cmd = "SELECT vms.uuid as uuid, flavor_id, image_id, vms.name as name, vms.description as description " \
 378                                 " FROM vnfs join vms on vnfs.uuid=vms.vnf_id " \
 379                                 " WHERE vnfs.uuid='" + vnf['vnf_id'] +"'"  \
 380                                 " ORDER BY vms.created_at"
 381                         self.logger.debug(cmd)
 382                         self.cur.execute(cmd)
 383                         vnf['vms'] = self.cur.fetchall()
 384                         for vm in vnf['vms']:
 385                             if datacenter_id!=None:
 386                                 cmd = "SELECT vim_id FROM datacenters_images WHERE image_id='{}' AND datacenter_id='{}'".format(vm['image_id'],datacenter_id)
 387                                 self.logger.debug(cmd)
 388                                 self.cur.execute(cmd)
 389                                 if self.cur.rowcount==1:
 390                                     vim_image_dict = self.cur.fetchone()
 391                                     vm['vim_image_id']=vim_image_dict['vim_id']
 392                                 cmd = "SELECT vim_id FROM datacenters_flavors WHERE flavor_id='{}' AND datacenter_id='{}'".format(vm['flavor_id'],datacenter_id)
 393                                 self.logger.debug(cmd)
 394                                 self.cur.execute(cmd)
 395                                 if self.cur.rowcount==1:
 396                                     vim_flavor_dict = self.cur.fetchone()
 397                                     vm['vim_flavor_id']=vim_flavor_dict['vim_id']
 398
 399                             #interfaces
 400                             cmd = "SELECT uuid,internal_name,external_name,net_id,type,vpci,mac,bw,model" \
 401                                     " FROM interfaces" \
 402                                     " WHERE vm_id='{}'" \
 403                                     " ORDER BY created_at".format(vm['uuid'])
 404                             self.logger.debug(cmd)
 405                             self.cur.execute(cmd)
 406                             vm['interfaces'] = self.cur.fetchall()
 407                         #nets    every net of a vms
 408                         cmd = "SELECT uuid,name,type,description FROM nets WHERE vnf_id='{}'".format(vnf['vnf_id'])
 409                         self.logger.debug(cmd)
 410                         self.cur.execute(cmd)
 411                         vnf['nets'] = self.cur.fetchall()
 412                     #sce_nets
 413                     cmd = "SELECT uuid,name,type,external,description" \
 414                           " FROM sce_nets  WHERE scenario_id='{}'" \
 415                           " ORDER BY created_at ".format(scenario_dict['uuid'])
 416                     self.logger.debug(cmd)
 417                     self.cur.execute(cmd)
 418                     scenario_dict['nets'] = self.cur.fetchall()
 419                     #datacenter_nets
 420                     for net in scenario_dict['nets']:
 421                         if str(net['external']) == 'false':
 422                             continue
 423                         WHERE_=" WHERE name='{}'".format(net['name'])
 424                         if datacenter_id!=None:
 425                             WHERE_ += " AND datacenter_id='{}'".format(datacenter_id)
 426                         cmd = "SELECT vim_net_id FROM datacenter_nets" + WHERE_
 427                         self.logger.debug(cmd)
 428                         self.cur.execute(cmd)
 429                         d_net = self.cur.fetchone()
 430                         if d_net==None or datacenter_id==None:
 431                             #print "nfvo_db.get_scenario() WARNING external net %s not found"  % net['name']
 432                             net['vim_id']=None
 433                         else:
 434                             net['vim_id']=d_net['vim_net_id']
 435
 436                     db_base._convert_datetime2str(scenario_dict)
 437                     db_base._convert_str2boolean(scenario_dict, ('public','shared','external') )
 438                     return scenario_dict
 439             except (mdb.Error, AttributeError) as e:
 440                 self._format_error(e, tries)
 441             tries -= 1
 442
 443
 444     def delete_scenario(self, scenario_id, tenant_id=None):
 445         '''Deletes a scenario, filtering by one or several of the tenant, uuid or name
 446         scenario_id is the uuid or the name if it is not a valid uuid format
 447         Only one scenario must mutch the filtering or an error is returned
 448         '''
 449         tries = 2
 450         while tries:
 451             try:
 452                 with self.con:
 453                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 454
 455                     #scenario table
 456                     where_text = "uuid='{}'".format(scenario_id)
 457                     if not tenant_id and tenant_id != "any":
 458                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 459                     cmd = "SELECT * FROM scenarios WHERE "+ where_text
 460                     self.logger.debug(cmd)
 461                     self.cur.execute(cmd)
 462                     rows = self.cur.fetchall()
 463                     if self.cur.rowcount==0:
 464                         raise db_base.db_base_Exception("No scenario found where " + where_text, db_base.HTTP_Bad_Request)
 465                     elif self.cur.rowcount>1:
 466                         raise db_base.db_base_Exception("More than one scenario found where " + where_text, db_base.HTTP_Bad_Request)
 467                     scenario_uuid = rows[0]["uuid"]
 468                     scenario_name = rows[0]["name"]
 469
 470                     #sce_vnfs
 471                     cmd = "DELETE FROM scenarios WHERE uuid='{}'".format(scenario_uuid)
 472                     self.logger.debug(cmd)
 473                     self.cur.execute(cmd)
 474
 475                     return scenario_uuid + " " + scenario_name
 476             except (mdb.Error, AttributeError) as e:
 477                 self._format_error(e, tries, "delete", "instances running")
 478             tries -= 1
 479
 480     def new_instance_scenario_as_a_whole(self,tenant_id,instance_scenario_name,instance_scenario_description,scenarioDict):
 481         tries = 2
 482         while tries:
 483             created_time = time.time()
 484             try:
 485                 with self.con:
 486                     self.cur = self.con.cursor()
 487                     #instance_scenarios
 488                     datacenter_tenant_id = scenarioDict['datacenter_tenant_id']
 489                     datacenter_id = scenarioDict['datacenter_id']
 490                     INSERT_={'tenant_id': tenant_id,
 491                         'datacenter_tenant_id': datacenter_tenant_id,
 492                         'name': instance_scenario_name,
 493                         'description': instance_scenario_description,
 494                         'scenario_id' : scenarioDict['uuid'],
 495                         'datacenter_id': datacenter_id
 496                     }
 497                     instance_uuid = self._new_row_internal('instance_scenarios', INSERT_, add_uuid=True, root_uuid=None, created_time=created_time)
 498
 499                     net_scene2instance={}
 500                     #instance_nets   #nets interVNF
 501                     for net in scenarioDict['nets']:
 502                         INSERT_={'vim_net_id': net['vim_id'], 'external': net['external'], 'instance_scenario_id':instance_uuid } #,  'type': net['type']
 503                         INSERT_['datacenter_id'] = net.get('datacenter_id', datacenter_id)
 504                         INSERT_['datacenter_tenant_id'] = net.get('datacenter_tenant_id', datacenter_tenant_id)
 505                         if net.get("uuid"):
 506                             INSERT_['sce_net_id'] = net['uuid']
 507                         created_time += 0.00001
 508                         instance_net_uuid =  self._new_row_internal('instance_nets', INSERT_, True, instance_uuid, created_time)
 509                         net_scene2instance[ net['uuid'] ] = instance_net_uuid
 510                         net['uuid'] = instance_net_uuid  #overwrite scnario uuid by instance uuid
 511
 512                     #instance_vnfs
 513                     for vnf in scenarioDict['vnfs']:
 514                         INSERT_={'instance_scenario_id': instance_uuid,  'vnf_id': vnf['vnf_id']  }
 515                         INSERT_['datacenter_id'] = vnf.get('datacenter_id', datacenter_id)
 516                         INSERT_['datacenter_tenant_id'] = vnf.get('datacenter_tenant_id', datacenter_tenant_id)
 517                         if vnf.get("uuid"):
 518                             INSERT_['sce_vnf_id'] = vnf['uuid']
 519                         created_time += 0.00001
 520                         instance_vnf_uuid =  self._new_row_internal('instance_vnfs', INSERT_, True, instance_uuid, created_time)
 521                         vnf['uuid'] = instance_vnf_uuid  #overwrite scnario uuid by instance uuid
 522
 523                         #instance_nets   #nets intraVNF
 524                         for net in vnf['nets']:
 525                             INSERT_={'vim_net_id': net['vim_id'], 'external': 'false', 'instance_scenario_id':instance_uuid  } #,  'type': net['type']
 526                             INSERT_['datacenter_id'] = net.get('datacenter_id', datacenter_id)
 527                             INSERT_['datacenter_tenant_id'] = net.get('datacenter_tenant_id', datacenter_tenant_id)
 528                             if net.get("uuid"):
 529                                 INSERT_['net_id'] = net['uuid']
 530                             created_time += 0.00001
 531                             instance_net_uuid =  self._new_row_internal('instance_nets', INSERT_, True, instance_uuid, created_time)
 532                             net_scene2instance[ net['uuid'] ] = instance_net_uuid
 533                             net['uuid'] = instance_net_uuid  #overwrite scnario uuid by instance uuid
 534
 535                         #instance_vms
 536                         for vm in vnf['vms']:
 537                             INSERT_={'instance_vnf_id': instance_vnf_uuid,  'vm_id': vm['uuid'], 'vim_vm_id': vm['vim_id']  }
 538                             created_time += 0.00001
 539                             instance_vm_uuid =  self._new_row_internal('instance_vms', INSERT_, True, instance_uuid, created_time)
 540                             vm['uuid'] = instance_vm_uuid  #overwrite scnario uuid by instance uuid
 541
 542                             #instance_interfaces
 543                             for interface in vm['interfaces']:
 544                                 net_id = interface.get('net_id', None)
 545                                 if net_id is None:
 546                                     #check if is connected to a inter VNFs net
 547                                     for iface in vnf['interfaces']:
 548                                         if iface['interface_id'] == interface['uuid']:
 549                                             net_id = iface.get('sce_net_id', None)
 550                                             break
 551                                 if net_id is None:
 552                                     continue
 553                                 interface_type='external' if interface['external_name'] is not None else 'internal'
 554                                 INSERT_={'instance_vm_id': instance_vm_uuid,  'instance_net_id': net_scene2instance[net_id],
 555                                     'interface_id': interface['uuid'], 'vim_interface_id': interface.get('vim_id'), 'type':  interface_type  }
 556                                 #created_time += 0.00001
 557                                 interface_uuid =  self._new_row_internal('instance_interfaces', INSERT_, True, instance_uuid) #, created_time)
 558                                 interface['uuid'] = interface_uuid  #overwrite scnario uuid by instance uuid
 559                 return instance_uuid
 560             except (mdb.Error, AttributeError) as e:
 561                 self._format_error(e, tries)
 562             tries -= 1
 563
 564     def get_instance_scenario(self, instance_id, tenant_id=None, verbose=False):
 565         '''Obtain the instance information, filtering by one or several of the tenant, uuid or name
 566         instance_id is the uuid or the name if it is not a valid uuid format
 567         Only one instance must mutch the filtering or an error is returned
 568         '''
 569         tries = 2
 570         while tries:
 571             try:
 572                 with self.con:
 573                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 574                     #instance table
 575                     where_list=[]
 576                     if tenant_id is not None: where_list.append( "inst.tenant_id='" + tenant_id +"'" )
 577                     if db_base._check_valid_uuid(instance_id):
 578                         where_list.append( "inst.uuid='" + instance_id +"'" )
 579                     else:
 580                         where_list.append( "inst.name='" + instance_id +"'" )
 581                     where_text = " AND ".join(where_list)
 582                     cmd = "SELECT inst.uuid as uuid,inst.name as name,inst.scenario_id as scenario_id, datacenter_id" +\
 583                                 " ,datacenter_tenant_id, s.name as scenario_name,inst.tenant_id as tenant_id" + \
 584                                 " ,inst.description as description,inst.created_at as created_at" +\
 585                             " FROM instance_scenarios as inst join scenarios as s on inst.scenario_id=s.uuid"+\
 586                             " WHERE " + where_text
 587                     self.logger.debug(cmd)
 588                     self.cur.execute(cmd)
 589                     rows = self.cur.fetchall()
 590
 591                     if self.cur.rowcount==0:
 592                         raise db_base.db_base_Exception("No instance found where " + where_text, db_base.HTTP_Not_Found)
 593                     elif self.cur.rowcount>1:
 594                         raise db_base.db_base_Exception("More than one instance found where " + where_text, db_base.HTTP_Bad_Request)
 595                     instance_dict = rows[0]
 596
 597                     #instance_vnfs
 598                     cmd = "SELECT iv.uuid as uuid,sv.vnf_id as vnf_id,sv.name as vnf_name, sce_vnf_id, datacenter_id, datacenter_tenant_id"\
 599                             " FROM instance_vnfs as iv join sce_vnfs as sv on iv.sce_vnf_id=sv.uuid" \
 600                             " WHERE iv.instance_scenario_id='{}'" \
 601                             " ORDER BY iv.created_at ".format(instance_dict['uuid'])
 602                     self.logger.debug(cmd)
 603                     self.cur.execute(cmd)
 604                     instance_dict['vnfs'] = self.cur.fetchall()
 605                     for vnf in instance_dict['vnfs']:
 606                         vnf_manage_iface_list=[]
 607                         #instance vms
 608                         cmd = "SELECT iv.uuid as uuid, vim_vm_id, status, error_msg, vim_info, iv.created_at as created_at, name "\
 609                                 " FROM instance_vms as iv join vms on iv.vm_id=vms.uuid "\
 610                                 " WHERE instance_vnf_id='{}' ORDER BY iv.created_at".format(vnf['uuid'])
 611                         self.logger.debug(cmd)
 612                         self.cur.execute(cmd)
 613                         vnf['vms'] = self.cur.fetchall()
 614                         for vm in vnf['vms']:
 615                             vm_manage_iface_list=[]
 616                             #instance_interfaces
 617                             cmd = "SELECT vim_interface_id, instance_net_id, internal_name,external_name, mac_address, ip_address, vim_info, i.type as type "\
 618                                     " FROM instance_interfaces as ii join interfaces as i on ii.interface_id=i.uuid "\
 619                                     " WHERE instance_vm_id='{}' ORDER BY created_at".format(vm['uuid'])
 620                             self.logger.debug(cmd)
 621                             self.cur.execute(cmd )
 622                             vm['interfaces'] = self.cur.fetchall()
 623                             for iface in vm['interfaces']:
 624                                 if iface["type"] == "mgmt" and iface["ip_address"]:
 625                                     vnf_manage_iface_list.append(iface["ip_address"])
 626                                     vm_manage_iface_list.append(iface["ip_address"])
 627                                 if not verbose:
 628                                     del iface["type"]
 629                             if vm_manage_iface_list: vm["ip_address"] = ",".join(vm_manage_iface_list)
 630                         if vnf_manage_iface_list: vnf["ip_address"] = ",".join(vnf_manage_iface_list)
 631
 632                     #instance_nets
 633                     #select_text = "instance_nets.uuid as uuid,sce_nets.name as net_name,instance_nets.vim_net_id as net_id,instance_nets.status as status,instance_nets.external as external"
 634                     #from_text = "instance_nets join instance_scenarios on instance_nets.instance_scenario_id=instance_scenarios.uuid " + \
 635                     #            "join sce_nets on instance_scenarios.scenario_id=sce_nets.scenario_id"
 636                     #where_text = "instance_nets.instance_scenario_id='"+ instance_dict['uuid'] + "'"
 637                     cmd = "SELECT uuid,vim_net_id,status,error_msg,vim_info,external, sce_net_id, net_id as vnf_net_id, datacenter_id, datacenter_tenant_id"\
 638                             " FROM instance_nets" \
 639                             " WHERE instance_scenario_id='{}' ORDER BY created_at".format(instance_dict['uuid'])
 640                     self.logger.debug(cmd)
 641                     self.cur.execute(cmd)
 642                     instance_dict['nets'] = self.cur.fetchall()
 643
 644                     db_base._convert_datetime2str(instance_dict)
 645                     db_base._convert_str2boolean(instance_dict, ('public','shared','external') )
 646                     return instance_dict
 647             except (mdb.Error, AttributeError) as e:
 648                 self._format_error(e, tries)
 649             tries -= 1
 650
 651     def delete_instance_scenario(self, instance_id, tenant_id=None):
 652         '''Deletes a instance_Scenario, filtering by one or serveral of the tenant, uuid or name
 653         instance_id is the uuid or the name if it is not a valid uuid format
 654         Only one instance_scenario must mutch the filtering or an error is returned
 655         '''
 656         tries = 2
 657         while tries:
 658             try:
 659                 with self.con:
 660                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 661
 662                     #instance table
 663                     where_list=[]
 664                     if tenant_id is not None: where_list.append( "tenant_id='" + tenant_id +"'" )
 665                     if db_base._check_valid_uuid(instance_id):
 666                         where_list.append( "uuid='" + instance_id +"'" )
 667                     else:
 668                         where_list.append( "name='" + instance_id +"'" )
 669                     where_text = " AND ".join(where_list)
 670                     cmd = "SELECT * FROM instance_scenarios WHERE "+ where_text
 671                     self.logger.debug(cmd)
 672                     self.cur.execute(cmd)
 673                     rows = self.cur.fetchall()
 674
 675                     if self.cur.rowcount==0:
 676                         raise db_base.db_base_Exception("No instance found where " + where_text, db_base.HTTP_Bad_Request)
 677                     elif self.cur.rowcount>1:
 678                         raise db_base.db_base_Exception("More than one instance found where " + where_text, db_base.HTTP_Bad_Request)
 679                     instance_uuid = rows[0]["uuid"]
 680                     instance_name = rows[0]["name"]
 681
 682                     #sce_vnfs
 683                     cmd = "DELETE FROM instance_scenarios WHERE uuid='{}'".format(instance_uuid)
 684                     self.logger.debug(cmd)
 685                     self.cur.execute(cmd)
 686
 687                     return instance_uuid + " " + instance_name
 688             except (mdb.Error, AttributeError) as e:
 689                 self._format_error(e, tries, "delete", "No dependences can avoid deleting!!!!")
 690             tries -= 1
 691
 692     def new_instance_scenario(self, instance_scenario_dict, tenant_id):
 693         #return self.new_row('vnfs', vnf_dict, None, tenant_id, True, True)
 694         return self._new_row_internal('instance_scenarios', instance_scenario_dict, tenant_id, add_uuid=True, root_uuid=None, log=True)
 695
 696     def update_instance_scenario(self, instance_scenario_dict):
 697         #TODO:
 698         return
 699
 700     def new_instance_vnf(self, instance_vnf_dict, tenant_id, instance_scenario_id = None):
 701         #return self.new_row('vms', vm_dict, tenant_id, True, True)
 702         return self._new_row_internal('instance_vnfs', instance_vnf_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
 703
 704     def update_instance_vnf(self, instance_vnf_dict):
 705         #TODO:
 706         return
 707
 708     def delete_instance_vnf(self, instance_vnf_id):
 709         #TODO:
 710         return
 711
 712     def new_instance_vm(self, instance_vm_dict, tenant_id, instance_scenario_id = None):
 713         #return self.new_row('vms', vm_dict, tenant_id, True, True)
 714         return self._new_row_internal('instance_vms', instance_vm_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
 715
 716     def update_instance_vm(self, instance_vm_dict):
 717         #TODO:
 718         return
 719
 720     def delete_instance_vm(self, instance_vm_id):
 721         #TODO:
 722         return
 723
 724     def new_instance_net(self, instance_net_dict, tenant_id, instance_scenario_id = None):
 725         return self._new_row_internal('instance_nets', instance_net_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
 726
 727     def update_instance_net(self, instance_net_dict):
 728         #TODO:
 729         return
 730
 731     def delete_instance_net(self, instance_net_id):
 732         #TODO:
 733         return
 734
 735     def new_instance_interface(self, instance_interface_dict, tenant_id, instance_scenario_id = None):
 736         return self._new_row_internal('instance_interfaces', instance_interface_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
 737
 738     def update_instance_interface(self, instance_interface_dict):
 739         #TODO:
 740         return
 741
 742     def delete_instance_interface(self, instance_interface_dict):
 743         #TODO:
 744         return
 745
 746     def update_datacenter_nets(self, datacenter_id, new_net_list=[]):
 747         ''' Removes the old and adds the new net list at datacenter list for one datacenter.
 748         Attribute
 749             datacenter_id: uuid of the datacenter to act upon
 750             table: table where to insert
 751             new_net_list: the new values to be inserted. If empty it only deletes the existing nets
 752         Return: (Inserted items, Deleted items) if OK, (-Error, text) if error
 753         '''
 754         tries = 2
 755         while tries:
 756             created_time = time.time()
 757             try:
 758                 with self.con:
 759                     self.cur = self.con.cursor()
 760                     cmd="DELETE FROM datacenter_nets WHERE datacenter_id='{}'".format(datacenter_id)
 761                     self.logger.debug(cmd)
 762                     self.cur.execute(cmd)
 763                     deleted = self.cur.rowcount
 764                     inserted = 0
 765                     for new_net in new_net_list:
 766                         created_time += 0.00001
 767                         self._new_row_internal('datacenter_nets', new_net, add_uuid=True, created_time=created_time)
 768                         inserted += 1
 769                     return inserted, deleted
 770             except (mdb.Error, AttributeError) as e:
 771                 self._format_error(e, tries)
 772             tries -= 1
 773
 774