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_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 from_userid(self, userid: UserID) -> List[ArcadeID]: """ Given a user ID, look up the arcades that this user owns. Parameters: userid - Integer specifying the user we are interested in. Returns: A list of integer IDs of the arcades this user owns. """ sql = "SELECT arcadeid FROM arcade_owner WHERE userid = :userid" cursor = self.execute(sql, {'userid': userid}) return [ArcadeID(result['arcadeid']) for result in cursor.fetchall()]
def from_session(self, session: str) -> Optional[ArcadeID]: """ 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. """ arcadeid = self._from_session(session, 'arcadeid') if arcadeid is None: return None return ArcadeID(arcadeid)