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