def usage():
"""
Posts usage data to the user metrics table
"""
dbops = DBOps()
user_id = request.args.get('userId')
action = request.args.get('action')
dbops.post_user_action(user_id, action)
msg = '%s posted for user %s' % (action, user_id)
LOGGER.info(msg)
return jsonify({'message': msg})
def delete_activity():
"""
Deletes an activity from the database
"""
user_id = request.args.get('userId')
id = int(request.args.get('id'))
dbops = DBOps()
dbops.delete_activity(user_id=user_id, id=id)
msg = 'Activity %s deleted for user %s' % (id, user_id)
return jsonify({'action': msg})
def get_activities():
"""
Retrieves activities from the database for the specified user
"""
user_id = request.args.get('userId')
dbops = DBOps()
activities = dbops.get_activities(user_id)
for activity in activities:
activity['completed'] = str(activity['completed'])
activity['id'] = str(activity['id'])
return jsonify(activities)
def initialize_tables(drop_existing):
"""
Initializes the tables in mysql
"""
# Build the tables in my sql
dbops = DBOps()
dbops.initialize_tables(drop_existing=drop_existing)
# Read in the example json datafile
path = os.path.dirname(os.path.realpath(__file__))
filename = path + '/data/example_activities.json'
with open(filename, 'r') as f:
activities = json.load(f)
# Load the demo activities into mysql
for activity in activities:
dbops.upsert_activity(activity)
# Read in the default start/finish
filename = path + '/data/example_endpoints.json'
with open(filename, 'r') as f:
endpoints = json.load(f)
# Load the examples endpoints into mysql
for endpoint in endpoints:
dbops.upsert_endpoint(endpoint)
def get_schedule():
"""
Retrieves a saved schedule from the database
"""
dbops = DBOps()
user_id = request.args.get('userId')
schedule = dbops.get_schedule(user_id)
if not schedule:
schedule = {}
else:
for key in schedule:
if key not in ['userId', 'insert_timestamp']:
schedule[key] = json.loads(schedule[key])
return jsonify(schedule)
def update_endpoint():
"""
Updates the start or endpoint in the database
"""
# Check to see if there is a JSON in the request
if not request.json:
error = {'error': 'JSON not found'}
return make_response(jsonify(error), 400)
location = request.json
dbops = DBOps()
dbops.upsert_endpoint(location)
msg = '%s updated for user %s' % (location['endpoint'], location['userId'])
return jsonify({'action': msg})
def update_schedule():
"""
Posts an updated schedule to the database
"""
# Check to see if there is a JSON in the request
if not request.json:
error = {'error': 'JSON not found'}
return make_response(jsonify(error), 400)
schedule = request.json
dbops = DBOps()
dbops.upsert_schedule(schedule)
msg = 'Schedule updated for user %s' % (schedule['userId'])
return jsonify({'action': msg})
def get_endpoint():
"""
Retreives start and end locations for a user
"""
dbops = DBOps()
user_id = request.args.get('userId')
endpoint = request.args.get('endpoint')
location = dbops.get_endpoint(user_id, endpoint)
# Use the default location if a different location
# hasn't been saved for the user
if not location:
location = dbops.get_endpoint('default', endpoint)
return jsonify(location)
def test_usage():
dbops = DBOps()
dbops.post_user_action('test123', 'test')
df = dbops.get_user_action('test')
assert len(df) > 0
dbops.clear_user_action('test')
df = dbops.get_user_action('test')
assert len(df) == 0
def test_insert_csv():
dbops = DBOps()
sql = "TRUNCATE cbc_schedule.test"
with dbops.connection.cursor() as cursor:
cursor.execute(sql)
dbops.connection.commit()
path = os.path.dirname(os.path.realpath(__file__))
filename = path + '/../cbc_api/data/insert_test.csv'
dbops.insert_csv('test', filename, truncate=True)
sql = "SELECT * FROM cbc_schedule.test"
df = pd.read_sql(sql, dbops.connection)
assert len(df) == 2
sql = "TRUNCATE cbc_schedule.test"
with dbops.connection.cursor() as cursor:
cursor.execute(sql)
dbops.connection.commit()
def update_activity():
"""
Upserts and activity into the database
"""
# Check to see if there is a JSON in the request
if not request.json:
error = {'error': 'JSON not found'}
return make_response(jsonify(error), 400)
activity = request.json
activity['completed'] = bool(activity['completed'])
activity['id'] = int(activity['id'])
dbops = DBOps()
dbops.upsert_activity(activity)
msg = 'Activity %s added for user %s' % (activity['id'],
activity['userId'])
return jsonify({'action': msg})
def test_cleanup_table():
dbops = DBOps()
sql = "TRUNCATE cbc_schedule.test"
with dbops.connection.cursor() as cursor:
cursor.execute(sql)
dbops.connection.commit()
now = datetime.datetime.utcnow()
past = datetime.datetime.now() - datetime.timedelta(days=31)
sql1 = """
INSERT INTO cbc_schedule.test
(name, insert_timestamp)
VALUES
(%s, %s)
"""
sql2 = """
INSERT INTO cbc_schedule.test
(name, insert_timestamp)
VALUES
(%s, %s)
"""
with dbops.connection.cursor() as cursor:
cursor.execute(sql1, ('test1', now))
cursor.execute(sql2, ('test2', past))
dbops.connection.commit()
sql = "SELECT * FROM cbc_schedule.test"
df = pd.read_sql(sql, dbops.connection)
assert len(df) == 2
dbops.cleanup_table('test', 30)
sql = "SELECT * FROM cbc_schedule.test"
df = pd.read_sql(sql, dbops.connection)
assert len(df) == 1
sql = "TRUNCATE cbc_schedule.test"
with dbops.connection.cursor() as cursor:
cursor.execute(sql)
dbops.connection.commit()
def test_schedule_db():
dbops = DBOps()
path = os.path.dirname(os.path.realpath(__file__))
filename = path + '/../cbc_api/data/example_schedule.json'
with open(filename, 'r') as f:
test_schedule = json.load(f)
dbops.upsert_schedule(test_schedule)
schedule = dbops.get_schedule('testid')
for key in schedule:
if key != 'insert_timestamp':
if type(schedule[key]) == dict:
for key_ in schedule[key][key_]:
if key_ != 'insert_timestamp':
assert schedule[key][key_] == test_schedule[key][key_]
dbops.delete_schedule('testid')
schedule = dbops.get_schedule('testid')
assert schedule == None
def test_endpoint_db():
dbops = DBOps()
test_start = {
'userId': 'testid',
'endpoint': 'start',
'address': 'testaddress',
'city': 'testcity',
'state': 'teststate',
'zipCode': 'testzip',
'coordinates': [1, 2]
}
dbops.upsert_endpoint(test_start)
start = dbops.get_endpoint('testid', 'start')
for key in start:
if key != 'insert_timestamp':
assert start[key] == test_start[key]
dbops.delete_endpoint('testid', 'start')
activity = dbops.get_endpoint('testid', 'start')
assert activity == None
def insert_csv(filename, table, truncate=False):
""" Inserts values from a csv file """
dbops = DBOps()
dbops.insert_csv(filename=filename, table=table, truncate=truncate)
def test_activity_db():
dbops = DBOps()
test_activity = {
'userId': 'testid',
'id': 1,
'caseName': 'testcase',
'activityType': 'testactivity',
'expectedDuration': 30,
'address': 'testaddress',
'city': 'testcity',
'state': 'teststate',
'zipCode': 'testzip',
'coordinates': [1, 2],
'completed': False
}
dbops.upsert_activity(test_activity)
activity = dbops.get_activity('testid', 1)
for key in activity:
if key != 'insert_timestamp':
assert activity[key] == test_activity[key]
test_activity2 = {
'userId': 'testid',
'id': 2,
'caseName': 'testcase',
'activityType': 'testactivity',
'expectedDuration': 30,
'address': 'testaddress',
'city': 'testcity',
'state': 'teststate',
'zipCode': 'testzip',
'coordinates': [1, 2],
'completed': False
}
dbops.upsert_activity(test_activity2)
activity = dbops.get_activity('testid', 2)
for key in activity:
if key != 'insert_timestamp':
assert activity[key] == test_activity2[key]
activities = dbops.get_activities('testid')
assert len(activities) == 2
dbops.delete_activity('testid', 1)
activity = dbops.get_activity('testid', 1)
assert activity == None
dbops.delete_activity('testid', 2)
activity = dbops.get_activity('testid', 2)
assert activity == None
def cleanup_table(table, days):
""" Cleans up old data in the specified table """
dbops = DBOps()
dbops.cleanup_table(table=table, days=days)