def new_mood_report(user_id: int, mood: str) -> None:
"""Saves a new mood report, if a mood report has already been
submitted today it raises MoodAlreadySubmittedException"""
conn = get_connection()
cur = conn.cursor()
current_date = datetime.now().date()
# check to see if the user has already submitted a mood today.
cur.execute(
"SELECT COUNT(*) FROM mood_report WHERE date = ? AND user_id = ?",
(current_date, user_id))
if not cur.fetchone()[0] >= 1:
# deduplication of mood values, unique index prevents duplicates
cur.execute("INSERT OR IGNORE INTO mood_value (value) VALUES (?)",
(mood, ))
# getting the id of the newly or previously inserted value as RETURNING is not supported.
cur.execute("SELECT id FROM mood_value WHERE value = ?", (mood, ))
mood_value_id = cur.fetchone()[0]
cur.execute(
"INSERT INTO mood_report (mood_value_id, user_id, date) VALUES (?,?,?)",
(mood_value_id, user_id, current_date))
cur.execute("UPDATE users SET last_submission = ? WHERE int_id = ?",
(current_date, user_id))
conn.commit()
conn.close()
else:
conn.close()
raise MoodAlreadySubmittedException()
def get_user_streak_length(user_int_id: int) -> int:
"""Calculates the current users streak in days"""
conn = get_connection()
cur = conn.cursor()
cur.execute(
"SELECT date FROM mood_report where user_id = ? ORDER BY date desc",
(user_int_id, ))
dates = cur.fetchall()
streak_days = 0
x = 0
for date in dates:
if date[0] == str(datetime.now().date() - timedelta(days=x)):
streak_days += 1
else:
break
x += 1
cur.execute("UPDATE users SET streak_days = ? WHERE int_id = ?",
(streak_days, user_int_id))
conn.commit()
conn.close()
return streak_days
def get_mood_reports_by_user(user_int_id: int) -> list:
"""This returns a list of MoodReports for a single user"""
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM mood_report WHERE user_id = ?", (user_int_id, ))
mood_report_result = cur.fetchall()
mood_values = dict()
mood_reports = list()
for mood_report in mood_report_result:
if mood_report[1] not in mood_values:
cur.execute("SELECT value FROM mood_value WHERE id = ?",
(mood_report[1], ))
mood_values[mood_report[1]] = cur.fetchone()[0]
mood_value = mood_values[mood_report[1]]
else:
mood_value = mood_values[mood_report[1]]
mood_reports.append(
MoodReport(mood_report[0], mood_report[1], mood_report[2],
mood_report[3], mood_value))
return mood_reports
def create_session(user_int_id: int, token: str) -> Session:
"""Creates a session for a given user_int_id and returns it"""
conn = get_connection()
cur = conn.cursor()
cur.execute("INSERT INTO sessions (user_int_id, token) VALUES (?, ?)",
(user_int_id, token))
session = Session(user_int_id, cur.lastrowid, token)
conn.commit()
conn.close()
return session
def get_user_by_id(user_name: str) -> User:
"""Returns a user object from the database by id"""
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM users WHERE user_name = ?", (user_name, ))
result = cur.fetchone()
conn.close()
if result is not None:
return User(result[0], result[1], result[2], result[3], result[4])
def get_streak_eligible_user_totals() -> list:
"""This returns a list of tuples containing the current total streak_days
for each user currently on a streak"""
conn = get_connection()
cur = conn.cursor()
cur.execute(
"SELECT streak_days FROM users WHERE last_submission >= ? ORDER BY streak_days desc",
(datetime.now().date() - timedelta(days=1), ))
result = cur.fetchall()
conn.close()
return result
def save_percentile_data(percentile_data: dict) -> None:
"""This saves the precalculated percentile data to the database"""
conn = get_connection()
cur = conn.cursor()
cur.execute("DELETE FROM mood_percentiles")
for percentile in percentile_data:
cur.execute(
"INSERT INTO mood_percentiles (streak_days, percentile) VALUES (?,?)",
(percentile_data[percentile], percentile))
conn.commit()
conn.close()
def get_session_by_token(token: str) -> Session:
"""Returns a session objects for any given token, raises
SessionNotFoundException when the session does not exist"""
conn = get_connection()
cur = conn.cursor()
cur.execute("SELECT * FROM sessions WHERE token = ?", (token,))
result = cur.fetchone()
conn.close()
if result is None:
raise(SessionNotFoundException)
return Session(result[1], result[0], result[2])
def get_percentile_for_streak(streak: int) -> float:
"""This looks up the percentile values for each possible number
of streak days and returns the appropriate percentile value"""
conn = get_connection()
cur = conn.cursor()
cur.execute(
"SELECT percentile FROM mood_percentiles WHERE streak_days = ? ORDER BY percentile desc",
(streak, ))
result = cur.fetchone()
conn.close()
if result is not None:
return result[0]
else:
raise PercentileMatrixNotInitializedException
def create_new_user(user_name: str, password_hash: str) -> int:
"""Returns the int_id (primary key) of a newly created user"""
conn = get_connection()
cur = conn.cursor()
try:
cur.execute(
"INSERT INTO users (user_name, password_hash) VALUES (?,?)",
(user_name, password_hash))
except sqliteError:
conn.close()
raise UsernameAlreadyInUseException(
"The username selected is already in use")
conn.commit()
conn.close()
return cur.lastrowid
def update_mood_report_by_user(user_int_id: int, mood: str) -> None:
"""Updates a previously recorded mood in the database"""
conn = get_connection()
cur = conn.cursor()
cur.execute("INSERT OR IGNORE INTO mood_value (value) VALUES (?)",
(mood, ))
cur.execute("SELECT id FROM mood_value WHERE value = ?", (mood, ))
mood_value_id = cur.fetchone()[0]
try:
cur.execute(
"UPDATE mood_report SET mood_value_id = ? WHERE user_id = ? AND date = ?",
(mood_value_id, user_int_id, datetime.now().date()))
conn.commit()
conn.close()
except sqliteError:
conn.close()
raise NoPreviousMoodFoundException()
def historical_mood_report(user_id: int, mood: str, date: date,
streak: int) -> None:
"""Saves an historical mood report, for testing purposes does not do any sanity checks"""
conn = get_connection()
cur = conn.cursor()
cur.execute("INSERT OR IGNORE INTO mood_value (value) VALUES (?)",
(mood, ))
cur.execute("SELECT id FROM mood_value WHERE value = ?", (mood, ))
mood_value_id = cur.fetchone()[0]
cur.execute(
"INSERT INTO mood_report (mood_value_id, user_id, date) VALUES (?,?,?)",
(mood_value_id, user_id, date))
cur.execute(
"UPDATE users SET last_submission = ? AND streak_days = ? WHERE int_id = ?",
(date, streak, user_id))
conn.commit()
conn.close()