def leaderboard(info=None):
"""Create or update leaderboard materialized view."""
materialized_view = 'users_rank'
materialized_view_idx = 'users_rank_idx'
if info:
materialized_view = 'users_rank_%s' % info
materialized_view_idx = 'users_rank_%s_idx' % info
if exists_materialized_view(db, materialized_view):
return refresh_materialized_view(db, materialized_view)
else:
sql = '''
CREATE MATERIALIZED VIEW {} AS WITH scores AS (
SELECT "user".*, COUNT(task_run.user_id) AS score
FROM "user" LEFT JOIN task_run
ON task_run.user_id="user".id GROUP BY "user".id
) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
'''.format(materialized_view)
if info:
sql = '''
CREATE MATERIALIZED VIEW {} AS WITH scores AS (
SELECT "user".*, COALESCE(CAST("user".info->>'{}' AS INTEGER), 0) AS score
FROM "user" ORDER BY score DESC) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
'''.format(materialized_view, info)
db.session.execute(sql)
db.session.commit()
sql = '''
CREATE UNIQUE INDEX {}
on {}(id, rank);
'''.format(materialized_view_idx, materialized_view)
db.session.execute(sql)
db.session.commit()
return "Materialized view created"
def rank_and_score(user_id):
"""Return rank and score for a user."""
if exists_materialized_view(db, 'users_rank') is False:
lb()
sql = text('''SELECT * from users_rank WHERE id=:user_id''')
results = session.execute(sql, dict(user_id=user_id))
rank_and_score = dict(rank=None, score=None)
for row in results:
rank_and_score['rank'] = row.rank
rank_and_score['score'] = row.score
return rank_and_score
def rank_and_score(user_id):
"""Return rank and score for a user."""
if exists_materialized_view(db, 'users_rank') is False:
lb()
sql = text('''SELECT * from users_rank WHERE id=:user_id''')
results = session.execute(sql, dict(user_id=user_id))
rank_and_score = dict(rank=None, score=None)
for row in results:
rank_and_score['rank'] = row.rank
rank_and_score['score'] = row.score
return rank_and_score
def leaderboard():
"""Create or update leaderboard materialized view."""
if exists_materialized_view(db, 'users_rank'):
return refresh_materialized_view(db, 'users_rank')
else:
sql = text('''
CREATE MATERIALIZED VIEW users_rank AS WITH scores AS (
SELECT "user".*, COUNT(task_run.user_id) AS score
FROM "user" LEFT JOIN task_run
ON task_run.user_id="user".id GROUP BY "user".id
) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
''')
db.session.execute(sql)
db.session.commit()
return "Materialized view created"
def leaderboard(info=None):
"""Create or update leaderboard materialized view."""
materialized_view = 'users_rank'
materialized_view_idx = 'users_rank_idx'
if info:
materialized_view = 'users_rank_%s' % info
materialized_view_idx = 'users_rank_%s_idx' % info
if exists_materialized_view(db, materialized_view):
return refresh_materialized_view(db, materialized_view)
else:
sql = '''
CREATE MATERIALIZED VIEW "{}" AS WITH scores AS (
SELECT "user".*, COUNT(task_run.user_id) AS score
FROM "user" LEFT JOIN task_run
ON task_run.user_id="user".id where
"user".restrict=false GROUP BY "user".id
) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
'''.format(materialized_view)
if info:
# sql = '''
# CREATE MATERIALIZED VIEW "{}" AS WITH scores AS (
# SELECT "user".*, COALESCE(CAST("user".info->>'{}' AS INTEGER), 0) AS score
# FROM "user" where "user".restrict=false ORDER BY score DESC) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
# '''.format(materialized_view, info)
terms = info.split('_')
sql = '''
CREATE MATERIALIZED VIEW "{0}" AS WITH scores AS (
SELECT "user".*, COUNT(task_run.user_id) AS score
FROM "user" LEFT JOIN task_run
ON task_run.user_id="user".id where
"user".info -> 'container' ->> '{1}' = '{2}' AND
"user".restrict=false GROUP BY "user".id
) SELECT *, row_number() OVER (ORDER BY score DESC) as rank FROM scores;
'''.format(materialized_view, terms[0], terms[1])
db.session.execute(sql)
db.session.commit()
sql = '''
CREATE UNIQUE INDEX "{}"
on "{}"(id, rank);
'''.format(materialized_view_idx, materialized_view)
db.session.execute(sql)
db.session.commit()
return "Materialized view created"
