def __format_reflec_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
status = {
'np': DBConstants.REFLEC_BEAT_CLEAR_TYPE_NO_PLAY,
'failed': DBConstants.REFLEC_BEAT_CLEAR_TYPE_FAILED,
'cleared': DBConstants.REFLEC_BEAT_CLEAR_TYPE_CLEARED,
'hc': DBConstants.REFLEC_BEAT_CLEAR_TYPE_HARD_CLEARED,
'shc': DBConstants.REFLEC_BEAT_CLEAR_TYPE_S_HARD_CLEARED,
}.get(data.get('status'), DBConstants.REFLEC_BEAT_CLEAR_TYPE_NO_PLAY)
halo = {
'none': DBConstants.REFLEC_BEAT_COMBO_TYPE_NONE,
'ac': DBConstants.REFLEC_BEAT_COMBO_TYPE_ALMOST_COMBO,
'fc': DBConstants.REFLEC_BEAT_COMBO_TYPE_FULL_COMBO,
'fcaj': DBConstants.REFLEC_BEAT_COMBO_TYPE_FULL_COMBO_ALL_JUST,
}.get(data.get('halo'), DBConstants.REFLEC_BEAT_COMBO_TYPE_NONE)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'achievement_rate': int(data.get('rate', -1)),
'clear_type': status,
'combo_type': halo,
'miss_count': int(data.get('miss', -1)),
'combo': int(data.get('combo', -1)),
},
)
def __format_iidx_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
status = {
'np': DBConstants.IIDX_CLEAR_STATUS_NO_PLAY,
'failed': DBConstants.IIDX_CLEAR_STATUS_FAILED,
'ac': DBConstants.IIDX_CLEAR_STATUS_ASSIST_CLEAR,
'ec': DBConstants.IIDX_CLEAR_STATUS_EASY_CLEAR,
'nc': DBConstants.IIDX_CLEAR_STATUS_CLEAR,
'hc': DBConstants.IIDX_CLEAR_STATUS_HARD_CLEAR,
'exhc': DBConstants.IIDX_CLEAR_STATUS_EX_HARD_CLEAR,
'fc': DBConstants.IIDX_CLEAR_STATUS_FULL_COMBO,
}.get(data.get('status'), DBConstants.IIDX_CLEAR_STATUS_NO_PLAY)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'clear_status': status,
'ghost': bytes([int(b) for b in data.get('ghost', [])]),
'miss_count': int(data.get('miss', -1)),
'pgreats': int(data.get('pgreat', -1)),
'greats': int(data.get('great', -1)),
},
)
def __merge_jubeat_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'ghost':
oldscore.data.get('ghost') if oldscore.points > newscore.points
else newscore.data.get('ghost'),
'combo':
self.__max(oldscore.data['combo'], newscore.data['combo']),
'medal':
self.__max(oldscore.data['medal'], newscore.data['medal']),
'music_rate':
self.__max(oldscore.data.get('music_rate', 0),
newscore.data.get('music_rate', 0))
},
)
def __merge_reflec_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'clear_type':
self.__max(oldscore.data['clear_type'],
newscore.data['clear_type']),
'combo_type':
self.__max(oldscore.data['combo_type'],
newscore.data['combo_type']),
'miss_count':
self.__min(oldscore.data.get_int('miss_count', -1),
newscore.data.get_int('miss_count', -1)),
'combo':
self.__max(oldscore.data['combo'], newscore.data['combo']),
'achievement_rate':
self.__max(oldscore.data['achievement_rate'],
newscore.data['achievement_rate']),
},
)
def __merge_sdvx_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'grade':
self.__max(oldscore.data['grade'], newscore.data['grade']),
'clear_type':
self.__max(oldscore.data['clear_type'],
newscore.data['clear_type']),
'combo':
self.__max(oldscore.data.get_int('combo', 1),
newscore.data.get_int('combo', -1)),
'stats':
oldscore.data['stats'] if oldscore.points > newscore.points
else newscore.data['stats'],
},
)
def __merge_iidx_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'clear_status':
self.__max(oldscore.data['clear_status'],
newscore.data['clear_status']),
'ghost':
oldscore.data.get('ghost') if oldscore.points > newscore.points
else newscore.data.get('ghost'),
'miss_count':
self.__min(oldscore.data.get_int('miss_count', -1),
newscore.data.get_int('miss_count', -1)),
'pgreats':
oldscore.data.get_int('pgreats', -1) if oldscore.points >
newscore.points else newscore.data.get_int('pgreats', -1),
'greats':
oldscore.data.get_int('greats', -1) if oldscore.points >
newscore.points else newscore.data.get_int('greats', -1),
},
)
def __merge_ddr_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'rank':
self.__max(oldscore.data['rank'], newscore.data['rank']),
'halo':
self.__max(oldscore.data['halo'], newscore.data['halo']),
'ghost':
oldscore.data.get('ghost') if oldscore.points > newscore.points
else newscore.data.get('ghost'),
'trace':
oldscore.data.get('trace') if oldscore.points > newscore.points
else newscore.data.get('trace'),
'combo':
self.__max(oldscore.data['combo'], newscore.data['combo']),
},
)
def __format_jubeat_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
status = {
'failed': DBConstants.JUBEAT_PLAY_MEDAL_FAILED,
'cleared': DBConstants.JUBEAT_PLAY_MEDAL_CLEARED,
'nfc': DBConstants.JUBEAT_PLAY_MEDAL_NEARLY_FULL_COMBO,
'fc': DBConstants.JUBEAT_PLAY_MEDAL_FULL_COMBO,
'nec': DBConstants.JUBEAT_PLAY_MEDAL_NEARLY_EXCELLENT,
'exc': DBConstants.JUBEAT_PLAY_MEDAL_EXCELLENT,
}.get(data.get('status'), DBConstants.JUBEAT_PLAY_MEDAL_FAILED)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'medal': status,
'combo': int(data.get('combo', -1)),
'ghost': [int(x) for x in data.get('ghost', [])]
},
)
def __format_popn_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
status = {
'cf': DBConstants.POPN_MUSIC_PLAY_MEDAL_CIRCLE_FAILED,
'df': DBConstants.POPN_MUSIC_PLAY_MEDAL_DIAMOND_FAILED,
'sf': DBConstants.POPN_MUSIC_PLAY_MEDAL_STAR_FAILED,
'ec': DBConstants.POPN_MUSIC_PLAY_MEDAL_EASY_CLEAR,
'cc': DBConstants.POPN_MUSIC_PLAY_MEDAL_CIRCLE_CLEARED,
'dc': DBConstants.POPN_MUSIC_PLAY_MEDAL_DIAMOND_CLEARED,
'sc': DBConstants.POPN_MUSIC_PLAY_MEDAL_STAR_CLEARED,
'cfc': DBConstants.POPN_MUSIC_PLAY_MEDAL_CIRCLE_FULL_COMBO,
'dfc': DBConstants.POPN_MUSIC_PLAY_MEDAL_DIAMOND_FULL_COMBO,
'sfc': DBConstants.POPN_MUSIC_PLAY_MEDAL_STAR_FULL_COMBO,
'p': DBConstants.POPN_MUSIC_PLAY_MEDAL_PERFECT,
}.get(data.get('status'),
DBConstants.POPN_MUSIC_PLAY_MEDAL_CIRCLE_FAILED)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'medal': status,
'combo': int(data.get('combo', -1)),
},
)
def __format_ddr_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
halo = {
'none': DBConstants.DDR_HALO_NONE,
'gfc': DBConstants.DDR_HALO_GOOD_FULL_COMBO,
'fc': DBConstants.DDR_HALO_GREAT_FULL_COMBO,
'pfc': DBConstants.DDR_HALO_PERFECT_FULL_COMBO,
'mfc': DBConstants.DDR_HALO_MARVELOUS_FULL_COMBO,
}.get(data.get('halo'), DBConstants.DDR_HALO_NONE)
rank = {
"AAA": DBConstants.DDR_RANK_AAA,
"AA+": DBConstants.DDR_RANK_AA_PLUS,
"AA": DBConstants.DDR_RANK_AA,
"AA-": DBConstants.DDR_RANK_AA_MINUS,
"A+": DBConstants.DDR_RANK_A_PLUS,
"A": DBConstants.DDR_RANK_A,
"A-": DBConstants.DDR_RANK_A_MINUS,
"B+": DBConstants.DDR_RANK_B_PLUS,
"B": DBConstants.DDR_RANK_B,
"B-": DBConstants.DDR_RANK_B_MINUS,
"C+": DBConstants.DDR_RANK_C_PLUS,
"C": DBConstants.DDR_RANK_C,
"C-": DBConstants.DDR_RANK_C_MINUS,
"D+": DBConstants.DDR_RANK_D_PLUS,
"D": DBConstants.DDR_RANK_D,
"E": DBConstants.DDR_RANK_E,
}.get(data.get('rank'), DBConstants.DDR_RANK_E)
ghost = ''
trace: List[int] = []
if version == VersionConstants.DDR_ACE:
# DDR Ace is specia
ghost = ''.join([str(x) for x in data.get('ghost', [])])
else:
trace = [int(x) for x in data.get('ghost', [])]
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'combo': int(data.get('combo', -1)),
'rank': rank,
'halo': halo,
'ghost': ghost,
'trace': trace,
},
)
def get_scores(
self,
game: str,
version: int,
userid: UserID,
since: Optional[int] = None,
until: Optional[int] = None,
) -> List[Score]:
"""
Look up all of a user's previous high scores.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
userid - Integer representing a user. Usually looked up with UserData.
Returns:
A list of Score objects representing all high scores for a game.
"""
sql = (
"SELECT music.songid AS songid, music.chart AS chart, score.id AS scorekey, score.timestamp AS timestamp, score.update AS `update`, score.lid AS lid, "
+
"(select COUNT(score_history.timestamp) FROM score_history WHERE score_history.musicid = music.id AND score_history.userid = :userid) AS plays, "
+
"score.points AS points, score.data AS data FROM score, music WHERE score.userid = :userid AND score.musicid = music.id "
+ "AND music.game = :game AND music.version = :version")
if since is not None:
sql = sql + ' AND score.update >= :since'
if until is not None:
sql = sql + ' AND score.update < :until'
cursor = self.execute(
sql, {
'userid': userid,
'game': game,
'version': version,
'since': since,
'until': until
})
scores = []
for result in cursor.fetchall():
scores.append(
Score(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['update'],
result['lid'],
result['plays'],
self.deserialize(result['data']),
))
return scores
def get_score(self, game: str, version: int, userid: UserID, songid: int,
songchart: int) -> Optional[Score]:
"""
Look up a user's previous high score.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
userid - Integer representing a user. Usually looked up with UserData.
songid - ID of the song according to the game.
songchart - Chart number according to the game.
Returns:
The optional data stored by the game previously, or None if no score exists.
"""
sql = (
"SELECT music.songid AS songid, music.chart AS chart, score.id AS scorekey, score.timestamp AS timestamp, score.update AS `update`, score.lid AS lid, "
+
"(select COUNT(score_history.timestamp) FROM score_history WHERE score_history.musicid = music.id AND score_history.userid = :userid) AS plays, "
+
"score.points AS points, score.data AS data FROM score, music WHERE score.userid = :userid AND score.musicid = music.id "
+
"AND music.game = :game AND music.version = :version AND music.songid = :songid AND music.chart = :songchart"
)
cursor = self.execute(
sql,
{
'userid': userid,
'game': game,
'version': version,
'songid': songid,
'songchart': songchart,
},
)
if cursor.rowcount != 1:
# score doesn't exist
return None
result = cursor.fetchone()
return Score(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['update'],
result['lid'],
result['plays'],
self.deserialize(result['data']),
)
def __format_sdvx_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
status = {
'np': DBConstants.SDVX_CLEAR_TYPE_NO_PLAY,
'failed': DBConstants.SDVX_CLEAR_TYPE_FAILED,
'cleared': DBConstants.SDVX_CLEAR_TYPE_CLEAR,
'hc': DBConstants.SDVX_CLEAR_TYPE_HARD_CLEAR,
'uc': DBConstants.SDVX_CLEAR_TYPE_ULTIMATE_CHAIN,
'puc': DBConstants.SDVX_CLEAR_TYPE_PERFECT_ULTIMATE_CHAIN,
}.get(data.get('status'), DBConstants.SDVX_CLEAR_TYPE_NO_PLAY)
rank = {
'E': DBConstants.SDVX_GRADE_NO_PLAY,
'D': DBConstants.SDVX_GRADE_D,
'C': DBConstants.SDVX_GRADE_C,
'B': DBConstants.SDVX_GRADE_B,
'A': DBConstants.SDVX_GRADE_A,
'A+': DBConstants.SDVX_GRADE_A_PLUS,
'AA': DBConstants.SDVX_GRADE_AA,
'AA+': DBConstants.SDVX_GRADE_AA_PLUS,
'AAA': DBConstants.SDVX_GRADE_AAA,
'AAA+': DBConstants.SDVX_GRADE_AAA_PLUS,
'S': DBConstants.SDVX_GRADE_S,
}.get(data.get('rank'), DBConstants.SDVX_GRADE_NO_PLAY)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'grade': rank,
'clear_type': status,
'combo': int(data.get('combo', -1)),
'stats': {
'btn_rate': int(data.get('buttonrate', -1)),
'long_rate': int(data.get('longrate', -1)),
'vol_rate': int(data.get('volrate', -1)),
},
},
)
def get_score_by_key(self, game: str, version: int,
key: int) -> Optional[Tuple[UserID, Score]]:
"""
Look up previous high score by key.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
key - Integer representing a unique key fetched in a previous Score lookup.
Returns:
The optional data stored by the game previously, or None if no score exists.
"""
sql = (
"SELECT music.songid AS songid, music.chart AS chart, score.id AS scorekey, score.timestamp AS timestamp, score.update AS `update`, "
+ "score.userid AS userid, score.lid AS lid, " +
"(select COUNT(score_history.timestamp) FROM score_history WHERE score_history.musicid = music.id AND score_history.userid = score.userid) AS plays, "
+
"score.points AS points, score.data AS data FROM score, music WHERE score.id = :scorekey AND score.musicid = music.id "
+ "AND music.game = :game AND music.version = :version")
cursor = self.execute(
sql,
{
'game': game,
'version': version,
'scorekey': key,
},
)
if cursor.rowcount != 1:
# score doesn't exist
return None
result = cursor.fetchone()
return (UserID(result['userid']),
Score(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['update'],
result['lid'],
result['plays'],
self.deserialize(result['data']),
))
def __merge_popn_score(self, version: int, oldscore: Score,
newscore: Score) -> Score:
return Score(
-1,
oldscore.id,
oldscore.chart,
self.__max(oldscore.points, newscore.points),
self.__max(oldscore.timestamp, newscore.timestamp),
self.__max(self.__max(oldscore.update, newscore.update),
self.__max(oldscore.timestamp, newscore.timestamp)),
oldscore.
location, # Always propagate location from local setup if possible
oldscore.plays + newscore.plays,
{
'combo':
self.__max(oldscore.data['combo'], newscore.data['combo']),
'medal':
self.__max(oldscore.data['medal'], newscore.data['medal']),
},
)
def __format_museca_score(self, version: int, songid: int, songchart: int,
data: Dict[str, Any]) -> Score:
rank = {
'death': DBConstants.MUSECA_GRADE_DEATH,
'poor': DBConstants.MUSECA_GRADE_POOR,
'mediocre': DBConstants.MUSECA_GRADE_MEDIOCRE,
'good': DBConstants.MUSECA_GRADE_GOOD,
'great': DBConstants.MUSECA_GRADE_GREAT,
'excellent': DBConstants.MUSECA_GRADE_EXCELLENT,
'superb': DBConstants.MUSECA_GRADE_SUPERB,
'masterpiece': DBConstants.MUSECA_GRADE_MASTERPIECE,
'perfect': DBConstants.MUSECA_GRADE_PERFECT,
}.get(data.get('rank'), DBConstants.MUSECA_GRADE_DEATH)
status = {
'failed': DBConstants.MUSECA_CLEAR_TYPE_FAILED,
'cleared': DBConstants.MUSECA_CLEAR_TYPE_CLEARED,
'fc': DBConstants.MUSECA_CLEAR_TYPE_FULL_COMBO,
}.get(data.get('status'), DBConstants.MUSECA_CLEAR_TYPE_FAILED)
return Score(
-1,
songid,
songchart,
int(data.get('points', 0)),
int(data.get('timestamp', -1)),
self.__max(int(data.get('timestamp', -1)),
int(data.get('updated', -1))),
-1, # No location for remote play
1, # No play info for remote play
{
'grade': rank,
'clear_type': status,
'combo': int(data.get('combo', -1)),
'stats': {
'btn_rate': int(data.get('buttonrate', -1)),
'long_rate': int(data.get('longrate', -1)),
'vol_rate': int(data.get('volrate', -1)),
},
},
)
def get_all_records(
self,
game: str,
version: Optional[int] = None,
userlist: Optional[List[UserID]] = None,
locationlist: Optional[List[int]] = None,
) -> List[Tuple[UserID, Score]]:
"""
Look up all of a game's records, only returning the top score for each song. For score ties,
king-of-the-hill rules are in effect, so for two players with an identical top score, the player
that got the score last wins. If a list of user IDs is given, we will only look up records pertaining
to those users. So if another user has a higher record, we will ignore this. This can be used to
display area-local high scores, etc.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
userlist - List of UserIDs to limit the search to.
locationlist - A list of location IDs to limit searches to.
Returns:
A list of UserID, Score objects representing all high scores for a game.
"""
# First, construct the queries for grabbing the songid/chart
if version is not None:
songidquery = (
'SELECT songid FROM music WHERE music.id = score.musicid AND game = :game AND version = :version'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score.musicid AND game = :game AND version = :version'
)
else:
songidquery = (
'SELECT songid FROM music WHERE music.id = score.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
# Next, get a list of all songs that were played given the input criteria
musicid_sql = (
"SELECT DISTINCT(score.musicid) FROM score, music WHERE score.musicid = music.id AND music.game = :game"
)
params: Dict[str, Any] = {'game': game}
if version is not None:
musicid_sql = musicid_sql + ' AND music.version = :version'
params['version'] = version
# Figure out where the record was earned
if locationlist is not None:
if len(locationlist) == 0:
# We don't have any locations, but SQL will shit the bed, so lets add a default one.
locationlist.append(-1)
location_sql = "AND score.lid IN :locationlist"
params['locationlist'] = tuple(locationlist)
else:
location_sql = ""
# Figure out who got the record
if userlist is not None:
if len(userlist) == 0:
# We don't have any users, but SQL will shit the bed, so lets add a fake one.
userlist.append(UserID(-1))
user_sql = f"SELECT userid FROM score WHERE score.musicid = played.musicid AND score.userid IN :userlist {location_sql} ORDER BY points DESC, timestamp DESC LIMIT 1"
params['userlist'] = tuple(userlist)
else:
user_sql = f"SELECT userid FROM score WHERE score.musicid = played.musicid {location_sql} ORDER BY points DESC, timestamp DESC LIMIT 1"
records_sql = f"SELECT ({user_sql}) AS userid, musicid FROM ({musicid_sql}) played"
# Now, join it up against the score and music table to grab the info we need
sql = (
"SELECT ({}) AS songid, ({}) AS chart, score.points AS points, score.userid AS userid, score.id AS scorekey, score.data AS data, "
+ "score.timestamp AS timestamp, score.update AS `update`, " +
"score.lid AS lid, (select COUNT(score_history.timestamp) FROM score_history WHERE score_history.musicid = score.musicid) AS plays "
+
"FROM score, ({}) records WHERE records.userid = score.userid AND records.musicid = score.musicid"
).format(songidquery, chartquery, records_sql)
cursor = self.execute(sql, params)
scores = []
for result in cursor.fetchall():
scores.append((UserID(result['userid']),
Score(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['update'],
result['lid'],
result['plays'],
self.deserialize(result['data']),
)))
return scores
def get_all_scores(
self,
game: str,
version: Optional[int] = None,
userid: Optional[UserID] = None,
songid: Optional[int] = None,
songchart: Optional[int] = None,
since: Optional[int] = None,
until: Optional[int] = None,
) -> List[Tuple[UserID, Score]]:
"""
Look up all of a game's high scores for all users.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
Returns:
A list of UserID, Score objects representing all high scores for a game.
"""
# First, construct the queries for grabbing the songid/chart
if version is not None:
songidquery = (
'SELECT songid FROM music WHERE music.id = score.musicid AND game = :game AND version = :version'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score.musicid AND game = :game AND version = :version'
)
else:
songidquery = (
'SELECT songid FROM music WHERE music.id = score.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
# Select statement for getting play count
playselect = (
'SELECT COUNT(timestamp) FROM score_history WHERE score_history.musicid = score.musicid AND score_history.userid = score.userid'
)
# Now, construct the inner select statement so we can choose which scores we care about
innerselect = ('SELECT DISTINCT(id) FROM music WHERE game = :game')
if version is not None:
innerselect = innerselect + ' AND version = :version'
if songid is not None:
innerselect = innerselect + ' AND songid = :songid'
if songchart is not None:
innerselect = innerselect + ' AND chart = :songchart'
# Finally, construct the full query
sql = (
"SELECT ({}) AS songid, ({}) AS chart, id AS scorekey, points, timestamp, `update`, lid, data, userid, ({}) AS plays "
"FROM score WHERE musicid IN ({})").format(songidquery, chartquery,
playselect, innerselect)
# Now, limit the query
if userid is not None:
sql = sql + ' AND userid = :userid'
if since is not None:
sql = sql + ' AND score.update >= :since'
if until is not None:
sql = sql + ' AND score.update < :until'
# Now, query itself
cursor = self.execute(
sql, {
'game': game,
'version': version,
'userid': userid,
'songid': songid,
'songchart': songchart,
'since': since,
'until': until,
})
# Objectify result
scores = []
for result in cursor.fetchall():
scores.append((UserID(result['userid']),
Score(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['update'],
result['lid'],
result['plays'],
self.deserialize(result['data']),
)))
return scores
