osm_ro/nfvo_db.py

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