osm_ro/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 #import sys, os
  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", "datacenter_nets",
  40                            "instance_actions", "vim_actions"]
  41
  42
  43 class nfvo_db(db_base.db_base):
  44     def __init__(self, host=None, user=None, passwd=None, database=None, log_name='openmano.db', log_level=None):
  45         db_base.db_base.__init__(self, host, user, passwd, database, log_name, log_level)
  46         db_base.db_base.tables_with_created_field=tables_with_createdat_field
  47         return
  48
  49     def new_vnf_as_a_whole(self,nfvo_tenant,vnf_name,vnf_descriptor,VNFCDict):
  50         self.logger.debug("Adding new vnf to the NFVO database")
  51         tries = 2
  52         while tries:
  53             created_time = time.time()
  54             try:
  55                 with self.con:
  56
  57                     myVNFDict = {}
  58                     myVNFDict["name"] = vnf_name
  59                     myVNFDict["descriptor"] = vnf_descriptor['vnf'].get('descriptor')
  60                     myVNFDict["public"] = vnf_descriptor['vnf'].get('public', "false")
  61                     myVNFDict["description"] = vnf_descriptor['vnf']['description']
  62                     myVNFDict["class"] = vnf_descriptor['vnf'].get('class',"MISC")
  63                     myVNFDict["tenant_id"] = vnf_descriptor['vnf'].get("tenant_id")
  64
  65                     vnf_id = self._new_row_internal('vnfs', myVNFDict, add_uuid=True, root_uuid=None, created_time=created_time)
  66                     #print "Adding new vms to the NFVO database"
  67                     #For each vm, we must create the appropriate vm in the NFVO database.
  68                     vmDict = {}
  69                     for _,vm in VNFCDict.iteritems():
  70                         #This code could make the name of the vms grow and grow.
  71                         #If we agree to follow this convention, we should check with a regex that the vnfc name is not including yet the vnf name
  72                         #vm['name'] = "%s-%s" % (vnf_name,vm['name'])
  73                         #print "VM name: %s. Description: %s" % (vm['name'], vm['description'])
  74                         vm["vnf_id"] = vnf_id
  75                         created_time += 0.00001
  76                         vm_id = self._new_row_internal('vms', vm, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
  77                         #print "Internal vm id in NFVO DB: %s" % vm_id
  78                         vmDict[vm['name']] = vm_id
  79
  80                     #Collect the bridge interfaces of each VM/VNFC under the 'bridge-ifaces' field
  81                     bridgeInterfacesDict = {}
  82                     for vm in vnf_descriptor['vnf']['VNFC']:
  83                         if 'bridge-ifaces' in  vm:
  84                             bridgeInterfacesDict[vm['name']] = {}
  85                             for bridgeiface in vm['bridge-ifaces']:
  86                                 created_time += 0.00001
  87                                 if 'port-security' in bridgeiface:
  88                                     bridgeiface['port_security'] = bridgeiface.pop('port-security')
  89                                 if 'floating-ip' in bridgeiface:
  90                                     bridgeiface['floating_ip'] = bridgeiface.pop('floating-ip')
  91                                 db_base._convert_bandwidth(bridgeiface, logger=self.logger)
  92                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']] = {}
  93                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['vpci'] = bridgeiface.get('vpci',None)
  94                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['mac'] = bridgeiface.get('mac_address',None)
  95                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['bw'] = bridgeiface.get('bandwidth', None)
  96                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['model'] = bridgeiface.get('model', None)
  97                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['port_security'] = \
  98                                     int(bridgeiface.get('port_security', True))
  99                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['floating_ip'] = \
 100                                     int(bridgeiface.get('floating_ip', False))
 101                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']]['created_time'] = created_time
 102
 103                     # Collect the data interfaces of each VM/VNFC under the 'numas' field
 104                     dataifacesDict = {}
 105                     for vm in vnf_descriptor['vnf']['VNFC']:
 106                         dataifacesDict[vm['name']] = {}
 107                         for numa in vm.get('numas', []):
 108                             for dataiface in numa.get('interfaces', []):
 109                                 created_time += 0.00001
 110                                 db_base._convert_bandwidth(dataiface, logger=self.logger)
 111                                 dataifacesDict[vm['name']][dataiface['name']] = {}
 112                                 dataifacesDict[vm['name']][dataiface['name']]['vpci'] = dataiface.get('vpci')
 113                                 dataifacesDict[vm['name']][dataiface['name']]['bw'] = dataiface['bandwidth']
 114                                 dataifacesDict[vm['name']][dataiface['name']]['model'] = "PF" if dataiface[
 115                                                                                                      'dedicated'] == "yes" else (
 116                                 "VF" if dataiface['dedicated'] == "no" else "VFnotShared")
 117                                 dataifacesDict[vm['name']][dataiface['name']]['created_time'] = created_time
 118
 119                     #For each internal connection, we add it to the interfaceDict and we  create the appropriate net in the NFVO database.
 120                     #print "Adding new nets (VNF internal nets) to the NFVO database (if any)"
 121                     internalconnList = []
 122                     if 'internal-connections' in vnf_descriptor['vnf']:
 123                         for net in vnf_descriptor['vnf']['internal-connections']:
 124                             #print "Net name: %s. Description: %s" % (net['name'], net['description'])
 125
 126                             myNetDict = {}
 127                             myNetDict["name"] = net['name']
 128                             myNetDict["description"] = net['description']
 129                             myNetDict["type"] = net['type']
 130                             myNetDict["vnf_id"] = vnf_id
 131
 132                             created_time += 0.00001
 133                             net_id = self._new_row_internal('nets', myNetDict, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 134
 135                             for element in net['elements']:
 136                                 ifaceItem = {}
 137                                 #ifaceItem["internal_name"] = "%s-%s-%s" % (net['name'],element['VNFC'], element['local_iface_name'])
 138                                 ifaceItem["internal_name"] = element['local_iface_name']
 139                                 #ifaceItem["vm_id"] = vmDict["%s-%s" % (vnf_name,element['VNFC'])]
 140                                 ifaceItem["vm_id"] = vmDict[element['VNFC']]
 141                                 ifaceItem["net_id"] = net_id
 142                                 ifaceItem["type"] = net['type']
 143                                 if ifaceItem ["type"] == "data":
 144                                     dataiface = dataifacesDict[ element['VNFC'] ][ element['local_iface_name'] ]
 145                                     ifaceItem["vpci"] =  dataiface['vpci']
 146                                     ifaceItem["bw"] =    dataiface['bw']
 147                                     ifaceItem["model"] = dataiface['model']
 148                                     created_time_iface = dataiface['created_time']
 149                                 else:
 150                                     bridgeiface =  bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]
 151                                     ifaceItem["vpci"]          = bridgeiface['vpci']
 152                                     ifaceItem["mac"]           = bridgeiface['mac']
 153                                     ifaceItem["bw"]            = bridgeiface['bw']
 154                                     ifaceItem["model"]         = bridgeiface['model']
 155                                     ifaceItem["port_security"] = bridgeiface['port_security']
 156                                     ifaceItem["floating_ip"]   = bridgeiface['floating_ip']
 157                                     created_time_iface = bridgeiface['created_time']
 158                                 internalconnList.append(ifaceItem)
 159                             #print "Internal net id in NFVO DB: %s" % net_id
 160
 161                     #print "Adding internal interfaces to the NFVO database (if any)"
 162                     for iface in internalconnList:
 163                         #print "Iface name: %s" % iface['internal_name']
 164                         iface_id = self._new_row_internal('interfaces', iface, add_uuid=True, root_uuid=vnf_id, created_time = created_time_iface)
 165                         #print "Iface id in NFVO DB: %s" % iface_id
 166
 167                     #print "Adding external interfaces to the NFVO database"
 168                     for iface in vnf_descriptor['vnf']['external-connections']:
 169                         myIfaceDict = {}
 170                         #myIfaceDict["internal_name"] = "%s-%s-%s" % (vnf_name,iface['VNFC'], iface['local_iface_name'])
 171                         myIfaceDict["internal_name"] = iface['local_iface_name']
 172                         #myIfaceDict["vm_id"] = vmDict["%s-%s" % (vnf_name,iface['VNFC'])]
 173                         myIfaceDict["vm_id"] = vmDict[iface['VNFC']]
 174                         myIfaceDict["external_name"] = iface['name']
 175                         myIfaceDict["type"] = iface['type']
 176                         if iface["type"] == "data":
 177                             dataiface = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]
 178                             myIfaceDict["vpci"]         = dataiface['vpci']
 179                             myIfaceDict["bw"]           = dataiface['bw']
 180                             myIfaceDict["model"]        = dataiface['model']
 181                             created_time_iface = dataiface['created_time']
 182                         else:
 183                             bridgeiface = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]
 184                             myIfaceDict["vpci"]         = bridgeiface['vpci']
 185                             myIfaceDict["bw"]           = bridgeiface['bw']
 186                             myIfaceDict["model"]        = bridgeiface['model']
 187                             myIfaceDict["mac"]          = bridgeiface['mac']
 188                             myIfaceDict["port_security"]= bridgeiface['port_security']
 189                             myIfaceDict["floating_ip"]  = bridgeiface['floating_ip']
 190                             created_time_iface = bridgeiface['created_time']
 191                         #print "Iface name: %s" % iface['name']
 192                         iface_id = self._new_row_internal('interfaces', myIfaceDict, add_uuid=True, root_uuid=vnf_id, created_time = created_time_iface)
 193                         #print "Iface id in NFVO DB: %s" % iface_id
 194
 195                     return vnf_id
 196
 197             except (mdb.Error, AttributeError) as e:
 198                 self._format_error(e, tries)
 199             tries -= 1
 200
 201     def new_vnf_as_a_whole2(self,nfvo_tenant,vnf_name,vnf_descriptor,VNFCDict):
 202         self.logger.debug("Adding new vnf to the NFVO database")
 203         tries = 2
 204         while tries:
 205             created_time = time.time()
 206             try:
 207                 with self.con:
 208
 209                     myVNFDict = {}
 210                     myVNFDict["name"] = vnf_name
 211                     myVNFDict["descriptor"] = vnf_descriptor['vnf'].get('descriptor')
 212                     myVNFDict["public"] = vnf_descriptor['vnf'].get('public', "false")
 213                     myVNFDict["description"] = vnf_descriptor['vnf']['description']
 214                     myVNFDict["class"] = vnf_descriptor['vnf'].get('class',"MISC")
 215                     myVNFDict["tenant_id"] = vnf_descriptor['vnf'].get("tenant_id")
 216
 217                     vnf_id = self._new_row_internal('vnfs', myVNFDict, add_uuid=True, root_uuid=None, created_time=created_time)
 218                     #print "Adding new vms to the NFVO database"
 219                     #For each vm, we must create the appropriate vm in the NFVO database.
 220                     vmDict = {}
 221                     for _,vm in VNFCDict.iteritems():
 222                         #This code could make the name of the vms grow and grow.
 223                         #If we agree to follow this convention, we should check with a regex that the vnfc name is not including yet the vnf name
 224                         #vm['name'] = "%s-%s" % (vnf_name,vm['name'])
 225                         #print "VM name: %s. Description: %s" % (vm['name'], vm['description'])
 226                         vm["vnf_id"] = vnf_id
 227                         created_time += 0.00001
 228                         vm_id = self._new_row_internal('vms', vm, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 229                         #print "Internal vm id in NFVO DB: %s" % vm_id
 230                         vmDict[vm['name']] = vm_id
 231
 232                     #Collect the bridge interfaces of each VM/VNFC under the 'bridge-ifaces' field
 233                     bridgeInterfacesDict = {}
 234                     for vm in vnf_descriptor['vnf']['VNFC']:
 235                         if 'bridge-ifaces' in  vm:
 236                             bridgeInterfacesDict[vm['name']] = {}
 237                             for bridgeiface in vm['bridge-ifaces']:
 238                                 created_time += 0.00001
 239                                 db_base._convert_bandwidth(bridgeiface, logger=self.logger)
 240                                 if 'port-security' in bridgeiface:
 241                                     bridgeiface['port_security'] = bridgeiface.pop('port-security')
 242                                 if 'floating-ip' in bridgeiface:
 243                                     bridgeiface['floating_ip'] = bridgeiface.pop('floating-ip')
 244                                 ifaceDict = {}
 245                                 ifaceDict['vpci'] = bridgeiface.get('vpci',None)
 246                                 ifaceDict['mac'] = bridgeiface.get('mac_address',None)
 247                                 ifaceDict['bw'] = bridgeiface.get('bandwidth', None)
 248                                 ifaceDict['model'] = bridgeiface.get('model', None)
 249                                 ifaceDict['port_security'] = int(bridgeiface.get('port_security', True))
 250                                 ifaceDict['floating_ip'] = int(bridgeiface.get('floating_ip', False))
 251                                 ifaceDict['created_time'] = created_time
 252                                 bridgeInterfacesDict[vm['name']][bridgeiface['name']] = ifaceDict
 253
 254                     # Collect the data interfaces of each VM/VNFC under the 'numas' field
 255                     dataifacesDict = {}
 256                     for vm in vnf_descriptor['vnf']['VNFC']:
 257                         dataifacesDict[vm['name']] = {}
 258                         for numa in vm.get('numas', []):
 259                             for dataiface in numa.get('interfaces', []):
 260                                 created_time += 0.00001
 261                                 db_base._convert_bandwidth(dataiface, logger=self.logger)
 262                                 ifaceDict = {}
 263                                 ifaceDict['vpci'] = dataiface.get('vpci')
 264                                 ifaceDict['bw'] = dataiface['bandwidth']
 265                                 ifaceDict['model'] = "PF" if dataiface['dedicated'] == "yes" else \
 266                                     ("VF" if dataiface['dedicated'] == "no" else "VFnotShared")
 267                                 ifaceDict['created_time'] = created_time
 268                                 dataifacesDict[vm['name']][dataiface['name']] = ifaceDict
 269
 270                     #For each internal connection, we add it to the interfaceDict and we  create the appropriate net in the NFVO database.
 271                     #print "Adding new nets (VNF internal nets) to the NFVO database (if any)"
 272                     if 'internal-connections' in vnf_descriptor['vnf']:
 273                         for net in vnf_descriptor['vnf']['internal-connections']:
 274                             #print "Net name: %s. Description: %s" % (net['name'], net['description'])
 275
 276                             myNetDict = {}
 277                             myNetDict["name"] = net['name']
 278                             myNetDict["description"] = net['description']
 279                             if (net["implementation"] == "overlay"):
 280                                 net["type"] = "bridge"
 281                                 #It should give an error if the type is e-line. For the moment, we consider it as a bridge
 282                             elif (net["implementation"] == "underlay"):
 283                                 if (net["type"] == "e-line"):
 284                                     net["type"] = "ptp"
 285                                 elif (net["type"] == "e-lan"):
 286                                     net["type"] = "data"
 287                             net.pop("implementation")
 288                             myNetDict["type"] = net['type']
 289                             myNetDict["vnf_id"] = vnf_id
 290
 291                             created_time += 0.00001
 292                             net_id = self._new_row_internal('nets', myNetDict, add_uuid=True, root_uuid=vnf_id, created_time=created_time)
 293
 294                             if "ip-profile" in net:
 295                                 ip_profile = net["ip-profile"]
 296                                 myIPProfileDict = {}
 297                                 myIPProfileDict["net_id"] = net_id
 298                                 myIPProfileDict["ip_version"] = ip_profile.get('ip-version',"IPv4")
 299                                 myIPProfileDict["subnet_address"] = ip_profile.get('subnet-address',None)
 300                                 myIPProfileDict["gateway_address"] = ip_profile.get('gateway-address',None)
 301                                 myIPProfileDict["dns_address"] = ip_profile.get('dns-address',None)
 302                                 if ("dhcp" in ip_profile):
 303                                     myIPProfileDict["dhcp_enabled"] = ip_profile["dhcp"].get('enabled',"true")
 304                                     myIPProfileDict["dhcp_start_address"] = ip_profile["dhcp"].get('start-address',None)
 305                                     myIPProfileDict["dhcp_count"] = ip_profile["dhcp"].get('count',None)
 306
 307                                 created_time += 0.00001
 308                                 ip_profile_id = self._new_row_internal('ip_profiles', myIPProfileDict)
 309
 310                             for element in net['elements']:
 311                                 ifaceItem = {}
 312                                 #ifaceItem["internal_name"] = "%s-%s-%s" % (net['name'],element['VNFC'], element['local_iface_name'])
 313                                 ifaceItem["internal_name"] = element['local_iface_name']
 314                                 #ifaceItem["vm_id"] = vmDict["%s-%s" % (vnf_name,element['VNFC'])]
 315                                 ifaceItem["vm_id"] = vmDict[element['VNFC']]
 316                                 ifaceItem["net_id"] = net_id
 317                                 ifaceItem["type"] = net['type']
 318                                 ifaceItem["ip_address"] = element.get('ip_address',None)
 319                                 if ifaceItem ["type"] == "data":
 320                                     ifaceDict = dataifacesDict[ element['VNFC'] ][ element['local_iface_name'] ]
 321                                     ifaceItem["vpci"] =  ifaceDict['vpci']
 322                                     ifaceItem["bw"] =    ifaceDict['bw']
 323                                     ifaceItem["model"] = ifaceDict['model']
 324                                 else:
 325                                     ifaceDict = bridgeInterfacesDict[ element['VNFC'] ][ element['local_iface_name'] ]
 326                                     ifaceItem["vpci"] =  ifaceDict['vpci']
 327                                     ifaceItem["mac"] =  ifaceDict['mac']
 328                                     ifaceItem["bw"] =    ifaceDict['bw']
 329                                     ifaceItem["model"] = ifaceDict['model']
 330                                     ifaceItem["port_security"] = ifaceDict['port_security']
 331                                     ifaceItem["floating_ip"] = ifaceDict['floating_ip']
 332                                 created_time_iface = ifaceDict["created_time"]
 333                                 #print "Iface name: %s" % iface['internal_name']
 334                                 iface_id = self._new_row_internal('interfaces', ifaceItem, add_uuid=True, root_uuid=vnf_id, created_time=created_time_iface)
 335                                 #print "Iface id in NFVO DB: %s" % iface_id
 336
 337                     #print "Adding external interfaces to the NFVO database"
 338                     for iface in vnf_descriptor['vnf']['external-connections']:
 339                         myIfaceDict = {}
 340                         #myIfaceDict["internal_name"] = "%s-%s-%s" % (vnf_name,iface['VNFC'], iface['local_iface_name'])
 341                         myIfaceDict["internal_name"] = iface['local_iface_name']
 342                         #myIfaceDict["vm_id"] = vmDict["%s-%s" % (vnf_name,iface['VNFC'])]
 343                         myIfaceDict["vm_id"] = vmDict[iface['VNFC']]
 344                         myIfaceDict["external_name"] = iface['name']
 345                         myIfaceDict["type"] = iface['type']
 346                         if iface["type"] == "data":
 347                             myIfaceDict["vpci"]  = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['vpci']
 348                             myIfaceDict["bw"]    = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['bw']
 349                             myIfaceDict["model"] = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['model']
 350                             created_time_iface = dataifacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['created_time']
 351                         else:
 352                             myIfaceDict["vpci"]  = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['vpci']
 353                             myIfaceDict["bw"]    = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['bw']
 354                             myIfaceDict["model"] = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['model']
 355                             myIfaceDict["mac"] = bridgeInterfacesDict[ iface['VNFC'] ][ iface['local_iface_name'] ]['mac']
 356                             myIfaceDict["port_security"] = \
 357                                 bridgeInterfacesDict[iface['VNFC']][iface['local_iface_name']]['port_security']
 358                             myIfaceDict["floating_ip"] = \
 359                                 bridgeInterfacesDict[iface['VNFC']][iface['local_iface_name']]['floating_ip']
 360                             created_time_iface = bridgeInterfacesDict[iface['VNFC']][iface['local_iface_name']]['created_time']
 361                         #print "Iface name: %s" % iface['name']
 362                         iface_id = self._new_row_internal('interfaces', myIfaceDict, add_uuid=True, root_uuid=vnf_id, created_time=created_time_iface)
 363                         #print "Iface id in NFVO DB: %s" % iface_id
 364
 365                     return vnf_id
 366
 367             except (mdb.Error, AttributeError) as e:
 368                 self._format_error(e, tries)
 369 #             except KeyError as e2:
 370 #                 exc_type, exc_obj, exc_tb = sys.exc_info()
 371 #                 fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
 372 #                 self.logger.debug("Exception type: %s; Filename: %s; Line number: %s", exc_type, fname, exc_tb.tb_lineno)
 373 #                 raise KeyError
 374             tries -= 1
 375
 376     def new_scenario(self, scenario_dict):
 377         tries = 2
 378         while tries:
 379             created_time = time.time()
 380             try:
 381                 with self.con:
 382                     self.cur = self.con.cursor()
 383                     tenant_id = scenario_dict.get('tenant_id')
 384                     #scenario
 385                     INSERT_={'tenant_id': tenant_id,
 386                              'name': scenario_dict['name'],
 387                              'description': scenario_dict['description'],
 388                              'public': scenario_dict.get('public', "false")}
 389
 390                     scenario_uuid =  self._new_row_internal('scenarios', INSERT_, add_uuid=True, root_uuid=None, created_time=created_time)
 391                     #sce_nets
 392                     for net in scenario_dict['nets'].values():
 393                         net_dict={'scenario_id': scenario_uuid}
 394                         net_dict["name"] = net["name"]
 395                         net_dict["type"] = net["type"]
 396                         net_dict["description"] = net.get("description")
 397                         net_dict["external"] = net.get("external", False)
 398                         if "graph" in net:
 399                             #net["graph"]=yaml.safe_dump(net["graph"],default_flow_style=True,width=256)
 400                             #TODO, must be json because of the GUI, change to yaml
 401                             net_dict["graph"]=json.dumps(net["graph"])
 402                         created_time += 0.00001
 403                         net_uuid =  self._new_row_internal('sce_nets', net_dict, add_uuid=True, root_uuid=scenario_uuid, created_time=created_time)
 404                         net['uuid']=net_uuid
 405
 406                         if net.get("ip-profile"):
 407                             ip_profile = net["ip-profile"]
 408                             myIPProfileDict = {
 409                                 "sce_net_id": net_uuid,
 410                                 "ip_version": ip_profile.get('ip-version', "IPv4"),
 411                                 "subnet_address": ip_profile.get('subnet-address'),
 412                                 "gateway_address": ip_profile.get('gateway-address'),
 413                                 "dns_address": ip_profile.get('dns-address')}
 414                             if "dhcp" in ip_profile:
 415                                 myIPProfileDict["dhcp_enabled"] = ip_profile["dhcp"].get('enabled', "true")
 416                                 myIPProfileDict["dhcp_start_address"] = ip_profile["dhcp"].get('start-address')
 417                                 myIPProfileDict["dhcp_count"] = ip_profile["dhcp"].get('count')
 418                             self._new_row_internal('ip_profiles', myIPProfileDict)
 419
 420                     # sce_vnfs
 421                     for k, vnf in scenario_dict['vnfs'].items():
 422                         INSERT_ = {'scenario_id': scenario_uuid,
 423                                    'name': k,
 424                                    'vnf_id': vnf['uuid'],
 425                                    # 'description': scenario_dict['name']
 426                                    'description': vnf['description']}
 427                         if "graph" in vnf:
 428                             #I NSERT_["graph"]=yaml.safe_dump(vnf["graph"],default_flow_style=True,width=256)
 429                             # TODO, must be json because of the GUI, change to yaml
 430                             INSERT_["graph"] = json.dumps(vnf["graph"])
 431                         created_time += 0.00001
 432                         scn_vnf_uuid = self._new_row_internal('sce_vnfs', INSERT_, add_uuid=True,
 433                                                               root_uuid=scenario_uuid, created_time=created_time)
 434                         vnf['scn_vnf_uuid']=scn_vnf_uuid
 435                         # sce_interfaces
 436                         for iface in vnf['ifaces'].values():
 437                             # print 'iface', iface
 438                             if 'net_key' not in iface:
 439                                 continue
 440                             iface['net_id'] = scenario_dict['nets'][ iface['net_key'] ]['uuid']
 441                             INSERT_={'sce_vnf_id': scn_vnf_uuid,
 442                                      'sce_net_id': iface['net_id'],
 443                                      'interface_id':  iface['uuid'],
 444                                      'ip_address': iface.get('ip_address')}
 445                             created_time += 0.00001
 446                             iface_uuid = self._new_row_internal('sce_interfaces', INSERT_, add_uuid=True,
 447                                                                  root_uuid=scenario_uuid, created_time=created_time)
 448
 449                     return scenario_uuid
 450
 451             except (mdb.Error, AttributeError) as e:
 452                 self._format_error(e, tries)
 453             tries -= 1
 454
 455     def edit_scenario(self, scenario_dict):
 456         tries = 2
 457         while tries:
 458             modified_time = time.time()
 459             item_changed=0
 460             try:
 461                 with self.con:
 462                     self.cur = self.con.cursor()
 463                     #check that scenario exist
 464                     tenant_id = scenario_dict.get('tenant_id')
 465                     scenario_uuid = scenario_dict['uuid']
 466
 467                     where_text = "uuid='{}'".format(scenario_uuid)
 468                     if not tenant_id and tenant_id != "any":
 469                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 470                     cmd = "SELECT * FROM scenarios WHERE "+ where_text
 471                     self.logger.debug(cmd)
 472                     self.cur.execute(cmd)
 473                     self.cur.fetchall()
 474                     if self.cur.rowcount==0:
 475                         raise db_base.db_base_Exception("No scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 476                     elif self.cur.rowcount>1:
 477                         raise db_base.db_base_Exception("More than one scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 478
 479                     #scenario
 480                     nodes = {}
 481                     topology = scenario_dict.pop("topology", None)
 482                     if topology != None and "nodes" in topology:
 483                         nodes = topology.get("nodes",{})
 484                     UPDATE_ = {}
 485                     if "name" in scenario_dict:        UPDATE_["name"] = scenario_dict["name"]
 486                     if "description" in scenario_dict: UPDATE_["description"] = scenario_dict["description"]
 487                     if len(UPDATE_)>0:
 488                         WHERE_={'tenant_id': tenant_id, 'uuid': scenario_uuid}
 489                         item_changed += self._update_rows('scenarios', UPDATE_, WHERE_, modified_time=modified_time)
 490                     #sce_nets
 491                     for node_id, node in nodes.items():
 492                         if "graph" in node:
 493                             #node["graph"] = yaml.safe_dump(node["graph"],default_flow_style=True,width=256)
 494                             #TODO, must be json because of the GUI, change to yaml
 495                             node["graph"] = json.dumps(node["graph"])
 496                         WHERE_={'scenario_id': scenario_uuid, 'uuid': node_id}
 497                         #Try to change at sce_nets(version 0 API backward compatibility and sce_vnfs)
 498                         item_changed += self._update_rows('sce_nets', node, WHERE_)
 499                         item_changed += self._update_rows('sce_vnfs', node, WHERE_, modified_time=modified_time)
 500                     return item_changed
 501
 502             except (mdb.Error, AttributeError) as e:
 503                 self._format_error(e, tries)
 504             tries -= 1
 505
 506 #     def get_instance_scenario(self, instance_scenario_id, tenant_id=None):
 507 #         '''Obtain the scenario instance information, filtering by one or serveral of the tenant, uuid or name
 508 #         instance_scenario_id is the uuid or the name if it is not a valid uuid format
 509 #         Only one scenario isntance must mutch the filtering or an error is returned
 510 #         '''
 511 #         print "1******************************************************************"
 512 #         try:
 513 #             with self.con:
 514 #                 self.cur = self.con.cursor(mdb.cursors.DictCursor)
 515 #                 #scenario table
 516 #                 where_list=[]
 517 #                 if tenant_id is not None: where_list.append( "tenant_id='" + tenant_id +"'" )
 518 #                 if db_base._check_valid_uuid(instance_scenario_id):
 519 #                     where_list.append( "uuid='" + instance_scenario_id +"'" )
 520 #                 else:
 521 #                     where_list.append( "name='" + instance_scenario_id +"'" )
 522 #                 where_text = " AND ".join(where_list)
 523 #                 self.cur.execute("SELECT * FROM instance_scenarios WHERE "+ where_text)
 524 #                 rows = self.cur.fetchall()
 525 #                 if self.cur.rowcount==0:
 526 #                     return -HTTP_Bad_Request, "No scenario instance found with this criteria " + where_text
 527 #                 elif self.cur.rowcount>1:
 528 #                     return -HTTP_Bad_Request, "More than one scenario instance found with this criteria " + where_text
 529 #                 instance_scenario_dict = rows[0]
 530 #
 531 #                 #instance_vnfs
 532 #                 self.cur.execute("SELECT uuid,vnf_id FROM instance_vnfs WHERE instance_scenario_id='"+ instance_scenario_dict['uuid'] + "'")
 533 #                 instance_scenario_dict['instance_vnfs'] = self.cur.fetchall()
 534 #                 for vnf in instance_scenario_dict['instance_vnfs']:
 535 #                     #instance_vms
 536 #                     self.cur.execute("SELECT uuid, vim_vm_id "+
 537 #                                 "FROM instance_vms  "+
 538 #                                 "WHERE instance_vnf_id='" + vnf['uuid'] +"'"
 539 #                                 )
 540 #                     vnf['instance_vms'] = self.cur.fetchall()
 541 #                 #instance_nets
 542 #                 self.cur.execute("SELECT uuid, vim_net_id FROM instance_nets WHERE instance_scenario_id='"+ instance_scenario_dict['uuid'] + "'")
 543 #                 instance_scenario_dict['instance_nets'] = self.cur.fetchall()
 544 #
 545 #                 #instance_interfaces
 546 #                 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'] + "'")
 547 #                 instance_scenario_dict['instance_interfaces'] = self.cur.fetchall()
 548 #
 549 #                 db_base._convert_datetime2str(instance_scenario_dict)
 550 #                 db_base._convert_str2boolean(instance_scenario_dict, ('public','shared','external') )
 551 #                 print "2******************************************************************"
 552 #                 return 1, instance_scenario_dict
 553 #         except (mdb.Error, AttributeError) as e:
 554 #             print "nfvo_db.get_instance_scenario DB Exception %d: %s" % (e.args[0], e.args[1])
 555 #             return self._format_error(e)
 556
 557     def get_scenario(self, scenario_id, tenant_id=None, datacenter_vim_id=None, datacenter_id=None):
 558         '''Obtain the scenario information, filtering by one or serveral of the tenant, uuid or name
 559         scenario_id is the uuid or the name if it is not a valid uuid format
 560         if datacenter_vim_id,d datacenter_id is provided, it supply aditional vim_id fields with the matching vim uuid
 561         Only one scenario must mutch the filtering or an error is returned
 562         '''
 563         tries = 2
 564         while tries:
 565             try:
 566                 with self.con:
 567                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 568                     where_text = "uuid='{}'".format(scenario_id)
 569                     if not tenant_id and tenant_id != "any":
 570                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 571                     cmd = "SELECT * FROM scenarios WHERE " + where_text
 572                     self.logger.debug(cmd)
 573                     self.cur.execute(cmd)
 574                     rows = self.cur.fetchall()
 575                     if self.cur.rowcount==0:
 576                         raise db_base.db_base_Exception("No scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 577                     elif self.cur.rowcount>1:
 578                         raise db_base.db_base_Exception("More than one scenario found with this criteria " + where_text, db_base.HTTP_Bad_Request)
 579                     scenario_dict = rows[0]
 580                     if scenario_dict["cloud_config"]:
 581                         scenario_dict["cloud-config"] = yaml.load(scenario_dict["cloud_config"])
 582                     del scenario_dict["cloud_config"]
 583                     #sce_vnfs
 584                     cmd = "SELECT uuid,name,member_vnf_index,vnf_id,description FROM sce_vnfs WHERE scenario_id='{}' "\
 585                           "ORDER BY created_at".format(scenario_dict['uuid'])
 586                     self.logger.debug(cmd)
 587                     self.cur.execute(cmd)
 588                     scenario_dict['vnfs'] = self.cur.fetchall()
 589                     for vnf in scenario_dict['vnfs']:
 590                         #sce_interfaces
 591                         cmd = "SELECT scei.uuid,scei.sce_net_id,scei.interface_id,i.external_name,scei.ip_address"\
 592                               " FROM sce_interfaces as scei join interfaces as i on scei.interface_id=i.uuid"\
 593                               " WHERE scei.sce_vnf_id='{}' ORDER BY scei.created_at".format(vnf['uuid'])
 594                         self.logger.debug(cmd)
 595                         self.cur.execute(cmd)
 596                         vnf['interfaces'] = self.cur.fetchall()
 597                         #vms
 598                         cmd = "SELECT vms.uuid as uuid, flavor_id, image_id, vms.name as name," \
 599                               " vms.description as description, vms.boot_data as boot_data, count," \
 600                               " vms.availability_zone as availability_zone" \
 601                               " FROM vnfs join vms on vnfs.uuid=vms.vnf_id" \
 602                               " WHERE vnfs.uuid='" + vnf['vnf_id'] + "'"  \
 603                               " ORDER BY vms.created_at"
 604                         self.logger.debug(cmd)
 605                         self.cur.execute(cmd)
 606                         vnf['vms'] = self.cur.fetchall()
 607                         for vm in vnf['vms']:
 608                             if vm["boot_data"]:
 609                                 vm["boot_data"] = yaml.safe_load(vm["boot_data"])
 610                             else:
 611                                 del vm["boot_data"]
 612                             if datacenter_vim_id!=None:
 613                                 cmd = "SELECT vim_id FROM datacenters_images WHERE image_id='{}' AND datacenter_vim_id='{}'".format(vm['image_id'],datacenter_vim_id)
 614                                 self.logger.debug(cmd)
 615                                 self.cur.execute(cmd)
 616                                 if self.cur.rowcount==1:
 617                                     vim_image_dict = self.cur.fetchone()
 618                                     vm['vim_image_id']=vim_image_dict['vim_id']
 619                                 cmd = "SELECT vim_id FROM datacenters_flavors WHERE flavor_id='{}' AND datacenter_vim_id='{}'".format(vm['flavor_id'],datacenter_vim_id)
 620                                 self.logger.debug(cmd)
 621                                 self.cur.execute(cmd)
 622                                 if self.cur.rowcount==1:
 623                                     vim_flavor_dict = self.cur.fetchone()
 624                                     vm['vim_flavor_id']=vim_flavor_dict['vim_id']
 625
 626                             #interfaces
 627                             cmd = "SELECT uuid,internal_name,external_name,net_id,type,vpci,mac,bw,model,ip_address," \
 628                                   "floating_ip, port_security" \
 629                                     " FROM interfaces" \
 630                                     " WHERE vm_id='{}'" \
 631                                     " ORDER BY created_at".format(vm['uuid'])
 632                             self.logger.debug(cmd)
 633                             self.cur.execute(cmd)
 634                             vm['interfaces'] = self.cur.fetchall()
 635                             for index in range(0,len(vm['interfaces'])):
 636                                 vm['interfaces'][index]['port-security'] = vm['interfaces'][index].pop("port_security")
 637                                 vm['interfaces'][index]['floating-ip'] = vm['interfaces'][index].pop("floating_ip")
 638                         #nets    every net of a vms
 639                         cmd = "SELECT uuid,name,type,description FROM nets WHERE vnf_id='{}'".format(vnf['vnf_id'])
 640                         self.logger.debug(cmd)
 641                         self.cur.execute(cmd)
 642                         vnf['nets'] = self.cur.fetchall()
 643                         for vnf_net in vnf['nets']:
 644                             SELECT_ = "ip_version,subnet_address,gateway_address,dns_address,dhcp_enabled,dhcp_start_address,dhcp_count"
 645                             cmd = "SELECT {} FROM ip_profiles WHERE net_id='{}'".format(SELECT_,vnf_net['uuid'])
 646                             self.logger.debug(cmd)
 647                             self.cur.execute(cmd)
 648                             ipprofiles = self.cur.fetchall()
 649                             if self.cur.rowcount==1:
 650                                 vnf_net["ip_profile"] = ipprofiles[0]
 651                             elif self.cur.rowcount>1:
 652                                 raise db_base.db_base_Exception("More than one ip-profile found with this criteria: net_id='{}'".format(vnf_net['uuid']), db_base.HTTP_Bad_Request)
 653
 654                     #sce_nets
 655                     cmd = "SELECT uuid,name,type,external,description" \
 656                           " FROM sce_nets  WHERE scenario_id='{}'" \
 657                           " ORDER BY created_at ".format(scenario_dict['uuid'])
 658                     self.logger.debug(cmd)
 659                     self.cur.execute(cmd)
 660                     scenario_dict['nets'] = self.cur.fetchall()
 661                     #datacenter_nets
 662                     for net in scenario_dict['nets']:
 663                         if str(net['external']) == 'false':
 664                             SELECT_ = "ip_version,subnet_address,gateway_address,dns_address,dhcp_enabled,dhcp_start_address,dhcp_count"
 665                             cmd = "SELECT {} FROM ip_profiles WHERE sce_net_id='{}'".format(SELECT_,net['uuid'])
 666                             self.logger.debug(cmd)
 667                             self.cur.execute(cmd)
 668                             ipprofiles = self.cur.fetchall()
 669                             if self.cur.rowcount==1:
 670                                 net["ip_profile"] = ipprofiles[0]
 671                             elif self.cur.rowcount>1:
 672                                 raise db_base.db_base_Exception("More than one ip-profile found with this criteria: sce_net_id='{}'".format(net['uuid']), db_base.HTTP_Bad_Request)
 673                             continue
 674                         WHERE_=" WHERE name='{}'".format(net['name'])
 675                         if datacenter_id!=None:
 676                             WHERE_ += " AND datacenter_id='{}'".format(datacenter_id)
 677                         cmd = "SELECT vim_net_id FROM datacenter_nets" + WHERE_
 678                         self.logger.debug(cmd)
 679                         self.cur.execute(cmd)
 680                         d_net = self.cur.fetchone()
 681                         if d_net==None or datacenter_vim_id==None:
 682                             #print "nfvo_db.get_scenario() WARNING external net %s not found"  % net['name']
 683                             net['vim_id']=None
 684                         else:
 685                             net['vim_id']=d_net['vim_net_id']
 686
 687                     db_base._convert_datetime2str(scenario_dict)
 688                     db_base._convert_str2boolean(scenario_dict, ('public','shared','external','port-security','floating-ip') )
 689                     return scenario_dict
 690             except (mdb.Error, AttributeError) as e:
 691                 self._format_error(e, tries)
 692             tries -= 1
 693
 694     def delete_scenario(self, scenario_id, tenant_id=None):
 695         '''Deletes a scenario, filtering by one or several of the tenant, uuid or name
 696         scenario_id is the uuid or the name if it is not a valid uuid format
 697         Only one scenario must mutch the filtering or an error is returned
 698         '''
 699         tries = 2
 700         while tries:
 701             try:
 702                 with self.con:
 703                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 704
 705                     #scenario table
 706                     where_text = "uuid='{}'".format(scenario_id)
 707                     if not tenant_id and tenant_id != "any":
 708                         where_text += " AND (tenant_id='{}' OR public='True')".format(tenant_id)
 709                     cmd = "SELECT * FROM scenarios WHERE "+ where_text
 710                     self.logger.debug(cmd)
 711                     self.cur.execute(cmd)
 712                     rows = self.cur.fetchall()
 713                     if self.cur.rowcount==0:
 714                         raise db_base.db_base_Exception("No scenario found where " + where_text, db_base.HTTP_Bad_Request)
 715                     elif self.cur.rowcount>1:
 716                         raise db_base.db_base_Exception("More than one scenario found where " + where_text, db_base.HTTP_Bad_Request)
 717                     scenario_uuid = rows[0]["uuid"]
 718                     scenario_name = rows[0]["name"]
 719
 720                     #sce_vnfs
 721                     cmd = "DELETE FROM scenarios WHERE uuid='{}'".format(scenario_uuid)
 722                     self.logger.debug(cmd)
 723                     self.cur.execute(cmd)
 724
 725                     return scenario_uuid + " " + scenario_name
 726             except (mdb.Error, AttributeError) as e:
 727                 self._format_error(e, tries, "delete", "instances running")
 728             tries -= 1
 729
 730     def new_rows(self, tables, uuid_list=None):
 731         """
 732         Make a transactional insertion of rows at several tables
 733         :param tables: list with dictionary where the keys are the table names and the values are a row or row list
 734             with the values to be inserted at the table. Each row is a dictionary with the key values. E.g.:
 735             tables = [
 736                 {"table1": [ {"column1": value, "column2: value, ... }, {"column1": value, "column2: value, ... }, ...],
 737                 {"table2": [ {"column1": value, "column2: value, ... }, {"column1": value, "column2: value, ... }, ...],
 738                 {"table3": {"column1": value, "column2: value, ... }
 739             }
 740             If tables does not contain the 'created_at', it is generated incrementally with the order of tables. You can
 741             provide a integer value, that it is an index multiply by 0.00001 to add to the created time to manually set
 742             up and order
 743         :param uuid_list: list of created uuids, first one is the root (#TODO to store at uuid table)
 744         :return: None if success,  raise exception otherwise
 745         """
 746         tries = 2
 747         while tries:
 748             created_time = time.time()
 749             try:
 750                 with self.con:
 751                     self.cur = self.con.cursor()
 752                     for table in tables:
 753                         for table_name, row_list in table.items():
 754                             index = 0
 755                             if isinstance(row_list, dict):
 756                                 row_list = (row_list, )  #create a list with the single value
 757                             for row in row_list:
 758                                 if table_name in self.tables_with_created_field:
 759                                     if "created_at" in row:
 760                                         created_time_param = created_time + row.pop("created_at")*0.00001
 761                                     else:
 762                                         created_time_param = created_time + index*0.00001
 763                                         index += 1
 764                                 else:
 765                                     created_time_param = 0
 766                                 self._new_row_internal(table_name, row, add_uuid=False, root_uuid=None,
 767                                                                created_time=created_time_param)
 768                     return
 769             except (mdb.Error, AttributeError) as e:
 770                 self._format_error(e, tries)
 771             tries -= 1
 772
 773     def new_instance_scenario_as_a_whole(self,tenant_id,instance_scenario_name,instance_scenario_description,scenarioDict):
 774         tries = 2
 775         while tries:
 776             created_time = time.time()
 777             try:
 778                 with self.con:
 779                     self.cur = self.con.cursor()
 780                     #instance_scenarios
 781                     datacenter_id = scenarioDict['datacenter_id']
 782                     INSERT_={'tenant_id': tenant_id,
 783                         'datacenter_tenant_id': scenarioDict["datacenter2tenant"][datacenter_id],
 784                         'name': instance_scenario_name,
 785                         'description': instance_scenario_description,
 786                         'scenario_id' : scenarioDict['uuid'],
 787                         'datacenter_id': datacenter_id
 788                     }
 789                     if scenarioDict.get("cloud-config"):
 790                         INSERT_["cloud_config"] = yaml.safe_dump(scenarioDict["cloud-config"], default_flow_style=True, width=256)
 791
 792                     instance_uuid = self._new_row_internal('instance_scenarios', INSERT_, add_uuid=True, root_uuid=None, created_time=created_time)
 793
 794                     net_scene2instance={}
 795                     #instance_nets   #nets interVNF
 796                     for net in scenarioDict['nets']:
 797                         net_scene2instance[ net['uuid'] ] ={}
 798                         datacenter_site_id = net.get('datacenter_id', datacenter_id)
 799                         if not "vim_id_sites" in net:
 800                             net["vim_id_sites"] ={datacenter_site_id: net['vim_id']}
 801                             net["vim_id_sites"]["datacenter_site_id"] = {datacenter_site_id: net['vim_id']}
 802                         sce_net_id = net.get("uuid")
 803
 804                         for datacenter_site_id,vim_id in net["vim_id_sites"].iteritems():
 805                             INSERT_={'vim_net_id': vim_id, 'created': net.get('created', False), 'instance_scenario_id':instance_uuid } #,  'type': net['type']
 806                             INSERT_['datacenter_id'] = datacenter_site_id
 807                             INSERT_['datacenter_tenant_id'] = scenarioDict["datacenter2tenant"][datacenter_site_id]
 808                             if not net.get('created', False):
 809                                 INSERT_['status'] = "ACTIVE"
 810                             if sce_net_id:
 811                                 INSERT_['sce_net_id'] = sce_net_id
 812                             created_time += 0.00001
 813                             instance_net_uuid =  self._new_row_internal('instance_nets', INSERT_, True, instance_uuid, created_time)
 814                             net_scene2instance[ sce_net_id ][datacenter_site_id] = instance_net_uuid
 815                             net['uuid'] = instance_net_uuid  #overwrite scnario uuid by instance uuid
 816
 817                         if 'ip_profile' in net:
 818                             net['ip_profile']['net_id'] = None
 819                             net['ip_profile']['sce_net_id'] = None
 820                             net['ip_profile']['instance_net_id'] = instance_net_uuid
 821                             created_time += 0.00001
 822                             ip_profile_id = self._new_row_internal('ip_profiles', net['ip_profile'])
 823
 824                     #instance_vnfs
 825                     for vnf in scenarioDict['vnfs']:
 826                         datacenter_site_id = vnf.get('datacenter_id', datacenter_id)
 827                         INSERT_={'instance_scenario_id': instance_uuid,  'vnf_id': vnf['vnf_id']  }
 828                         INSERT_['datacenter_id'] = datacenter_site_id
 829                         INSERT_['datacenter_tenant_id'] = scenarioDict["datacenter2tenant"][datacenter_site_id]
 830                         if vnf.get("uuid"):
 831                             INSERT_['sce_vnf_id'] = vnf['uuid']
 832                         created_time += 0.00001
 833                         instance_vnf_uuid =  self._new_row_internal('instance_vnfs', INSERT_, True, instance_uuid, created_time)
 834                         vnf['uuid'] = instance_vnf_uuid  #overwrite scnario uuid by instance uuid
 835
 836                         #instance_nets   #nets intraVNF
 837                         for net in vnf['nets']:
 838                             net_scene2instance[ net['uuid'] ] = {}
 839                             INSERT_={'vim_net_id': net['vim_id'], 'created': net.get('created', False), 'instance_scenario_id':instance_uuid  } #,  'type': net['type']
 840                             INSERT_['datacenter_id'] = net.get('datacenter_id', datacenter_site_id)
 841                             INSERT_['datacenter_tenant_id'] = scenarioDict["datacenter2tenant"][datacenter_id]
 842                             if net.get("uuid"):
 843                                 INSERT_['net_id'] = net['uuid']
 844                             created_time += 0.00001
 845                             instance_net_uuid =  self._new_row_internal('instance_nets', INSERT_, True, instance_uuid, created_time)
 846                             net_scene2instance[ net['uuid'] ][datacenter_site_id] = instance_net_uuid
 847                             net['uuid'] = instance_net_uuid  #overwrite scnario uuid by instance uuid
 848
 849                             if 'ip_profile' in net:
 850                                 net['ip_profile']['net_id'] = None
 851                                 net['ip_profile']['sce_net_id'] = None
 852                                 net['ip_profile']['instance_net_id'] = instance_net_uuid
 853                                 created_time += 0.00001
 854                                 ip_profile_id = self._new_row_internal('ip_profiles', net['ip_profile'])
 855
 856                         #instance_vms
 857                         for vm in vnf['vms']:
 858                             INSERT_={'instance_vnf_id': instance_vnf_uuid,  'vm_id': vm['uuid'], 'vim_vm_id': vm['vim_id']  }
 859                             created_time += 0.00001
 860                             instance_vm_uuid =  self._new_row_internal('instance_vms', INSERT_, True, instance_uuid, created_time)
 861                             vm['uuid'] = instance_vm_uuid  #overwrite scnario uuid by instance uuid
 862
 863                             #instance_interfaces
 864                             for interface in vm['interfaces']:
 865                                 net_id = interface.get('net_id', None)
 866                                 if net_id is None:
 867                                     #check if is connected to a inter VNFs net
 868                                     for iface in vnf['interfaces']:
 869                                         if iface['interface_id'] == interface['uuid']:
 870                                             if 'ip_address' in iface:
 871                                                 interface['ip_address'] = iface['ip_address']
 872                                             net_id = iface.get('sce_net_id', None)
 873                                             break
 874                                 if net_id is None:
 875                                     continue
 876                                 interface_type='external' if interface['external_name'] is not None else 'internal'
 877                                 INSERT_={'instance_vm_id': instance_vm_uuid,  'instance_net_id': net_scene2instance[net_id][datacenter_site_id],
 878                                     'interface_id': interface['uuid'], 'vim_interface_id': interface.get('vim_id'), 'type':  interface_type,
 879                                     'ip_address': interface.get('ip_address'), 'floating_ip': int(interface.get('floating-ip',False)),
 880                                     'port_security': int(interface.get('port-security',True))}
 881                                 #created_time += 0.00001
 882                                 interface_uuid =  self._new_row_internal('instance_interfaces', INSERT_, True, instance_uuid) #, created_time)
 883                                 interface['uuid'] = interface_uuid  #overwrite scnario uuid by instance uuid
 884                 return instance_uuid
 885             except (mdb.Error, AttributeError) as e:
 886                 self._format_error(e, tries)
 887             tries -= 1
 888
 889     def get_instance_scenario(self, instance_id, tenant_id=None, verbose=False):
 890         '''Obtain the instance information, filtering by one or several of the tenant, uuid or name
 891         instance_id is the uuid or the name if it is not a valid uuid format
 892         Only one instance must mutch the filtering or an error is returned
 893         '''
 894         tries = 2
 895         while tries:
 896             try:
 897                 with self.con:
 898                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 899                     # instance table
 900                     where_list=[]
 901                     if tenant_id is not None: where_list.append( "inst.tenant_id='" + tenant_id +"'" )
 902                     if db_base._check_valid_uuid(instance_id):
 903                         where_list.append( "inst.uuid='" + instance_id +"'" )
 904                     else:
 905                         where_list.append( "inst.name='" + instance_id +"'" )
 906                     where_text = " AND ".join(where_list)
 907                     cmd = "SELECT inst.uuid as uuid,inst.name as name,inst.scenario_id as scenario_id, datacenter_id" +\
 908                                 " ,datacenter_tenant_id, s.name as scenario_name,inst.tenant_id as tenant_id" + \
 909                                 " ,inst.description as description,inst.created_at as created_at" +\
 910                                 " ,inst.cloud_config as 'cloud_config'" +\
 911                             " FROM instance_scenarios as inst join scenarios as s on inst.scenario_id=s.uuid"+\
 912                             " WHERE " + where_text
 913                     self.logger.debug(cmd)
 914                     self.cur.execute(cmd)
 915                     rows = self.cur.fetchall()
 916
 917                     if self.cur.rowcount==0:
 918                         raise db_base.db_base_Exception("No instance found where " + where_text, db_base.HTTP_Not_Found)
 919                     elif self.cur.rowcount>1:
 920                         raise db_base.db_base_Exception("More than one instance found where " + where_text, db_base.HTTP_Bad_Request)
 921                     instance_dict = rows[0]
 922                     if instance_dict["cloud_config"]:
 923                         instance_dict["cloud-config"] = yaml.load(instance_dict["cloud_config"])
 924                     del instance_dict["cloud_config"]
 925
 926                     # instance_vnfs
 927                     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"\
 928                             " FROM instance_vnfs as iv join sce_vnfs as sv on iv.sce_vnf_id=sv.uuid" \
 929                             " WHERE iv.instance_scenario_id='{}'" \
 930                             " ORDER BY iv.created_at ".format(instance_dict['uuid'])
 931                     self.logger.debug(cmd)
 932                     self.cur.execute(cmd)
 933                     instance_dict['vnfs'] = self.cur.fetchall()
 934                     for vnf in instance_dict['vnfs']:
 935                         vnf_manage_iface_list=[]
 936                         #instance vms
 937                         cmd = "SELECT iv.uuid as uuid, vim_vm_id, status, error_msg, vim_info, iv.created_at as created_at, name "\
 938                                 " FROM instance_vms as iv join vms on iv.vm_id=vms.uuid "\
 939                                 " WHERE instance_vnf_id='{}' ORDER BY iv.created_at".format(vnf['uuid'])
 940                         self.logger.debug(cmd)
 941                         self.cur.execute(cmd)
 942                         vnf['vms'] = self.cur.fetchall()
 943                         for vm in vnf['vms']:
 944                             vm_manage_iface_list=[]
 945                             #instance_interfaces
 946                             cmd = "SELECT vim_interface_id, instance_net_id, internal_name,external_name, mac_address,"\
 947                                   " ii.ip_address as ip_address, vim_info, i.type as type, sdn_port_id"\
 948                                   " FROM instance_interfaces as ii join interfaces as i on ii.interface_id=i.uuid"\
 949                                   " WHERE instance_vm_id='{}' ORDER BY created_at".format(vm['uuid'])
 950                             self.logger.debug(cmd)
 951                             self.cur.execute(cmd )
 952                             vm['interfaces'] = self.cur.fetchall()
 953                             for iface in vm['interfaces']:
 954                                 if iface["type"] == "mgmt" and iface["ip_address"]:
 955                                     vnf_manage_iface_list.append(iface["ip_address"])
 956                                     vm_manage_iface_list.append(iface["ip_address"])
 957                                 if not verbose:
 958                                     del iface["type"]
 959                             if vm_manage_iface_list: vm["ip_address"] = ",".join(vm_manage_iface_list)
 960                         if vnf_manage_iface_list: vnf["ip_address"] = ",".join(vnf_manage_iface_list)
 961
 962                     #instance_nets
 963                     #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"
 964                     #from_text = "instance_nets join instance_scenarios on instance_nets.instance_scenario_id=instance_scenarios.uuid " + \
 965                     #            "join sce_nets on instance_scenarios.scenario_id=sce_nets.scenario_id"
 966                     #where_text = "instance_nets.instance_scenario_id='"+ instance_dict['uuid'] + "'"
 967                     cmd = "SELECT uuid,vim_net_id,status,error_msg,vim_info,created, sce_net_id, net_id as vnf_net_id, datacenter_id, datacenter_tenant_id, sdn_net_id"\
 968                             " FROM instance_nets" \
 969                             " WHERE instance_scenario_id='{}' ORDER BY created_at".format(instance_dict['uuid'])
 970                     self.logger.debug(cmd)
 971                     self.cur.execute(cmd)
 972                     instance_dict['nets'] = self.cur.fetchall()
 973
 974                     db_base._convert_datetime2str(instance_dict)
 975                     db_base._convert_str2boolean(instance_dict, ('public','shared','created') )
 976                     return instance_dict
 977             except (mdb.Error, AttributeError) as e:
 978                 self._format_error(e, tries)
 979             tries -= 1
 980
 981     def delete_instance_scenario(self, instance_id, tenant_id=None):
 982         '''Deletes a instance_Scenario, filtering by one or serveral of the tenant, uuid or name
 983         instance_id is the uuid or the name if it is not a valid uuid format
 984         Only one instance_scenario must mutch the filtering or an error is returned
 985         '''
 986         tries = 2
 987         while tries:
 988             try:
 989                 with self.con:
 990                     self.cur = self.con.cursor(mdb.cursors.DictCursor)
 991
 992                     #instance table
 993                     where_list=[]
 994                     if tenant_id is not None: where_list.append( "tenant_id='" + tenant_id +"'" )
 995                     if db_base._check_valid_uuid(instance_id):
 996                         where_list.append( "uuid='" + instance_id +"'" )
 997                     else:
 998                         where_list.append( "name='" + instance_id +"'" )
 999                     where_text = " AND ".join(where_list)
1000                     cmd = "SELECT * FROM instance_scenarios WHERE "+ where_text
1001                     self.logger.debug(cmd)
1002                     self.cur.execute(cmd)
1003                     rows = self.cur.fetchall()
1004
1005                     if self.cur.rowcount==0:
1006                         raise db_base.db_base_Exception("No instance found where " + where_text, db_base.HTTP_Bad_Request)
1007                     elif self.cur.rowcount>1:
1008                         raise db_base.db_base_Exception("More than one instance found where " + where_text, db_base.HTTP_Bad_Request)
1009                     instance_uuid = rows[0]["uuid"]
1010                     instance_name = rows[0]["name"]
1011
1012                     #sce_vnfs
1013                     cmd = "DELETE FROM instance_scenarios WHERE uuid='{}'".format(instance_uuid)
1014                     self.logger.debug(cmd)
1015                     self.cur.execute(cmd)
1016
1017                     return instance_uuid + " " + instance_name
1018             except (mdb.Error, AttributeError) as e:
1019                 self._format_error(e, tries, "delete", "No dependences can avoid deleting!!!!")
1020             tries -= 1
1021
1022     def new_instance_scenario(self, instance_scenario_dict, tenant_id):
1023         #return self.new_row('vnfs', vnf_dict, None, tenant_id, True, True)
1024         return self._new_row_internal('instance_scenarios', instance_scenario_dict, tenant_id, add_uuid=True, root_uuid=None, log=True)
1025
1026     def update_instance_scenario(self, instance_scenario_dict):
1027         #TODO:
1028         return
1029
1030     def new_instance_vnf(self, instance_vnf_dict, tenant_id, instance_scenario_id = None):
1031         #return self.new_row('vms', vm_dict, tenant_id, True, True)
1032         return self._new_row_internal('instance_vnfs', instance_vnf_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
1033
1034     def update_instance_vnf(self, instance_vnf_dict):
1035         #TODO:
1036         return
1037
1038     def delete_instance_vnf(self, instance_vnf_id):
1039         #TODO:
1040         return
1041
1042     def new_instance_vm(self, instance_vm_dict, tenant_id, instance_scenario_id = None):
1043         #return self.new_row('vms', vm_dict, tenant_id, True, True)
1044         return self._new_row_internal('instance_vms', instance_vm_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
1045
1046     def update_instance_vm(self, instance_vm_dict):
1047         #TODO:
1048         return
1049
1050     def delete_instance_vm(self, instance_vm_id):
1051         #TODO:
1052         return
1053
1054     def new_instance_net(self, instance_net_dict, tenant_id, instance_scenario_id = None):
1055         return self._new_row_internal('instance_nets', instance_net_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
1056
1057     def update_instance_net(self, instance_net_dict):
1058         #TODO:
1059         return
1060
1061     def delete_instance_net(self, instance_net_id):
1062         #TODO:
1063         return
1064
1065     def new_instance_interface(self, instance_interface_dict, tenant_id, instance_scenario_id = None):
1066         return self._new_row_internal('instance_interfaces', instance_interface_dict, tenant_id, add_uuid=True, root_uuid=instance_scenario_id, log=True)
1067
1068     def update_instance_interface(self, instance_interface_dict):
1069         #TODO:
1070         return
1071
1072     def delete_instance_interface(self, instance_interface_dict):
1073         #TODO:
1074         return
1075
1076     def update_datacenter_nets(self, datacenter_id, new_net_list=[]):
1077         ''' Removes the old and adds the new net list at datacenter list for one datacenter.
1078         Attribute
1079             datacenter_id: uuid of the datacenter to act upon
1080             table: table where to insert
1081             new_net_list: the new values to be inserted. If empty it only deletes the existing nets
1082         Return: (Inserted items, Deleted items) if OK, (-Error, text) if error
1083         '''
1084         tries = 2
1085         while tries:
1086             created_time = time.time()
1087             try:
1088                 with self.con:
1089                     self.cur = self.con.cursor()
1090                     cmd="DELETE FROM datacenter_nets WHERE datacenter_id='{}'".format(datacenter_id)
1091                     self.logger.debug(cmd)
1092                     self.cur.execute(cmd)
1093                     deleted = self.cur.rowcount
1094                     inserted = 0
1095                     for new_net in new_net_list:
1096                         created_time += 0.00001
1097                         self._new_row_internal('datacenter_nets', new_net, add_uuid=True, created_time=created_time)
1098                         inserted += 1
1099                     return inserted, deleted
1100             except (mdb.Error, AttributeError) as e:
1101                 self._format_error(e, tries)
1102             tries -= 1
1103
1104