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