def UpdatePersonnel(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
check_list = ["PersonnelId", "JsonData"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
if "UserId" not in post_data:
post_data["UserId"] = 0
if "Type" not in post_data:
post_data["Type"] = 0
if "Domicile" not in post_data:
post_data["Domicile"] = ""
try:
db = Database()
err = db.update_personnel(post_data["PersonnelId"],
post_data["UserId"], post_data["Type"],
post_data["Domicile"], post_data["JsonData"])
except Exception as err:
return jsonFail(err)
else:
if err:
return jsonFail(err)
db.close()
return jsonSuccess("Success")
def delete_id(id):
db = Database()
resp = db.delete(id)
db.close()
if resp['id'] == 'does not exist':
return jsonify(resp), 404
return jsonify(resp)
def UpdateFamily(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
check_list = ["FamilyId", "JsonData"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
if "AddrId" not in post_data:
post_data["AddrId"] = 0
if "MasterName" not in post_data:
post_data["MasterName"] = ""
try:
db = Database()
err = db.update_family(post_data["FamilyId"], post_data["AddrId"],
post_data["MasterName"], post_data["JsonData"])
except Exception as err:
return jsonFail(err)
else:
db.close()
if err:
return jsonFail(err)
return jsonSuccess("Success")
def run(self):
database = Database()
client = TelegramClient('session_name', local.api_id, local.api_hash)
client.start()
add_notification = (
"INSERT INTO coin_notification (id, message, date) VALUES (%(notification_id)s, %(notification_message)s, %(notification_date)s)"
)
cursor = database.query("select max(id) as mx from coin_notification")
last_db_id = 0
for mx in cursor:
last_db_id = mx[0]
regex = r'[^a-zA-Z0-9.()%/#]+'
channel = PeerChannel(channel_id=1131948783)
while True:
messages = client.get_messages(channel,
min_id=last_db_id,
limit=1000)
if len(messages) == 0:
break
for m in messages:
m.message = re.sub(regex, ' ', m.message)
notification = {
'notification_id': m.id,
'notification_message': m.message.encode('utf8'),
'notification_date': m.date
}
database.execute(add_notification, notification)
database.close()
def add_task(self, id, name, user):
"""Adds a task to Task table. Requires ID, NAME, USER params"""
db = Database(connect=True)
#print "INSERT INTO Task VALUES(%s, '%s', '%s');" % (id, name, user)
q = db.query("insert into Task values(%s, '%s', '%s');" % (id, name, user))
db.close()
return q
def search_db():
if request.headers['Content-Type'] == 'application/json':
content = request.json
db = Database()
resp = db.search(content)
db.close()
return jsonify(resp)
def wrapper(*args, **kwargs):
db = Database()
try:
result = callback(db=db, *args, **kwargs)
finally:
db.close()
return result
def UpdateAddr(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
check_list = ["AddrId", "Community", "Building", "Dormitory"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
if "FamilyId" not in post_data:
post_data["FamilyId"] = 0
# 更新房屋地址
try:
db = Database()
err = db.update_addr(post_data["AddrId"], post_data["Community"],
post_data["Building"], post_data["Dormitory"],
post_data["FamilyId"])
except Exception as err:
return jsonFail(err)
else:
db.close()
if err:
return jsonFail(err)
return jsonSuccess("Success")
def add_task_event(self, id, task_id, user, is_started, start, stop, minutes_sum):
"""Adds a task to Task table. Requires ID, NAME, USER params"""
db = Database(connect=True)
values = "('%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (id, task_id, user,
is_started, start,
stop, minutes_sum)
q = db.query("insert into TaskEvent values%s;" % (values))
db.close()
return q
def new_entry():
if request.headers['Content-Type'] == 'application/json':
content = request.json
if all(key in content for key in ("name", "address", "phone")):
db = Database()
resp = db.insert(name=content['name'],
address=content['address'],
phone=content['phone'])
db.close()
return jsonify(resp)
def test_db(self):
aiven_results = 0
try:
psql_conn = Database(Config.PS_DATABASE_NAME)
print("DB Connected!")
query = """ CREATE TABLE IF NOT EXISTS """ + Config.PS_TEST_WEBSITE_TABLE_NAME + """ (
name varchar(255) NOT NULL,
url varchar(255) NOT NULL,
status_code integer NOT NULL,
reason varchar(255) NOT NULL,
response_time decimal NOT NULL,
checked_at timestamp NOT NULL,
pattern varchar(255),
has_pattern boolean DEFAULT FALSE,
PRIMARY KEY(url, checked_at)
) """
psql_conn.query(query)
print("Table created successfully in PostgreSQL ")
query = "DELETE FROM " + Config.PS_TEST_WEBSITE_TABLE_NAME + " WHERE url = 'https://aiven.io'"
psql_conn.query(query)
prod, cons = app.run(Config.K_MONITOR_TOPIC,
Config.PS_DATABASE_NAME,
Config.PS_TEST_WEBSITE_TABLE_NAME,
"tests/t_monitor_db.yml")
print("all 'aiven.io' is deleted from PostgreSQL ")
interval = File.read_time_interval("tests/t_monitor_corrupted.yml")
time.sleep(interval - 1)
app.stop_monitor(prod, cons)
query = "SELECT * FROM " + Config.PS_TEST_WEBSITE_TABLE_NAME + " WHERE url = 'https://aiven.io'"
cursor = psql_conn.query(query)
aiven_results = cursor.fetchall()
psql_conn = Database(Config.PS_DATABASE_NAME)
query = "DROP TABLE " + Config.PS_TEST_WEBSITE_TABLE_NAME
psql_conn.query(query)
psql_conn.close()
except Exception as error:
print("Error while connecting to PostgreSQL", error)
self.assertEqual(len(aiven_results), 1)
class TableTest(TestCase):
def setUp(self):
# reset db on every test
if os.path.isfile("norowdb.sqlite"):
os.remove("norowdb.sqlite")
self.db = Database()
def test_create_table(self):
self.db.create_table("Cats")
def test_get_tables(self):
self.db.create_table("Cats")
tables = self.db.get_tables()
self.assertEqual(tables, ["Cats"])
def test_delete_table(self):
self.db.create_table("Cats")
self.db.delete_table("Cats")
tables = self.db.get_tables()
self.assertEqual(tables, [])
def test_duplicate_table(self):
self.db.create_table("Cats")
with self.assertRaises(sqlite3.OperationalError):
self.db.create_table("Cats")
def test_add_column(self):
self.db.create_table("Cats")
self.db.add_column("Cats", "name")
def test_delete_column(self):
self.db.create_table("Cats")
self.db.add_column("Cats", "name")
self.db.add_column("Cats", "age")
self.db.delete_column("Cats", "name")
def test_delete_table_column(self):
self.db.create_table("Cats")
self.db.add_column("Cats", "name")
self.db.add_column("Cats", "age")
self.db.delete_table("Cats")
# Check table is deleted
tables = self.db.get_tables()
self.assertEqual(tables, [])
# Check columns are removed
self.db.create_table("Cats")
columns = self.db.get_columns("Cats")
self.assertEqual(columns, [])
def tearDown(self):
self.db.close()
def update_id(id):
if request.headers['Content-Type'] == 'application/json':
content = request.json
print content
db = Database()
resp = db.update(id=id, params=content)
db.close()
print resp
if 'id' in resp and resp['id'] == 'does not exist':
return jsonify(resp), 404
return jsonify(resp)
else:
return jsonify(
{'response': 'failure, content-type should be application/json'})
def create_table_for_consumer(self):
psql_conn = Database(self.db)
psql_conn.query(""" CREATE TABLE IF NOT EXISTS """ + self.table + """
(name varchar(255),
url varchar(255) NOT NULL,
status_code integer NOT NULL,
reason varchar(255) NOT NULL,
response_time decimal NOT NULL,
checked_at timestamp NOT NULL,
pattern varchar(255),
has_pattern boolean DEFAULT FALSE,
PRIMARY KEY(url, checked_at)) """)
psql_conn.close()
class Repository:
def __init__(self):
self.db = Database(**db_credentials)
def get_portfolio(self, session_token):
result = self.db.get_portfolio(session_token)
if not result:
return None
user_id, data = result
fonds = {fond_data["ticker"]: Fond(**fond_data) for fond_data in data}
return Portfolio(user_id, fonds)
def put_portfolio(self, portfolio):
self.db.save_portfolio(portfolio.to_json(), portfolio.user_id)
def get_user_info(self, session_token):
return self.db.get_user_info(session_token)
def _get_user_info_by_google_id(self, google_id):
return self.db.get_user_info_by_google_id(google_id)
def create_user(self, user_info):
return self.db.create_user(user_info)
def generate_session_key(self, user_data):
user_info = self._get_user_info_by_google_id(user_data["id"])
if user_info:
user_id, data = user_info
else:
user_id = self.create_user(user_data)
return self.db.new_session(user_id)
def delete_session_key(self, session_key):
session = self.db.get_session(session_key)
if not session:
return None
key, user_id, created = session
self.db.delete_sessions_for_user(user_id)
def valid_session_key(self, session_key):
return self.db.get_session(session_key) is not None
def close(self):
self.db.close()
def Login(post_data):
# 校验请求参数
if 'ValidateCode' not in session:
return jsonFail("Please get the ValidateCode first")
check_list = ["ValidateCode", "UserName", "Password"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
if post_data['ValidateCode'] != session['ValidateCode']:
return jsonFail('ValidateCode error')
try:
post_data["Password"] = decryption(post_data["Password"])
except Exception:
return jsonFail('password ras decryption error')
else:
if post_data["Password"] == "解密失败":
return jsonFail('password ras decryption error')
# 数据库校验账号密码是否正确
try:
db = Database()
user, err = db.check_login(post_data["UserName"],
post_data["Password"])
except Exception as err:
return jsonFail(err)
else:
db.close()
if err:
return jsonFail(err)
# 保存登录信息
session['ID'] = user["id"]
session['UserName'] = user["username"]
session['Password'] = user["password"]
session['Type'] = user["type"]
# 删除不需要返回给前端的参数
del user["createtime"]
del user["password"]
del user["lastupdate"]
del user["status"]
return jsonSuccess(user)
def SelectInfo(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
try:
db = Database()
result = db.select_info(session["ID"], session["Type"], post_data)
except Exception as err:
print_exc()
return jsonFail(err)
else:
db.close()
return jsonSuccess(result)
def FamilyInfo(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
check_list = ["FamilyId"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
try:
db = Database()
result = db.select_family_info(post_data["FamilyId"])
except Exception as err:
return jsonFail(err)
else:
db.close()
return jsonSuccess(result)
def ListFamily():
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
try:
db = Database()
family = db.select_family(session["ID"], session['Type'])
except Exception as err:
return jsonFail(err)
else:
db.close()
# 删除不需要返回给前端的参数
for index in range(len(family)):
del family[index]["createtime"]
del family[index]["lastupdate"]
del family[index]["user_id"]
return jsonSuccess(family)
def ListUser():
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
try:
db = Database()
result = db.select_user(session["ID"], session["Type"])
except Exception as err:
return jsonFail(err)
else:
for index in range(len(result)):
del result[index]["createtime"]
del result[index]["lastupdate"]
del result[index]["status"]
db.close()
return jsonSuccess(result)
def AddAddr(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
check_list = ["Community", "Building", "Dormitory"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
try:
db = Database()
db.insert_addr(session["ID"], post_data["Community"],
post_data["Building"], post_data["Dormitory"])
except Exception as err:
return jsonFail(err)
else:
db.close()
return jsonSuccess("Success")
def DeleteUser(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
check_list = ["UserId"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
try:
db = Database()
err = db.delete_user(post_data["UserId"])
except Exception as err:
return jsonFail(err)
else:
if err:
return jsonFail(err)
db.close()
return jsonSuccess("Success")
def run(self):
start_time = time.time()
spider2 = Captch(proxy)
mysqldb = Database()
while not self.stop_flag:
while not news_queue.empty():
news_data = news_queue.get()
title = translate(news_data['title'])
flag = spider2.captch(title)
news_data.update({
'title': title,
'compliance': flag
})
mysqldb.insert(news_data)
if time.time() - start_time > self.timeout:
self.stop_flag = True
mysqldb.close()
print("百度验证线程-{}关闭.".format(self.name), end="")
def Register(post_data):
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
if session['Type'] not in [2, 3]:
return jsonFail("暂无权限访问")
check_list = ["UserName", "Type"]
check_result = checkPostData(check_list, post_data)
if check_result:
return jsonFail(check_result)
if "Password" not in post_data:
post_data["Password"] = "******"
if post_data["Type"] == 3:
return jsonFail("暂无权限访问")
if post_data["Type"] == 2 and session['Type'] != 3:
return jsonFail("暂无权限访问")
if post_data["Type"] not in [1, 2, 3]:
return jsonFail("暂无权限访问")
if post_data["Type"] == 1 and "PersonnelId" not in post_data:
return jsonFail("PersonnelId must exist")
try:
db = Database()
err = db.insert_user(session["ID"], post_data["UserName"],
post_data["Password"], post_data["Type"],
post_data["PersonnelId"])
except Exception as err:
return jsonFail(err)
else:
if err:
return jsonFail(err)
db.close()
return jsonSuccess("Success")
def ListPersonnel():
# 校验请求参数是否符合预期
if ("ID" not in session) or ("Type" not in session):
return jsonFail("Login information is invalid, please to login")
# 如果是管理员登录
if session["Type"] in [2, 3]:
try:
result = []
db = Database()
family = db.select_family(session["ID"], session['Type'])
for index, tmp in enumerate(family):
# 查询个人信息
personnel = db.select_personnel(tmp["id"])
# 删除不需要返回给前端的参数
for index in range(len(personnel)):
del personnel[index]["createtime"]
del personnel[index]["lastupdate"]
result.append(personnel)
except Exception as err:
return jsonFail(err)
else:
db.close()
return jsonSuccess(result)
# 如果是普通用户登录
else:
try:
db = Database()
result = db.select_personnel_by_normal_user(session["ID"])
except Exception as err:
return jsonFail(err)
else:
db.close()
return jsonSuccess(result)
from transforms import UpdateDataTransform
from db import Database
from country_aliases_transforms import CountryAliasesTransforms
from missing_country_transforms import MissingCountryTransforms
from city_aliases_transforms import CityAliasesTransforms
from skill_aliases_transforms import SkillAliasesTransforms
db = Database()
db.connect()
transform_batches = [
CountryAliasesTransforms(db),
MissingCountryTransforms(db),
CityAliasesTransforms(db),
SkillAliasesTransforms(db)
]
for transform_batch in transform_batches:
for transform in transform_batch.get_transforms():
transform.run()
db.close()
class Wagtail:
def __init__(self):
self.principal = get_principal()
self.config = ConfigManager(self)
self.db = Database(self)
self.zgram_queue = queue.Queue()
self.error_queue = queue.Queue()
def zgram_handler(zp, zgram):
self.zgram_queue.put(zgram)
# we send a SIGWINCH signal to ourselves,
# making ncurses think that the window was resized.
# this is the best way I know of to interrupt ncurses
# in blocking mode.
os.kill(os.getpid(), SIGWINCH)
def error_handler(error):
self.error_queue.put(error)
os.kill(os.getpid(), SIGWINCH)
self.zpipe = zpipe.ZPipe(['./zpipe/zpipe'], zgram_handler,
error_handler)
self.zpipe.subscribe('message', '*', self.principal)
for class_, instance, recipient, _ in self.db.get_subscriptions():
self.zpipe.subscribe(class_, instance, recipient)
def __enter__(self):
return self
def __exit__(self, exc_type, exc_value, traceback):
self.db.close()
self.zpipe.close()
def get_config_path(self):
if os.getenv('XDG_CONFIG_HOME') is not None:
return os.path.join(os.getenv('XDG_CONFIG_HOME'), 'wagtail',
'config.py')
return os.path.expanduser('~/.config/wagtail/config.py')
def get_database_path(self):
if os.getenv('XDG_CONFIG_HOME') is not None:
return os.path.join(os.getenv('XDG_CONFIG_HOME'), 'wagtail',
'database.sqlite3')
return os.path.expanduser('~/.config/wagtail/database.sqlite3')
def event_quit(self):
self.should_quit = True
def event_cmdline_open(self, initial_input=''):
self.window_stack.append(CommandLine(self, initial_input))
def event_cmdline_close(self):
assert isinstance(self.window_stack[-1], CommandLine)
self.window_stack.pop().close()
def event_cmdline_exec(self, cmdline):
events = parse_cmdline_into_events(cmdline)
self.handle_events(events)
def event_status(self, text):
self.status_bar.set_status(text)
def event_zwrite(self, opts):
if ((opts.class_.lower() == 'message')
and ((opts.instance.lower() == 'personal') or
(opts.instance.lower() == 'urgent'))
and (len(opts.recipients) == 0)):
self.status_bar.set_status(
'Cannot send personal message with no recipient.')
else:
self.window_stack.append(ZephyrgramComposer(self, opts))
def event_composer_close(self):
assert (isinstance(self.window_stack[-1], ZephyrgramComposer))
self.window_stack.pop().close()
def event_send_zephyrgrams(self, zgrams):
for zgram in zgrams:
self.zpipe.zwrite(zgram.to_zpipe())
if zgram.is_personal():
# this is a personal message. we won't get it back, so
# we should save a copy.
# TODO: what if the zgram actually fails to send?
zgram.time = zgram.time or datetime.now()
zgram.sender = zgram.sender or self.principal
self.db.append_message(zgram)
self.main_window.redraw() # to display the personals
def event_subscribe(self, class_, instance, recipient):
new_subs = self.db.subscribe(class_, instance, recipient)
if len(new_subs) == 0:
self.status_bar.set_status('Error: Already subscribed.')
else:
for args in new_subs:
self.zpipe.subscribe(*args)
self.status_bar.set_status('Subscribed successfully.')
def event_unsubscribe(self, class_, instance, recipient):
unsubs = self.db.unsubscribe(class_, instance, recipient)
if len(unsubs) == 0:
self.status_bar.set_status(
('Error: either not subscribed, or trying to unsubscribe '
'from unclass.'))
else:
for args in unsubs:
self.zpipe.unsubscribe(*args)
self.status_bar.set_status('Unsubscribed successfully.')
def event_import_zsubs(self, path):
if path is None:
path = os.path.expanduser('~/.zephyr.subs')
try:
with open(path) as zsubs:
processed = 0
skipped = 0
for line in zsubs:
if len(line.strip()) == 0:
continue
parts = line.strip().split(',')
if len(parts) != 3:
skipped += 1
else:
for sub in self.db.subscribe(*parts):
self.zpipe.subscribe(*sub)
processed += 1
self.status_bar.set_status(
'Done, {} lines processed successfuly, {} skipped.'.format(
processed, skipped))
except FileNotFoundError:
self.status_bar.set_status(
'Error: file {} not found.'.format(path))
def event_reload_config(self):
self.config.reload()
self.main_window.redraw() # styling rules might've changed
def event_filter(self, new_filter):
self.main_window.set_filter(new_filter)
def handle_events(self, events):
for event, *event_args in events:
# call self.event_{eventname}(*event_args)
getattr(self, 'event_{}'.format(event))(*event_args)
def main_curses(self, screen):
# tell the terminal to not send us SIGINTs when Ctrl+C is pressed
tty_attributes = termios.tcgetattr(sys.stdin)
tty_attributes[3] &= ~termios.ISIG
termios.tcsetattr(sys.stdin, termios.TCSANOW, tty_attributes)
self.screen = screen
curses.use_default_colors()
curses.curs_set(0)
self.status_bar = StatusBar(self)
self.main_window = MainWindow(self)
curses.doupdate()
# this window stack kind of duplicates the one kept by
# curses.panel — perhaps we should just use that directly?
self.window_stack = [self.status_bar, self.main_window]
self.should_quit = False
while True:
key = screen.get_wch() # blocks indefinitely
if key == curses.KEY_RESIZE:
# this could be either a legitimate SIGWINCH, notifying us that
# the terminal was resized,
# or a fake SIGWINCH, generated by the zgram_handler
# (see __init__) notifying us that we have new zephyrgrams
# to take from the queue
while not self.zgram_queue.empty():
zgram = Zephyrgram.from_zpipe(self.zgram_queue.get())
self.db.append_message(zgram)
# if this is in class 'ununclass', and we aren't yet
# subscribed to 'unununclass', do so
undepth, class_stripped = take_unprefix(zgram.class_)
new_unclass = 'un' + zgram.class_
for instance, recipient in self.db.update_undepth(
class_stripped, undepth + 1):
self.zpipe.subscribe(new_unclass, instance, recipient)
for window in self.window_stack:
window.update_size()
else:
self.status_bar.clear_status()
self.should_quit = False
events = self.window_stack[-1].handle_keypress(key)
self.handle_events(events)
if self.should_quit:
break
if not self.error_queue.empty():
error = self.error_queue.get()
self.status_bar.set_status('Error in {}: {}'.format(
error.operation, error.message))
curses.doupdate()
def main(self):
curses.wrapper(self.main_curses)
transaction.add_query(q.select, _k, 0, [1, 1, 1, 1, 1])
q = Query(grades_table)
transaction.add_query(q.increment, key, 1)
transaction_workers[i % num_threads].add_transaction(transaction)
threads = []
for transaction_worker in transaction_workers:
threads.append(threading.Thread(target=transaction_worker.run, args=()))
for i, thread in enumerate(threads):
# print('Thread', i, 'started')
thread.start()
for i, thread in enumerate(threads):
thread.join()
# print('Thread', i, 'finished')
num_committed_transactions = sum(t.result for t in transaction_workers)
# print(num_committed_transactions, 'transaction committed.')
query = Query(grades_table)
s = query.sum(keys[0], keys[-1], 1)
if s != num_committed_transactions * num_operations:
print('Expected sum:', num_committed_transactions * num_operations,
', actual:', s, '. Failed.')
else:
print('Pass.' + "Number of commited:" + str(num_committed_transactions))
db.close()
def main():
parser = argparse.ArgumentParser(description='Files of rooms and students')
parser.add_argument('--in_dir_rooms',
type=str,
default="rooms.json",
help='Input path for file with rooms')
parser.add_argument('--in_dir_students',
type=str,
default="students.json",
help='Input path for file with students')
parser.add_argument('--extension',
type=str,
default="json",
help='Input extension for output file')
parser.add_argument('--db_user',
type=str,
default="root",
help='Input user for db')
parser.add_argument('--db_password',
type=str,
default="",
help='Input password for db')
parser.add_argument('--db_name',
type=str,
default="task4",
help='Input database name')
args = parser.parse_args()
db = Database(args.db_user, args.db_password)
# Create db if not exists
db.execute(create_db_query(args.db_name))
db.select_database(args.db_name)
# Create tables if not exist
db.execute(create_table_room_query())
db.execute(create_table_students_query())
# Parse files
rooms_reader = JsonReader(args.in_dir_rooms)
rooms_list = rooms_reader.read()
students_reader = JsonReader(args.in_dir_students)
students_list = students_reader.read()
# Insert values into tables
db.executemany(insert_rooms_query(), rooms_list)
db.executemany(insert_students_query(), students_list)
# Create writer to save results
writer = JSWriter()
if args.extension == 'xml':
writer = XMLWriter()
timing_results = {"name": select_queries.keys(), "w/o": [], "w": []}
for query_name in select_queries:
start_time = time.time()
result = db.query(select_queries[query_name]())
end_time = time.time()
writer.write_in_file(query_name, result)
timing_results["w/o"].append(end_time - start_time)
# Create index1 for Students table
# result = db.query(add_index1_query())
# Create index2 for Students table
result = db.query(add_index2_query())
for query_name in select_queries:
start_time = time.time()
result = db.query(select_queries[query_name]())
end_time = time.time()
writer.write_in_file(query_name, result)
timing_results["w"].append(end_time - start_time)
# Close connection
db.close()
# Print time results
print(pd.DataFrame(timing_results))
class MainWin(QtWidgets.QMainWindow):
def __init__(self, *args, **kwargs):
super().__init__(*args, **kwargs)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.central_widget = QtWidgets.QStackedWidget()
self.setCentralWidget(self.central_widget)
self.main_menu = Menu()
self.database = Database("stocktracker", "db.ini", self.ui.statusbar.showMessage)
self.database.connect_db()
self.database.close()
self.add_trans = AddTransactionForm(self.database, self.ui.statusbar.showMessage)
self.add_stock = AddStockForm(self.database)
self.list_trans = ListTransactionForm(self.database)
self.list_stocks = ListStockForm(self.database)
self.distribution = DistributionForm(self.database)
self.central_widget.addWidget(self.main_menu)
self.central_widget.addWidget(self.add_trans)
self.central_widget.addWidget(self.add_stock)
self.central_widget.addWidget(self.list_trans)
self.central_widget.addWidget(self.list_stocks)
self.central_widget.addWidget(self.distribution)
self.ui.actionExit.triggered.connect(self.close)
self.main_menu.exited.connect(self.close)
self.main_menu.ui.button_add_transaction.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.add_trans))
self.main_menu.ui.button_list_transactions.clicked.connect \
(self.list_trans_event)
self.main_menu.ui.button_list_stocks.clicked.connect \
(self.list_stock_event)
self.main_menu.ui.button_add_stock.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.add_stock))
self.main_menu.ui.button_distribution.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.distribution))
self.add_trans.ui.pushButton_cancel.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.main_menu))
self.list_trans.ui.return_btn.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.main_menu))
self.add_stock.ui.pushButton_cancel.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.main_menu))
self.list_stocks.ui.return_btn.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.main_menu))
self.distribution.ui.return_btn.clicked.connect \
(lambda: self.central_widget.setCurrentWidget(self.main_menu))
self.central_widget.setCurrentWidget(self.main_menu)
self.resize(1400,600)
def list_trans_event(self):
self.list_trans.load_entries()
self.central_widget.setCurrentWidget(self.list_trans)
def list_stock_event(self):
self.list_stocks.load_entries()
self.central_widget.setCurrentWidget(self.list_stocks)
def closeEvent(self, event):
self.database.close()
self.close()
def main():
print "database structure will be created, any exising database/user WILL BE DROPPED"
print "enter mysql root username (leave blank for \"root\"):"
username = raw_input()
password = getpass()
if username == "" or username == None:
username = "******"
try:
conn = Database(username, password, database=None)
except:
print "error connecting to mysql database as root"
sys.exit(1)
conn.query("DROP DATABASE IF EXISTS comp4321;")
conn.query("GRANT USAGE ON *.* TO 'user4321'@'localhost';")
conn.query("DROP USER 'user4321'@'localhost';")
conn.query("CREATE DATABASE comp4321;")
conn.query("GRANT ALL PRIVILEGES ON comp4321.* To 'user4321'@'localhost' IDENTIFIED BY 'gryphon';")
conn.close()
userConn = Database()
userConn.query("CREATE TABLE Documents (\
document_id int NOT NULL AUTO_INCREMENT,\
document_url varchar(255) NOT NULL,\
document_title varchar(255) NOT NULL,\
document_size int NOT NULL,\
modified DATETIME,\
fetched TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\
max_tf int DEFAULT 1,\
document_chars int NOT NULL,\
PRIMARY KEY (document_id),\
UNIQUE (document_url),\
INDEX url_index (document_url));")
userConn.query("CREATE TABLE Links (\
parent_id int NOT NULL,\
child_url varchar(255) NOT NULL,\
PRIMARY KEY (parent_id, child_url),\
FOREIGN KEY (parent_id) REFERENCES Documents(document_id) ON DELETE CASCADE\
);")
#FOREIGN KEY (child_id) REFERENCES Documents(document_id) ON DELETE CASCADE
#disabled this constraint for the time being, to allow links to children which have not yet been scraped. Eventually it may be bettter to add this back in and add rows to Links when processing the child document instead of the parent document (would require keeping track of which document adds pages to the scrape queue)
#also this would allow child_url to become child_id again
#eventually include idf in this table
userConn.query("CREATE TABLE KeyWords (\
word_id int NOT NULL AUTO_INCREMENT,\
word VARCHAR(255) NOT NULL,\
document_frequency int NOT NULL COMMENT 'the number of unique documents which contain at least one instance of the word',\
PRIMARY KEY (word_id),\
UNIQUE (word),\
INDEX word_index (word) USING HASH);")
userConn.query("CREATE TABLE InvertedIndex (\
word_id int NOT NULL,\
document_id int NOT NULL,\
term_frequency int NOT NULL DEFAULT 0 COMMENT 'the number of appearances this word (word_id) makes in the document (document_id)',\
in_title TINYINT(1) DEFAULT 0,\
PRIMARY KEY (word_id, document_id),\
INDEX forward_index (document_id),\
FOREIGN KEY (word_id) REFERENCES KeyWords(word_id) ON DELETE CASCADE,\
FOREIGN KEY (document_id) REFERENCES Documents(document_id) ON DELETE CASCADE);")
userConn.query("CREATE TABLE IndexPositions (\
word_id int NOT NULL,\
document_id int NOT NULL,\
position int NOT NULL COMMENT 'the word position of this this word (word_id) in the document (document_id)',\
PRIMARY KEY (word_id, document_id, position),\
FOREIGN KEY (word_id, document_id) REFERENCES InvertedIndex(word_id, document_id) ON DELETE CASCADE,\
FOREIGN KEY (document_id) REFERENCES Documents (document_id) ON DELETE CASCADE);")
userConn.close()
def get_all():
db = Database()
resp = db.dump()
db.close()
return jsonify(resp)
def query(self, data):
db = Database(connect=True)
q = db.query(data)
db.close()
return q
def select(self, query): db = Database(connect=True) q = db.select(query) db.close() return q
parser = argparse.ArgumentParser()
parser.add_argument("--port",
type=int,
help="The port for the server run locally",
default=8000)
parser.add_argument("--sensitivity",
type=float,
help="Model sensitivity",
default=0.35)
parser.add_argument("--speed_factor",
type=float,
help="Keyboard speed factor playback",
default=0)
return parser.parse_args(argv)
args = parse_argument(sys.argv[1:])
if (len(database.get_all()) > 0):
listenter = Listener(sensitivity=args.sensitivity,
speed_factor=args.speed_factor)
listenter.start()
app.run('0.0.0.0', args.port)
sound_recorder.end()
try:
listenter.stop()
except:
pass
database.close()
class TestDatabase(unittest.TestCase):
def setUp(self):
self.db = Database()
self.db.init_tables()
def tearDown(self):
if self.db.conn.closed == 0:
self.db.clear()
self.db.close()
def test_init_tables_Users(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Users')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Users table exists.")
def test_init_tables_Lists(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Lists')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Lists table exists.")
def test_init_tables_Notes(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Notes')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Notes table exists.")
def test_init_tables_Images(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Images')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Images table exists.")
def test_init_tables_Files(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Files')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Files table exists.")
def test_init_tables_Voices(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Voices')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Voices table exists.")
def test_init_tables_Audio(self):
self.db.cur.execute(
"""SELECT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Audio')""")
self.assertIsNotNone(self.db.cur.fetchone(), "Audio table exists.")
def test_new_user(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.cur.execute("SELECT first_name, State FROM Users WHERE userID=%s", (441220162,))
data = self.db.cur.fetchone()
self.assertTrue((data[0] == 'Tai' and data[1] == 0), "New user created.")
def test_new_user_invalid_name(self):
id = 441220162
name = "a" * 51
state = 0
self.db.new_user(id, name, state)
self.db.cur.execute("SELECT first_name, State FROM Users WHERE userID=%s", (id,))
data = self.db.cur.fetchone()
self.assertIsNone(data, "Invalid username.")
def test_user_exists_false(self):
id = 441220162
self.assertFalse(self.db.user_exists(id), "Not created user does not exist.")
def test_user_exists_true(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertTrue(self.db.user_exists(id), "Created user exists.")
def test_set_state(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.set_state(id, 3)
self.db.cur.execute("SELECT State FROM Users WHERE userID=%s", (441220162,))
self.assertEqual(self.db.cur.fetchone()[0], 3, "State set.")
def test_init_state(self):
id = 441220162
name = "Tai"
state = 3
self.db.new_user(id, name, state)
self.db.init_state(id)
self.db.cur.execute("SELECT State FROM Users WHERE userID=%s", (441220162,))
self.assertEqual(self.db.cur.fetchone()[0], 0, "Init state set.")
def test_get_state(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.set_state(id, 3)
self.db.cur.execute("SELECT State FROM Users WHERE userID = %s", (441220162,))
self.assertEqual(self.db.cur.fetchone()[0], self.db.get_state(441220162)[0], "Get state.")
def test_new_list(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.cur.execute("SELECT ListName FROM Lists WHERE userID = %s", (441220162,))
self.assertEqual(self.db.cur.fetchone()[0], "List", "New list added.")
def test_new_list_invalid_name(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
list_name = "a"*51
self.db.new_list(id, list_name)
self.db.cur.execute("SELECT ListName FROM Lists WHERE userID = %s", (441220162,))
self.assertIsNone(self.db.cur.fetchone(), "Invalid list name.")
def test_set_list(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.cur.execute("SELECT ListID FROM Users WHERE userID=%s", (441220162,))
listID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT ListName FROM Lists WHERE ListID = %s", (listID,))
self.assertEqual(self.db.cur.fetchone()[0], "List", "List set.")
def test_set_list_notExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.set_list(id, 0)
self.db.cur.execute("SELECT ListID FROM Users WHERE userID=%s", (id,))
self.assertIsNone(self.db.cur.fetchone()[0], "List is not set if does not exist.")
def test_get_cur_list_numbNotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertIsNone(self.db.get_cur_list_numb(id), "Got current list number if does not exist.")
def test_get_cur_list_numbExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.assertEqual(self.db.get_cur_list_numb(id)[0], 0, "Got current list number.")
def test_new_note_listExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.cur.execute("SELECT NoteName FROM Notes WHERE userID = %s", (id,))
self.assertEqual(self.db.cur.fetchone()[0], "Note", "New note added.")
def test_new_note_invalid_name(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
note_name = "a" * 51
self.db.new_note(id, note_name)
self.db.cur.execute("SELECT NoteName FROM Notes WHERE userID = %s", (id,))
self.assertIsNone(self.db.cur.fetchone(), "Invalid note name.")
def test_new_note_listNotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_note(id, "Note1")
self.db.cur.execute("SELECT NoteName FROM Notes WHERE userID = %s", (id,))
self.assertIsNone(self.db.cur.fetchone(), "New note added.")
def test_delete_list_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.delete_list(id)
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "List"))
self.assertIsNone(self.db.cur.fetchone(), "List deleted")
def test_delete_list_checkNumb(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List0")
self.db.new_list(id, "List1")
self.db.set_list(id, 0)
self.db.delete_list(id)
self.db.cur.execute("SELECT numb FROM Lists WHERE userID = %s and ListName = %s", (id, "List1"))
self.assertEqual(self.db.cur.fetchone()[0], 0, "Number changed")
def test_delete_list_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.delete_list(id)
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "List"))
self.assertIsNotNone(self.db.cur.fetchone(), "List is not deleted")
def test_edit_list_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.edit_list(id, "New_list")
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "New_list"))
new_name = self.db.cur.fetchone()
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "List"))
old_name = self.db.cur.fetchone()
self.assertTrue((new_name is not None) and (old_name is None), "List edited")
def test_edit_list_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.edit_list(id, "New_list")
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "New_list"))
new_name = self.db.cur.fetchone()
self.db.cur.execute("SELECT * FROM Lists WHERE userID = %s and ListName = %s", (id, "List"))
old_name = self.db.cur.fetchone()
self.assertTrue((new_name is None) and (old_name is not None), "List is not edited if does not exist")
def test_set_note_listExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID=%s", (id,))
noteID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT NoteName FROM Notes WHERE NoteID = %s", (noteID,))
self.assertEqual(self.db.cur.fetchone()[0], "Note", "Note set.")
def test_set_note_listNotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID=%s", (id,))
self.assertIsNone(self.db.cur.fetchone()[0], "List set.")
def test_set_note_noteNotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_note(id, 0)
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID=%s", (id,))
self.assertIsNone(self.db.cur.fetchone()[0], "List set.")
def test_delete_note_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.delete_note(id)
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "Note"))
self.assertIsNone(self.db.cur.fetchone(), "Note deleted")
def test_delete_note_checkNumb(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note0")
self.db.new_note(id, "Note1")
self.db.set_note(id, 0)
self.db.delete_note(id)
self.db.cur.execute("SELECT numb FROM Notes WHERE userID = %s", (id,))
self.assertEqual(self.db.cur.fetchone()[0], 0, "Number changed")
def test_delete_note_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.delete_note(id)
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "Note"))
self.assertIsNotNone(self.db.cur.fetchone(), "Note is not deleted if does not exist")
def test_edit_note_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.edit_note(id, "New_note")
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "New_note"))
new_name = self.db.cur.fetchone()
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "Note"))
old_name = self.db.cur.fetchone()
self.assertTrue((new_name is not None) and (old_name is None), "Note edited")
def test_edit_note_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.edit_note(id, "New_note")
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "New_note"))
new_name = self.db.cur.fetchone()
self.db.cur.execute("SELECT * FROM Notes WHERE userID = %s and NoteName = %s", (id, "Note"))
old_name = self.db.cur.fetchone()
self.assertTrue((new_name is None) and (old_name is not None), "Note is not edited if does not exist")
def test_get_lists_exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List0")
self.db.new_list(id, "List1")
data = self.db.get_lists(id)
self.assertTrue((len(data) == 2) and (data[0][0] == "List0") and (data[1][0] == "List1"), "Lists exist.")
def test_get_lists_notExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
data = self.db.get_lists(id)
self.assertTrue(len(data) == 0, "Lists do not exist.")
def test_get_notes_exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note0")
self.db.new_note(id, "Note1")
data = self.db.get_notes(id, 0)
self.assertTrue((len(data) == 2) and (data[0][0] == "Note0") and (data[1][0] == "Note1"), "Notes exist.")
def test_get_notes_notExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
data = self.db.get_notes(id, 0)
self.assertTrue(len(data) == 0, "Notes do not exist.")
def test_get_notes_listNotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.set_list(id, 0)
self.assertIsNone(self.db.get_notes(id, 0), "Notes do not exist.")
def test_new_image_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_image(id, "123")
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID = %s", (id,))
noteID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT NoteImage FROM Images WHERE NoteID = %s", (noteID,))
data = self.db.cur.fetchone()[0]
self.assertEqual(data, "123", "New image added.")
def test_new_image_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_image(id, "123")
self.db.cur.execute("SELECT * FROM Images WHERE NoteImage = %s", ("123",))
self.assertIsNone(self.db.cur.fetchone(), "New image is not added.")
def test_new_file_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_file(id, "123")
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID = %s", (id,))
noteID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT NoteFile FROM Files WHERE NoteID = %s", (noteID,))
data = self.db.cur.fetchone()[0]
self.assertEqual(data, "123", "New file added.")
def test_new_file_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_file(id, "123")
self.db.cur.execute("SELECT * FROM Files WHERE NoteFile = %s", ("123",))
self.assertIsNone(self.db.cur.fetchone(), "New file is not added.")
def test_new_voice_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_voice(id, "123")
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID = %s", (id,))
noteID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT NoteVoice FROM Voices WHERE NoteID = %s", (noteID,))
data = self.db.cur.fetchone()[0]
self.assertEqual(data, "123", "New voice added.")
def test_new_voice_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_voice(id, "123")
self.db.cur.execute("SELECT * FROM Voices WHERE NoteVoice = %s", ("123",))
self.assertIsNone(self.db.cur.fetchone(), "New voice is not added.")
def test_new_audio_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_audio(id, "123")
self.db.cur.execute("SELECT NoteID FROM Users WHERE userID = %s", (id,))
noteID = self.db.cur.fetchone()[0]
self.db.cur.execute("SELECT NoteAudio FROM Audio WHERE NoteID = %s", (noteID,))
data = self.db.cur.fetchone()[0]
self.assertEqual(data, "123", "New audio added.")
def test_new_audio_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_audio(id, "123")
self.db.cur.execute("SELECT * FROM Audio WHERE NoteAudio = %s", ("123",))
self.assertIsNone(self.db.cur.fetchone(), "New audio is not added.")
def test_get_images_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_image(id, "123")
self.db.new_image(id, "abc")
data = self.db.get_images(id)
self.assertTrue((data[0][0] == "123") and (data[1][0] == "abc") and (len(data) == 2), "Got images.")
def test_get_images_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertIsNone(self.db.get_images(id), "No images.")
def test_get_files_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_file(id, "123")
self.db.new_file(id, "abc")
data = self.db.get_files(id)
self.assertTrue((data[0][0] == "123") and (data[1][0] == "abc") and (len(data) == 2), "Got files.")
def test_get_files_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertIsNone(self.db.get_files(id), "No files.")
def test_get_voices_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_voice(id, "123")
self.db.new_voice(id, "abc")
data = self.db.get_voices(id)
self.assertTrue((data[0][0] == "123") and (data[1][0] == "abc") and (len(data) == 2), "Got voices.")
def test_get_voices_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertIsNone(self.db.get_voices(id), "No voices.")
def test_get_audio_Exists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.db.new_list(id, "List")
self.db.set_list(id, 0)
self.db.new_note(id, "Note")
self.db.set_note(id, 0)
self.db.new_audio(id, "123")
self.db.new_audio(id, "abc")
data = self.db.get_audio(id)
self.assertTrue((data[0][0] == "123") and (data[1][0] == "abc") and (len(data) == 2), "Got audio.")
def test_get_audio_NotExists(self):
id = 441220162
name = "Tai"
state = 0
self.db.new_user(id, name, state)
self.assertIsNone(self.db.get_audio(id), "No audio.")
def test_close(self):
self.db.close()
self.assertTrue(self.db.conn != 0, "Closed")
class Solver:
def __init__(self, db_dir, db_name, table, drop_on_init, t, R, t0, tf, dt):
"""
initialise kinematics solver
param db_dir: simulation results directory
param db_name: simulation database name
param table: table name
param drop_on_int: remove existing database if exists
param t: symbolic variable t - removes req to import sympy
param R: symbolic equation for change in position over time
vector in the form R = [rxi, ryj, rzk]
param t0: initial time
param tf: final time
param dt: change in time
"""
print('\n', '=' * 50, '\n\nInitialising solver...')
columns = self.get_columns()
print('\nvariables to write to db:', columns)
self.db = Database(db_dir, db_name, table, drop_on_init, columns)
self.t = t
if not self.validate_time(t0, tf, dt):
print('Invalid time input...')
raise ValueError('Time must be positive and numeric')
self.times = np.arange(t0, tf, dt, dtype='float')
self.n_steps = (tf - t0) / dt
print('\nt0 = {}\ntf = {}\ndt = {}\nsteps = {}\n'.format(
t0, tf, dt, self.n_steps))
self.R = R
self.V = self.vector_derivative(R, t, 'position', 'velocity')
self.A = self.vector_derivative(self.V, t, 'velocity', 'acceleration')
self.at = self.vector_magnitude(self.V).diff(t)
self.system_equations_info()
print('\n', '=' * 50)
@staticmethod
def validate_time(t0, tf, dt):
"""
validate time inputs should be numeric and > 0
param t0: initial time (s)
param tf: final time (s)
param dt: change in time (dt)
"""
try:
t0 = float(t0)
tf = float(tf)
dt = float(dt)
except Exception:
return False
if t0 >= 0 and tf > 0 and dt > 0:
return True
return False
def system_equations_info(self):
"""
print equation information to console
"""
print('\nPosition, R = {}\n'.format(self.R))
print(' rx =', self.R[0])
print(' ry =', self.R[1])
print(' rz =', self.R[2])
print('\nVelocity, V = {}\n'.format(self.V))
print(' vx =', self.V[0])
print(' vy =', self.V[1])
print(' vz =', self.V[2], '\n')
print('\nAccekeration, A = {}\n'.format(self.A))
print(' ax =', self.A[0])
print(' ay =', self.A[1])
print(' az =', self.A[2])
def evaluate_state(self, ti):
"""
evaluate position, velocity, acceleration etc. at time ti
param ti: current time step
"""
# NOTE: no. of parms in insert_data must match no. of columns
ti_r = self.d2r(ti)
r_mag, R = self.eval_v(self.R, ti_r)
v_mag, V = self.eval_v(self.V, ti_r)
a_mag, A = self.eval_v(self.A, ti_r)
v_theta = [self.r2d(tht) for tht in self.direction_angles(V, v_mag)]
a_psi = [self.r2d(psi) for psi in self.direction_angles(A, a_mag)]
ut = self.unit_vector(v_vmag=(V, v_mag))
ub = self.unit_vector(v_a=(V, A))
un = self.unit_vector(u1_u2=(ub, ut))
at = self.at.subs(self.t, ti_r)
an = np.dot(A, un)
rho = v_mag**2 / an
Rc = R + (rho * un)
rc_mag = self.vector_magnitude(Rc)
self.insert_data(ti, R[0], R[1], R[2], r_mag, V[0], V[1], V[2], v_mag,
A[0], A[1], A[2], a_mag, an, at, rho, Rc[0], Rc[1],
Rc[2], rc_mag)
@staticmethod
def get_columns():
"""
table names for results database
return: string of variable names separated by ,
"""
# NOTE: these are the column names used in the db
return """
\'t\', \'rx\', \'ry\', \'rz\', \'r_mag\',
\'vx\', \'vy\', \'vz\', \'v_mag\',
\'ax\', \'ay\', \'az\', \'a_mag\', \'an\', \'at\',
\'rho\', \'rcx\', \'rcy\', \'rcz\', \'rc_mag\'
"""
def vector_derivative(self, v, wrt, diff=None, result=None):
"""
differentiate vector components wrt a symbolic variable
param v: vector to differentiate
param wrt: symbolic variable
param diff: (str) quantity being differentiated
param result: (str) name of resultant quantity
return V: velcoity vector and components in x, y, z
"""
if diff is not None and result is not None:
print('d({})/d{} = {}'.format(diff, wrt, result))
return [c.diff(wrt) for c in v]
def eval_v(self, v, ti):
"""
evaluate vector components and magnitude @ti
param v: symbolic vector expression to evaluate @ti
param ti: time step
return mag, v_xyz: magnitude of vector and components evaluated @ti
"""
v_xyz = [c.subs(self.t, ti).evalf() for c in v]
mag = self.vector_magnitude(v_xyz)
return mag, (v_xyz)
def direction_angles(self, v, mag=None):
"""
compute direction angles a vector makes with + x,y,z axes
param v: vector with x, y, z components
param mag: magnitude of vector
"""
if mag is None:
mag = self.vector_magnitude(v)
return [acos(c / mag) for c in v]
def unit_vector(self, v_vmag=None, v_a=None, u1_u2=None):
"""
Calculate a unit vector using one of three input parameters.
Two ways to compute unit vector:
1. using vector and vector magnitude
e.g. unit tangent vector is given by v/v_mag
param v: vector with components x,y,z
param v_mag: magnitude of vector
2. using vectors v and a.
e.g. velocity and acceleration vectors can be used to
determine the unit binormal. ub = cross(v,a)/mag(cross(v,a))
3. using orthogonal unit vectors.
e.g. unit tangent and unit normal vectors can be used to
determine the unit binormal. ub = corss(ut, un)
2. and 3. used generally used to calculate one of two vectors.
a. unit binormal
Orthogonal unit vectors in the direction of velocity (ut)
and in the direction towards the centre of curvature (un)
form the osculating plane. The unit binormal vector is the
vector perpendicular to the osculating plane.
param v_a: tuple of vectors velocity vector and acceleration vector
param ua_ub: tuple of unit tangent and unit normal vectors
return ub: unit binormal vector
b. unit normal
The unit normal is the unit vector in the direction of the
centre of curvature from the particle at position P.
param Rcp_rho: tuple position vector of C relative to P and rho
param ub_ut: tuple of unit binormal and unit tangent vectors
return un: unit normal vector
"""
if v_vmag is not None:
v, mag = v_vmag[0], v_vmag[1]
if mag is None:
mag = self.vector_magnitude(v)
return [c / mag for c in v]
if v_a is not None:
v, a = v_a[0], v_a[1]
b = np.cross(v, a)
return self.unit_vector(v_vmag=(b, None))
if u1_u2 is not None:
u1, u2 = u1_u2[0], u1_u2[1]
return np.cross(u1, u2)
@staticmethod
def vector_magnitude(v):
"""
compute magnitude of a vector
param v: vector with components of Cartesian form
return: magnitude of vector
"""
# NOTE: np.linalg.norm(v) computes Euclidean norm
mag = 0
for c in v:
mag += c**2
return mag**(1 / 2)
@staticmethod
def get_components(v):
"""
get components from vector, v
return [vx, vy, vz]: vector in x, y, z components
"""
# TODO: Extend for more dimensions
return v[0], v[1], v[2]
def insert_data(self, *args):
"""
insert results into database
param *args: parameters to save
"""
args = [float(arg) for arg in args]
self.db.insert(args)
def propogate(self):
"""
propogate system through time steps
"""
print('\npropagating state...', end=' ')
for ti in self.times:
self.evaluate_state(ti)
print('finished')
@staticmethod
def d2r(d):
"""
convert from degrees to radians using sympi pi module
param d: degrees
return: radians
"""
return d * (sp.pi.evalf() / 180)
@staticmethod
def r2d(rad):
"""
convert from radians to degrees
param rad: radians
return: degrees
"""
return rad * (180 / sp.pi.evalf())
def close(self):
"""
cleanup code. close connection to database
"""
self.db.close()
