def __init__(self, interface, table_timewindow, summ_timewindow, log_table,
summ_table, user, password, host, database, display_rate,
display_size):
self.db_manager = db_manager.DBManager(user, password, host, database)
# self.data_processor = data_processor.DataProcessor()
self.table_timewindow = table_timewindow
self.summ_timewindow = summ_timewindow
self.capture = pyshark.LiveCapture(interface=interface)
self.log_table = log_table
self.summ_table = summ_table
self.monitor_file = "monitor.csv"
open(self.monitor_file, "w").close()
monitor_log = open(self.monitor_file, "a")
writer = csv.writer(monitor_log,
quoting=csv.QUOTE_MINIMAL,
quotechar='"',
delimiter=",",
lineterminator="\r\n")
writer.writerow(
["summ row count", "src:port", "dst:port", "pps", "delay"])
monitor_log.close()
self.check_ts = time.time()
self.display_rate = display_rate # seconds
self.delay_count = 0
self.avg_delay = 0
self.total_delay = 0
self.display_size = display_size
self.skip_count = 0
def setUp(self):
"""Because these tests involve writing to the database, we switch
out the database file, using a dedicated 'unittest.db' database file.
At the end of the test it switches back to the live database.
"""
self.set_test_database()
self.dbm = db_manager.DBManager()
def save_orders(self, last_event):
print(last_event)
data = self.get_events(last_event)
print(len(data['events']))
try:
with db_manager.DBManager() as db:
if db.check_base():
for event in data['events']:
print('id: {},\noccurredAt: {}'.format(
event['id'], event['occurredAt']))
time_stamp = event['occurredAt']
order = self.get_order_details(
event['order']['checkoutForm']['id'])
if not db.find_order_by_id(order['id']):
address_id = db.find_delivery_address_id(
order['delivery']['address'])
db.add_buyer(order['buyer'], time_stamp)
db.add_delivery_address(
address_id, order['delivery']['address'])
db.add_order(
order['id'], order['buyer']['id'], address_id,
order['status'], order['payment']['type'],
order['delivery']['method']['name'],
order['summary']['totalToPay']['amount'],
order['messageToSeller'] if 'messageToSeller'
in order else '', time_stamp)
db.connect_address_buyer(order['buyer']['id'],
address_id)
print('len(lineItems): {}'.format(
len(order['lineItems'])))
for item in order['lineItems']:
db.add_item(item['offer']['id'],
item['offer']['name'],
item['price']['amount'])
db.connect_order_item(order['id'],
item['offer']['id'],
item['quantity'])
db.print_values()
return 1
except KeyError as e:
print('KeyError: {}'.format(e))
return 0
return 0
def runTest(self):
"""
@summary - runs tests for VisualisationManager.
@description - will run the unit tests for VisualisationManager
@author - mah60
@param - None
@return - None
"""
self.db = dbm.DBManager()
self.vm = visualisation_manager.VisualisationManager(self.db)
self.test_convert_rssi_to_distance()
self.test_get_counter_labels()
self.db.db_close()
def get_buyers():
limit = 100
with db_manager.DBManager() as db:
if 'limit' in request.args and 'from' in request.args:
limit = request.args['limit']
from_date = request.args['from']
return jsonify(db.get_buyers_from(from_date, limit))
elif 'limit' in request.args:
limit = request.args['limit']
return jsonify(db.get_last_buyers(limit))
elif 'from' and 'to' in request.args:
from_date = request.args['from']
to_date = request.args['to']
return jsonify(db.get_buyers_from_to(from_date, to_date))
elif 'from' in request.args:
from_date = request.args['from']
return jsonify(db.get_buyers_from(from_date, limit))
return jsonify(db.get_last_buyers(limit))
#!/usr/bin/env python3
import db_manager
DB_PATH = 'proxy_db.db'
dbManager = db_manager.DBManager(DB_PATH)
username = input("username: "******"password: ")
dbManager.add_user(username, password)
print('test DBManager.user_auth()...')
assert dbManager.user_auth(username, password)
print('pass')
print()
print('User added.')
import db_manager as dbm
path = 'db/students.sqlite3'
db = dbm.DBManager(path)
if db.connect():
table_name = 'studenti'
# pokud tabulka neexistuje, vytvor novou
if not db.does_table_exist(table_name):
db.create_table(table_name)
print('table created')
import db_manager, commands
from ConfigUtils import ConfigUtils
from pb2py import CLServerCommand_pb2, LCServerCommand_pb2
import RequestSender
db = db_manager.DBManager()
conf = ConfigUtils()
Database = conf.get('game_db')
Table = "configs"
def get_configs(serverid):
m_conn = db.get_db(Database)
m_conn.select_db(Database)
m_cursor = m_conn.cursor()
retlist = []
m_cursor.execute("select * from " + Table + " order by Name")
for config in m_cursor.fetchall():
retlist.append((config[0], config[1]))
m_cursor.close()
m_conn.close()
return retlist
def edit_configs(request):
m_conn = db.get_db(Database)
m_cursor = m_conn.cursor()
keys = request.getlist('keys[]')
values = request.getlist('values[]')
for field, value in request.items():
if field == 'keys[]':
def __init__(self, api_key, api_secret, passphrase, config):
self.auth_client = cbpro.AuthenticatedClient(api_key, api_secret, passphrase)
self.db_manager = db_manager.DBManager()
self.accounts = []
self.coinbase_accounts = []
self.config = config
def main():
"""
@summary - the application controller.
@description - will controll what is happening at what time. e.g. install
data from recordings text files or creating visualisations.
@author - mah60
@param - None
@return - None
"""
db = dbm.DBManager()
rh = record_handler.RecordHandler(db)
vm = visualisation_manager.VisualisationManager(db)
#-----------------------------------
#db.reset_tables()
#file = "recordings_4.txt"
#rh.import_txt_to_db(file)
# create visualisations
#mac = "0C:CB:85:25:D1:F1"
#vm.device_rssi_over_time(mac, False, 4)
#vm.device_rssi_over_time(mac, True, 4)
#vm.total_count_over_time()
#vm.count_over_time()
#vm.rssi_to_distance_graph()
#vm.get_dwelling_time_frequency(60)
"""
# test turnover bytes - for fasts speed
#-------------------------------------------------
turn_bytes = [10000, 100000, 250000, 500000, 750000, 1000000, 2000000, 4000000]
for b in turn_bytes:
for i in range (0, 3):
db.reset_tables()
print("-------------------------------")
print("test " + str(i+1)+ ":")
print("bytes limit: " + str(b))
before = int(round(time.time()))
file = "recordings_3.txt"
rh.import_txt_to_db(file, db, b)
after = int(round(time.time()))
stopwatch_time = after-before
print("time (sec): " + str(stopwatch_time))
"""
# get average rssi over time -> testing
#-----------------------------------
""""
for i in range(1,11):
file = "meter_test_" + str(i) + ".txt"
db.reset_tables()
rh.import_txt_to_db(file)
mac = "0C:CB:85:25:D1:F1"
average, item_amount = vm.get_average_rssi_over_time(mac)
print("test " + str(i) + ": average value: " + str(average) + "at 1 meter for 5 mins\nWith " + str(item_amount) + " records")
# results
"""
"""
test 1: average value: -78.0at 1 meter for 5 mins
With 1 records
test 2: average value: -77.33333333333333at 1 meter for 5 mins
With 3 records
test 3: average value: -69.30674846625767at 1 meter for 5 mins
With 163 records
test 4: average value: -71.06629834254143at 1 meter for 5 mins
With 362 records
test 5: average value: -67.04672897196262at 1 meter for 5 mins
With 214 records
test 6: average value: -70.0863309352518at 1 meter for 5 mins
With 278 records
test 7: average value: -72.04761904761905at 1 meter for 5 mins
With 462 records
test 8: average value: -68.49095607235142at 1 meter for 5 mins
With 387 records
test 9: average value: -71.70263788968825at 1 meter for 5 mins
With 417 records
test 10: average value: -72.53793103448275at 1 meter for 5 mins
With 290 records
Average results determines rssi value to be 71.5 or 70
when there are more than 10 records
"""
db.db_close()
def save_data_to_db(self):
manager = db_manager.DBManager()
for row in self.data:
manager.insert_subproducto(row)
manager.close_connection()
def main(screen):
parser = argparse.ArgumentParser()
parser.add_argument("-pi_hosts",
required=True,
nargs="+",
help="List of host addresses of each raspberry pi")
parser.add_argument("-port",
required=True,
help="Port to communicate with each raspberry pi")
parser.add_argument("-user", required=True, help="User of the database")
parser.add_argument("-password",
required=True,
help="User's access password")
parser.add_argument("-host",
required=True,
help="Host database is located on")
parser.add_argument("-database",
required=True,
help="Name of the database")
parser.add_argument("-summ_table",
required=True,
help="Table the stream is summarized to")
args = parser.parse_args()
hosts = args.pi_hosts
port = args.port
socks = []
for i in range(len(hosts)):
try:
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.connect((hosts[i], port))
socks.append(sock)
except ConnectionRefusedError:
print(f"Connection failed for {hosts[i]}:{port}")
sys.exit(1)
dbmanager = db_manager.DBManager(user=args.user,
password=args.password,
host=args.host,
database=args.database)
summ_table = args.summ_table
max_buffer_size = 5120
while True:
dbmanager.clear_summ_table(summ_table)
for sock in socks:
sock.send(b"1")
for sock in socks:
sock.recv(max_buffer_size)
log_window = 1
pps_info = dbmanager.get_summ_pps_info(summ_table, log_window)
sorted_pps_info = sorted(pps_info,
key=lambda info: (-info[5], info[4]))
screen.clear()
screen.print_at(f"Window: {log_window} secs", 0, 1)
col_info = [
"src:port", 21, "dst:port", 21, "last occurrence(secs)", 21, "pps",
8
] # header follow by max width
header_str = ""
for i in range(0, len(col_info), 2):
header_str += "{0:{1}s} | ".format(col_info[i], col_info[i + 1])
header_str = header_str[:-3]
screen.print_at(header_str, 0, 2)
screen.print_at("-" * len(header_str), 0, 3)
data_offset = 4
for row in range(len(sorted_pps_info)):
info = sorted_pps_info[row]
data_str = ""
data_str += "{0:{1}s} | ".format(
info[0],
col_info[1]) if info[1] is None else "{0:{1}s} | ".format(
info[0] + ":" + str(info[1]), col_info[1])
data_str += "{0:{1}s} | ".format(
info[2],
col_info[3]) if info[3] is None else "{0:{1}s} | ".format(
info[2] + ":" + str(info[3]), col_info[3])
for i in range(4, len(info)):
j = (i - 2) * 2 + 1
data = "{:0.2f}".format(
info[i]) if isinstance(info[i], int) or isinstance(
info[i], float) else str(info[i])
data_str += "{0:{1}s} | ".format(
data, col_info[j]) if i == 4 else "{0:{1}s} | ".format(
data, col_info[j])
data_str = data_str[:-3]
screen.print_at(data_str, 0, row + data_offset)
screen.refresh()
time.sleep(3)
#!/usr/bin/env python3
import os
import db_manager
TEST_DB_PATH = "test.db"
TEST_USER = "******"
TEST_PASSWD = "password"
TEST_RULE_PATH = db_manager.RULE_PATH_PREFIX + TEST_USER + ".rule"
dbManager = db_manager.DBManager(TEST_DB_PATH)
def init_db():
"""
initialize the database for test
"""
with open("init.sql") as init_file:
init_script = init_file.read()
dbManager.cursor.executescript(init_script)
dbManager.conn.commit()
def clear():
dbManager.conn.close()
os.remove(TEST_DB_PATH)
os.remove(TEST_RULE_PATH)
class DBInsert(ModuleClass):
db_manager = db_manager.DBManager()
log, res, err_log = None, None, None
def __init__(self):
super(DBInsert, self).__init__()
self.db_manager.connect()
self.log, self.res, self.err_log = LogManager.__call__().get_logger()
def get_last_working_day(self, subject_code):
query = """
select max(working_day) as last_working_day from %s
""" % subject_code
if not self.exist_table(subject_code): return '2000-01-01'
result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
if result[0] == None:
return '2000-01-01'
return str(result[0])
def check_first_input(self, subject_code, working_day):
query = """
select date from %s where working_day =\'%s\' limit 1
""" % (subject_code, working_day)
result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
if result == None:
return 'err'
return result
def check_last_input(self, subject_code, working_day):
query = """
select date from %s where working_day =\'%s\' order by id desc limit 1
""" % (subject_code, working_day)
result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
if result == None:
return 'err'
return result
#18로 되어있는 월물중에 working_day가 유효한지 판단
def check_subject_code(self, subject_code, working_day):
query = """
select date from %s where working_day =\'%s\'
""" % (subject_code, working_day)
result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
return result
def get_subject_code(self):
result_list = []
query = """
show tables like \"%%%s%%\"
""" % str(datetime.date.today().year).replace('20', '')
return self.db_manager.exec_query(query, fetch_type=FETCH_ALL)
def insert_data(self, subject_code, data):
try:
query = """
insert into %s
(date, price, volume, working_day)
values
""" % subject_code
query = query + "(%s, %s, %s, %s)"
if not self.exist_table(subject_code):
self.create_table(subject_code)
self.log.info("%s 종목 데이터 수[%s], 처음 데이터 %s" %
(subject_code, len(data), data[0]))
'''
while len(data) > 0:
if len(data) > 1000:
self.db_manager.exec_query_many(query, data[:1000])
data = data[1000:]
else:
self.db_manager.exec_query_many(query, data)
break
'''
self.db_manager.exec_query_many(query, data)
except Exception as err:
print(err)
def exist_table(self, subject_code):
query = "show tables like '%s'" % subject_code
row = self.db_manager.exec_query(query, FETCH_ONE)
if row is None:
return False
return True
def drop_table(self, table_name):
query = "drop table %s" % table_name
return self.db_manager.exec_query(query)
def create_table(self, table_name):
query = """
CREATE TABLE `%s` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`date` TIMESTAMP NULL DEFAULT NULL,
`price` FLOAT NULL DEFAULT NULL,
`volume` SMALLINT(6) NULL DEFAULT NULL,
`working_day` DATE NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `date` (`date`),
INDEX `working_day` (`working_day`)
)
"""
return self.db_manager.exec_query(query % table_name)
def get_table(self, table_name):
query = 'select date, price, working_day from %s' % table_name
return self.db_manager.exec_query(query, fetch_type=FETCH_ALL)
def get_table_list(self, subject_symbol, start_date, end_date):
query = "show tables where substr(Tables_in_haedong, 1, %s) = '%s'\
and substr(Tables_in_haedong, (select char_length(Tables_in_haedong))\
- 7, 8) between '%s' and '%s'" % (
len(subject_symbol), subject_symbol, start_date, end_date)
return self.db_manager.exec_query(query, fetch_type=FETCH_ALL)
def is_empty_table(self, table_name):
query = """
select count(*) from %s
""" % table_name
if not self.exist_table(table_name): return True
result = self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
if result[0] > 0: return False
return True
def get_name(self):
return str(self.__class__.__name__)
def print_status(self):
print(self.__getattribute__())
def check_nomal_data(self, table_name):
query = 'select * from %s order by id desc limit 1' % table_name
return self.db_manager.exec_query(query, fetch_type=FETCH_ONE)
# 당일데이터 삭제
def del_err_data(self, table_name, working_day):
query = "select date from %s where working_day =\'%s\'" % table_name, table_name
return self.db_manager.exec_query(query)
def main():
parser = argparse.ArgumentParser()
parser.add_argument("-master",
required=True,
nargs="+",
help="Host address of the master node")
parser.add_argument("-port",
required=True,
help="Port to communicate with the master node")
parser.add_argument("-local_user",
required=True,
help="User of the local database")
parser.add_argument("-local_password",
required=True,
help="User's local access password")
parser.add_argument("-local_host",
required=True,
help="Host local database is located on")
parser.add_argument("-local_database",
required=True,
help="Name of the local database")
parser.add_argument("-remote_user",
required=True,
help="User of the remote database")
parser.add_argument("-remote_password",
required=True,
help="User's remote access password")
parser.add_argument("-remote_host",
required=True,
help="Host remote database is located on")
parser.add_argument("-remote_database",
required=True,
help="Name of the remote database")
parser.add_argument("-summ_table",
required=True,
help="Table the stream is summarized to")
args = parser.parse_args()
host = args.master
port = args.port
soc = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
soc.setsockopt(socket.SOL_SOCKET, socket.SO_REUSEADDR, 1)
print("Socket created!")
try:
soc.bind((host, port))
except:
print(f"Bind failed. Error: {str(sys.exc_info())}")
sys.exit(1)
soc.listen(5)
print("Socket now listening!")
max_buffer_size = 5120
while True:
connection, address = soc.accept()
ip, port = str(address[0]), str(address[1])
print(f"Connection Accepted!")
try:
local_dbmanager = db_manager.DBManager(
user=args.local_user,
password=args.local_password,
host=args.local_host,
database=args.local_database,
sql_log_file="sql_log_worker_server.SQL",
csv_file="summary.csv")
remote_dbmanager = db_manager.DBManager(
user=args.remote_user,
password=args.remote_password,
host=args.remote_host,
database=args.remote_database,
sql_log_file="sql_log_worker_server.SQL",
csv_file="summary.csv",
clear_log=False)
summ_table = args.summ_table
receive_master(connection, local_dbmanager, remote_dbmanager,
summ_table, max_buffer_size)
except:
connection.shutdown(socket.SHUT_RDWR)
connection.close()
print("Master disconnected!")
def main():
"""
@summary - the application controller.
@description - will controll what is happening at what time. e.g. install
data from recordings text files or creating visualisations.
@author - mah60
@param - None
@return - None
"""
db = dbm.DBManager()
rh = record_handler.RecordHandler(db)
vm = visualisation_manager.VisualisationManager(db)
# check tables exist
if (db.is_tables()):
if (cf.is_import): # check if should be importing data
# reset tables
db.reset_tables()
# add data from record files
for i, r in enumerate(cf.counter_records):
r = cf.record_path + r
print("importing file : " + r)
before = int(round(time.time()))
rh.import_txt_to_db(r, i, cf.import_byte_limit)
after = int(round(time.time()))
stopwatch_time = after - before
print("time (sec): " + str(stopwatch_time))
# get visulisations
if (cf.is_graphs):
vm.rssi_to_distance_graph()
vm.total_count_over_time(False)
vm.total_count_over_time(True)
vm.count_over_time(False)
vm.count_over_time(True)
for timeout in cf.dwellingtime_timeout:
vm.get_dwelling_time_frequency(timeout, False)
vm.get_dwelling_time_frequency(timeout, True)
for mac in cf.rssi_distance_mac_address:
vm.device_rssi_over_time(mac, False,
cf.rssi_distance_graph_threshold,
False)
vm.device_rssi_over_time(mac, False,
cf.rssi_distance_graph_threshold,
True)
vm.device_rssi_over_time(mac, True,
cf.rssi_distance_graph_threshold,
False)
vm.device_rssi_over_time(mac, True,
cf.rssi_distance_graph_threshold,
True)
vm.send_graphs_to_pdf()
# test turnover bytes - for fasts speed
#-------------------------------------------------
if (cf.test_time_import_different_byte_size):
turn_bytes = [
10000, 100000, 250000, 500000, 750000, 1000000, 2000000, 4000000
]
for index, r in enumerate(cf.counter_records):
r = cf.record_path + r
for b in turn_bytes:
for i in range(0, 3):
db.reset_tables()
print("-------------------------------")
print("test " + str(i + 1) + ":")
print("test file : " + r)
print("bytes limit: " + str(b))
before = int(round(time.time()))
rh.import_txt_to_db(r, index, b)
after = int(round(time.time()))
stopwatch_time = after - before
print("time (sec): " + str(stopwatch_time))
# get average rssi over time -> testing
#-----------------------------------
""""
for i in range(1,11):
file = "meter_test_" + str(i) + ".txt"
db.reset_tables()
rh.import_txt_to_db(file)
mac = "0C:CB:85:25:D1:F1"
average, item_amount = vm.get_average_rssi_over_time(mac)
print("test " + str(i) + ": average value: " + str(average) + "at 1 meter for 5 mins\nWith " + str(item_amount) + " records")
# results
"""
"""
test 1: average value: -78.0at 1 meter for 5 mins
With 1 records
test 2: average value: -77.33333333333333at 1 meter for 5 mins
With 3 records
test 3: average value: -69.30674846625767at 1 meter for 5 mins
With 163 records
test 4: average value: -71.06629834254143at 1 meter for 5 mins
With 362 records
test 5: average value: -67.04672897196262at 1 meter for 5 mins
With 214 records
test 6: average value: -70.0863309352518at 1 meter for 5 mins
With 278 records
test 7: average value: -72.04761904761905at 1 meter for 5 mins
With 462 records
test 8: average value: -68.49095607235142at 1 meter for 5 mins
With 387 records
test 9: average value: -71.70263788968825at 1 meter for 5 mins
With 417 records
test 10: average value: -72.53793103448275at 1 meter for 5 mins
With 290 records
Average results determines rssi value to be 71.5 or 70
when there are more than 10 records
"""
db.db_close()
def get_order_items(order_id):
with db_manager.DBManager() as db:
data = db.get_order_items(order_id)
return jsonify(data) if data else abort(404)
def get_delivery_address(buyer_id):
with db_manager.DBManager() as db:
data = db.get_buyer_delivery_addresses(buyer_id)
return jsonify(data) if data else abort(404)
def get_buyer_orders(buyer_id):
with db_manager.DBManager() as db:
response = db.get_buyer_orders(buyer_id)
return jsonify(response) if response else abort(404)
