def get_links(self, game: str, version: int, userid: UserID) -> List[Link]:
"""
Given a game/version/userid, find all links between this user and other users
Parameters:
game - String identifier of the game looking up the user.
version - Integer version of the game looking up the user.
userid - Integer user ID, as looked up by one of the above functions.
Returns:
A list of Link objects.
"""
sql = "SELECT type, other_userid, data FROM link WHERE game = :game AND version = :version AND userid = :userid"
cursor = self.execute(sql, {'game': game, 'version': version, 'userid': userid})
links = []
for result in cursor.fetchall():
links.append(
Link(
userid,
result['type'],
UserID(result['other_userid']),
self.deserialize(result['data']),
)
)
return links
def get_all_time_based_achievements(self, game: str, version: int) -> List[Tuple[UserID, Achievement]]:
"""
Given a game/version, find all time-based achievements for all players.
Parameters:
game - String identifier of the game looking up the user.
version - Integer version of the game looking up the user.
Returns:
A list of (UserID, Achievement) objects.
"""
sql = (
"SELECT time_based_achievement.id AS id, time_based_achievement.type AS type, "
"time_based_achievement.data AS data, time_based_achievement.timestamp AS timestamp, "
"refid.userid AS userid FROM time_based_achievement, refid WHERE refid.game = :game AND "
"refid.version = :version AND refid.refid = time_based_achievement.refid"
)
cursor = self.execute(sql, {'game': game, 'version': version})
achievements = []
for result in cursor.fetchall():
achievements.append(
(
UserID(result['userid']),
Achievement(
result['id'],
result['type'],
result['timestamp'],
self.deserialize(result['data']),
),
)
)
return achievements
def get_all_arcades(self) -> List[Arcade]:
"""
List all known arcades in the system.
Returns:
A list of Arcade objects.
"""
sql = "SELECT userid, arcadeid FROM arcade_owner"
cursor = self.execute(sql)
arcade_to_owners: Dict[int, List[UserID]] = {}
for row in cursor.fetchall():
arcade = row['arcadeid']
owner = UserID(row['userid'])
if arcade not in arcade_to_owners:
arcade_to_owners[arcade] = []
arcade_to_owners[arcade].append(owner)
sql = "SELECT id, name, description, pin, data FROM arcade"
cursor = self.execute(sql)
return [
Arcade(
ArcadeID(result['id']),
result['name'],
result['description'],
result['pin'],
self.deserialize(result['data']),
arcade_to_owners.get(result['id'], []),
) for result in cursor.fetchall()
]
def get_all_lobbies(self, game: str,
version: int) -> List[Tuple[UserID, ValidatedDict]]:
"""
Given a game and version, look up all active lobbies.
Parameters:
game - String identifying a game series.
version - Integer identifying the version of the game in the series.
Returns:
A list of dictionaries representing lobby info stored by a game class.
"""
sql = ("SELECT userid, id, data FROM lobby "
"WHERE game = :game AND version = :version AND time > :time")
cursor = self.execute(
sql,
{
'game': game,
'version': version,
'time': Time.now() - Time.SECONDS_IN_HOUR,
},
)
ret = []
for result in cursor.fetchall():
data = ValidatedDict(self.deserialize(result['data']))
data['id'] = result['id']
ret.append((UserID(result['userid']), data))
return ret
def get_all_play_session_infos(
self, game: str,
version: int) -> List[Tuple[UserID, ValidatedDict]]:
"""
Given a game and version, look up all play session information.
Parameters:
game - String identifying a game series.
version - Integer identifying the version of the game in the series.
Returns:
A list of Tuples, consisting of a UserID and the dictionary that would be
returned for that user if get_play_session_info() was called for that user.
"""
sql = ("SELECT id, time, userid, data FROM playsession "
"WHERE game = :game AND version = :version "
"AND time > :time")
cursor = self.execute(
sql,
{
'game': game,
'version': version,
'time': Time.now() - Time.SECONDS_IN_HOUR,
},
)
ret = []
for result in cursor.fetchall():
data = ValidatedDict(self.deserialize(result['data']))
data['id'] = result['id']
data['time'] = result['time']
ret.append((UserID(result['userid']), data))
return ret
def get_events(
self,
userid: Optional[UserID] = None,
arcadeid: Optional[ArcadeID] = None,
event: Optional[str] = None,
limit: Optional[int] = None,
since_id: Optional[int] = None,
until_id: Optional[int] = None,
) -> List[Event]:
# Base query
sql = "SELECT id, timestamp, userid, arcadeid, type, data FROM audit "
# Lets get specific!
wheres = []
if userid is not None:
wheres.append("userid = :userid")
if arcadeid is not None:
wheres.append("arcadeid = :arcadeid")
if event is not None:
wheres.append("type = :event")
if since_id is not None:
wheres.append("id >= :since_id")
if until_id is not None:
wheres.append("id < :until_id")
if len(wheres) > 0:
sql = sql + f"WHERE {' AND '.join(wheres)} "
# Order it newest to oldest
sql = sql + "ORDER BY id DESC"
if limit is not None:
sql = sql + " LIMIT :limit"
cursor = self.execute(
sql, {
'userid': userid,
'arcadeid': arcadeid,
'event': event,
'limit': limit,
'since_id': since_id,
'until_id': until_id
})
events = []
for result in cursor.fetchall():
if result['userid'] is not None:
userid = UserID(result['userid'])
else:
userid = None
if result['arcadeid'] is not None:
arcadeid = ArcadeID(result['arcadeid'])
else:
arcadeid = None
events.append(
Event(
result['id'],
result['timestamp'],
userid,
arcadeid,
result['type'],
self.deserialize(result['data']),
), )
return events
def get_all_profiles(self, game: str,
version: int) -> List[Tuple[UserID, ValidatedDict]]:
"""
Given a game/version, look up all user profiles for that game.
Parameters:
game - String identifier of the game we want all user profiles for.
version - Integer version of the game we want all user profiles for.
Returns:
A list of (UserID, dictionaries) previously stored by a game class for each profile.
"""
sql = (
"SELECT refid.userid AS userid, refid.refid AS refid, extid.extid AS extid, profile.data AS data "
"FROM refid, profile, extid "
"WHERE refid.game = :game AND refid.version = :version "
"AND refid.refid = profile.refid AND extid.game = refid.game AND extid.userid = refid.userid"
)
cursor = self.execute(sql, {'game': game, 'version': version})
profiles = []
for result in cursor.fetchall():
profile = {
'refid': result['refid'],
'extid': result['extid'],
'game': game,
'version': version,
}
profile.update(self.deserialize(result['data']))
profiles.append((
UserID(result['userid']),
ValidatedDict(profile),
))
return profiles
def from_extid(self, game: str, version: int,
extid: int) -> Optional[UserID]:
"""
Given a generated ExtID, look up a user ID.
Note that there is a unique RefID and ExtID for each profile, and both can be used
to look up a user. When creating a new profile, we generate a unique RefID and ExtID.
Parameters:
game - String identifier of the game looking up the user.
version - Integer version of the game looking up the user.
extid - ExtID in question, most likely previously generated by this class.
Returns:
User ID as an integer if found, or None if not.
"""
# First, look up the user account
sql = "SELECT userid FROM extid WHERE game = :game AND extid = :extid"
cursor = self.execute(sql, {'game': game, 'extid': extid})
if cursor.rowcount != 1:
# Couldn't find a user with this refid
return None
result = cursor.fetchone()
return UserID(result['userid'])
def get_all_cards(self) -> List[Tuple[str, Optional[UserID]]]:
"""
Look up all cards associated with any account.
Returns:
A list of Tuples representing representing card ID, user ID pairs.
"""
sql = "SELECT id, userid FROM card"
cursor = self.execute(sql)
return [(res['id'], UserID(res['userid']) if res['userid'] is not None else None) for res in cursor.fetchall()]
def get_all_cards(self) -> List[Tuple[str, UserID]]:
"""
Look up all cards associated with any account.
Returns:
A list of Tuples representing representing card ID, user ID pairs.
"""
sql = "SELECT id, userid FROM card"
cursor = self.execute(sql)
return [(str(res['id']).upper(), UserID(res['userid'])) for res in cursor.fetchall()]
def get_all_users(self) -> List[User]:
"""
Look up all users in the system.
Returns:
A list of User objects representing all users.
"""
sql = "SELECT id, username, email, admin FROM user"
cursor = self.execute(sql)
return [
User(UserID(result['id']), result['username'], result['email'], result['admin'] == 1)
for result in cursor.fetchall()
]
def from_session(self, session: str) -> Optional[UserID]:
"""
Given a previously-opened session, look up a user ID.
Parameters:
session - String identifying a session that was opened by create_session.
Returns:
User ID as an integer if found, or None if the session is expired or doesn't exist.
"""
userid = self._from_session(session, 'userid')
if userid is None:
return None
return UserID(userid)
def get_all_players(self, game: str, version: int) -> List[UserID]:
"""
Given a game/version, look up all user IDs that played this game/version.
Parameters:
game - String identifier of the game we want all user profiles for.
version - Integer version of the game we want all user profiles for.
Returns:
A list of UserIDs for users that played this version of this game.
"""
sql = ("SELECT refid.userid AS userid FROM refid "
"WHERE refid.game = :game AND refid.version = :version")
cursor = self.execute(sql, {'game': game, 'version': version})
return [UserID(result['userid']) for result in cursor.fetchall()]
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 from_username(self, username: str) -> Optional[UserID]:
"""
Given a username, look up a user ID.
Parameters:
username - A string representing the user's username.
Returns:
User ID as an integer if found, or None if not.
"""
sql = "SELECT id FROM user WHERE username = :username"
cursor = self.execute(sql, {'username': username})
if cursor.rowcount != 1:
# Couldn't find this username
return None
result = cursor.fetchone()
return UserID(result['id'])
def get_attempt_by_key(self, game: str, version: int,
key: int) -> Optional[Tuple[UserID, Attempt]]:
"""
Look up a previous attempt 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 Attempt 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_history.id AS scorekey, score_history.timestamp AS timestamp, score_history.userid AS userid, "
+
"score_history.lid AS lid, score_history.new_record AS new_record, score_history.points AS points, score_history.data AS data FROM score_history, music "
+
"WHERE score_history.id = :scorekey AND score_history.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']),
Attempt(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['lid'],
True if result['new_record'] == 1 else False,
self.deserialize(result['data']),
))
def get_balances(self, arcadeid: ArcadeID) -> List[Tuple[UserID, int]]:
"""
Given an arcade ID, look up all user's PASELI balances for that arcade.
Parameters:
arcadeid - The arcade in question.
Returns:
The PASELI balance for each user at this arcade.
"""
sql = "SELECT userid, balance FROM balance WHERE arcadeid = :arcadeid"
cursor = self.execute(sql, {'arcadeid': arcadeid})
balances = []
for entry in cursor.fetchall():
balances.append((
UserID(entry['userid']),
entry['balance'],
))
return balances
def from_cardid(self, cardid: str) -> Optional[UserID]:
"""
Given a 16 digit card ID, look up a user ID.
Note that this is the E004 number as stored on the card. Not the 16 digit
ASCII value on the back. Use CardCipher to convert.
Parameters:
cardid - 16-digit card ID to look for.
Returns:
User ID as an integer if found, or None if not.
"""
# First, look up the user account
sql = "SELECT userid FROM card WHERE id = :id"
cursor = self.execute(sql, {'id': cardid})
if cursor.rowcount != 1:
# Couldn't find a user with this card
return None
result = cursor.fetchone()
return UserID(result['userid'])
def card_to_userid(cardid: str) -> UserID:
return UserID(int(cardid, 16))
def get_all_attempts(
self,
game: str,
version: Optional[int] = None,
userid: Optional[UserID] = None,
songid: Optional[int] = None,
songchart: Optional[int] = None,
timelimit: Optional[int] = None,
limit: Optional[int] = None,
offset: Optional[int] = None,
) -> List[Tuple[Optional[UserID], Attempt]]:
"""
Look up all of the attempts to score for a particular game.
Parameters:
game - String representing a game series.
version - Integer representing which version of the game.
Returns:
A list of UserID, Attempt objects representing all score attempts for a game, sorted newest to oldest attempts.
"""
# First, construct the queries for grabbing the songid/chart
if version is not None:
songidquery = (
'SELECT songid FROM music WHERE music.id = score_history.musicid AND game = :game AND version = :version'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score_history.musicid AND game = :game AND version = :version'
)
else:
songidquery = (
'SELECT songid FROM music WHERE music.id = score_history.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
chartquery = (
'SELECT chart FROM music WHERE music.id = score_history.musicid AND game = :game ORDER BY version DESC LIMIT 1'
)
# 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, timestamp, points, new_record, lid, data, userid "
"FROM score_history WHERE musicid IN ({})").format(
songidquery, chartquery, innerselect)
# Now, limit the query
if userid is not None:
sql = sql + ' AND userid = :userid'
if timelimit is not None:
sql = sql + ' AND timestamp >= :timestamp'
sql = sql + ' ORDER BY timestamp DESC'
if limit is not None:
sql = sql + ' LIMIT :limit'
if offset is not None:
sql = sql + ' OFFSET :offset'
# Now, query itself
cursor = self.execute(
sql, {
'game': game,
'version': version,
'userid': userid,
'songid': songid,
'songchart': songchart,
'timestamp': timelimit,
'limit': limit,
'offset': offset,
})
# Now objectify the attempts
attempts = []
for result in cursor.fetchall():
attempts.append(
(UserID(result['userid']) if result['userid'] > 0 else None,
Attempt(
result['scorekey'],
result['songid'],
result['chart'],
result['points'],
result['timestamp'],
result['lid'],
True if result['new_record'] == 1 else False,
self.deserialize(result['data']),
)))
return attempts
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
def test_is_remote(self) -> None:
self.assertTrue(RemoteUser.is_remote(UserID(2**64 - 1)))
self.assertTrue(RemoteUser.is_remote(UserID(2**32)))
self.assertFalse(RemoteUser.is_remote(UserID(2**32 - 1)))
self.assertFalse(RemoteUser.is_remote(UserID(0)))
self.assertFalse(RemoteUser.is_remote(UserID(1)))
def test_get_league_scores(self) -> None:
data = Mock()
data.local = Mock()
data.local.user = Mock()
# Test correct behavior on empty input
self.assertEqual(
JubeatProp._get_league_scores(
None,
999,
[]
),
(
[],
[],
),
)
# Test that we can load last week's score if it exists for a user
data.local.user.get_achievement = Mock(return_value={'score': [123, 456, 789]})
self.assertEqual(
JubeatProp._get_league_scores(
data,
999,
[(UserID(1337), {})],
),
(
[(1337, 1368)],
[],
),
)
data.local.user.get_achievement.assert_called_once_with(
JubeatProp.game,
JubeatProp.version,
1337,
998,
'league',
)
data.local.user.get_achievement.reset_mock()
# Test that if it doesn't exist last week they get marked as absent
data.local.user.get_achievement = Mock(return_value=None)
self.assertEqual(
JubeatProp._get_league_scores(
data,
999,
[(UserID(1337), {})],
),
(
[],
[1337],
),
)
data.local.user.get_achievement.assert_called_once_with(
JubeatProp.game,
JubeatProp.version,
1337,
998,
'league',
)
data.local.user.get_achievement.reset_mock()
