class SQLManager(object):
def __init__(self):
self.connect_to_database()
def connect_to_database(self):
DB_NAME = settings.get_database()
self.connpool = MySQLConnectionPool(user=settings.DatabaseConfig.USERNAME,
password=settings.DatabaseConfig.PASSWORD,
host=settings.DatabaseConfig.HOST,
pool_name="order_manager",
pool_size=5,
autocommit=True,
database=DB_NAME
)
self.cnx1 = self.connpool.get_connection()
self.cur1 = self.cnx1.cursor()
self.cnx2 = self.connpool.get_connection()
self.cur2 = self.cnx2.cursor()
self.cnx3 = self.connpool.get_connection()
self.cur3 = self.cnx3.cursor()
self.cnx4 = self.connpool.get_connection()
self.cur4 = self.cnx4.cursor()
self.cnx5 = self.connpool.get_connection()
self.cur5 = self.cnx5.cursor()
class DataSource:
def __init__(self,
host='127.0.0.1',
port='3306',
user='******',
password='',
database='jground',
_pool_name='remote_query',
_pool_size=10):
self.pool = MySQLConnectionPool(pool_size=_pool_size,
host=host,
port=port,
user=user,
password=password,
database=database,
pool_reset_session=True,
autocommit=True,
charset='utf8mb4')
def getConnection(self):
return self.pool.get_connection()
def returnConnection(self, con):
con.close()
class DatabaseConnectionPool(object):
INSTANCE = None
def __init__(self, filename='../resources/db_properties.ini'):
if self.INSTANCE is not None:
raise ValueError("An instantiation already exists!")
else:
db_config = read_db_config(filename)
self.__cnxPool = MySQLConnectionPool(pool_name="myPool",
pool_size=10,
**db_config)
@classmethod
def get_instance(
cls,
filename='../resources/db_properties.ini',
):
print("get_instance() ------------", filename)
if cls.INSTANCE is None:
cls.INSTANCE = DatabaseConnectionPool(filename)
return cls.INSTANCE
def get_connection(self):
return self.__cnxPool.get_connection()
@classmethod
def pool_close(cls):
cls.INSTANCE = None
class MySQLPooledConnection(MySQLConnectionABC):
""" MySQL Connection from connection-pool """
def __init__(self,
*args,
db_schema: DBSchema,
dictionary: bool = False,
named_tuple: bool = False,
pool_size: int = 16,
**kwargs):
print('MySQLPooledConnection: Init with pool_size=%d' % pool_size)
self.cnx_pool = MySQLConnectionPool(pool_size=pool_size,
*args,
**kwargs)
super().__init__(*args,
db_schema=db_schema,
dictionary=dictionary,
named_tuple=named_tuple,
**kwargs)
def new_cnx(self, *args, **kwargs):
return self.cnx_pool.get_connection()
def create_cursor(self, *args, **options) -> 'MySQLCursor':
self.close()
self.cnx = self.new_cnx()
return MySQLCursor(
self, self.cnx.cursor(*args, **self._cursor_options(**options)))
class MySQL(object):
def __init__(self):
self._cnxpool = MySQLConnectionPool(pool_name="mysqlPool",
pool_size=3,
**Config.dbconfig)
def _getConnection(self):
return self._cnxpool.get_connection()
def query(self, query, queryArgsTuple=None):
cnx = self._getConnection()
with closing(cnx.cursor()) as cursor:
cursor.execute(query, queryArgsTuple)
results = cursor.fetchall()
cnx.close()
return results
def modify(self, query, queryArgsTuple=None):
cnx = self._getConnection()
with closing(cnx.cursor()) as cursor:
cursor.execute(query, queryArgsTuple)
cnx.commit()
cnx.close()
class Event(Flask):
def __init__(self, *args, **kwargs):
kwargs.update({
'template_folder': settings.STATIC_ASSETS_PATH,
'static_folder': settings.STATIC_ASSETS_PATH,
'static_url_path': '',
})
super(Event, self).__init__(__name__, *args, **kwargs)
self.connection_pool = None
self.enums = None
def create_connection_pool(self):
pool_config = {
"host": settings.EVENT_MYSQL_DATABASE_HOST,
"port": settings.EVENT_MYSQL_DATABASE_PORT,
"database": settings.EVENT_MYSQL_DATABASE_NAME,
"user": settings.EVENT_MYSQL_DATABASE_USER,
"password": settings.EVENT_MYSQL_DATABASE_PASSWORD,
"charset": settings.EVENT_MYSQL_DATABASE_CHARSET,
"use_unicode": True,
"get_warnings": True,
"ssl_verify_cert": False,
"pool_name": self.__class__.__name__,
"pool_reset_session":
settings.EVENT_MYSQL_DATABASE_POOL_RESET_SESSION,
"pool_size": settings.EVENT_MYSQL_DATABASE_POOL_SIZE,
"autocommit": True
}
if settings.EVENT_MYSQL_DATABASE_SSL_VERIFY_CERT:
pool_config["ssl_verify_cert"] = True
pool_config["ssl_ca"] = settings.EVENT_MYSQL_DATABASE_SSL_CA_PATH
self.connection_pool = MySQLConnectionPool(**pool_config)
def load_enums(self):
sql = 'SELECT * FROM c_enum'
cnx = self.connection_pool.get_connection()
enum_list = execute_sql_statement(sql, sql_cnx=cnx)
enum_map = defaultdict(list)
enum_obj_map = {}
for enum in enum_list:
enum_map[enum.get("enum_type_name")].append(enum)
value_tuple = namedtuple("VALUE", ["value", "name"])
for enum_name_code, enum_value_list in enum_map.items():
value_map = {
enum_value.get("enum_code"):
value_tuple(enum_value.get("enum_value"),
enum_value.get("enum_name"))
for enum_value in enum_value_list
}
temp_named_tuple = namedtuple(enum_name_code,
list(value_map.keys()))
enum_obj_map[enum_name_code.upper()] = temp_named_tuple(
**value_map)
enum_type_tuple = namedtuple("ENUM", enum_obj_map.keys())
self.enums = enum_type_tuple(**enum_obj_map)
cnx.close()
REGISTRATION_TYPE_LIST.extend(
self.enums.REGISTRATION_TYPE._asdict().keys())
class SQLPool:
__slots__ = ('conn', )
def __init__(self, **kwargs):
self.conn = MySQLConnectionPool(autocommit=True, **kwargs)
def execute(self, query: str, params: SQLParams = []) -> int:
if not (cnx := self.conn.get_connection()):
raise Exception('MySQL: Failed to retrieve a worker.')
cur = cnx.cursor()
cur.execute(query, params)
cur.fetchmany()
# Since we are executing a command, we
# simply return the last row affected's id.
res = cur.lastrowid
[x.close() for x in (cur, cnx)]
return res
class MySqlDriver:
"""MySQL driver class."""
def __init__(self):
"""Mysqldriver Constructor."""
self.config = {
"host":
"db-espresso-dev" if environ.get("APP_ENV") == "development" else
environ.get("MYSQL_HOST"),
"database":
environ.get("MYSQL_DATABASE", "espresso_db"),
"user":
environ.get("MYSQL_USER", "root"),
"password":
environ.get("MYSQL_ROOT_PASSWORD", "strawberry"),
"port":
int(environ.get("MYSQL_PORT", 3306)),
"pool_name":
"mysql_pool",
"pool_size":
5,
"pool_reset_session":
False, # MySQL version 5.7.2 and earlier does not support COM_RESET_CONNECTION.
}
try:
self.cnxpool = MySQLConnectionPool(**self.config)
except errors.Error as err:
print(err)
def query(self, query: str, data: tuple = ()) -> dict or False:
"""Execute a query on database instance."""
try:
cnx = self.cnxpool.get_connection()
cursor = cnx.cursor()
cursor.execute(query, data)
result = cursor.fetchall()
return result
except errors.Error as err:
print(err)
return False
finally:
cursor.close()
cnx.close()
class ExplicitlyConnectionPool(object):
INSTANCE = None
def __init__(self):
if self.INSTANCE is not None:
raise ValueError("An instantiation already exists!")
else:
self.__cnxPool = MySQLConnectionPool(pool_name="myPool",
pool_size=5,
option_files='config.conf')
@classmethod
def get_instance(cls):
if cls.INSTANCE is None:
cls.INSTANCE = ExplicitlyConnectionPool()
return cls.INSTANCE
def get_connection(self):
return self.__cnxPool.get_connection()
class DatabaseConnectionPool:
INSTANCE = None
def __init__(self):
if self.INSTANCE is not None:
raise ValueError("An instantiation already exists!")
else:
db_config = read_db_config(filename='Config.ini', section='mysql')
print(type(db_config), db_config)
self.__cnxPool = MySQLConnectionPool(pool_name='myPool', pool_size=5, **db_config)
@classmethod
def get_instance(cls):
if cls.INSTANCE is None:
cls.INSTANCE = DatabaseConnectionPool()
return cls.INSTANCE
def get_connection(self):
return self.__cnxPool.get_connection()
class MysqlPooledDataSource(AbstractDataSource):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self._conn_params['use_pure'] = True
self._conn_params['pool_name'] = 'test_pool'
self._conn_params['pool_size'] = 10
# self._pool = MySQLConnectionPool(**{'user':self._user_name,'password':self._pass_word,\
# 'host':self._host,'port':self._port,'database':self._data_base,\
# 'use_pure':True,'pool_name':"test_pool",'pool_size':10})
try:
self._traceback = None
self._pool = MySQLConnectionPool(**self._conn_params)
except InterfaceError as e:
print('MysqlPooledDataSource connect error')
e.with_traceback(self._traceback)
raise e
def get_conn(self):
return PooledMySQLConnection(self, self._pool.get_connection())
class DatabaseConnectionPool(object):
INSTANCE = None
def __init__(self):
if self.INSTANCE is not None:
raise ValueError("An instantiation already exists!")
else:
db_config = read_db_config()
self.__cnxPool = MySQLConnectionPool(pool_name="myPool",
pool_size=5,
**db_config)
@classmethod
def get_instance(cls):
if cls.INSTANCE is None:
cls.INSTANCE = DatabaseConnectionPool()
return cls.INSTANCE
def get_connection(self):
return self.__cnxPool.get_connection()
class Database(object, metaclass=Singleton):
def __init__(self):
cfg = self.__get_cfg()
self.pool = MySQLConnectionPool(pool_name='dbpool',
pool_size=5,
**cfg)
def __get_cfg(self):
with open('database.yml') as f:
cfg = yaml.load(f.read())['production']
keys = ['username', 'password', 'host', 'port', 'database', 'ssl_ca',
'ssl_cert', 'ssl_key']
no_ = lambda s: s.replace('_', '')
cfg = { k: cfg.get(no_(k)) for k in keys if no_(k) in cfg }
cfg['ssl_verify_cert'] = True
# Mysql documentation incorrectly says 'username' is an alias of 'user'.
cfg['user'] = cfg.pop('username')
return cfg
@contextmanager
def get_connection(self):
def get_conn(attempt=0):
try:
return self.pool.get_connection()
except PoolError:
if 20 > attempt:
time.sleep(0.2)
return get_conn(attempt + 1)
else:
raise
connection = get_conn()
yield connection
# connection.close() will fail with an unread_result.
if connection._cnx.unread_result:
connection._cnx.get_rows()
connection.close()
def close_all(self):
self.pool.reset_session()
class Db(object):
"""
Instantiate a db connection.
"""
def __init__(self):
dbconfig = {
"database": "geoDev",
"user": "******",
"password": "******",
"host": "localhost",
#"raw": True,
"pool_name": "geo_pool",
"pool_size": 20,
"pool_reset_session": True
}
try:
self.__conn_pool = MySQLConnectionPool(**dbconfig)
#self.__conn_pool = mysql.connect(**dbconfig)
except Exception:
raise
def __get_session(self):
"""
Returns the private session var.
"""
return self.__conn_pool.get_connection()
#return self.__conn_pool
def __cant_set(self):
"""Raises runtime error."""
raise RuntimeError("Private property cannot be set.")
def __cant_get(self):
"""Raises runtime error."""
raise RuntimeError("Cannot get protected property.")
db_conn = property(__cant_get, __cant_set)
session = property(__get_session, __cant_set)
class ConnectionPool:
INSTANCE = None
def __init__(self):
if self.INSTANCE is not None:
raise ValueError("An instantiation already exists.")
else:
self.__cnxPool = MySQLConnectionPool(pool_name='myPool', pool_size=5, option_files='conf/db_config.conf')
@classmethod
def get_instance(cls):
if cls.INSTANCE is None:
cls.INSTANCE = ConnectionPool()
return cls.INSTANCE
def get_connection(self):
return self.__cnxPool.get_connection()
@classmethod
def pool_close(cls):
cls.INSTANCE = None
class MysqlPooledDataSource(AbstractDataSource):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
# self._conn_params['use_pure'] = True
self._conn_params['pool_name'] = 'test_pool'
self._conn_params['pool_size'] = 10
try:
self._pool = MySQLConnectionPool(**self._conn_params)
self._closed = False
except InterfaceError as e:
log.error('MysqlPooledDataSource connect error')
log.error(formatErrorMsg(e))
raise e
def _inner_close(self):
self._pool.close()
def get_conn(self):
return PooledMySQLConnection(self, self._pool.get_connection())
def release_conn(self, conn):
conn.close()
class DbPlugin(plugins.SimplePlugin):
def __init__(self, bus, config):
plugins.SimplePlugin.__init__(self, bus)
self.engine = None
self._pool = MySQLConnectionPool(pool_size=32, **config)
def start(self):
self.bus.subscribe('before_request', self._create_connection)
self.bus.subscribe('after_request', self._close_connection)
self.bus.log('DB plugin STARTED')
def stop(self):
self.bus.unsubscribe('before_request', self._create_connection)
self.bus.unsubscribe('after_request', self._close_connection)
if self._pool:
del self._pool
self.bus.log('DB plugin STOPPED')
def _create_connection(self):
cp.request.db = self._pool.get_connection()
def _close_connection(self):
cp.request.db.close()
del cp.request.db
class Database:
def __init__(self, **kwargs):
self.pool = MySQLConnectionPool(**kwargs)
def get_connection(self):
return self.pool.get_connection()
def execute(self, sql, multi=False):
try:
con = self.get_connection()
cursor = con.cursor()
cursor.execute(sql, multi=multi)
try:
res = cursor.fetchall()
con.commit()
return res
except Exception as e1:
con.commit()
return cursor.lastrowid
except Exception as e:
print(e)
raise e
finally:
con.close()
def main(argv):
inputfile = ''
database = ''
try:
opts, args = getopt.getopt(argv, "hi:d:", ["ifile=", "db="])
except getopt.GetoptError:
print('import_iaa_iiif.py -i <inputfile> -d <database_name>')
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print('import_iaa_iiif.py -i <inputfile> -d <database_name>')
sys.exit()
elif opt in ("-i", "--ifile"):
inputfile = arg
elif opt in ("-d", "--db"):
database = arg
print('Input file is', inputfile)
print('Database is', database.rstrip(' '))
dbconfig = {
'host': "localhost",
'port': "3307",
'user': "******",
'password': "******",
'database': database
}
cnxpool = MySQLConnectionPool(pool_name="mypool", pool_size=30, **dbconfig)
db = cnxpool.get_connection()
cursor = db.cursor()
unprocessed = []
exclude = [
'1094', '1095', '1096', '1097', '1098', '1099', '1100', '1101', '1102',
'1103', '1104', '1106', '1107', '998'
]
lines = [line.rstrip('\n') for line in open(inputfile)]
for line in lines:
print(line)
m = re.search(
r'P([\*]{0,1}\d{1,5})(\_\d|[a-zA-Z]{0,1}).*Fg(\d{1,5}).*-(R|V)-.*(LR445|LR924|ML445|ML924|_026|_028)',
line)
if m is not None and len(m.groups(0)) == 5:
plate = str(m.group(1)) + m.group(2).replace('_', '/')
fragment = str(m.group(3)).lstrip('0')
side = '0'
if ('R' in str(m.group(4))):
side = '0'
else:
side = '1'
wvStart = '0'
wvEnd = '0'
type = '1'
master = '0'
if ('445' in str(m.group(5))):
wvStart = '445'
wvEnd = '704'
type = '0'
master = '1'
elif ('26' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
type = '2'
elif ('28' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
type = '3'
elif ('924' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
sql = 'SELECT image_catalog_id, edition_catalog_id FROM image_catalog '\
'JOIN image_to_edition_catalog USING(image_catalog_id) '\
'WHERE institution = "IAA" '\
'AND catalog_number_1 = "' + plate + '" '\
'AND catalog_number_2 = "' + fragment + '" '\
'AND catalog_side = ' + side + ';'
cursor.execute(sql)
result_set = cursor.fetchall()
print(plate, fragment, side, result_set)
# I should perhaps have an else clause following
# this conditional that sticks images without edition
# cataloguing data into 4Q9999 or something like that.
if (cursor.rowcount != 0):
imageCatalogId = str(result_set[0][0])
editionCatalogId = str(result_set[0][1])
print(plate, fragment, side, wvStart, wvEnd, type,
imageCatalogId, editionCatalogId)
if any(x not in plate for x in exclude):
sql = 'INSERT IGNORE INTO SQE_image '\
'(image_urls_id, filename, native_width, native_height, dpi, type, wavelength_start, wavelength_end, is_master, image_catalog_id, edition_catalog_id) '\
'VALUES(2,"' + line + '",7216,5412,1215,' + type +',' + wvStart + ',' + wvEnd + ',' + master + ',' + imageCatalogId + ',' + editionCatalogId + ');'
cursor.execute(sql)
db.commit()
print('New id:', cursor.lastrowid)
else:
unprocessed.append(line)
cursor.close()
db.close()
print(unprocessed)
class PooledQueryExecutor:
def __init__(self, host, port, username, password, database, poolsize=1):
self.host = host
self.port = port
self.user = username
self.password = password
self.database = database
self._poolsize = poolsize
self._pool = None
self._pool_mutex = Lock()
self._connection_semaphore = Semaphore(poolsize)
self._init_pool()
def _init_pool(self):
logger.info("Connecting to DB")
dbconfig = {
"host": self.host,
"port": self.port,
"user": self.user,
"password": self.password,
"database": self.database
}
with self._pool_mutex:
self._pool = MySQLConnectionPool(pool_name="db_wrapper_pool",
pool_size=self._poolsize,
**dbconfig)
def close(self, conn, cursor):
"""
A method used to close connection of mysql.
:param conn:
:param cursor:
:return:
"""
cursor.close()
conn.close()
def setup_cursor(self, conn, **kwargs):
conn_args = {}
use_dict = kwargs.get('use_dict', False)
prepared = kwargs.get('prepared', False)
if use_dict:
conn_args['dictionary'] = True
if prepared:
conn_args['prepared'] = True
return conn.cursor(**conn_args)
def execute(self, sql, args=(), commit=False, **kwargs):
"""
Execute a sql, it could be with args and with out args. The usage is
similar with execute() function in module pymysql.
:param sql: sql clause
:param args: args need by sql clause
:param commit: whether to commit
:return: if commit, return None, else, return result
"""
self._connection_semaphore.acquire()
conn = self._pool.get_connection()
cursor = self.setup_cursor(conn, **kwargs)
get_id = kwargs.get('get_id', False)
get_dict = kwargs.get('get_dict', False)
raise_exc = kwargs.get('raise_exc', False)
suppress_log = kwargs.get('suppress_log', False)
# We do not want to display binary data
has_binary = False
disp_args = []
if args and type(args) is tuple:
for value in args:
if isinstance(value, bytes):
disp_args.append(value[:10])
has_binary = True
else:
disp_args.append(value)
else:
disp_args = (args)
try:
multi = False
if type(args) != tuple and args is not None:
args = (args,)
if sql.count(';') > 1:
multi = True
for _ in conn.cmd_query_iter(sql):
pass
else:
cursor.execute(sql, args)
if not has_binary:
logger.debug3(cursor.statement)
else:
logger.debug3("SQL: {}", sql)
logger.debug3("Args: {}", disp_args)
if commit is True:
conn.commit()
if not multi:
affected_rows = cursor.rowcount
if get_id:
return cursor.lastrowid
else:
return affected_rows
else:
if not multi:
res = cursor.fetchall()
if get_dict:
return self.__convert_to_dict(cursor.column_names, res)
return res
except mysql.connector.Error as err:
if not suppress_log:
logger.error("Failed executing query: {} ({}), error: {}", sql, disp_args, err)
if raise_exc:
raise err
return None
except Exception as e:
logger.error("Unspecified exception in dbWrapper: {}", str(e))
return None
finally:
self.close(conn, cursor)
self._connection_semaphore.release()
def executemany(self, sql, args, commit=False, **kwargs):
"""
Execute with many args. Similar with executemany() function in pymysql.
args should be a sequence.
:param sql: sql clause
:param args: args
:param commit: commit or not.
:return: if commit, return None, else, return result
"""
# get connection form connection pool instead of create one.
self._connection_semaphore.acquire()
conn = self._pool.get_connection()
cursor = conn.cursor()
try:
cursor.executemany(sql, args, **kwargs)
if commit is True:
conn.commit()
return None
else:
res = cursor.fetchall()
return res
except mysql.connector.Error as err:
logger.error("Failed executing query: {}", str(err))
return None
except Exception as e:
logger.error("Unspecified exception in dbWrapper: {}", str(e))
return None
finally:
self.close(conn, cursor)
self._connection_semaphore.release()
# ===================================================
# =============== DB Helper Functions ===============
# ===================================================
def __convert_to_dict(self, descr, rows):
desc = [n for n in descr]
return [dict(zip(desc, row)) for row in rows]
def __create_clause(self, col_names, col_subs):
""" Creates a clause and handles lists
Args:
col_names (list): List of column names
col_subs (list): List of column value substitutions
Returns (list):
List of elements for the clause
"""
clause = []
for ind, name in enumerate(col_names):
if col_subs[ind].find(",") != -1:
clause.append("`%s` IN (%s)" % (name, col_subs[ind]))
else:
clause.append("`%s` = %s" % (name, col_subs[ind]))
return clause
def __fix_table(self, table):
""" Encapsualtes the table in backticks
Args:
table (str): Table to encapsulate
Returns (str):
Encapsulated table
"""
split_table = table.split(".")
table_name = ""
if len(split_table) > 2:
raise Exception("Invalid table format, %s" % table)
for name in split_table:
name = name.replace("`", "")
if len(table_name) != 0:
table_name += "."
table_name += "`%s`" % name
return table_name
def __process_literals(self, optype, keyvals, literals):
""" Processes literals and returns a tuple containing all data required for the query
Args:
keyvals (dict): Data to insert into the table
literals (list): Datapoints that should not be escaped
optype (str): Type of operation
Returns (tuple):
(Column names, Column Substitutions, Column Values, Literal Values, OnDuplicate)
"""
column_names = []
column_substituion = []
column_values = []
literal_values = []
ondupe_out = []
for key, value in keyvals.items():
if type(value) is list and optype not in ["DELETE", "UPDATE"]:
raise Exception("Unable to process a list in key %s" % key)
column_names += [key]
# Determine the type of data to insert
sub_op = "%%s"
if key in literals:
sub_op = "%s"
# Number of times to repeat
num_times = 1
if type(value) is list:
num_times = len(value)
column_substituion += [",".join(sub_op for _ in range(0, num_times))]
# Add to the entries
if key in literals:
if type(value) is list:
literal_values += value
else:
literal_values += [value]
else:
if type(value) is list:
column_values += value
else:
column_values += [value]
for key, value in keyvals.items():
if optype == "ON DUPLICATE":
tmp_value = "`%s` = %%s" % key
if key in literals:
tmp_value = tmp_value % value
else:
column_values += [value]
ondupe_out += [tmp_value]
return (column_names, column_substituion, column_values, literal_values, ondupe_out)
def autofetch_all(self, sql, args=(), **kwargs):
""" Fetch all data and have it returned as a dictionary """
return self.execute(sql, args=args, get_dict=True, raise_exc=True, **kwargs)
def autofetch_value(self, sql, args=(), **kwargs):
""" Fetch the first value from the first row """
data = self.execute(sql, args=args, raise_exc=True, **kwargs)
if not data or len(data) == 0:
return None
return data[0][0]
def autofetch_row(self, sql, args=(), **kwargs):
""" Fetch the first row and have it return as a dictionary """
# TODO - Force LIMIT 1
data = self.execute(sql, args=args, get_dict=True, raise_exc=True, **kwargs)
if not data or len(data) == 0:
return {}
return data[0]
def autofetch_column(self, sql, args=None, **kwargs):
""" get one field for 0, 1, or more rows in a query and return the result in a list
"""
data = self.execute(sql, args=args, raise_exc=True, **kwargs)
if data is None:
data = []
returned_vals = []
for row in data:
returned_vals.append(row[0])
return returned_vals
def autoexec_delete(self, table, keyvals, literals=None, where_append=None, **kwargs):
""" Performs a delete
Args:
table (str): Table to run the query against
keyvals (dict): Data to insert into the table
literals (list): Datapoints that should not be escaped
where_append (list): Additional data to append to the query
"""
if literals is None:
literals = []
if where_append is None:
where_append = []
if type(keyvals) is not dict:
raise Exception("Data must be a dictionary")
if type(literals) is not list:
raise Exception("Literals must be a list")
table = self.__fix_table(table)
parsed_literals = self.__process_literals("DELETE", keyvals, literals)
(column_names, column_substituion, column_values, literal_values, _) = parsed_literals
query = "DELETE FROM %s\nWHERE "
where_clauses = where_append + self.__create_clause(column_names, column_substituion)
query += "\nAND ".join(k for k in where_clauses)
literal_values = [table] + literal_values
query = query % tuple(literal_values)
self.execute(query, args=tuple(column_values), commit=True, raise_exc=True, **kwargs)
def autoexec_insert(self, table, keyvals, literals=None, optype="INSERT", **kwargs):
""" Auto-inserts into a table and handles all escaping
Args:
table (str): Table to run the query against
keyvals (dict): Data to insert into the table
literals (list): Datapoints that should not be escaped
optype (str): Type of operation. Valid operations are ["INSERT", "REPLACE", "INSERT IGNORE",
"ON DUPLICATE"]
log (bool): If the query should be logged
logger (logging.logger): Logger that will be used if log = True
Returns (int):
Primary key for the row
"""
if literals is None:
literals = []
optype = optype.upper()
if optype not in ["INSERT", "REPLACE", "INSERT IGNORE", "ON DUPLICATE"]:
raise ProgrammingError("MySQL operation must be 'INSERT', 'REPLACE', 'INSERT IGNORE', 'ON DUPLICATE',"
"got '%s'" % optype)
if type(keyvals) is not dict:
raise Exception("Data must be a dictionary")
if type(literals) is not list:
raise Exception("Literals must be a list")
table = self.__fix_table(table)
parsed_literals = self.__process_literals(optype, keyvals, literals)
(column_names, column_substituion, column_values, literal_values, ondupe_out) = parsed_literals
ondupe_values = []
inital_type = optype
if optype == "ON DUPLICATE":
inital_type = "INSERT"
if inital_type in ["INSERT", "REPLACE"]:
inital_type += " INTO"
rownames = ",".join("`%s`" % k for k in column_names)
rowvalues = ", ".join(k for k in column_substituion)
query = "%s %s\n" \
"(%s)\n" \
"VALUES(%s)" % (inital_type, table, rownames, rowvalues) % tuple(literal_values)
if optype == "ON DUPLICATE":
dupe_out = ",\n".join("%s" % k for k in ondupe_out)
query += "\nON DUPLICATE KEY UPDATE\n" \
"%s" % dupe_out
column_values += ondupe_values
return self.execute(query, args=tuple(column_values), commit=True, get_id=True, raise_exc=True, **kwargs)
def autoexec_update(self, table, set_keyvals, literals=None, where_keyvals=None, where_literals=None, **kwargs):
""" Auto-updates into a table and handles all escaping
Args:
table (str): Table to run the query against
set_keyvals (dict): Data to set
set_literals (list): Datapoints that should not be escaped
where_keyvals (dict): Data used in the where clause
where_literals (list): Datapoints that should not be escaped
"""
if literals is None:
literals = []
if where_keyvals is None:
where_keyvals = {}
if where_literals is None:
where_literals = []
if type(set_keyvals) is not dict:
raise Exception("Set Keyvals must be a dictionary")
if type(literals) is not list:
raise Exception("Literals must be a list")
if type(where_keyvals) is not dict:
raise Exception("Where Keyvals must be a dictionary")
if type(where_literals) is not list:
raise Exception("Literals must be a list")
parsed_set = self.__process_literals("SET", set_keyvals, literals)
(set_col_names, set_col_sub, set_val, set_literal_val, _) = parsed_set
parsed_where = self.__process_literals("UPDATE", where_keyvals, where_literals)
(where_col_names, where_col_sub, where_val, where_literal_val, _) = parsed_where
first_sub = [table]
actual_values = set_val + where_val
set_clause = self.__create_clause(set_col_names, set_col_sub)
first_sub.append(",".join(set_clause) % tuple(set_literal_val))
query = "UPDATE %s\n" \
"SET %s"
if where_col_names:
query += "\nWHERE %s"
where_clause = self.__create_clause(where_col_names, where_col_sub)
first_sub.append("\nAND".join(where_clause) % tuple(where_literal_val))
query = query % tuple(first_sub)
self.execute(query, args=tuple(actual_values), commit=True, raise_exc=True, **kwargs)
class DatabaseManager:
def __init__(self):
print("DatabaseManager: __init__")
self.createConnectionPool()
def createConnectionPool(self):
dbconfig = {
"user": "******",
"password":"******",
"host":'mihass-g-mysql', #set host to mysql using docker run link
"database":'ProjectOrganiser',
"port":'3306'
}
try:
self.cnxpool = MySQLConnectionPool(
pool_name = "mypool",
pool_size = 32,
**dbconfig)
except:
# sleep - hopefully will help - might be that the MySQL
#container is not up and running yet
print("Exception... sleeping for 5 seconds then retry")
tb = traceback.format_exc()
print("tb: " + tb)
time.sleep(5)
# try again
return self.createConnectionPool()
def insert_into_table(self, table_name, my_dict):
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
placeholders = ", ".join(["%s"] * len(my_dict))
stmt = "INSERT INTO `{table}` ({columns}) VALUES ({values});".format(
table=table_name,
columns=",".join(my_dict.keys()),
values=placeholders
)
cursor.execute(stmt, list(my_dict.values()))
connector.commit()
cursor.close()
connector.close()
print("complete")
def update_table(self, table_name, tracker_arr):
connector = self.cnxpool.get_connection()
cursor = connector.cursor()
stmt = ""
for i in tracker_arr:
stmt = "UPDATE `{table}` SET {column} = CONCAT(ifnull({column},'{value}'), '{value}') WHERE {field} = '{conditional}';".format(
table=table_name,
column="logs",
value=json.dumps(i),
field="email",
conditional=i["userID"]
)
cursor.execute(stmt)
connector.commit()
cursor.close()
connector.close()
print("table updated")
def replace_into_table(self, table_name, my_dict):
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
placeholders = ", ".join(["%s"] * len(my_dict))
stmt = "REPLACE INTO `{table}` ({columns}) VALUES ({values});".format(
table=table_name,
columns=",".join(my_dict.keys()),
values=placeholders
)
cursor.execute(stmt, list(my_dict.values()))
connector.commit()
cursor.close()
connector.close()
def select_all_from_table(self, table_name):
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
stmt = "SELECT * FROM `"+table_name+"`;"
#print(stmt)
cursor.execute(stmt)
data = cursor.fetchall()
cursor.close()
connector.close()
return data
def delete_assignment(self, id):
#Inserts a dictionary into table table_name
#print("delete assignment")
id = str(id)
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
stmt = ("DELETE FROM Assignments WHERE Assignments.id="+ id +" LIMIT 1")
#print(stmt)
cursor.execute(stmt)
connector.commit()
cursor.close()
connector.close()
def delete_user(self, email):
#Inserts a dictionary into table table_name
#print("delete user")
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
stmt = ("DELETE FROM Users WHERE Users.email='"+email+"' LIMIT 1")
#print("stmt:")
#print(stmt)
cursor.execute(stmt)
connector.commit()
cursor.close()
connector.close()
def check_password(self, email, password):
#return true if successful
#print("check_password")
result = False
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Users WHERE Users.email='"+email+"' AND Users.password='******'")
#print("query:")
#print(query)
cursor.execute(query)
cursor.fetchall()
if cursor.rowcount == 1:
result = True
cursor.close()
connector.close()
return result
def get_user_info(self, message_data):
#print ("get_user_data")
email = message_data["email"]
print("0")
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Users WHERE Users.email='"+email+"'")
print("1")
#print(query)
cursor.execute(query)
datas = cursor.fetchall()
data = datas[0]
cursor.close()
connector.close()
return data
def get_all_users(self):
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Users")
#print(query)
cursor.execute(query)
users_table = cursor.fetchall()
cursor.close()
connector.close()
#sort it
users = []
for user_table in users_table:
user = {}
user["email"] = user_table["email"]
user["name"] = user_table["name"]
user["surname"] = user_table["surname"]
users.append(user)
return users
def select_submissions_for_user(self, user_id):
print("select_submissions_from_assignments")
user_id = str(user_id)
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Submissions WHERE Submissions.user_id=" + user_id )
#print(query)
cursor.execute(query)
data = cursor.fetchall()
cursor.close()
connector.close()
return data
def add_review(self, data):
print("add_review")
connector = self.cnxpool.get_connection()
# first we need to get the submission
cursor = connector.cursor(dictionary=True)
submission_id = str(data["submission_id"])
query = ("SELECT * FROM Submissions WHERE Submissions.id=" + submission_id)
cursor.execute(query)
submission = cursor.fetchall()[0]
cursor.close()
connector.close()
feedbacks = json.loads(submission["feedbacks"])
feedbacks.append(data)
submission["feedbacks"] = json.dumps(feedbacks)
self.replace_into_table("Submissions", submission)
def update_review(self, data):
print("update_review")
connector = self.cnxpool.get_connection()
# first we need to get the submission
cursor = connector.cursor(dictionary=True)
submission_id = str(data["submission_id"])
query = ("SELECT * FROM Submissions WHERE Submissions.id=" + submission_id)
cursor.execute(query)
submission = cursor.fetchall()[0]
cursor.close()
connector.close()
feedbacks = json.loads(submission["feedbacks"])
for i in range(0,len(feedbacks)):
if feedbacks[i]["reviewer_id"] == data["reviewer_id"]:
if feedbacks[i]["iteration_submitted"] == data["iteration_submitted"]:
#print("FEEDback old:", feedbacks[i])
print("FEEDback new:", data["review"])
feedbacks[i]["review"] = data["review"] #TEST THIS PLACE
print("WHY", feedbacks[i]["review"])
submission["feedbacks"] = json.dumps(feedbacks)
print ("RESULT:", submission["feedbacks"])
self.replace_into_table("Submissions", submission)
class ConnectionPool:
CASSANDRA = 'Cassandra'
REDIS = 'Redis'
MONGO = 'MongoDB'
MYSQL = 'MySQL'
HOST = 'host'
PORT = 'port'
USER = '******'
PASS = '******'
DATABASE = 'database'
KEYSPACE = 'keyspace'
COMMA = ','
configs = None
redisPool = None
mongoPool = None
cassandraPool = None
mysqlPool = None
keyspace = None
def __init__(self):
global configs
self.configs = get_configs()
self.create_cassandra()
# self.create_redis()
self.create_mongo()
# self.create_mysql()
def get_params(self, config):
global HOST, PORT, USER, PASS
return config[self.HOST], config[self.PORT], config[self.USER], config[
self.PASS]
# perform exception handling with logging
def create_cassandra(self):
global CASSANDRA, COMMA, KEYSPACE, cassandraPool, keyspace
config = self.configs[self.CASSANDRA]
hosts, port, user, password = self.get_params(config)
hosts = hosts.strip().split(self.COMMA)
self.keyspace = config[self.KEYSPACE]
self.cassandraPool = Cluster(hosts, port)
def create_redis(self):
global REDIS, redisPool
config = self.configs[self.REDIS]
hosts, port, user, password = self.get_params(config)
self.redisPool = ConnectionPool(host=hosts, port=port)
def create_mysql(self, database=None):
global MYSQL, DATABASE, mysqlPool
config = self.configs[self.MYSQL]
hosts, port, user, password = self.get_params(config)
if not database:
database = config[self.DATABASE]
dbconfig = {
'database': database,
'user': user,
'password': password,
'host': hosts,
'port': port
}
self.mysqlPool = MySQLConnectionPool(pool_size=CNX_POOL_MAXSIZE,
pool_name='POOL',
**dbconfig)
def create_mongo(self):
global MONGO, mongoPool
config = self.configs[self.MONGO]
hosts, port, user, password = self.get_params(config)
self.mongoPool = MongoClient(host=hosts, port=port, connect=False)
def get_redis(self):
return Redis(connection_pool=self.redisPool)
def get_cassandra(self):
return self.cassandraPool.connect(self.keyspace)
def get_mysql(self):
return self.mysqlPool.get_connection()
def get_mongo(self):
return self.mongoPool
class DbWrapperBase(ABC):
def_spawn = 240
def __init__(self, args):
self.application_args = args
self.host = args.dbip
self.port = args.dbport
self.user = args.dbusername
self.password = args.dbpassword
self.database = args.dbname
self.pool = None
self.pool_mutex = Lock()
self.connection_semaphore = Semaphore(
self.application_args.db_poolsize)
self.dbconfig = {
"database": self.database,
"user": self.user,
"host": self.host,
"password": self.password,
"port": self.port
}
self._init_pool()
def _init_pool(self):
logger.info("Connecting pool to DB")
self.pool_mutex.acquire()
self.pool = MySQLConnectionPool(
pool_name="db_wrapper_pool",
pool_size=self.application_args.db_poolsize,
**self.dbconfig)
self.pool_mutex.release()
def _check_column_exists(self, table, column):
query = ("SELECT count(*) "
"FROM information_schema.columns "
"WHERE table_name = %s "
"AND column_name = %s "
"AND table_schema = %s")
vals = (
table,
column,
self.database,
)
return int(self.execute(query, vals)[0][0])
def _check_create_column(self, field):
if self._check_column_exists(field["table"], field["column"]) == 1:
return
alter_query = ("ALTER TABLE {} "
"ADD COLUMN {} {}".format(field["table"],
field["column"],
field["ctype"]))
self.execute(alter_query, commit=True)
if self._check_column_exists(field["table"], field["column"]) == 1:
logger.info("Successfully added '{}.{}' column", field["table"],
field["column"])
return
else:
logger.error("Couldn't create required column {}.{}'",
field["table"], field["column"])
sys.exit(1)
def close(self, conn, cursor):
"""
A method used to close connection of mysql.
:param conn:
:param cursor:
:return:
"""
cursor.close()
conn.close()
def execute(self, sql, args=None, commit=False):
"""
Execute a sql, it could be with args and with out args. The usage is
similar with execute() function in module pymysql.
:param sql: sql clause
:param args: args need by sql clause
:param commit: whether to commit
:return: if commit, return None, else, return result
"""
self.connection_semaphore.acquire()
conn = self.pool.get_connection()
cursor = conn.cursor()
# TODO: consider catching OperationalError
# try:
# cursor = conn.cursor()
# except OperationalError as e:
# logger.error("OperationalError trying to acquire a DB cursor: {}", str(e))
# conn.rollback()
# return None
try:
if args:
cursor.execute(sql, args)
else:
cursor.execute(sql)
if commit is True:
affected_rows = cursor.rowcount
conn.commit()
return affected_rows
else:
res = cursor.fetchall()
return res
except mysql.connector.Error as err:
logger.error("Failed executing query: {}", str(err))
return None
except Exception as e:
logger.error("Unspecified exception in dbWrapper: {}", str(e))
return None
finally:
self.close(conn, cursor)
self.connection_semaphore.release()
def executemany(self, sql, args, commit=False):
"""
Execute with many args. Similar with executemany() function in pymysql.
args should be a sequence.
:param sql: sql clause
:param args: args
:param commit: commit or not.
:return: if commit, return None, else, return result
"""
# get connection form connection pool instead of create one.
self.connection_semaphore.acquire()
conn = self.pool.get_connection()
cursor = conn.cursor()
try:
cursor.executemany(sql, args)
if commit is True:
conn.commit()
return None
else:
res = cursor.fetchall()
return res
except mysql.connector.Error as err:
logger.error("Failed executing query: {}", str(err))
return None
except Exception as e:
logger.error("Unspecified exception in dbWrapper: {}", str(e))
return None
finally:
self.close(conn, cursor)
self.connection_semaphore.release()
@abstractmethod
def auto_hatch_eggs(self):
"""
Check the entire DB for unhatched level 5 eggs and updates the mon ID if there is only one
possible raidmon
"""
pass
@abstractmethod
def db_timestring_to_unix_timestamp(self, timestring):
"""
Converts a DB timestring to a unix timestamp (seconds since epoch)
"""
pass
@abstractmethod
def get_next_raid_hatches(self, delay_after_hatch, geofence_helper=None):
"""
In order to build a priority queue, we need to be able to check for the next hatches of raid eggs
The result may not be sorted by priority, to be done at a higher level!
:return: unsorted list of next hatches within delay_after_hatch
"""
pass
@abstractmethod
def submit_raid(self,
gym,
pkm,
lvl,
start,
end,
type,
raid_no,
capture_time,
unique_hash="123",
MonWithNoEgg=False):
"""
Insert or update raid in DB and send webhook
:return: if raid has all the required values = True, else False
"""
pass
@abstractmethod
def read_raid_endtime(self, gym, raid_no, unique_hash="123"):
"""
Check if a raid already has an endtime and return True/False appropriately
:return: if raid has endtime = True, else False
"""
pass
@abstractmethod
def get_raid_endtime(self, gym, raid_no, unique_hash="123"):
"""
Retrieves the time the requested raid ends - if present
:return: returns (Boolean, Value) with Value being the time or None, Boolean being True/False appropriately
"""
pass
@abstractmethod
def raid_exist(self, gym, type, raid_no, unique_hash="123", mon=0):
"""
Checks if a raid is already present in the DB
:return: returns True/False indicating if a raid is already present in the database
"""
pass
@abstractmethod
def refresh_times(self, gym, raid_no, capture_time, unique_hash="123"):
"""
Update last_modified/last_scanned/updated of a gym
"""
pass
@abstractmethod
def get_near_gyms(self, lat, lng, hash, raid_no, dist, unique_hash="123"):
"""
Retrieve gyms around a given lat, lng within the given dist
:return: returns list of gyms within dist sorted by distance
"""
pass
@abstractmethod
def set_scanned_location(self, lat, lng, capture_time):
"""
Update scannedlocation (in RM) of a given lat/lng
"""
pass
@abstractmethod
def check_stop_quest(self, lat, lng):
"""
Update scannedlocation (in RM) of a given lat/lng
"""
pass
@abstractmethod
def get_gym_infos(self, id=False):
"""
Retrieve all the gyminfos from DB
:return: returns dict containing all the gyminfos contained in the DB
"""
pass
@abstractmethod
def gyms_from_db(self, geofence_helper):
"""
Retrieve all the gyms valid within the area set by geofence_helper
:return: numpy array with coords
"""
pass
@abstractmethod
def update_encounters_from_db(self, geofence_helper, latest=0):
"""
Retrieve all encountered ids inside the geofence.
:return: the new value of latest and a dict like encounter_id: disappear_time
"""
pass
@abstractmethod
def stops_from_db(self, geofence_helper):
"""
Retrieve all the pokestops valid within the area set by geofence_helper
:return: numpy array with coords
"""
pass
@abstractmethod
def quests_from_db(self, GUID=None, timestamp=None):
"""
Retrieve all the pokestops valid within the area set by geofence_helper
:return: numpy array with coords
"""
pass
@abstractmethod
def update_insert_weather(self,
cell_id,
gameplay_weather,
capture_time,
cloud_level=0,
rain_level=0,
wind_level=0,
snow_level=0,
fog_level=0,
wind_direction=0,
weather_daytime=0):
"""
Updates the weather in a given cell_id
"""
pass
@abstractmethod
def submit_mon_iv(self, origin, timestamp, encounter_proto):
"""
Update/Insert a mon with IVs
"""
pass
@abstractmethod
def submit_mons_map_proto(self, origin, map_proto, mon_ids_ivs):
"""
Update/Insert mons from a map_proto dict
"""
pass
@abstractmethod
def submit_pokestops_map_proto(self, origin, map_proto):
"""
Update/Insert pokestops from a map_proto dict
"""
pass
@abstractmethod
def submit_pokestops_details_map_proto(self, map_proto):
"""
Update/Insert pokestop details from a GMO
:param map_proto:
:return:
"""
pass
@abstractmethod
def submit_gyms_map_proto(self, origin, map_proto):
"""
Update/Insert gyms from a map_proto dict
"""
pass
@abstractmethod
def submit_raids_map_proto(self, origin, map_proto):
"""
Update/Insert raids from a map_proto dict
"""
pass
@abstractmethod
def get_pokemon_spawns(self, hours):
"""
Get Pokemon Spawns for dynamic rarity
"""
pass
@abstractmethod
def submit_weather_map_proto(self, origin, map_proto, received_timestamp):
"""
Update/Insert weather from a map_proto dict
"""
pass
@abstractmethod
def download_gym_images(self):
pass
@abstractmethod
def get_to_be_encountered(self, geofence_helper, min_time_left_seconds,
eligible_mon_ids):
pass
@abstractmethod
def stop_from_db_without_quests(self, geofence_helper):
pass
@abstractmethod
def get_raids_changed_since(self, timestamp):
pass
@abstractmethod
def get_mon_changed_since(self, timestamp):
pass
@abstractmethod
def get_quests_changed_since(self, timestamp):
pass
@abstractmethod
def get_gyms_changed_since(self, timestamp):
pass
@abstractmethod
def get_weather_changed_since(self, timestamp):
pass
def statistics_get_pokemon_count(self, days):
pass
@abstractmethod
def statistics_get_gym_count(self, days):
pass
@abstractmethod
def statistics_get_stop_quest(self, days):
pass
def create_hash_database_if_not_exists(self):
"""
In order to store 'hashes' of crops/images, we require a table to store those hashes
"""
logger.debug(
"DbWrapperBase::create_hash_database_if_not_exists called")
logger.debug('Creating hash db in database')
query = (' Create table if not exists trshash ( ' +
' hashid MEDIUMINT NOT NULL AUTO_INCREMENT, ' +
' hash VARCHAR(255) NOT NULL, ' +
' type VARCHAR(10) NOT NULL, ' +
' id VARCHAR(255) NOT NULL, ' +
' count INT(10) NOT NULL DEFAULT 1, ' +
' modify DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ' +
' PRIMARY KEY (hashid))')
self.execute(query, commit=True)
return True
def create_quest_database_if_not_exists(self):
"""
In order to store 'hashes' of crops/images, we require a table to store those hashes
"""
logger.debug(
"DbWrapperBase::create_quest_database_if_not_exists called")
logger.debug('Creating hash db in database')
query = (' Create table if not exists trs_quest ( ' +
' GUID varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,' +
' quest_type tinyint(3) NOT NULL, ' +
' quest_timestamp int(11) NOT NULL,' +
' quest_stardust smallint(4) NOT NULL,' +
' quest_pokemon_id smallint(4) NOT NULL,' +
' quest_reward_type smallint(3) NOT NULL,' +
' quest_item_id smallint(3) NOT NULL,' +
' quest_item_amount tinyint(2) NOT NULL,' +
' quest_target tinyint(3) NOT NULL,' +
' quest_condition varchar(500), ' + ' PRIMARY KEY (GUID), ' +
' KEY quest_type (quest_type))')
self.execute(query, commit=True)
return True
def check_for_hash(self,
imghash,
type,
raid_no,
distance,
unique_hash="123"):
logger.debug("DbWrapperBase::check_for_hash called")
logger.debug("[Crop: {} ({})] check_for_hash: Checking for hash in db",
str(raid_no), str(unique_hash))
query = (
"SELECT id, hash, "
"BIT_COUNT( "
"CONVERT((CONV(hash, 16, 10)), UNSIGNED) "
"^ "
"CONVERT((CONV(%s, 16, 10)), UNSIGNED)) as hamming_distance, type, count, modify "
"FROM trshash "
"HAVING hamming_distance < %s AND type = %s "
"ORDER BY hamming_distance ASC")
vals = (str(imghash), distance, str(type))
res = self.execute(query, vals)
number_of_rows = len(res)
logger.debug(
"[Crop: {} ({})] check_for_hash: Found hashes in database: {}",
str(raid_no), str(unique_hash), str(number_of_rows))
if number_of_rows > 0:
logger.debug("[Crop: {} ({})] check_for_hash: returning found ID",
str(raid_no), str(unique_hash))
for row in res:
logger.debug("[Crop: {} ({})] check_for_hash: ID = {}",
str(raid_no), str(unique_hash), str(row[0]))
logger.debug("DbWrapperBase::check_for_hash done")
return True, row[0], row[1], row[4], row[5]
else:
logger.debug(
"[Crop: {} ({})] check_for_hash: No matching hash found",
str(raid_no), str(unique_hash))
logger.debug("DbWrapperBase::check_for_hash done")
return False, None, None, None, None
def get_all_hash(self, type):
logger.debug("DbWrapperBase::get_all_hash called")
query = ("SELECT id, hash, type, count, modify "
"FROM trshash "
"HAVING type = %s")
vals = (str(type), )
logger.debug(query)
res = self.execute(query, vals)
return res
def insert_hash(self, imghash, type, id, raid_no, unique_hash="123"):
logger.debug("DbWrapperBase::insert_hash called")
if type == 'raid':
distance = 4
else:
distance = 4
double_check = self.check_for_hash(imghash, type, raid_no, distance)
if double_check[0]:
logger.debug(
"[Crop: {} ({})] insert_hash: Already in DB, updating counter",
str(raid_no), str(unique_hash))
# TODO: consider INSERT... ON DUPLICATE KEY UPDATE ??
if not double_check[0]:
query = ("INSERT INTO trshash (hash, type, id) "
"VALUES (%s, %s, %s)")
vals = (str(imghash), str(type), id)
else:
query = ("UPDATE trshash "
"SET count=count+1, modify=NOW() "
"WHERE hash=%s")
vals = (str(imghash), )
self.execute(query, vals, commit=True)
logger.debug("DbWrapperBase::insert_hash done")
return True
def delete_hash_table(self, ids, type, mode=' not in ', field=' id '):
logger.debug("DbWrapperBase::delete_hash_table called")
logger.debug('Deleting old Hashes of type {}', type)
logger.debug('Valid ids: {}', ids)
query = ("DELETE FROM trshash "
"WHERE " + field + " " + mode + " (%s) "
"AND type like %s")
vals = (str(ids), str(type))
logger.debug(query)
self.execute(query, vals, commit=True)
return True
def clear_hash_gyms(self, mons):
logger.debug("DbWrapperBase::clear_hash_gyms called")
data = []
query = ("SELECT hashid "
"FROM trshash "
"WHERE id LIKE '%\"mon\":\"%s\"%' AND type='raid'")
mon_split = mons.split('|')
for mon in mon_split:
args = (int(mon), )
res = self.execute(query, args)
for dbid in res:
data.append(int(dbid[0]))
_mon_list = ','.join(map(str, data))
logger.debug('clearHashGyms: Read Raid Hashes with known Mons')
if len(data) > 0:
query = ('DELETE FROM trshash ' + ' WHERE hashid NOT IN (' +
_mon_list + ')' + ' AND type=\'raid\'')
self.execute(query, commit=True)
logger.info('clearHashGyms: Deleted Raidhashes with unknown mons')
def getspawndef(self, spawn_id):
if not spawn_id:
return False
logger.debug("DbWrapperBase::getspawndef called")
spawnids = ",".join(map(str, spawn_id))
spawnret = {}
query = ("SELECT spawnpoint, spawndef "
"FROM trs_spawn where spawnpoint in (%s)" % (spawnids))
# vals = (spawn_id,)
res = self.execute(query)
for row in res:
spawnret[row[0]] = row[1]
return spawnret
def submit_spawnpoints_map_proto(self, origin, map_proto):
logger.debug(
"DbWrapperBase::submit_spawnpoints_map_proto called with data received by {}",
str(origin))
cells = map_proto.get("cells", None)
if cells is None:
return False
spawnpoint_args, spawnpoint_args_unseen = [], []
spawnids = []
query_spawnpoints = (
"INSERT INTO trs_spawn (spawnpoint, latitude, longitude, earliest_unseen, "
"last_scanned, spawndef, calc_endminsec) "
"VALUES (%s, %s, %s, %s, %s, %s, %s) "
"ON DUPLICATE KEY UPDATE last_scanned=VALUES(last_scanned), "
"earliest_unseen=LEAST(earliest_unseen, VALUES(earliest_unseen)), "
"spawndef=VALUES(spawndef), calc_endminsec=VALUES(calc_endminsec)"
"")
query_spawnpoints_unseen = (
"INSERT INTO trs_spawn (spawnpoint, latitude, longitude, earliest_unseen, last_non_scanned, spawndef) "
"VALUES (%s, %s, %s, %s, %s, %s) "
"ON DUPLICATE KEY UPDATE spawndef=VALUES(spawndef), last_non_scanned=VALUES(last_non_scanned)"
"")
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
dt = datetime.now()
for cell in cells:
for wild_mon in cell["wild_pokemon"]:
spawnids.append(int(str(wild_mon['spawnpoint_id']), 16))
spawndef = self.getspawndef(spawnids)
for cell in cells:
for wild_mon in cell["wild_pokemon"]:
spawnid = int(str(wild_mon['spawnpoint_id']), 16)
lat, lng, alt = S2Helper.get_position_from_cell(
int(str(wild_mon['spawnpoint_id']) + '00000', 16))
despawntime = wild_mon['time_till_hidden']
minpos = self._get_min_pos_in_array()
# TODO: retrieve the spawndefs by a single executemany and pass that...
spawndef_ = spawndef.get(spawnid, False)
if spawndef_:
newspawndef = self._set_spawn_see_minutesgroup(
spawndef_, minpos)
else:
newspawndef = self._set_spawn_see_minutesgroup(
DbWrapperBase.def_spawn, minpos)
last_scanned = None
last_non_scanned = None
if 0 <= int(despawntime) <= 90000:
fulldate = dt + timedelta(milliseconds=despawntime)
earliest_unseen = int(despawntime)
last_scanned = now
calcendtime = fulldate.strftime("%M:%S")
spawnpoint_args.append(
(spawnid, lat, lng, earliest_unseen, last_scanned,
newspawndef, calcendtime))
else:
earliest_unseen = 99999999
last_non_scanned = now
calcendtime = None
spawnpoint_args_unseen.append(
(spawnid, lat, lng, earliest_unseen, last_non_scanned,
newspawndef))
self.executemany(query_spawnpoints, spawnpoint_args, commit=True)
self.executemany(query_spawnpoints_unseen,
spawnpoint_args_unseen,
commit=True)
def submitspsightings(self, spid, encid, secs):
logger.debug("DbWrapperBase::submitspsightings called")
if 0 <= int(secs) <= 90000:
query = (
"INSERT INTO trs_spawnsightings (encounter_id, spawnpoint_id, tth_secs) "
"VALUES (%s, %s, %s)")
vals = (encid, spid, secs)
else:
query = (
"INSERT INTO trs_spawnsightings (encounter_id, spawnpoint_id) "
"VALUES (%s, %s)")
vals = (encid, spid)
self.execute(query, vals, commit=True)
def get_spawn_infos(self):
logger.debug("DbWrapperBase::get_spawn_infos called")
query = (
"SELECT count(spawnpoint), "
"ROUND ( "
"(COUNT(calc_endminsec) + 1) / (COUNT(*) + 1) * 100, 2) AS percent "
"FROM trs_spawn")
found = self.execute(query)
logger.info(
"Spawnpoint statistics: {}, Spawnpoints with detected endtime: {}",
str(found[0][0]), str(found[0][1]))
return float(found[0][1])
def get_detected_spawns(self, geofence_helper):
logger.debug("DbWrapperBase::get_detected_spawns called")
query = ("SELECT latitude, longitude " "FROM trs_spawn")
list_of_coords = []
logger.debug(
"DbWrapperBase::get_detected_spawns executing select query")
res = self.execute(query)
logger.debug("DbWrapperBase::get_detected_spawns result of query: {}",
str(res))
for (latitude, longitude) in res:
list_of_coords.append([latitude, longitude])
if geofence_helper is not None:
logger.debug(
"DbWrapperBase::get_detected_spawns applying geofence")
geofenced_coords = geofence_helper.get_geofenced_coordinates(
list_of_coords)
logger.debug(geofenced_coords)
return geofenced_coords
else:
logger.debug(
"DbWrapperBase::get_detected_spawns converting to numpy")
to_return = np.zeros(shape=(len(list_of_coords), 2))
for i in range(len(to_return)):
to_return[i][0] = list_of_coords[i][0]
to_return[i][1] = list_of_coords[i][1]
return to_return
def get_undetected_spawns(self, geofence_helper):
logger.debug("DbWrapperBase::get_undetected_spawns called")
query = ("SELECT latitude, longitude "
"FROM trs_spawn "
"WHERE calc_endminsec is NULL")
list_of_coords = []
logger.debug(
"DbWrapperBase::get_undetected_spawns executing select query")
res = self.execute(query)
logger.debug(
"DbWrapperBase::get_undetected_spawns result of query: {}",
str(res))
for (latitude, longitude) in res:
list_of_coords.append([latitude, longitude])
if geofence_helper is not None:
logger.debug(
"DbWrapperBase::get_undetected_spawns applying geofence")
geofenced_coords = geofence_helper.get_geofenced_coordinates(
list_of_coords)
logger.debug(geofenced_coords)
return geofenced_coords
else:
logger.debug(
"DbWrapperBase::get_undetected_spawns converting to numpy")
to_return = np.zeros(shape=(len(list_of_coords), 2))
for i in range(len(to_return)):
to_return[i][0] = list_of_coords[i][0]
to_return[i][1] = list_of_coords[i][1]
return to_return
def get_detected_endtime(self, spawn_id):
logger.debug("DbWrapperBase::get_detected_endtime called")
query = ("SELECT calc_endminsec "
"FROM trs_spawn "
"WHERE spawnpoint=%s")
args = (spawn_id, )
found = self.execute(query, args)
if found and len(found) > 0 and found[0][0]:
return str(found[0][0])
else:
return False
def _gen_endtime(self, known_despawn):
hrmi = known_despawn.split(':')
known_despawn = datetime.now().replace(hour=0,
minute=int(hrmi[0]),
second=int(hrmi[1]),
microsecond=0)
now = datetime.now()
if now.minute <= known_despawn.minute:
despawn = now + timedelta(
minutes=known_despawn.minute - now.minute,
seconds=known_despawn.second - now.second)
elif now.minute > known_despawn.minute:
despawn = now + timedelta(hours=1) - timedelta(
minutes=(now.minute - known_despawn.minute),
seconds=now.second - known_despawn.second)
else:
return None
return time.mktime(despawn.timetuple())
def _get_min_pos_in_array(self):
min = datetime.now().strftime("%M")
if 0 <= int(min) < 15:
pos = 4
elif 15 <= int(min) < 30:
pos = 5
elif 30 <= int(min) < 45:
pos = 6
elif 45 <= int(min) < 60:
pos = 7
else:
pos = None
self.__globaldef = pos
return pos
def _set_spawn_see_minutesgroup(self, spawndef, pos):
# b = BitArray([int(digit) for digit in bin(spawndef)[2:]])
b = BitArray(uint=spawndef, length=8)
if pos == 4:
b[0] = 0
b[4] = 1
if pos == 5:
b[1] = 0
b[5] = 1
if pos == 6:
b[2] = 0
b[6] = 1
if pos == 7:
b[3] = 0
b[7] = 1
return b.uint
def check_and_create_spawn_tables(self):
logger.debug("DbWrapperBase::check_and_create_spawn_tables called")
query_trs_spawn = (
'CREATE TABLE IF NOT EXISTS `trs_spawn` ('
'`spawnpoint` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL, '
'`latitude` double NOT NULL, '
'`longitude` double NOT NULL, '
'`spawndef` int(11) NOT NULL DEFAULT "240", '
'`earliest_unseen` int(6) NOT NULL, '
'`last_scanned` datetime DEFAULT NULL, '
'`first_detection` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, '
'`last_non_scanned` datetime DEFAULT NULL, '
'`calc_endminsec` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL, '
'UNIQUE KEY `spawnpoint_2` (`spawnpoint`), '
'KEY `spawnpoint` (`spawnpoint`) '
') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;'
)
query_trs_spawnsightings = (
'CREATE TABLE IF NOT EXISTS `trs_spawnsightings` ('
'`id` int(11) NOT NULL AUTO_INCREMENT, '
'`encounter_id` bigint(20) UNSIGNED NOT NULL, '
'`spawnpoint_id` bigint(20) UNSIGNED NOT NULL, '
'`scan_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, '
'`tth_secs` int(11) DEFAULT NULL, '
'PRIMARY KEY (`id`), '
'KEY `trs_spawnpointdd_spawnpoint_id` (`spawnpoint_id`) '
') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;'
)
self.execute(query_trs_spawn, commit=True)
self.execute(query_trs_spawnsightings, commit=True)
def download_spawns(self):
logger.debug("dbWrapper::download_spawns")
spawn = {}
query = ("SELECT spawnpoint, latitude, longitude, calc_endminsec, "
"spawndef, last_scanned "
"FROM `trs_spawn`")
res = self.execute(query)
for (spawnid, lat, lon, endtime, spawndef, last_scanned) in res:
spawn[spawnid] = {
'lat': lat,
'lon': lon,
'endtime': endtime,
'spawndef': spawndef,
'lastscan': str(last_scanned)
}
return str(json.dumps(spawn, indent=4, sort_keys=True))
def retrieve_next_spawns(self, geofence_helper):
"""
Retrieve the spawnpoints with their respective unixtimestamp that are due in the next 300 seconds
:return:
"""
current_time_of_day = datetime.now().replace(microsecond=0)
logger.debug("DbWrapperBase::retrieve_next_spawns called")
query = (
"SELECT latitude, longitude, spawndef, calc_endminsec FROM trs_spawn WHERE calc_endminsec IS NOT NULL and "
"DATE_FORMAT(STR_TO_DATE(calc_endminsec,'%i:%s'),'%i:%s') between DATE_FORMAT(DATE_ADD(NOW(), "
"INTERVAL if(spawndef=15,60,30) MINUTE),'%i:%s') and DATE_FORMAT(DATE_ADD(NOW(), "
"INTERVAL if(spawndef=15,70,40) MINUTE),'%i:%s')")
res = self.execute(query)
next_up = []
current_time = time.time()
for (latitude, longitude, spawndef, calc_endminsec) in res:
if geofence_helper and not geofence_helper.is_coord_inside_include_geofence(
[latitude, longitude]):
continue
endminsec_split = calc_endminsec.split(":")
minutes = int(endminsec_split[0])
seconds = int(endminsec_split[1])
temp_date = current_time_of_day.replace(minute=minutes,
second=seconds)
if minutes < datetime.now().minute:
temp_date = temp_date + timedelta(hours=1)
if temp_date < current_time_of_day:
# spawn has already happened, we should've added it in the past, let's move on
# TODO: consider crosschecking against current mons...
continue
spawn_duration_minutes = 60 if spawndef == 15 else 30
timestamp = time.mktime(temp_date.timetuple()) - \
spawn_duration_minutes * 60
# check if we calculated a time in the past, if so, add an hour to it...
timestamp = timestamp + 60 * 60 if timestamp < current_time else timestamp
# TODO: consider the following since I am not sure if the prio Q clustering handles stuff properly yet
# if timestamp >= current_time + 600:
# # let's skip monspawns that are more than 10minutes in the future
# continue
next_up.append((timestamp, Location(latitude, longitude)))
return next_up
def submit_quest_proto(self, map_proto):
logger.debug("DbWrapperBase::submit_quest_proto called")
fort_id = map_proto.get("fort_id", None)
if fort_id is None:
return False
if 'challenge_quest' not in map_proto:
return False
quest_type = map_proto['challenge_quest']['quest'].get(
"quest_type", None)
quest_template = map_proto['challenge_quest']['quest'].get(
"template_id", None)
if map_proto['challenge_quest']['quest'].get("quest_rewards", None):
rewardtype = map_proto['challenge_quest']['quest'][
'quest_rewards'][0].get("type", None)
reward = map_proto['challenge_quest']['quest'].get(
"quest_rewards", None)
item = map_proto['challenge_quest']['quest']['quest_rewards'][0][
'item'].get("item", None)
itemamount = map_proto['challenge_quest']['quest'][
'quest_rewards'][0]['item'].get("amount", None)
stardust = map_proto['challenge_quest']['quest']['quest_rewards'][
0].get("stardust", None)
pokemon_id = map_proto['challenge_quest']['quest'][
'quest_rewards'][0]['pokemon_encounter'].get(
"pokemon_id", None)
target = map_proto['challenge_quest']['quest']['goal'].get(
"target", None)
condition = map_proto['challenge_quest']['quest']['goal'].get(
"condition", None)
task = questtask(int(quest_type), str(condition), int(target))
query_quests = (
"INSERT INTO trs_quest (GUID, quest_type, quest_timestamp, quest_stardust, quest_pokemon_id, "
"quest_reward_type, quest_item_id, quest_item_amount, quest_target, quest_condition, quest_reward, "
"quest_task, quest_template) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
"ON DUPLICATE KEY UPDATE quest_type=VALUES(quest_type), quest_timestamp=VALUES(quest_timestamp), "
"quest_stardust=VALUES(quest_stardust), quest_pokemon_id=VALUES(quest_pokemon_id), "
"quest_reward_type=VALUES(quest_reward_type), quest_item_id=VALUES(quest_item_id), "
"quest_item_amount=VALUES(quest_item_amount), quest_target=VALUES(quest_target), "
"quest_condition=VALUES(quest_condition), quest_reward=VALUES(quest_reward), "
"quest_task=VALUES(quest_task), quest_template=VALUES(quest_template)"
)
vals = (fort_id, quest_type, time.time(), stardust, pokemon_id,
rewardtype, item, itemamount, target, str(condition),
str(reward), task, quest_template)
logger.debug(
"DbWrapperBase::submit_quest_proto submitted quest typ {} at stop {}",
str(quest_type), str(fort_id))
self.execute(query_quests, vals, commit=True)
return True
def create_status_database_if_not_exists(self):
logger.debug(
"DbWrapperBase::create_status_database_if_not_exists called")
query = (' Create table if not exists trs_status ( '
'origin VARCHAR(50) NOT NULL , '
' currentPos VARCHAR(50) NULL DEFAULT NULL, '
' lastPos VARCHAR(50) NULL DEFAULT NULL, '
' routePos INT(11) NULL DEFAULT NULL, '
' routeMax INT(11) NULL DEFAULT NULL, '
' routemanager VARCHAR(255) NULL DEFAULT NULL, '
' rebootCounter INT(11) NULL DEFAULT NULL, '
' lastProtoDateTime VARCHAR(50) NULL DEFAULT NULL, '
' lastPogoRestart VARCHAR(50) NULL DEFAULT NULL, '
' init TEXT NOT NULL, '
' rebootingOption TEXT NOT NULL, '
' restartCounter TEXT NOT NULL, '
' PRIMARY KEY (origin))')
self.execute(query, commit=True)
return True
def create_usage_database_if_not_exists(self):
logger.debug(
"DbWrapperBase::create_usage_database_if_not_exists called")
query = ('CREATE TABLE if not exists trs_usage ( '
'usage_id INT(10) AUTO_INCREMENT , '
'instance varchar(100) NULL DEFAULT NULL, '
'cpu FLOAT NULL DEFAULT NULL , '
'memory FLOAT NULL DEFAULT NULL , '
'garbage INT(5) NULL DEFAULT NULL , '
'timestamp INT(11) NULL DEFAULT NULL, '
'PRIMARY KEY (usage_id))')
self.execute(query, commit=True)
return True
def insert_usage(self, instance, cpu, mem, garbage, timestamp):
logger.debug("dbWrapper::insert_usage")
query = (
"INSERT into trs_usage (instance, cpu, memory, garbage, timestamp) VALUES "
"(%s, %s, %s, %s, %s)")
vals = (instance, cpu, mem, garbage, timestamp)
self.execute(query, vals, commit=True)
return
def save_status(self, data):
logger.debug("dbWrapper::save_status")
query = (
"INSERT into trs_status (origin, currentPos, lastPos, routePos, routeMax, "
"routemanager, rebootCounter, lastProtoDateTime, "
"init, rebootingOption, restartCounter) values "
"(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
"ON DUPLICATE KEY UPDATE currentPos=VALUES(currentPos), "
"lastPos=VALUES(lastPos), routePos=VALUES(routePos), "
"routeMax=VALUES(routeMax), routemanager=VALUES(routemanager), "
"rebootCounter=VALUES(rebootCounter), lastProtoDateTime=VALUES(lastProtoDateTime), "
"init=VALUES(init), rebootingOption=VALUES(rebootingOption), restartCounter=VALUES(restartCounter)"
)
vals = (data["Origin"], str(data["CurrentPos"]), str(data["LastPos"]),
data["RoutePos"], data["RouteMax"], data["Routemanager"],
data["RebootCounter"], data["LastProtoDateTime"], data["Init"],
data["RebootingOption"], data["RestartCounter"])
self.execute(query, vals, commit=True)
return
def save_last_reboot(self, origin):
logger.debug("dbWrapper::save_last_reboot")
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
query = (
"insert into trs_status(origin, lastPogoReboot, globalrebootcount) "
"values (%s, %s, %s) "
"ON DUPLICATE KEY UPDATE lastPogoReboot=VALUES(lastPogoReboot), globalrebootcount=(globalrebootcount+1)"
)
vals = (origin, now, 1)
self.execute(query, vals, commit=True)
return
def save_last_restart(self, origin):
logger.debug("dbWrapper::save_last_restart")
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
query = (
"insert into trs_status(origin, lastPogoRestart, globalrestartcount) "
"values (%s, %s, %s) "
"ON DUPLICATE KEY UPDATE lastPogoRestart=VALUES(lastPogoRestart), globalrestartcount=(globalrestartcount+1)"
)
vals = (origin, now, 1)
self.execute(query, vals, commit=True)
return
def download_status(self):
logger.debug("dbWrapper::download_status")
workerstatus = []
query = (
"SELECT origin, currentPos, lastPos, routePos, routeMax, "
"routemanager, rebootCounter, lastProtoDateTime, lastPogoRestart, "
"init, rebootingOption, restartCounter, globalrebootcount, globalrestartcount, lastPogoReboot "
"FROM trs_status")
result = self.execute(query)
for (origin, currentPos, lastPos, routePos, routeMax, routemanager,
rebootCounter, lastProtoDateTime, lastPogoRestart, init,
rebootingOption, restartCounter, globalrebootcount,
globalrestartcount, lastPogoReboot) in result:
status = {
"origin": origin,
"currentPos": currentPos,
"lastPos": lastPos,
"routePos": routePos,
"routeMax": routeMax,
"routemanager": routemanager,
"rebootCounter": rebootCounter,
"lastProtoDateTime": str(lastProtoDateTime),
"lastPogoRestart": str(lastPogoRestart),
"init": init,
"rebootingOption": rebootingOption,
"restartCounter": restartCounter,
"lastPogoReboot": lastPogoReboot,
"globalrebootcount": globalrebootcount,
"globalrestartcount": globalrestartcount
}
workerstatus.append(status)
return str(json.dumps(workerstatus, indent=4, sort_keys=True))
def statistics_get_quests_count(self, days):
logger.debug('Fetching quests count from db')
query_where = ''
query_date = "unix_timestamp(DATE_FORMAT(FROM_UNIXTIME(quest_timestamp), '%y-%m-%d %k:00:00')) * 1000 " \
"as Timestamp"
if days:
days = datetime.utcnow() - timedelta(days=days)
query_where = ' WHERE FROM_UNIXTIME(quest_timestamp) > \'%s\' ' % str(
days)
query = (
"SELECT %s, count(GUID) as Count FROM trs_quest %s "
"group by day(FROM_UNIXTIME(quest_timestamp)), hour(FROM_UNIXTIME(quest_timestamp))"
"order by quest_timestamp" % (str(query_date), str(query_where)))
res = self.execute(query)
return res
def statistics_get_usage_count(self, minutes=120, instance=None):
logger.debug('Fetching usage from db')
query_where = ''
if minutes:
days = datetime.now() - timedelta(minutes=int(minutes))
query_where = ' WHERE FROM_UNIXTIME(timestamp) > \'%s\' ' % str(
days)
if instance is not None:
query_where = query_where + \
' and instance = \'%s\' ' % str(instance)
query = (
"SELECT cpu, memory, garbage, timestamp, instance FROM trs_usage %s "
"order by timestamp" % (str(query_where)))
res = self.execute(query)
return res
class MySQLStorage(object):
DoesNotExist = DoesNotExist
def __init__(self, **kwargs):
self.pool = MySQLConnectionPool(**kwargs)
def get_list_by_path(self, path):
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor(cursor_class=MySQLCursorDict)
cur.execute('SELECT * FROM rules WHERE path=%s', (path,))
rows = cur.fetchall()
if not rows:
raise DoesNotExist(
'Rule with path={path} does not exist.'.format(path=path)
)
return [_rule_from_row(row) for row in rows]
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
def get_by_id(self, _id):
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor(cursor_class=MySQLCursorDict)
cur.execute('SELECT * FROM rules WHERE id=%s', (_id,))
row = cur.fetchone()
if not row:
raise DoesNotExist(
'Rule with id={id} does not exist.'.format(id=_id)
)
return _rule_from_row(row)
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
def get_list(self):
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor(cursor_class=MySQLCursorDict)
cur.execute('SELECT * FROM `rules`')
rows = cur.fetchall()
return [_rule_from_row(row) for row in rows]
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
def create(self, rule):
sql = 'INSERT INTO `rules` (`path`, `request`, `response`)' \
'VALUES (%s, %s, %s)'
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor(cursor_class=MySQLCursorDict)
cur.execute(sql, _row_from_rule(rule))
rule['id'] = cur.lastrowid
con.commit()
return rule
except:
if con is not None:
con.rollback()
raise
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
def delete_by_id(self, _id):
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor()
cur.execute('DELETE FROM rules WHERE id=%s', (_id,))
con.commit()
if cur.rowcount == 0:
raise DoesNotExist(
'Rule with id={id} does not exist.'.format(id=_id)
)
except:
if con is not None:
con.rollback()
raise
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
def clear(self):
con = None
cur = None
try:
con = self.pool.get_connection()
cur = con.cursor()
cur.execute('TRUNCATE TABLE `rules`')
con.commit()
except:
if con is not None:
con.rollback()
raise
finally:
if cur is not None:
cur.close()
if con is not None:
con.close()
class MysqlConnection:
"""
Wrapper object for the MySQL connection.
"""
def __init__(self, host, database, user, password):
"""
Creates a new instance of a MySQL connection with its own connection pool.
:param host: the mysql server
:param database: the database on the mysql server
:param user: the username for the mysql server
:param password: the password for the mysql server
"""
self.host = host
self.database = database
self.user = user
self.password = password
self.logger = Logger(self.__class__.__name__)
self.connection_pool = MySQLConnectionPool(pool_name="pynative_pool",
pool_size=20,
pool_reset_session=True,
host=host,
database=database,
user=user,
password=password)
def create_connection(self):
"""
Opens a new connection to MySQL
:return: the established connection
"""
connection = connect(host=self.host,
database=self.database,
user=self.user,
passwd=self.password,
auth_plugin="mysql_native_password")
return connection
def get_connection(self):
"""
Retrieves a connection from the connection pool or forces to create a new connection. These connections will be
returned to the connection pool, if you close them.
:return: a pooled mysql connection
"""
try:
con = self.connection_pool.get_connection()
if not con.is_connected():
# attempt to fetch a different connection
return self.get_connection()
return con
except PoolError as pe:
# no connection available
self.logger.exception("No connection available: {}".format(pe))
return self._get_new_connection(con)
def _get_new_connection(self):
"""
Creates a new pooled mysql connection and adds it to the existing connection pool.
:return: a pooled mysql connection
"""
connection = self.create_connection()
pooled_connection = PooledMySQLConnection(self.connection_pool,
connection)
return pooled_connection
def cursor(self, prepared=False):
"""
Proxy to create a new cursor.
:param prepared: boolean flag whether this cursor is a prepared statement cursor
:return: a tuple containing a cursor allowing for DB access and the connection
"""
try:
connection = self.connection_pool.get_connection()
cursor = connection.cursor(prepared)
return cursor, connection
except PoolError as pe:
# no connection in pool or pool full
self.logger.exception(
"No connection in pool or pool full: {}".format(pe))
except OperationalError as oe:
# reconnect in case of disconnect after a while
self.logger.exception("Operational error: {}".format(oe))
# and for the geom_tables folder.
import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
dbconfig = {
'host': "localhost",
'port': "3307",
'user': "******",
'password': "******",
'database': "SQE_DEV"
}
cnxpool = MySQLConnectionPool(pool_name="mypool", pool_size=30, **dbconfig)
db = cnxpool.get_connection()
cursor = db.cursor()
sql = 'SHOW TABLES'
cursor.execute(sql)
result_set = cursor.fetchall()
path = '/tmp/backup/'
owner_tables = set()
non_owner_tables = set()
exclude_tables = {
'user', 'user_sessions', 'sqe_session', 'artefact_shape', 'scroll_version',
'external_font_glyph', 'image_to_image_map', 'single_action',
'main_action', 'roi_shape'
}
for result in result_set:
if 'owner' in result[0]:
owner_tables.add(result[0].replace("_owner", ""))
class DatabaseManager:
def __init__(self):
print("DatabaseManager: __init__")
self.createConnectionPool()
def createConnectionPool(self):
dbconfig = {
"user": "******",
"password":"******",
"host":'mysql', #set host to mysql using docker run link
"database":'ProjectOrganiser',
"port":'3306'
}
try:
self.cnxpool = MySQLConnectionPool(
pool_name = "mypool",
pool_size = 32,
**dbconfig)
except:
# sleep - hopefully will help - might be that the MySQL
#container is not up and running yet
print("Exception... sleeping for 5 seconds then retry")
tb = traceback.format_exc()
print("tb: " + tb)
time.sleep(5)
# try again
return self.createConnectionPool()
def insert_into_table(self, table_name, my_dict):
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
columns = ', '.join(my_dict.keys())
placeholders = ", ".join(["%s"] * len(my_dict))
stmt = "INSERT INTO `{table}` ({columns}) VALUES ({values});".format(
table=table_name,
columns=",".join(my_dict.keys()),
values=placeholders
)
cursor.execute(stmt, list(my_dict.values()))
connector.commit()
cursor.close()
connector.close()
def delete_user(self, email):
#Inserts a dictionary into table table_name
print("delete user")
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
stmt = ("DELETE * FROM Users WHERE Users.email='"+email+"'")
print("stmt:")
print(stmt)
cursor.execute(stmt)
connector.commit()
cursor.close()
connector.close()
def check_password(self, email, password):
#return true if successful
print("check_password")
result = False
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Users WHERE Users.email='"+email+"' AND Users.password='******'")
print("query:")
print(query)
cursor.execute(query)
cursor.fetchall()
if cursor.rowcount == 1:
result = True
cursor.close()
connector.close()
return result
def get_user_info(self, message_data):
print ("get_user_data")
email = message_data["email"]
connector = self.cnxpool.get_connection()
cursor = connector.cursor(dictionary=True)
query = ("SELECT * FROM Users WHERE Users.email='"+email+"'")
print(query)
cursor.execute(query)
data = cursor.fetchall()
print ("Cursor : ", data)
message_data["role"] = data[0]["role"]
message_data["name"] = data[0]["name"]
message_data["surname"] = data[0]["surname"]
cursor.close()
connector.close()
def main(argv):
inputfile = ''
database = ''
try:
opts, args = getopt.getopt(argv, "hi:d:", ["ifile=", "db="])
except getopt.GetoptError:
print('test.py -i <inputfile> -d <database_name>')
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print('test.py -i <inputfile> -d <database_name>')
sys.exit()
elif opt in ("-i", "--ifile"):
inputfile = arg
elif opt in ("-d", "--db"):
database = arg
print('Input file is', inputfile)
print('Database is', database.rstrip(' '))
dbconfig = {
'host': "localhost",
'port': "3307",
'user': "******",
'password': "******",
'database': database
}
cnxpool = MySQLConnectionPool(pool_name="mypool", pool_size=10, **dbconfig)
db = cnxpool.get_connection()
cursor = db.cursor()
unprocessed = []
processed = []
lines = [line.rstrip('\n') for line in open(inputfile)]
for line in tqdm(lines):
try:
# req = requests.get('https://www.qumranica.org/image-proxy?address=http://192.114.7.208:8182/iiif/2/' + line + '/info.json')
# resp = req.json()
height = 7216 #resp["height"]
width = 5412 #resp["width"]
m = re.search(
r'([X|\*]{0,1}\d{1,5}.*)(-Fg|Fg)(\d{1,5}).*-(R|V)-.*(LR445|LR924|ML445|ML924|_026|_028)',
line)
if m is not None and len(m.groups(0)) == 5:
plate = str(
m.group(1).replace('Rec', '').replace('Vrs', '').replace(
'_', '/').replace('X', '*').replace('-',
'/').rstrip('/'))
fragment = str(m.group(3)).lstrip('0')
side = '0'
if ('R' in str(m.group(4))):
side = '0'
else:
side = '1'
wvStart = '0'
wvEnd = '0'
imgType = '1'
master = '0'
if ('445' in str(m.group(5))):
wvStart = '445'
wvEnd = '704'
imgType = '0'
master = '1'
elif ('26' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
imgType = '2'
elif ('28' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
imgType = '3'
elif ('924' in str(m.group(5))):
wvStart = '924'
wvEnd = '924'
sql = """
SELECT image_catalog_id FROM image_catalog
WHERE institution = "IAA"
AND catalog_number_1 = %s
AND catalog_number_2 = %s
AND catalog_side = %s;
"""
cursor.execute(sql, (plate, fragment, side))
result_set = cursor.fetchall()
if (len(result_set) == 1):
imageCatalogId = str(result_set[0][0])
sql = """
INSERT INTO SQE_image
(image_urls_id, filename,
dpi, type, wavelength_start, wavelength_end, is_master,
image_catalog_id, native_width, native_height)
VALUES(2,%s,1215,%s,%s,%s,%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE sqe_image_id=LAST_INSERT_ID(sqe_image_id);
"""
cursor.execute(sql, (line, imgType, wvStart, wvEnd, master,
imageCatalogId, width, height))
db.commit()
processed.append("%s %s" % (
line,
cursor.lastrowid,
))
else:
unprocessed.append(line + " " + plate + " " + fragment)
else:
institution = ""
number_1 = ""
number_2 = None
if line[0] == "M" or line[0] == "m":
institution = "PAM"
pam = re.search(r'[M|m](\d{2})(\d{1,5})-', line)
if pam is not None and len(pam.groups()) == 2:
number_1 = str(pam.group(1))
number_2 = str(pam.group(2))
elif line[0] == "I":
institution = "IDAM-IAA"
pam = re.search(r'I(\d{1,7})-', line)
if pam is not None and len(pam.groups()) == 1:
number_1 = str(pam.group(1))
number_2 = None
elif line[0] == "P":
institution = "IAA"
pam = re.search(r'P(\d{1,7}.*?\d{1,2})(-F|_n|F)', line)
if pam is not None and len(pam.groups()) > 0:
number_1 = str(pam.group(1))
number_2 = None
if institution is not "" and number_1 is not "":
sql = """
INSERT INTO image_catalog (institution, catalog_number_1, catalog_number_2, catalog_side)
VALUE (%s, %s, %s, 0)
ON DUPLICATE KEY UPDATE image_catalog_id = LAST_INSERT_ID(image_catalog_id)
"""
cursor.execute(sql, (institution, number_1, number_2))
db.commit()
insert_id = cursor.lastrowid
sql = """
INSERT INTO SQE_image
(image_urls_id, filename,
dpi, type, wavelength_start, wavelength_end, is_master,
image_catalog_id, native_width, native_height)
VALUES(2,%s,800,0,0,0,0,%s,%s,%s)
ON DUPLICATE KEY UPDATE sqe_image_id=LAST_INSERT_ID(sqe_image_id);
"""
cursor.execute(sql, (line, insert_id, width, height))
db.commit()
processed.append("%s %s" % (
line,
cursor.lastrowid,
))
else:
if number_2 is None:
number_2 = ""
unprocessed.append(line + " " + institution + " " +
number_1 + " " + number_2)
except:
unprocessed.append(line)
print("Collecting tiled images.")
sql = """
SELECT image_catalog.institution, image_catalog.catalog_number_1 AS plate, image_catalog.catalog_number_2 AS fragment, IF(image_catalog.catalog_side = 0, 'R', 'V') AS side, filename
FROM SQE_image
JOIN image_catalog USING(image_catalog_id)
WHERE image_catalog_id in (
SELECT image_catalog_id
FROM (
SELECT image_catalog_id
FROM SQE_image
GROUP BY image_catalog_id
HAVING COUNT(sqe_image_id) > 4
) t
)
"""
cursor.execute(sql)
results = cursor.fetchall()
with open('tiled_images.csv', 'w') as f:
f.write("%s\n" % 'institution,plate,fragment,side,filename')
for entry in results:
f.write("%s\n" % ','.join(map(str, entry)))
db.commit()
print("Deleting tiled images.")
sql = """
DELETE FROM SQE_image
WHERE image_catalog_id in (
SELECT image_catalog_id
FROM (
SELECT image_catalog_id
FROM SQE_image
GROUP BY image_catalog_id
HAVING COUNT(sqe_image_id) > 4
) t
)
"""
cursor.execute(sql)
db.commit()
print("Writing the image_to_image_maps.")
sql = """
INSERT IGNORE INTO image_to_image_map (image1_id, image2_id, region_on_image1, region_on_image2)
SELECT sqe1.sqe_image_id,
sqe2.sqe_image_id,
PolygonFromText(CONCAT('POLYGON((0 0,', sqe1.native_width, ' 0,', sqe1.native_width, ' ', sqe1.native_height, ',0 ', sqe1.native_height, ',0 0))')),
PolygonFromText(CONCAT('POLYGON((0 0,', sqe2.native_width, ' 0,', sqe2.native_width, ' ', sqe2.native_height, ',0 ', sqe2.native_height, ',0 0))'))
FROM SQE_image sqe1
JOIN SQE_image sqe2 ON sqe2.image_catalog_id = sqe1.image_catalog_id
AND sqe2.sqe_image_id != sqe1.sqe_image_id
WHERE sqe1.is_master = 1
"""
cursor.execute(sql)
db.commit()
sql = """
INSERT INTO image_to_image_map_author (image_to_image_map_id, user_id)
SELECT image_to_image_map.image_to_image_map_id, (SELECT user_id FROM user WHERE user_name = "sqe_api")
FROM image_to_image_map
LEFT JOIN image_to_image_map_author ON image_to_image_map_author.image_to_image_map_id = image_to_image_map.image_to_image_map_id
AND image_to_image_map_author.user_id = (SELECT user_id FROM user WHERE user_name = "sqe_api")
WHERE image_to_image_map_author.image_to_image_map_id IS NULL
"""
cursor.execute(sql)
db.commit()
cursor.close()
db.close()
with open('import_failed.txt', 'w') as f:
for item in unprocessed:
f.write("%s\n" % item)
with open('import_succeeded.txt', 'w') as f:
for item in processed:
f.write("%s\n" % item)
print("%s unsuccessful matches." % (len(unprocessed), ))
print("%s successful matches." % (len(processed), ))
class DealerDatabase():
'''
Wrapper for the Dealer MySQL database.
'''
def __init__(self,
host: str,
port: int,
database: str,
user: str,
password=None,
pool_size=5):
'''
Create a new DB wrapper class an instantiate the MySQL connection pool.
'''
self.connection_pool = MySQLConnectionPool(pool_name='connection_pool',
pool_size=pool_size,
host=host,
port=port,
user=user,
password=password,
database=database)
self._init_tables()
def add_exchange_order(self, order_id: str, exchange: str, symbol: str,
side: str, qty: str, price: str, ts: str) -> None:
'''
Add an exchange order to the database.
:param id: The exchange-specific order ID.
:param exchange: The name of the exchange the order was posted to.
:param symbol: The market symbol of the order.
:param side: The side of the order (buy/sell).
:param qty: The quantity of the base asset in the order (size).
:param price: The price of the order in the quote asset.
:param ts: The timestamp the order was posted at.
'''
qs = (
"INSERT INTO `exchange_order_history` (`order_id`, `exchange`, `pair`, `side`, `size`, `price`, `time_placed`) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
try:
self._execute_single_query(
qs, (order_id, exchange, symbol, side, qty, price, ts))
except Exception as error:
raise DealerDatabaseError('failed to add exchange order',
error.args)
def add_zero_ex_order(self, quote_id: str, side: str, pair: str, size: str,
price: str, expiration: str, fee: str, status: str,
tx_id: str) -> None:
'''
Add a filled (or failed) 0x order into the database.
:param quote_id: The UUID sent with the quote.
:param side: The side of the quote (bid or ask).
:param pair: The market symbol the quote is for (BASE/QUOTE).
:param size: The size of the quote in units of the base asset.
:param price: The price of the quote in units of the quote asset.
:param expiration: Quote expiration time.
:param fee: The fee sent with the quote.
:param status: Order status at time of insertion.
:param tx_id: The order transaction hash.
'''
qs = (
"INSERT INTO `zero_ex_order_history` ( `quote_id`, `side`, `pair`, `size`, `price`, `expiration`, `fee`, `status`, `transaction_id` ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)")
try:
self._execute_single_query(qs, (quote_id, side, pair, size, price,
expiration, fee, status, tx_id))
except Exception as error:
raise DealerDatabaseError('failed to add 0x order', error.args)
def _init_tables(self) -> None:
exchange_order_table_query = (
"CREATE TABLE IF NOT EXISTS `dealer_db`.`exchange_order_history` "
"(`id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` VARCHAR(45) NOT NULL, `exchange` VARCHAR(45) NOT NULL,"
"`pair` VARCHAR(45) NULL, `side` VARCHAR(45) NULL, `size` VARCHAR(45) NULL, `price` VARCHAR(45) NULL,"
"`time_placed` VARCHAR(45) NULL, `filled_size` VARCHAR(45) NULL, `status` VARCHAR(45) NULL);"
)
zrx_order_table_query = (
"CREATE TABLE IF NOT EXISTS `dealer_db`.`zero_ex_order_history` (`id` INT AUTO_INCREMENT PRIMARY KEY, "
"`quote_id` VARCHAR(45) NOT NULL, `side` VARCHAR(45) NULL, `pair` VARCHAR(45) NULL, `size` VARCHAR(45) NULL, "
"`price` VARCHAR(45) NULL, `expiration` VARCHAR(45) NULL, `fee` VARCHAR(45) NULL, `status` VARCHAR(45) NULL, "
"`transaction_id` VARCHAR(66) NULL);")
try:
connection = self._get_connection()
cursor = self._get_cursor(connection)
self._execute_query(cursor, exchange_order_table_query)
self._execute_query(cursor, zrx_order_table_query)
self._commit(connection)
self._close_cursor(cursor)
self._give_connection(connection)
except Exception as error:
raise DealerDatabaseError('failed to create tables', error.args)
def _execute_single_query(self, query: str, args=None) -> None:
'''
Execute a provided SQL query, but do not return any values (write only).
:param query: The valid SQL query string.
'''
connection = self._get_connection()
cursor = self._get_cursor(connection)
self._execute_query(cursor, query, args)
self._commit(connection)
self._close_cursor(cursor)
self._give_connection(connection)
def _get_connection(self) -> PooledMySQLConnection:
return self.connection_pool.get_connection()
def _give_connection(self, connection: PooledMySQLConnection) -> None:
connection.close()
def _get_cursor(self, connetion: PooledMySQLConnection) -> MySQLCursor:
return connetion.cursor()
def _close_cursor(self, cursor: MySQLCursor) -> None:
cursor.close()
def _execute_query(self,
cursor: MySQLCursor,
query: str,
args=None) -> MySQLCursor:
cursor.execute(query, args)
return cursor
def _commit(self,
connection: PooledMySQLConnection) -> PooledMySQLConnection:
connection.commit()
return connection
class TheFlashServer:
def __init__(self, debug=False):
if debug:
logging.info('Initializing The Flash Server as debug mode')
else:
logging.info('Initializing The Flash Server as production mode')
self.debug = debug
self.app = Flask(__name__)
logging.info('Flask Initialized')
dbconfig = { "user" : settings.db_user_name,
"password" : settings.db_password,
"database" : settings.db_name,
"host" : settings.db_host
}
self.mysql_pool = MySQLConnectionPool(pool_name=None, pool_size=4, pool_reset_session=True, **dbconfig)
logging.info('Database Initialized')
self.redis_pool = redis.ConnectionPool(host=settings.redis_host, port=settings.redis_port, db=0)
logging.info('Redis Initialized')
self.application = tornado.web.Application([
(r"/auth", RequestAuth, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/get_users", RequestGetUsers, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/get_beacons", RequestGetBeacons, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/update_user_pos", RequestUpdateUserPosition, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/register_beacon", RequestRegisterBeacon, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/unregister_beacon", RequestUnregisterBeacon, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool)),
(r"/", RequestPage, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool, page_name="index.html")),
(r"/manage", RequestPage, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool, page_name="manage.html")),
(r"/about", RequestPage, dict(mysql_pool=self.mysql_pool, redis_pool=self.redis_pool, page_name="about.html")),
],
debug=debug,
autoreload=debug,
static_path=settings.static_path
)
logging.info('Application Initialized')
self.init_cache()
def init_cache(self):
conn = None
cursor = None
try:
conn = self.mysql_pool.get_connection()
cursor = conn.cursor()
beacon_cache(conn, cursor, self.redis_pool)
user_cache(conn, cursor, self.redis_pool)
except Exception as e:
logging.error(str(e))
raise
finally:
if cursor is not None:
cursor.close()
if conn is not None:
conn.close()
def start(self):
logging.info('The Flash Server Start')
server = tornado.httpserver.HTTPServer(self.application)
if self.debug:
print '- Start as debug mode'
server.listen(settings.service_port)
else:
server.bind(settings.service_port)
server.start(0)
IOLoop.current().start()
class Registry():
def __init__(self, broker_cloud, mode, db_config, time_inactive_platform, time_update_conf, time_check_platform_active):
self.time_update_conf = time_update_conf
self.time_check_platform_active = time_check_platform_active
self.time_inactive_platform = time_inactive_platform
self.cnxpool = MySQLConnectionPool(pool_name="mypool", pool_size=32, **db_config)
self.mode = mode
self.producer_connection = Connection(broker_cloud)
self.consumer_connection = Connection(broker_cloud)
self.exchange = Exchange("IoT", type="direct")
def update_config_changes_by_platform_id(self, platform_id):
message = {
'reply_to': 'driver.response.registry.api_check_configuration_changes',
'platform_id': platform_id
}
# send request to Driver
queue = Queue(name='driver.request.api_check_configuration_changes', exchange=self.exchange,
routing_key='driver.request.api_check_configuration_changes')
routing_key = 'driver.request.api_check_configuration_changes'
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message),
exchange=self.exchange.name,
routing_key=routing_key,
declare=[queue],
retry=True
)
def check_platform_active(self):
# print("Check active platform")
list_platforms = self.get_list_platforms("active")
for platform in list_platforms:
if (time.time() - platform['last_response']) > self.time_inactive_platform:
# print("Mark inactive platform: {}".format(platform['platform_id']))
self.mark_inactive(str(platform['platform_id']))
self.send_notification_to_collector()
threading.Timer(self.time_check_platform_active, self.check_platform_active).start()
def update_changes_to_db(self, new_info, platform_id):
# print("Update change of {} to database".format(platform_id))
now_info = self.get_things_by_platform_id(platform_id, "all", "all")
inactive_things = now_info[:]
new_things = new_info[:]
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
for now_thing in now_info:
for new_thing in new_info:
if now_thing["thing_global_id"] == new_thing["thing_global_id"]:
if (now_thing['thing_name'] != new_thing['thing_name'] \
or now_thing['thing_type'] != new_thing['thing_type'] \
or now_thing['location'] != new_thing['location']):
cursor_1.execute(
"""UPDATE Thing SET thing_name=%s, thing_type=%s, location=%s, thing_status=%s WHERE thing_global_id=%s""",
(new_thing["thing_name"], new_thing["thing_type"], new_thing["location"], 'active',
now_thing["thing_global_id"]))
if now_thing['thing_status'] == 'inactive':
cursor_1.execute("""UPDATE Thing SET thing_status=%s WHERE thing_global_id=%s""",
('active', now_thing["thing_global_id"]))
inactive_items = now_thing["items"][:]
new_items = new_thing['items'][:]
for now_item in now_thing["items"]:
for new_item in new_thing["items"]:
if now_item["item_global_id"] == new_item["item_global_id"]:
if (now_item["item_name"] != new_item["item_name"] or
now_item["item_type"] != new_item["item_type"] or
now_item['can_set_state'] != new_item['can_set_state']):
cursor_1.execute(
"""UPDATE Item SET item_name=%s, item_type=%s, can_set_state=%s WHERE item_global_id=%s""",
(new_item["item_name"], new_item["item_type"], new_item["can_set_state"],
now_item['item_global_id']))
if now_item['item_status'] == 'inactive':
cursor_1.execute("""UPDATE Item SET item_status=%s WHERE item_global_id=%s""",
('active', now_item['item_global_id']))
inactive_items.remove(now_item)
new_items.remove(new_item)
break
if len(inactive_items) != 0:
# Item inactive
# print("Item inactive")
for item_inactive in inactive_items:
cursor_1.execute("""UPDATE Item SET item_status=%s WHERE item_global_id=%s""",
("inactive", item_inactive['item_global_id']))
if len(new_items) != 0:
# print("New Item ")
for item in new_items:
cursor_1.execute("""INSERT INTO Item VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(item['item_global_id'], new_thing['thing_global_id'], item['item_name'],
item['item_type'], item['item_local_id'], item['can_set_state'],
"active"))
inactive_things.remove(now_thing)
new_things.remove(new_thing)
break
if len(inactive_things) != 0:
# Thing inactive
# print("Thing inactive")
for thing_inactive in inactive_things:
cursor_1.execute("""UPDATE Thing SET thing_status=%s WHERE thing_global_id=%s""",
("inactive", thing_inactive['thing_global_id']))
for item_inactive in thing_inactive['items']:
cursor_1.execute("""UPDATE Item SET item_status=%s WHERE item_global_id=%s""",
("inactive", item_inactive['item_global_id']))
if len(new_things) != 0:
# New things
# print("New Thing")
for thing in new_things:
cursor_1.execute("""INSERT INTO Thing VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(thing['thing_global_id'], platform_id, thing['thing_name'],
thing['thing_type'], thing['thing_local_id'], thing['location'], "active"))
# print('Updated Things')
for item in thing['items']:
# print("{}".format(item['item_global_id']))
cursor_1.execute("""INSERT INTO Item VALUES (%s,%s,%s,%s,%s,%s,%s)""",
(item['item_global_id'], thing['thing_global_id'], item['item_name'],
item['item_type'], item['item_local_id'], item['can_set_state'], "active"))
# print('Updated Items')
cnx_1.commit()
cursor_1.close()
cnx_1.close()
def get_things_by_platform_id(self, platform_id, thing_status, item_status):
# print("Get things in platform_id: {}".format(platform_id))
things_in_system = self.get_things(thing_status, item_status)
things_in_platform = []
for thing in things_in_system:
if thing['platform_id'] == platform_id:
things_in_platform.append(thing)
return things_in_platform
def handle_configuration_changes(self, body, message):
cnx_2 = self.get_connection_to_db()
cursor_2 = cnx_2.cursor()
body = json.loads(body)
platform_id = body['platform_id']
if body['have_change'] == False:
# print('Platform have Id: {} no changes'.format(platform_id))
cursor_2.execute("""SELECT platform_status FROM Platform WHERE platform_id=%s""",
(str(platform_id),))
platform_status = cursor_2.fetchone()[0]
if platform_status == 'active':
pass
else:
new_info = body['new_info']
self.update_changes_to_db(new_info, platform_id)
self.send_notification_to_collector()
else:
print('Platform have Id: {} changed the configuration file'.format(platform_id))
new_info = body['new_info']
self.update_changes_to_db(new_info, platform_id)
#Update last_response and status of platform
cursor_2.execute("""UPDATE Platform SET last_response=%s, platform_status=%s WHERE platform_id=%s""",
(time.time(), 'active', platform_id))
cnx_2.commit()
cursor_2.close()
cnx_2.close()
def mark_inactive(self, platform_id):
print('Mark Thing and Item inactive')
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
cursor_1.execute("""SELECT thing_global_id FROM Thing WHERE platform_id = %s""", (str(platform_id),))
list_thing_global_id = cursor_1.fetchall()
# print('List_thing {}'.format(list_thing_global_id))
for thing_global_id in list_thing_global_id:
# print(thing_global_id[0])
# thing_global_id[0] để lấy ra kết quả. Còn thing_global_id vẫn là list.
# VD: ('d32d30b4-8917-4eb1-a273-17f7f440b240/sensor.humidity',)
cursor_1.execute("""UPDATE Item SET item_status=%s WHERE thing_global_id=%s""",
("inactive", str(thing_global_id[0])))
cnx_1.commit()
cursor_1.execute("""UPDATE Thing SET thing_status=%s WHERE platform_id=%s""", ("inactive", str(platform_id)))
cursor_1.execute("""UPDATE Platform SET platform_status=%s WHERE platform_id=%s""",
("inactive", str(platform_id)))
cnx_1.commit()
cursor_1.close()
cnx_1.close()
def update_all_config_changes(self):
# print('Run Update All Configuration Changes')
list_platforms = self.get_list_platforms("active")
for platform in list_platforms:
self.update_config_changes_by_platform_id(platform['platform_id'])
threading.Timer(self.time_update_conf, self.update_all_config_changes).start()
def get_list_platforms(self, platform_status):
# print('Get list platforms')
list_platforms = []
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
if platform_status == "active":
cursor_1.execute("""SELECT platform_id, platform_name, host, port, last_response, platform_status
FROM Platform WHERE platform_status='active'""")
elif platform_status == "inactive":
cursor_1.execute("""SELECT platform_id, platform_name, host, port, last_response, platform_status
FROM Platform WHERE platform_status='inactive'""")
elif platform_status == "all":
cursor_1.execute("""SELECT platform_id, platform_name, host, port, last_response, platform_status
FROM Platform""")
else:
return list_platforms
rows = cursor_1.fetchall()
for row in rows:
list_platforms.append({
"platform_id": row[0],
"platform_name": row[1],
"host": row[2],
"port": row[3],
"last_response": row[4],
"platform_status": row[5]
})
# print(list_platforms)
cursor_1.close()
cnx_1.close()
return list_platforms
def api_get_list_platforms(self, body, message):
print("API get list platform with platform_status")
platform_status = json.loads(body)['platform_status']
reply_to = json.loads(body)['reply_to']
message_response = {
"list_platforms": self.get_list_platforms(platform_status)
}
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message_response),
exchange=self.exchange.name,
routing_key=reply_to,
retry=True
)
def api_add_platform(self, body, message):
body = json.loads(body)
print ("api_add_platform")
print (body)
host = body['host']
port = body['port']
platform_name = body['platform_name']
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
if "platform_id" in body:
platform_id = body['platform_id']
print("Platform {} have id: {} come back to system".format(platform_name, platform_id))
cursor_1.execute("""UPDATE Platform SET platform_status=%s, last_response=%s WHERE platform_id=%s""",
('active', time.time(), platform_id))
else:
platform_id = str(uuid.uuid4())
print('Add {} have address {}:{} to system '.format(platform_name, host, port))
print('Generate id for this platform : ', platform_id)
cursor_1.execute("""INSERT INTO Platform VALUES (%s,%s,%s,%s,%s,%s)""",
(platform_id, platform_name, host, port, time.time(), "active"))
message_response = {
'platform_id': platform_id,
'host': host,
'port': port,
'platform_name': platform_name
}
# check connection and publish message
queue_response = Queue(name='registry.response.driver.api_add_platform', exchange=self.exchange,
routing_key='registry.response.driver.api_add_platform')
routing_key = 'registry.response.driver.api_add_platform'
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message_response),
exchange=self.exchange.name,
routing_key=routing_key,
declare=[queue_response],
retry=True
)
cnx_1.commit()
cursor_1.close()
cnx_1.close()
self.send_notification_to_collector()
def get_things(self, thing_status, item_status):
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
query_thing = ""
query_item = ""
if thing_status == 'active':
query_thing = """SELECT Thing.platform_id, Thing.thing_global_id, Thing.thing_name,
Thing.thing_type, Thing.location, Thing.thing_local_id, Thing.thing_status
FROM Thing
WHERE Thing.thing_status = 'active'; """
elif thing_status == 'inactive':
query_thing = """SELECT Thing.platform_id, Thing.thing_global_id, Thing.thing_name,
Thing.thing_type, Thing.location, Thing.thing_local_id, Thing.thing_status
FROM Thing
WHERE Thing.thing_status = 'inactive'; """
elif thing_status == 'all':
query_thing = """SELECT Thing.platform_id, Thing.thing_global_id, Thing.thing_name,
Thing.thing_type, Thing.location, Thing.thing_local_id, Thing.thing_status
FROM Thing;"""
if item_status == 'active':
query_item = """SELECT Item.thing_global_id, Item.item_global_id, Item.item_name,
Item.item_type, Item.can_set_state, Item.item_local_id, Item.item_status
FROM Item
WHERE Item.item_status='active';"""
elif item_status == 'inactive':
query_item = """SELECT Item.thing_global_id, Item.item_global_id, Item.item_name,
Item.item_type, Item.can_set_state, Item.item_local_id, Item.item_status
FROM Item
WHERE Item.item_status='inactive';"""
elif item_status == 'all':
query_item = """SELECT Item.thing_global_id, Item.item_global_id, Item.item_name,
Item.item_type, Item.can_set_state, Item.item_local_id, Item.item_status
FROM Item;"""
cursor_1.execute(query_thing)
thing_rows = cursor_1.fetchall()
cursor_1.execute(query_item)
item_rows = cursor_1.fetchall()
cursor_1.close()
cnx_1.close()
things = []
for thing in thing_rows:
temp_thing = {
'platform_id': thing[0],
'thing_global_id': thing[1],
'thing_name': thing[2],
'thing_type': thing[3],
'location': thing[4],
'thing_local_id': thing[5],
'thing_status': thing[6],
'items': []
}
for item in item_rows:
if item[0] == thing[1]:
temp_item = {
'item_global_id': item[1],
'item_name': item[2],
'item_type': item[3],
'can_set_state': item[4],
'item_local_id': item[5],
'item_status': item[6]
}
temp_thing['items'].append(temp_item)
things.append(temp_thing)
return things
def get_thing_by_global_id(self, thing_global_id):
cnx_1 = self.get_connection_to_db()
cursor_1 = cnx_1.cursor()
cursor_1.execute("""SELECT Thing.platform_id, Thing.thing_global_id, Thing.thing_name,
Thing.thing_type, Thing.location, Thing.thing_local_id, Thing.thing_status
FROM Thing
WHERE Thing.thing_global_id=%s; """, (thing_global_id,))
thing_rows = cursor_1.fetchall()
cursor_1.execute("""SELECT Item.thing_global_id, Item.item_global_id, Item.item_name,
Item.item_type, Item.can_set_state, Item.item_local_id, Item.item_status
FROM Item
WHERE Item.thing_global_id=%s;""", (thing_global_id,))
item_rows = cursor_1.fetchall()
cursor_1.close()
cnx_1.close()
things = []
for thing in thing_rows:
temp_thing = {
'platform_id': thing[0],
'thing_global_id': thing[1],
'thing_name': thing[2],
'thing_type': thing[3],
'location': thing[4],
'thing_local_id': thing[5],
'thing_status': thing[6],
'items': []
}
for item in item_rows:
if item[0] == thing[1]:
temp_item = {
'item_global_id': item[1],
'item_name': item[2],
'item_type': item[3],
'can_set_state': item[4],
'item_local_id': item[5],
'item_status': item[6]
}
temp_thing['items'].append(temp_item)
things.append(temp_thing)
return things
def api_get_things(self, body, message):
print('API Get All Things')
reply_to = json.loads(body)['reply_to']
thing_status = json.loads(body)['thing_status']
item_status = json.loads(body)['item_status']
things = self.get_things(thing_status, item_status)
message_response = {
'things': things
}
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message_response),
exchange=self.exchange.name,
routing_key=reply_to,
retry=True
)
def api_get_thing_by_global_id(self, body, message):
print('API Get Thing by thing_global_id')
reply_to = json.loads(body)['reply_to']
thing_global_id = json.loads(body)['thing_global_id']
things = self.get_thing_by_global_id(thing_global_id)
message_response = {
'things': things
}
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message_response),
exchange=self.exchange.name,
routing_key=reply_to,
retry=True
)
def api_get_things_by_platform_id(self, body, message):
print('Get Thing by platform_id')
reply_to = json.loads(body)['reply_to']
platform_id = json.loads(body)['platform_id']
thing_status = json.loads(body)['thing_status']
item_status = json.loads(body)['item_status']
things = self.get_things_by_platform_id(platform_id, thing_status, item_status)
message_response = {
'things': things
}
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message_response),
exchange=self.exchange.name,
routing_key=reply_to,
retry=True
)
def get_connection_to_db(self):
while True:
try:
# print("Get connection DB")
connection = self.cnxpool.get_connection()
return connection
except:
# print("Can't get connection DB")
pass
def send_notification_to_collector(self):
print('Send notification to Collector')
message = {
'notification': 'Have Platform_id change'
}
queue = Queue(name='collector.request.notification', exchange=self.exchange,
routing_key='collector.request.notification')
routing_key = 'collector.request.notification'
self.producer_connection.ensure_connection()
with Producer(self.producer_connection) as producer:
producer.publish(
json.dumps(message),
exchange=self.exchange.name,
routing_key=routing_key,
declare=[queue],
retry=True
)
def run(self):
queue_get_things = Queue(name='registry.request.api_get_things', exchange=self.exchange,
routing_key='registry.request.api_get_things', ttl=20)
queue_get_list_platforms = Queue(name='registry.request.api_get_list_platforms', exchange=self.exchange,
routing_key='registry.request.api_get_list_platforms')
queue_add_platform = Queue(name='registry.request.api_add_platform', exchange=self.exchange,
routing_key='registry.request.api_add_platform')
queue_check_config = Queue(name='driver.response.registry.api_check_configuration_changes', exchange=self.exchange,
routing_key='driver.response.registry.api_check_configuration_changes')
queue_get_thing_by_global_id = Queue(name='registry.request.api_get_thing_by_global_id', exchange=self.exchange,
routing_key='registry.request.api_get_thing_by_global_id')
queue_get_things_by_platform_id = Queue(name='registry.request.api_get_things_by_platform_id',
exchange=self.exchange,
routing_key='registry.request.api_get_things_by_platform_id')
if self.mode == 'PULL':
self.update_all_config_changes()
self.check_platform_active()
while 1:
try:
self.consumer_connection.ensure_connection(max_retries=1)
with nested(Consumer(self.consumer_connection, queues=queue_get_things_by_platform_id,
callbacks=[self.api_get_things_by_platform_id], no_ack=True),
Consumer(self.consumer_connection, queues=queue_get_thing_by_global_id,
callbacks=[self.api_get_thing_by_global_id], no_ack=True),
Consumer(self.consumer_connection, queues=queue_add_platform, callbacks=[self.api_add_platform],
no_ack=True),
Consumer(self.consumer_connection, queues=queue_get_things, callbacks=[self.api_get_things],
no_ack=True),
Consumer(self.consumer_connection, queues=queue_get_list_platforms,
callbacks=[self.api_get_list_platforms], no_ack=True),
Consumer(self.consumer_connection, queues=queue_check_config,
callbacks=[self.handle_configuration_changes], no_ack=True)):
while True:
self.consumer_connection.drain_events()
except (ConnectionRefusedError, exceptions.OperationalError):
print('Connection lost')
except self.consumer_connection.connection_errors:
print('Connection error')
class DatabaseModule(mp_module.MPModule):
def __init__(self, mpstate):
super(DatabaseModule, self).__init__(mpstate, "database", "database module", public=True)
while True:
try:
self.pool = MySQLConnectionPool(pool_name="mavproxy_pool", pool_size=30, host=os.environ.get("DATABASE_HOST", "localhost"), user="******", passwd="aeolus", db="mavproxy", auth_plugin='mysql_native_password')
break
except mysql.connector.Error as e:
logger.error("Cannot connect to MYSql database. Please start MYSql ('mysql.server start') or run setup_db.sh if MYSql has not been installed: " + str(e))
logger.info("Retrying connection in 10 seconds...")
time.sleep(10)
self.autopilot_boot_time = None
self.time_inconsistent = False
if not mpstate.airapi:
self.interop_instance = interop.get_instance()
self.interop_instance.bind_to_new_obstacle(self.handle_interop_obstacles)
self.Heartbeat = namedtuple('Heartbeat', ['time', 'plane_link'])
self.heartbeat = self.Heartbeat(0, False)
self.GPS_INT = namedtuple('GlobalPositionInt', ['time', 'rel_alt', 'asl_alt', 'lat', 'lon', 'heading', 'groundvx', 'groundvy', 'groundvz'])
self.global_position_int = self.GPS_INT(0, 0, 0, 0, 0, 0, 0, 0, 0)
self.GPS_STATUS = namedtuple('GPSStatus', ['time', 'satellite_number'])
self.GPSStatus = self.GPS_STATUS(0, 0)
self.VFR_HUD = namedtuple('VFRHUD', ['time', 'airvx', 'airvy', 'airvz', 'speed', 'climb', 'throttle'])
self.vfr_hud = self.VFR_HUD(0, 0, 0, 0, 0, 0, 0)
self.FLIGHT_TIME = namedtuple("FlightTime", ['time', 'time_start', 'is_flying'])
self.flight_time = self.FLIGHT_TIME(0, None, False)
self.WIND = namedtuple('Wind', ['time', 'windvx', 'windvy', 'windvz'])
self.wind = self.WIND(0, 0, 0, 0)
self.ATTITUDE = namedtuple("Attitude", ['time', 'roll', 'pitch', 'yaw', 'rollspeed', 'pitchspeed', 'yawspeed'])
self.attitude = self.ATTITUDE(0, 0, 0, 0, 0, 0, 0)
self.BATTERY = namedtuple("Battery", ['time', 'batterypct', 'batteryvoltage', 'batterycurrent'])
self.battery = self.BATTERY(0, 0, 0, 0)
self.MAV_INFO = namedtuple("MAVInfo", ['time', 'text', 'severity'])
self.mav_info = self.MAV_INFO(0, "", 0)
self.MAV_WARNING = namedtuple("MAVWarning", ['time', 'text', 'severity'])
self.mav_warning = self.MAV_WARNING(0, "", 0)
self.MAV_ERROR = namedtuple("MAVError", ['time', 'text', 'severity'])
self.mav_error = self.MAV_ERROR(0, "", 0)
self.CAMERA_FEEDBACK = namedtuple("CameraFeedback", ['ds_time', 'lat', 'lon', 'alt_rel', 'roll', 'pitch', 'yaw', 'mavproxy_time'])
self.mode = "MAV"
self.mode2num = {"UNKNOWN": -1, "MAV": -1, "MANUAL": 0, "CIRCLE": 1, "STABILIZE": 2, "TRAINING": 3, "ACRO": 4, "FBWA": 5, "FBWB": 6, "CRUISE": 7, "AUTOTUNE": 8, "AUTO": 10, "TL": 11, "LOITER": 12, "AVOID_ADSB": 14, "GUIDED": 15, "QSTABILIZE": 17, "QHOVER": 18, "QLOITER": 19, "QLAND": 20, "RTL" : 21, "INITIALISING" : 22}
self.num2mode = {n: mode for mode, n in self.mode2num.items()}
self.num2mode[-1] = "MAV"
self.SIGNAL = namedtuple("Signal", ['time', 'signal_strength'])
self.signal = self.SIGNAL(0, 0)
self.STATIONARY_OBSTACLE = namedtuple("STATIONARY_OBSTACLES", ['time', 'lat', 'lon', 'cylinder_radius', 'cylinder_height'])
self.WAYPOINT = namedtuple("WAYPOINT", ['command', 'current', 'param1', 'param2', 'param3', 'param4', 'lat', 'lon', 'alt', 'index'])
self.wps = [] # these are mavwaypoints. use get_wps to get a list of WAYPOINT objects
self.current_waypoint_index = 0
def _get_packet_time(self, msg):
# # If the packet includes a time since boot, use autopilot boot time
# # Better to ask forgiveness than permission
# try:
# t = msg.time_boot_ms/1000.0 + self.autopilot_boot_time
# if not self.set_time and self.mpstate.airapi:
# self.set_time = True
# time_string = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))
# logger.info("Syncing system time to Autopilot time ({})".format(time_string))
# subprocess.call("(sudo /usr/local/bin/set-time.sh \"" + time_string + "\")", shell=True)
# if abs(t - time.time()) > TIME_INCONSISTENCY_MAX:
# if not self.time_inconsistent:
# self.time_inconsistent = True
# print("Autopilot time and computer system time are inconsistent")
# except (AttributeError, TypeError):
# # Note that this also catches the case where we haven't set self.autopilot_boot_time yet
# # The most likely reason for this is that the GPS isn't working - we still want to store the packet though
# # So we resort to returning the current system time
# t = time.time()
t = time.time()
return datetime.fromtimestamp(t)
def _db_connector(func):
def wrapper(self, arg=None):
try:
conn = self.pool.get_connection()
cursor = conn.cursor(buffered=True) # set so we can fetchone when we don't want all the results
r_value = None
try:
r_value = func(self, cursor, arg)
except mysql.connector.IntegrityError as e:
pass
except mysql.connector.Error as e:
logger.error("MYSQl error in " + str(func) + ": " + str(e))
conn.commit()
conn.close()
return r_value
except Exception as e:
logger.error(traceback.format_exc())
return wrapper
# @param data: [ [t1, t1_data1, t1_data2, t1_data3], [t2, t2_data1, t2_data2, t2_data3], [t3, t3_data1, t3_data2, t3_data3] ]
# @param goal_time: time to interopolate to in seconds.
# @returns [tgoal_data1, tgoal_data2, tgoal_data3]
def interpolate(self, data, req_time):
if len(data) < 1 or len(data[0]) < 1:
logger.debug("Empty data, cannot interpolate")
return data
data = data[:10] # don't use tons of old data
interpolated = [req_time]
times = [d[0].strftime("%s") for d in data] # get the unix time version of each datetime
for i in range(1, len(data[0])):
values = [d[i] for d in data]
interpolated.append(np.interp(req_time, times, values))
return interpolated
# Gets data close by a to a particular time with linear backoff for interpolation
def get_nearby_data(self, cursor, req_time, query, fetch_one=False):
modifier = 0
closest = []
while not closest and (TIME_VARIANCE+modifier) < TIME_VARIANCE_MAX:
# print modifier
time_start = req_time - TIME_VARIANCE - modifier
time_end = req_time + TIME_VARIANCE + modifier
cursor.execute(query, (time_start, time_end, req_time))
if not fetch_one:
closest = cursor.fetchall()
else:
closest = cursor.fetchone()
modifier += 1
return closest
def make_interpolated_query(self, cursor, req_time, query):
closest = self.get_nearby_data(cursor, req_time, query)
return self.interpolate(closest, req_time)
# returns a list of waypoints as a dict
def mavwp_to_wps(self, wplist):
def wp2dict(wp):
return self.WAYPOINT(wp.command, wp.current, float(wp.param1), float(wp.param2), float(wp.param3), float(wp.param4), float(wp.x), float(wp.y), float(wp.z), wp.seq)._asdict()
return map(wp2dict, wplist)
def get_wps(self):
return self.mavwp_to_wps(self.wps)
# ###################
#
#
# HANDLES (POSTS)
#
#
# ###################
@_db_connector
def handle_waypoints(self, cursor, arg):
wploader = self.mpstate.public_modules['wp'].wploader
# Update if our waypoints are out of date, and the loader is at the expected count.
if wploader.wpoints != self.wps and len(wploader.wpoints) == wploader.expected_count:
# If the waypoints change, then the minimum distance to each waypoint calculated before
# is no longer valid, so reset it.
self.wps = list(wploader.wpoints)
data = (datetime.now(), json.dumps(self.mavwp_to_wps(self.wps)))
query = """
INSERT INTO
waypoints (time, json)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
self.mpstate.public_modules['mission'].reset_min_dists()
cursor.execute(query, data)
@_db_connector
def handle_mode(self, cursor, arg):
if self.mode != self.master.flightmode:
# mode has changed
# These next two lines are pretty janky and someone should trace it
# back to see why the error occurs in the first place
if self.master.flightmode.startswith("Mode"):
self.master.flightmode = self.num2mode[int(self.master.flightmode[5:-1])]
self.mode = self.master.flightmode
data = (datetime.now(), self.mode2num[self.mode])
query = """
INSERT INTO
mode (time, mode)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, data)
@_db_connector
def handle_heartbeat(self, cursor, msg):
# Note: We're only callling this function after we've received a heartbeat from the plane
# so there will never be a linkerror when this function is called. The boolean stored is
# never referenced but is left in to avoid needing to update the database schema
self.heartbeat = self.Heartbeat(self._get_packet_time(msg),
not self.master.linkerror)
query = """
INSERT INTO plane_link
(time, plane_link)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.heartbeat)
@_db_connector
def handle_global_position_int(self, cursor, msg):
self.global_position_int = self.GPS_INT(self._get_packet_time(msg), # seconds
float(msg.relative_alt) / 1000, # meters
float(msg.alt) / 1000, # meters
float(msg.lat) / pow(10, 7), # decimal
float(msg.lon) / pow(10, 7), # decimal
msg.hdg / float(100), # degrees
msg.vx / float(100), # m/s
msg.vy / float(100), # m/s
msg.vz / float(100)) # m/s
query = """
INSERT INTO global_position_int
(time, rel_alt, asl_alt, lat, lon, heading, groundvx, groundvy, groundvz)
VALUES
(%s,%s,%s,%s,%s,%s,%s,%s,%s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.global_position_int)
@_db_connector
def handle_vfr_hud(self, cursor, msg):
airx, airy = heading_to_vector(msg.heading, msg.airspeed)
airz = self.global_position_int.groundvz
self.vfr_hud = self.VFR_HUD(self._get_packet_time(msg), # seconds
airx, # m/s
airy, # m/s
airz, # m/s
msg.airspeed, # m/s
msg.climb, # m/s
float(msg.throttle)) # pct
query = """
INSERT INTO
vfr_hud (time, airspeedvx, airspeedvy, airspeedvz, speed, climb, throttle)
VALUES
(%s,%s,%s,%s,%s,%s,%s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.vfr_hud)
@_db_connector
def handle_flight_time(self, cursor, msg):
# if the throttle is on and it is climbing and this is the first time this has been seen then
# this is the start of the flight time
if(msg.throttle > 2 and msg.climb > 2 and not self.flight_time.is_flying):
self.flight_time = self.FLIGHT_TIME(self._get_packet_time(msg), self._get_packet_time(msg), True)
# if the flight has started and you are still moving then the plane is still in flight
elif(msg.airspeed > 2 and self.global_position_int.rel_alt > 2 and self.flight_time.is_flying):
self.flight_time = self.FLIGHT_TIME(self._get_packet_time(msg), self.flight_time.time_start, True)
else:
self.flight_time = self.FLIGHT_TIME(self._get_packet_time(msg), self.flight_time.time_start, False)
query = """
INSERT INTO
flight_time (time, time_start, is_flying)
VALUES
(%s, %s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.flight_time)
@_db_connector
def handle_signal(self, cursor, msg):
self.signal = self.SIGNAL(self._get_packet_time(msg), msg.rssi)
query = """
INSERT INTO signal_status
(time, signal_strength)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.signal)
@_db_connector
def handle_attitude(self, cursor, msg):
self.attitude = self.ATTITUDE(self._get_packet_time(msg), # seconds
msg.roll, # rad -pi...+pi
msg.pitch, # rad -pi...+pi
msg.yaw, # rad -pi...+pi
msg.rollspeed, # rad/s
msg.pitchspeed, # rad/s
msg.yawspeed) # rad/s
query = """
INSERT INTO attitude
(time, roll, pitch, yaw, rollspeed, pitchspeed, yawspeed)
VALUES
(%s,%s,%s,%s,%s,%s,%s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.attitude)
@_db_connector
def handle_status_text(self, cursor, msg):
if '#EP' in msg.text and len(self.wps) > 0:
lat, lon = msg.text.split('#EP:')[1].split(',')
self.wps[0].x = float(lat)
self.wps[0].y = float(lon)
data = (self._get_packet_time(msg),
msg.text,
msg.severity) # 0: emergency, 1: alert, 2: critical, 3: error, 4: warning, 5: notice, 6: info
if msg.severity == 6:
self.mav_info = self.MAV_INFO(*data)
elif msg.severity in (5, 4):
self.mav_warning = self.MAV_WARNING(*data)
elif msg.severity < 4:
self.mav_error = self.MAV_ERROR(*data)
query = """
INSERT INTO mav_message
(time, text, severity)
VALUES
(%s, %s, %s)
"""
if not self.mpstate.airapi:
cursor.execute(query, data)
@_db_connector
def handle_sys_status(self, cursor, msg):
self.battery = self.BATTERY(self._get_packet_time(msg), # seconds
float(msg.battery_remaining), # percent
float(msg.voltage_battery) / 1000.0, # Volts
float(msg.current_battery) / 10.0) # mAh
query = """
INSERT INTO battery
(time, batterypct, batteryvoltage, batterycurrent)
VALUES
(%s, %s, %s, %s)
"""
if not self.mpstate.airapi:
cursor.execute(query, self.battery)
@_db_connector
def handle_wind(self, cursor, msg):
windx, windy = heading_to_vector(msg.direction, msg.speed)
self.wind = self.WIND(self._get_packet_time(msg), # seconds
windx, # m/s
windy, # m/s
msg.speed_z) # m/s
query = """
INSERT INTO wind
(time, windx, windy, windz)
VALUES
(%s, %s, %s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.wind)
@_db_connector
def handle_current_wp(self, cursor, msg):
self.current_waypoint_index = msg.seq
data = (self._get_packet_time(msg), # datetime
msg.seq) # int
query = """
INSERT INTO current_wp
(time, current_wp)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, data)
@_db_connector
def handle_gps_status(self, cursor, msg):
self.GPSStatus = self.GPS_STATUS(self._get_packet_time(msg),
msg.satellites_visible)
query = """
INSERT INTO gps_status
(time, satellite_number)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, self.GPSStatus)
def handle_ekf(self, msg):
pass
def handle_hw_status(self, msg):
pass
def handle_power_status(self, msg):
pass
def handle_radio(self, msg):
pass
def handle_nav_controller(self, msg):
pass
def handle_param(self, msg):
pass
# @param obstacles: {'stationary_obstaces': {"lat": lat, "lon": lon, "cylinder_radius": cylinder_radius, "cylinder_height": cylinder_height} }
# @param t: option time in seconds
@_db_connector
def handle_interop_obstacles(self, cursor, obstacles):
if 'stationary_obstacles' not in obstacles:
logger.error("handle_interop_obstacles: incorrect obstacle format: {}".format(obstacles.keys()))
return
data = (datetime.now(),
json.dumps(obstacles['stationary_obstacles']))
query = """
INSERT INTO obstacles
(time, st_obstacles)
VALUES
(%s, %s);
"""
if not self.mpstate.airapi:
cursor.execute(query, data)
@_db_connector
def handle_camera_feedback(self, cursor, msg):
data = (msg.time_usec, float(msg.lat) / pow(10, 7),
float(msg.lng) / pow(10, 7), msg.alt_rel,
degrees_to_rads(msg.roll), # Comes from roll_sensor in degrees 0-360, converting to -pi...+pi
degrees_to_rads(msg.pitch), # Comes from pitch_sensor in degrees 0-360, converting to -pi...+pi
degrees_to_rads(msg.yaw), # Comes from yaw_sensor in degrees 0-360, converting to -pi...+pi
datetime.fromtimestamp(time.time()), msg.img_idx)
query = """
INSERT INTO camera_feedback
(ds_time, lat, lon, alt_rel,
roll, pitch, yaw,
mavproxy_time, image_index)
VALUES
(%s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
cursor.execute(query, data)
# #############
#
#
# GETS
#
#
# #############
@_db_connector
def get_camera_feedback(self, cursor, req_time=None):
if req_time is None:
return None
query = """
SELECT
ds_time
, lat
, lon
, alt_rel
, roll
, pitch
, yaw
, mavproxy_time
FROM
camera_feedback
WHERE
ds_time = %s
"""
cursor.execute(query, (req_time,))
data = cursor.fetchone()
try:
cam_feedback = self.CAMERA_FEEDBACK(*data)._asdict()
cam_feedback['mavproxy_time'] = cam_feedback['mavproxy_time'].strftime('%s')
return cam_feedback
except TypeError as e:
logger.error("get_camera_feedback:" + str(e) + str(data))
return None
@_db_connector
def get_attitude(self, cursor, req_time=None):
if req_time is None:
return self.attitude._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, roll
, pitch
, yaw
, rollspeed
, pitchspeed
, yawspeed
FROM attitude
WHERE time > FROM_UNIXTIME(%s) AND time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
data = self.make_interpolated_query(cursor, req_time, query)
if len(data) == 0:
logger.debug("get_attitude: no data at time {}".format(req_time))
return None
try:
return self.ATTITUDE(*data)._asdict()
except TypeError as e:
logger.error("get_attitude:" + str(e) + str(data))
return None
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_param(self, cursor, req_time=None):
pass
@_db_connector
def get_heartbeat(self, cursor, req_time=None):
if req_time is None:
data = abs(self.heartbeat[0] - time.time()) < HEARTBEAT_TIMEOUT
elif not self.mpstate.airapi:
query = """
SELECT
time
FROM plane_link
WHERE time > FROM_UNIXTIME(%s) and time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
time_start = req_time - TIME_VARIANCE
time_end = req_time
cursor.execute(query, (time_start, time_end, req_time))
closest = cursor.fetchall()
if len(closest) == 0:
data = False
else:
data = abs(time.mktime(closest[-1][0].timetuple()) - req_time) < HEARTBEAT_TIMEOUT
else:
raise ValueError("Database query not supported in AirAPI mode")
return data
@_db_connector
def get_gps_status(self, cursor, req_time=None):
if req_time is None:
return self.GPSStatus._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, satellite_number
FROM gps_status
WHERE time > FROM_UNIXTIME(%s) and time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
time_start = req_time - TIME_VARIANCE
time_end = req_time + TIME_VARIANCE
cursor.execute(query, (time_start, time_end, req_time))
closest = cursor.fetchone()
if closest is None:
logger.debug("get_gps_status: no data at time {}".format(req_time))
return 0
else:
return self.GPS_STATUS(*closest)._asdict()
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_flight_time(self, cursor, req_time=None):
if req_time is None:
return self.flight_time._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, time_start
, is_flying
FROM flight_time
WHERE time > FROM_UNIXTIME(%s) and time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
cursor.execute(query, (req_time - TIME_VARIANCE, req_time + TIME_VARIANCE, req_time))
closest = cursor.fetchone()
if closest is None:
logger.debug("get_flight_time: no data at time {}".format(req_time))
return 0
else:
# as it turns out mysql does not store booleans as true or false it stores them as 0 and 1
# this was causing the closest tupple to have an int for the value that represented is_flying (a bool)
# this changes the 0s and 1s back to booleans
lst = list(closest)
lst[2] = bool(lst[2])
toReturn = tuple(lst)
return self.FLIGHT_TIME(*toReturn)._asdict()
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_gps(self, cursor, req_time=None):
if req_time is None:
return self.global_position_int._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, rel_alt
, asl_alt
, lat
, lon
, heading
, groundvx
, groundvy
, groundvz
FROM global_position_int
WHERE time > FROM_UNIXTIME(%s) AND time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
data = self.make_interpolated_query(cursor, req_time, query)
if len(data) == 0:
logger.debug("get_gps: no data at time {}".format(req_time))
return None
try:
return self.GPS_INT(*data)._asdict()
except TypeError as e:
logger.error("get_gps: " + str(e))
return None
else:
raise ValueError("Database query not supported in AirAPI mode")
# @param time: time in seconds
# @returns dict of format VFR_HUD or None if unavailable
@_db_connector
def get_vfr_hud(self, cursor, req_time=None):
if req_time is None:
return self.vfr_hud._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, airspeedvx
, airspeedvy
, airspeedvz
, speed
, climb
, throttle
FROM vfr_hud
WHERE time > FROM_UNIXTIME(%s) AND time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
data = self.make_interpolated_query(cursor, req_time, query)
if len(data) == 0:
logger.debug("get_vfr_hud: no data at time {}".format(req_time))
return None
try:
return self.VFR_HUD(*data)._asdict()
except TypeError as e:
logger.error("get_vfr_hud" + str(e))
return None
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_signal(self, cursor, req_time=None):
if req_time is None:
return self.signal._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, signal_strength
FROM signal_status
WHERE time > FROM_UNIXTIME(%s) and time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
cursor.execute(query, (req_time - TIME_VARIANCE, req_time + TIME_VARIANCE, req_time))
closest = cursor.fetchone()
if closest is None:
logger.debug("get_signal : no data at time {}".format(req_time))
return None
else:
return self.SIGNAL(*closest)._asdict()
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_status_text(self, cursor, req_time=None):
if req_time is None:
return {'info_text': self.mav_info.text, 'warning_text': self.mav_warning.text, 'error_text': self.mav_error.text}
elif not self.mpstate.airapi:
# 0: emergency, 1: alert, 2: critical, 3: error, 4: warning, 5: notice, 6: info
query = """
SELECT
COALESCE(CASE WHEN severity = 6 THEN text END) as info
, COALESCE(CASE WHEN severity >= 4 AND severity <= 5 THEN text END) as warning
, COALESCE(CASE WHEN severity < 4 THEN text END) as error
FROM mav_message
WHERE time < FROM_UNIXTIME(%s)
AND time > FROM_UNIXTIME(%s)
ORDER BY time desc
"""
cursor.execute(query, (req_time, req_time - TIME_HISTORY_MAX))
data = cursor.fetchone()
if data is None or len(data) < 3:
logger.debug("get_status_text: bad data {} is null.".format(data))
return
return {'info_text': data[0], 'warning_text': data[1], 'error_text': data[2]}
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_sys_status(self, cursor, req_time=None):
if req_time is None:
return self.battery._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, batterypct # percent
, batteryvoltage # volts
, batterycurrent # current
FROM battery
WHERE time > FROM_UNIXTIME(%s) AND time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
data = self.make_interpolated_query(cursor, req_time, query)
if len(data) == 0:
logger.debug("get_sys_status: no data at time {}".format(req_time))
return None
try:
return self.BATTERY(*data)._asdict()
except TypeError as e:
logger.error("get_sys_status" + str(e))
return None
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_wind(self, cursor, req_time=None):
if req_time is None:
return self.wind._asdict()
elif not self.mpstate.airapi:
query = """
SELECT
time
, windx
, windy
, windz
FROM wind
WHERE time > FROM_UNIXTIME(%s) AND time < FROM_UNIXTIME(%s)
ORDER BY ABS(DATEDIFF(time, FROM_UNIXTIME(%s)))
"""
data = self.make_interpolated_query(cursor, req_time, query)
if len(data) == 0:
logger.debug("get_wind: no data at time {}".format(req_time))
return None
else:
raise ValueError("Database query not supported in AirAPI mode")
try:
return self.WIND(*data)._asdict()
except TypeError as e:
logger.error("get_wind error" + str(e))
return None
@_db_connector
def get_waypoints(self, cursor, req_time=None):
if req_time is not None and self.mpstate.airapi:
raise ValueError("Database query not supported in AirAPI mode")
try:
if req_time is None:
return self.mavwp_to_wps(self.wps)
else:
query = """
SELECT
json
FROM waypoints
WHERE time < FROM_UNIXTIME(%s)
AND time > FROM_UNIXTIME(%s)
ORDER BY time desc;
"""
cursor.execute(query, (req_time, req_time - TIME_HISTORY_MAX))
result = cursor.fetchone()
if result is None:
logger.debug('get_waypoints: no data at time {}'.format(req_time))
return []
return json.loads(result[0])
except Exception as e:
logger.error("get_waypoints error {}".format(str(e)))
@_db_connector
def get_mode(self, cursor, req_time=None):
if req_time is None:
return self.master.flightmode
elif not self.mpstate.airapi:
query = """
SELECT
mode
FROM mode
WHERE time < FROM_UNIXTIME(%s)
AND time > FROM_UNIXTIME(%s)
ORDER by time desc;
"""
cursor.execute(query, (req_time, req_time - TIME_HISTORY_MAX))
result = cursor.fetchone() # returns tuple of length one (0, )
if result is None:
return "MAV"
return self.num2mode[result[0]]
else:
raise ValueError("Database query not supported in AirAPI mode")
@_db_connector
def get_interop_obstacles(self, cursor, req_time=None):
if req_time is None:
return self.interop_instance.obstacles
elif not self.mpstate.airapi:
query = """
SELECT
time
, st_obstacles
FROM obstacles
WHERE time < FROM_UNIXTIME(%s)
AND time > FROM_UNIXTIME(%s)
ORDER BY time desc;
"""
cursor.execute(query, (req_time, req_time - TIME_HISTORY_MAX))
result = cursor.fetchone()
if result is None:
return []
mv_obstacles = json.loads(result[1])
st_obstacles = json.loads(result[2])
else:
raise ValueError("Database query not supported in AirAPI mode")
return {'time': result[0], 'stationary_obstacles': st_obstacles}
@_db_connector
def get_ekf(self, cursor, req_time=None):
raise NotImplementedError
@_db_connector
def get_hw_status(self, cursor, req_time=None):
raise NotImplementedError
@_db_connector
def get_power_status(self, cursor, req_time=None):
raise NotImplementedError
@_db_connector
def get_radio(self, cursor, req_time=None):
raise NotImplementedError
@_db_connector
def get_nav_controller(self, cursor, req_time=None):
raise NotImplementedError
@_db_connector
def get_current_wp(self, cursor, req_time=None):
if req_time is None:
data = self.current_waypoint_index
elif not self.mpstate.airapi:
query = """
SELECT
current_wp
FROM current_wp
WHERE time < FROM_UNIXTIME(%s)
AND time > FROM_UNIXTIME(%s)
ORDER BY time desc
LIMIT 1;
"""
cursor.execute(query, (req_time, req_time - TIME_HISTORY_MAX))
data_tuple = cursor.fetchone()
if data_tuple is None:
raise ValueError("No data for time: " + str(req_time))
data = data_tuple[0]
else:
raise ValueError("Database query not supported in AirAPI mode")
return data
def mavlink_packet(self, msg):
try: # use giant try catch so mavproxy doesn't eat this errors
msgt = msg.get_type() # message type
if msgt == 'SYSTEM_TIME' and msg.time_unix_usec != 0:
if self.autopilot_boot_time is None:
self.autopilot_boot_time = (msg.time_unix_usec/1000.0 - msg.time_boot_ms)/1000.0
new_time = round(msg.time_unix_usec / 1000000)
logger.info("Recorded autopilot boot time: " + str(datetime.fromtimestamp(new_time)))
if self.mpstate.airapi:
time_string = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(new_time))
logger.info("Syncing system time to Autopilot time ({})".format(time_string))
subprocess.call("(sudo /usr/local/bin/set-time.sh \"" + time_string + "\")", shell=True)
if msgt == 'PARAM_VALUE':
self.handle_param(msg)
elif msgt == 'HEARTBEAT':
self.handle_heartbeat(msg)
elif msgt == 'GLOBAL_POSITION_INT':
self.handle_global_position_int(msg)
elif msgt == 'ATTITUDE':
self.handle_attitude(msg)
elif msgt == 'VFR_HUD':
self.handle_vfr_hud(msg)
self.handle_flight_time(msg)
elif msgt == 'WIND':
self.handle_wind(msg)
elif msgt == 'STATUSTEXT':
self.handle_status_text(msg)
elif msgt == 'SYS_STATUS':
self.handle_sys_status(msg)
elif msgt == 'EKF_STATUS_REPORT':
self.handle_ekf(msg)
elif msgt == 'HWSTATUS':
self.handle_hw_status(msg)
elif msgt == 'POWER_STATUS':
self.handle_power_status(msg)
elif msgt in['RADIO', 'RADIO_STATUS']:
self.handle_radio(msg)
elif msgt in['RC_CHANNELS', 'RC_CHANNELS_RAW']:
self.handle_signal(msg)
elif msgt == 'NAV_CONTROLLER_OUTPUT':
self.handle_nav_controller(msg)
elif msgt == 'GPS_RAW_INT':
self.handle_gps_status(msg)
elif msgt in ['MISSION_CURRENT', "WAYPOINT_CURRENT"]:
self.handle_current_wp(msg)
elif msgt == 'CAMERA_FEEDBACK':
self.handle_camera_feedback(msg)
self.handle_waypoints(None)
self.handle_mode(None)
except Exception as e:
exc_type, _, exc_tb = sys.exc_info()
fname = os.path.split(exc_tb.tb_frame.f_code.co_filename)[1]
logger.error("mavlink_packet: " + str(e) + str(exc_type) + str(fname) + str(exc_tb.tb_lineno))
from mysql.connector import connect
global pool
pool = MySQLConnectionPool(pool_name=None,
pool_size=50,
pool_reset_session=True,
user=db_user,
password=db_pass,
database=db_name,
host=db_host)
@contextmanager
def getconnection_mysql(buffered=True, raw=True):
con = pool.get_connection(buffered=buffered, raw=raw)
try:
yield con
finally:
con.close()
@contextmanager
def getcursor_mysql(prepared=False, buffered=True, raw=True):
con = pool.get_connection(buffered=buffered, raw=raw)
try:
yield con.cursor(prepared=prepared)
finally:
con.close()
con = pool.get_connection()
