import datetime import re import os import time import traceback from src.containers.DeviceContainer import * from src.dbconnectors.db import DBConnector import src.settings as settings from pymssql import IntegrityError class DeviceDB(DBConnector): def __init__(self, host, user, password, database): DBConnector.__init__(self, host, user, password, database) self.logger = settings.logger self.retry_count = 0 def __set_data(self, cur, dev_container, dev, table_prefix, column_prefix, listStatusCodes): self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' processing device. Version: ' + str(dev.version)) default_table_name = table_prefix + dev.loggerId + '_' + dev.deviceId ldid = dev.loggerId + '_' + dev.deviceId # get table name if device exists; otherwise set table_name as default_table_name cur.execute("use " + self.database + "; SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE DEVICE_NAME='ldid' AND DEVICE_VALUE=%s", (ldid,)) resTableName = cur.fetchone() if resTableName!=None: self.logger.debug("device exists already. Update") # case: device exists table_name = resTableName[0] # Check if device version exists; if yes -> do nothing and continue to next device statementCheckDevVersion = ("use " + self.database + "; SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version' AND DEVICE_VALUE=%s") cur.execute(statementCheckDevVersion, (table_name, dev.version)) resV = cur.fetchone() if resV!=None: listStatusCodes[dev.deviceId] = 'DEVICE_VERSION_EXISTS' self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' device version already exists. No modification will be made') return dev_alias = '' for p in dev.parameters: if 'alias' == p.key.lower(): #re.search('alias', p.key, re.IGNORECASE) != None: dev_alias = p.value aliasChanged = False cur.execute("SELECT DEVICE_VALUE FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='alias'",(table_name,)) res = cur.fetchone() if res!=None: if dev_alias!=res[0]: self.logger.debug("logger alias not equal: {_a} <> {_b}".format(_a=dev_alias, _b=res[0]) ) aliasChanged = True old_alias = res[0] else: self.logger.debug("Alias of device has not been changed.") else: # case: device is new # replace dashes in the names of data tables because otherwise the exporter won't work table_name = default_table_name.replace('-','dash') # replace slash in the name of data tables becasue otherwise the exporter won't work table_name = table_name.replace('/','slash') # insert device id, if the device is new self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Insert device id into xxx_RAW_Device_Properties') propList = [(table_name, "Device_ID", dev.deviceId), (table_name, "ldid", ldid), (table_name, "Database_Container", table_name)] sql = """INSERT INTO xxx_RAW_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES (%s, %s, %s)""" cur.executemany(sql, propList) paramList = [(table_name, "Device_Class", table_name, "Device_Class", dev.category, dev.category, table_name, "Device_Class"), (table_name, "Module_Name", table_name, "Module_Name", dev.dtype, dev.dtype, table_name, "Module_Name"), (table_name, "Type", table_name, "Type", dev.dtype, dev.dtype, table_name, "Type")] # Insert device category statementInsertDevVersion = ("IF NOT EXISTS (SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME=%s)" "INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,%s,%s) " "ELSE UPDATE xxx_Raw_Device_Properties SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%s") cur.executemany(statementInsertDevVersion, paramList) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' First Commit on xxx_Raw_Device_Properties for') self._connection.commit() # gather device parameters in an array of tuples for the cursor 'executemany' statement paramVals = [] dev_alias = '' for p in dev.parameters: if 'alias' == p.key.lower(): #re.search('alias', p.key, re.IGNORECASE) != None: dev_alias = p.value paramVals.append((table_name, p.key, table_name, p.key, p.value, p.value, table_name, p.key)) # check if alias parameter has changed in case the device is not new. We need # this information later when inserting the measurement curves into the xxx_RAW_Device_Properties # and xxx_Virtual_Devices tables in order to avoid updating of all measurement curve rows only to update the alias, # because this is the biggest performance bottleneck of the setDevice operation aliasChanged = False cur.execute("SELECT DEVICE_VALUE FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='alias'",(table_name,)) res = cur.fetchone() if res!=None: if dev_alias!=res[0]: self.logger.debug("logger alias not equal: {_a} <> {_b}".format(_a=dev_alias, _b=res[0]) ) aliasChanged = True old_alias = res[0] else: # fetch alias of the device. sql = """SELECT DISTINCT TOP 1 virtual_device_name AS alias FROM dbo.xxx_Virtual_Devices WHERE Table_Name=%s""" cur.execute(sql, (table_name,)) res = cur.fetchall() for r in res: dev_alias = r[0] else: self.logger.debug("Device has no alias parameter") # add device parameters if they don't exist, otherwise update statementInsertDevParam = ("IF NOT EXISTS (SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME=%s) " "INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,%s,%s) " "ELSE UPDATE xxx_Raw_Device_Properties SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%s") cur.executemany(statementInsertDevParam, paramVals) # when unpacking the XML request key-value pairs with empty 'v' attribute are stored in a separate list and deleted here. See specification for pkey in dev.paramsToDelete: cur.execute('DELETE FROM xxx_Raw_Device_Properties WHERE TABLE_NAME=%s AND DEVICE_NAME=%s',(table_name,pkey)) # Insert into xxx_Device_Mappings cur.execute("IF NOT EXISTS (SELECT * FROM xxx_Device_Mappings (READUNCOMMITTED) WHERE Table_Name=%s) " "INSERT INTO xxx_Device_Mappings (Table_Name, RAW_Table_Name, RAW_Name) VALUES(%s,%s,%s)", (table_name, table_name, table_name, dev.deviceId)) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Second Commit on xxx_Device_Mappings for') self._connection.commit() self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' finished inserting device parameters into xxx_RAW_Device_Properties') # start preparing for creating data tables columns = 'Date_and_time datetime, Storage_time datetime, Storage_delay int, ' columnDefinitionsArr = [] columnNamesArr = [] insertMesCurvRawPropParams = [] insertMesCurvVirtDevParams = [] '''Append virtual delay value to virtual devices table''' insertMesCurvVirtDevParams.append((table_name, 'Storage_delay', table_name, 'Storage_delay', dev.category, 'DELAY', 'DELAY', dev_alias, 'DELAY', 'seconds')) vtype_arr = [] vtype_duplications = {} for v in dev.deviceValues: matching = [s for s in vtype_arr if v.vtype == s] if len(matching) >= 1: if vtype_duplications.get(v.vtype): vtype_duplications[v.vtype] += 1 else: vtype_duplications[v.vtype] = 2 number = vtype_duplications[v.vtype] v.vtype = "{_vtype}_{_nr}".format(_vtype=v.vtype, _nr=number) vtype_arr.append(v.vtype) # find the storage type for the given curve cur.execute("SELECT datatype, vpc, unit FROM COMMONDB.DBO.value_type (READUNCOMMITTED) WHERE value_type=%s",(v.vtype,)) self.logger.debug("v.type:" + str(v.vtype)) resValueType = cur.fetchone() if resValueType!=None and resValueType[1]!=None: value_storage_type = resValueType[0] value_vpc = resValueType[1].replace(' ', '') value_unit = resValueType[2] if value_unit: value_unit = value_unit.strip() column_prefix = "COL_SR_" else: #storage_type = v.datatype self.logger.warning('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' vpc for value_type: '+ v.vtype + ' and device id: ' + dev.deviceId +' either could not be found or was null') if dev_container.auth!='skygateCGZOTFgfkhik7hzgrm2hbq4aE': self.logger.warning('DeviceDB: unknown vtype will be ignored') continue value_unit = None param_classes = None # this is the default value data type value_datatype = "nvarchar(32)" for p in v.parameters: if p.key == "datatype": value_datatype = p.value if value_datatype and "float" in value_datatype.lower(): value_datatype = "float" elif value_datatype and "int" in value_datatype.lower(): value_datatype = "bigint" value_storage_type = value_datatype elif p.key == "unit": value_unit = p.value elif p.key == "class": if p.value: param_classes = p.value.split(',') #sql = """SELECT max(cast(vpc AS bigint))+1 AS vpc FROM COMMONDB.DBO.value_type""" #cur.execute(sql) #res_vpc = cur.fetchone() #value_vpc = res_vpc[0] #if int(value_vpc) < 1000000: # value_vpc = 1000000 value_vpc = v.vtype self.logger.debug('pos1') sql = """INSERT INTO COMMONDB.DBO.value_type (value_type, description, datatype, unit, storage_type, vpc) VALUES(%s,%s,%s,%s,%s,%s)""" '''self.logger.debug(v.vtype) self.logger.debug(value_datatype) self.logger.debug(value_unit) self.logger.debug(value_vpc)''' self.logger.debug(v.storage) cur.execute(sql, (v.vtype, "", value_datatype, value_unit, v.storage, value_vpc)) self.logger.debug('pos2') #MR to shorten the value column name. we don't need the prefix COL_SR_ .... column_prefix = "" col_name = column_prefix + str(value_vpc) class1 = None class2 = None self.logger.debug('param classes: ' + str(param_classes)) if param_classes and len(param_classes) > 0: class1 = param_classes[0] if not class1: class1 = 'UNKNOWN' if len(param_classes) > 1: class2 = param_classes[1] else: class1 = 'UNKNOWN' sql = """INSERT INTO [cdb].[dbo].[CDB_COLUMN_TYPES] (Column_Value,Column_Type_String,Translation_Key,Device_Type, Device_Class,Device_Class_2,Unit_String) VALUES(%s,%s,%s,%s,%s,%s,%s)""" try: cur.execute(sql, (col_name, value_datatype, v.vtype, dev.category, class1, class2, value_unit)) except IntegrityError, err: self.logger.warning("Column does already exists in cdb_column_types.") value_alias = '' for vp in v.parameters: if vp.key == 'alias': value_alias = vp.value statement_tr_key_ger = """IF NOT EXISTS (SELECT * FROM [cdb].[dbo].[Translations] WHERE Language=1 AND Translation_Key=%s AND Translation=%s) INSERT INTO [cdb].[dbo].[Translations] (Language, Translation_Key, Translation) VALUES(1, %s, %s)""" statement_tr_key_eng = """IF NOT EXISTS (SELECT * FROM [cdb].[dbo].[Translations] WHERE Language=2 AND Translation_Key=%s AND Translation=%s) INSERT INTO [cdb].[dbo].[Translations] (Language, Translation_Key, Translation) VALUES(2, %s, %s)""" cur.execute(statement_tr_key_ger, (value_vpc, value_alias, value_vpc, value_alias)) cur.execute(statement_tr_key_eng, (value_vpc, value_alias, value_vpc, value_alias)) #HACK MR: if vpc is not an integer setup_col_prefix to "" try: int(value_vpc) self.logger.debug("value_vpc: " + value_vpc + " prefix: " + column_prefix) except: self.logger.debug("reset column prefix. vpc: %s" % value_vpc) column_prefix = "" column_name = column_prefix + str(value_vpc) columnDefinition = column_name + ' ' + value_storage_type columns += columnDefinition + ',' columnDefinitionsArr.append(columnDefinition) columnNamesArr.append(column_name) # append parameter for bulk insert of measurement curves into xxx_RAW_Device_Properties table '''insertMesCurvRawPropParams.append((table_name, column_name, table_name, dev_alias, v.valueId, value_unit, column_name, value_vpc, dev_alias, table_name, column_name))''' insertMesCurvRawPropParams.append((table_name, column_name, table_name, dev_alias, v.valueId, value_unit, column_name, str(value_vpc))) # retrieve column_types from cdb_column_types cur.execute("SELECT Translation_Key, Device_Class, Device_Class_2 FROM cdb.dbo.CDB_COLUMN_TYPES (READUNCOMMITTED) WHERE Column_Value=%s", (column_name,)) resCT = cur.fetchone() if resCT!=None: value_tk = resCT[0] value_dc = resCT[1] value_dc2 = resCT[2] else: self.logger.warning('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' column_value was not found in cdb.dbo.CDB_COLUMN_TYPES for column name ' + column_name) continue # append parameter for bulk insert of measurement curves into xxx_virtual_devices table '''insertMesCurvVirtDevParams.append((table_name, column_name, table_name, column_name, dev.category, value_dc, value_dc2, dev_alias, value_tk, value_unit, dev.category, dev_alias, table_name, column_name)) ''' insertMesCurvVirtDevParams.append((table_name, column_name, table_name, column_name, dev.category, value_dc, value_dc2, dev_alias, value_tk, value_unit)) # Insert value parameters valParamVals = [] for vp in v.parameters: valParamVals.append((v.lvid, vp.key, v.lvid, vp.key, vp.value, vp.value, v.lvid, vp.key)) if len(valParamVals) > 0: sql_create = """ IF NOT EXISTS (SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='xxx_value_parameter') CREATE TABLE [dbo].[xxx_value_parameter]( [lvid] [nvarchar](100) NOT NULL, [value_key] [nvarchar](100) NOT NULL, [value] [nvarchar](100) NOT NULL, CONSTRAINT [xxx_value_parameter_pkey] PRIMARY KEY CLUSTERED ([lvid] ASC,[value_key] ASC))""" cur.execute(sql_create) statementInsertValParam = ("IF NOT EXISTS (SELECT * FROM xxx_value_parameter (READUNCOMMITTED) WHERE lvid=%s AND value_key=%s) " "INSERT INTO xxx_value_parameter VALUES(%s,%s,%s) " "ELSE UPDATE xxx_value_parameter " "SET value=%s WHERE lvid=%s AND value_key=%s") cur.executemany(statementInsertValParam, valParamVals) for vkey in v.paramsToDelete: cur.execute('DELETE FROM xxx_value_parameter WHERE lvid=%s AND value_key=%s',(v.lvid,vkey)) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Third Commit on xxx_value_parameter') self._connection.commit() #---------------------------------------bulk insert of values # bulk insert of measurement curves into xxx_Virtual_Devices self.logger.debug("""IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name=%s) " "INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID, Unit_String) " "VALUES(%s, %s, %s, %s, %s, %s, %s, '-1', %s) """) self.logger.debug('DeviceDB: insertMesCurvVirtDevParams: %s' % insertMesCurvVirtDevParams) cur.executemany("IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name=%s) " "INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID, Unit_String) " "VALUES(%s, %s, %s, %s, %s, %s, %s, '-1', %s) ", insertMesCurvVirtDevParams) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' finished inserting into xxx_Virtual_Devices') self._connection.commit() old_dev_category = '' cur.execute("SELECT Device_Type, Column_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s", (table_name)) res_vd = cur.fetchall() ################### deactivate columns table_columns = [col[1] for col in res_vd] self.logger.info(table_columns) columns_deactive = list(set(table_columns) - set(columnNamesArr) - set(['COL_E_TODAY_CALC', 'Storage_delay'])) self.logger.debug("DeviceDB: columns_deactive %s" % str(columns_deactive)) if len(columns_deactive)>=1: col_deact_string = "','".join(columns_deactive) del_statement = """DELETE FROM xxx_Virtual_Devices WHERE Table_name=%s AND Column_Name IN ('{_col}')""".format(_col=col_deact_string) self.logger.debug('DeviceDB: del col from virtual devices: %s' % col_deact_string) cur.execute(del_statement, (table_name,)) ################################### end deactivate old column if res_vd!=[]: old_dev_category = res_vd[0][0] if aliasChanged: self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') - device alias has been changed') cur.execute("UPDATE xxx_RAW_Device_Properties SET DEVICE_NAME=%s WHERE TABLE_NAME=%s AND DEVICE_NAME=%s",(dev_alias, table_name, old_alias)) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Update xxx_RAW_Device_Properties') cur.execute("UPDATE xxx_Virtual_Devices SET Device_Type=%s, Virtual_Device_Name=%s WHERE Table_Name=%s",(dev.category, dev_alias, table_name)) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Update xxx_Virtual_Devices') elif old_dev_category!='': cur.execute("UPDATE xxx_Virtual_Devices SET Device_Type=%s WHERE Table_Name=%s",(dev.category, table_name)) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Update xxx_Virtual_Devices in else') self.logger.debug("insert into raw_device_properties") self.logger.debug(insertMesCurvRawPropParams) # bulk insert of measurement curves into xxx_RAW_Device_Properties sql = ("IF NOT EXISTS (SELECT TABLE_NAME FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND COLUMN_NAME=%s) " "INSERT INTO xxx_RAW_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE, DEVICE_UNIT, COLUMN_NAME, " "DEVICE_VPC, VALUE_TYPE, DEVICE_TRANSMIT) " "VALUES(%s,%s,%s,%s,%s,%s,1,1) ") self.logger.debug(sql) cur.executemany(sql, insertMesCurvRawPropParams) self.logger.debug("insert into raw_device_properties end") self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' finished inserting measurement curves into xxx_RAW_Device_Properties') # check if the table for the measurement data exists statementCheckTable = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES (READUNCOMMITTED) WHERE TABLE_NAME='"+table_name+"' " cur.execute(statementCheckTable) resFromCheck = cur.fetchone() # create data table if does not exist; otherwise add missing columns if resFromCheck==None: self.logger.debug('DeviceDB: ' + dev.loggerId + ' ' + dev.deviceId + ' data table does not exist. Creating table.') # build pk constraint for data table constraint = 'CONSTRAINT ' + table_name + '_pkey PRIMARY KEY (Date_and_time)' statementCreateValTable = "CREATE TABLE " + table_name + "(" + columns + constraint + ") " self.logger.debug("create table sql: %s" % statementCreateValTable) cur.execute(statementCreateValTable) else: self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' data table exists. Checking if an update is needed for device') for index in range(len(columnNamesArr)): statementColumns = ("IF NOT EXISTS (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS (READUNCOMMITTED) WHERE TABLE_NAME=%s AND COLUMN_NAME=%s)" "ALTER TABLE "+ table_name +" ADD " +columnDefinitionsArr[index]) params = (table_name, columnNamesArr[index]) cur.execute(statementColumns,params) self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Fifth Commit for device') self._connection.commit() # Insert device version statementInsertDevVersion = ("""IF NOT EXISTS (SELECT * FROM xxx_RAW_Device_Properties (READUNCOMMITTED) WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version') INSERT INTO xxx_Raw_Device_Properties (TABLE_NAME, DEVICE_NAME, DEVICE_VALUE) VALUES(%s,'Config_Version',%s) ELSE UPDATE xxx_Raw_Device_Properties SET DEVICE_VALUE=%s WHERE TABLE_NAME=%s AND DEVICE_NAME='Config_Version'""") cur.execute(statementInsertDevVersion, (table_name, table_name, dev.version, dev.version, table_name )) #-----ADD COL_E_TODAY_CALC if dev.category == 'INVERTER': cur.execute("""IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[{_tbl_1}]') AND name = 'COL_E_TODAY_CALC') ALTER TABLE {_tbl_2} ADD COL_E_TODAY_CALC bigint""".format(_tbl_1=table_name, _tbl_2=table_name )) self.logger.debug("DeviceDB: create COL_E_TODAY_CALC") self.logger.debug("DeviceDB: table_name: %s" % table_name) self.logger.debug("DeviceDB: Column_Name: COL_E_TODAY_CALC") cur.execute("IF NOT EXISTS (SELECT Table_Name FROM xxx_Virtual_Devices (READUNCOMMITTED) WHERE Table_Name=%s AND Column_Name='COL_E_TODAY_CALC') " "INSERT INTO xxx_Virtual_Devices (Table_Name, Column_Name, Device_Type, Device_Class, Device_Class_2, Virtual_Device_Name, Translation_Key, Station_ID) " "VALUES(%s, 'COL_E_TODAY_CALC', 'INVERTER', 'E_TODAY', 'E_TODAY', %s, 'ETODAY_ADJUSTED', '-1') ",(table_name, table_name, dev_alias)) #---------------END ADD COL_E_TODAY_CALC self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Final Commit for device.') self._connection.commit() def setData(self, devContainer): self.logger.debug('DeviceDB: entered processing setdevice request') if self._connection != None: listStatusCodes = {} cur = self._connection.cursor() cur.execute('use {_db}'.format(_db=self.database)) cur.execute('select db_name()') curr_db = cur.fetchone()[0] if curr_db != self.database: self.logger.error('Mismatch between current db and routing db. Exiting') raise Exception table_prefix = 'Data_Table_' column_prefix = 'COL_SR_' ##################################hidden device hidden_devices = [] try: self.__create_hidden_devs_table(cur) self._connection.commit() hidden_devices = self.__get_hidden_devices(cur, devContainer.loggerId) except Exception as e: self.logger.debug('Something went wrong with the hidden device code') ################################### for dev in devContainer.listObjects: if dev.deviceId in hidden_devices: listStatusCodes[dev.deviceId] = 'SUCCESS' self.logger.debug('DeviceDB: device {_dev} is marked as hidden. Going to skip'.format(_dev=dev.deviceId)) continue tryCount = 0 while(tryCount <= self.retry_count): try: self.__set_data(cur, devContainer, dev, table_prefix, column_prefix, listStatusCodes) #raise Exception ('deadlock error') tryCount = self.retry_count + 1 except Exception as e: self.logger.error('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' operation failed for device: Error msg: ' + str(traceback.format_exc())) self._connection.rollback() if str(e).find("deadlock") > -1: tryCount += 1 if tryCount > self.retry_count: listStatusCodes[dev.deviceId] = 'SERVER_SIDE_ERROR' self.logger.debug('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Giving up on retrying ') ''' else: self.__closeConnection() time.sleep(5) self.get_connection() cur = self._connection.cursor() self.logger.error('DeviceDB: (' + str(os.getpid()) + ') ' + dev.loggerId + ' ' + dev.deviceId + ' Deadlock retry Number of retries: [' + str(tryCount) + ':' + str(self.retry_count) + ']') ''' else: tryCount = self.retry_count + 1 listStatusCodes[dev.deviceId] = 'SERVER_SIDE_ERROR' return listStatusCodes def __create_hidden_devs_table(self, cur): sql = """IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'dbo.[xxx_Hidden_Devices]') and OBJECTPROPERTY(id, N'IsTable') = 1) BEGIN CREATE TABLE dbo.[xxx_Hidden_Devices] ( sdid nvarchar(100), lid nvarchar(100) CONSTRAINT hidden_devices_pk PRIMARY KEY (sdid, lid) ) END """.format(_schema=self.database) cur.execute(sql) def __get_hidden_devices(self, cur, lid): hidden_devs = [] sql = """SELECT sdid FROM xxx_Hidden_Devices WHERE lid=%s""" cur.execute(sql, (lid,)) res = cur.fetchall() for row in res: hidden_devs.append(row[0]) return hidden_devs