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]