def getUsersByRating(interval): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT u.elo - MOD(u.elo, %s) AS elo_category, COUNT(u.id) FROM users AS u GROUP BY elo_category;""", (interval, )) data = connection.cursor.fetchall() distribution = [[elo_category, count] for (elo_category, count) in data] return { 'status': 'ok', 'data': { 'users-rating-distribution': distribution } }
def isBlunderTaskExist(user_id, blunder_id, type): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT bt.id FROM blunder_tasks as bt WHERE bt.user_id = %s AND bt.blunder_id = %s AND bt.type_id = ( SELECT btt.id FROM blunder_task_type AS btt WHERE btt.name = %s ) """, (user_id, blunder_id, type)) if connection.cursor.rowcount != 0: return True return False
def getBlundersByRating(interval): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT (b.elo - b.elo %% %s) AS elo_category, COUNT(b.id) as count FROM blunders AS b GROUP BY elo_category ORDER BY elo_category;""", (interval, )) data = connection.cursor.fetchall() distribution = [[elo_category, count] for (elo_category, count) in data] return { 'status': 'ok', 'data': { 'blunders-rating-distribution': distribution } }
def getTaskStartDate(user_id, blunder_id, type): if user_id is None: return #TODO: value or exception? with core.PostgreConnection('r') as connection: connection.cursor.execute( """SELECT assign_date FROM blunder_tasks AS bt INNER JOIN blunder_task_type AS btt ON bt.type_id = btt.id WHERE bt.user_id = %s AND bt.blunder_id = %s AND btt.name = %s;""", (user_id, blunder_id, type)) if connection.cursor.rowcount != 1: return None (assign_date, ) = connection.cursor.fetchone() return assign_date
def assignPack(user_id, pack_id): with core.PostgreConnection('w') as connection: connection.cursor.execute(""" INSERT INTO pack_users(pack_id, user_id) VALUES (%s, %s) """, (pack_id, user_id) ) if connection.cursor.rowcount != 1: raise Exception('Failed to assign pack to user') # When working in pack mode, this is ok to have same blunders in different packs # and user can assign them both. We need to check if blunder already assigned # This will fail and not right to do. Duplicated blunder will be added only once. # When user will try to solve second duplication blunder, it will get validation error # This is ok because it is very rare situation #TODO: In PostgreSQL 9.5 INSERT ... ON CONFLICT DO NOTHING added, rewrite after update? blunder_ids = getPackBlundersByIdAll(pack_id) for blunder_id in blunder_ids: if blunder.isBlunderTaskExist(user_id, blunder_id, const.tasks.PACK): continue blunder.assignBlunderTask(user_id, blunder_id, const.tasks.PACK)
def signupUser(username, salt, hash, email): with core.PostgreConnection('w') as connection: try: connection.cursor.execute( """ INSERT INTO users (username, salt, password, role, email, registration, last_login) VALUES (%s, %s, %s, %s, %s, NOW(), NOW()); """, (username, salt, hash, const.roles.USER, email)) except IntegrityError: return { 'status': 'error', 'field': 'username', 'message': 'Already registered' } success = (connection.cursor.rowcount == 1) if not success: return {'status': 'error', 'message': "Unable to register user"} return {'status': 'ok'}
def getAssignedBlunder(user_id, type): if user_id is None: return None with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT bt.blunder_id FROM blunder_tasks AS bt INNER JOIN blunder_task_type AS btt ON bt.type_id = btt.id WHERE bt.user_id = %s AND btt.name = %s; """, (user_id, type)) if connection.cursor.rowcount != 1: return None (blunder_id, ) = connection.cursor.fetchone() return getBlunderById(blunder_id)
def getRandomBlunder(): with core.PostgreConnection('r') as connection: connection.cursor.execute(""" SELECT * FROM GET_RANDOM_BLUNDER(); """) if connection.cursor.rowcount != 1: raise Exception('Fail to get random blunder') (id, forced_line, elo, fen_before, blunder_move, move_index, game_id) = connection.cursor.fetchone() return { 'id': id, 'forced_line': forced_line, 'elo': elo, 'fen_before': fen_before, 'blunder_move': blunder_move, 'move_index': move_index, 'game_id': game_id }
def getRatingByDate(username, interval): user_id = getUserId(username) if interval == 'all': query = """ SELECT TO_CHAR(b.date_finish, 'YYYY/MM/DD 12:00') AS date, AVG(b.user_elo) FROM blunder_history AS b GROUP BY date, b.user_id HAVING b.user_id = %s ORDER BY date ASC;""" elif interval == 'last-month': # TODO: NOW() - INTERVAL 1 MONTH -> use date_trunct to set to midnight? query = """ SELECT TO_CHAR(b.date_finish, 'YYYY/MM/DD 12:00') AS date, AVG(b.user_elo) FROM blunder_history AS b WHERE b.date_finish > NOW() - INTERVAL '1 MONTH' GROUP BY date, b.user_id HAVING b.user_id = %s ORDER BY date ASC;""" else: return { 'status': 'error', 'message': 'Error value for interval parameter: %s' % interval } with core.PostgreConnection('r') as connection: connection.cursor.execute(query, (user_id, )) data = connection.cursor.fetchall() rating = [[date, int(elo)] for (date, elo) in data] return { 'status': 'ok', 'username': username, 'data': { 'rating-statistic': rating } }
def removePack(user_id, pack_id, success): ##TODO: optimize? ##delete from blunder_tasks as bt using pack_blunders as pb where bt.blunder_id = pb.blunder_id ##and pb.pack_id = 74 and bt.user_id = 282 and type_id = 3; blunder_ids = getAssignedBlunders(user_id, pack_id) if blunder_ids is None: return assign_date = getPackAssignDate(user_id, pack_id) for blunder_id in blunder_ids: blunder.closeBlunderTask(user_id, blunder_id, const.tasks.PACK) with core.PostgreConnection('w') as connection: connection.cursor.execute(""" DELETE FROM pack_users as pu WHERE pu.user_id = %s AND pu.pack_id = %s """, (user_id, pack_id) ) savePackHistory(user_id, pack_id, assign_date, success)
def saveBlunderHistory(user_id, user_elo, blunder_id, blunder_elo, success, userLine, date_start, spent_time): if user_id is None: raise Exception('postre.saveBlunderHistory for anonim') if date_start is None: raise Exception('postre.saveBlunderHistory date start is not defined') result = 1 if success else 0 if success: userLine = [] with core.PostgreConnection('w') as connection: connection.cursor.execute( """ INSERT INTO blunder_history (user_id, blunder_id, result, user_elo, blunder_elo, user_line, date_start, spent_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s); """, (user_id, blunder_id, result, user_elo, blunder_elo, userLine, date_start, spent_time)) if connection.cursor.rowcount != 1: raise Exception('Failed to write into blunder history table')
def saveAnalyze(user_id, blunder_id, user_line, user_move, engine_line, engine_score, time_ms): with core.PostgreConnection('w') as connection: connection.cursor.execute( """ INSERT INTO blunder_analyze( created_by, blunder_id, user_line, user_move, engine_line, engine_score, time_ms ) VALUES(%s, %s, %s, %s, %s, %s, %s) """, (user_id, blunder_id, user_line, user_move, engine_line, dumps(engine_score), time_ms)) if connection.cursor.rowcount == 1: return True return False
def getUnlockedPacks(user_id, packs): if len(packs) >= 4: # Limit packs user can have return [] with core.PostgreConnection('r') as connection: connection.cursor.execute(""" SELECT id, name, caption, body FROM pack_type as pt ORDER BY priority DESC """ ) pack_types = connection.cursor.fetchall() result = [] for (id, name, caption, body) in pack_types: if name == const.pack_type.RANDOM: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.MATEINN: result.extend(getUnlockedMateInN(name, caption, body)) elif name == const.pack_type.GRANDMASTERS: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.OPENING: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.ENDGAME: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.PROMOTION: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.CLOSEDGAME: result.extend(getUnlockedAsIs(name, caption, body)) elif name == const.pack_type.DIFFICULTYLEVELS: result.extend(getDifficultyLevels(user_id, name, caption, body)) elif name == const.pack_type.REPLAYFAILED: result.extend(getReplayFailed(user_id, name, caption, body)) #else: # raise Exception('') return result
def getBlundersFavoritesIds(user_id, offset, limit): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT f.blunder_id, f.assign_date FROM blunder_favorites AS f WHERE f.user_id = %s ORDER BY f.assign_date DESC LIMIT %s OFFSET %s""", ( user_id, limit, offset, )) data = connection.cursor.fetchall() blunders = [{ "blunder_id": blunder_id, "assign_date": assign_date } for (blunder_id, assign_date) in data] return blunders
def getPackTypeByName(pack_type_name): with core.PostgreConnection('r') as connection: connection.cursor.execute(""" SELECT id, name, priority, caption, body, use_cache FROM pack_type as pt WHERE pt.name = %s """, (pack_type_name,) ) if connection.cursor.rowcount != 1: raise Exception('Wrong pack type name') (id, name, priority, caption, body, use_cache) = connection.cursor.fetchone() return { 'id': id, 'name': name, 'priority': priority, 'caption': caption, 'body': body, 'use_cache': use_cache }
def getBlunderForReplayFailed(user_id, count): forgot_interval = '1 week' with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT grouped.blunder_id FROM (SELECT f.blunder_id, sum(f.result) AS success_tries, count(1) AS total_tries, max(f.date_finish) AS date_last FROM (SELECT * FROM (SELECT bh.blunder_id, bh.result, bh.date_finish FROM blunder_history AS bh WHERE bh.user_id = %s) AS history LEFT JOIN (SELECT pb.blunder_id FROM pack_users AS pu INNER JOIN pack_blunders AS pb ON pu.pack_id = pb.pack_id AND pu.user_id = %s) AS CURRENT USING(blunder_id) WHERE current.blunder_id IS NULL) AS f GROUP BY f.blunder_id) AS grouped WHERE grouped.success_tries = 0 AND grouped.date_last < now() - interval %s ORDER BY grouped.date_last LIMIT %s;""", (user_id, user_id, forgot_interval, count)) result = [ blunder_id for (blunder_id, ) in connection.cursor.fetchall() ] return result
def getUsersTopByActivity(interval, number): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT u.username, COUNT(bh.result) AS totalTries, SUM(bh.result) AS successTries FROM users AS u INNER JOIN blunder_history AS bh ON u.id = bh.user_id WHERE bh.date_finish > NOW() - INTERVAL %s GROUP BY u.username ORDER BY totalTries DESC LIMIT %s;""", (interval, number)) data = connection.cursor.fetchall() top = [{ 'username': username, 'totalTries': totalTries, 'successTries': successTries } for (username, totalTries, successTries) in data] return top
def getBlunderById(blunder_id): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT b.id, b.forced_line, b.elo, b.fen_before, b.blunder_move, b.move_index, b.game_id FROM blunders AS b WHERE b.id = %s """, (blunder_id, )) if connection.cursor.rowcount != 1: return None (id, forced_line, elo, fen_before, blunder_move, move_index, game_id) = connection.cursor.fetchone() return { 'id': id, 'forced_line': forced_line, 'elo': elo, 'fen_before': fen_before, 'blunder_move': blunder_move, 'move_index': move_index, 'game_id': game_id }
def getPackBlundersByIdAssignedOnly(user_id, pack_id): with core.PostgreConnection('r') as connection: connection.cursor.execute(""" SELECT pb.blunder_id FROM pack_blunders AS pb INNER JOIN blunder_tasks AS bt USING(blunder_id) WHERE pb.pack_id = %s AND bt.user_id = %s AND bt.type_id = ( SELECT bty.id FROM blunder_task_type AS bty WHERE bty.name = %s ) ORDER BY pb.blunder_id ; """, (pack_id, user_id, const.tasks.PACK) ) pack_blunders = [ blunder_id for (blunder_id,) in connection.cursor.fetchall() ] return pack_blunders
def getUserProfile(username): with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT u.id, u.username, u.elo FROM users AS u WHERE u.username = %s;""", (username, )) if connection.cursor.rowcount != 1: return { 'status': 'error', 'message': 'Trying to get not exist user with name %s' % username } (user_id, username, user_elo) = connection.cursor.fetchone() with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT SUM(bcv.vote) FROM blunder_comments as bc INNER JOIN blunder_comments_votes as bcv ON bc.id = bcv.comment_id WHERE bc.user_id = %s;""", (user_id, )) (commentLikeSum, ) = connection.cursor.fetchone() if commentLikeSum is None: commentLikeSum = 0 with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT COUNT(id) FROM blunder_comments_votes AS bvc WHERE bvc.user_id = %s;""", (user_id, )) (commentVoteSum, ) = connection.cursor.fetchone() if commentVoteSum is None: commentVoteSum = 0 with core.PostgreConnection('r') as connection: connection.cursor.execute( """ SELECT COUNT(id) FROM blunder_votes AS bv WHERE bv.user_id = %s;""", (user_id, )) (voteSum, ) = connection.cursor.fetchone() if voteSum is None: voteSum = 0 favoriteCount = getBlunderFavoritesCount(user_id) commentCount = getCommentsByUserCount(user_id) karma = 10 + commentLikeSum * 5 + commentCount * 2 + voteSum + favoriteCount + commentVoteSum userJoinDate = getUserField(user_id, "to_char(registration, 'Month DD, YYYY')") userLastActivity = lastUserActivity(username, 'Month DD, YYYY') return { 'status': 'ok', 'data': { 'user-rating-value': user_elo, 'user-karma-value': karma, 'username-value': username, 'user-join-value': userJoinDate, 'user-last-activity-value': userLastActivity } }
def getUserField(user_id, field): with core.PostgreConnection('r') as connection: connection.cursor.execute( 'SELECT ' + field + ' from users WHERE id = %s;', (user_id, )) return connection.cursor.fetchone()[0]