def __init__(self, gateway, config, connector_type):
super().__init__()
self.daemon = True
self.setName(config.get("name", 'ODBC Connector ' + ''.join(choice(ascii_lowercase) for _ in range(5))))
self.statistics = {'MessagesReceived': 0,
'MessagesSent': 0}
self.__gateway = gateway
self.__connector_type = connector_type
self.__config = config
self.__stopped = False
self.__config_dir = "thingsboard_gateway/config/odbc/"
self.__connection = None
self.__cursor = None
self.__rpc_cursor = None
self.__iterator = None
self.__iterator_file_name = ""
self.__devices = {}
self.__column_names = []
self.__attribute_columns = []
self.__timeseries_columns = []
self.__converter = OdbcUplinkConverter() if not self.__config.get("converter", "") else \
TBUtility.check_and_import(self.__connector_type, self.__config["converter"])
self.__configure_pyodbc()
self.__parse_rpc_config()
class OdbcUplinkConverterTests(unittest.TestCase):
def setUp(self):
self.converter = OdbcUplinkConverter()
self.db_data = {"boolValue": True,
"intValue": randint(0, 256),
"floatValue": uniform(-3.1415926535, 3.1415926535),
"stringValue": "".join(choice(ascii_lowercase) for _ in range(8))}
def test_glob_matching(self):
converted_data = self.converter.convert("*", self.db_data)
self.assertDictEqual(converted_data, self.db_data)
def test_data_subset(self):
config = ["floatValue", "boolValue"]
converted_data = self.converter.convert(config, self.db_data)
expected_data = {}
for key in config:
expected_data[key] = self.db_data[key]
self.assertDictEqual(converted_data, expected_data)
def test_alias(self):
config = [{"column": "stringValue", "name": "valueOfString"}]
converted_data = self.converter.convert(config, self.db_data)
self.assertDictEqual(converted_data, {config[0]["name"]: self.db_data[config[0]["column"]]})
def test_name_expression(self):
attr_name = "someAttribute"
config = [{"nameExpression": "key", "value": "intValue"}]
self.db_data["key"] = attr_name
converted_data = self.converter.convert(config, self.db_data)
self.assertDictEqual(converted_data, {attr_name: self.db_data[config[0]["value"]]})
def test_value_config(self):
config = [{"name": "someValue", "value": "stringValue + str(intValue)"}]
converted_data = self.converter.convert(config, self.db_data)
self.assertDictEqual(converted_data, {config[0]["name"]: self.db_data["stringValue"] + str(self.db_data["intValue"])})
def test_one_valid_one_invalid_configs(self):
config = ["unkownColumnValue", "stringValue"]
converted_data = self.converter.convert(config, self.db_data)
self.assertDictEqual(converted_data, {config[1]: self.db_data[config[1]]})
class OdbcConnector(Connector, Thread):
DEFAULT_SEND_IF_CHANGED = True
DEFAULT_RECONNECT_STATE = True
DEFAULT_SAVE_ITERATOR = False
DEFAULT_RECONNECT_PERIOD = 60
DEFAULT_POLL_PERIOD = 60
DEFAULT_ENABLE_UNKNOWN_RPC = False
DEFAULT_OVERRIDE_RPC_PARAMS = False
def __init__(self, gateway, config, connector_type):
super().__init__()
self.daemon = True
self.setName(config.get("name", 'ODBC Connector ' + ''.join(choice(ascii_lowercase) for _ in range(5))))
self.statistics = {'MessagesReceived': 0,
'MessagesSent': 0}
self.__gateway = gateway
self.__connector_type = connector_type
self.__config = config
self.__stopped = False
self.__config_dir = "thingsboard_gateway/config/odbc/"
self.__connection = None
self.__cursor = None
self.__rpc_cursor = None
self.__iterator = None
self.__iterator_file_name = ""
self.__devices = {}
self.__column_names = []
self.__attribute_columns = []
self.__timeseries_columns = []
self.__converter = OdbcUplinkConverter() if not self.__config.get("converter", "") else \
TBUtility.check_and_import(self.__connector_type, self.__config["converter"])
self.__configure_pyodbc()
self.__parse_rpc_config()
def open(self):
log.debug("[%s] Starting...", self.get_name())
self.__stopped = False
self.start()
def close(self):
if not self.__stopped:
self.__stopped = True
log.debug("[%s] Stopping", self.get_name())
def get_name(self):
return self.name
def is_connected(self):
return self.__connection is not None
def on_attributes_update(self, content):
pass
def server_side_rpc_handler(self, content):
done = False
try:
if not self.is_connected():
log.warn("[%s] Cannot process RPC request: not connected to database", self.get_name())
raise Exception("no connection")
sql_params = self.__config["serverSideRpc"]["methods"].get(content["data"]["method"])
if sql_params is None:
if not self.__config["serverSideRpc"]["enableUnknownRpc"]:
log.warn("[%s] Ignore unknown RPC request '%s' (id=%s)",
self.get_name(), content["data"]["method"], content["data"]["id"])
raise Exception("unknown RPC request")
else:
sql_params = content["data"].get("params", {}).get("args", [])
elif self.__config["serverSideRpc"]["overrideRpcConfig"]:
if content["data"].get("params", {}).get("args", []):
sql_params = content["data"]["params"]["args"]
log.debug("[%s] Processing '%s' RPC request (id=%s) for '%s' device: params=%s",
self.get_name(), content["data"]["method"], content["data"]["id"],
content["device"], content["data"].get("params"))
if self.__rpc_cursor is None:
self.__rpc_cursor = self.__connection.cursor()
if sql_params:
self.__rpc_cursor.execute("{{CALL {} ({})}}".format(content["data"]["method"],
("?," * len(sql_params))[0:-1]),
sql_params)
else:
self.__rpc_cursor.execute("{{CALL {}}}".format(content["data"]["method"]))
done = True
log.debug("[%s] Processed '%s' RPC request (id=%s) for '%s' device",
self.get_name(), content["data"]["method"], content["data"]["id"], content["device"])
except pyodbc.Warning as w:
log.warn("[%s] Warning while processing '%s' RPC request (id=%s) for '%s' device: %s",
self.get_name(), content["data"]["method"], content["data"]["id"], content["device"], str(w))
except Exception as e:
log.error("[%s] Failed to process '%s' RPC request (id=%s) for '%s' device: %s",
self.get_name(), content["data"]["method"], content["data"]["id"], content["device"], str(e))
finally:
self.__gateway.send_rpc_reply(content["device"], content["data"]["id"], {"success": done})
def run(self):
while not self.__stopped:
# Initialization phase
if not self.is_connected():
while not self.__stopped and \
not self.__init_connection() and \
self.__config["connection"].get("reconnect", self.DEFAULT_RECONNECT_STATE):
reconnect_period = self.__config["connection"].get("reconnectPeriod", self.DEFAULT_RECONNECT_PERIOD)
log.info("[%s] Will reconnect to database in %d second(s)", self.get_name(), reconnect_period)
sleep(reconnect_period)
if not self.is_connected():
log.error("[%s] Cannot connect to database so exit from main loop", self.get_name())
self.__stopped = True
break
if not self.__init_iterator():
log.error("[%s] Cannot init database iterator so exit from main loop", self.get_name())
break
# Polling phase
try:
self.__poll()
# self.server_side_rpc_handler({"device": "RPC test",
# "data": {
# "id": 777,
# "method": "usp_NoParameters",
# "params": [ 8, True, "Three" ]
# }})
if not self.__stopped:
polling_period = self.__config["polling"].get("period", self.DEFAULT_POLL_PERIOD)
log.debug("[%s] Next polling iteration will be in %d second(s)", self.get_name(), polling_period)
sleep(polling_period)
except pyodbc.Warning as w:
log.warn("[%s] Warning while polling database: %s", self.get_name(), str(w))
except pyodbc.Error as e:
log.error("[%s] Error while polling database: %s", self.get_name(), str(e))
self.__close()
self.__close()
log.info("[%s] Stopped", self.get_name())
def __close(self):
if self.is_connected():
try:
self.__connection.close()
finally:
log.info("[%s] Connection to database closed", self.get_name())
self.__connection = None
self.__cursor = None
def __poll(self):
rows = self.__cursor.execute(self.__config["polling"]["query"], self.__iterator["value"])
if not self.__column_names and self.__cursor.rowcount > 0:
for column in self.__cursor.description:
self.__column_names.append(column[0])
log.info("[%s] Fetch column names: %s", self.get_name(), self.__column_names)
for row in rows:
# log.debug("[%s] Fetch row: %s", self.get_name(), row)
self.__process_row(row)
self.__iterator["total"] += self.__cursor.rowcount
log.info("[%s] Polling iteration finished. Processed rows: current %d, total %d",
self.get_name(), self.__cursor.rowcount, self.__iterator["total"])
if self.__config["polling"]["iterator"].get("persistent",
self.DEFAULT_SAVE_ITERATOR) and self.__cursor.rowcount > 0:
self.__save_iterator_config()
def __process_row(self, row):
try:
data = {}
for column in self.__column_names:
data[column] = getattr(row, column)
to_send = {"attributes": {} if "attributes" not in self.__config["mapping"] else
self.__converter.convert(self.__config["mapping"]["attributes"], data),
"telemetry": {} if "timeseries" not in self.__config["mapping"] else
self.__converter.convert(self.__config["mapping"]["timeseries"], data)}
device_name = eval(self.__config["mapping"]["device"]["name"], globals(), data)
if device_name not in self.__devices:
self.__devices[device_name] = {"attributes": {}, "telemetry": {}}
self.__gateway.add_device(device_name, {"connector": self})
self.__iterator["value"] = getattr(row, self.__iterator["name"])
self.__check_and_send(device_name,
self.__config["mapping"]["device"].get("type", self.__connector_type),
to_send)
except Exception as e:
log.warn("[%s] Failed to process database row: %s", self.get_name(), str(e))
def __check_and_send(self, device_name, device_type, new_data):
self.statistics['MessagesReceived'] += 1
to_send = {"attributes": [], "telemetry": []}
send_on_change = self.__config["mapping"].get("sendDataOnlyOnChange", self.DEFAULT_SEND_IF_CHANGED)
for tb_key in to_send.keys():
for key, new_value in new_data[tb_key].items():
if not send_on_change or self.__devices[device_name][tb_key].get(key, None) != new_value:
self.__devices[device_name][tb_key][key] = new_value
to_send[tb_key].append({key: new_value})
if to_send["attributes"] or to_send["telemetry"]:
to_send["deviceName"] = device_name
to_send["deviceType"] = device_type
log.debug("[%s] Pushing to TB server '%s' device data: %s", self.get_name(), device_name, to_send)
self.__gateway.send_to_storage(self.get_name(), to_send)
self.statistics['MessagesSent'] += 1
else:
log.debug("[%s] '%s' device data has not been changed", self.get_name(), device_name)
def __init_connection(self):
try:
log.debug("[%s] Opening connection to database", self.get_name())
connection_config = self.__config["connection"]
self.__connection = pyodbc.connect(connection_config["str"], **connection_config.get("attributes", {}))
if connection_config.get("encoding", ""):
log.info("[%s] Setting encoding to %s", self.get_name(), connection_config["encoding"])
self.__connection.setencoding(connection_config["encoding"])
decoding_config = connection_config.get("decoding")
if decoding_config is not None:
if isinstance(decoding_config, dict):
if decoding_config.get("char", ""):
log.info("[%s] Setting SQL_CHAR decoding to %s", self.get_name(), decoding_config["char"])
self.__connection.setdecoding(pyodbc.SQL_CHAR, decoding_config["char"])
if decoding_config.get("wchar", ""):
log.info("[%s] Setting SQL_WCHAR decoding to %s", self.get_name(), decoding_config["wchar"])
self.__connection.setdecoding(pyodbc.SQL_WCHAR, decoding_config["wchar"])
if decoding_config.get("metadata", ""):
log.info("[%s] Setting SQL_WMETADATA decoding to %s",
self.get_name(), decoding_config["metadata"])
self.__connection.setdecoding(pyodbc.SQL_WMETADATA, decoding_config["metadata"])
else:
log.warn("[%s] Unknown decoding configuration %s. Read data may be misdecoded", self.get_name(),
decoding_config)
self.__cursor = self.__connection.cursor()
log.info("[%s] Connection to database opened, attributes %s",
self.get_name(), connection_config.get("attributes", {}))
except pyodbc.Error as e:
log.error("[%s] Failed to connect to database: %s", self.get_name(), str(e))
self.__close()
return self.is_connected()
def __resolve_iterator_file(self):
file_name = ""
try:
# The algorithm of resolving iterator file name is described in
# https://thingsboard.io/docs/iot-gateway/config/odbc/#subsection-iterator
# Edit that description whether algorithm is changed.
file_name += self.__connection.getinfo(pyodbc.SQL_DRIVER_NAME)
file_name += self.__connection.getinfo(pyodbc.SQL_SERVER_NAME)
file_name += self.__connection.getinfo(pyodbc.SQL_DATABASE_NAME)
file_name += self.__config["polling"]["iterator"]["column"]
self.__iterator_file_name = sha1(file_name.encode()).hexdigest() + ".json"
log.debug("[%s] Iterator file name resolved to %s", self.get_name(), self.__iterator_file_name)
except Exception as e:
log.warn("[%s] Failed to resolve iterator file name: %s", self.get_name(), str(e))
return bool(self.__iterator_file_name)
def __init_iterator(self):
save_iterator = self.__config["polling"]["iterator"].get("persistent", self.DEFAULT_SAVE_ITERATOR)
log.info("[%s] Iterator saving %s", self.get_name(), "enabled" if save_iterator else "disabled")
if save_iterator and self.__load_iterator_config():
log.info("[%s] Init iterator from file '%s': column=%s, start_value=%s",
self.get_name(), self.__iterator_file_name,
self.__iterator["name"], self.__iterator["value"])
return True
self.__iterator = {"name": self.__config["polling"]["iterator"]["column"],
"total": 0}
if "value" in self.__config["polling"]["iterator"]:
self.__iterator["value"] = self.__config["polling"]["iterator"]["value"]
log.info("[%s] Init iterator from configuration: column=%s, start_value=%s",
self.get_name(), self.__iterator["name"], self.__iterator["value"])
elif "query" in self.__config["polling"]["iterator"]:
try:
self.__iterator["value"] = \
self.__cursor.execute(self.__config["polling"]["iterator"]["query"]).fetchone()[0]
log.info("[%s] Init iterator from database: column=%s, start_value=%s",
self.get_name(), self.__iterator["name"], self.__iterator["value"])
except pyodbc.Warning as w:
log.warn("[%s] Warning on init iterator from database: %s", self.get_name(), str(w))
except pyodbc.Error as e:
log.error("[%s] Failed to init iterator from database: %s", self.get_name(), str(e))
else:
log.error("[%s] Failed to init iterator: value/query param is absent", self.get_name())
return "value" in self.__iterator
def __save_iterator_config(self):
try:
Path(self.__config_dir).mkdir(exist_ok=True)
with Path(self.__config_dir + self.__iterator_file_name).open("w") as iterator_file:
iterator_file.write(dumps(self.__iterator, indent=2, sort_keys=True))
log.debug("[%s] Saved iterator configuration to %s", self.get_name(), self.__iterator_file_name)
except Exception as e:
log.error("[%s] Failed to save iterator configuration to %s: %s",
self.get_name(), self.__iterator_file_name, str(e))
def __load_iterator_config(self):
if not self.__iterator_file_name:
if not self.__resolve_iterator_file():
log.error("[%s] Unable to load iterator configuration from file: file name is not resolved",
self.get_name())
return False
try:
iterator_file_path = Path(self.__config_dir + self.__iterator_file_name)
if not iterator_file_path.exists():
return False
with iterator_file_path.open("r") as iterator_file:
self.__iterator = load(iterator_file)
log.debug("[%s] Loaded iterator configuration from %s", self.get_name(), self.__iterator_file_name)
except Exception as e:
log.error("[%s] Failed to load iterator configuration from %s: %s",
self.get_name(), self.__iterator_file_name, str(e))
return bool(self.__iterator)
def __configure_pyodbc(self):
pyodbc_config = self.__config.get("pyodbc", {})
if not pyodbc_config:
return
for name, value in pyodbc_config.items():
pyodbc.__dict__[name] = value
log.info("[%s] Set pyodbc attributes: %s", self.get_name(), pyodbc_config)
def __parse_rpc_config(self):
if "enableUnknownRpc" not in self.__config["serverSideRpc"]:
self.__config["serverSideRpc"]["enableUnknownRpc"] = self.DEFAULT_ENABLE_UNKNOWN_RPC
log.info("[%s] Processing unknown RPC %s", self.get_name(),
"enabled" if self.__config["serverSideRpc"]["enableUnknownRpc"] else "disabled")
if "overrideRpcConfig" not in self.__config["serverSideRpc"]:
self.__config["serverSideRpc"]["overrideRpcConfig"] = self.DEFAULT_OVERRIDE_RPC_PARAMS
log.info("[%s] Overriding RPC config %s", self.get_name(),
"enabled" if self.__config["serverSideRpc"]["overrideRpcConfig"] else "disabled")
if "serverSideRpc" not in self.__config or not self.__config["serverSideRpc"].get("methods", []):
self.__config["serverSideRpc"] = {"methods": {}}
return
reformatted_config = {}
for rpc_config in self.__config["serverSideRpc"]["methods"]:
if isinstance(rpc_config, str):
reformatted_config[rpc_config] = []
elif isinstance(rpc_config, dict):
reformatted_config[rpc_config["name"]] = rpc_config.get("params", [])
else:
log.warn("[%s] Wrong RPC config format. Expected str or dict, get %s", self.get_name(), type(rpc_config))
self.__config["serverSideRpc"]["methods"] = reformatted_config
def setUp(self):
self.converter = OdbcUplinkConverter()
self.db_data = {"boolValue": True,
"intValue": randint(0, 256),
"floatValue": uniform(-3.1415926535, 3.1415926535),
"stringValue": "".join(choice(ascii_lowercase) for _ in range(8))}