def getUserRecommendations(userid, interval, order, start, n):
'''Returns recommended articles for user with userid and total number of recomendations in the result set. Interval is the number of days before today which should be included in the result,
order should be one of "titleasc","titledesc","scoreasc" and "scoredesc" and decides the order the resulting articles are sorted,
start the position in the result set the returned result begins at, and n is the number of recomendations returned.'''
cur = getDb().cursor(dictionary=True)
orders = {
'titleasc': 'title ASC',
'titledesc': 'title DESC',
'scoreasc': 'score ASC',
'scoredesc': 'score DESC'
}
# IMPORTANT sanitizes order, against sql injection
order = orders.get(order.lower(), 'score DESC')
sql = '''SELECT SQL_CALC_FOUND_ROWS article_id,saved,title,abstract,explanation, GROUP_CONCAT(concat(firstname," ",lastname) SEPARATOR ", ") as authors
FROM article_feedback
NATURAL JOIN articles NATURAL JOIN article_authors
WHERE user_id = %s AND DATE(recommendation_date) >= DATE_SUB(UTC_DATE(), INTERVAL %s DAY)
group by article_id ORDER BY {} LIMIT %s,%s'''.format(order)
cur.execute(sql, (
userid,
interval,
start,
n,
))
articles = cur.fetchall()
cur.execute('SELECT FOUND_ROWS() as total', )
total = cur.fetchone()['total']
cur.close()
return articles, total
def saveArticle(articleId, userid, setTo):
'''Sets saved to setTo for given article and user. Returns true if successful save, false if unsuccessful'''
cur = getDb().cursor()
if setTo:
sql = 'UPDATE article_feedback SET saved=CURRENT_TIMESTAMP WHERE article_id = %s AND user_id = %s'
else:
sql = 'UPDATE article_feedback SET saved=null WHERE article_id = %s AND user_id = %s'
cur.execute(sql, (
articleId,
userid,
))
if cur.rowcount == 0:
return False
getDb().commit()
cur.close()
return True
def add_activate_trace(trace, email):
"""Connects the trace from the activation email to the user."""
conn = getDb()
cur = conn.cursor()
sql = '''update users set activate_trace = %s where email = %s'''
cur.execute(sql, (trace, email))
conn.commit()
cur.close()
def activate_user(trace):
"""Activates the user with the supplied trace."""
conn = getDb()
with closing(conn.cursor()) as cur:
sql = '''update users set inactive = 0 where activate_trace = %s'''
cur.execute(sql, (trace, ))
conn.commit()
return cur.rowcount == 1
def getAdmins():
'''Returns admin users id, email and names'''
cur = getDb().cursor(dictionary=True)
sql = 'select user_id, email, firstname, lastname from users where admin=1'
cur.execute(sql)
admindata = cur.fetchall()
cur.close()
return admindata
def get_article_feedback(article_id):
"""Checks if article has been shown to user."""
with closing(getDb().cursor(dictionary=True)) as cur:
cur.execute(
'''SELECT * FROM articles NATURAL JOIN article_feedback
WHERE article_id = %s AND user_id = %s''',
[article_id, g.user])
return cur.fetchone()
def article_is_recommended_for_user(article_id):
"""Checks if article has been shown to user."""
with closing(getDb().cursor()) as cur:
cur.execute(
'''SELECT EXISTS(SELECT article_id FROM article_feedback
WHERE user_id = %s AND article_id = %s)''',
(g.user, article_id))
return cur.fetchone()[0] == 1
def set_user_categories(user_id, user):
"""Helper function for setting user categories does not
commit."""
conn = getDb()
with closing(conn.cursor()) as cur:
cur.execute('DELETE FROM user_categories WHERE user_ID = %s',
[user_id])
data = [(user_id, category_id) for category_id in user.categories]
cur.executemany('INSERT INTO user_categories VALUES(%s, %s)', data)
def get_freetext_feedback(user_id):
"""Get freetext feedback from given user.
:param user_id: User to get feedback for.
:return: List of feedback instances.
"""
cur = getDb().cursor(dictionary=True)
sql = '''SELECT article_id, type, feedback_text, feedback_values
FROM feedback WHERE user_id = %s'''
cur.execute(sql, (user_id, ))
return cur.fetchall()
def getSystem(ID):
'''Returns requested system.'''
cur = getDb().cursor(dictionary=True)
cur.execute(
'''SELECT * FROM systems left join users
on users.user_id = systems.admin_user_id
where system_id = %s''', (ID, ))
data = cur.fetchone()
cur.close()
return data
def seenArticle(articles):
'''Sets seen_web to true for given articles and useres. Returns True on success.
<articles> should be a list of tuples: [(article,user),(article,user)].'''
conn = getDb()
cur = conn.cursor()
sql = 'UPDATE article_feedback SET seen_web=CURRENT_TIMESTAMP WHERE article_id=%s AND user_id = %s'
cur.executemany(sql, articles)
cur.close()
conn.commit()
return True
def digest_unsubscribe(trace):
"""Unsubscribes the user with the supplied trace from the
digest email and assigns a new unsubscribe trace to the user."""
conn = getDb()
with closing(conn.cursor()) as cur:
sql = '''update users set notification_interval = 0,
unsubscribe_trace = %s where unsubscribe_trace = %s'''
cur.execute(sql, (str(uuid4()), trace))
conn.commit()
return cur.rowcount == 1
def get_user_systems(user_id):
"""Gets systems belonging to a user."""
conn = getDb()
cur = conn.cursor(dictionary=True)
sql = '''select system_id, api_key, active, email, firstname, lastname,
organization, system_name from systems left join users on
users.user_id = systems.admin_user_id where users.user_id = %s'''
cur.execute(sql, (user_id, ))
systems = cur.fetchall()
cur.close()
return systems
def get_systems(user_id):
"""Gets systems belonging to a user.
:param user_id: User to get feedback for.
:return: List of system dictionaries.
"""
with closing(getDb().cursor(dictionary=True)) as cur:
sql = '''SELECT system_id, system_name, api_key, active
FROM systems WHERE admin_user_id = %s'''
cur.execute(sql, (user_id, ))
return cur.fetchall()
def userExist(email):
"""Checks if email is already in use by another user.
Returns True if in use and False if not."""
cur = getDb().cursor()
sql = 'SELECT user_id FROM users WHERE email = %s'
cur.execute(sql, (email, ))
row = cur.fetchone()
cur.close()
if not row:
return False
return False if row[0] == g.user else True
def getSystems():
"""Returns list of all recommending systems with null values if
the systems are not connected to a user."""
cur = getDb().cursor(dictionary=True)
cur.execute(
'''select system_id, api_key, active, email, firstname, lastname,
organization, system_name from systems left join users
on users.user_id = systems.admin_user_id;''')
systems = cur.fetchall()
cur.close()
return systems
def search_topics(search_string, max_results=50):
"""Searches the topics table for topics starting with `search_string`.
:param search_string: String topics should start with.
:param max_results: Number of results to return.
:return: List of topics.
"""
with closing(getDb().cursor()) as cur:
sql = '''SELECT topic FROM topics WHERE topic
LIKE CONCAT(LOWER(%s), '%') LIMIT %s'''
cur.execute(sql, (search_string, max_results))
return [x[0] for x in cur.fetchall()]
def saveArticleEmail(articleId, userid, trace):
'''Sets saved to true for given article,user and trace. Returns True on succes and False on failure.'''
conn = getDb()
cur = conn.cursor()
result = 0
sql = 'UPDATE article_feedback SET saved=CURRENT_TIMESTAMP WHERE article_id = %s AND user_id = %s AND trace_save_email = %s'
cur.execute(sql, (articleId, userid, trace))
result = cur.rowcount
cur.close()
conn.commit()
return True if result > 0 else False
def get_article_feedback_by_date(start_date, end_date, system=None):
"""Gets all article feedback between start and end date."""
cur = getDb().cursor(dictionary=True)
sql = '''SELECT user_id, system_id, DATE(recommendation_date) as date,
clicked_email, clicked_web, saved, seen_web, seen_email
FROM article_feedback WHERE DATE(recommendation_date) >= %s AND
DATE(recommendation_date) <= %s'''
if system:
sql += 'AND system_id = %s'
cur.execute(sql, (start_date, end_date, system))
else:
cur.execute(sql, (start_date, end_date))
return cur.fetchall()
def clickArticle(articleId, userid):
'''Sets clicked_web to true for given article and user. Returns True on success.'''
conn = getDb()
cur = conn.cursor()
sql = 'UPDATE article_feedback SET clicked_web=CURRENT_TIMESTAMP WHERE article_id = %s AND user_id = %s'
cur.execute(sql, (
articleId,
userid,
))
cur.close()
conn.commit()
return True
def get_topic_feedback_by_date(start_date, end_date, system=None):
"""Gets all topic feedback between start and end date."""
cur = getDb().cursor(dictionary=True)
sql = '''SELECT tr.user_id, tr.system_id, tr.interleaving_batch, ut.state
FROM topic_recommendations tr JOIN user_topics ut
ON tr.user_id = ut.user_id AND tr.topic_id = ut.topic_id
WHERE tr.interleaving_order IS NOT NULL AND
tr.interleaving_batch >= %s AND tr.interleaving_batch <= %s'''
if system:
sql += 'AND tr.system_id = %s'
cur.execute(sql, (start_date, end_date, system))
else:
cur.execute(sql, (start_date, end_date))
return cur.fetchall()
def update_user_topic(topic_id, user_id, state):
"""Sets interaction time, state and seen flag for the supplied topic
to the current datetime."""
conn = getDb()
with closing(conn.cursor(dictionary=True)) as cur:
user_topics_sql = '''insert into user_topics values (%s,%s,%s,%s)'''
topic_recommendations_sql = '''update topic_recommendations set clicked = %s
where user_id = %s and topic_id = %s and interleaving_order is not null'''
current_time = datetime.utcnow()
cur.execute(user_topics_sql, (user_id, topic_id, state, current_time))
cur.execute(topic_recommendations_sql,
(current_time, user_id, topic_id))
conn.commit()
return cur.rowcount == 1
def validatePassword(email, password):
"""Checks if users password is correct. Returns userid if correct password,
none if user does not exists and false if incorrect password"""
cur = getDb().cursor()
sql = 'SELECT user_id,salted_Hash FROM users WHERE email = %s'
cur.execute(sql, (email, ))
user = cur.fetchone()
cur.close()
if not user:
return None
if pbkdf2_sha256.verify(password.encode('utf-8'), user[1].encode('utf-8')):
return user[0]
return False
def updatePassword(id, password):
"""Hash and update password to user with id. Returns True on success\""""
conn = getDb()
cur = conn.cursor()
passwordsql = 'UPDATE users SET salted_hash = %s WHERE user_id = %s'
password = password.encode('utf-8')
hashedPassword = pbkdf2_sha256.hash(password)
cur.execute(passwordsql, (
hashedPassword,
id,
))
cur.close()
conn.commit()
return True
def set_user_topics(user_id, user):
"""Helper function for setting user topics, does not
commit."""
conn = getDb()
with closing(conn.cursor()) as cur:
cur.executemany('INSERT IGNORE INTO topics(topic) VALUE(%s)',
[(t, ) for t in user.topics])
placeholders = ','.join(['%s'] * len(user.topics))
select_topics = '''SELECT topic_id FROM topics where topic
in ({})'''.format(placeholders)
cur.execute(select_topics, user.topics)
topic_ids = cur.fetchall()
current_time = datetime.utcnow()
topic_ids = [t[0] for t in topic_ids]
topic_update_sql = '''insert ignore into user_topics(user_id, topic_id, state,
interaction_time) values(%s, %s, 'USER_ADDED', %s)'''
cur.executemany(topic_update_sql,
[(user_id, t, current_time) for t in topic_ids])
topic_update_sql = '''update user_topics set state = 'USER_REJECTED',
interaction_time = %s where
user_id = %s and state = 'USER_ADDED' and topic_id
not in ({})'''.format(placeholders)
cur.execute(topic_update_sql, [current_time, user_id, *topic_ids])
topic_update_sql = '''update user_topics set state = 'SYSTEM_RECOMMENDED_REJECTED',
interaction_time =%s where
user_id = %s and state = 'SYSTEM_RECOMMENDED_ACCEPTED' and topic_id
not in ({})'''.format(placeholders)
cur.execute(topic_update_sql, [current_time, user_id, *topic_ids])
topic_update_sql = '''update user_topics set state = 'USER_ADDED',
interaction_time = %s where
user_id = %s and state in ('SYSTEM_RECOMMENDED_REJECTED', 'EXPIRED',
'REFRESHED') and topic_id in ({})'''.format(
placeholders)
cur.execute(topic_update_sql, [current_time, user_id, *topic_ids])
topic_update_sql = '''update user_topics set state = 'USER_ADDED',
interaction_time = %s where
user_id = %s and state = 'USER_REJECTED' and topic_id
in ({})'''.format(placeholders)
cur.execute(topic_update_sql, [current_time, user_id, *topic_ids])
def insertSystem(system_name, user_id):
"""Inserts a new system into the database, name will be used as Name for the system,
and using uuid a random API-key is generated. Returns None, key if successfull and an error, None if not."""
conn = getDb()
cur = conn.cursor()
sql = 'INSERT INTO systems VALUES(null, %s, %s, False, %s)'
key = str(uuid4())
try:
cur.execute(sql, (key, system_name, user_id))
except connector.errors.IntegrityError as e:
col = str(e).split("key ", 1)[1]
if col == "'system_name'":
return "System name already in use by another system.", None
else:
return "Error, can not connect to server. Try again later", None
conn.commit()
return None, key
def update_user(user_id, user):
"""Update user with user_id. User object contains new info for this user."""
conn = getDb()
with closing(conn.cursor()) as cur:
sql = '''UPDATE users SET email = %s, firstname = %s, lastname = %s,
organization = %s, personal_website = %s, dblp_profile = %s,
google_scholar_profile = %s, semantic_scholar_profile = %s,
notification_interval = %s
WHERE user_id = %s'''
cur.execute(
sql, (user.email, user.firstname, user.lastname, user.organization,
user.personal_website, user.dblp_profile,
user.google_scholar_profile, user.semantic_scholar_profile,
user.notification_interval, user_id))
set_user_categories(user_id, user)
set_user_topics(user_id, user)
conn.commit()
def get_topic_recommendations(user_id):
"""Get topic recommendations for given user. Includes user interaction data
if the recommendation has been shown to the user.
:param user_id: User to get feedback for.
:return: List of system recommendation instances.
"""
cur = getDb().cursor(dictionary=True)
sql = '''SELECT topic, system_name, datestamp, system_score,
interleaving_order, seen, clicked, state, interaction_time
FROM topic_recommendations tr
NATURAL JOIN topics t
NATURAL LEFT JOIN user_topics ut
NATURAL LEFT JOIN systems s
WHERE user_id = %s
ORDER BY datestamp desc, system_name desc, system_score desc;'''
cur.execute(sql, (user_id, ))
return cur.fetchall()
def get_user(user_id):
"""Gets userdata.
:param user_id: Id of user to get.
:return: User data as dictionary.
"""
cur = getDb().cursor(dictionary=True)
sql = '''SELECT user_id, email, firstname, lastname, organization,
notification_interval, registered, last_email_date,
last_recommendation_date, dblp_profile, google_scholar_profile,
semantic_scholar_profile, personal_website, show_semantic_scholar_popup
FROM users WHERE user_id = %s'''
cur.execute(sql, (user_id, ))
user = cur.fetchone()
if not user:
return None
# Add categories to user
sql = '''SELECT u.category_id,c.category_name FROM user_categories u
NATURAL JOIN categories c WHERE u.user_id = %s'''
cur.execute(sql, (user_id, ))
user['categories'] = sorted(cur.fetchall(),
key=lambda x: x['category_name'])
# Add topics to user
sql = '''SELECT t.topic_id, t.topic FROM user_topics ut
NATURAL JOIN topics t WHERE user_id = %s AND NOT t.filtered
and ut.state in ('USER_ADDED','SYSTEM_RECOMMENDED_ACCEPTED')'''
cur.execute(sql, (user_id, ))
user['topics'] = sorted(cur.fetchall(), key=lambda x: x['topic'])
# Add Semantic Scholar profile suggestions to user
sql = '''SELECT semantic_scholar_id, name, score
FROM semantic_scholar_suggestions s
WHERE user_id = %s AND created = (
SELECT max(created) FROM semantic_scholar_suggestions WHERE user_id = s.user_id
)
ORDER BY score'''
cur.execute(sql, (user_id, ))
user['semantic_scholar_suggestions'] = cur.fetchall()
cur.close()
return user
def get_user_topics(user_id):
"""Returns list of top nr recommended topics for a user and marks
these topics as seen."""
conn = getDb()
with closing(conn.cursor(dictionary=True)) as cur:
sql = '''SELECT tr.topic_id, t.topic
FROM topic_recommendations tr INNER JOIN topics t
ON t.topic_id = tr.topic_id
LEFT JOIN user_topics ut
ON ut.topic_id = tr.topic_id AND tr.user_id = ut.user_id
WHERE tr.user_id = %s
AND ut.state IS NULL
AND tr.interleaving_batch = (
SELECT max(interleaving_batch)
FROM topic_recommendations
WHERE user_id = %s
AND interleaving_batch >
DATE_SUB(%s, INTERVAL 24 HOUR))
ORDER BY tr.interleaving_order DESC'''
cur.execute(sql, (user_id, user_id, datetime.utcnow()))
topics = cur.fetchall()
if not topics:
# Marks any previous suggested topics as expired if any.
# Then runs topic interleaver for new topics
clear_suggested_user_topics(user_id, 'EXPIRED')
multileave_topics.run(user_id)
cur.execute(sql, (user_id, user_id, datetime.utcnow()))
topics = cur.fetchall()
seen_sql = '''update topic_recommendations set
seen = %s where topic_id = %s
and user_id = %s and interleaving_batch is
not NULL and seen is NULL'''
current_time = datetime.utcnow()
data = [(current_time, topic['topic_id'], user_id) for topic in topics]
cur.executemany(seen_sql, data)
conn.commit()
return topics
